c# - mvc - ¿Cómo acelerar el vertido de un DataTable en una hoja de cálculo de Excel?

libreria para leer excel c#

Question

Tengo la siguiente rutina que vuelca una DataTable en una hoja de cálculo de Excel.

    private void RenderDataTableOnXlSheet(DataTable dt, Excel.Worksheet xlWk, 
                                    string [] columnNames, string [] fieldNames)
    {
        // render the column names (e.g. headers)
        for (int i = 0; i < columnNames.Length; i++)
            xlWk.Cells[1, i + 1] = columnNames[i];

        // render the data 
        for (int i = 0; i < fieldNames.Length; i++)
        {
            for (int j = 0; j < dt.Rows.Count; j++)
            {
                xlWk.Cells[j + 2, i + 1] = dt.Rows[j][fieldNames[i]].ToString();
            }
        }
    }

Por el motivo que sea, tirar DataTable de 25 columnas y 400 filas lleva entre 10 y 15 segundos en mi PC relativamente moderna. Toma máquinas de probadores aún más largos.

¿Hay algo que pueda hacer para acelerar este código? ¿O la interoperabilidad es inherentemente lenta?

SOLUCIÓN: En base a las sugerencias de Helen Toomik, modifiqué el método y ahora debería funcionar para varios tipos de datos comunes (int32, double, datetime, string). Siéntase libre de extenderlo. La velocidad para procesar mi conjunto de datos pasó de 15 segundos a menos de 1.

    private void RenderDataTableOnXlSheet(DataTable dt, Excel.Worksheet xlWk, string [] columnNames, string [] fieldNames)
    {
        Excel.Range rngExcel = null;
        Excel.Range headerRange = null;

        try
        {
            // render the column names (e.g. headers)
            for (int i = 0; i < columnNames.Length; i++)
                xlWk.Cells[1, i + 1] = columnNames[i];

            // for each column, create an array and set the array 
            // to the excel range for that column.
            for (int i = 0; i < fieldNames.Length; i++)
            {
                string[,] clnDataString = new string[dt.Rows.Count, 1];
                int[,] clnDataInt = new int[dt.Rows.Count, 1];
                double[,] clnDataDouble = new double[dt.Rows.Count, 1];

                string columnLetter = char.ConvertFromUtf32("A".ToCharArray()[0] + i);
                rngExcel = xlWk.get_Range(columnLetter + "2", Missing.Value);
                rngExcel = rngExcel.get_Resize(dt.Rows.Count, 1);

                string dataTypeName = dt.Columns[fieldNames[i]].DataType.Name;

                for (int j = 0; j < dt.Rows.Count; j++)
                {
                    if (fieldNames[i].Length > 0)
                    {
                        switch (dataTypeName)
                        {
                            case "Int32":
                                clnDataInt[j, 0] = Convert.ToInt32(dt.Rows[j][fieldNames[i]]);
                                break;
                            case "Double":
                                clnDataDouble[j, 0] = Convert.ToDouble(dt.Rows[j][fieldNames[i]]);
                                break;
                            case "DateTime":
                                if (fieldNames[i].ToLower().Contains("time"))
                                    clnDataString[j, 0] = Convert.ToDateTime(dt.Rows[j][fieldNames[i]]).ToShortTimeString();
                                else if (fieldNames[i].ToLower().Contains("date"))
                                    clnDataString[j, 0] = Convert.ToDateTime(dt.Rows[j][fieldNames[i]]).ToShortDateString();
                                else 
                                    clnDataString[j, 0] = Convert.ToDateTime(dt.Rows[j][fieldNames[i]]).ToString();

                                break;
                            default:
                                clnDataString[j, 0] = dt.Rows[j][fieldNames[i]].ToString();
                                break;
                        }
                    }
                    else
                        clnDataString[j, 0] = string.Empty;
                }

                // set values in the sheet wholesale.
                if (dataTypeName == "Int32") 
                    rngExcel.set_Value(Missing.Value, clnDataInt);
                else if (dataTypeName == "Double")
                    rngExcel.set_Value(Missing.Value, clnDataDouble);                             
                else
                    rngExcel.set_Value(Missing.Value, clnDataString);
            }


            // figure out the letter of the last column (supports 1 letter column names)
            string lastColumn = char.ConvertFromUtf32("A".ToCharArray()[0] + columnNames.Length - 1);

            // make the header range bold
            headerRange = xlWk.get_Range("A1", lastColumn + "1");
            headerRange.Font.Bold = true;

            // autofit for better view
            xlWk.Columns.AutoFit();

        }
        finally
        {
            ReleaseObject(headerRange);
            ReleaseObject(rngExcel);
        }
    }

    private void ReleaseObject(object obj)
    {
        try
        {
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
            obj = null;
        }
        catch
        {
            obj = null;
        }
        finally
        {
            GC.Collect();
        }
    }

