无法生成.xlsx文件

Question

我有一个文本文件，它位于制表符分隔符中，下面是生成其Excel的代码。

protected void to_excel(object sender, EventArgs e)
    {
        string filepath = Path.Combine(Server.MapPath("~/Files"), fileupload.FileName);
        fileupload.SaveAs(filepath);
        string fname = fileupload.PostedFile.FileName;
        DataTable dt = (DataTable)ReadToEnd(filepath);
        string sFilename = fname.Substring(0, fname.IndexOf("."));
        HttpResponse response = HttpContext.Current.Response;
        Response.Clear();
        Response.AddHeader("content-disposition", "attachment;filename=" + sFilename + ".xls");
        Response.Charset = "";
        Response.Cache.SetCacheability(HttpCacheability.NoCache);
        Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
        System.IO.StringWriter stringWrite = new System.IO.StringWriter();
        System.Web.UI.HtmlTextWriter htmlWrite = new System.Web.UI.HtmlTextWriter(stringWrite);
        System.Web.UI.WebControls.DataGrid dg = new System.Web.UI.WebControls.DataGrid();
        dg.DataSource = dt;
        dg.DataBind();
        dg.RenderControl(htmlWrite);
        Response.Write(stringWrite.ToString());
        Response.End();
    }
    private object ReadToEnd(string filePath)
    {
        DataTable dtDataSource = new DataTable();
        string[] fileContent = File.ReadAllLines(filePath);
        if (fileContent.Count() > 0)
        {
            string[] columns = fileContent[0].Split('\t');
            for (int i = 0; i < columns.Count(); i++)
            {
                dtDataSource.Columns.Add(columns[i]);
            }
            for (int i = 1; i < fileContent.Count(); i++)
            {
                string[] rowData = fileContent[i].Split('\t');
                dtDataSource.Rows.Add(rowData);
            }
        }
        return dtDataSource;
    }

这段代码运行良好，因为我正在生成2003年的excel文件(.xls)。

但是如果我通过将代码更改为生成2007 (.xlsx)

Response.AddHeader("content-disposition", "attachment;filename=" + sFilename + ".xlsx");

我得到了一个error like this。

我做了一些准备工作，才知道这个错误是因为我的程序生成的.xlsx文件是使用超文本标记语言(markup language) XML (markup language)生成的，这实际上应该是针对2007EXCEL文件所做的。

我的问题是，我应该做哪些更改才能得到想要的结果，即我得到了2007年的excel工作表！

Answer 1

您必须使用扩展库，我推荐使用EPPlus，这是一个使用Open Office Xml format (xlsx)读写Excel2007/2010文件的.net库。Library

然后替换代码

protected void to_excel(object sender, EventArgs e)
    {
        string filepath = Path.Combine(Server.MapPath("~/Files"), fileupload.FileName);
        fileupload.SaveAs(filepath);
        string fname = fileupload.PostedFile.FileName;
        DataTable dt = (DataTable)ReadToEnd(filepath);
        string sFilename = fname.Substring(0, fname.IndexOf("."));
        sFilename = sFilename + ".xlsx";
        MemoryStream ms = DataTableToExcelXlsx(dt, "Sheet1");
        ms.WriteTo(HttpContext.Current.Response.OutputStream);
        HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
        HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=" + sFilename);
        HttpContext.Current.Response.StatusCode = 200;
        HttpContext.Current.Response.End();
    }

public void toexcel(DataTable dt, string Filename)
    {
        MemoryStream ms = DataTableToExcelXlsx(dt, "Sheet1");
        ms.WriteTo(HttpContext.Current.Response.OutputStream);
        HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
        HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=" + Filename);
        HttpContext.Current.Response.StatusCode = 200;
        HttpContext.Current.Response.End();
    }
    public bool IsReusable
    {
        get { return false; }
    }
    public static MemoryStream DataTableToExcelXlsx(DataTable table, string sheetName)
    {
        MemoryStream Result = new MemoryStream();
        ExcelPackage pack = new ExcelPackage();
        ExcelWorksheet ws = pack.Workbook.Worksheets.Add(sheetName);
        int col = 1;
        int row = 1;
        foreach (DataColumn column in table.Columns)
        {
            ws.Cells[row, col].Value = column.ColumnName.ToString();
            col++;
        }
        col = 1;
        row = 2;
        foreach (DataRow rw in table.Rows)
        {
            foreach (DataColumn cl in table.Columns)
            {
                if (rw[cl.ColumnName] != DBNull.Value)
                    ws.Cells[row, col].Value = rw[cl.ColumnName].ToString();
                col++;
            }
            row++;
            col = 1;
        }
        pack.SaveAs(Result);
        return Result;
    }

