DataGridView导出到Excel的三个方法

?

1. #region?DataGridView数据显示到Excel?????
2. ///?<summary>??????
3. ///?打开Excel并将DataGridView控件中数据导出到Excel?????
4. ///?</summary>??????
5. ///?<param?name="dgv">DataGridView对象?</param>??????
6. ///?<param?name="isShowExcle">是否显示Excel界面?</param>??????
7. ///?<remarks>?????
8. ///?add?com?"Microsoft?Excel?11.0?Object?Library"?????
9. ///?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. ????}?????
37. ????return?true;?????
38. }????
39. #endregion?????
40. ??
41. #region?DateGridView导出到csv格式的Excel?????
42. ///?<summary>?????
43. ///?常用方法，列之间加/t，一行一行输出，此文件其实是csv文件，不过默认可以当成Excel打开。?????
44. ///?</summary>?????
45. ///?<remarks>?????
46. ///?using?System.IO;?????
47. ///?<param?name="dgv"></param>?????
48. private?void?DataGridViewToExcel(DataGridView?dgv)?????
49. ????SaveFileDialog?dlg?=?new?SaveFileDialog();?????
50. ????dlg.Filter?=?"Execl?files?(*.xls)|*.xls";?????
51. ????dlg.FilterIndex?=?0;?????
52. ????dlg.RestoreDirectory?=?true;?????
53. ????dlg.CreatePrompt?=?true;?????
54. ????dlg.Title?=?"保存为Excel文件";?????
55. ????
56. ????if?(dlg.ShowDialog()?==?DialogResult.OK)?????
57. ????????Stream?myStream;?????
58. ????????myStream?=?dlg.OpenFile();?????
59. ????????StreamWriter?sw?=?new?StreamWriter(myStream,?System.Text.Encoding.GetEncoding(-0));?????
60. ????????string?columnTitle?=?"";?????
61. ????????try????
62. ????????{?????
63. ????????????//写入列标题?????
64. ????????????for?(int?i?=?0;?i?<?dgv.ColumnCount;?i++)?????
65. ????????????????if?(i?>?0)?????
66. ????????????????{?????
67. ????????????????????columnTitle?+=?"/t";?????
68. ????????????????}?????
69. ????????????????columnTitle?+=?dgv.Columns[i].HeaderText;?????
70. ????????????sw.WriteLine(columnTitle);?????
71. ????
72. ????????????//写入列内容?????
73. ????????????for?(int?j?=?0;?j?<?dgv.Rows.Count;?j++)?????
74. ????????????{?????
75. ????????????????string?columnValue?=?"";?????
76. ????????????????for?(int?k?=?0;?k?<?dgv.Columns.Count;?k++)?????
77. ????????????????????if?(k?>?0)?????
78. ????????????????????{?????
79. ????????????????????????columnValue?+=?"/t";?????
80. ????????????????????}?????
81. ????????????????????if?(dgv.Rows[j].Cells[k].Value?==?null)?????
82. ????????????????????????columnValue?+=?"";?????
83. ????????????????????else????
84. ????????????????????????columnValue?+=?dgv.Rows[j].Cells[k].Value.ToString().Trim();?????
85. ????????????????}?????
86. ????????????????sw.WriteLine(columnValue);?????
87. ????????????}?????
88. ????????????sw.Close();?????
89. ????????????myStream.Close();?????
90. ????????}?????
91. ????????catch?(Exception?e)?????
92. ????????????MessageBox.Show(e.ToString());?????
93. ????????finally????
94. ????????????sw.Close();?????
95. ????????????myStream.Close();?????
96. }?????
97. #endregion????
98. ??
99. #region?DataGridView导出到Excel，有一定的判断性?????
100. ///方法，导出DataGridView中的数据到Excel文件??????
101. ///?using?System.Reflection;?????
102. ///?</remarks>?????
103. ///?<param?name=?"dgv">?DataGridView?</param>??????
104. public?static?void?DataGridViewToExcel(DataGridView?dgv)?????
105. {????
106. ????#region???验证可操作性?????
107. //申明保存对话框??????
108. //默然文件后缀??????
109. ????dlg.DefaultExt?=?"xls?";?????
110. //文件后缀列表??????
111. ????dlg.Filter?=?"EXCEL文件(*.XLS)|*.xls?";?????
112. //默然路径是系统当前路径??????
113. ????dlg.InitialDirectory?=?Directory.GetCurrentDirectory();?????
114. //打开保存对话框??????
115. ????if?(dlg.ShowDialog()?==?DialogResult.Cancel)?return;?????
116. //返回文件路径??????
117. ????string?fileNameString?=?dlg.FileName;?????
118. //验证strFileName是否为空或值无效??????
119. ????if?(fileNameString.Trim()?==?"?")?????
120. ????{?return;?}?????
121. //定义表格内数据的行数和列数??????
122. ????int?rowscount?=?dgv.Rows.Count;?????
123. ????int?colscount?=?dgv.Columns.Count;?????
124. //行数必须大于0??????
125. ????if?(rowscount?<=?0)?????
126. ????????MessageBox.Show("没有数据可供保存?",?"提示?",?MessageBoxButtons.OK,?MessageBoxIcon.Information);?????
127. ????????return;?????
128. //列数必须大于0??????
129. ????if?(colscount?<=?0)?????
130. ????????MessageBox.Show("没有数据可供保存?",?MessageBoxIcon.Information);?????
131. ????????return;?????
132. //行数不可以大于65536??????
133. ????if?(rowscount?>?65536)?????
134. ????????MessageBox.Show("数据记录数太多(最多不能超过65536条)，不能保存?",0); background-color:inherit">//列数不可以大于255??????
135. ????if?(colscount?>?255)?????
136. ????????MessageBox.Show("数据记录行数太多，不能保存?",0); background-color:inherit">//验证以fileNameString命名的文件是否存在，如果存在删除它??????
137. ????FileInfo?file?=?new?FileInfo(fileNameString);?????
138. ????if?(file.Exists)?????
139. ????????????file.Delete();?????
140. ????????catch?(Exception?error)?????
141. ????????????MessageBox.Show(error.Message,?"删除失败?",?MessageBoxIcon.Warning);?????
142. ????????????return;?????
143. ????}????
144. ????#endregion?????
145. ????Excel.Application?objExcel?=?null;?????
146. ????Excel.Workbook?objWorkbook?=?null;?????
147. ????Excel.Worksheet?objsheet?=?null;?????
148. ????try????
149. ????????//申明对象??????
150. ????????objExcel?=?new?Microsoft.Office.Interop.Excel.Application();?????
151. ????????objWorkbook?=?objExcel.Workbooks.Add(Missing.Value);?????
152. ????????objsheet?=?(Excel.Worksheet)objWorkbook.ActiveSheet;?????
153. //设置EXCEL不可见??????
154. ????????objExcel.Visible?=?false;?????
155. ????????//向Excel中写入表格的表头??????
156. ????????int?displayColumnsCount?=?1;?????
157. ????????for?(int?i?=?0;?i?<=?dgv.ColumnCount?-?1;?i++)?????
158. ????????????if?(dgv.Columns[i].Visible?==?true)?????
159. ????????????????objExcel.Cells[1,?displayColumnsCount]?=?dgv.Columns[i].HeaderText.Trim();?????
160. ????????????????displayColumnsCount++;?????
161. //设置进度条??????
162. //tempProgressBar.Refresh();??????
163. //tempProgressBar.Visible???=???true;??????
164. //tempProgressBar.Minimum=1;??????
165. //tempProgressBar.Maximum=dgv.RowCount;??????
166. //tempProgressBar.Step=1;??????
167. //向Excel中逐行逐列写入表格中的数据??????
168. ????????for?(int?row?=?0;?row?<=?dgv.RowCount?-?1;?row++)?????
169. //tempProgressBar.PerformStep();??????
170. ????????????displayColumnsCount?=?1;?????
171. ????????????for?(int?col?=?0;?col?<?colscount;?col++)?????
172. ????????????????if?(dgv.Columns[col].Visible?==?true)?????
173. ????????????????????try????
174. ????????????????????????objExcel.Cells[row?+?2,?displayColumnsCount]?=?dgv.Rows[row].Cells[col].Value.ToString().Trim();?????
175. ????????????????????????displayColumnsCount++;?????
176. ????????????????????}?????
177. ????????????????????catch?(Exception)?????
178. ????????????????????{?????
179. //隐藏进度条??????
180. //tempProgressBar.Visible???=???false;??????
181. //保存文件??????
182. ????????objWorkbook.SaveAs(fileNameString,?Missing.Value,?????
183. ????????????????Missing.Value,?Excel.XlSaveAsAccessMode.xlShared,?????
184. ????????????????Missing.Value,?Missing.Value);?????
185. ????catch?(Exception?error)?????
186. ????????MessageBox.Show(error.Message,?"警告?",?MessageBoxIcon.Warning);?????
187. ????finally????
188. //关闭Excel应用??????
189. ????????if?(objWorkbook?!=?null)?objWorkbook.Close(Missing.Value,?Missing.Value);?????
190. ????????if?(objExcel.Workbooks?!=?null)?objExcel.Workbooks.Close();?????
191. ????????if?(objExcel?!=?null)?objExcel.Quit();?????
192. ????????objsheet?=?null;?????
193. ????????objWorkbook?=?null;?????
194. ????????objExcel?=?null;?????
195. ????MessageBox.Show(fileNameString?+?"/n/n导出完毕!?",248)"> }????
196. #endregion????
197. ?
198. #region?DataGridView数据显示到Excel??
199. ///?<summary>???
200. ///?打开Excel并将DataGridView控件中数据导出到Excel??
201. ///?</summary>???
202. ///?<param?name="dgv">DataGridView对象?</param>???
203. ///?<param?name="isShowExcle">是否显示Excel界面?</param>???
204. ///?<remarks>??
205. ///?add?com?"Microsoft?Excel?11.0?Object?Library"??
206. ///?using?Excel=Microsoft.Office.Interop.Excel;??
207. ///?</remarks>??
208. ///?<returns>?</returns>???
209. public?bool?DataGridviewShowToExcel(DataGridView?dgv,?bool?isShowExcle)??
210. {??
211. ????if?(dgv.Rows.Count?==?0)??
212. ????????return?false;??
213. //建立Excel对象???
214. ????Excel.Application?excel?=?new?Excel.Application();??
215. ????excel.Application.Workbooks.Add(true);??
216. ????excel.Visible?=?isShowExcle;??
217. //生成字段名称???
218. ????for?(int?i?=?0;?i?<?dgv.ColumnCount;?i++)??
219. ????{??
220. ????????excel.Cells[1,?i?+?1]?=?dgv.Columns[i].HeaderText;??
221. ????}??
222. //填充数据???
223. ????for?(int?i?=?0;?i?<?dgv.RowCount?-?1;?i++)??
224. ????{??
225. ????????for?(int?j?=?0;?j?<?dgv.ColumnCount;?j++)??
226. ????????{??
227. ????????????if?(dgv[j,?i].ValueType?==?typeof(string))??
228. ????????????{??
229. ????????????????excel.Cells[i?+?2,?i].Value.ToString();??
230. ????????????}??
231. ????????????else??
232. ????????}??
233. ????}??
234. ????return?true;??
235. }??
236. #endregion???
237. ?
238. #region?DateGridView导出到csv格式的Excel??
239. ///?<summary>??
240. ///?常用方法，列之间加/t，一行一行输出，此文件其实是csv文件，不过默认可以当成Excel打开。??
241. ///?</summary>??
242. ///?<remarks>??
243. ///?using?System.IO;??
244. ///?<param?name="dgv"></param>??
245. private?void?DataGridViewToExcel(DataGridView?dgv)??
246. ????SaveFileDialog?dlg?=?new?SaveFileDialog();??
247. ????dlg.Filter?=?"Execl?files?(*.xls)|*.xls";??
248. ????dlg.FilterIndex?=?0;??
249. ????dlg.RestoreDirectory?=?true;??
250. ????dlg.CreatePrompt?=?true;??
251. ????dlg.Title?=?"保存为Excel文件";??
252. ??
253. ????if?(dlg.ShowDialog()?==?DialogResult.OK)??
254. ????????Stream?myStream;??
255. ????????myStream?=?dlg.OpenFile();??
256. ????????StreamWriter?sw?=?new?StreamWriter(myStream,?System.Text.Encoding.GetEncoding(-0));??
257. ????????string?columnTitle?=?"";??
258. ????????try??
259. ????????{??
260. //写入列标题??
261. ????????????for?(int?i?=?0;?i?<?dgv.ColumnCount;?i++)??
262. ????????????????if?(i?>?0)??
263. ????????????????{??
264. ????????????????????columnTitle?+=?"/t";??
265. ????????????????}??
266. ????????????????columnTitle?+=?dgv.Columns[i].HeaderText;??
267. ????????????sw.WriteLine(columnTitle);??
268. ??
269. //写入列内容??
270. ????????????for?(int?j?=?0;?j?<?dgv.Rows.Count;?j++)??
271. ????????????{??
272. ????????????????string?columnValue?=?"";??
273. ????????????????for?(int?k?=?0;?k?<?dgv.Columns.Count;?k++)??
274. ????????????????????if?(k?>?0)??
275. ????????????????????{??
276. ????????????????????????columnValue?+=?"/t";??
277. ????????????????????}??
278. ????????????????????if?(dgv.Rows[j].Cells[k].Value?==?null)??
279. ????????????????????????columnValue?+=?"";??
280. ????????????????????else??
281. ????????????????????????columnValue?+=?dgv.Rows[j].Cells[k].Value.ToString().Trim();??
282. ????????????????}??
283. ????????????????sw.WriteLine(columnValue);??
284. ????????????}??
285. ????????????sw.Close();??
286. ????????????myStream.Close();??
287. ????????}??
288. ????????catch?(Exception?e)??
289. ????????????MessageBox.Show(e.ToString());??
290. ????????finally??
291. ????????????sw.Close();??
292. ????????????myStream.Close();??
293. }???
294. #endregion??
295. #region?DataGridView导出到Excel，有一定的判断性??
296. ///方法，导出DataGridView中的数据到Excel文件???
297. ///?using?System.Reflection;??
298. ///?</remarks>??
299. ///?<param?name=?"dgv">?DataGridView?</param>???
300. public?static?void?DataGridViewToExcel(DataGridView?dgv)??
301. {??
302. ????#region???验证可操作性??
303. //申明保存对话框???
304. //默然文件后缀???
305. ????dlg.DefaultExt?=?"xls?";??
306. //文件后缀列表???
307. ????dlg.Filter?=?"EXCEL文件(*.XLS)|*.xls?";??
308. //默然路径是系统当前路径???
309. ????dlg.InitialDirectory?=?Directory.GetCurrentDirectory();??
310. //打开保存对话框???
311. ????if?(dlg.ShowDialog()?==?DialogResult.Cancel)?return;??
312. //返回文件路径???
313. ????string?fileNameString?=?dlg.FileName;??
314. //验证strFileName是否为空或值无效???
315. ????if?(fileNameString.Trim()?==?"?")??
316. ????{?return;?}??
317. //定义表格内数据的行数和列数???
318. ????int?rowscount?=?dgv.Rows.Count;??
319. ????int?colscount?=?dgv.Columns.Count;??
320. //行数必须大于0???
321. ????if?(rowscount?<=?0)??
322. ????????return;??
323. //列数必须大于0???
324. ????if?(colscount?<=?0)??
325. ????????return;??
326. //行数不可以大于65536???
327. ????if?(rowscount?>?65536)??
328. ????????MessageBox.Show("数据记录数太多(最多不能超过65536条)，不能保存?",0); background-color:inherit">//列数不可以大于255???
329. ????if?(colscount?>?255)??
330. ????????MessageBox.Show("数据记录行数太多，不能保存?",0); background-color:inherit">//验证以fileNameString命名的文件是否存在，如果存在删除它???
331. ????FileInfo?file?=?new?FileInfo(fileNameString);??
332. ????if?(file.Exists)??
333. ????????????file.Delete();??
334. ????????catch?(Exception?error)??
335. ????????????MessageBox.Show(error.Message,?MessageBoxIcon.Warning);??
336. ????????????return;??
337. ????#endregion??
338. ????Excel.Application?objExcel?=?null;??
339. ????Excel.Workbook?objWorkbook?=?null;??
340. ????Excel.Worksheet?objsheet?=?null;??
341. ????try??
342. //申明对象???
343. ????????objExcel?=?new?Microsoft.Office.Interop.Excel.Application();??
344. ????????objWorkbook?=?objExcel.Workbooks.Add(Missing.Value);??
345. ????????objsheet?=?(Excel.Worksheet)objWorkbook.ActiveSheet;??
346. //设置EXCEL不可见???
347. ????????objExcel.Visible?=?false;??
348. //向Excel中写入表格的表头???
349. ????????int?displayColumnsCount?=?1;??
350. ????????for?(int?i?=?0;?i?<=?dgv.ColumnCount?-?1;?i++)??
351. ????????????if?(dgv.Columns[i].Visible?==?true)??
352. ????????????????objExcel.Cells[1,?displayColumnsCount]?=?dgv.Columns[i].HeaderText.Trim();??
353. ????????????????displayColumnsCount++;??
354. //设置进度条???
355. //tempProgressBar.Refresh();???
356. //tempProgressBar.Visible???=???true;???
357. //tempProgressBar.Minimum=1;???
358. //tempProgressBar.Maximum=dgv.RowCount;???
359. //tempProgressBar.Step=1;???
360. //向Excel中逐行逐列写入表格中的数据???
361. ????????for?(int?row?=?0;?row?<=?dgv.RowCount?-?1;?row++)??
362. //tempProgressBar.PerformStep();???
363. ????????????displayColumnsCount?=?1;??
364. ????????????for?(int?col?=?0;?col?<?colscount;?col++)??
365. ????????????????if?(dgv.Columns[col].Visible?==?true)??
366. ????????????????????try??
367. ????????????????????????objExcel.Cells[row?+?2,?displayColumnsCount]?=?dgv.Rows[row].Cells[col].Value.ToString().Trim();??
368. ????????????????????????displayColumnsCount++;??
369. ????????????????????}??
370. ????????????????????catch?(Exception)??
371. ????????????????????{??
372. //隐藏进度条???
373. //tempProgressBar.Visible???=???false;???
374. //保存文件???
375. ????????objWorkbook.SaveAs(fileNameString,??
376. ????catch?(Exception?error)??
377. ????????MessageBox.Show(error.Message,?MessageBoxIcon.Warning);??
378. ????finally??
379. //关闭Excel应用???
380. ????????if?(objWorkbook?!=?null)?objWorkbook.Close(Missing.Value,?Missing.Value);??
381. ????????if?(objExcel.Workbooks?!=?null)?objExcel.Workbooks.Close();??
382. ????????if?(objExcel?!=?null)?objExcel.Quit();??
383. ????????objsheet?=?null;??
384. ????????objWorkbook?=?null;??
385. ????????objExcel?=?null;??
386. ????MessageBox.Show(fileNameString?+?"/n/n导出完毕!?",153); list-style:decimal-leading-zero outside; color:inherit; line-height:18px; margin:0px!important; padding:0px 3px 0px 10px!important"> }??
387. #endregion??

（编辑：北几岛）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!