总结DataGridView导出Excel代码
private void DataGridViewToExcel(DataGridView dgv)
{
#region 验证可操作性
int rowscount = dgv.Rows.Count;
int colscount = dgv.Columns.Count;
if (rowscount <= 0)
{
MessageBox.Show("没有数据可供保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
if (colscount <= 0)
{
MessageBox.Show("没有数据可供保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
if (rowscount > 65536)
{
MessageBox.Show("数据记录数太多(最多不能超过65536条),不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
if (colscount > 255)
{
MessageBox.Show("数据记录行数太多(最多不能超过255列),不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
SaveFileDialog dlg = new SaveFileDialog();
dlg.DefaultExt = "xls ";
dlg.Filter = "EXCEL文件(*.XLS)|*.xls ";
dlg.InitialDirectory = Directory.GetCurrentDirectory();
if (dlg.ShowDialog() == DialogResult.Cancel)
{
return;
}
string fileNameString = dlg.FileName;
if (fileNameString.Trim() == " ")
{
return;
}
//验证以fileNameString命名的文件是否存在,如果存在删除它
FileInfo file = new FileInfo(fileNameString);
if (file.Exists)
{
try
{
file.Delete();
}
catch (Exception error)
{
MessageBox.Show(error.Message, "删除失败 ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
}
#endregion
//建立Excel对象
Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
Excel.Workbook xBk = excel.Workbooks.Add(true);
Excel.Worksheet xSt = (Excel.Worksheet)xBk.Worksheets[1];
excel.Visible = false;
#region 填充表头和表体
int displayColumnsCount = 1;
for (int i = 0; i < colscount; i++)
{
if (dgv.Columns[i].Visible)
{
excel.Cells[1, displayColumnsCount] = dgv.Columns[i].HeaderText.Trim();
displayColumnsCount++;
}
}
tempProgressBar.Refresh();
tempProgressBar.Visible = true;
tempProgressBar.Minimum = 1;
tempProgressBar.Maximum = rowscount;
tempProgressBar.Step = 5;
for (int row = 0; row < rowscount; row++)
{
if (row % 5 == 0)
{
tempProgressBar.PerformStep();
}
displayColumnsCount = 1;
for (int col = 0; col < colscount; col++)
{
if (dgv.Columns[col].Visible)
{
try
{
excel.Cells[row + 2, displayColumnsCount] = "'" + dgv.Rows[row].Cells[col].Value.ToString().Trim();
displayColumnsCount++;
}
catch (Exception)
{
}
}
}
}
excel.Cells.Borders.LineStyle = 1;
excel.Columns.AutoFit();
tempProgressBar.Visible = false;
#endregion
xBk.SaveAs(fileNameString, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, XlSaveAsAccessMode.xlShared, Missing.Value, Missing.Value,
Missing.Value,
Missing.Value, Missing.Value);
if (DialogResult.Yes.Equals(MessageBox.Show(fileNameString + "/n/n导出完毕! 要打开导出的文件么? ", "提示 ", MessageBoxButtons.YesNo, MessageBoxIcon.Question)))
{
excel.Visible = true;
return;
}
else
{
if (xBk != null)
{
xBk.Close(Missing.Value, Missing.Value, Missing.Value);
}
if (excel.Workbooks != null)
{
excel.Workbooks.Close();
}
if (excel != null)
{
excel.Quit();
}
xSt = null;
xBk = null;
excel = null;
}
}