/// <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语句的内容就不应该执行
}
internal Workbook(Interop.Excel.Workbook workbook)
{
this.workbook = workbook;
}
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();
}
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();
}
internal Workbook(Interop.Excel.Workbook workbook)
{
this.workbook = workbook;
}