C#如何定制Excel界面并实现与数据库交互

趣玩斋主人 發表於 2015-11-25 09:33:00

C#如何定制Excel界面并实现与数据库交互

Excel是微软办公套装软件的一个重要的组成部分，它可以进行各种数据的处理、统计分析和辅助决策操作，广泛地应用于管理、统计财经、金融等众多领域。(另外,Excel还是伦敦一所会展中心的名称)。.NET可以创建Excel Add-In对Excel进行功能扩展,这些扩展的功能包括自定义用户函数,自定义UI,与数据库进行数据交互等。
<h3>一主要的Excel开发方式</h3>
　　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文件，而且兼容多个版本。
<h3>二使用Excel Add-In构建扩展</h3>
　　开发环境: 操作系统为Windows Server 2008R2 x64;Excel为Excel 2010 x64;开发工具为Visual Studio 2012旗舰版x64;数据库为SQL Server 2008R2 x64.
　　1 程序结构
　　用Visual Studio 2012新建一个ExcelAddInDemo的Excel Add-In项目,并添加若干文件,程序结构如下图:
<img src="https://images2015.cnblogs.com/blog/76497/201511/76497-20151125083622624-1038016384.jpg" alt="" width="384" height="523">
　　其中,RibbonAddIn可以定制2010的UI面板,SqlHelper.cs是一个简单的数据库访问帮助类,UClog.cs,UCPaneLeft.cs,UCTaskGrid.cs,UCTaskPane.cs都为添加的自定义控件,并通过程序添加到EXCEL界面中.运行起来的界面如下:
<img src="https://images2015.cnblogs.com/blog/76497/201511/76497-20151125084200687-1776706541.jpg" alt="" width="686" height="392">
　　程序可以通过在Excel界面中输入ID,First,Last,Email的值(对应标签的后一个单元格),单击用户列表面板上的保存按钮,将数据保存到数据库中.
　　2 RibbonAddIn设计
　　我们通过RibbonAddIn.cs给Excel的Ribbon添加了一个名为CUMT的插件.RibbonAddIn面板可以通过工具条控件方便的拖放到设计界面上.RibbonAddIn.cs的属性设置如下图所示:
<img src="https://images2015.cnblogs.com/blog/76497/201511/76497-20151125085924999-1829482906.jpg" alt="" width="665" height="279">
　　后台代码如下:
<div class="cnblogs_code">
<pre> 1 using System;
 2 using System.Collections.Generic;
 3 using System.Linq;
 4 using System.Text;
 5 using Microsoft.Office.Tools.Ribbon;
 6
 7 namespace ExcelAddInDemo
 8 {
 9 public partial class RibbonAddIn
10 {
11
12 private void RibbonAddIn_Load(object sender, RibbonUIEventArgs e)
13 {
14
15 }
16
17 private void btnAbout_Click(object sender, RibbonControlEventArgs e)
18 {
19 System.Windows.Forms.MessageBox.Show("JackWangCUMT!");
20 }
21
22 private void btnShow_Click(object sender, RibbonControlEventArgs e)
23 {
24 if (Globals.ThisAddIn._MyCustomTaskPane != null)
25 {
26 Globals.ThisAddIn._MyCustomTaskPane.Visible = true;
27 }
28 }
29
30 private void btnHide_Click(object sender, RibbonControlEventArgs e)
31 {
32 if (Globals.ThisAddIn._MyCustomTaskPane != null)
33 {
34 Globals.ThisAddIn._MyCustomTaskPane.Visible = false;
35 }
36 }
37 }
38 }</pre>
</div>
　　3 ThisAddIn逻辑编写
<div class="cnblogs_code">
<pre>1 using System;
2 using System.Collections.Generic;
3 using System.Linq;
4 using System.Text;
5 using System.Xml.Linq;
6 using Excel = Microsoft.Office.Interop.Excel;
7 namespace ExcelAddInDemo
8 {
9 using Microsoft.Office.Tools;
 10 public partial class ThisAddIn
 11 {
 12 public CustomTaskPane _MyCustomTaskPane = null;
 13
 14 private void ThisAddIn_Startup(object sender, System.EventArgs e)
 15 {
 16 UCTaskPane taskPane = new UCTaskPane();
 17 _MyCustomTaskPane = this.CustomTaskPanes.Add(taskPane, "我的任务面板");
 18 _MyCustomTaskPane.Width = 30;//height有问题，此处width ==height
 19 _MyCustomTaskPane.Visible = true;
 20 _MyCustomTaskPane.DockPosition = Microsoft.Office.Core.MsoCTPDockPosition.msoCTPDockPositionTop;
 21
 22 UCPaneLeft panLeft = new UCPaneLeft();
 23 _MyCustomTaskPane = this.CustomTaskPanes.Add(panLeft, "组织");
 24 _MyCustomTaskPane.Width = 200;
 25 _MyCustomTaskPane.Visible = true;
 26 _MyCustomTaskPane.DockPosition = Microsoft.Office.Core.MsoCTPDockPosition.msoCTPDockPositionLeft;
 27
 28 UCTaskGrid panRight = new UCTaskGrid();
 29 _MyCustomTaskPane = this.CustomTaskPanes.Add(panRight, "用户列表");
 30 _MyCustomTaskPane.Width = 200;
 31 _MyCustomTaskPane.Visible = true;
 32 _MyCustomTaskPane.DockPosition = Microsoft.Office.Core.MsoCTPDockPosition.msoCTPDockPositionRight;
 33
 34 UCLog panLog = new UCLog();
 35 _MyCustomTaskPane = this.CustomTaskPanes.Add(panLog, "日志列表");
 36 _MyCustomTaskPane.Width = 60;
 37 _MyCustomTaskPane.Visible = true;
 38 _MyCustomTaskPane.DockPosition = Microsoft.Office.Core.MsoCTPDockPosition.msoCTPDockPositionBottom;
 39
 40 //Hook into the workbook open event
 41 //This is because Office doesn't always have a document ready when this method is run 
 42 this.Application.WorkbookActivate += Application_WorkbookActivate;
 43 //test
 44 //this.Application.SheetSelectionChange += Application_SheetSelectionChange;
 45 }
 46
 47 void Application_SheetSelectionChange(object Sh, Excel.Range Target)
 48 {
 49 if (this.Application != null)
 50 {
 51 this.Application.Caption = this.Application.ActiveCell.Address.ToString();//$A$1
 52 //+ this.Application.ActiveCell.AddressLocal.ToString();//$A$1
 53 //this.Application.ActiveCell.Formula = "=sum(1+2)";
 54
 55 }
 56 }
 57
 58 void Application_WorkbookActivate(Excel.Workbook Wb)
 59 {
 60 //using Microsoft.Office.Tools.Excel 和 using Microsoft.Office.Interop.Excel 都有worksheet等,容易混淆
 61 //string path = this.Application.ActiveWorkbook.FullName;
 62 Excel._Worksheet ws = (Excel._Worksheet)this.Application.ActiveWorkbook.ActiveSheet;
 63 ws.Cells[2, 2] = "ID2";
 64 //如何设置只读等有待研究
 65 int r=2,c=2;
 66 //((Excel.Range)ws.Cells).NumberFormat = format;
 67 ((Excel.Range)ws.Cells).Value2 = "ID";
 68 ((Excel.Range)ws.Cells).Interior.Color =System.Drawing. ColorTranslator.ToOle(System.Drawing.Color.Red);
 69 //((Excel.Range)ws.Cells).Style.Name = "Normal";
 70 ((Excel.Range)ws.Cells).Style.Font.Bold = true;
 71
 72 #region format
 73 ((Microsoft.Office.Interop.Excel.Range)ws.get_Range("A2", "E10")).Font.Bold = true;
 74 ((Microsoft.Office.Interop.Excel.Range)ws.get_Range("A2", "E10")).Font.Italic = true;
 75 ((Microsoft.Office.Interop.Excel.Range)ws.get_Range("A2", "E10")).Font.Color = System.Drawing.Color.FromArgb(96, 32, 0).ToArgb();
 76 ((Microsoft.Office.Interop.Excel.Range)ws.get_Range("A2", "E10")).Font.Name = "Calibri";
 77 ((Microsoft.Office.Interop.Excel.Range)ws.get_Range("A2", "E10")).Font.Size = 15;
 78
 79 //border
 80 Excel.Range range = ((Microsoft.Office.Interop.Excel.Range)ws.get_Range("B2", "E3"));
 81 Excel. Borders border = range.Borders;
 82 border.LineStyle =Excel. XlLineStyle.xlContinuous;
 83 border.Weight = 2d;
 84 border.LineStyle = Excel.XlLineStyle.xlContinuous;
 85 border.LineStyle = Excel.XlLineStyle.xlContinuous;
 86 border.LineStyle = Excel.XlLineStyle.xlContinuous;
 87 #endregion
 88 ws.Cells[2, 4] = "First";
 89 ws.Cells[3, 2] = "Last";
 90 ws.Cells[3, 4] = "Email";
 91 }
 92 private void ThisAddIn_Shutdown(object sender, System.EventArgs e)
 93 {
 94 }
 95
 96 #region VSTO 生成的代码
 97
 98 /// <summary>
 99 /// 设计器支持所需的方法 - 不要
100 /// 使用代码编辑器修改此方法的内容。
101 /// </summary>
102 private void InternalStartup()
103 {
104 this.Startup += new System.EventHandler(ThisAddIn_Startup);
105 this.Shutdown += new System.EventHandler(ThisAddIn_Shutdown);
106 }
107
108 #endregion
109 }
110 }</pre>
</div>
 
　　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控件,其设计界面如下:
<img src="https://images2015.cnblogs.com/blog/76497/201511/76497-20151125091400999-1585149393.jpg" alt="" width="376" height="366">
　　后台代码如下:
<div class="cnblogs_code">
<pre> 1 using System;
 2 using System.Collections.Generic;
 3 using System.ComponentModel;
 4 using System.Drawing;
 5 using System.Data;
 6 using System.Linq;
 7 using System.Text;
 8 using System.Windows.Forms;
 9
