C# 开发excel addin插件的小积累

好吧，进园子这么长时间了，第一次写博文，欢迎拍砖！

关于excel的一些基本操作：

1、引用

开发excel插件，这两个引用是必不可少的。

using Microsoft.Office.Interop.Excel;
using Microsoft.Office.Tools.Ribbon;

2、 工作簿、工作表、单元格的操作

ApplicationClass application = new ApplicationClass(); //创建一个excel进程
Workbook wbook = Globals.ThisAddIn.Application.ActiveWorkbook; //当前活动workbook
Worksheet worksheet = (Worksheet)wbook.ActiveSheet; //当前活动sheet
Range range = (Range)worksheet.Application.Selection;//当前选中的cells

根据条件选择指定的sheet：

Worksheet worksheet = wbook.Worksheets["sheet1"];//获取名为sheet1的工作表
Worksheet worksheet = wbook.Worksheets[1];//获取第一个工作表

其他的操作：

workSheet.Range[workSheet.Cells[range.Row,1],workSheet.Cells[workSheet.UsedRange.Rows.Count,workSheet.UsedRange.Columns.Count]].Clear();//清除当前选定行以下的所有数据
workSheet.UsedRange.Rows.Count//已使用的行数
workSheet.UsedRange.Columns.Count//已使用的列数
worksheet.Range["A1"].Font.ColorIndex = 3;//将“A1”单元格字体设为红色
worksheet.Range["A1","B2"].Interior.ColorIndex = 3; //将“A1”到“B2”范围背景设为红色
int num = wss1.Range[wss1.Cells[1, Y1]].Find("").Row; //第一次出现空单元格的行数
worksheet.Cells["A1"].Rows.Hidden=true;//隐藏行
worksheet.Cells["A1"].Columns.Hidden=true;//隐藏列

range与cells的区别不大，最重要的事range的范围比cells大；

当然，用C#遍历所有单元格时若遇到空单元格，有可能会弹出“无法对null执行运行时绑定”，此时对单元格判断是否为空并用正则表达式匹配数字，然后比较大小的方法（将所有小于6的数字字体设为红色）：

string str = Convert.ToString(worksheet.Cells[i, j].value);
string regex = @"^[1-9]d*|0$";
if (str != null && Regex.IsMatch(str, regex))
{
     if (Convert.ToDouble(str) < 6)
     {
        worksheet.Cells[i, j].Font.ColorIndex = 3;
     }
}

3、 图表生成

(1)以下是生成柱状图的方法（绑定数据的两种方法，一种是连续绑定数据，数据是连续的；另一种是部分绑定，数据是不连续的）：

wbook.Charts.Add(Type.Missing); //添加一個页面
wbook.ActiveChart.ChartType = XlChartType.xlColumnClustered; //设置要显示的图表样式（簇状条形图）      
Range rang = worksheet.get_Range(strx + "," + stry);
wbook.ActiveChart.SetSourceData(rang, XlRowCol.xlColumns);//数据不连续
//wbook.ActiveChart.SetSourceData(ws.Range["A1",ws.Cells[ws.UsedRange.Rows.Count,ws.UsedRange.Columns.Count]], XlRowCol.xlRows);//绑定数据，数据是连续的
//wbook.ActiveChart.SetSourceData(ws.Range["A1","A40"], XlRowCol.xlColumns);//绑定数据，柱状图中图柱所代表的数据
//没有这个标题就出不来
wbook.ActiveChart.HasTitle = true;
//设置绘图区的数据标志（就是线形顶上出现值）显示出值来
wbook.ActiveChart.ApplyDataLabels(XlDataLabelsType.xlDataLabelsShowValue, false, false, false, false, false,true, false, false, false);
//后面一坨也是有用的，例如要显示饼图的百分比，就必须将倒数第三个false改为true
//设置Legend图例的位置和格式
wbook.ActiveChart.HasLegend = true;
wbook.ActiveChart.PlotArea.Width = 550; //设置绘图区宽度
wbook.ActiveChart.PlotArea.Top = 30;
wbook.ActiveChart.PlotArea.Height = 400; //设置绘图区高度
wbook.ActiveChart.PlotArea.Left = 20;
//表示图示画在SHEET1的，改成自己的SHEET名就好
wbook.ActiveChart.Location(XlChartLocation.xlLocationAsObject, aimWorksheet.Name);
//图形距离左上角的距离
//wbook.ActiveSheet.ChartObjects.count //当前表中chart的数量
wbook.ActiveChart.ChartArea.Top = (wbook.ActiveSheet.ChartObjects.count - 1) * 200 + wbook.ActiveSheet.ChartObjects.count * 20;
wbook.ActiveChart.ChartArea.Left = 20;
wbook.ActiveChart.ChartArea.Height = 200;
wbook.ActiveChart.ChartArea.Width = 600;
//x轴样式
Axis xAxis = (Axis)wbook.ActiveChart.Axes(XlAxisType.xlCategory, XlAxisGroup.xlPrimary);
//y轴样式
Axis yAxis = (Axis)wbook.ActiveChart.Axes(XlAxisType.xlValue, XlAxisGroup.xlPrimary);
//  xAxis.CategoryNames = ws.Range["B2", ws.Cells[ws.UsedRange.Rows.Count, 2]];//x轴标注信息绑定
xAxis.HasTitle = false;
xAxis.TickLabels.Font.Name = "宋体";
xAxis.TickLabels.Font.Size = 8;
yAxis.TickLabels.NumberFormat = "##元"; //可以通过格式指定y轴的显示方式
yAxis.TickLabels.Orientation = Microsoft.Office.Interop.Excel.XlTickLabelOrientation.xlTickLabelOrientationHorizontal;
//Y轴显示的方向,是水平还是垂直等
yAxis.TickLabels.Font.Size = 8;
yAxis.TickLabels.Font.Name = "宋体";
 //设置标题和坐标轴
