none
Exportación a Excel tarda mucho RRS feed

  • Pregunta

  • Buenas tardes. Estoy modificando un programa donde se exportan datos de una db sql a excel.

    El problema es que tarda muchísimo en exportarlos. Ya porbé exportando casi 3000 registros y tardó unos 3 minutos, no es tanto pero si se exportan más tarda bastante.

    Hay manera de hacer que se exporte más rápido?

    En la empresa donde están usando el programa me avisaron de que quisieron exportar una buena cantidad de registros (no recuerdo cuantos) y tardó aproximadamente 40 minutos.

    Espero sus respuestas. gracias :D


    Skull

    miércoles, 6 de noviembre de 2019 22:35

Respuestas

  • Estoy modificando un programa donde se exportan datos [...] El problema es que tarda muchísimo en exportarlos.

    La causa más usual de que tarde tanto es que se esté realizando la exportación llamando directamente a Excel mediante COM/Interop, y se exporta celda por celda realizando un salto a través de COM por cada una de las celdas. Cada una de esas llamadas es relativamente lenta, por lo que si se hacen muchas el tiempo total es grande.

    Una solución para eso es meter todos los datos en un array y exportar de golpe todo el array a un bloque de celdas con una única llamada COM/Interop. Esto es muchísimo más rápido que exportar celda por celda.

    Otra solución es cambiar el mecanismo de exportación, de forma que se grabe directamente en el archivo Excel en lugar de hacer llamadas a través de la aplicación Excel. Hay varias formas de hacer esto. Una de ellas consiste en usar el proveedor OleDb para Excel que te permite tratar el archivo como una tabla de base de datos, de manera que grabas mediante sentencias "Insert". Esto es precisamente lo que hace el "asistente para importar y exportar" de SQL Server, que ya te han sugerido en otra respuesta. Con ese asistente puedes hacer el experimento en un momento, y si ves que es lo bastante rápido puedes duplicar esa funcionalidad desde tu programa usando el mismo driver de OleDb.

    • Marcado como respuesta Skull15101996 jueves, 7 de noviembre de 2019 23:22
    jueves, 7 de noviembre de 2019 7:18
    Moderador

