保存/创建/导出Excel工作表时的内存异常
本文关键字:异常 内存 工作 创建 导出 Excel 保存 | 更新日期: 2023-09-27 18:36:36
开发环境:
- 操作系统 - 视窗 7 64 位
- 中央处理器 - i5 460M
- 内存 - 8GB
- .NET 框架 - 4.0 Excel
- 互操作 - Microsoft Excel 14.0 对象库
我正在使用Excel-Interop从DataGridView(dgv)导出Excel文件。
当我保存超过 150,000 行时
内存不足异常
被扔了。
{
object[,] valueObjArray = new object[rowCnt, colCnt];
int rowCnt = dgv.Rows.Count;
int colCnt = dgv.Columns.Count;
for (int rowIndex = 0; rowIndex < rowCnt; rowIndex++)
{
for (int colIndex = 0; colIndex < colCnt; colIndex++)
{
valueObjArray[rowIndex, colIndex] = dgv[colIndex, rowIndex].Value;
}
}
_workSheet.get_Range("A1", Convert.ToChar(colCnt + 64).ToString() + "1").Value2 = headerObjArray;
_workSheet.get_Range("A2", Convert.ToChar(colCnt + 64).ToString() + (rowCnt + 1).ToString()).Value2 = valueObjArray;
_workSheet.get_Range("B2", "B" + (rowCnt+1).ToString()).NumberFormat = "yyyy-mm-dd hh:mm";
_workBook.SaveAs(path);
}
这是我所知道的最好的加速方法。
但是,在监视RAM之后,我认为这会导致内存增加。当内存使用量达到大约 900Mb 时引发异常。
如何捕获此异常?
尝试分批进行:
//We will call SaveAs method many times and we don't want to be asked
//if a file should be overwritten every time.
xlApp.DisplayAlerts = false
int rowCnt = dgv.Rows.Count;
int colCnt = dgv.Columns.Count;
int batchSize = 100000; //Try to experiment with other values
int currentRow = 0;
object[,] valueObjArray = new object[batchSize, colCnt];
_workSheet.get_Range("A1", Convert.ToChar(colCnt + 64).ToString() + "1").Value2 = headerObjArray;
while (currentRow < rowCnt)
{
for (int rowIndex = 0; rowIndex < batchSize && currentRow + rowIndex < rowCnt; rowIndex++)
{
for (int colIndex = 0; colIndex < colCnt; colIndex++)
{
valueObjArray[rowIndex, colIndex] =
dgv[colIndex, currentRow + rowIndex].Value;
}
}
ws.get_Range("A2", Convert.ToChar(colCnt + 64).ToString() + (currentRow + batchSize + 1).ToString()).Value2 = valueObjArray;
ws.get_Range("B2", "B" + (currentRow + batchSize + 1).ToString()).NumberFormat = "yyyy-mm-dd hh:mm";
wb.SaveAs("a.xlsx");
currentRow += batchSize;
}
我能够以这种方式保存 100 万行。我用假数据对其进行了测试,因此可能需要进行一些小的更改/修复。
尝试这样做....
我有大量数据 20 万及以上。首先我正在使用excel包和Microsoft.Office.Interop.Excel本地系统工作正常,但是 iis(静态 IP 地址)托管在 (工作表.单元格/工作表.范围) 内存不足e异常错误之后。所以我正在使用StreamWriter并写入.xls文件。.xls转换为.xlsx文件并删除.xls文件后。它为我工作。我的英语不好.请理解。
private void ExportxlFile(DataTable dt)
{
try
{
//open file
//non english not support(ex: Bangla Language)"সালাউদ্দিন স্টোর";
// StreamWriter wr = new StreamWriter(@"D:'TestBook.xls");
//non english support(ex: Bangla Language)
StreamWriter wr = new StreamWriter(@"D:'TestBook.xls", true, Encoding.Unicode); // Encoding.Unicode or Encoding.UTF32
for (int i = 0; i < dt.Columns.Count; i++)
{
wr.Write(dt.Columns[i].ToString().ToUpper() + "'t");
}
wr.WriteLine();
//write rows to excel file
for (int i = 0; i < (dt.Rows.Count); i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
if (dt.Rows[i][j] != null)
{
wr.Write(Convert.ToString(dt.Rows[i][j]) + "'t");
}
else
{
wr.Write("'t");
}
}
//go to next line
wr.WriteLine();
}
//close file
wr.Close();
//xls to xlsx convertion
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook wb = app.Workbooks.Open(@"D:'TestBook.xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
wb.SaveAs(@"D:'TestBook.xlsx", Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
wb.Close();
app.Quit();
//delete xls file
System.IO.File.Delete(@"D:'TestBook.xls");
}
catch (Exception ex)
{
throw ex;
}
}