【问题】
C#的DataGridView中已经有了数据了:
接下来,想要将这些数据,导出为excel。
其中要注意的是,最后一列为DataGridViewButtonColumn,每个cell都是DataGridViewButtonCell。
【解决过程】
1.参考:
玩转DataGridView之将数据导出成Excel和Word格式
How to export DataGridView to excel file
去试试。
2.先去导入dll库:Microsoft.Office.Interop.Excel.dl
找了下,才在.NET下找到这个:
3.然后再去写代码:
using Excel = Microsoft.Office.Interop.Excel; private void btnSaveAll_Click(object sender, EventArgs e) { Excel.Application xlApp = new Excel.Application(); Excel.Workbook xlWorkBook; Excel.Worksheet xlWorkSheet; object misValue = System.Reflection.Missing.Value; xlApp = new Excel.ApplicationClass(); xlWorkBook = xlApp.Workbooks.Add(misValue); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); int i = 0; int j = 0; for (i = 0; i <= dgvSearchResult.RowCount - 1; i++) { for (j = 0; j <= dgvSearchResult.ColumnCount - 1; j++) { DataGridViewCell cell = dgvSearchResult[j, i]; if (j == girUrlColumnIdx) { xlWorkSheet.Cells[i + 1, j + 1] = cell.Tag.ToString(); } else { xlWorkSheet.Cells[i + 1, j + 1] = cell.Value; } } } //xlWorkBook.SaveAs("csharp.net-informations.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); xlWorkBook.SaveAs("fiverrComScrapedResult.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); releaseObject(xlWorkSheet); releaseObject(xlWorkBook); releaseObject(xlApp); MessageBox.Show("Excel file created , you can find the file c:\\csharp.net-informations.xls"); } private void releaseObject(object obj) { try { System.Runtime.InteropServices.Marshal.ReleaseComObject(obj); obj = null; } catch (Exception ex) { obj = null; MessageBox.Show("Exception Occured while releasing object " + ex.ToString()); } finally { GC.Collect(); } }
结果是,代码运行正常,但是却在当前的程序目录下,找不到所保存的excel文件。
4.参考:
去试试文件的绝对路径:
xlWorkBook.SaveAs("c;\\fiverrComScrapedResult.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
结果竟然出错了:
很明显,之前一直是把excel文件保存到
C:\Users\CLi\Documents
中的。
所以,就去看看,果然有之前就保存好的excel文件:fiverrComScrapedResult.xls
打开后,效果如下:
5.很明显,没有header。
再去加代码。
折腾过程参见:
6.后来改为:
//save header for (i = 0; i <= dgvSearchResult.ColumnCount - 1; i++) { xlWorkSheet.Cells[0+1, i+1] = dgvSearchResult.Columns[i].HeaderText; }
就正常了,因为把i和j搞错了。。。
结果保存出来的数据,和原先的数据,第一行丢失了。
7.最终,使用如下代码:
using Excel = Microsoft.Office.Interop.Excel; private void btnSaveAll_Click(object sender, EventArgs e) { Excel.Application xlApp = new Excel.Application(); Excel.Workbook xlWorkBook; Excel.Worksheet xlWorkSheet; object misValue = System.Reflection.Missing.Value; xlApp = new Excel.ApplicationClass(); xlWorkBook = xlApp.Workbooks.Add(misValue); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); int i = 0; int j = 0; //save header for (i = 0; i <= dgvSearchResult.ColumnCount - 1; i++) { xlWorkSheet.Cells[0+1, i+1] = dgvSearchResult.Columns[i].HeaderText; } //save cells for (i = 0; i <= dgvSearchResult.RowCount - 1; i++) { for (j = 0; j <= dgvSearchResult.ColumnCount - 1; j++) { DataGridViewCell cell = dgvSearchResult[j, i]; if (j == girUrlColumnIdx) { xlWorkSheet.Cells[i + 2, j + 1] = cell.Tag.ToString(); } else { xlWorkSheet.Cells[i + 2, j + 1] = cell.Value; } } } string currentPath = System.Environment.CurrentDirectory; string outputFilename = "fiverrComScrapedResult.xls"; string fullFilename = Path.Combine(currentPath, outputFilename); //xlWorkBook.SaveAs(fullFilename, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); xlWorkBook.SaveAs(fullFilename, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); releaseObject(xlWorkSheet); releaseObject(xlWorkBook); releaseObject(xlApp); } private void releaseObject(object obj) { try { System.Runtime.InteropServices.Marshal.ReleaseComObject(obj); obj = null; } catch (Exception ex) { obj = null; MessageBox.Show("Exception Occured while releasing object " + ex.ToString()); } finally { GC.Collect(); } }
解决了标题的问题,最终可以获得了正常的输出,带标题的:
【总结】
效果还不错。
关于继续折腾格式化的问题,可参见: