Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

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();

}
share|improve this question
1  
Have you tested yourself by puting 10000 records? –  Dhaval Patel Jun 16 at 12:46
    
@Dhavel --At the moment i don't have that many records ... but as said earlier foreseeing future use.. want to make code stable... without pagetimeout .. code execution taking long... –  Phanindra Jun 16 at 12:49
2  
As a general point you might want to take a look at the EPPlus package rather than doing the COM automation of Excel yourself. –  PhilPursglove Jun 16 at 12:52
    
@PhilPursglove i want to use something native ... –  Phanindra Jun 16 at 13:12
    
@Phanindra OK, just trying to save you some work/pain... –  PhilPursglove Jun 16 at 16:00
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.