/// <summary> /// 导出为Excel方法 /// </summary> /// <param name="savePath">保存EXCEL的路径,如"C:\\ ab.xls"</param> /// <param name="dt1">数据表-可以改为范型集合</param> public void ToExcel(string savePath, System.Windows.Forms.DataGridView dgv) { Interop.Excel.ApplicationClass xlApp = new Interop.Excel.ApplicationClass(); Interop.Excel.Workbooks workbooks = xlApp.Workbooks; Interop.Excel.Workbook workbook = workbooks.Add(Interop.Excel.XlWBATemplate.xlWBATWorksheet); Interop.Excel.Worksheet worksheet = (Interop.Excel.Worksheet)workbook.Worksheets[1]; Interop.Excel.Range range = (Interop.Excel.Range)worksheet.Columns; range.EntireColumn.AutoFit(); //自动调整列宽 range.NumberFormatLocal = "@"; worksheet.Name = tabFiled[0];//命名工作表的名称 try { //加列头 for (int i = 1; i < tabFiled.Count; i++) { worksheet.Cells[1, i] = tabFiled[i]; } //加列值 for (int p = 0; p < dgv.Rows.Count; p++) { for (int j = 0; j < dgv.Columns.Count; j++) { worksheet.Cells[p + 2, j + 1] = dgv.Rows[p].Cells[j].Value.ToString(); } } workbook.Saved = true; workbook.SaveCopyAs(savePath); } catch (Exception ed) { throw new Exception(ed.Message); } finally { System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet); worksheet = null; System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook); workbook = null; workbooks.Close(); System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks); workbooks = null; xlApp.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp); xlApp = null; GC.Collect();//使用此方法强制对所有代码进行垃圾回收。 } //xlApp.Visible = true;//如果要显示EXCEL,那么finally语句的内容就不应该执行 }
public static void ExportToExcel(DataGridView dgv, string fileName, bool overWrite, string sheetName, bool exportHeaders, bool open) { if (dgv == null) { throw new ArgumentNullException("dgv"); } if (dgv.Columns.Count == 0) { throw new Exception("所提供的 DataGridView 中不包含任何列."); } if (File.Exists(fileName)) { if (overWrite) { File.Delete(fileName); } else { throw new Exception("文件已经存在!"); } } Interop.Excel.Application app = new Interop.Excel.ApplicationClass { Visible = false }; Interop.Excel.Workbooks books = app.Workbooks; Interop.Excel.Workbook book = books.Add(miss); Interop.Excel.Worksheet sheet = (Interop.Excel.Worksheet)book.ActiveSheet; foreach (Interop.Excel.Worksheet s in book.Sheets) { if (s != sheet) { s.Delete(); } } if (!string.IsNullOrEmpty(sheetName)) { sheet.Name = sheetName; } Recordset rs = ConvertDataGridViewToRecordSet(dgv); Interop.Excel.Range range = (Interop.Excel.Range)sheet.Cells[1, 1]; if (exportHeaders) // 标题行 { int i = 0; foreach (DataGridViewColumn col in dgv.Columns) { if (col.Visible) { Interop.Excel.Range rangeHeader = sheet.get_Range((object)(GetColumnLabel(i + 1) + "1"), miss); rangeHeader.Value2 = col.HeaderText; i++; } } range = (Interop.Excel.Range)sheet.Cells[2, 1]; } range.CopyFromRecordset(rs, miss, miss); book.SaveAs(fileName, Interop.Excel.XlFileFormat.xlExcel7, miss, miss, miss, miss, Interop.Excel.XlSaveAsAccessMode.xlNoChange, miss, miss, miss, miss, miss); if (open) { app.Visible = true; } else { book.Close(false, miss, miss); books.Close(); app.Quit(); } System.Runtime.InteropServices.Marshal.ReleaseComObject(book); System.Runtime.InteropServices.Marshal.ReleaseComObject(books); System.Runtime.InteropServices.Marshal.ReleaseComObject(app); GC.Collect(); }