Link to home
Start Free TrialLog in
Avatar of Eamon
EamonFlag 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

ASKER CERTIFIED SOLUTION
Avatar of Member_2_4913559
Member_2_4913559
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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

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