Avatar of Eamon
Eamon
Flag for Ireland asked on

Issue with Exporting Datatable to Excel

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

Open in new window

.NET ProgrammingMicrosoft SQL ServerASP.NET

Avatar of undefined
Last Comment
Monica P

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Member_2_4913559

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Sammy

You shouldn't used HTML in excel.
here is a better and cleaner solution from
http://stackoverflow.com/questions/7843822/export-datatable-to-excel-file-in-asp-net

string attachment = "attachment; filename=myfile.xls"; 
Response.ClearContent(); 
Response.AddHeader("content-disposition", attachment); 
Response.ContentType = "application/vnd.ms-excel"; 
string tab = ""; 
foreach (DataColumn dc in dt.Columns) 
{ 
    Response.Write(tab + dc.ColumnName); 
    tab = "\t"; 
} 
Response.Write("\n"); 
int i; 
foreach (DataRow dr in dt.Rows) 
{ 
    tab = ""; 
    for (i = 0; i < dt.Columns.Count; i++) 
    { 
        Response.Write(tab + dr[i].ToString()); 
        tab = "\t"; 
    } 
    Response.Write("\n"); 
} 
Response.End();

Open in new window

Monica P

I have used this...Give a try on this ..it may help you

Public Sub Export(ByVal dt As System.Data.DataTable, ByVal filepath As String, ByVal tablename As String)
        'excel 2003
        Dim connString As String = ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
                    + (filepath + ";Extended Properties=Excel 8.0;"))
        'Excel 2007
        'string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
        '       filepath + ";Extended Properties=Excel 12.0 Xml;";
        Try
            Dim con As System.Data.OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection(connString)
            con.Open()
            Dim strSQL As StringBuilder = New StringBuilder
            strSQL.Append("CREATE TABLE ").Append(("[" _
                            + (tablename + "]")))
            strSQL.Append("(")
            Dim i1 As Integer = 0
            Do While (i1 < dt.Columns.Count)
                strSQL.Append(("[" _
                                + (dt.Columns(i1).ColumnName + "] text,")))
                i1 = (i1 + 1)
            Loop
            strSQL = strSQL.Remove((strSQL.Length - 1), 1)
            strSQL.Append(")")
            Dim cmd As OleDbCommand = New OleDbCommand(strSQL.ToString, con)
            cmd.ExecuteNonQuery()
            Dim i As Integer = 0
            Do While (i < dt.Rows.Count)
                strSQL.Length = 0
                Dim strfield As StringBuilder = New StringBuilder
                Dim strvalue As StringBuilder = New StringBuilder
                Dim j As Integer = 0
                Do While (j < dt.Columns.Count)
                    strfield.Append(("[" _
                                    + (dt.Columns(j).ColumnName + "]")))
                    strvalue.Append(("'" _
                                    + (dt.Rows(i)(j).ToString + "'")))
                    If (j _
                                <> (dt.Columns.Count - 1)) Then
                        strfield.Append(",")
                        strvalue.Append(",")
                    Else

                    End If
                    j = (j + 1)
                Loop
                cmd.CommandText = strSQL.Append((" insert into [" _
                                + (tablename + "]( "))).Append(strfield.ToString).Append(") values (").Append(strvalue).Append(")").ToString
                cmd.ExecuteNonQuery()
                i = (i + 1)
            Loop
            con.Close()
            ' Console.WriteLine("OK")
        Catch ex As Exception

        End Try
    End Sub
Your help has saved me hundreds of hours of internet surfing.
fblack61