0

I want to upload excel file of size 40MB from the client side and take the data from excel file and store it in database but while uploading .xls format file i am getting the following error:

 "Invalid header signature; read 0x090A0D3E7669643C, expected 0xE11AB1A1E011CFD0 - Your file appears not to be a valid OLE2 document"

But for .xlsx file i am not getting this error since i am using NPOI Library i think i should be able to upload both .xls and .xlsx file. Below is my code:

private DataTable Excel_To_DataTable(int Index)
{
    DataTable dt = new DataTable();
    try
    {
        if (FileUpload1.HasFile)
        {
            string extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
            string filename = Path.GetFullPath(FileUpload1.FileName);
            IWorkbook workbook = null;
            //Stream uploadFileStream = FileUpload1.PostedFile.InputStream;
            HttpPostedFile file = Request.Files[0];
            MemoryStream mem = new MemoryStream();
            mem.SetLength((int)file.ContentLength);
            file.InputStream.Read(mem.GetBuffer(), 0, (int)file.ContentLength);
            //using (MemoryStream file= new MemoryStream())
            //{
            if (extension == ".xlsx")
            {
                workbook = new XSSFWorkbook(mem);
            }
            else if (extension == ".xls")
            {
                workbook = new HSSFWorkbook(mem);
            }
            else
            {
                throw new Exception("This format is not supported");
            }
            //}
            //IWorkbook workbook = WorkbookFactory.Create(uploadFileStream);
            ISheet sheet = workbook.GetSheetAt(0);
            System.Collections.IEnumerator rows = sheet.GetRowEnumerator();

            IRow headerRow = sheet.GetRow(0);
            int cellCount = headerRow.LastCellNum;

            for (int j = 0; j < cellCount; j++)
            {
                ICell cell = headerRow.GetCell(j);
                dt.Columns.Add(cell.ToString());
            }

            for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
            {
                IRow row = sheet.GetRow(i);
                DataRow dataRow = dt.NewRow();
                if (row == null)
                {
                    break;
                }
                for (int j = row.FirstCellNum; j < cellCount; j++)
                {
                    if (row.GetCell(j) != null)
                        dataRow[j] = row.GetCell(j).ToString();
                }

                dt.Rows.Add(dataRow);
            }
            return dt;
        }
        else
        {
            return null;
        }
    }
    catch (Exception ex)
    {
        return null;
    }
}
abhishek
  • 301
  • 1
  • 5
  • 29
  • I think NPOI cannot work with .xls files (Excel files prior to Excel 2007) because it's binary files. xlsx files are zipped xml (OpenXML format) – Nino Aug 29 '17 at 06:21
  • 1
    No NPOI provides support for both types of file Its EPPlus which does not support – abhishek Aug 29 '17 at 06:33
  • can you try using Filestream instead of memoryStream. Please check this link of NPOI https://stackoverflow.com/questions/5855813/npoi-how-to-read-file-using-npoi – kumar chandraketu Aug 29 '17 at 15:06
  • Please check this [answer](https://stackoverflow.com/a/11849067/1351076). Your xls file is probably not a true xls file. I tried your code with a xls file and didn't get any errors. – krlzlx Aug 30 '17 at 10:05
  • 1
    Thank you the issue is with the file and not in the code. – abhishek Aug 31 '17 at 06:34

0 Answers0