troubleshooting Question

Issue with Exporting Datatable to Excel

Avatar of Eamon
EamonFlag for Ireland asked on
Microsoft SQL Server.NET ProgrammingASP.NET
3 Comments1 Solution974 ViewsLast Modified:
I have the below code which prevents me losing the preceding zeros in a cell when exporting a Datatable from asp.net to excel. My Datatable returns 30,000 records. The code runs fines and creates the file. However when I go to open the file - It takes 10 minutes to open and then when it does finally open it doesn't include all the records. I put a variable (countofRows) in my code just to check all rows were looped through and they were.

Any ideas? Also if anyone has any better ideas to export to excel I would be delighted to try them. Previous to this I had an RDLC report but this would time out when I'd export to Excel.

Thanks.

SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString);
        DataTable dt = new DataTable();

        string product = ddl_CreditProduct.SelectedValue;
        string salesChannel = ddl_SalesChannel.SelectedValue;
        string finalEligibilityStatusID = ddl_FinalEligibilityStatus.SelectedValue;


        string queryStr = "sp_Export_GetCasesByProduct '', '', ''";
        SqlDataAdapter sda = new SqlDataAdapter(queryStr, conn);
        sda.Fill(dt);

        if (dt != null)
        {
            string filename = "CasesByProduct_" + ddl_CreditProduct.SelectedItem.Text + "_Export_on_" + DateTime.Now.ToString("dd-MM-yyyy hh_mm");
            string attach = "attachment; filename=" + filename + ".xls";



            HttpContext.Current.Response.Clear();
            HttpContext.Current.Response.ClearContent();
            HttpContext.Current.Response.ClearHeaders();
            HttpContext.Current.Response.Buffer = true;
            HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
            HttpContext.Current.Response.Write(@"<!DOCTYPE HTML PUBLIC ""-//W3C//DTD HTML 4.0 Transitional//EN"">");
            HttpContext.Current.Response.AddHeader("Content-Disposition", attach);

            HttpContext.Current.Response.Charset = "utf-8";
            HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("windows-1250");
            

            //sets the table border, cell spacing, border color, font of the text, background, foreground, font height
            HttpContext.Current.Response.Write("<Table> <TR>");
            //am getting my grid's column headers
            

            foreach (DataColumn dc in dt.Columns)
            {
                HttpContext.Current.Response.Write("<Td>");
                //Get column headers  and make it as bold in excel columns
                HttpContext.Current.Response.Write("<B>");
                HttpContext.Current.Response.Write(dc.ColumnName);
                HttpContext.Current.Response.Write("</B>");
                HttpContext.Current.Response.Write("</Td>");
            }
            
            HttpContext.Current.Response.Write("</TR>");


            int countofRows = 0;
            foreach (DataRow dr in dt.Rows)
            {
                HttpContext.Current.Response.Write("<TR>");
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    string myRow = dr[i].ToString();
                    if (myRow.ToString().Length > 0 && myRow.ToString().Substring(0, 1) == "0")
                    {
                        HttpContext.Current.Response.Write(@"<Td style='mso-number-format:\@'>");
                    }
                    else
                    {
                        HttpContext.Current.Response.Write("<Td>");
                    }
                    
                    HttpContext.Current.Response.Write(myRow.ToString());
                    HttpContext.Current.Response.Write("</Td>");
                    
                }
                HttpContext.Current.Response.Write("</TR>");
                countofRows += 1;
            }

            
            HttpContext.Current.Response.Write("</Table>");
            HttpContext.Current.Response.Flush();
            HttpContext.Current.Response.End();
ASKER CERTIFIED SOLUTION
ddayx10

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros