Unable to read excel file(.xls) in java poi

Question

Trying to use the Workbook wb = WorkbookFactory.create(new File(fileName)); // Read the xls file found on the below link

Tried some other libraries jxcel, no luck, Can someone help if am doing anything wrong

XLS files : https://www.huduser.gov/portal/datasets/excel/wy_foreclosure.zip

Code:

import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
.......
......

try (InputStream is = new FileInputStream(new File(fileName));
                Workbook wb = WorkbookFactory.create(new File(fileName));) {

        }

Exception Trace :

Exception in thread "main" org.apache.poi.util.RecordFormatException: Not enough data (0) to read requested (2) bytes
    at org.apache.poi.hssf.record.RecordInputStream.checkRecordPosition(RecordInputStream.java:243)
    at org.apache.poi.hssf.record.RecordInputStream.readShort(RecordInputStream.java:262)
    at org.apache.poi.hssf.record.PrintSetupRecord.<init>(PrintSetupRecord.java:74)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
    at org.apache.poi.hssf.record.RecordFactory$ReflectionConstructorRecordCreator.create(RecordFactory.java:84)
    at org.apache.poi.hssf.record.RecordFactory.createSingleRecord(RecordFactory.java:345)
    at org.apache.poi.hssf.record.RecordFactoryInputStream.readNextRecord(RecordFactoryInputStream.java:288)
    at org.apache.poi.hssf.record.RecordFactoryInputStream.nextRecord(RecordFactoryInputStream.java:254)
    at org.apache.poi.hssf.record.RecordFactory.createRecords(RecordFactory.java:494)
    at org.apache.poi.hssf.usermodel.HSSFWorkbook.<init>(HSSFWorkbook.java:344)
    at org.apache.poi.hssf.usermodel.HSSFWorkbookFactory.createWorkbook(HSSFWorkbookFactory.java:49)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.apache.poi.ss.usermodel.WorkbookFactory.createWorkbook(WorkbookFactory.java:314)
    at org.apache.poi.ss.usermodel.WorkbookFactory.createHSSFWorkbook(WorkbookFactory.java:292)
    at org.apache.poi.ss.usermodel.WorkbookFactory.create(WorkbookFactory.java:128)
    at org.apache.poi.ss.usermodel.WorkbookFactory.create(WorkbookFactory.java:74)
    at org.apache.poi.ss.usermodel.WorkbookFactory.create(WorkbookFactory.java:281)
    at org.apache.poi.ss.usermodel.WorkbookFactory.create(WorkbookFactory.java:253)
    at org.apache.poi.ss.usermodel.WorkbookFactory.create(WorkbookFactory.java:234)
    at com.noteunlimited.util.ForeClosureDump.generateStatistics(ForeClosureDump.java:55)
    at com.noteunlimited.util.ForeClosureDump.main(ForeClosureDump.java:64)

Could you get whoever created those xls files to check that they are saving them properly? Those files in the the zip do not appear to be standard xls format. If there are xlsb format, you might be able to use poi.apache.org/apidocs/dev/org/apache/poi/xssf/binary/… — PJ Fanning

Martin Martin · Accepted Answer · 2019-03-30T14:11:13

To open an existing XLS file you can e.g. use this code:

import java.io.FileInputStream;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class OpenXLS {

  public static void main(String[] args) throws Exception {

    HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream("yourFile.xls"));
    // Do something with it.
    workbook.close();
  }
}

This won't work with the files you provided. But when you open one of these files in LO Calc and save it without any changes made then everything is fine. So, something seems to be not right with your files.

Unable to read excel file(.xls) in java poi

1 Answers