将数据表数据导出到excel不起作用 [英] Export data table data to excel is not working

问题描述

导出数据表数据到excel功能不起作用。我发现以下代码存在任何问题，并且没有产生任何错误。

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.

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

推荐答案

这是因为您甚至没有尝试以Excel格式编写任何内容。如果您使用Excel的内容类型，则只是意味着您歪曲了您在HTTP响应中写入的数据，实际上这是一些以制表符分隔和行尾分隔的内容。如果您启动Excel应用程序并使用此文件进行导入，它将起作用，但其内容类型必须是text / plain。



如果您创建一个文件这样的内容并命名为test.txt，Excel会打开它，但如果你将它命名为test.xls，Excel会说：Excel无法打开文件'test.xlsx'，因为文件扩展名的文件格式无效。请验证文件是否已损坏，文件扩展名是否与文件格式匹配。如果您将内容保存在Excel文件中，则会发生这种情况;如果你选择通过URL打开Excel内容，它可能很简单 - 你没有报告问题是如何表现出来的，当然，我从来没有尝试做过这么奇怪的事情。



怎么办？如果您声称内容是text / plain，那么它也可能不够好，因为您不希望浏览器在浏览器页面中打开它（但是，您的处置标头可能会阻止它）。也许您必须提供本机Excel格式，即Microsoft Open XML格式。请参阅：

创建基本的Excel工作簿Open XML ，

如何从中添加microsoft excel 15.0对象库在MS Visual Studio 2010中添加引用。



如果要查找更接近问题的代码示例，搜索的好地方之一是CodeProject。



我刚发现这个演示，请看：将数据写入ASP.Net中的Excel文件（.xls和.xlsx）。

-SA

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

这篇关于将数据表数据导出到excel不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！