将DataTable复制到Excel

Question

将DataTable复制到Excel

3

我有一个大小为m x n的DataTable，想要将所有内容（包括列标题）复制到一个已经打开的excel文件中。我有对Excel.Workbook的引用，并且知道数据将被复制到哪个工作表中。我知道最简单（也是最不规范的）的方法是：

Excel.WorkSheet outSheet; //set to desired worksheet
int rowIdx = 1;
int colIdx = 1;
//add header row
foreach (DataColumn dc in dt.Columns)
{
    outSheet.Cells[rowIdx, colIdx++] = dc.ColumnName;
}

colIdx = 1; //reset to Cell 1

//add rest of rows
foreach (DataRow dr in dt.Rows)
{
    colIdx = 0;
    foreach (DataColumn dc in dt.Columns)
    {
        outSheet.Cells[rowIdx + 1, colIdx + 1] = dr[colIdx].ToString();
        colIdx++;
    }
    rowIdx++;
}

这种方法可以实现，但是需要逐个单元格地访问和粘贴数据，时间成本非常高。有没有更好的方法来完成这个任务呢？

- KatariaA

1

加快当前进程的一种方法是在循环之前设置 Application.ScreenUpdating = false 和 Application.Calculation = xlCalculationManual。此外，似乎您并没有复制/粘贴，而是仅设置值，这比复制/粘贴要快得多。 - StoriKnow

谢谢，这确实很有帮助，但不是恰当的解决方案。 - KatariaA

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Pilgerstorfer Franz · Accepted Answer

我为您编写了一个小例子。tl;dr意思是，你可以将一个值数组分配给Excel范围。但这个数组必须符合一些规格要求。感谢Eric Carter。

    Stopwatch sw = new Stopwatch();
    sw.Start();
    Application xlApp = new Application();
    Workbook xlBook = xlApp.Workbooks.Open(@"E:\Temp\StackOverflow\COM_Interop_CS\bin\Debug\demo.xlsx");
    Worksheet wrkSheet = xlBook.Worksheets[1];            

    try
    {
        /// credits go to: 
        /// http://blogs.msdn.com/b/eric_carter/archive/2004/05/04/126190.aspx
        /// 
        /// [cite] when you want to set a range of values to an array, you must declare that array as a 2 
        /// dimensional array where the left-most dimension is the number of rows you are going to set and 
        /// the right-most dimension is the number of columns you are going to set.  
        /// 
        /// Even if you are just setting one column, you can’t create a 1 dimensional array and have it work[/cite]

        Excel.Range range = wrkSheet.Range["A1", "Z100000"];
        int maxRows = 100000, maxCols = 26;
        object[,] values = new object[maxRows, maxCols];

        int counter = 0;
        for (int row = 0; row < maxRows; row++)
        {
            for (int col = 0; col < maxCols; col++)
            {
                values[row, col] = counter++;
            }
        }
        range.Value2 = values;                
    }
    catch (Exception ex)
    {
        Debug.WriteLine(ex.Message);
    }
    xlApp.Visible = true;
    sw.Stop();
    Console.WriteLine("Elapsed: {0}", sw.Elapsed);

我在不到10秒钟的时间内添加了10万行和26列。希望这对您来说是合适的！