0 votes

How to generate an Excel from dataGridView faster with C#

I have an application in which in a dataGridView1 it shows me information of a stored procedure. And I, by means of a button what I do is to take that information of the dataGridView and I convert it to an Excel.

I did a test with 1520 records (rows) and it takes a little time (about 5 minutes), I know it may be because of the amount of information that has to be converted, but I would like to know if there is another way to do it that is more optimal because then I will have to transfer more information to excel, this is my code:

private void button1_Click(object sender, EventArgs e)
{
    SaveFileDialog fichero = new SaveFileDialog();
    fichero.Filter = "Excel (*.xls)|*.xls";
    if (fichero.ShowDialog() == DialogResult.OK)
    {
        Microsoft.Office.Interop.Excel.Application aplicacion;
        Microsoft.Office.Interop.Excel.Workbook libros_trabajo;
        Microsoft.Office.Interop.Excel.Worksheet hoja_trabajo;
        aplicacion = new Microsoft.Office.Interop.Excel.Application();
        libros_trabajo = aplicacion.Workbooks.Add();
        hoja_trabajo =
            (Microsoft.Office.Interop.Excel.Worksheet)libros_trabajo.Worksheets.get_Item(1);

        //Recorremos el DataGridView rellenando la hoja de trabajo
        for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
        {
            for (int j = 0; j < dataGridView1.Columns.Count; j++)
            {
                hoja_trabajo.Cells[i + 1, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();
            }
        }
        libros_trabajo.SaveAs(fichero.FileName,
            Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal);
        libros_trabajo.Close(true);
        aplicacion.Quit();
    }
}

Thank you very much.

1voto

Jonathan Aguilar Points 79

If you want to export in excel the content of a GridView you can use as a resource the class StringWriter

public void exportarExcel()
    {
        Response.Clear();
        Response.Buffer = true;
        Response.Charset = "";
        Response.AddHeader("content-disposition", "attachment;filename=NombreArchivo.xls");
        Response.ContentType = "application/ms-excel";
        StringWriter sw = new StringWriter();
        HtmlTextWriter hw = new HtmlTextWriter(sw);
        GridView1.AllowPaging = false;
        GridView1.DataBind();
        GridView1.RenderControl(hw);
        Response.Output.Write(sw.ToString());
        Response.Flush();
        Response.End();
    }

    public override void VerifyRenderingInServerForm(Control control)
    {
        return;
    }

The performance when exporting the DataSet is much better.

Greetings.

HolaDevs.com

HolaDevs is an online community of programmers and software lovers.
You can check other people responses or create a new question if you don't find a solution

Powered by:

X