欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

C#定制Excel界面并实现与数据库交互的方法

程序员文章站 2022-03-21 13:50:35
excel是微软办公套装软件的一个重要的组成部分,它可以进行各种数据的处理、统计分析和辅助决策操作,广泛地应用于管理、统计财经、金融等众多领域。(另外,excel还是伦敦一...

excel是微软办公套装软件的一个重要的组成部分,它可以进行各种数据的处理、统计分析和辅助决策操作,广泛地应用于管理、统计财经、金融等众多领域。(另外,excel还是伦敦一所会展中心的名称)。.net可以创建excel add-in对excel进行功能扩展,这些扩展的功能包括自定义用户函数,自定义ui,与数据库进行数据交互等。

一 主要的excel开发方式

  1 vba

    vba是一种visual basic的宏语言,它是最早的office提供定制化的一种解决方案,vba是vb的一个子集,和visual basic不同,vba是一种宿主型语言,无论是专业的开发人员,还是刚入门的非开发人员,都可以利用vba完成简单或复杂的需求。

  2 excel addin

    excel addin,就像visual studio外接插件一样,也可以使用一些技术为office开发一些插件。对vba的一些问题,一些专业的开发人员,可以使用 visualbasic或者visualc++等工具来引用office的一些dll,来针对office进行开发。开发的时候将dll注册为com组 件,并在注册表里面进行注册,这样就可以在excel里直接调用这些插件。

  3 vsto (visual studio tools for office)

    vsto主要是对office的一些dll进行了.net封装,使得我们可以使用.net上的语言来方便的对office的一些方法进行调用。所 以,office开发跨入了一个新的时代,开发人员可以使用更加高级的语言和熟悉的技术来更容易的进行office开发。 对于企业及的应用和开发,vsto或许是首要选择,他极大地扩展了office应用程序的能力,使用.net平台支持的编程语言,能够直接访问.net上面众多的类库。具有较好的安全机制。简化了office插件的开发和部署。

  4 xll

    xll是excel的一种外接应用程序,他使用c和c++开发,程序通过调用excel暴漏的c接口来实现扩展功能。这种方式开发的应用程序效率高,但是难度大,对开发者自身的要求较高。开源项目excel-dna就是使用xll技术开发的,能够帮助.net 开发人员来极大地简化rtd函数,同步、异步udf函数的编写和开发。

  5 openxml

    如果用户没有安装excel应用程序,或者在服务器端需要动态生成excel文件的时候。我们可能需要直接读取或者生成excel文件,这种情况下,如果要对excel文件进行各种定制化开发的话,建议使用openxml。npoi开源项目可以直接读写excel文件,而且兼容多个版本。

二 使用excel add-in构建扩展

  开发环境: 操作系统为windows server 2008r2 x64;excel为excel 2010 x64;开发工具为visual studio 2012旗舰版x64;数据库为sql server 2008r2 x64.

  1 程序结构

  用visual studio 2012新建一个exceladdindemo的excel add-in项目,并添加若干文件,程序结构如下图:

C#定制Excel界面并实现与数据库交互的方法

  其中,ribbonaddin可以定制2010的ui面板,sqlhelper.cs是一个简单的数据库访问帮助类,uclog.cs,ucpaneleft.cs,uctaskgrid.cs,uctaskpane.cs都为添加的自定义控件,并通过程序添加到excel界面中.运行起来的界面如下:

C#定制Excel界面并实现与数据库交互的方法

  程序可以通过在excel界面中输入id,first,last,email的值(对应标签的后一个单元格),单击用户列表面板上的保存按钮,将数据保存到数据库中.

  2 ribbonaddin设计

  我们通过ribbonaddin.cs给excel的ribbon添加了一个名为cumt的插件.ribbonaddin面板可以通过工具条控件方便的拖放到设计界面上.ribbonaddin.cs的属性设置如下图所示:

C#定制Excel界面并实现与数据库交互的方法

  后台代码如下:

 using system;
 using system.collections.generic;
 using system.linq;
 using system.text;
 using microsoft.office.tools.ribbon;
 namespace exceladdindemo
 {
   public partial class ribbonaddin
   {
     private void ribbonaddin_load(object sender, ribbonuieventargs e)
     {
     }
     private void btnabout_click(object sender, ribboncontroleventargs e)
     {
       system.windows.forms.messagebox.show("jackwangcumt!");
     }
     private void btnshow_click(object sender, ribboncontroleventargs e)
     {
       if (globals.thisaddin._mycustomtaskpane != null)
       {
         globals.thisaddin._mycustomtaskpane.visible = true;
       }
     }
     private void btnhide_click(object sender, ribboncontroleventargs e)
     {
       if (globals.thisaddin._mycustomtaskpane != null)
       {
         globals.thisaddin._mycustomtaskpane.visible = false;
       }
     }
   }
 }

  3 thisaddin逻辑编写

using system;
  using system.collections.generic;
  using system.linq;
  using system.text;
  using system.xml.linq;
  using excel = microsoft.office.interop.excel;
  namespace exceladdindemo
  {
    using microsoft.office.tools;
   public partial class thisaddin
   {
     public customtaskpane _mycustomtaskpane = null;
     private void thisaddin_startup(object sender, system.eventargs e)
     {
       uctaskpane taskpane = new uctaskpane();
       _mycustomtaskpane = this.customtaskpanes.add(taskpane, "我的任务面板");
       _mycustomtaskpane.width = ;//height有问题,此处width ==height
       _mycustomtaskpane.visible = true;
       _mycustomtaskpane.dockposition = microsoft.office.core.msoctpdockposition.msoctpdockpositiontop;
       ucpaneleft panleft = new ucpaneleft();
       _mycustomtaskpane = this.customtaskpanes.add(panleft, "组织");
       _mycustomtaskpane.width = ;
       _mycustomtaskpane.visible = true;
       _mycustomtaskpane.dockposition = microsoft.office.core.msoctpdockposition.msoctpdockpositionleft;
       uctaskgrid panright = new uctaskgrid();
       _mycustomtaskpane = this.customtaskpanes.add(panright, "用户列表");
       _mycustomtaskpane.width = ;
       _mycustomtaskpane.visible = true;
       _mycustomtaskpane.dockposition = microsoft.office.core.msoctpdockposition.msoctpdockpositionright;
       uclog panlog = new uclog();
       _mycustomtaskpane = this.customtaskpanes.add(panlog, "日志列表");
       _mycustomtaskpane.width = ;
       _mycustomtaskpane.visible = true;
       _mycustomtaskpane.dockposition = microsoft.office.core.msoctpdockposition.msoctpdockpositionbottom;
       //hook into the workbook open event
       //this is because office doesn't always have a document ready when this method is run 
       this.application.workbookactivate += application_workbookactivate;
       //test 
       //this.application.sheetselectionchange += application_sheetselectionchange;
     }
     void application_sheetselectionchange(object sh, excel.range target)
     {
       if (this.application != null)
       {
         this.application.caption = this.application.activecell.address.tostring();//$a$
         //+ this.application.activecell.addresslocal.tostring();//$a$
         //this.application.activecell.formula = "=sum(+)";
       }
     }
     void application_workbookactivate(excel.workbook wb)
     {
       //using microsoft.office.tools.excel 和 using microsoft.office.interop.excel 都有worksheet等,容易混淆
       //string path = this.application.activeworkbook.fullname;
       excel._worksheet ws = (excel._worksheet)this.application.activeworkbook.activesheet;
       ws.cells[, ] = "id";
       //如何设置只读等有待研究
       int r=,c=;
       //((excel.range)ws.cells[r, c]).numberformat = format;
       ((excel.range)ws.cells[r, c]).value = "id";
       ((excel.range)ws.cells[r, c]).interior.color =system.drawing. colortranslator.toole(system.drawing.color.red);
       //((excel.range)ws.cells[r, c]).style.name = "normal";
       ((excel.range)ws.cells[r, c]).style.font.bold = true;
       #region format
       ((microsoft.office.interop.excel.range)ws.get_range("a", "e")).font.bold = true;
       ((microsoft.office.interop.excel.range)ws.get_range("a", "e")).font.italic = true;
       ((microsoft.office.interop.excel.range)ws.get_range("a", "e")).font.color = system.drawing.color.fromargb(, , ).toargb();
       ((microsoft.office.interop.excel.range)ws.get_range("a", "e")).font.name = "calibri";
       ((microsoft.office.interop.excel.range)ws.get_range("a", "e")).font.size = ;
       //border
       excel.range range = ((microsoft.office.interop.excel.range)ws.get_range("b", "e"));
       excel. borders border = range.borders;
       border[excel.xlbordersindex.xledgebottom].linestyle =excel. xllinestyle.xlcontinuous;
       border.weight = d;
       border[excel.xlbordersindex.xledgetop].linestyle = excel.xllinestyle.xlcontinuous;
       border[excel.xlbordersindex.xledgeleft].linestyle = excel.xllinestyle.xlcontinuous;
       border[excel.xlbordersindex.xledgeright].linestyle = excel.xllinestyle.xlcontinuous;
       #endregion
       ws.cells[, ] = "first";
       ws.cells[, ] = "last";
       ws.cells[, ] = "email";
     }
     private void thisaddin_shutdown(object sender, system.eventargs e)
     {
     }
     #region vsto 生成的代码
     /// <summary>
     /// 设计器支持所需的方法 - 不要
     /// 使用代码编辑器修改此方法的内容。
     /// </summary>
     private void internalstartup()
     {
       this.startup += new system.eventhandler(thisaddin_startup);
       this.shutdown += new system.eventhandler(thisaddin_shutdown);
     }
     #endregion
   }
 }

   thisaddin_startup事件中,初始化四个面板,并对其基本属性进行设置,停靠在上的面板我设置其height无效,改成width后其效果和height预期的一样(不知道这个底层开发人员是怎么想的,哈哈!)另外 excel._worksheet ws = (excel._worksheet)this.application.activeworkbook.activesheet;是非常关键的一句,我这里足足折腾了很久,原因是using microsoft.office.tools.excel 和 using microsoft.office.interop.excel 都有worksheet元素,结构混淆了,运行时老是获取不到excel的activeworkbook.

  4 uctaskgrid设计

  uctaskgrid是一个用户控件,包含一个工具条和一个datagridview1控件,其设计界面如下:

C#定制Excel界面并实现与数据库交互的方法

  后台代码如下:

using system;
 using system.collections.generic;
 using system.componentmodel;
 using system.drawing;
 using system.data;
 using system.linq;
 using system.text;
 using system.windows.forms;
 namespace exceladdindemo
 {
   using excel = microsoft.office.interop.excel;
   public partial class uctaskgrid : usercontrol
   {
     public uctaskgrid()
     {
       initializecomponent();
     }
     private void uctaskgrid_load(object sender, eventargs e)
     {
       //load data
       system.data.datatable dt = sqlhelper.getdatetable("select * from act_id_user", null);
       this.datagridview.datasource = dt;
     }
     private void 保存stoolstripbutton_click(object sender, eventargs e)
     {
       //核心代码,获取当前的worksheet
       excel._worksheet ws = (excel._worksheet)globals.thisaddin.application.activeworkbook.activesheet;
       string name = ws.name;
       string id = ((string)(ws.cells[, ] as excel.range).value).tostring();
       string first = ((string)(ws.cells[, ] as excel.range).value).tostring();
       string last = ((string)(ws.cells[, ] as excel.range).value).tostring();
       string email = ((string)(ws.cells[, ] as excel.range).value).tostring();
       string sql = string.format("insert into act_id_user ([id_],[first_],[last_],[email_]) values('{}','{}','{}','{}')", id, first, last, email);
       int rows= sqlhelper.executenonquery(sqlhelper.connectionstringlocaltransaction, system.data.commandtype.text,sql,null);
       if (rows == )
       {
         system.windows.forms.messagebox.show("saved");
       }
       else
       {
         system.windows.forms.messagebox.show("error");
       }
     }
     private void 打开otoolstripbutton_click(object sender, eventargs e)
     {
       //refresh
       system.data.datatable dt = sqlhelper.getdatetable("select * from act_id_user", null);
       this.datagridview.datasource = dt;
     }
   }
 }

  5 add-in强签名

  通过设置程序的属性中的签名页,让vs自动生成一个签名即可(需设置密码)

C#定制Excel界面并实现与数据库交互的方法

三 最终效果演示

  为了直观的展示,看下面的动画:

C#定制Excel界面并实现与数据库交互的方法

四 猜想 excel service

  现在功能很强大的excel服务器,其中一个亮点就是在excel中进行界面设计和数据操作,然后就数据持久化到数据库中,那么我的猜想是,能不能通过addin的方式实现一个excel service功能呢,将界面设计序列化保存到数据库中,并给一个路径(唯一),但用户单击菜单(确定了路径)后将界面设计呈现到excel中,然后用户操作完成后,通过后台程序将数据库保存到数据库中.