更有效的方式来写数据表到excel？ [英] More efficient way to write data table to excel?

问题描述

在我的 WPF 应用程序中，我有一个巨大的数据表（ System.Data.DataTable ）写入Excel文档中的工作表。函数的重要部分：

In my WPF application, I have a huge data table (System.Data.DataTable) that I need to write to a sheet in an excel document. The heavy part of the function:

        for (; i < dt.Rows.Count; i++)
        {
            for (int colNum = 0; colNum < dt.Columns.Count; colNum++)
                newSheet.Cells[i + rowNumber, colNum + 1] = dt.Rows[i][colNum].ToString();
            applyRowBorderStyle(newSheet, i + rowNumber, dt.Columns.Count);
        }

dt是DataTable，neewSheet是我写入的Excel工作表，并且applyRowBorderStyle （..）为该行中的所有单元格添加边框。当数据表很大时，它运行非常缓慢，可能需要10分钟甚至更长的时间。有什么方法可以使其运行得更快？

dt is the DataTable, neewSheet is the excel sheet i write to, and applyRowBorderStyle(..) adds borders to all the cells in the row. It runs very slowly when the data table is big, might take 10 minutes or even more. Is there any way to make it run faster?

编辑：该程序分析了大量数据，并产生了很多工作表，而我不能让用户做任何事情。我只能使用Microsoft Excel。该表表格始终有42列，但是行数根据程序接收的数据量（约500行）而变化。 applyRowBorderStyle将使代码运行更快，但不满足要求。.我真的希望有另一种方法可以使它运行得更快。。

The program analyses a lot of data and makes a lot of sheets, and I can't make the user do anything. I must use only Microsoft Excel. This sheets table always has 42 columns, but the number of lines changes according to how much data did the program receive, ~500 lines. "applyRowBorderStyle" will make the code run a bit faster, but doesn't meet the requirements.. I really hope there is another way to make it run faster..

推荐答案

找到答案！这是iv'e编写的函数，以及我使用的参考： http://www.codeproject.com/Articles/21519/Fast-Exporting-from-DataSet-to-Excel

found the answer! here's the function iv'e wrote, and the reference I used: http://www.codeproject.com/Articles/21519/Fast-Exporting-from-DataSet-to-Excel

private void FastDtToExcel(System.Data.DataTable dt, Worksheet sheet, int firstRow, int firstCol, int lastRow, int lastCol)
    {
        Range top = sheet.Cells[firstRow, firstCol];
        Range bottom = sheet.Cells[lastRow, lastCol];
        Range all = (Range)sheet.get_Range(top, bottom);
        string[,] arrayDT = new string[dt.Rows.Count, dt.Columns.Count];
        for (int i = 0; i < dt.Rows.Count; i++)
            for (int j = 0; j < dt.Columns.Count; j++)
                arrayDT[i, j] = dt.Rows[i][j].ToString();
        all.Value2 = arrayDT;
    }

花费不到一秒钟的时间，真是棒极了：）

takes less than a second, which is awesome :)

这篇关于更有效的方式来写数据表到excel？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！