10 namespace ExcelAddInDemo
11 {
12 using Excel = Microsoft.Office.Interop.Excel;
13 public partial class UCTaskGrid : UserControl
14 {
15 public UCTaskGrid()
16 {
17 InitializeComponent();
18 }
19
20 private void UCTaskGrid_Load(object sender, EventArgs e)
21 {
22 //load data
23 System.Data.DataTable dt = SqlHelper.getDateTable("select * from ACT_ID_USER", null);
24 this.dataGridView1.DataSource = dt;
25 }
26
27 private void 保存SToolStripButton_Click(object sender, EventArgs e)
28 {
29 //核心代码,获取当前的worksheet
30 Excel._Worksheet ws = (Excel._Worksheet)Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet;
31 string name = ws.Name;
32 string ID = ((string)(ws.Cells[2, 5] as Excel.Range).Value).ToString();
33 string First = ((string)(ws.Cells[2, 5] as Excel.Range).Value).ToString();
34 string Last = ((string)(ws.Cells[3, 3] as Excel.Range).Value).ToString();
35 string Email = ((string)(ws.Cells[3, 5] as Excel.Range).Value).ToString();
36 string sql = string.Format("insert into ACT_ID_USER (,,,) values('{0}','{1}','{2}','{3}')", ID, First, Last, Email);
37 int rows= SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringLocalTransaction, System.Data.CommandType.Text,sql,null);
38 if (rows == 1)
39 {
40 System.Windows.Forms.MessageBox.Show("saved");
41 }
42 else
43 {
44 System.Windows.Forms.MessageBox.Show("error");
45 }
46
47 }
48
49 private void 打开OToolStripButton_Click(object sender, EventArgs e)
50 {
51 //refresh
52 System.Data.DataTable dt = SqlHelper.getDateTable("select * from ACT_ID_USER", null);
53 this.dataGridView1.DataSource = dt;
54 }
55 }
56 }</pre>
</div>
　　5 Add-In强签名
　　通过设置程序的属性中的签名页,让VS自动生成一个签名即可(需设置密码)
<img src="https://images2015.cnblogs.com/blog/76497/201511/76497-20151125091740546-240725053.jpg" alt="" width="603" height="455">
<h3>三最终效果演示</h3>
　　为了直观的展示,看下面的动画:
<img src="https://images2015.cnblogs.com/blog/76497/201511/76497-20151125092351921-1036373514.gif" alt="" width="693" height="390">
<h3>四猜想 Excel Service</h3>
　　现在功能很强大的Excel服务器,其中一个亮点就是在Excel中进行界面设计和数据操作,然后就数据持久化到数据库中,那么我的猜想是,能不能通过AddIn的方式实现一个excel service功能呢,将界面设计序列化保存到数据库中,并给一个路径(唯一),但用户单击菜单(确定了路径)后将界面设计呈现到excel中,然后用户操作完成后,通过后台程序将数据保存到数据库中.
 

</div>
<div id="MySignature" role="contentinfo">
水平有限，望各位园友不吝赐教！如果觉得不错，请点击推荐和关注！
 
出处：http://www.cnblogs.com/isaboy/
 
声明：本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。 
来源：https://www.cnblogs.com/isaboy/p/excel_add-in.html

頁: [1]

琼殿技术论坛's Archiver

C#如何定制Excel界面并实现与数据库交互