Answer 1

En lugar de establecer valores de celda uno por uno, hágalo en un lote.

Paso 1. Transfiera los datos de su DataTable a una matriz con las mismas dimensiones.

Paso 2. Defina un objeto de rango de Excel que abarque el rango apropiado.

Paso 3. Configura Range.Value para la matriz.

Esto será mucho más rápido porque tendrá un total de dos llamadas a través del límite Interop (una para obtener el objeto Range, otra para establecer su valor), en lugar de dos por celda (obtener celda, establecer el valor).

Hay algún código de muestra en el artículo 302096 de MSDN KB .

Answer 2

Estoy de acuerdo con Charles Interop es realmente lento. Pero prueba esto:

private void RenderDataTableOnXlSheet(DataTable dt, Excel.Worksheet xlWk, 
                                    string [] columnNames, string [] fieldNames)
{
    // render the column names (e.g. headers)
    int columnLength = columnNames.Length;
    for (int i = 0; i < columnLength; i++)
        xlWk.Cells[1, i + 1] = columnNames[i];

    // render the data 
        int fieldLength = fieldNames.Length;
        int rowCount = dt.Rows.Count;
        for (int j = 0; j < rowCount; j++)
        { 
            for (int i = 0; i < fieldLength; i++)
            {
                xlWk.Cells[j + 2, i + 1] = dt.Rows[j][fieldNames[i]].ToString();
            }
        }
}

HTH

Answer 3

Interop es inherentemente muy lento. Hay una gran sobrecarga asociada con cada llamada. Para acelerarlo, intente escribir una matriz de datos de objetos en un rango de celdas en una declaración de asignación.

O si se trata de un problema grave, intente utilizar una de las extensiones de Excel de código administrado que pueden leer / escribir datos utilizando código administrado a través de la interfaz XLL. (Addin Express, XLL administrado, etc.)

Answer 4

Si tiene un conjunto de registros, la forma más rápida de escribir en Excel es CopyFromRecordset.

Answer 5

¿Tiene un requisito específico para ir a la ruta de automatización COM? Si no, tienes algunas otras opciones.

1. Use el proveedor OLEDB para crear / escribir en un archivo Excel
   http://support.microsoft.com/kb/316934

2. Use una biblioteca de terceros para escribir en Excel. Dependiendo de sus requisitos de licencia hay algunas opciones. Actualización: una buena biblioteca gratuita es NPOI http://npoi.codeplex.com/

3. Escriba los datos en un archivo csv y cárguelo en Excel

4. Escriba los datos como XML que pueden cargarse en Excel.

5. Use el Open XML SDK
   http://www.microsoft.com/downloads/details.aspx?familyid=C6E744E5-36E9-45F5-8D8C-331DF206E0D0&displaylang=en

Answer 6

Puede crear un complemento de Excel con código VBA para realizar todo su trabajo pesado de db. desde .NET, todo lo que necesita hacer es crear una instancia de Excel, agregar el complemento y llamar a la rutina de Excel VBA, pasándole los parámetros necesarios para ejecutar sus sentencias de SQL.