Member Avatar for nav07

This is my code, but its taking time to save record in excel. In SQL Server table have 11201 rows and 80 columns.

please suggest

 private void button1_Click(object sender, EventArgs e)
        {
            Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
            Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
            app.Visible = true;
            worksheet = workbook.Sheets["Sheet1"];
            worksheet = workbook.ActiveSheet;
            worksheet.Name = "Exported from gridview";
            for (int i = 1; i < dataGridView1.Columns.Count + 1; i++)
            {
                worksheet.Cells[1, i] = dataGridView1.Columns[i - 1].HeaderText;
            }
            for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
            {
                for (int j = 0; j < dataGridView1.Columns.Count; j++)
                {
                    worksheet.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();
                }
            }
            workbook.SaveAs("d:\\output.xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            workbook.Close();
            app.Quit();



            MessageBox.Show("Its saved");
        }
Edited by Dani because: Formatting fixed
  • 2 Contributors
  • 3 Replies
  • 229 Views
  • 9 Hours Discussion Span
  • Latest Post Latest Post by hericles

Dani AI

Generated

— the slowdown comes from doing a separate COM call for every cell. With 11,201 rows × 80 columns (~896k cells) each worksheet.Cells[...] write becomes a round-trip and that is why it feels painfully slow. is on the right track: avoid per-cell Interop and avoid iterating the on‑screen DataGridView if you can read directly from SQL.

Faster options (ordered by typical speed and reliability):

  • Produce a CSV by streaming rows from the database (or DataGridView) to disk. Very fast, minimal memory, and Excel will open it. Escape commas/quotes/newlines.
  • Build a single object[,] in memory and assign it once via Range.Value2. That converts ~900k COM calls into one.
  • Use a native .xlsx library (ClosedXML, EPPlus, NPOI) to write XLSX directly without Excel installed — fast and stable.
  • Export directly from SQL (SqlDataReader -> stream to CSV or DataTable -> ClosedXML). Bypassing the grid removes UI-cost and extra copies.

Example: bulk-write to Excel in one assignment

// build a single 2D object array (headers + data)
object[,] data = new object[rows + 1, cols];
for(int c = 0; c < cols; c++) data[0, c] = headers[c];
for(int r = 0; r < rows; r++)
  for(int c = 0; c < cols; c++)
    data[r + 1, c] = grid.Rows[r].Cells[c].Value ?? "";
worksheet.Range[worksheet.Cells[1,1], worksheet.Cells[rows+1,cols]].Value2 = data;

CSV streaming pattern (very fast):

using(var sw = new StreamWriter(@"d:\output.csv", false, Encoding.UTF8))
{
  sw.WriteLine(string.Join(",", headers.Select(h => Quote(h))));
  while(reader.Read()) sw.WriteLine(string.Join(",", Enumerable.Range(0,cols).Select(i => Quote(reader.GetValue(i)?.ToString()))));
}
string Quote(string s) => "\"" + (s ?? "").Replace("\"","\"\"") + "\"";

Extra tips: set app.ScreenUpdating = false, turn off automatic calculation, save as .xlsx when possible (older .xls has a 65,536 row limit), and always release COM objects with Marshal.ReleaseComObject followed by GC.Collect() to avoid orphan Excel processes.

Recommended Answers

All 3 Replies

Member Avatar for hericles

Well, you're making it do 11201 * 80 reads from the grid view so that simply will take time. Is the problem that it is timing out or are you simply looking for a way to make it faster?

Member Avatar for nav07

Well, you're making it do 11201 * 80 reads from the grid view so that simply will take time. Is the problem that it is timing out or are you simply looking for a way to make it faster?

I am looking faster way.....

Member Avatar for hericles

Do you even need the grid view? I doubt you are viewing 11000+ rows on the screen. A direct export from the database to XML would be much faster. This is assuming the file doesn't NEED to be a .xls however. If you did that you would, of course, end up with an XML file (which can be opened in excel).

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.