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 foreseeing long term use whats the best practice?
If I have some 10000 records what modifications do I need to do to the code, so that I don't encounter any error while creating the page dynamically.
Below is the code i used :
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";
}
}
Below is the function
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();
}