0

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.

Nadeem Shaikh
  • 362
  • 5
  • 23

1 Answers1

0

ClosedXML generates .xlsx files, not .xls files.

Check your code:

Response.AddHeader("content-disposition", "attachment;filename= "+fileName + ".xls");
Francois Botha
  • 4,520
  • 1
  • 34
  • 46
  • Thanks for reply. ClosedXML is able to generate .xls file through my code. There is no problem in generating .xls. Generated file also gets open with MS-Excel the only problem is it cannot be read through my source code as I have given above. – Nadeem Shaikh Jan 23 '17 at 05:47
  • 1
    @NadeemShaikh I'm one of the developers on ClosedXML. I guarantee you it does NOT generate valid .xls files. – Francois Botha Jan 23 '17 at 08:49
  • 1
    @NadeemShaikh Refer to https://github.com/ClosedXML/ClosedXML/wiki/Does-it-support-Excel-2003-and-prior-formats-(.xls)%3F – Francois Botha Jan 23 '17 at 08:50
  • 1
    Yes, Francois it show me dialog box while opening this generated .xls file but it gets open in MS-Excel. I was unaware of closedXML .xls support. Thanks for informing me about it. Also it will be very helpful if you can provide any method for generating .xls which can be read through my code as other .xls file are readable with ExcelReaderFactory.CreateBinaryReader(stream) but only for this generated .xls files this code doesn't work. In short I want to generate .xls file which should be uploadable using some form of code. – Nadeem Shaikh Jan 23 '17 at 12:55
  • Why do you want to generate `.xls` files specifically instead of `.xlsx`? – Francois Botha Jan 23 '17 at 14:04
  • Just thought of doing it. If user has Office 2003 in case. – Nadeem Shaikh Jan 24 '17 at 11:59