#region DataGridView数据显示到Excel
02./// <summary>
03./// 打开Excel并将DataGridView控件中数据导出到Excel
04./// </summary>
05./// <param name="dgv">DataGridView对象 </param>
06./// <param name="isShowExcle">是否显示Excel界面 </param>
07./// <remarks>
08./// add com "Microsoft Excel 11.0 Object Library"
09./// using Excel=Microsoft.Office.Interop.Excel;
10./// </remarks>
11./// <returns> </returns>
12.public bool DataGridviewShowToExcel(DataGridView dgv, bool isShowExcle)
13.{
14. if (dgv.Rows.Count == 0)
15. return false;
16. //建立Excel对象
17. Excel.Application excel = new Excel.Application();
18. excel.Application.Workbooks.Add(true);
19. excel.Visible = isShowExcle;
20. //生成字段名称
21. for (int i = 0; i < dgv.ColumnCount; i++)
22. {
23. excel.Cells[1, i + 1] = dgv.Columns[i].HeaderText;
24. }
25. //填充数据
26. for (int i = 0; i < dgv.RowCount - 1; i++)
27. {
28. for (int j = 0; j < dgv.ColumnCount; j++)
29. {
30. if (dgv[j, i].ValueType == typeof(string))
31. {
32. excel.Cells[i + 2, j + 1] = "'" + dgv[j, i].Value.ToString();
33. }
34. else
35. {
36. excel.Cells[i + 2, j + 1] = dgv[j, i].Value.ToString();
37. }
38. }
39. }
40. return true;
41.}
42.#endregion
43.
44.#region DateGridView导出到csv格式的Excel
45./// <summary>
46./// 常用方法，列之间加/t，一行一行输出，此文件其实是csv文件，不过默认可以当成Excel打开。
47./// </summary>
48./// <remarks>
49./// using System.IO;
50./// </remarks>
51./// <param name="dgv"></param>
52.private void DataGridViewToExcel(DataGridView dgv)
53.{
54. SaveFileDialog dlg = new SaveFileDialog();
55. dlg.Filter = "Execl files (*.xls)|*.xls";
56. dlg.FilterIndex = 0;
57. dlg.RestoreDirectory = true;
58. dlg.CreatePrompt = true;
59. dlg.Title = "保存为Excel文件";
60.
61. if (dlg.ShowDialog() == DialogResult.OK)
62. {
63. Stream myStream;
64. myStream = dlg.OpenFile();
65. StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding(-0));
66. string columnTitle = "";
67. try
68. {
69. //写入列标题
70. for (int i = 0; i < dgv.ColumnCount; i++)
71. {
72. if (i > 0)
73. {
74. columnTitle += "/t";
75. }
76. columnTitle += dgv.Columns[i].HeaderText;
77. }
78. sw.WriteLine(columnTitle);
79.
80. //写入列内容
81. for (int j = 0; j < dgv.Rows.Count; j++)
82. {
83. string columnValue = "";
84. for (int k = 0; k < dgv.Columns.Count; k++)
85. {
86. if (k > 0)
87. {
88. columnValue += "/t";
89. }
90. if (dgv.Rows[j].Cells[k].Value == null)
91. columnValue += "";
92. else
93. columnValue += dgv.Rows[j].Cells[k].Value.ToString().Trim();
94. }
95. sw.WriteLine(columnValue);
96. }
97. sw.Close();
98. myStream.Close();
99. }
100. catch (Exception e)
101. {
102. MessageBox.Show(e.ToString());
103. }
104. finally
105. {
106. sw.Close();
107. myStream.Close();
108. }
109. }
110.}
111.#endregion
112.
113.#region DataGridView导出到Excel，有一定的判断性
114./// <summary>
115.///方法，导出DataGridView中的数据到Excel文件
116./// </summary>
117./// <remarks>
118./// add com "Microsoft Excel 11.0 Object Library"
119./// using Excel=Microsoft.Office.Interop.Excel;
120./// using System.Reflection;
121./// </remarks>
122./// <param name= "dgv"> DataGridView </param>
123.public static void DataGridViewToExcel(DataGridView dgv)
124.{
125.
126.
127. #region 验证可操作性
128.
129. //申明保存对话框
130. SaveFileDialog dlg = new SaveFileDialog();
131. //默然文件后缀
132. dlg.DefaultExt = "xls ";
133. //文件后缀列表
134. dlg.Filter = "EXCEL文件(*.XLS)|*.xls ";
135. //默然路径是系统当前路径
136. dlg.InitialDirectory = Directory.GetCurrentDirectory();
137. //打开保存对话框
138. if (dlg.ShowDialog() == DialogResult.Cancel) return;
139. //返回文件路径
140. string fileNameString = dlg.FileName;
141. //验证strFileName是否为空或值无效
142. if (fileNameString.Trim() == " ")
143. { return; }
144. //定义表格内数据的行数和列数
145. int rowscount = dgv.Rows.Count;
146. int colscount = dgv.Columns.Count;
147. //行数必须大于0
148. if (rowscount <= 0)
149. {
150. MessageBox.Show("没有数据可供保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
151. return;
152. }
153.
154. //列数必须大于0
155. if (colscount <= 0)
156. {
157. MessageBox.Show("没有数据可供保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
158. return;
159. }
160.
161. //行数不可以大于65536
162. if (rowscount > 65536)
163. {
164. MessageBox.Show("数据记录数太多(最多不能超过65536条)，不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
165. return;
166. }
167.
168. //列数不可以大于255
169. if (colscount > 255)
170. {
171. MessageBox.Show("数据记录行数太多，不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
172. return;
173. }
174.
175. //验证以fileNameString命名的文件是否存在，如果存在删除它
176. FileInfo file = new FileInfo(fileNameString);
177. if (file.Exists)
178. {
179. try
180. {
181. file.Delete();
182. }
183. catch (Exception error)
184. {
185. MessageBox.Show(error.Message, "删除失败 ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
186. return;
187. }
188. }
189. #endregion
190. Excel.Application objExcel = null;
191. Excel.Workbook objWorkbook = null;
192. Excel.Worksheet objsheet = null;
193. try
194. {
195. //申明对象
196. objExcel = new Microsoft.Office.Interop.Excel.Application();
197. objWorkbook = objExcel.Workbooks.Add(Missing.Value);
198. objsheet = (Excel.Worksheet)objWorkbook.ActiveSheet;
199. //设置EXCEL不可见
200. objExcel.Visible = false;
201.
202. //向Excel中写入表格的表头
203. int displayColumnsCount = 1;
204. for (int i = 0; i <= dgv.ColumnCount - 1; i++)
205. {
206. if (dgv.Columns[i].Visible == true)
207. {
208. objExcel.Cells[1, displayColumnsCount] = dgv.Columns[i].HeaderText.Trim();
209. displayColumnsCount++;
210. }
211. }
212. //设置进度条
213. //tempProgressBar.Refresh();
214. //tempProgressBar.Visible = true;
215. //tempProgressBar.Minimum=1;
216. //tempProgressBar.Maximum=dgv.RowCount;
217. //tempProgressBar.Step=1;
218. //向Excel中逐行逐列写入表格中的数据
219. for (int row = 0; row <= dgv.RowCount - 1; row++)
220. {
221. //tempProgressBar.PerformStep();
222.
223. displayColumnsCount = 1;
224. for (int col = 0; col < colscount; col++)
225. {
226. if (dgv.Columns[col].Visible == true)
227. {
228. try
229. {
230. objExcel.Cells[row + 2, displayColumnsCount] = dgv.Rows[row].Cells[col].Value.ToString().Trim();
231. displayColumnsCount++;
232. }
233. catch (Exception)
234. {
235.
236. }
237.
238. }
239. }
240. }
241. //隐藏进度条
242. //tempProgressBar.Visible = false;
243. //保存文件
244. objWorkbook.SaveAs(fileNameString, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
245. Missing.Value, Excel.XlSaveAsAccessMode.xlShared, Missing.Value, Missing.Value, Missing.Value,
246. Missing.Value, Missing.Value);
247. }
248. catch (Exception error)
249. {
250. MessageBox.Show(error.Message, "警告 ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
251. return;
252. }
253. finally
254. {
255. //关闭Excel应用
256. if (objWorkbook != null) objWorkbook.Close(Missing.Value, Missing.Value, Missing.Value);
257. if (objExcel.Workbooks != null) objExcel.Workbooks.Close();
258. if (objExcel != null) objExcel.Quit();
259.
260. objsheet = null;
261. objWorkbook = null;
262. objExcel = null;
263. }
264. MessageBox.Show(fileNameString + "/n/n导出完毕! ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
265.
266.}
267.
268.#endregion
269.
270.#region DataGridView数据显示到Excel
271./// <summary>
272./// 打开Excel并将DataGridView控件中数据导出到Excel
273./// </summary>
274./// <param name="dgv">DataGridView对象 </param>
275./// <param name="isShowExcle">是否显示Excel界面 </param>
276./// <remarks>
277./// add com "Microsoft Excel 11.0 Object Library"
278./// using Excel=Microsoft.Office.Interop.Excel;
279./// </remarks>
280./// <returns> </returns>
281.public bool DataGridviewShowToExcel(DataGridView dgv, bool isShowExcle)
282.{
283. if (dgv.Rows.Count == 0)
284. return false;
285. //建立Excel对象
286. Excel.Application excel = new Excel.Application();
287. excel.Application.Workbooks.Add(true);
288. excel.Visible = isShowExcle;
289. //生成字段名称
290. for (int i = 0; i < dgv.ColumnCount; i++)
291. {
292. excel.Cells[1, i + 1] = dgv.Columns[i].HeaderText;
293. }
294. //填充数据
295. for (int i = 0; i < dgv.RowCount - 1; i++)
296. {
297. for (int j = 0; j < dgv.ColumnCount; j++)
298. {
299. if (dgv[j, i].ValueType == typeof(string))
300. {
301. excel.Cells[i + 2, j + 1] = "'" + dgv[j, i].Value.ToString();
302. }
303. else
304. {
305. excel.Cells[i + 2, j + 1] = dgv[j, i].Value.ToString();
306. }
307. }
308. }
309. return true;
310.}
311.#endregion
312.
313.#region DateGridView导出到csv格式的Excel
314./// <summary>
315./// 常用方法，列之间加/t，一行一行输出，此文件其实是csv文件，不过默认可以当成Excel打开。
316./// </summary>
317./// <remarks>
318./// using System.IO;
319./// </remarks>
320./// <param name="dgv"></param>
321.private void DataGridViewToExcel(DataGridView dgv)
322.{
323. SaveFileDialog dlg = new SaveFileDialog();
324. dlg.Filter = "Execl files (*.xls)|*.xls";
325. dlg.FilterIndex = 0;
326. dlg.RestoreDirectory = true;
327. dlg.CreatePrompt = true;
328. dlg.Title = "保存为Excel文件";
329.
330. if (dlg.ShowDialog() == DialogResult.OK)
331. {
332. Stream myStream;
333. myStream = dlg.OpenFile();
334. StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding(-0));
335. string columnTitle = "";
336. try
337. {
338. //写入列标题
339. for (int i = 0; i < dgv.ColumnCount; i++)
340. {
341. if (i > 0)
342. {
343. columnTitle += "/t";
344. }
345. columnTitle += dgv.Columns[i].HeaderText;
346. }
347. sw.WriteLine(columnTitle);
348.
349. //写入列内容
350. for (int j = 0; j < dgv.Rows.Count; j++)
351. {
352. string columnValue = "";
353. for (int k = 0; k < dgv.Columns.Count; k++)
354. {
355. if (k > 0)
356. {
357. columnValue += "/t";
358. }
359. if (dgv.Rows[j].Cells[k].Value == null)
360. columnValue += "";
361. else
362. columnValue += dgv.Rows[j].Cells[k].Value.ToString().Trim();
363. }
364. sw.WriteLine(columnValue);
365. }
366. sw.Close();
367. myStream.Close();
368. }
369. catch (Exception e)
370. {
371. MessageBox.Show(e.ToString());
372. }
373. finally
374. {
375. sw.Close();
376. myStream.Close();
377. }
378. }
379.}
380.#endregion
381.
382.#region DataGridView导出到Excel，有一定的判断性
383./// <summary>
384.///方法，导出DataGridView中的数据到Excel文件
385./// </summary>
386./// <remarks>
387./// add com "Microsoft Excel 11.0 Object Library"
388./// using Excel=Microsoft.Office.Interop.Excel;
389./// using System.Reflection;
390./// </remarks>
391./// <param name= "dgv"> DataGridView </param>
392.public static void DataGridViewToExcel(DataGridView dgv)
393.{
394.
395.
396. #region 验证可操作性
397.
398. //申明保存对话框
399. SaveFileDialog dlg = new SaveFileDialog();
400. //默然文件后缀
401. dlg.DefaultExt = "xls ";
402. //文件后缀列表
403. dlg.Filter = "EXCEL文件(*.XLS)|*.xls ";
404. //默然路径是系统当前路径
405. dlg.InitialDirectory = Directory.GetCurrentDirectory();
406. //打开保存对话框
407. if (dlg.ShowDialog() == DialogResult.Cancel) return;
408. //返回文件路径
409. string fileNameString = dlg.FileName;
410. //验证strFileName是否为空或值无效
411. if (fileNameString.Trim() == " ")
412. { return; }
413. //定义表格内数据的行数和列数
414. int rowscount = dgv.Rows.Count;
415. int colscount = dgv.Columns.Count;
416. //行数必须大于0
417. if (rowscount <= 0)
418. {
419. MessageBox.Show("没有数据可供保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
420. return;
421. }
422.
423. //列数必须大于0
424. if (colscount <= 0)
425. {
426. MessageBox.Show("没有数据可供保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
427. return;
428. }
429.
430. //行数不可以大于65536
431. if (rowscount > 65536)
432. {
433. MessageBox.Show("数据记录数太多(最多不能超过65536条)，不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
434. return;
435. }
436.
437. //列数不可以大于255
438. if (colscount > 255)
439. {
440. MessageBox.Show("数据记录行数太多，不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
441. return;
442. }
443.
444. //验证以fileNameString命名的文件是否存在，如果存在删除它
445. FileInfo file = new FileInfo(fileNameString);
446. if (file.Exists)
447. {
448. try
449. {
450. file.Delete();
451. }
452. catch (Exception error)
453. {
454. MessageBox.Show(error.Message, "删除失败 ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
455. return;
456. }
457. }
458. #endregion
459. Excel.Application objExcel = null;
460. Excel.Workbook objWorkbook = null;
461. Excel.Worksheet objsheet = null;
462. try
463. {
464. //申明对象
465. objExcel = new Microsoft.Office.Interop.Excel.Application();
466. objWorkbook = objExcel.Workbooks.Add(Missing.Value);
467. objsheet = (Excel.Worksheet)objWorkbook.ActiveSheet;
468. //设置EXCEL不可见
469. objExcel.Visible = false;
470.
471. //向Excel中写入表格的表头
472. int displayColumnsCount = 1;
473. for (int i = 0; i <= dgv.ColumnCount - 1; i++)
474. {
475. if (dgv.Columns[i].Visible == true)
476. {
477. objExcel.Cells[1, displayColumnsCount] = dgv.Columns[i].HeaderText.Trim();
478. displayColumnsCount++;
479. }
480. }
481. //设置进度条
482. //tempProgressBar.Refresh();
483. //tempProgressBar.Visible = true;
484. //tempProgressBar.Minimum=1;
485. //tempProgressBar.Maximum=dgv.RowCount;
486. //tempProgressBar.Step=1;
487. //向Excel中逐行逐列写入表格中的数据
488. for (int row = 0; row <= dgv.RowCount - 1; row++)
489. {
490. //tempProgressBar.PerformStep();
491.
492. displayColumnsCount = 1;
493. for (int col = 0; col < colscount; col++)
494. {
495. if (dgv.Columns[col].Visible == true)
496. {
497. try
498. {
499. objExcel.Cells[row + 2, displayColumnsCount] = dgv.Rows[row].Cells[col].Value.ToString().Trim();
500. displayColumnsCount++;
501. }
502. catch (Exception)
503. {
504.
505. }
506.
507. }
508. }
509. }
510. //隐藏进度条
511. //tempProgressBar.Visible = false;
512. //保存文件
513. objWorkbook.SaveAs(fileNameString, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
514. Missing.Value, Excel.XlSaveAsAccessMode.xlShared, Missing.Value, Missing.Value, Missing.Value,
515. Missing.Value, Missing.Value);
516. }
517. catch (Exception error)
518. {
519. MessageBox.Show(error.Message, "警告 ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
520. return;
521. }
522. finally
523. {
524. //关闭Excel应用
525. if (objWorkbook != null) objWorkbook.Close(Missing.Value, Missing.Value, Missing.Value);
526. if (objExcel.Workbooks != null) objExcel.Workbooks.Close();
527. if (objExcel != null) objExcel.Quit();
528.
529. objsheet = null;
530. objWorkbook = null;
531. objExcel = null;
532. }
533. MessageBox.Show(fileNameString + "/n/n导出完毕! ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
534.
535.}
536.
537.#endregion
View Code
以测试的：
public bool GetDatagridview(DataGridView dv, bool isShowExcel)
{
if (dv.Rows.Count == 0)
{
return false;
}
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
excel.Application.Workbooks.Add(true);
excel.Visible = isShowExcel;
for (int i = 0; i < dv.ColumnCount; i++)
{
excel.Cells[1, i + 1] = dv.Columns[i].HeaderText;
}
for (int i = 0; i < dv.RowCount - 1; i++)
{
for (int j = 0; j < dv.ColumnCount; j++)
{
if (dv[j, i].ValueType == typeof(string))
{
excel.Cells[i + 2, j + 1] = "'" + dv[j, i].Value.ToString();
}
else
{
excel.Cells[i + 2, j + 1] = dv[j, i].Value.ToString();
}
}
}
return true;
}
View Code
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