wbook.ActiveChart.HasTitle = true;
wbook.ActiveChart.ChartTitle.Text = str4+"柱状图"; //将默认标题改为指定标题
wbook.ActiveChart.Axes(XlAxisType.xlCategory, XlAxisGroup.xlPrimary).HasTitle = true;
wbook.ActiveChart.Axes(XlAxisType.xlCategory, XlAxisGroup.xlPrimary).AxisTitle.Characters.Text = str3;//在x轴上显示横坐标
wbook.ActiveChart.Axes(XlAxisType.xlValue, XlAxisGroup.xlPrimary).HasTitle = true;
wbook.ActiveChart.Axes(XlAxisType.xlValue, XlAxisGroup.xlPrimary).AxisTitle.Characters.Text = str4;//在y轴上显示纵坐标

当然生成图表并不是结束，我们还可以对其进行修改（下面是对当前选中的图表数据进行修改，try部分是为了如果是饼形图修改数据不会报错）：

Range rang = swsheet.get_Range(str1 + "," + str2);
wbook.ActiveChart.SetSourceData(rang, XlRowCol.xlColumns);
//设置标题和坐标轴
wbook.ActiveChart.HasTitle = true;
wbook.ActiveChart.ChartTitle.Text = comboBox4.SelectedItem + "图"; //将默认标题改为指定标题
try
{
wbook.ActiveChart.Axes(XlAxisType.xlCategory, XlAxisGroup.xlPrimary).HasTitle = true;
wbook.ActiveChart.Axes(XlAxisType.xlCategory, XlAxisGroup.xlPrimary).AxisTitle.Characters.Text =
comboBox5.SelectedItem.ToString(); //在x轴上显示横坐标
wbook.ActiveChart.Axes(XlAxisType.xlValue, XlAxisGroup.xlPrimary).HasTitle = true;
wbook.ActiveChart.Axes(XlAxisType.xlValue, XlAxisGroup.xlPrimary).AxisTitle.Characters.Text =
comboBox1.SelectedItem.ToString(); //在y轴上显示纵坐标
}
catch (Exception exception)
{
}

还可以对图表的数据源进行筛选，筛选不是将无关数据删除，只是将其隐藏起来，隐藏数据的值和位置都是不变的：

swsheet.Range["A1",swsheet.Cells[swsheet.UsedRange.Rows.Count, swsheet.UsedRange.Columns.Count]].AutoFilter(x, “>1”, XlAutoFilterOperator.xlAnd, Type.Missing, true); //筛选选定区域第x列所有大于1的数据
wss1.Range[wss1.Cells[1, i], wss1.Cells[wss1.UsedRange.Rows.Count, i]].AdvancedFilter(XlFilterAction.xlFilterCopy, Type.Missing, wss1.Range["AZ1"], true);//将不重复数据写到Z列

(2)数据透视表

即水晶报表，将数据进行统计（下面进行多数据绑定中的两种方法：一个是pivotTable.AddFields可以绑定除数据求和项外的所有数据；若想求和项也绑定多数据，可以定义多个PivotField pivotField，因为一个sheet中只能有一个数据透视表）：

