Export large amount of data to Excel

Asked

Viewed 361 times

2

I have a DataGridView where in it there is an average of about 50 thousand lines where these should be exported to an Excel file.

But because of the huge amount of data the application simply freezes and does not end, I am using the library Microsoft.Office.Interop.Excel the button code is this:

 private void btnExportar_Click(object sender, EventArgs e)
    {
        //exportando arquivos para o excel
        if (dgvDados.Rows.Count > 0)
        {
            try
            {
                XcelApp.Application.Workbooks.Add(Type.Missing);
                for (int i = 1; i < dgvDados.Columns.Count + 1; i++)
                {
                    XcelApp.Cells[1, i] = dgvDados.Columns[i - 1].HeaderText;
                }
                //
                for (int i = 0; i < dgvDados.Rows.Count - 1; i++)
                {
                    for (int j = 0; j < dgvDados.Columns.Count; j++)
                    {
                        XcelApp.Cells[i + 2, j + 1] = dgvDados.Rows[i].Cells[j].Value.ToString();
                    }
                }
                //
                XcelApp.Columns.AutoFit();
                //
                XcelApp.Visible = true;
            }
            catch (Exception ex)
            {
                MessageBox.Show("Erro : " + ex.Message);
                XcelApp.Quit();
            }
        }
    }

Is there any way to export something from this level to excel?

c# winforms excel

  • It tries to create Paggings, ex: if there are 50 thousand lines it makes him go to the bank and search every 10 thousand, opening several requests within each pagination.

  • The application is freezing because it is processing a lot in the main thread, if you create a new thread to run it the application will not freeze

  • Explain better what you mean by "lock", some error occurs, the process does not end or only takes time and the user interface freezes until the finish?

  • @Leandroangelo the interface freezes, but I’ve never seen it finish and I’ve left a matter of 1 h and still remains frozen

  • debugging, you measured how long it is taking per line, although the large volume does not have as much complexity as...

  • I did the test now by line ended up being very variable, for example when I open the application and export for the first time it takes about 5s but after running the first time it takes around 0.5s. I tested with 7mil lines and took 15 min

  • the same problem is the application stay frozen without being able to do anything, I believe thread is really a good option but do not know mt well the feature I will give a search

  • but still if follow by this reasoning 7 thousand lines = 15 min 50 thousand lines =~ 2h is long time

Show 3 more comments

2 answers

1


You can use the Clipboard to do the operation. For this amount of data will also take a little while, but it will be much faster than copying cell by cell

    private void btnExportar_Click(object sender, EventArgs e)
    {
        if (dgvDados.Rows.Count == 0) return;

        bool RowHeadersVisible = dgvDados.RowHeadersVisible;

        //Seleciona todas linhas de dgvDados e passa para o clipboard
        dgvDados.RowHeadersVisible = false;
        dgvDados.SelectAll();
        DataObject dataObj = dgvDados.GetClipboardContent();
        if (dataObj != null) Clipboard.SetDataObject(dataObj);

        dgvDados.RowHeadersVisible = RowHeadersVisible;

        //Declara as variáveis para usar para referir aos elementos do excel 
        Microsoft.Office.Interop.Excel.Application xlexcel;
        Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
        Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;

        //Cria uma nova instanci do excel
        xlexcel = new Excel.Application();
        xlexcel.Visible = true;

        //Adiciona um workbook
        xlWorkBook = xlexcel.Workbooks.Add(System.Reflection.Missing.Value);

        //Pega a primeira planilha do workbook
        xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

        //Seleciona a primeira célula
        Excel.Range CR = (Excel.Range)xlWorkSheet.Cells[1, 1];
        CR.Select();

        //Cola o conteúdo do clipboard
        xlWorkSheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true);          
    }

  • By the amount of data would be better to use Oledb, one example here.

  • what library I should call with the using to use the namespace Excel ?

  • https://docs.microsoft.com/pt-br/dotnet/api/microsoft.office.interop.excel?view=excel-pia

  • 1

    very good, what was almost an estimated of 3h now makes in 45s glaring improvement, since so it selects all lines at once is much more efficient

1

If your goal is not to freeze the user interface, you can start a new Task to accomplish this task, but this will not solve the question of time for the execution.

private void btnExportar_Click(object sender, EventArgs e)
{
    //exportando arquivos para o excel
    if (dgvDados.Rows.Count > 0)
    {
       var task = Task.Factory.StartNew(() =>
        {
            try
            {

                XcelApp.Application.Workbooks.Add(Type.Missing);
                for (int i = 1; i < dgvDados.Columns.Count + 1; i++)
                {
                    XcelApp.Cells[1, i] = dgvDados.Columns[i - 1].HeaderText;
                }
                //
                for (int i = 0; i < dgvDados.Rows.Count - 1; i++)
                {
                    for (int j = 0; j < dgvDados.Columns.Count; j++)
                    {
                        XcelApp.Cells[i + 2, j + 1] = dgvDados.Rows[i].Cells[j].Value.ToString();
                    }
                }
                //
                XcelApp.Columns.AutoFit();
                //
                XcelApp.Visible = true;

                MessageBox.Show("Sucesso : Exportação concluída");
            }
            catch (Exception ex)
            {
                MessageBox.Show("Erro : " + ex.Message);
                XcelApp.Quit();
            }
        });
    }
}

Browser other questions tagged c# winforms excel

You are not signed in. Login or sign up in order to post.