将SQL数据导出到Excel非常慢

选项1:

看到这个答案.使用名为ClosedXML的库将数据写入Excel.

选项2:

获取足够大的范围以容纳所有数据,并将值设置为等于2维范围.这非常快,没有另一个引用另一个库.我尝试了70000条记录.

// Get an excel instance
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();

// Get a workbook
Workbook wb = excel.Workbooks.Add();

// Get a worksheet
Worksheet ws = wb.Worksheets.Add();
ws.Name = "Test Export";

// Add column names to the first row
int col = 1;
foreach (DataColumn c in table.Columns) {
    ws.Cells[1, col] = c.ColumnName;
    col++;
}

// Create a 2D array with the data from the table
int i = 0;
string[,] data = new string[table.Rows.Count, table.Columns.Count];
foreach (DataRow row in table.Rows) {                
    int j = 0;
    foreach (DataColumn c in table.Columns) {
        data[i,j] = row[c].ToString();
        j++;
    }
    i++;            
}                   

// Set the range value to the 2D array
ws.Range[ws.Cells[2, 1], ws.Cells[table.Rows.Count + 1, table.Columns.Count]].value = data;

// Auto fit columns and rows, show excel, save.. etc
excel.Columns.AutoFit();
excel.Rows.AutoFit();
excel.Visible = true;

编辑:此版本在我的机器上导出了一百万条记录,大约需要一分钟.此示例使用Excel互操作并将行分成100,000个块.

// Start a stopwatch to time the process
System.Diagnostics.Stopwatch sw = new System.Diagnostics.Stopwatch();
sw.Start();

// Check if there are rows to process
if (table != null && table.Rows.Count > 0) {

    // Determine the number of chunks
    int chunkSize = 100000;
    double chunkCountD = (double)table.Rows.Count / (double)chunkSize;
    int chunkCount = table.Rows.Count / chunkSize;
    chunkCount = chunkCountD > chunkCount ? chunkCount + 1 : chunkCount;

    // Instantiate excel
    Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();

    // Get a workbook
    Workbook wb = excel.Workbooks.Add();

    // Get a worksheet
    Worksheet ws = wb.Worksheets.Add();
    ws.Name = "Test Export";

    // Add column names to excel
    int col = 1;                
    foreach (DataColumn c in table.Columns) {
        ws.Cells[1, col] = c.ColumnName;
        col++;
    }

    // Build 2D array
    int i = 0;
    string[,] data = new string[table.Rows.Count, table.Columns.Count];
    foreach (DataRow row in table.Rows) {
        int j = 0;
        foreach (DataColumn c in table.Columns) {
            data[i, j] = row[c].ToString();
            j++;
        }
        i++;
    }

    int processed = 0;
    int data2DLength = data.GetLength(1);
    for (int chunk = 1; chunk <= chunkCount; chunk++) {
        if (table.Rows.Count - processed < chunkSize) chunkSize = table.Rows.Count - processed;            
        string[,] chunkData = new string[chunkSize, data2DLength];
        int l = 0;
        for (int k = processed; k < chunkSize + processed; k++) {
            for (int m = 0; m < data2DLength; m++) {
                chunkData[l,m] = table.Rows[k][m].ToString();
            }
            l++;
        }
        // Set the range value to the chunk 2d array
        ws.Range[ws.Cells[2 + processed, 1], ws.Cells[processed + chunkSize + 1, data2DLength]].value = chunkData;
        processed += chunkSize;
    }

    // Auto fit columns and rows, show excel, save.. etc
    excel.Columns.AutoFit();
    excel.Rows.AutoFit();
    excel.Visible = true;                
}

// Stop the stopwatch and display the seconds elapsed
sw.Stop();
MessageBox.Show(sw.Elapsed.TotalSeconds.ToString());