private void GeneratetPivot1(Worksheet worksheet, Workbook wbook, Worksheet wss1)
{
PivotCaches pivotCaches = wbook.PivotCaches();
PivotCache pivotCache = pivotCaches.Add(XlPivotTableSourceType.xlDatabase, worksheet.UsedRange);//可选择数据绑定
Range range = wss1.Range["A1", wss1.Cells[wss1.UsedRange.Rows.Count, wss1.UsedRange.Columns.Count]];//透视表起始位置
//创建数据透视表
PivotTable pivotTable = pivotCache.CreatePivotTable(range, wss1.Name, true,
XlPivotTableVersionList.xlPivotTableVersionCurrent);
pivotTable.AddFields("合同名称", Type.Missing, Type.Missing, true); //向数据透视表或数据透视图中添加行字段、列字段和页字段。
pivotTable.AddFields(Type.Missing, Type.Missing,"季度",  true);
pivotTable.AddFields( Type.Missing, "外协名称",Type.Missing, true);
pivotTable.AddFields( Type.Missing, Type.Missing,"推广项目", true);
PivotField pivotField;
//PivotField pivotField2;
// pivotField = (PivotField)pivotTable.PivotFields("季度"); //数据透视表的某一字段（左上）
// pivotField.Orientation = XlPivotFieldOrientation.xlPageField;
//pivotField = (PivotField)pivotTable.PivotFields("外协名称"); //数据透视表的某一字段（左上）
// pivotField.Orientation = XlPivotFieldOrientation.xlPageField;
//字段在指定的数据透视表中的位置xlHidden,xlRowField行,xlColumnField列,xlPageField筛选,xlDataField统计 
//pivotField = (PivotField)pivotTable.PivotFields(7); //数据透视表的某一字段（右上）
//pivotField.Orientation = XlPivotFieldOrientation.xlColumnField;
//pivotField = (PivotField)pivotTable.PivotFields(8); //数据透视表的某一字段（左下）
//pivotField.Orientation = XlPivotFieldOrientation.xlRowField;
pivotField = (PivotField)pivotTable.PivotFields("供应商评估（十分制）"); //数据透视表的某一字段（右下）
pivotField.Orientation = XlPivotFieldOrientation.xlDataField;
pivotField.Function = XlConsolidationFunction.xlAverage; //返回或设置对数据透视表字段汇总时所使用的函数
}

4、结束进程

结束进程可以结束指定进程，也可以结束所有进程：

private void KillExcelProceed()//结束所有进程
{
System.Diagnostics.Process[] ExcelProcesses;
ExcelProcesses = System.Diagnostics.Process.GetProcessesByName("EXCEL");
foreach (System.Diagnostics.Process IsProcedding in ExcelProcesses)
{
      if (IsProcedding.ProcessName == "EXCEL")
      {
          IsProcedding.Kill();
      }
}
}
[System.Runtime.InteropServices.DllImport("User32.dll", CharSet = System.Runtime.InteropServices.CharSet.Auto)]
private static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);
private void Kill(Application excel)//结束指定excel进程
{
IntPtr t = new IntPtr(excel.Hwnd); //得到这个句柄，具体作用是得到这块内存入口 
int k = 0;
GetWindowThreadProcessId(t, out k); //得到唯一标志k
System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k); //k的引用
p.Kill(); //关闭k
}

5、 数据写入

数据写入也有两种方法:

(1)第一种是直接写入,不过如果有空数据则有可能报错:

wsheet1.Cells[i, j].value = wsheet2.Cells[x, y].value;

(2)第二种是用copy函数将数据整体复制,相当于手动选择数据区域,复制到另一块区域:

public static void CopyData(Worksheet worksheet, Worksheet sheetsource)
        {
            int src = sheetsource.UsedRange.Rows.Count;
            int scc = sheetsource.UsedRange.Columns.Count;
            int drc = worksheet.UsedRange.Rows.Count;

            Range range = sheetsource.Range[sheetsource.Cells[3, 1], sheetsource.Cells[src, scc]];
            range.Copy(Type.Missing);//复制数据
            Range range1 = worksheet.Range[worksheet.Cells[drc+1, 1], worksheet.Cells[drc + src-1, scc]];
            worksheet.Paste(range1, false);//粘贴数据

        }

(3)数据排序

像第一种一条一条写入数据，有时可以将数据先排序然后导入，在特定时候可以节省很多不必要的操作，提高效率：

