Read Excel using NPOI

Question

I am trying to read excel[xls and xlsx] using NPOI,I am using following code, but it is giving 'Unable to Read entire header; 27 bytes Read; expected 512 bytes' while reading an 8KB xls file

    byte[] byteArray = Encoding.UTF8.GetBytes(filepath);
    MemoryStream stream = new MemoryStream(byteArray);
    MemoryStream stream1 = new MemoryStream(Encoding.UTF8.GetBytes(filepath ?? ""));

    NPOI.HSSF.UserModel.HSSFWorkbook hssfwb = default(HSSFWorkbook);
    hssfwb = new NPOI.HSSF.UserModel.HSSFWorkbook(stream1);
    Sheet sheet = hssfwb.GetSheetAt(0);
    DataTable dtinputExcel = new DataTable();

I have tried every possible code available on net for this error. Please guide me what's the errorless method to read and excel[xls/xlsx] of any size.

check if your file is not open in an oter program, You could be reading the lock instead of the file. — Drag and Drop, May 12 '17 at 06:53
I have closed excel process through coding before reading excel/calling above code, but still it gives the error. — Pratik, May 12 '17 at 08:15
check this link https://stackoverflow.com/questions/5855813/npoi-how-to-read-file-using-npoi — kumar chandraketu, Aug 21 '17 at 15:32

score 1 · Answer 1 · answered May 12 '17 at 22:48

The problem is that the constructor of HSSFWorkbook is expecting a stream containing the contents of the spreadsheet file, while you are passing it a MemoryStream containing the name of the file. You should be using a FileStream to read the file and passing that stream to the HSSFWorkbook constructor.

Try it like this:

IWorkbook hssfwb;
using (FileStream fs = new FileStream(filepath, FileMode.Open, FileAccess.Read))
{
    hssfwb = new HSSFWorkbook(fs);
}

ISheet sheet = hssfwb.GetSheetAt(0);

Read Excel using NPOI

1 Answers1