C#操作Excel
最近做项目的时候用到Excel编程这个模块，在用C#操作Excel之前要先把.net组件添加进来。
只要电脑上安装了office的都会有这个组件。
1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Text; 5 using Microsoft.Office.Interop.Excel; 6 using System.Reflection; 7 using System.Runtime.InteropServices; 8 9 namespace XmlTest 10 { 11 public class Excel 12 { 13 [DllImport("User32.dll", CharSet = CharSet.Auto)] 14 public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID); 15 16 //引用Excel Application类別 17 ApplicationClass _excel = null; 18 //活页簿 19 Workbook _wb = null; 20 //工作表 21 Worksheet _ws = null; 22 //Range用于操作单元格 23 Range _range = null; 24 Object missing = Missing.Value; 25 public Excel() 26 { 27 try 28 { 29 _excel = new ApplicationClass(); 30 _wb = OpenExcel("C:\\ExcelDemo.xls"); 31 //活页簿中工作表的数量 32 int count = _wb.Worksheets.Count; 33 //第一个工作表 34 _ws = (Worksheet)_wb.Worksheets[1]; 35 //修改工作表的名字 36 _ws.Name = "Jimmy"; 37 //获取使用到的单元格的行数 38 int rows = _ws.UsedRange.Rows.Count; 39 //获取使用到的单元格的行数 40 int columns = _ws.UsedRange.Columns.Count; 41 //获取指定的单元格 42 _range = (Microsoft.Office.Interop.Excel.Range)_ws.Cells[1, 1]; 43 //获取单元格的值 44 string value = _range.Text.ToString(); 45 //设置单元格的值 46 _range.Value = "Jimmy"; 47 //获取单元格的颜色的整数表达式 48 int color = Convert.ToInt32(_range.Interior.Color); 49 //设置颜色值为黑色 50 _range.Interior.Color = 1; 51 /* 52 53 无色 = -4142, 自动 = -4105, 黑色 = 1, 褐色 = 53, 橄榄 = 52, 深绿 = 51, 深青 = 49, 54 深蓝 = 11, 靛蓝 = 55, 灰色80 = 56, 深红 = 9, 橙色 = 46, 深黄 = 12, 绿色 = 10, 55 青色 = 14, 蓝色 = 5, 蓝灰 = 47, 灰色50 = 16, 红色 = 3, 浅橙色 = 45, 酸橙色 = 43, 56 海绿 = 50, 水绿色 = 42, 浅蓝 = 41, 紫罗兰 = 13, 灰色40 = 48, 粉红 = 7, 57 金色 = 44, 黄色 = 6, 鲜绿 = 4, 青绿 = 8, 天蓝 = 33, 梅红 = 54, 灰色25 = 15, 58 玫瑰红 = 38, 茶色 = 40, 浅黄 = 36, 浅绿 = 35, 浅青绿 = 34, 淡蓝 = 37, 淡紫 = 39, 59 白色 = 2 60 */ 61 62 //设置粗体 63 _range.Font.Bold = true; 64 //设置字体大小 65 _range.Font.Size = 12; 66 //水平对齐 67 _range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; 68 //垂直对齐 69 _range.VerticalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; 70 //公式设置 71 _range.FormulaR1C1 = "=AVERAGE(A1:C1)"; 72 //列宽 73 _range.ColumnWidth = 20; 74 //行高 75 _range.RowHeight = 20; 76 //给单元格加边框 77 _range.BorderAround(XlLineStyle.xlLineStyleNone, XlBorderWeight.xlThick, XlColorIndex.xlColorIndexAutomatic, System.Drawing.Color.Black.ToArgb()); 78 //单元格边框线类型(线型，虚线型) 79 _range.Borders.LineStyle = 1;//线型 80 81 //获取指定行 82 _range = (Microsoft.Office.Interop.Excel.Range)_ws.Cells[1, missing]; 83 //合并单元格 84 _range.Merge(missing); 85 //往指定的行下添加行 86 _range.Insert(Microsoft.Office.Interop.Excel.XlDirection.xlDown); 87 Workbook wbs = OpenExcel("D:\\1.xls"); 88 Worksheet wsh = (Worksheet)wbs.Worksheets[2]; 89 //将_ws复制到wsh的后一位 90 _ws.Copy(missing, wsh); 91 //保存 92 wbs.Save(); 93 94 //关闭 95 wbs.Close(false, missing, missing); 96 if (_excel != null) 97 { 98 Close(); 99 } 100 } 101 catch 102 { 103 if (_excel != null) 104 { 105 Close(); 106 } 107 } 108 } 109 /// <summary> 110 /// 打开excel 111 /// </summary> 112 /// <param name="docPath">文件路径</param> 113 /// <returns>活页簿</returns> 114 public Workbook OpenExcel(string docPath) 115 { 116 Workbook ew = _excel.Workbooks.Open(docPath.ToString(), missing, missing, missing, missing, 117 missing, missing, missing, 118 missing, missing, missing, 119 missing, missing, missing, 120 missing); 121 return ew; 122 } 123 124 /// <summary> 125 /// 往Excel中填数据 126 /// </summary> 127 /// <param name="filePth">文件路径</param> 128 /// <param name="fileName">文件名</param> 129 /// <param name="index">要操作的工作表在活页簿中的位置</param> 130 /// <param name="value">要填的数据</param> 131 /// <param name="row">单元格的行号</param> 132 /// <param name="column">单元格的列号</param> 133 public void SetTextRegion(string filePth, string fileName, int index, string value, int row, int column) 134 { 135 try 136 { 137 //打开一个Excel表 138 Workbook wb = OpenExcel(filePth + fileName); 139 //选择位置为index的工作表 140 Worksheet wsh = (Worksheet)wb.Worksheets[index]; 141 //往指定单元格中填数据 142 wsh.Cells[row, column] = value; 143 } 144 catch 145 { 146 return; 147 } 148 } 149 150 public void Close() 151 { 152 try 153 { 154 IntPtr t = new IntPtr(_excel.Hwnd); //杀死进程的好方法，很有效 155 int k = 0; 156 GetWindowThreadProcessId(t, out k); 157 System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k); 158 p.Kill(); 159 } 160 catch 161 { 162 163 } 164 } 165 } 166 }