我得到了这个解决方案here

Answer 2

您可以使用DataSet创建包含多个工作表的Excel

using Excel = Microsoft.Office.Interop.Excel;
using Microsoft.Office.Interop.Excel;
using System.Data;
using System.IO;

[HttpPost]
    public ActionResult CreateExcel(int id)
    {
        DataSet dataSet = new DataSet(); //Your Data Set
        if (dataSet.Tables.Count > 0)
        {
            System.Data.DataTable dt1 = new System.Data.DataTable();
            System.Data.DataTable dt2 = new System.Data.DataTable();
            dt1 = dataSet.Tables[0];
            dt2 = dataSet.Tables[1];

            if (dt1.Rows.Count > 1 && dt2.Rows.Count > 1)
            {
                var excel = new Microsoft.Office.Interop.Excel.Application();
                var workbook = excel.Workbooks.Add(true);

                AddExcelSheet(dt1, workbook);
                AddExcelSheet(dt2, workbook);

                //KK-Save the excel file into server path
                string strLocation = "~/Upload/TempExcel/";
                string fName = "Report_" + DateTime.Now.ToString("MMddyyyyHHmmss") + ".xlsx";
                string strPath = Path.Combine(Server.MapPath(strLocation), fName);
                workbook.SaveAs(strPath);
                workbook.Close();

                //KK-Generate downloading link view page
                System.Web.HttpResponse Response = System.Web.HttpContext.Current.Response;
                Response.ClearContent();
                Response.Clear();
                //Response.ContentType = "application/vnd.ms-excel";  //This is for office 2003
                Response.ContentType = "application/application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                Response.AppendHeader("Content-Disposition", "attachment; filename=YourReport.xlsx");
                Response.TransmitFile(strPath);
                Response.Flush();
                Response.End();

                //KK-Deleting the file after downloading
                if (System.IO.File.Exists(strPath))
                    System.IO.File.Delete(strPath);
            }
        }

        return View();
    }

    /// <summary>
    /// KK-This method add new Excel Worksheet using DataTable
    /// </summary>
    /// <param name="ds"></param>
    private static void AddExcelSheet(System.Data.DataTable dt, Workbook wb)
    {
        Excel.Sheets sheets = wb.Sheets;
        Excel.Worksheet newSheet = sheets.Add();

        int iCol = 0;

        foreach (DataColumn c in dt.Columns)
        {
            iCol++;
            newSheet.Cells[1, iCol] = c.ColumnName;
            newSheet.Cells[1, iCol].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.RoyalBlue);
            newSheet.Cells[1, iCol].Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White);
            newSheet.Cells[1, iCol].Font.Bold = true;
            newSheet.Cells[1, iCol].BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin);
        }

        int iRow = 0;
        foreach (DataRow r in dt.Rows)
        {
            iRow++;

            // add each row's cell data...
            iCol = 0;

            foreach (DataColumn c in dt.Columns)
            {
                iCol++;
                newSheet.Cells[iRow + 1, iCol] = r[c.ColumnName];
                newSheet.Cells[iRow + 1, iCol].BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin);
            }
        }

    }

Answer 3

你应该检查一下OpenXML SDK：http://msdn.microsoft.com/en-us/library/office/gg278328.aspx

或者，您可以查看此处列出的各种商业库：http://polymathprogrammer.com/spreadsheetopenxml/spreadsheetcodelibrariescomparison.pdf

我对Aspose Cells有一个很好的体验。

PS:你的代码生成的不是有效的xls文件，而是一个能被excel正确解释的html。