title: Creating Excel sheet from SQL server table data
body: <p>I'm dynamically creating an Excel sheet from data retrieved from SQL Server 
  with a button click and a function. Everything works fine at the moment as I have some 300 odd records but I am concerned about performance for more records. </p>
  
  <p>If I have some 10,000 records what modifications do I need to do to the code, so that I don't encounter any error while creating the page dynamically?</p>
  
  <p>Below is the code I used :</p>
  
  <pre><code>protected void Button1_Click(object sender, EventArgs e)
  {
      using (SqlConnection con = new SqlConnection(connectionString))
      {
  
          SqlCommand cmd = new SqlCommand("usp_myReport", con);
          cmd.CommandType = CommandType.StoredProcedure;
          SqlDataAdapter da = new SqlDataAdapter(cmd);
          DataTable dt = new DataTable();
          da.Fill(dt);
          Excel_FromDataTable(dt);
          Label1.Text = "Excel file created";
      }
  }
  </code></pre>
  
  <p>Below is the function: </p>
  
  <pre><code>private static void Excel_FromDataTable(DataTable dt)
  {
  
      Excel.Application excel = new Excel.Application();
      Excel.Workbook workbook = excel.Application.Workbooks.Add(true);
  
      int iCol = 0;
      string[] colNames = new string[dt.Columns.Count];
      foreach (DataColumn c in dt.Columns)
      {
          colNames[iCol++] = c.ColumnName;
          char lastColumn = (char)(65 + dt.Columns.Count - 1);
          excel.get_Range("A1", lastColumn + "1").Value2 = colNames;
         }
  
      int iRow = 0;
      foreach (DataRow r in dt.Rows)
      {
          iRow++;
                      iCol = 0;
          foreach (DataColumn c in dt.Columns)
          {
              iCol++;
              excel.Cells[iRow + 1, iCol] = r[c.ColumnName];
          }
      }
  
      object missing = System.Reflection.Missing.Value;
  
      workbook.SaveAs("MyExcelWorkBook.xls",
          Excel.XlFileFormat.xlXMLSpreadsheet, missing, missing,
          false, false, Excel.XlSaveAsAccessMode.xlNoChange,
          missing, missing, missing, missing, missing);
      excel.Visible = true;
      Excel.Worksheet worksheet = (Excel.Worksheet)excel.ActiveSheet;
      ((Excel._Worksheet)worksheet).Activate();
      ((Excel._Application)excel).Quit();
  
  }
  </code></pre>