1、数据导出至excel(数据流导出的方式)
优点:速度快,缺点:列宽无法实现自适应
public void ExportToExcel_1()
{
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.Filter = "Execl files (*.xls)|*.xls";
saveFileDialog.FilterIndex = 0;
saveFileDialog.RestoreDirectory = true;
//saveFileDialog.CreatePrompt = true; //当文件不存在时是否提示创建新文件
saveFileDialog.Title = "导出Excel文件";
saveFileDialog.ShowDialog();
if (saveFileDialog.FileName == "")
return;
Stream myStream;
myStream = saveFileDialog.OpenFile();
StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding(-0));
string str = "";
try
{
for (int i = 0; i < dgv_AIResult.ColumnCount; i++)
{
if (i > 0)
{
str += "\t";
}
str += dgv_AIResult.Columns[i].HeaderText;
}
sw.WriteLine(str);
for (int j = 0; j < dgv_AIResult.Rows.Count; j++)
{
string tempStr = "";
for (int k = 0; k < dgv_AIResult.Columns.Count; k++)
{
if (k > 0)
{
tempStr += "\t";
}
tempStr += dgv_AIResult.Rows[j].Cells[k].Value.ToString();
}
sw.WriteLine(tempStr);
}
sw.Close();
myStream.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
sw.Close();
myStream.Close();
MessageBox.Show("数据导出成功");
}
}
2、数据导出至excel(创建excel导出的方式)
优点:列宽自适应,缺点:速度慢、需要下载NuGet包(Microsoft.Office.Interop.Excel)
public void ExportToExcel_2()
{
string fileName = "";
string saveFileName = "";
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.DefaultExt = "xls";
saveDialog.Filter = "Excel文件|*.xls";
saveDialog.FileName = fileName;
saveDialog.ShowDialog();
saveFileName = saveDialog.FileName;
if (saveFileName.IndexOf(":") < 0)
{
return;
}
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];//取得sheet1
//写入标题
for (int i = 0; i < dgv_AIResult.ColumnCount; i++)
{
worksheet.Cells[1, i + 1] = dgv_AIResult.Columns[i].HeaderText;
}
for (int r = 0; r < dgv_AIResult.Rows.Count; r++)
{
for (int i = 0; i < dgv_AIResult.ColumnCount; i++)
{
worksheet.Cells[r + 2, i + 1] = dgv_AIResult.Rows[r].Cells[i].Value;
}
System.Windows.Forms.Application.DoEvents();
}
worksheet.Columns.EntireColumn.AutoFit();//列宽自适应
MessageBox.Show(fileName + "数据导出成功", "提示", MessageBoxButtons.OK);
if (saveFileName != "")
{
try
{
workbook.Saved = true;
workbook.SaveCopyAs(saveFileName); //fileSaved = true;
}
catch (Exception ex)
{
//fileSaved = false;
MessageBox.Show("导出文件时出错,文件可能正被打开!\n" + ex.Message);
}
xlApp.Quit();
GC.Collect();//强行销毁
}
}
总结:两种方法均可实现导出数据功能,看个人需求选择,无偿分享,记得点赞!!!