使用excel组件导出DataGuideView中的数据到excel表格,其中dgvres是DataGuideView控件名称,参数Exportall指定是导出全部数据包括隐藏的行,还是导出显示数据。导出完成后销毁所有创建的excel内存对象和后台进程,到开导出文件并定位。
调用示例:
需要添加excel组件引用,如已安装office可以直接添加,没有安装office可下载excel组件并添加引用。
导出可见行:
private void btnVisibleRow_Click(object sender, EventArgs e)
{
ExportToEXCEL(false);
}
导出全部行:
private void btnall_Click(object sender, EventArgs e)
{
ExportToEXCEL(true);
}
导出函数:
private void ExportToEXCEL(bool Exportall)
{
if (dgvres.Rows.Count == 0) { MessageBox.Show("数据为空!"); return; }
string fileName = "明细结果" + DateTime.Now.ToString("yyyyMMddhh24miss");
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.DefaultExt = "xls";
saveDialog.Filter = "(Excel2003).xls|*.xls|(Excel2007).xlsx|*.xlsx";
saveDialog.FileName = fileName;
saveDialog.ShowDialog();
string saveFileName = saveDialog.FileName;
if (saveFileName.IndexOf(":") < 0) return;
//Thread t = new Thread(new ThreadStart(delegate
//{
// Application.Run(new 弹窗之正在处理("正在导出数据"));
//}));
//t.Start();
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
MessageBox.Show("无法创建Excel对象,您的电脑可能未安装Excel");
return;
}
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
for (int i = 0; i < dgvres.ColumnCount; i++)
{ worksheet.Cells[1, i + 1] = dgvres.Columns[i].HeaderText; }
int rows = 2;
//写入数值
for (int r = 0; r < dgvres.Rows.Count; r++)
{
if (dgvres.Rows[r].Visible == false && Exportall == false)
continue;
for (int i = 0; i < dgvres.ColumnCount; i++)
{
worksheet.Cells[rows, i + 1] = dgvres.Rows[r].Cells[i].Value;
}
rows++;
}
worksheet.Columns.EntireColumn.AutoFit();
if (saveFileName != "")
{
workbook.Saved = true;
workbook.SaveCopyAs(saveFileName);
//worksheet.SaveAs(saveFileName);
}
try
{
workbook.Close(false);
xlApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
workbook = null;
workbooks = null;
xlApp = null;
System.GC.Collect();
}
catch (Exception ex)
{
MessageBox.Show("导出文件时出错,文件可能正被打开!\n" + ex.Message);
}
finally
{
GC.Collect();
}
//t.Abort();
MessageBox.Show(saveFileName + "导出成功", "提示", MessageBoxButtons.OK);//提示保存成功
if (System.IO.File.Exists(saveFileName))
{
System.Diagnostics.ProcessStartInfo psi = new System.Diagnostics.ProcessStartInfo("Explorer.exe");
psi.Arguments = " /select," + saveFileName;
System.Diagnostics.Process.Start(psi);
}
}