C#Microsoft.Office.Interop.Excel导出 [英] c# Microsoft.Office.Interop.Excel export

问题描述

我正在编写一个使用C#语言， DataSet 等的程序.我有大约20万个要导出到.xlsx文档的值.

I'm writing a program in which I'm using C# language, DataSet, etc. I have about 200 000 values what I want to export to an .xlsx document.

我的代码:

using Excel = Microsoft.Office.Interop.Excel;
...
Excel.Application excelApp = new Excel.Application();
Excel.Workbook excelworkbook = excelApp.Workbooks.Open(/location/);
Excel._Worksheet excelworkSheet = (Excel.Worksheet)excelApp.ActiveSheet;
...
excelApp.visible = true;
...
for (int i = 0; i < /value/; i++)
 for (int j = 0; j < /value/; j++) 
  excelworkSheet.Cells[i, j] = /value/;

效果很好，但是速度太慢(至少5-10分钟).

It works well, but it is too slow (at least 5-10 minutes).

您有什么建议吗?

推荐答案

我刚刚遇到了同样的性能问题，并将其写到了基准测试中:

I just took the same performance hit, wrote this to benchmark:

[Test]
public void WriteSpeedTest()
{
    var excelApp = new Application();
    var workbook = excelApp.Workbooks.Add();
    var sheet = (Worksheet)workbook.Worksheets[1];
    int n = 1000;
    var stopwatch = Stopwatch.StartNew();
    SeparateWrites(sheet, n);
    Console.WriteLine("SeparateWrites(sheet, " + n + "); took: " + stopwatch.ElapsedMilliseconds + " ms");

    stopwatch.Restart();
    BatchWrite(sheet, n);
    Console.WriteLine("BatchWrite(sheet, " + n + "); took: " + stopwatch.ElapsedMilliseconds + " ms");

    workbook.SaveAs(Path.Combine(@"C:\TEMP", "Test"));
    workbook.Close(false);
    Marshal.FinalReleaseComObject(excelApp);
}

private static void BatchWrite(Worksheet sheet, int n)
{
    string[,] strings = new string[n, 1];
    var array = Enumerable.Range(1, n).ToArray();
    for (var index = 0; index < array.Length; index++)
    {
        strings[index, 0] = array[index].ToString();
    }

    sheet.Range["B1", "B" + n].set_Value(null, strings);
}

private static void SeparateWrites(Worksheet sheet, int n)
{
    for (int i = 1; i <= n; i++)
    {
        sheet.Cells[i, 1].Value = i.ToString();
    }
}

结果:

                            n = 100   n = 1 000   n = 10 000    
SeparateWrites(sheet, n);   180 ms    1125 ms     10972 ms
BatchWrite(sheet, n);       3 ms      4 ms        14 ms

这篇关于C#Microsoft.Office.Interop.Excel导出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！