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