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();
}