Unable to generate the .xlsx file

Question

I have a text file which is in tab deliminator and following is my code to generate its 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;
    }

This code works fine since i am generating 2003 excel file (.xls).

But if i am generating a 2007 (.xlsx) by changing the code to

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

i get an error like this.

I did my homework and came to know that this error is because the .xlsx file generated by my program is done by using HTML (markup language) XML (markup language) which should actually be done for a 2007 excel file.

My question is what changes should i do so that i get the desired result i.e. I get the 2007 excel sheet!!!

Answer 1

You have to use extended libraries which I recommend using EPPlus which is a .net library that reads & writes Excel 2007/2010 files using the Open Office Xml format (xlsx). Library

and then replace the code

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

I got this solution here