C#对Excel的一些操作
最近组长给了个任务，需求需要对Excel进行导出及对Excel的单元格合并、设置样式等
网上代码复制粘帖了不少，删删改改，以下是总结出来的一些正对EXCEL的方法
DataGridView导出EXCEL
1 /// <summary>
2 /// DataGridView导出EXCEL
3 /// </summary>
4 /// <param name="fileNameString">要保存的文件路径</param>
5 /// <param name="dgvCAPA">DataGridView</param>
6 private void dgvToExcel(string fileNameString, DataGridView dgvCAPA)
7 {
8 //定义表格内数据的行数和列数
9 int rowscount = dgvCAPA.Rows.Count;
10 int colscount = dgvCAPA.Columns.Count;
11 //行数必须大于0
12 if (rowscount <= 0)
13 {
14 MessageBox.Show("没有数据可供保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
15 return;
16 }
17 //列数必须大于0
18 if (colscount <= 0)
19 {
20 MessageBox.Show("没有数据可供保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
21 return;
22 }
23 //行数不可以大于65536
24 if (rowscount > 65536)
25 {
26 MessageBox.Show("数据记录数太多(最多不能超过65536条)，不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
27 return;
28 }
29 //列数不可以大于255
30 if (colscount > 255)
31 {
32 MessageBox.Show("数据记录行数太多，不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
33 return;
34 }
35 //验证以fileNameString命名的文件是否存在，如果存在删除它
36 FileInfo file = new FileInfo(fileNameString);
37 if (file.Exists)
38 {
39 try
40 {
41 file.Delete();
42 }
43 catch (Exception error)
44 {
45 MessageBox.Show(error.Message, "删除失败 ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
46 return;
47 }
48 }
49 Microsoft.Office.Interop.Excel.Application objExcel = null;
50 Microsoft.Office.Interop.Excel.Workbook objWorkbook = null;
51 Microsoft.Office.Interop.Excel.Worksheet objsheet = null;
52 try
53 {
54 //申明对象
55 objExcel = new Microsoft.Office.Interop.Excel.Application();
56 objWorkbook = objExcel.Workbooks.Add(Missing.Value);
57 objsheet = (Microsoft.Office.Interop.Excel.Worksheet)objWorkbook.ActiveSheet;
58 //设置EXCEL不可见
59 objExcel.Visible = false;
60
61 //向Excel中写入表格的表头
62 int displayColumnsCount = 1;
63 for (int i = 0; i <= dgvCAPA.ColumnCount - 1; i++)
64 {
65 if (dgvCAPA.Columns[i].Visible == true)
66 {
67 objExcel.Cells[1, displayColumnsCount] = dgvCAPA.Columns[i].HeaderText.Trim();
68 displayColumnsCount++;
69 }
70 }
71
72 //向Excel中逐行逐列写入表格中的数据
73 for (int row = 0; row <= dgvCAPA.RowCount - 1; row++)
74 {
75 //tempProgressBar.PerformStep();
76 displayColumnsCount = 1;
77 for (int col = 0; col < colscount; col++)
78 {
79 if (dgvCAPA.Columns[col].Visible == true)
80 {
81 try
82 {
83
84 objExcel.Cells[row + 2, displayColumnsCount] = dgvCAPA.Rows[row].Cells[col].Value.ToString().Trim();
85 displayColumnsCount++;
86 }
87 catch (Exception)
88 {
89 }
90 }
91 }
92 }
93
94
95 //保存文件
96 objWorkbook.SaveAs(fileNameString, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
97 Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared, Missing.Value, Missing.Value, Missing.Value,
98 Missing.Value, Missing.Value);
99 }
100 catch (Exception error)
101 {
102 MessageBox.Show(error.Message, "警告 ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
103 return;
104 }
105 finally
106 {
107 //关闭Excel应用
108 if (objWorkbook != null) objWorkbook.Close(Missing.Value, Missing.Value, Missing.Value);
109 if (objExcel.Workbooks != null) objExcel.Workbooks.Close();
110 if (objExcel != null) objExcel.Quit();
111
112 objsheet = null;
113 objWorkbook = null;
114 objExcel = null;
115 GC.Collect();
116 }
117 }
合并指定EXCEL的单元格
1 /// <summary>
2 /// 合并指定EXCEL的单元格
3 /// </summary>
4 /// <param name="mySheet">指定的EXCEL工作表</param>
5 /// <param name="startLine">起始行</param>
6 /// <param name="recCount">总行数</param>
7 /// <param name="col">要合并的列</param>
8 private void MergeCell_Second(ref Microsoft.Office.Interop.Excel.Worksheet mySheet, int startLine, int recCount, string col)
9 {
10 string qy1 = mySheet.get_Range(col + startLine.ToString(), col + startLine.ToString()).Text.ToString();//获得起始行合并列单元格的填充内容
11
12 Microsoft.Office.Interop.Excel.Range rg1;
13 string strtemp = "";
14 bool endCycle = false;
15
16 //从起始行到终止行做循环
17 for (int i = startLine; i <= recCount + startLine - 1 && !endCycle; )
18 {
19 for (int j = i + 1; j <= recCount + startLine - 1; j++)
20 {
21 rg1 = mySheet.get_Range(col + j.ToString(), col + j.ToString());//获得下一行的填充内容
22 strtemp = rg1.Text.ToString().Trim();
23
24 if (strtemp.Trim() == qy1.Trim())//内容等于初始内容
25 {
26 rg1 = mySheet.get_Range(col + i.ToString(), col + j.ToString());//选取上条合并位置和当前行的合并区域
27 rg1.ClearContents();//清空要合并的区域
28 rg1.MergeCells = true;
29 if (col == "A")
30 mySheet.Cells[i, 1] = qy1;
31 else if (col == "B")
32 mySheet.Cells[i, 2] = qy1;
33
34 if (j == recCount + startLine - 1)
35 {
36 endCycle = true;
37 }
38 }
39 else//内容不等于初始内容
40 {
41 i = j;//i获取新值
42 qy1 = mySheet.get_Range(col + j.ToString(), col + j.ToString()).Text.ToString();
43 break;
44 }
45 }
46 }
47 }
一些对EXCEL的常用操作
1 //合併B列的相同字段單元格
2 MergeCell_Second(ref objsheet, 1, rowscount + 1, "B");
3 //刪除第一列
4 ((Microsoft.Office.Interop.Excel.Range)objsheet.Cells[1, 1]).Select();
5 ((Microsoft.Office.Interop.Excel.Range)objsheet.Cells[1, 1]).EntireColumn.Delete(0);
6 //設置樣式
7 //objExcel.get_Range("A1", "H1").Font.ColorIndex = 23;
8 objExcel.get_Range("A1", "H1").Interior.ColorIndex = 15;
9 objExcel.get_Range("A1", "H1").Borders.LineStyle = Microsoft.Office.Interop.Excel.XlBorderWeight.xlHairline;
10 //獲取某單元格值
11 objExcel.get_Range("B" + count, "B" + count).Value2.ToString()
