最近做项目的时候用到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 //保存 www.2cto.com
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 }