将数据集放入工作表的最快方法 [英] Fastest way to drop a DataSet into a worksheet

问题描述

需要将具有 16000 x 12 条目的相当 higeisch 数据集转储到工作表中.

A rather higeisch dataset with 16000 x 12 entries needs to be dumped into a worksheet.

我现在使用以下函数:

        for (int r = 0; r < dt.Rows.Count; ++r)
        {
            for (int c = 0; c < dt.Columns.Count; ++c)
            {
                worksheet.Cells[c + 1][r + 1] = dt.Rows[r][c].ToString();
            }
        }

我将示例简化为中心部分

I rediced the example to the center piece

这是我在阅读了 Dave Zych 的建议后实现的.这很好用.

Here is what i implemented after reading the suggestion from Dave Zych. This works great.

    private static void AppendWorkSheet(Excel.Workbook workbook, DataSet data, String tableName)
    {
        Excel.Worksheet worksheet;
        if (UsedSheets == 0) worksheet = workbook.Worksheets[1];
        else worksheet = workbook.Worksheets.Add();
        UsedSheets++;
        DataTable dt = data.Tables[0];
        var valuesArray = new object[dt.Rows.Count, dt.Columns.Count];

        for (int r = 0; r < dt.Rows.Count; ++r)
        {
            for (int c = 0; c < dt.Columns.Count; ++c)
            {
                valuesArray[r, c] = dt.Rows[r][c].ToString();
            }
        }
        Excel.Range c1 = (Excel.Range)worksheet.Cells[1, 1];
        Excel.Range c2 = (Excel.Range)worksheet.Cells[dt.Rows.Count, dt.Columns.Count];
        Excel.Range range = worksheet.get_Range(c1, c2);
        range.Cells.Value2 = valuesArray;
        worksheet.Name = tableName;
    }

推荐答案

从您的 DataSet 构建一个二维数组，然后您可以在 Excel 中将一系列值设置为数组.

Build a 2D array of your values from your DataSet, and then you can set a range of values in Excel to the values of the array.

object valuesArray = new object[dataTable.Rows.Count, dataTable.Columns.Count];
for(int i = 0; i < dt.Rows.Count; i++)
{
    //If you know the number of columns you have, you can specify them this way
    //Otherwise use an inner for loop on columns
    valuesArray[i, 0] = dt.Rows[i]["ColumnName"].ToString();
    valuesArray[i, 1] = dt.Rows[i]["ColumnName2"].ToString();
    ...
}

//Calculate the second column value by the number of columns in your dataset
//"O" is just an example in this case
//Also note: Excel is 1 based index
var sheetRange = worksheet.get_Range("A2:O2", 
    string.Format("A{0}:O{0}", dt.Rows.Count + 1));

sheetRange.Cells.Value2 = valuesArray;

这比单独循环和设置每个单元格要快得多.如果您单独设置每个单元格，则必须通过 COM 与 Excel 对话(因为缺少更好的短语)每个单元格(在您的情况下约为 192,000 次)，这非常慢.循环、构建数组并只与 Excel 对话一次可以消除大部分开销.

This is much, much faster than looping and setting each cell individually. If you're setting each cell individually, you have to talk to Excel through COM (for lack of a better phrase) for each cell (which in your case is ~192,000 times), which is incredibly slow. Looping, building your array and only talking to Excel once removes much of that overhead.

这篇关于将数据集放入工作表的最快方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！