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秒
}
}
uj5u.com熱心網友回復：
試試NPOI吧uj5u.com熱心網友回復：
你又知道是匯出的時候慢？不能是你的資料量大？uj5u.com熱心網友回復：
你可以讓它在保存的時候走瀏覽器下載路徑試一下uj5u.com熱心網友回復：
也就幾千條資料 不至于吧uj5u.com熱心網友回復：
不要直接操作cell或者range，用object[,]陣列。如果一定要直接操作單元格，至少加上ScreenUpdating=false
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/204093.html
標籤：C#
上一篇：dev控制元件 里的gridcontrol 里的一列為日期，為什么選完后在串列上是yi加上了 時分秒，如何去掉？