C#定制Excel界面并实现与数据库交互的方法

 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;
       }
     }
   }
 }

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

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;
     }
   }
 }