int a = workSheet.UsedRange.Rows.Count;
int b = workSheet.UsedRange.Columns.Count;
Range myRange = (Range) workSheet.Cells[1, num];
workSheet.Sort.SortFields.Clear();//清除绑定数据（不执行此操作则可能导致多次排序异常）
workSheet.Sort.SortFields.Add(myRange,XlSortOn.xlSortOnValues,XlSortOrder.xlAscending,Type.Missing,XlSortDataOption.xlSortNormal);
myRange = workSheet.Range[workSheet.Cells[1, 1], workSheet.Cells[a, b]];
workSheet.Sort.SetRange(myRange);
workSheet.Sort.Header = XlYesNoGuess.xlYes; //第一行或列是否包含头信息
workSheet.Sort.MatchCase = false; //区分大小写
workSheet.Sort.Orientation = XlSortOrientation.xlSortColumns; //行或列排序
workSheet.Sort.SortMethod = XlSortMethod.xlPinYin; //英文或中文排序
workSheet.Sort.Apply(); //执行排序

6、其他的一些操作

触发事件和禁止触发事件：

(1)触发事件（这是写在thisaddin_startup中的括号里面为触发函数，即当触发某一事件时，调用函数）:

Globals.ThisAddIn.Application.SheetSelectionChange += new Excel.AppEvents_SheetSelectionChangeEventHandler(Application_SheetSelectionChange);//点击单元格触发事件
Globals.ThisAddIn.Application.SheetChange += new Excel.AppEvents_SheetChangeEventHandler(Application_SheetChange);//修改内容触发事件
Globals.ThisAddIn.Application.SheetPivotTableUpdate += new Excel.AppEvents_SheetPivotTableUpdateEventHandler(Application_SheetPivotTableUpdate);//数据透视表更新触发事件
Globals.ThisAddIn.Application.SheetCalculate += new Excel.AppEvents_SheetCalculateEventHandler(Application_SheetCalculate);//对工作表进行重新计算之后
Globals.ThisAddIn.Application.SheetBeforeRightClick += new Excel.AppEvents_SheetBeforeRightClickEventHandler(Application_SheetBeforeRightClick); //右键单击工作表时触发事件
Globals.ThisAddIn.Application.SheetFollowHyperlink+= new Excel.AppEvents_SheetFollowHyperlinkEventHandler(Application_SheetFollowHyperlink );//单击工作表上的任意超链接时
Globals.ThisAddIn.Application.SheetActivate += new Excel.AppEvents_SheetActivateEventHandler(Application_SheetActivate);//激活工作表
void Application_SheetSelectionChange(object sender, Excel.Range range){}//点击单元格触发事件函数
void Application_SheetActivate(object sender){}//激活工作表函数

(2)禁止触发事件（触发事件是全局的，所以在特定时刻不需要触发事件时要将其禁止）:

Globals.ThisAddIn.Application.ScreenUpdating = false; //防止屏幕闪动
Globals.ThisAddIn.Application.EnableEvents = false;//禁止触发事件
Globals.ThisAddIn.Application.DisplayAlerts = false;//禁弹窗
Globals.ThisAddIn.Application.DisplayAlerts = true;//启弹窗
Globals.ThisAddIn.Application.ScreenUpdating = true; //启动屏幕闪动
Globals.ThisAddIn.Application.EnableEvents = true;//启动触发事件

7、  错误

(1)     在开发过程中，不可避免会出现一些bug和错误，调试运行的时候导致进程卡死的情况也有可能，次数多了，excel会自动禁用你调试出错的加载项。解决方法是：文件——选项，在弹出框最下方中间偏左下拉框选择“禁用项目”，点击“转到”，将被禁用的加载项启用，然后在“开发工具”选项卡点击“com加载项”，勾选你的项目名称，确定，加载项就出来了，又可以一起快乐的玩耍了！

还有“异常来自 HRESULT:0x800A03EC”这种错误，一般是代码有错误，具体原因视情况而定。（已遇到因为从0开始循环抛异常和因为透视表表格格式不同而使用pivotField.PivotFilters.Add时抛异常）

(2)     在卸载旧的addin时可能会未卸载干净，这就需要在注册表中删除，注册表位置：HKEY_CURRENT_USERSoftwareMicrosoftOfficeExcelAddins

8、参考

浅谈Excel开发：三 Excel 对象模型：

http://www.cnblogs.com/yangecnu/p/3247234.html

C#开发Excel报表系列整理：

http://www.360doc.com/content/07/0824/10/12027_691547.shtml

VSTO学习归结：

http://club.excelhome.net/forum.php?mod=viewthread&tid=896478&extra=page%3D1

工作簿工作表所有事件一览表：

http://club.excelhome.net/thread-71744-1-1.html