本文是列表导出规则Excel文档(直接读取DataGridView中的数据)的示例。
首先在项目中添加“Microsoft.Office.Interop.Excel”。
创建一个工作表,如下所示:
int v = 0;
SaveFileDialog sfd = new SaveFileDialog();
string currentPatch = Directory.GetCurrentDirectory();
sfd.Title = "请选择将导出的EXCEL文件存放路径";
sfd.FileName = Convert.ToDateTime(tempdate).ToString("yyyy年MM月") + "-保險代扣實際明細表";
sfd.Filter = "Excel文档(*.xls)|*.xls";
if (sfd.ShowDialog() == DialogResult.OK)
{
if (sfd.FileName != "")
{
Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
if (excelApp == null)
{
MessageBox.Show("无法创建Excel对象,可能您的机器未安装Excel");
}
else
{
try
{
Microsoft.Office.Interop.Excel.Workbooks workbooks = excelApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Worksheet)workbook.Worksheets[1];
for (int i = 0; i < dt.Tables[0].Rows.Count; i++)
{
for (int j = 1; j < dt.Tables[0].Columns.Count; j++)
{
if (i == 0)
{
if (j == 1)
{
worksheet.Cells[i + 1, j] = this.dtEmp.Columns[j-1].HeaderText;
}
else
{
worksheet.Cells[i + 1, j] = this.dtEmp.Columns[j].HeaderText;
}
}
worksheet.Cells[v + 2, j] = dt.Tables[0].Rows[i][j].ToString();
}
v++;
}
//保存方式一:保存WorkBook
//workbook.SaveAs(@"F:CData.xls",
// Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,
// Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,Missing.Value,Missing.Value,Missing.Value,
// Missing.Value,Missing.Value);
//保存方式二:保存WorkSheet
// worksheet.SaveAs(@"F:CData2.xls",
// Missing.Value, Missing.Value, Missing.Value, Missing.Value,
// Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
//保存方式三
workbook.Saved = true;
workbook.SaveCopyAs(sfd.FileName);
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;
excelApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
excelApp = null;
MessageBox.Show("导出Excel完成!");
}
catch
{
MessageBox.Show("保存出错,请检查文件是否被正使用!", "系统信息");
}
finally
{
GC.Collect();
}
}
}
注意事项:
1、有Excel的操作,把本页面中的DataTable全部要改为DataSet。
2、进行Excel的操作后,系统默认的路径发生了变化,需从默认路径读去数据的操作就会报错。在操作前写上string currentPatch = Directory.GetCurrentDirectory();语句保存默认路径,操作后用Directory.SetCurrentDirectory(currentPatch);恢复默认值。而且必须要在项目中添加“Microsoft.IO”。