[一篮饭特稀原创，转载请注明出自http://www.cnblogs.com/wanghafan/p/3228240.html]
1 using System;
2 using System.Data;
3 using System.IO;
4 using System.Windows.Forms;
5 using Excel = Microsoft.Office.Interop.Excel;
6 using System.Collections.Generic;
7
8 namespace PlaneExcel
9 {
10 static class Export2ExcelAction
11 {
12 /// <summary>
13 /// 单表
14 /// </summary>
15 /// <param name="FileName"></param>
16 /// <param name="dataTable"></param>
17 /// <param name="SheetName"></param>
18 /// <param name="form"></param>
19 public static void Export2Excel(string FileName, DataTable dataTable, string SheetName, System.Windows.Forms.Form form)
20 {
21 Excel.Application excelApplication = null;
22 try
23 {
24 excelApplication = new Excel.Application();
25 }
26 catch (Exception ex)
27 {
28 System.Windows.Forms.MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel...");
29 return;
30 }
31 excelApplication.DisplayAlerts = false;
32 Excel.Workbooks workbooks = excelApplication.Workbooks;
33 Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);//建工作簿
34 Excel.Worksheet TableASheet = (Excel.Worksheet)workbook.Worksheets.Add(Type.Missing, Type.Missing, 1, Type.Missing);
35 TableASheet.Name = SheetName;
36 TableASheet.Cells.Borders.LineStyle = Excel.XlLineStyle.xlLineStyleNone;
37 Excel.Range range = TableASheet.get_Range("A1", Type.Missing);
38 int ColumnIndex = 1;
39 foreach (DataColumn dc in dataTable.Columns)
40 {
41 range = TableASheet.get_Range(NunberToChar(ColumnIndex) + 1, Type.Missing);
42 range.Value2 = dc.ColumnName;
43 ColumnIndex++;
44 }
45 int RowIndex = 2;
46 foreach (DataRow dr in dataTable.Rows)
47 {
48 ColumnIndex = 1;
49 foreach (DataColumn dc in dataTable.Columns)
50 {
51 range = TableASheet.get_Range(NunberToChar(ColumnIndex) + RowIndex, Type.Missing);
52 range.Value2 = dr[dc.ColumnName].ToString();
53 ColumnIndex++;
54 }
55 RowIndex++;
56 }
57 try
58 {
59 ((Excel.Worksheet)workbook.Sheets.Item["Sheet1"]).Delete();
60 workbook.Saved = true;
61 workbook.SaveCopyAs(FileName);
62 }
63 catch (FileNotFoundException fileEx)
64 {
65 System.Windows.Forms.MessageBox.Show("请查看当前导出的文件是否存在或者正在运行中,
请重新尝试!", "错误", MessageBoxButtons.OKCancel, MessageBoxIcon.Error);
66 return;
67 }
68 catch (Exception ex)
69 {
70 System.Windows.Forms.MessageBox.Show("请查看当前导出的文件是否存在或者正在运行中,
请重新尝试!", "错误", MessageBoxButtons.OKCancel, MessageBoxIcon.Error);
71 return;
72 }
73 finally
74 {
75 workbook.Close();
76 workbooks.Close();
77 excelApplication.Quit();
78 System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
79 System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks);
80 System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApplication);
81 GC.Collect();
82 }
83
84 if (MessageBox.Show("导出成功，是否立即打开?", "导出结果", MessageBoxButtons.OKCancel, MessageBoxIcon.Question, MessageBoxDefaultButton.Button1) == DialogResult.OK)
85 System.Diagnostics.Process.Start(FileName);
86 }
87 /// <summary>
88 /// 多表
89 /// </summary>
90 /// <param name="FileName"></param>
91 /// <param name="li_dt"></param>
92 /// <param name="li_SheetName"></param>
93 /// <param name="form"></param>
94 public static void Export2Excel(string FileName, List<DataTable> li_dt, List<string> li_SheetName, System.Windows.Forms.Form form)
95 {
96 Excel.Application excelApplication = null;
97 try
98 {
99 excelApplication = new Excel.Application();
100 }
101 catch (Exception ex)
102 {
103 System.Windows.Forms.MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel...");
104 return;
105 }
106 excelApplication.DisplayAlerts = false;
107 Excel.Workbooks workbooks = excelApplication.Workbooks;
108 Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
109 for (int i = 0; i < li_dt.Count; i++)
110 {
111 Excel.Worksheet TableASheet = (Excel.Worksheet)workbook.Worksheets.Add(Type.Missing, Type.Missing, 1, Type.Missing);
112 TableASheet.Name = li_SheetName[i];
113 TableASheet.Cells.Borders.LineStyle = Excel.XlLineStyle.xlLineStyleNone;
114 Excel.Range range = TableASheet.get_Range("A1", Type.Missing);
115 int ColumnIndex = 1;
116 foreach (DataColumn dc in li_dt[i].Columns)
117 {
118 range = TableASheet.get_Range(NunberToChar(ColumnIndex) + 1, Type.Missing);
119 range.Value2 = dc.ColumnName;
120 ColumnIndex++;
121 }
122 int RowIndex = 2;
123 foreach (DataRow dr in li_dt[i].Rows)
124 {
125 ColumnIndex = 1;
126 foreach (DataColumn dc in li_dt[i].Columns)
127 {
128 range = TableASheet.get_Range(NunberToChar(ColumnIndex) + RowIndex, Type.Missing);
129 range.Value2 = dr[dc.ColumnName].ToString();
130 ColumnIndex++;
131 }
132 RowIndex++;
133 }
134 }
135 try
136 {
137 ((Excel.Worksheet)workbook.Sheets.Item["Sheet1"]).Delete();
138 workbook.Saved = true;
139 workbook.SaveCopyAs(FileName);
140 }
141 catch (FileNotFoundException fileEx)
142 {
143 System.Windows.Forms.MessageBox.Show("请查看当前导出的文件是否存在或者正在运行中,
请重新尝试!", "错误", MessageBoxButtons.OKCancel, MessageBoxIcon.Error);
144 return;
145 }
146 catch (Exception ex)
147 {
148 System.Windows.Forms.MessageBox.Show("请查看当前导出的文件是否存在或者正在运行中,
请重新尝试!", "错误", MessageBoxButtons.OKCancel, MessageBoxIcon.Error);
149 return;
150 }
151 finally
152 {
153 workbook.Close();
154 workbooks.Close();
155 excelApplication.Quit();
156 System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
157 System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks);
158 System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApplication);
159 GC.Collect();
160 }
161
162 if (MessageBox.Show("导出成功，是否立即打开?", "导出结果", MessageBoxButtons.OKCancel, MessageBoxIcon.Question, MessageBoxDefaultButton.Button1) == DialogResult.OK)
163 System.Diagnostics.Process.Start(FileName);
164 }
165 public static string NunberToChar(int number)
166 {
167 if (1 <= number && 26 >= number)
168 {
169 int num = number + 64;
170 System.Text.ASCIIEncoding asciiEncoding = new System.Text.ASCIIEncoding();
171 byte[] btNumber = new byte[] { (byte)num };
172 return asciiEncoding.GetString(btNumber);
173 }
174 else if (number > 26)
175 {
176 int leftValue = 0;
177 int rightValue = 0;
178 leftValue = number / 26;
179 rightValue = number % 26;
180
181 if (number % 26 == 0)
182 {
183 leftValue = leftValue - 1;
184 rightValue = ((number - 1) % 26) + 1;
185 }
186 return NunberToChar(leftValue) + NunberToChar(rightValue);
187 }
188 return string.Empty;
189 }
190
191 }
192 }