Uploading data from DataGridView to Excel

Question

Maybe someone knows how to optimize the code, because when unloading about 20-30 thousand lines, the process hangs for quite a long time.

for (int x = 0; x < DataGrid.Rows.Count; x++) { DataGridViewRow row = DataGrid.Rows[x]; for (int y = 0; y < row.Cells.Count; y++) { Excel.Cells[x + 2, y + 1] = row.Cells[y].Value; } }

There is nothing to optimize, you can only run it in a separate stream so that the interface does not hang. Or here’s another solution, try: How to export dataGridView data Instantly to Excel on button click?
@ beliy26rus, original advice ... I know about the streams, but in this case, they would not help me, because
In general, I did it through the Range, as I had already suggested, checked it on 100 thousand lines - it was unloaded in less than 5 seconds!

Accepted Answer · 2014-05-08T07:16:01

You drive all the data of the grid into the array Variant (ArrayData: Variant), then you copy them to Excel, for example (delphi):

  for i := 2 to (DM.IBQ_main.RecordCount + 1) do begin ArrayData[i, 1] := DM.IBQ_main.FieldByName('ID_0').AsString; ArrayData[i, 2] := DM.IBQ_main.FieldByName('ID').AsString; DM.IBQ_main.Next; end; // Левая верхняя ячейка области, в которую будем выводить данные Cell1 := EA1.Cells.Item[1, 1]; // Правая нижняя ячейка области, в которую будем выводить данные Cell2 := EA1.Cells.Item[DM.IBQ_main.RecordCount + 1, 49]; // Область, в которую будем выводить данные Range := EA1.Range[Cell1, Cell2]; Range.Value2 := ArrayData;

Thanks for the example, I understood the idea, I will try to work with Rang.

Uploading data from DataGridView to Excel

1 answer 1

More articles: