public static void ExportToExcel(DataSet ds, string fileName, bool overWrite, bool exportHeaders, bool open) { if (ds == null) { throw new ArgumentNullException("ds"); } if (File.Exists(fileName)) { if (overWrite) { File.Delete(fileName); } else { throw new Exception("文件已经存在!"); } } Interop.Excel.Application app = new Interop.Excel.Application { Visible = false }; Interop.Excel.Workbooks books = app.Workbooks; Interop.Excel.Workbook book = books.Add(miss); foreach (Interop.Excel.Worksheet item in book.Sheets) { item.Delete(); } foreach (DataTable dt in ds.Tables) { Interop.Excel.Worksheet sheet = (Interop.Excel.Worksheet)book.Sheets.Add(miss, miss, miss, miss); if (!string.IsNullOrEmpty(dt.TableName)) { sheet.Name = dt.TableName; } Recordset rs = ConvertDataTableToRecordset(dt); Interop.Excel.Range range = (Interop.Excel.Range)sheet.Cells[1, 1]; if (exportHeaders) // 标题行 { for (int i = 0; i < dt.Columns.Count; i++) { Interop.Excel.Range rangeHeader = sheet.get_Range((object)(GetColumnLabel(i + 1) + "1"), miss); rangeHeader.Value2 = dt.Columns[i].Caption; } 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(); }
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(); }