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中,然后用户操作完成后,通过后台程序将数据库保存到数据库中.
相关文章
- 操作类就是把一些常用的一系列的数据库或相关操作写在一个类中,这样调用时我们只要调用类文件,如果要执行相关操作就直接调用类文件中的方法函数就可以实现了,下面整理了...2016-11-25
- 本文给大家分享C#连接SQL数据库和查询数据功能的操作技巧,本文通过图文并茂的形式给大家介绍的非常详细,需要的朋友参考下吧...2021-05-17
- 这篇文章主要介绍了C#从数据库读取图片并保存的方法,帮助大家更好的理解和使用c#,感兴趣的朋友可以了解下...2021-01-16
- 这篇文章主要介绍了Intellij IDEA连接Navicat数据库的方法,本文通过图文并茂的形式给大家介绍的非常详细,对大家的学习或工作具有一定的参考借价值,需要的朋友可以参考下...2021-03-25
- 这篇文章主要介绍了c++中system("pause")的作用和含义,非常不错,具有参考借鉴价值,需要的朋友参考下吧...2020-04-25
- 在开发过程中,我们经常会将日期时间的毫秒数存放到数据库,但是它对应的时间看起来就十分不方便,我们可以使用一些函数将毫秒转换成date格式。 一、 在MySQL中,有内置的函数from_unixtime()来做相应的转换,使用如下: 复制...2014-05-31
- C#使用System.IO中的文件操作方法在Windows系统中处理本地文件相当顺手,这里我们还总结了在Oracle中保存文件的方法,嗯,接下来就来看看整理的C#操作本地文件及保存文件到数据库的基本方法总结...2020-06-25
- 这篇文章主要为大家详细介绍了SpringBoot实现excel文件生成和下载,文中示例代码介绍的非常详细,具有一定的参考价值,感兴趣的小伙伴们可以参考一下...2021-02-09
- 通过内网连另外一台机器的mysql服务, 确发现速度N慢! 等了大约几十秒才等到提示输入密码。 但是ping mysql所在服务器却很快! 想到很久之前有过类似的经验, telnet等一些服务在连接请求的时候,会做一些反向域名解析(如果...2015-10-21
基于BootStrap Metronic开发框架经验小结【八】框架功能总体界面介绍
这篇文章主要介绍了基于BootStrap Metronic开发框架经验小结【八】框架功能总体界面介绍 的相关资料,需要的朋友可以参考下...2016-05-14- 某些时候,例如为了搭建一个测试环境,或者克隆一个网站,需要复制一个已存在的mysql数据库。使用以下方法,可以非常简单地实现。假设已经存在的数据库名字叫db1,想要复制一份,命名为newdb。步骤如下:1. 首先创建新的数据库newd...2015-10-21
- mysqldump命令的用法1、导出所有库系统命令行mysqldump -uusername -ppassword --all-databases > all.sql 2、导入所有库mysql命令行mysql>source all.sql; 3、导出某些库系统命令行mysqldump -uusername -ppassword...2015-10-21
- 这篇文章主要介绍了node.js如何操作MySQL数据库,帮助大家更好的进行web开发,感兴趣的朋友可以了解下...2020-10-29
- 1005:创建表失败1006:创建数据库失败1007:数据库已存在,创建数据库失败1008:数据库不存在,删除数据库失败1009:不能删除数据库文件导致删除数据库失败1010:不能删除数据目录导致删除数据库失败1011:删除数据库...2013-09-23
- 这篇文章主要介绍了c#读取excel方法,实例分析了C#读取excel文件的原理与相关技巧,需要的朋友可以参考下...2020-06-25
- 这篇文章主要介绍了C# 16 进制字符串转 int的方法,非常不错,具有参考借鉴价值,需要的朋友可以参考下...2020-06-25
InterlliJ IDEA2020新建java web项目找不到Static Web的解决
这篇文章主要介绍了InterlliJ IDEA2020新建java web项目找不到Static Web的解决,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小编来一起学习学习吧...2020-09-02- 这篇文章主要介绍了c#从数据库里取得数据并异步更新ui的方法,大家参考使用吧...2020-06-25
- 这篇文章主要介绍了Python导入数值型Excel数据并生成矩阵操作,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2020-06-09
- yii2.0框架是PHP开发的一个比较高效率的框架,集合了作者的大量心血,下面通过用户为例给大家详解yii2使用中的一些基本的增删改查操作。 User::find()->all(); //返回所有用户数据; User::findOne($id); //返回 主键...2015-11-24