Todas las respuestas

  • Pues si es mucho, podrías compartir el código con el cual generas y exportas a excel?

    Saludos


    ARA San Juan 44 HEROES     ‗‗‗‗­|||||‗‗‗‗‗

    miércoles, 6 de noviembre de 2019 22:37
  • Hola Skull15101996:

    ¿Pero tarda el proceso de exportación o tarda la consulta que recupera los datos para exportar?

    O es que se realizan infinidad de conexiones y consultas para obtener los datos.

    Intenta realizarlo con la herramienta de exportación y compara el resultado

    https://javifer2.wordpress.com/2018/11/19/exportar-a-excel-desde-sql-server-con-la-herramienta-de-importacion-y-exportacion-facil/

    jueves, 7 de noviembre de 2019 5:32
  • Estoy modificando un programa donde se exportan datos [...] El problema es que tarda muchísimo en exportarlos.

    La causa más usual de que tarde tanto es que se esté realizando la exportación llamando directamente a Excel mediante COM/Interop, y se exporta celda por celda realizando un salto a través de COM por cada una de las celdas. Cada una de esas llamadas es relativamente lenta, por lo que si se hacen muchas el tiempo total es grande.

    Una solución para eso es meter todos los datos en un array y exportar de golpe todo el array a un bloque de celdas con una única llamada COM/Interop. Esto es muchísimo más rápido que exportar celda por celda.

    Otra solución es cambiar el mecanismo de exportación, de forma que se grabe directamente en el archivo Excel en lugar de hacer llamadas a través de la aplicación Excel. Hay varias formas de hacer esto. Una de ellas consiste en usar el proveedor OleDb para Excel que te permite tratar el archivo como una tabla de base de datos, de manera que grabas mediante sentencias "Insert". Esto es precisamente lo que hace el "asistente para importar y exportar" de SQL Server, que ya te han sugerido en otra respuesta. Con ese asistente puedes hacer el experimento en un momento, y si ves que es lo bastante rápido puedes duplicar esa funcionalidad desde tu programa usando el mismo driver de OleDb.

    • Marcado como respuesta Skull15101996 jueves, 7 de noviembre de 2019 23:22
    jueves, 7 de noviembre de 2019 7:18
    Moderador
  •        private void GenerarExcelTrimestrales()
            {
                // Creating a Excel object. 
                Excel._Application excel = new Microsoft.Office.Interop.Excel.Application();
                Excel._Workbook workbook = excel.Workbooks.Add(Type.Missing);
                Excel._Worksheet worksheet = null;
    
                try
                {
                    worksheet = workbook.ActiveSheet;
    
                    // Tamaño y tipo de letra
                    //((Excel.Range)worksheet.UsedRange).Font.Name = "Calibri";
                    //((Excel.Range)worksheet.UsedRange).Font.Size = 8;
    
                    worksheet.get_Range("A1", "ZZ1048576").Font.Name = "Calibri";
                    worksheet.get_Range("A1", "ZZ1048576").Font.Size = 8;
    
                    // Alto de cada fila
                    worksheet.Rows.RowHeight = 25;
    
                    // Alineacion vertical del texto "Centrada"
                    worksheet.get_Range("A1", "ZZ1048576").VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
    
                    // Ajustamos todos los textos a las celdas
                    worksheet.get_Range("A1", "ZZ1048576").WrapText = true;
    
                    // Creo Cabecera del Excel
                    worksheet.Cells[1, 1] = "NRO.CERTIF";
                    worksheet.Cells[1, 1].Interior.Color = System.Drawing.Color.FromArgb(184, 204, 228);
                    worksheet.get_Range("A1", "A1").ColumnWidth = 4.14 ;
    
                    worksheet.Cells[1, 2] = "FECHA EMISION";
                    worksheet.Cells[1, 2].Interior.Color = System.Drawing.Color.FromArgb(184, 204, 228);
                    worksheet.get_Range("B1", "B1").ColumnWidth = 7.43;
    
                    worksheet.Cells[1, 3] = "NOMBRE CLIENTE";
                    worksheet.Cells[1, 3].Interior.Color = System.Drawing.Color.FromArgb(184, 204, 228);
                    worksheet.get_Range("C1", "C1").ColumnWidth = 17.71;
    
                    worksheet.Cells[1, 4] = "DIRECCION";
                    worksheet.Cells[1, 4].Interior.Color = System.Drawing.Color.FromArgb(184, 204, 228);
                    worksheet.get_Range("D1", "D1").ColumnWidth = 17.71;
                    
                    worksheet.Cells[1, 5] = "PRINCIPIO ACTIVO";
                    worksheet.Cells[1, 5].Interior.Color = System.Drawing.Color.FromArgb(184, 204, 228);
                    worksheet.get_Range("E1", "E1").ColumnWidth = 13.43;
    
                    worksheet.Cells[1, 6] = "%";
                    worksheet.Cells[1, 6].Interior.Color = System.Drawing.Color.FromArgb(184, 204, 228);
                    worksheet.get_Range("F1", "F1").ColumnWidth = 3;
    
                    worksheet.Cells[1, 7] = "PRINCIPIO ACTIVO";
                    worksheet.Cells[1, 7].Interior.Color = System.Drawing.Color.FromArgb(184, 204, 228);
                    worksheet.get_Range("G1", "G1").ColumnWidth = 9.14;
    
                    worksheet.Cells[1, 8] = "%";
                    worksheet.Cells[1, 8].Interior.Color = System.Drawing.Color.FromArgb(184, 204, 228);
                    worksheet.get_Range("H1", "H1").ColumnWidth = 2;
    
                    worksheet.Cells[1, 9] = "PRINCIPIO ACTIVO";
                    worksheet.Cells[1, 9].Interior.Color = System.Drawing.Color.FromArgb(184, 204, 228);
                    worksheet.get_Range("I1", "I1").ColumnWidth = 10.86;
    
                    worksheet.Cells[1, 10] = "%";
                    worksheet.Cells[1, 10].Interior.Color = System.Drawing.Color.FromArgb(184, 204, 228);
                    worksheet.get_Range("J1", "J1").ColumnWidth = 4;
    
                    // Lista todos los Tratamientos con Status "Pendientes de Realizar"
                    DataTable tablaTratamientos = new DataTable();
                    tablaTratamientos = NTratamientos.GenerarTrimestrales(textBoxDesde.Text, textBoxHasta.Text);
    
                    // Indico que se va a cargar registro de la BD desde la linea 2 del Excel a crear
                    int fila_excel = 2;
                    // Tomo el limite inferior de los valores ingresados por pantalla
                    int indice = int.Parse(textBoxDesde.Text);
    
                    int nroCertificado = 0;
    
                    foreach (DataRow row in tablaTratamientos.Rows)
                    {
                        nroCertificado = int.Parse(row["numCertificado"].ToString());
    
                        if (nroCertificado == indice)
                        {
                            // Cargo registros en el Excel
                            worksheet.Cells[fila_excel, 1] = row["numCertificado"].ToString();
                            // Se agrega "'" para que se respete el formato de la columna
                            worksheet.Cells[fila_excel, 2] = "'" + String.Format("{0:dd/MM/yyyy}", DateTime.Parse(row["fechaTratamiento"].ToString()));
                            worksheet.Cells[fila_excel, 3] = row["nombreCliente"].ToString();
                            worksheet.Cells[fila_excel, 4] = row["direccion"].ToString();
    
                            // Texto Fijo para todos los registros menos los ANULADOS
                            worksheet.Cells[fila_excel, 5] = "HIDRAMETILNONA / CIPERMETRINA";
                            worksheet.Cells[fila_excel, 6] = "2,15 / 2";
                            worksheet.Cells[fila_excel, 7] = "AMONIO CUATERNARIO";
                            worksheet.Cells[fila_excel, 8] = "1";
                            worksheet.Cells[fila_excel, 9] = "BRODIFACUOM";
                            worksheet.Cells[fila_excel, 10] = "0,005";
                        }
                        else
                        {
                            do
                            {
                                if (nroCertificado != indice)
                                {
                                    // Cargo registros en el Excel
                                    worksheet.Cells[fila_excel, 1] = Convert.ToString(indice);
                                    worksheet.Cells[fila_excel, 1].Interior.Color = System.Drawing.Color.FromArgb(0, 255, 0);
                                    worksheet.Cells[fila_excel, 2] = "ANULADO";
                                    worksheet.Cells[fila_excel, 2].Interior.Color = System.Drawing.Color.FromArgb(0, 255, 0);
                                    worksheet.Cells[fila_excel, 3] = "ANULADO";
                                    worksheet.Cells[fila_excel, 3].Interior.Color = System.Drawing.Color.FromArgb(0, 255, 0);
                                    worksheet.Cells[fila_excel, 4] = "ANULADO";
                                    worksheet.Cells[fila_excel, 4].Interior.Color = System.Drawing.Color.FromArgb(0, 255, 0);
    
                                    // Pinto de color verde fluor las lineas para que se vea bien
                                    worksheet.Cells[fila_excel, 5].Interior.Color = System.Drawing.Color.FromArgb(0, 255, 0);
                                    worksheet.Cells[fila_excel, 6].Interior.Color = System.Drawing.Color.FromArgb(0, 255, 0);
                                    worksheet.Cells[fila_excel, 7].Interior.Color = System.Drawing.Color.FromArgb(0, 255, 0);
                                    worksheet.Cells[fila_excel, 8].Interior.Color = System.Drawing.Color.FromArgb(0, 255, 0);
                                    worksheet.Cells[fila_excel, 9].Interior.Color = System.Drawing.Color.FromArgb(0, 255, 0);
                                    worksheet.Cells[fila_excel, 10].Interior.Color = System.Drawing.Color.FromArgb(0, 255, 0);
    
                                    // Incremento las variables para la proxima vuelta
                                    indice++;
                                    fila_excel++;
                                }
                            }
                            while (!(nroCertificado == indice));
                            {
                                // Cargo registros en el Excel
                                worksheet.Cells[fila_excel, 1] = row["numCertificado"].ToString();
                                // Se agrega "'" para que se respete el formato de la columna
                                worksheet.Cells[fila_excel, 2] = "'" + String.Format("{0:dd/MM/yyyy}", DateTime.Parse(row["fechaTratamiento"].ToString()));
                                worksheet.Cells[fila_excel, 3] = row["nombreCliente"].ToString();
                                worksheet.Cells[fila_excel, 4] = row["direccion"].ToString();
    
                                // Texto Fijo para todos los registros menos los ANULADOS
                                worksheet.Cells[fila_excel, 5] = "HIDRAMETILNONA / CIPERMETRINA";
                                worksheet.Cells[fila_excel, 6] = "2,15 / 2";
                                worksheet.Cells[fila_excel, 7] = "AMONIO CUATERNARIO";
                                worksheet.Cells[fila_excel, 8] = "1";
                                worksheet.Cells[fila_excel, 9] = "BRODIFACUOM";
                                worksheet.Cells[fila_excel, 10] = "0,005";
                            }
                        }
                        
                        // Incremento las variables para la proxima vuelta
                        indice++;
                        fila_excel++;
                    }
    
                    // El numero del ultimo Certificado es menor al limite superior ingresado
                    // Tomo el limite superior de los valores ingresados por pantalla
                    do
                    {
                        if (indice < int.Parse(textBoxHasta.Text))
                        {
                            // Cargo registros en el Excel
                            worksheet.Cells[fila_excel, 1] = Convert.ToString(indice);
                            worksheet.Cells[fila_excel, 1].Interior.Color = System.Drawing.Color.FromArgb(0, 255, 0);
                            worksheet.Cells[fila_excel, 2] = "ANULADO";
                            worksheet.Cells[fila_excel, 2].Interior.Color = System.Drawing.Color.FromArgb(0, 255, 0);
                            worksheet.Cells[fila_excel, 3] = "ANULADO";
                            worksheet.Cells[fila_excel, 3].Interior.Color = System.Drawing.Color.FromArgb(0, 255, 0);
                            worksheet.Cells[fila_excel, 4] = "ANULADO";
                            worksheet.Cells[fila_excel, 4].Interior.Color = System.Drawing.Color.FromArgb(0, 255, 0);
    
                            // Pinto de color verde fluor las lineas para que se vea bien
                            worksheet.Cells[fila_excel, 5].Interior.Color = System.Drawing.Color.FromArgb(0, 255, 0);
                            worksheet.Cells[fila_excel, 6].Interior.Color = System.Drawing.Color.FromArgb(0, 255, 0);
                            worksheet.Cells[fila_excel, 7].Interior.Color = System.Drawing.Color.FromArgb(0, 255, 0);
                            worksheet.Cells[fila_excel, 8].Interior.Color = System.Drawing.Color.FromArgb(0, 255, 0);
                            worksheet.Cells[fila_excel, 9].Interior.Color = System.Drawing.Color.FromArgb(0, 255, 0);
                            worksheet.Cells[fila_excel, 10].Interior.Color = System.Drawing.Color.FromArgb(0, 255, 0);
    
                            // Incremento las variables para la proxima vuelta
                            indice++;
                            fila_excel++;
                        }
                        else
                        {
                            if (indice > int.Parse(textBoxHasta.Text))
                                indice = int.Parse(textBoxHasta.Text);
                        }
                    }
                    while (!(indice == int.Parse(textBoxHasta.Text)));
                    {
                        if (nroCertificado != int.Parse(textBoxHasta.Text))
                        {
                            // Cargo registros en el Excel
                            worksheet.Cells[fila_excel, 1] = Convert.ToString(indice);
                            worksheet.Cells[fila_excel, 1].Interior.Color = System.Drawing.Color.FromArgb(0, 255, 0);
                            worksheet.Cells[fila_excel, 2] = "ANULADO";
                            worksheet.Cells[fila_excel, 2].Interior.Color = System.Drawing.Color.FromArgb(0, 255, 0);
                            worksheet.Cells[fila_excel, 3] = "ANULADO";
                            worksheet.Cells[fila_excel, 3].Interior.Color = System.Drawing.Color.FromArgb(0, 255, 0);
                            worksheet.Cells[fila_excel, 4] = "ANULADO";
                            worksheet.Cells[fila_excel, 4].Interior.Color = System.Drawing.Color.FromArgb(0, 255, 0);
    
                            // Pinto de color verde fluor las lineas para que se vea bien
                            worksheet.Cells[fila_excel, 5].Interior.Color = System.Drawing.Color.FromArgb(0, 255, 0);
                            worksheet.Cells[fila_excel, 6].Interior.Color = System.Drawing.Color.FromArgb(0, 255, 0);
                            worksheet.Cells[fila_excel, 7].Interior.Color = System.Drawing.Color.FromArgb(0, 255, 0);
                            worksheet.Cells[fila_excel, 8].Interior.Color = System.Drawing.Color.FromArgb(0, 255, 0);
                            worksheet.Cells[fila_excel, 9].Interior.Color = System.Drawing.Color.FromArgb(0, 255, 0);
                            worksheet.Cells[fila_excel, 10].Interior.Color = System.Drawing.Color.FromArgb(0, 255, 0);
                        }
                    }
    
                    // Grabo el archivo TrabajosPendientesRealizar en Excel
                    SaveFileDialog saveDialog = new SaveFileDialog();
                    saveDialog.FileName = rutaDescarga();
                    worksheet.SaveAs(saveDialog.FileName);
    
                    excel.Quit();
                    workbook = null;
                    worksheet = null;
                    excel = null;
                }
                catch (System.Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
                finally
                {
                    MessageBox.Show("Trimestrales Generados Exitosamente.", "Informacion", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
            }
    El Código no lo escribí yo, lo escribieron los desarrolladores anteriores.

    Skull

    jueves, 7 de noviembre de 2019 15:44
  • Efectivamente, es lo que suponía. Cada uno de los sitios donde hace Worksheet.Cells[algo] supone una llamada entre procesos a través de COM/Interop. Y fíjate la cantidad de ellos que tienes. Desde luego se volverá lento cuando tengas muchas filas. No habrá manera de acelerarlo salvo que te decidas a modificarlo sustancialmente.

    Una opción para acelerarlo es primero meter todos los valores que calculas en un array. Y luego pasar el array de un solo golpe con una única llamada via COM/Interop. Esto vale para el contenido de las celdas, pero luego tienes también otros sitios donde le cambias el color. Aprovechando que es el mismo color para varios bloques de celdas, se puede seleccionar el rango completo y luego cambiarle el color al rango. Esto es más rápido que cambiar el color de cada celda una por una.

    Pero claro, todo esto requiere entender por completo lo que hace el código y cambiarlo de arriba a abajo. Si quieres dejarlo como está o minimizando los cambios, entonces me temo que habrá que resignarse a que sea lento. Con esa estructura la velocidad está limitada por el rendimiento de COM/Interop.

    jueves, 7 de noviembre de 2019 18:28
    Moderador
  • Otra opción además, podría ser tener una matríz del excel y rellenarlo con datos en las filas que correspondan mediante insert, como te comentaron, me parece que tardas más en "decorar" el excel que en cargarle los datos, la idea (ahora no la puedo desarrolar) es tener un excel preparado con las columnas, cabeceras, colores titulos y agregar solamente los datos, este excel puede estar en el path de la misma aplicación y hacer uso de ella.Creo que será sustancialmente más rápido.

    Saludos en cuanto pueda intento un ejemplo.


    ARA San Juan 44 HEROES     ‗‗‗‗­|||||‗‗‗‗‗

    jueves, 7 de noviembre de 2019 20:10
  • Perfecto. Pase los datos a un array, después los exporté y no demora nada y eso que son 5000 datos.

    Gracias :D


    Skull

    jueves, 7 de noviembre de 2019 23:24
  • Hola me puedes explicar como hiciste para pasar un array a Excel o donde encuentro un articulo para hacer eso.

    Gracias!

    martes, 17 de diciembre de 2019 23:14