获取"Error saving file"消息(或)系统。检测到内存不足异常(或)上下文切换死锁

Question

我使用C#开发了一个Windows窗体应用程序。

该应用程序将读取Excel文件，将该值存储到数据表中，进行一些处理，并将处理后的信息写入新的Excel文件。

写入时没有问题<400行，如果大于400，则应用程序抛出异常Error saving file或System.OutOfMemoryException或ContextSwitchDeadlock was detected

同时显示以下错误（在visual studio窗口内）：The CLR has been unable to transition from COM context 0x592b88 to COM context 0x592cf8 for 60 seconds. The thread that owns the destination context/apartment is most likely either doing a non pumping wait or processing a very long running operation without pumping Windows messages. This situation generally has a negative performance impact and may even lead to the application becoming non responsive or memory usage accumulating continually over time. To avoid this problem, all single threaded apartment (STA) threads should use pumping wait primitives (such as CoWaitForMultipleHandles) and routinely pump messages during long running operations.

worksheetToWrite.Cells["A" + Iteration1].Value = sMailID2.Trim();
worksheetToWrite.Cells["B" + Iteration1].Value = intCurrentBatchSize.ToString();
if (sFull1.Length <= 4000)
{
    worksheetToWrite.Cells["C" + Iteration1].Value = sFull1.Substring(0, sFull1.Length);
    worksheetToWrite.Cells["D" + Iteration1].Value = string.Empty;
    worksheetToWrite.Cells["E" + Iteration1].Value = string.Empty;
    worksheetToWrite.Cells["F" + Iteration1].Value = string.Empty;
    worksheetToWrite.Cells["G" + Iteration1].Value = string.Empty;
    worksheetToWrite.Cells["H" + Iteration1].Value = string.Empty;
    worksheetToWrite.Cells["I" + Iteration1].Value = string.Empty;
    worksheetToWrite.Cells["J" + Iteration1].Value = string.Empty;
    worksheetToWrite.Cells["K" + Iteration1].Value = string.Empty;
    worksheetToWrite.Cells["L" + Iteration1].Value = string.Empty;
}
else if (sFull1.Length > 4000 && sFull1.Length <= 8000)
{
    worksheetToWrite.Cells["C" + Iteration1].Value = sFull1.Substring(0, 4000);
    worksheetToWrite.Cells["D" + Iteration1].Value = sFull1.Substring(4000, sFull1.Length - 4000);
    worksheetToWrite.Cells["E" + Iteration1].Value = string.Empty;
    worksheetToWrite.Cells["F" + Iteration1].Value = string.Empty;
    worksheetToWrite.Cells["G" + Iteration1].Value = string.Empty;
    worksheetToWrite.Cells["H" + Iteration1].Value = string.Empty;
    worksheetToWrite.Cells["I" + Iteration1].Value = string.Empty;
    worksheetToWrite.Cells["J" + Iteration1].Value = string.Empty;
    worksheetToWrite.Cells["K" + Iteration1].Value = string.Empty;
    worksheetToWrite.Cells["L" + Iteration1].Value = string.Empty;
}
else if (sFull1.Length > 8000 && sFull1.Length <= 12000)
{
    worksheetToWrite.Cells["C" + Iteration1].Value = sFull1.Substring(0, 4000);
    worksheetToWrite.Cells["D" + Iteration1].Value = sFull1.Substring(4000, 4000);
    worksheetToWrite.Cells["E" + Iteration1].Value = sFull1.Substring(8000, sFull1.Length - 8000);
    worksheetToWrite.Cells["F" + Iteration1].Value = string.Empty;
    worksheetToWrite.Cells["G" + Iteration1].Value = string.Empty;
    worksheetToWrite.Cells["H" + Iteration1].Value = string.Empty;
    worksheetToWrite.Cells["I" + Iteration1].Value = string.Empty;
    worksheetToWrite.Cells["J" + Iteration1].Value = string.Empty;
    worksheetToWrite.Cells["K" + Iteration1].Value = string.Empty;
    worksheetToWrite.Cells["L" + Iteration1].Value = string.Empty;
}
else if (sFull1.Length > 12000 && sFull1.Length <= 16000)
{
    worksheetToWrite.Cells["C" + Iteration1].Value = sFull1.Substring(0, 4000);
    worksheetToWrite.Cells["D" + Iteration1].Value = sFull1.Substring(4000, 4000);
    worksheetToWrite.Cells["E" + Iteration1].Value = sFull1.Substring(8000, 4000);
    worksheetToWrite.Cells["F" + Iteration1].Value = sFull1.Substring(12000, sFull1.Length - 12000);
    worksheetToWrite.Cells["G" + Iteration1].Value = string.Empty;
    worksheetToWrite.Cells["H" + Iteration1].Value = string.Empty;
    worksheetToWrite.Cells["I" + Iteration1].Value = string.Empty;
    worksheetToWrite.Cells["J" + Iteration1].Value = string.Empty;
    worksheetToWrite.Cells["K" + Iteration1].Value = string.Empty;
    worksheetToWrite.Cells["L" + Iteration1].Value = string.Empty;
}
else if (sFull1.Length > 16000 && sFull1.Length <= 20000)
{
    worksheetToWrite.Cells["C" + Iteration1].Value = sFull1.Substring(0, 4000);
    worksheetToWrite.Cells["D" + Iteration1].Value = sFull1.Substring(4000, 4000);
    worksheetToWrite.Cells["E" + Iteration1].Value = sFull1.Substring(8000, 4000);
    worksheetToWrite.Cells["F" + Iteration1].Value = sFull1.Substring(12000, 4000);
    worksheetToWrite.Cells["G" + Iteration1].Value = sFull1.Substring(16000, sFull1.Length - 16000);
    worksheetToWrite.Cells["H" + Iteration1].Value = string.Empty;
    worksheetToWrite.Cells["I" + Iteration1].Value = string.Empty;
    worksheetToWrite.Cells["J" + Iteration1].Value = string.Empty;
    worksheetToWrite.Cells["K" + Iteration1].Value = string.Empty;
    worksheetToWrite.Cells["L" + Iteration1].Value = string.Empty;
}
else if (sFull1.Length > 20000 && sFull1.Length <= 24000)
{
    worksheetToWrite.Cells["C" + Iteration1].Value = sFull1.Substring(0, 4000);
    worksheetToWrite.Cells["D" + Iteration1].Value = sFull1.Substring(4000, 4000);
    worksheetToWrite.Cells["E" + Iteration1].Value = sFull1.Substring(8000, 4000);
    worksheetToWrite.Cells["F" + Iteration1].Value = sFull1.Substring(12000, 4000);
    worksheetToWrite.Cells["G" + Iteration1].Value = sFull1.Substring(16000, 4000);
    worksheetToWrite.Cells["H" + Iteration1].Value = sFull1.Substring(20000, sFull1.Length - 20000);
    worksheetToWrite.Cells["I" + Iteration1].Value = string.Empty;
    worksheetToWrite.Cells["J" + Iteration1].Value = string.Empty;
    worksheetToWrite.Cells["K" + Iteration1].Value = string.Empty;
    worksheetToWrite.Cells["L" + Iteration1].Value = string.Empty;
}
else if (sFull1.Length > 24000 && sFull1.Length <= 28000)
{
    worksheetToWrite.Cells["C" + Iteration1].Value = sFull1.Substring(0, 4000);
    worksheetToWrite.Cells["D" + Iteration1].Value = sFull1.Substring(4000, 4000);
    worksheetToWrite.Cells["E" + Iteration1].Value = sFull1.Substring(8000, 4000);
    worksheetToWrite.Cells["F" + Iteration1].Value = sFull1.Substring(12000, 4000);
    worksheetToWrite.Cells["G" + Iteration1].Value = sFull1.Substring(16000, 4000);
    worksheetToWrite.Cells["H" + Iteration1].Value = sFull1.Substring(20000, 4000);
    worksheetToWrite.Cells["I" + Iteration1].Value = sFull1.Substring(24000, sFull1.Length - 24000);
    worksheetToWrite.Cells["J" + Iteration1].Value = string.Empty;
    worksheetToWrite.Cells["K" + Iteration1].Value = string.Empty;
    worksheetToWrite.Cells["L" + Iteration1].Value = string.Empty;
}
else if (sFull1.Length > 28000 && sFull1.Length <= 32000)
{
    worksheetToWrite.Cells["C" + Iteration1].Value = sFull1.Substring(0, 4000);
    worksheetToWrite.Cells["D" + Iteration1].Value = sFull1.Substring(4000, 4000);
    worksheetToWrite.Cells["E" + Iteration1].Value = sFull1.Substring(8000, 4000);
    worksheetToWrite.Cells["F" + Iteration1].Value = sFull1.Substring(12000, 4000);
    worksheetToWrite.Cells["G" + Iteration1].Value = sFull1.Substring(16000, 4000);
    worksheetToWrite.Cells["H" + Iteration1].Value = sFull1.Substring(20000, 4000);
    worksheetToWrite.Cells["I" + Iteration1].Value = sFull1.Substring(24000, 4000);
    worksheetToWrite.Cells["J" + Iteration1].Value = sFull1.Substring(28000, sFull1.Length - 28000);
    worksheetToWrite.Cells["K" + Iteration1].Value = string.Empty;
    worksheetToWrite.Cells["L" + Iteration1].Value = string.Empty;
}
else if (sFull1.Length > 32000 && sFull1.Length <= 36000)
{
    worksheetToWrite.Cells["C" + Iteration1].Value = sFull1.Substring(0, 4000);
    worksheetToWrite.Cells["D" + Iteration1].Value = sFull1.Substring(4000, 4000);
    worksheetToWrite.Cells["E" + Iteration1].Value = sFull1.Substring(8000, 4000);
    worksheetToWrite.Cells["F" + Iteration1].Value = sFull1.Substring(12000, 4000);
    worksheetToWrite.Cells["G" + Iteration1].Value = sFull1.Substring(16000, 4000);
    worksheetToWrite.Cells["H" + Iteration1].Value = sFull1.Substring(20000, 4000);
    worksheetToWrite.Cells["I" + Iteration1].Value = sFull1.Substring(24000, 4000);
    worksheetToWrite.Cells["J" + Iteration1].Value = sFull1.Substring(28000, 4000);
    worksheetToWrite.Cells["K" + Iteration1].Value = sFull1.Substring(32000, sFull1.Length - 32000);
    worksheetToWrite.Cells["L" + Iteration1].Value = string.Empty;
}
else if (sFull1.Length > 36000 && sFull1.Length <= 40000)
{
    worksheetToWrite.Cells["C" + Iteration1].Value = sFull1.Substring(0, 4000);
    worksheetToWrite.Cells["D" + Iteration1].Value = sFull1.Substring(4000, 4000);
    worksheetToWrite.Cells["E" + Iteration1].Value = sFull1.Substring(8000, 4000);
    worksheetToWrite.Cells["F" + Iteration1].Value = sFull1.Substring(12000, 4000);
    worksheetToWrite.Cells["G" + Iteration1].Value = sFull1.Substring(16000, 4000);
    worksheetToWrite.Cells["H" + Iteration1].Value = sFull1.Substring(20000, 4000);
    worksheetToWrite.Cells["I" + Iteration1].Value = sFull1.Substring(24000, 4000);
    worksheetToWrite.Cells["J" + Iteration1].Value = sFull1.Substring(28000, 4000);
    worksheetToWrite.Cells["K" + Iteration1].Value = sFull1.Substring(32000, 4000);
    worksheetToWrite.Cells["L" + Iteration1].Value = sFull1.Substring(36000, sFull1.Length - 36000);
}

