C#定制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项目,并添加若干文件,程序结构如下图:

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

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

　　2 ribbonaddin设计

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

　　后台代码如下:

 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控件,其设计界面如下:

　　后台代码如下:

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自动生成一个签名即可(需设置密码)

三 最终效果演示

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

四 猜想 excel service

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