导出到Excel,基本是很多单据的标配功能了。笔者之前将Datagridview的数据导出到Excel时,将数据一个单元格一个单元格的写入,效率奇慢,
1030条数据花费了将近70s的时间。后来借鉴了前辈们的其它写法,将数据先生成到string变量,再写入,效率提升了150多倍。分享如下:
1、改善后结果:
2、导出到Excel使用的方法:
简要说明:
TableName:数据源数据表
FileName:要保存的Excel文件名
lblStatus:ToolStripStatusLable
barStatus:ToolStripProgressBar
引用及代码:
using System.IO;
using System.Data.SqlClient;
using System.Threading;
using System.Diagnostics;
public void ExportDataToExcel(DataTable TableName, string FileName)
{
SaveFileDialog saveFileDialog = new SaveFileDialog();
//设置文件标题
saveFileDialog.Title = "导出Excel文件";
//设置文件类型
saveFileDialog.Filter = "Microsoft Office Excel 工作簿(*.xls)|*.xls";
//设置默认文件类型显示顺序
saveFileDialog.FilterIndex = ;
//是否自动在文件名中添加扩展名
saveFileDialog.AddExtension = true;
//是否记忆上次打开的目录
saveFileDialog.RestoreDirectory = true;
//设置默认文件名
saveFileDialog.FileName = FileName;
//按下确定选择的按钮
if (saveFileDialog.ShowDialog() == DialogResult.OK)
{
//获得文件路径
string localFilePath = saveFileDialog.FileName.ToString(); //数据初始化
int TotalCount; //总行数
int RowRead = ; //已读行数
int Percent = ; //百分比 TotalCount = TableName.Rows.Count;
lblStatus.Text = "共有" + TotalCount + "条数据";
lblStatus.Visible = true;
barStatus.Visible = true; //数据流
Stream myStream = saveFileDialog.OpenFile();
StreamWriter sw = new StreamWriter(myStream, Encoding.GetEncoding("gb2312"));
string strHeader = ""; //秒钟
Stopwatch timer = new Stopwatch();
timer.Start(); try
{
//写入标题
for (int i = ; i < TableName.Columns.Count; i++)
{
if (i > )
{
strHeader += "\t";
}
strHeader += TableName.Columns[i].ColumnName.ToString();
}
sw.WriteLine(strHeader); //写入数据
//string strData;
for (int i = ; i < TableName.Rows.Count; i++)
{
RowRead++;
Percent = (int)( * RowRead / TotalCount);
barStatus.Maximum = TotalCount;
barStatus.Value = RowRead;
lblStatus.Text = "共有" + TotalCount + "条数据,已写入" + Percent.ToString() + "%的数据,共耗时" + timer.ElapsedMilliseconds + "毫秒。";
Application.DoEvents(); string strData = "";
for (int j = ; j < TableName.Columns.Count; j++)
{
if (j > )
{
strData += "\t";
}
strData += TableName.Rows[i][j].ToString();
}
sw.WriteLine(strData);
}
//关闭数据流
sw.Close();
myStream.Close();
//关闭秒钟
timer.Reset();
timer.Stop();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
finally
{
//关闭数据流
sw.Close();
myStream.Close();
//关闭秒钟
timer.Stop();
} //成功提示
if (MessageBox.Show("导出成功,是否立即打开?", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
{
System.Diagnostics.Process.Start(localFilePath);
} //赋初始值
lblStatus.Visible = false;
barStatus.Visible = false;
}
}
后记:
美中不足的是,上述代码仅能导出到xls格式,若需导出到xlsx格式的话,仍需调整代码。