111,082
社区成员
发帖
与我相关
我的任务
分享C#导出Excel速度慢的解决方案
C#导出Excel速度奇慢，大概要5分钟时间，这个怎么解决
#region Excel
string path;
private void daochu()
{
if (zhuangt == "" || zhuangt == null)
{
//MessageBox.Show("没有数据可导出哦！", "提示");
}
else
{
path = System.AppDomain.CurrentDomain.BaseDirectory + "sacolar storage.xlsx";
if (File.Exists(path))
{
//File.Delete(path);
ThreadStart entry = new ThreadStart(ex);//求和方法被定义为工作线程入口
workThread2 = new Thread(entry);
workThread2.Start();
}
else
{
//SaveFileDialog sfd = new SaveFileDialog();
//sfd.FileName = "sacolar storage";//文件名
//sfd.DefaultExt = "xlsx"; //设置默认扩展名为xls
//sfd.Filter = "Excel文件(*.xlsx)|*.xlsx";//另存文件时文件类型框中出现的内容
//if (sfd.ShowDialog() == DialogResult.OK) //获取选定的另存文件对话框存在
//{
//DoExport(this.textBox34, sfd.FileName);
CreateExcelFile(path);//文件保存地址
ThreadStart entry = new ThreadStart(ex);//求和方法被定义为工作线程入口
workThread2 = new Thread(entry);
workThread2.Start();
//}
}
}
}
//新建excel
private void CreateExcelFile(string FileName)
{
//create
object Nothing = System.Reflection.Missing.Value;
var app = new Excel.Application();
app.Visible = false;
Excel.Workbook workBook = app.Workbooks.Add(Nothing);
Excel.Worksheet worksheet = (Excel.Worksheet)workBook.Sheets[1];
// worksheet.Columns.ColumnWidth = 24;
worksheet.Name = "Work";
//headline
worksheet.Cells[1, 1] = "Serial number";
worksheet.Cells[1, 2] = "Time";
worksheet.Cells[1, 3] = "Status";
worksheet.Cells[1, 4] = "Vpv1(V)";
worksheet.Cells[1, 5] = "vpv2(V)";
worksheet.Cells[1, 6] = "Ppv1(W)";
worksheet.Cells[1, 7] = "ppv2(W)";
worksheet.Cells[1, 8] = "vBattery(V)";
worksheet.Cells[1, 9] = "Capacity(%)";
worksheet.Cells[1, 10] = "epvToday1(kWh)";
worksheet.Cells[1, 11] = "epvTotal1(kWh)";
worksheet.Cells[1, 12] = "epvToday2(kWh)";
worksheet.Cells[1, 13] = "epvTotal2(kWh)";
worksheet.Cells[1, 14] = "iChargePV1(A)";
worksheet.Cells[1, 15] = "iChargePV2(A)";
worksheet.Cells[1, 16] = "outPutPower(VA)";
worksheet.Cells[1, 17] = "pAcCharge(W)";
worksheet.Cells[1, 18] = "vGrid(V)";
worksheet.Cells[1, 19] = "freqGrid(Hz)";
worksheet.Cells[1, 20] = "outPutVolt(V)";
worksheet.Cells[1, 21] = "freqOutPut(Hz)";
worksheet.Cells[1, 22] = "loadPercent(%)";
worksheet.Cells[1, 23] = "outPutCurrent(A)";
worksheet.Cells[1, 24] = "eacChargeToday(kWh)";
worksheet.Cells[1, 25] = "eacChargeTotal(kWh)";
worksheet.Cells[1, 26] = "eBatDisChargeToday(kWh)";
worksheet.Cells[1, 27] = "eBatDisChargeTotal(kWh)";
worksheet.Cells[1, 28] = "eacDisChargeToday(kWh)";
worksheet.Cells[1, 29] = "eacDisChargeTotal(kWh)";
//worksheet.Cells[1, 30] = "activePower(W)";
//worksheet.Cells[1, 31] = "ApparentPower(VA)";
worksheet.SaveAs(FileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing);
workBook.Close(false, Type.Missing, Type.Missing);
app.Quit();
}
//插入数据
private void ex()
{
if (path != null)
{
//while (true)
//{
if (serialPort1.IsOpen)
{
Excel.Application xApp = new Excel.Application();
//1.创建Applicaton对象
// xApp.Visible = true;
//2.得到workbook对象，可以用两种方式之一：下面是打开已有的文件
// Excel.Workbook xBook = xApp.Workbooks.Open(@"E:\5_调试软件\ThinkCamWorkstation\PassData\192.168.116.248\2015-08-06\18\result.xlsx",
Excel.Workbook xBook = xApp.Workbooks.Open(path,
Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value);
//3.指定要操作的Sheet
Excel.Worksheet xSheet = (Excel.Worksheet)xBook.Sheets[1];
int Count = xSheet.UsedRange.Rows.Count;//获取行数
int rowsCount = Count + 1;
//4.写入数据
Excel.Range firstColumn = xSheet.get_Range("A" + rowsCount);//写入的位置
firstColumn.Value2 = zhi;//写入内容
firstColumn.EntireColumn.AutoFit();//自动调整列宽
Excel.Range firstColumnB = xSheet.get_Range("B" + rowsCount);
firstColumnB.Value2 = (DateTime.Now.Year + "-" + DateTime.Now.Month + "-" + DateTime.Now.Day + ". " + DateTime.Now.Hour + ":" + DateTime.Now.Minute + ":" + DateTime.Now.Second).ToString();//时间
firstColumnB.EntireColumn.AutoFit();
Excel.Range firstColumnC = xSheet.get_Range("C" + rowsCount);
firstColumnC.Value2 = zhuangt;
firstColumnC.EntireColumn.AutoFit();
Excel.Range firstColumnD = xSheet.get_Range("D" + rowsCount);
firstColumnD.Value2 = heVpv1;
firstColumnD.EntireColumn.AutoFit();
Excel.Range firstColumnE = xSheet.get_Range("E" + rowsCount);
firstColumnE.Value2 = heVpv2;
firstColumnE.EntireColumn.AutoFit();
Excel.Range firstColumnF = xSheet.get_Range("F" + rowsCount);
firstColumnF.Value2 = hePpv1;
firstColumnF.EntireColumn.AutoFit();
Excel.Range firstColumnG = xSheet.get_Range("G" + rowsCount);
firstColumnG.Value2 = hePpv2;
firstColumnG.EntireColumn.AutoFit();
Excel.Range firstColumnH = xSheet.get_Range("H" + rowsCount);
firstColumnH.Value2 = heBat_Volt;
firstColumnH.EntireColumn.AutoFit();
Excel.Range firstColumnI = xSheet.get_Range("I" + rowsCount);
firstColumnI.Value2 = heBatterySOC;
firstColumnI.EntireColumn.AutoFit();
Excel.Range carAmount = xSheet.get_Range("J" + rowsCount, Missing.Value);
carAmount.Value2 = kepv1;
firstColumnI.EntireColumn.AutoFit();
Excel.Range firstColumnK = xSheet.get_Range("K" + rowsCount);
firstColumnK.Value2 = keto1;
firstColumnK.EntireColumn.AutoFit();
Excel.Range firstColumnL = xSheet.get_Range("L" + rowsCount);
firstColumnL.Value2 = kepv2;
firstColumnL.EntireColumn.AutoFit();
Excel.Range invalid_license_plate = xSheet.get_Range("M" + rowsCount, Missing.Value);
invalid_license_plate.Value2 = keto2;
invalid_license_plate.EntireColumn.AutoFit();
Excel.Range chinese_character_wrong = xSheet.get_Range("N" + rowsCount, Missing.Value);
chinese_character_wrong.Value2 = kipv1;
chinese_character_wrong.EntireColumn.AutoFit();
Excel.Range letter_wrong = xSheet.get_Range("O" + rowsCount, Missing.Value);
letter_wrong.Value2 = kipv2;
letter_wrong.EntireColumn.AutoFit();
Excel.Range number_wrong = xSheet.get_Range("P" + rowsCount, Missing.Value);
number_wrong.Value2 = heOP_VA;
number_wrong.EntireColumn.AutoFit();
Excel.Range recognition_wrong = xSheet.get_Range("Q" + rowsCount, Missing.Value);
recognition_wrong.Value2 = pac;
recognition_wrong.EntireColumn.AutoFit();
xBook.Save();
//6.从内存中关闭Excel对象
xSheet = null;
xBook = null;
xApp.Quit(); //这一句非常重要，否则Excel对象不能从内存中退出
xApp = null;
}
else
{
workThread2.Abort();
//break;
}
//Thread.Sleep(Convert.ToInt32(textBox1.Text));//等待5秒
}
}
...全文
请发表友善的回复…
发表回复
Donniezhu 2020-11-12
- 打赏
- 举报
excel中带有很多的字体样式，单元格样式，公式，时间格式，缩放，数据格式，数据长度异常尤其是还要有颜色等，excel中还有一些数据合理性的验证，数据类型验证等等，不再一一举例。所以速度受很大影响。
如果想速度快，最简单，最有效的解决办法就是导入CSV格式文件。可以使用NPOI。
如果想速度快，最简单，最有效的解决办法就是导入CSV格式文件。可以使用NPOI。
U闲程序猿 2020-11-12
- 打赏
- 举报
看看是不是线程处理不当导致的，不用线程试一下看看。按道理导入千把数据应该 不会要那么长时间
frank1314 2020-11-12
- 打赏
- 举报
导出要选好格式等格式，我导出好几万都很快。还有记得列字段尽量为字符型。
HerryDong 2020-11-09
- 打赏
- 举报
兄嘚，说实话哈，你这方法不仅仅是导出速度慢的问题，而且还要求客户端必须安装Excel。.NET环境下导出Excel文件的首选就是NPOI，NPOI的优点就是导出速度极快 + 不需要安装Excel，你去搜一下NPOI就知道啦~
wangqx86662638 2020-11-09
- 打赏
- 举报
项目-》管理NUGET包
输入closedxml 然后安装
public static void Excel_write(string title,string filename, DataTable dt)
{
XLWorkbook workBook = new XLWorkbook();
IXLWorksheet sheet = workBook.Worksheets.Add(title);
IXLStyle style = sheet.Style;
style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
style.Alignment.WrapText = true; //自动回行
style.Font.FontName = "宋体";
for (int k = 0; k < dt.Columns.Count; k++)
{
sheet.Cell(1, k + 1).Value = dt.Columns[k].ColumnName;
sheet.Cell(1, k + 1).Style.Font.FontSize = 11;
sheet.Cell(1, k + 1).Style.Border.TopBorder = XLBorderStyleValues.Thin;
sheet.Cell(1, k + 1).Style.Border.TopBorderColor = XLColor.Black;
sheet.Cell(1, k + 1).Style.Border.BottomBorder = XLBorderStyleValues.Thin;
sheet.Cell(1, k + 1).Style.Border.BottomBorderColor = XLColor.Black;
sheet.Cell(1, k + 1).Style.Border.LeftBorder = XLBorderStyleValues.Thin;
sheet.Cell(1, k + 1).Style.Border.LeftBorderColor = XLColor.Black;
sheet.Cell(1, k + 1).Style.Border.RightBorder = XLBorderStyleValues.Thin;
sheet.Cell(1, k + 1).Style.Border.RightBorderColor = XLColor.Black;
sheet.Column(k + 1).Width = 14; //设置列宽
}
sheet.Row(1).Height = 14;//设置行高
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
sheet.Cell(i + 2, j + 1).Value = dt.Rows[i][j].ToString();
sheet.Cell(i + 2, j + 1).Style.Font.FontSize = 11;
sheet.Cell(i + 2, j + 1).Style.Border.TopBorder = XLBorderStyleValues.Thin;
sheet.Cell(i + 2, j + 1).Style.Border.TopBorderColor = XLColor.Black;
sheet.Cell(i + 2, j + 1).Style.Border.BottomBorder = XLBorderStyleValues.Thin;
sheet.Cell(i + 2, j + 1).Style.Border.BottomBorderColor = XLColor.Black;
sheet.Cell(i + 2, j + 1).Style.Border.LeftBorder = XLBorderStyleValues.Thin;
sheet.Cell(i + 2, j + 1).Style.Border.LeftBorderColor = XLColor.Black;
sheet.Cell(i + 2, j + 1).Style.Border.RightBorder = XLBorderStyleValues.Thin;
sheet.Cell(i + 2, j + 1).Style.Border.RightBorderColor = XLColor.Black;
}
}
workBook.SaveAs(filename);
MessageBox.Show("导出成功!");
}
Water Lee 2020-11-08
- 打赏
- 举报
之前我也遇到过，不过我后来直接不导出为excel了，直接导出为csv文件，这样写的时候可以以文本方式写，速度不会超过2秒。这样操作也可以用excel打开表格文件，但是excel打开文件后如果想另行保存，会提示格式问题。当然如果选择xls格式后就正常了。
看需求吧。希望对你有帮助。
程序小佳 2020-11-07
- 打赏
- 举报
使用NPOI，closedxml，epplus都比微软的快
以专业开发人员为伍 2020-11-07
- 打赏
- 举报
另外要注意，像这种调用 Office COM 的，需要在 UI 主线程调用，不要在子线程调用。子线程不但会慢几十倍，而且经常崩溃（线程无响应）。
以专业开发人员为伍 2020-11-07
- 打赏
- 举报
写入二维数组就一句
用不着逐个单元都要跨进程调用 Excel。
Range[...].Resize[rowNum,colNum].values2 = arr;用不着逐个单元都要跨进程调用 Excel。
Dear200892 2020-11-06
- 打赏
- 举报
你是要把数据写到Excel还是从Excel读取数据
清茶与酒&八度余温 2020-11-06
- 打赏
- 举报
这里 报错了清茶与酒&八度余温 2020-11-06
- 打赏
- 举报
有代码详情吗嘛
Dear200892 2020-11-06
- 打赏
- 举报
我使用NPOI插件,将2700条数据导出只需要几秒钟
沫含天下 2020-11-06
- 打赏
- 举报
数据量有多少？
earlsen 2020-11-06
- 打赏
- 举报
我们一般是用Infragistics.Win.UltraWinGrid 第三方控件，几千几万都是几秒钟
Dear200892 2020-11-06
- 打赏
- 举报
我的是导出
泡泡龙 2020-11-06
- 打赏
- 举报
这里 报错了[/quote]
请看微软官方文档
https://docs.microsoft.com/zh-cn/office/vba/api/excel.application.screenupdating清茶与酒&八度余温 2020-11-06
- 打赏
- 举报
写入
泡泡龙 2020-11-05
- 打赏
- 举报
不要直接操作cell或者range，用object[,]数组。
如果一定要直接操作单元格，至少加上ScreenUpdating=false
清茶与酒&八度余温 2020-11-05
- 打赏
- 举报
也就几千条数据 不至于吧
加载更多回复（3）
