Click here to Skip to main content
15,868,016 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Export Data Table data to excel functionality is not working. And i dint found any issue with the below code as well as it's not generating any error.



C#
string XlsPath = Server.MapPath(@"~/Add_data/test.xls");
        string attachment = string.Empty;
        if (XlsPath.IndexOf("\\") != -1)
        {
            string[] strFileName = XlsPath.Split(new char[] { '\\' });
            attachment = "attachment; filename=" + strFileName[strFileName.Length - 1];
        }
        else
            attachment = "attachment; filename=" + XlsPath;
        try
        {
            Response.ClearContent();
            Response.AddHeader("content-disposition", attachment);
            Response.ContentType = "application/vnd.ms-excel";
            string tab = string.Empty;

            foreach (DataColumn datacol in dtRecords.Columns)
            {
                Response.Write(tab + datacol.ColumnName);
                tab = "\t";
            }
            Response.Write("\n");

            foreach (DataRow dr in dtRecords.Rows)
            {
                tab = "";
                for (int j = 0; j < dtRecords.Columns.Count; j++)
                {
                    Response.Write(tab + Convert.ToString(dr[j]));
                    tab = "\t";
                }

                Response.Write("\n");
            }
            //Response.End();
            HttpContext.Current.ApplicationInstance.CompleteRequest();
        }
        catch (Exception ex)
        {
            Response.Write(ex.Message);
        }
Posted
Updated 8-Jun-16 19:59pm
v2
Comments
koolprasad2003 9-Jun-16 2:07am    
What is the error ?

1 solution

This is because you are not even trying to write anything in Excel format. If you use Excel's content type, it simply means that you misrepresent the data you write in your HTTP response, which is in fact some tab-separated and end-of-line-separated content. It would work if you started Excel application and used this file for import, but then its content type would have to be text/plain.

If you create a file with such content and name it as, say, test.txt, Excel will open it, but if you name it test.xls, Excel says: "Excel cannot open the file 'test.xlsx', because the file format of the file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file". This is what would observe if you save content in an Excel file; if you choose to open content with Excel via the URL, it could be something simple — you did not report how the problem is manifested, and, naturally, I never tried to do such weird thing.

What to do? If you claim the content is "text/plain", it also might be not good enough, because you don't want the browser to open it in the browser page (however, your disposition header can prevent it). Maybe you have to deliver native Excel format, which is Microsoft Open XML format. Please see:
Creating basic Excel workbook with Open XML,
How to add microsoft excel 15.0 object library from Add Reference in MS Visual Studio 2010.

If you want to find some code sample closer to your problem, one of the good places to search for is CodeProject.

I just found this demo, please see: Write data to Excel file (.xls and .xlsx) in ASP.Net.

—SA
 
Share this answer
 
v4

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS


CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900