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中,然后用户操作完成后,通过后台程序将数据库保存到数据库中.