How to upload excel file in c# using NPOI Dll

2.3k Views Asked by At

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

There are 0 best solutions below