Not able to read excel sheet saved as xls using closedxml

Question

I have the below code to save the data in excel sheet as .xls

    public ActionResult ExportToExcel()
    {
        DataTable tbl = CopyGenericToDataTable(res);
        tbl.TableName = "InvalidInvoices";


        using (XLWorkbook wb = new XLWorkbook())
        {
            wb.Worksheets.Add(tbl);
            wb.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
            wb.Style.Font.Bold = true;

            Response.Clear();
            Response.Buffer = true;
            Response.Charset = "";
            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            Response.AddHeader("content-disposition", "attachment;filename= "+fileName + ".xls");

            using (MemoryStream MyMemoryStream = new MemoryStream())
            {
                wb.SaveAs(MyMemoryStream);
                MyMemoryStream.WriteTo(Response.OutputStream);
                Response.Flush();
                Response.End();
            }
        }
    }

Above is code which download the xls excel sheet at client side. It works fine the data gets saved in excel sheet. Problem is if I try to upload this same file using below code -

       if (files != null)
       {
            HttpPostedFileBase upload = files.FirstOrDefault();
            Stream stream = upload.InputStream;
            DataSet result = new DataSet();
            if (upload != null && upload.ContentLength > 0)
            {
               if (upload.FileName.EndsWith(".xls") || upload.FileName.EndsWith(".xlsx"))
               {
                  // ExcelDataReader works with the binary Excel file, so it needs a FileStream
                  // to get started. This is how we avoid dependencies on ACE or Interop:
                 // We return the interface, so that

                 IExcelDataReader reader = null;

                 if (upload.FileName.EndsWith(".xls"))
                 {
                      reader = ExcelReaderFactory.CreateBinaryReader(stream);
                 }
                 else if (upload.FileName.EndsWith(".xlsx"))
                 {
                      reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
                 }

                 reader.IsFirstRowAsColumnNames = false;
                 result = reader.AsDataSet();
                 reader.Close();
               }
           }
 }

In above code I am getting error in ExcelReaderFactory.CreateBinaryReader(stream); In stream it has the values in bytes too just on using createBinaryreader of excelreaderfactory reader has error message as 'Invalid file signature'. Any help will be highly appreciated.

Francois Botha Francois Botha · Accepted Answer · 2017-01-21T17:46:07

ClosedXML generates .xlsx files, not .xls files.

Check your code:

Response.AddHeader("content-disposition", "attachment;filename= "+fileName + ".xls");

Not able to read excel sheet saved as xls using closedxml

1 Answers