Read Excel from Apache poi java

Question

I'm having a weird problem about reading excel file, I created a file to test but still have the same problem, the workbook is returning that has 0 sheets but it does have 3 sheets: here's my code:

FileInputStream fs = new FileInputStream(new File("C:/Users/TO124415/Desktop/test.xlsx"));

    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet hs = wb.getSheetAt(0);
    int number = wb.getNumberOfSheets();
    System.out.println(number);
    FormulaEvaluator form = wb.getCreationHelper().createFormulaEvaluator();
    HSSFCell value = wb.getSheetAt(0).getRow(14).getCell(1);
            for (Row rw : hs){
        for(Cell cell : rw){
            switch(form.evaluateInCell(cell).getCellType()){
            case Cell.CELL_TYPE_NUMERIC:
                System.out.println(cell.getNumericCellValue());
            case Cell.CELL_TYPE_STRING:
                System.out.println(cell.getStringCellValue());
            }
        }
    }

I'm having this error:

Exception in thread "main" java.lang.IllegalArgumentException: Sheet index (0) is out of range (no sheets)

This error must be if I set getSheetAt(3) because I know that the index is starting from 0 not 1. Someone can explain please?

Gagravarr · Accepted Answer · 2018-05-25 10:51:05Z

4

Your problem is that you're not loading your file, you're creating a new one. These lines here:

FileInputStream fs = new FileInputStream(new File("C:/Users/TO124415/Desktop/test.xlsx"));

HSSFWorkbook wb = new HSSFWorkbook();

Open a file for reading, then promptly ignore it and create a brand new empty workbook. An empty workbook with no sheets in it, which is why you're getting the error about there being no sheets....

You instead want to do something like:

File input = new File("C:/Users/TO124415/Desktop/test.xlsx");
Workbook wb = WorkbookFactory.create(input);

That will read in the contents of the workbook, and auto-detect the type too for you (so you can use XLS and XLSX)

answered May 25, 2018 at 10:51

Gagravarr

48.5k11 gold badges114 silver badges158 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dev web Over a year ago

it's doing the same thing, still have the problem.

Gagravarr Over a year ago

You need to load your file, not create a new empty one! And if you really want to work with .xlx you need to use the generic Workbook stuff not the HSSF classes which are .xls only

Subburaj · Accepted Answer · 2018-05-25 10:50:37Z

2

It looks, you are using the wrong file type for the unsupported Class.

HSSFWorkbook class will support only .xls file type whreas XSSFWorkbook class will support both .xls and .xlsx file type

Please do any one of the below steps

Change the file extension as .xls (.xlsx file is supported only in XSSF not in HSSF.)
Change the HSSF as XSSF by keeping the xlsx file extension

answered May 25, 2018 at 10:50

Subburaj

2,3543 gold badges23 silver badges40 bronze badges

3 Comments

Dev web Over a year ago

Thanks, I used .xls with XSSF and here's the new problem Caused by: java.lang.ClassNotFoundException: org.apache.xmlbeans.XmlException at java.net.URLClassLoader$1.run(Unknown Source) at java.security.AccessController.doPrivileged(Native Method) at java.net.URLClassLoader.findClass(Unknown Source) at java.lang.ClassLoader.loadClass(Unknown Source) at sun.misc.Launcher$AppClassLoader.loadClass(Unknown Source) at java.lang.ClassLoader.loadClass(Unknown Source) ... 1 more

Subburaj Over a year ago

@Dev Web: If you are using XSSFWorkbook, then XSSF POI jars needs to be added. Please add the required jars and then check

Dev web Over a year ago

The jar was included, but you were right anyway, I downloaded xmlbeans jar and it worked. Thanks a lot.

Michal · Accepted Answer · 2018-05-25 10:48:46Z

2

Try using XSSFWorkbook instead of HSSFWorkbook.

EDIT

As explained here https://poi.apache.org/spreadsheet/

HSSF is the POI Project's pure Java implementation of the Excel '97(-2007) file format. XSSF is the POI Project's pure Java implementation of the Excel 2007 OOXML (.xlsx) file format.

answered May 25, 2018 at 10:48

Michal

1,0408 silver badges13 bronze badges

2 Comments

Dev web Over a year ago

I'm getting this error: Caused by: java.lang.ClassNotFoundException: org.apache.xmlbeans.XmlException at java.net.URLClassLoader$1.run(Unknown Source) at java.security.AccessController.doPrivileged(Native Method) at java.net.URLClassLoader.findClass(Unknown Source) at java.lang.ClassLoader.loadClass(Unknown Source) at sun.misc.Launcher$AppClassLoader.loadClass(Unknown Source)

Michal Over a year ago

add poi-ooxml dependency

Julia Bijayalaxmi · Accepted Answer · 2022-03-07 07:48:28Z

Pom.xml :

    <!-- START Apache poi dependencies -->
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi</artifactId>
                <version>4.1.1</version>
            </dependency>
    
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-ooxml</artifactId>
                <version>4.1.1</version>
            </dependency>
    
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-ooxml-schemas</artifactId>
                <version>4.1.1</version>
            </dependency>
    
            <dependency>
                <groupId>org.apache.xmlbeans</groupId>
                <artifactId>xmlbeans</artifactId>
                <version>3.1.0</version>
            </dependency>
    </dependencies>


//Sample code snippet



    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.DataFormatter;
    import org.apache.poi.ss.usermodel.DateUtil;
    import org.apache.poi.ss.usermodel.FormulaEvaluator;
    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.ss.usermodel.Sheet;
    import org.apache.poi.ss.usermodel.Workbook;
    import org.apache.poi.ss.usermodel.WorkbookFactory;
    
    
    public static void readExcel() {
        try (FileInputStream fileInputStream = new FileInputStream("sample.xls"); // also accepts sample.xlsx
                Workbook workbook = WorkbookFactory.create(fileInputStream)) {
    
            for (Sheet sheet : workbook) {
                for (Row row : sheet) {
                    for (Cell cell : row) {
                    Object value = printCellValue(cell, workbook);
                    System.out.println(value);
                    }
                }
            }
    
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    
    private static Object printCellValue(Cell cell, Workbook workbook) {
        if (Objects.isNull(cell)) {
            return "";
        }
        switch (cell.getCellType()) {
        case BOOLEAN:
            return cell.getBooleanCellValue();
        case STRING:
            return cell.getRichStringCellValue().getString();
        case NUMERIC:
            DataFormatter dataFormatter = new DataFormatter();
            if (DateUtil.isCellDateFormatted(cell)) {
                return dataFormatter.formatCellValue(cell);
            } else {
                String cellValue = String.valueOf(cell.getNumericCellValue());
                if (cellValue.contains("E")) {
                    cellValue = BigDecimal.valueOf(cell.getNumericCellValue()).toPlainString();
                }
                return cellValue;
            }
        case FORMULA:
            FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
            DataFormatter formatter = new DataFormatter();
            String strValue = formatter.formatCellValue(cell, evaluator);
            return strValue;
        case ERROR:
            return cell.getErrorCellValue();
        case BLANK:
            return "";
        default:
            return "";
        }
    }

Collectives™ on Stack Overflow

Read Excel from Apache poi java

4 Answers 4

2 Comments

3 Comments

2 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

3 Comments

2 Comments

Comments

Related