你的位置:首页 > ASP.net教程

[ASP.net教程]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 Open

    如果用户没有安装Excel应用程序,或者在服务器端需要动态生成Excel文件的时候。我们可能需要直接读取或者生成Excel文件,这种情况下,如果要对Excel文件进行各种定制化开发的话,建议使用Open

二 使用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的属性设置如下图所示:

  后台代码如下:

 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 RibbonAddIn10   {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 }

  3 ThisAddIn逻辑编写

 1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Text; 5 using System. 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[r, c]).NumberFormat = format; 67       ((Excel.Range)ws.Cells[r, c]).Value2 = "ID"; 68       ((Excel.Range)ws.Cells[r, c]).Interior.Color =System.Drawing. ColorTranslator.ToOle(System.Drawing.Color.Red); 69       //((Excel.Range)ws.Cells[r, c]).Style.Name = "Normal"; 70       ((Excel.Range)ws.Cells[r, c]).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[Excel.XlBordersIndex.xlEdgeBottom].LineStyle =Excel. XlLineStyle.xlContinuous; 83       border.Weight = 2d; 84       border[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous; 85       border[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous; 86       border[Excel.XlBordersIndex.xlEdgeRight].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     #endregion109   }110 }

 

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

  后台代码如下:

 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 ExcelAddInDemo11 {12   using Excel = Microsoft.Office.Interop.Excel;13   public partial class UCTaskGrid : UserControl14   {15     public UCTaskGrid()16     {17       InitializeComponent();18     }19 20     private void UCTaskGrid_Load(object sender, EventArgs e)21     {22       //load data23       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       //核心代码,获取当前的worksheet30       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 ([ID_],[FIRST_],[LAST_],[EMAIL_]) 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       else43       {44         System.Windows.Forms.MessageBox.Show("error");45       }46 47     }48 49     private void 打开OToolStripButton_Click(object sender, EventArgs e)50     {51       //refresh52       System.Data.DataTable dt = SqlHelper.getDateTable("select * from ACT_ID_USER", null);53       this.dataGridView1.DataSource = dt;54     }55   }56 }

  5 Add-In强签名

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

三 最终效果演示

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

四 猜想 Excel Service

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