CCD_ 5表示行。除了第一行和第一个&每行的第二列。

有人能帮我解决吗？

Answer 1

以前我在每次迭代中都保存Excel文件。现在我创建了一个新的数据表，将所有信息（要写入Excel）合并到该数据表中。所以在写之前，所有的迭代都已经在这里完成了。在把所有内容都写进工作簿后，我将保存它。

它现在运行良好，在处理大量数据时没有问题。

代码：

<pre>ExcelWorksheet worksheetToWrite = packageToWrite1.Workbook.Worksheets["Lead Owners"];
     for (int i = 0; i < dtSalesPersonSingleSB.Columns.Count; i++)
     {
       worksheetToWrite.Cells[1, (i + 1)].Value = dtSalesPersonSingleSB.Columns[i].ColumnName; //dtSalesPersonSingle.Columns[i].ColumnName;
     }
     for (int i = 0; i < dtSalesPersonSingleSB.Rows.Count; i++)
     {
     // to do: format datetime values before printing
      for (int j = 0; j < dtSalesPersonSingleSB.Columns.Count; j++)
      {
        worksheetToWrite.Cells[(i + 2), (j + 1)].Value = dtSalesPersonSingleSB.Rows[i][j];
      }
     }
     packageToWrite1.Save();

感谢您为实现预期结果所给予的一切支持。