#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
以测试的:
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; }