Cannot read from Excel file using apache poi

Question

This is the code that I a have written.

import java.util.*;
import java.lang.*;
import java.io.*;

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;

public class Main
{
    public static void main (String[] args) throws java.lang.Exception
    {
        File inputFile = new File("./test.xlsx");
        HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(inputFile));
        HSSFSheet sheet = workbook.getSheetAt(0);
        Cell cell;
        Row row;
        Iterator<Row> rowIterator = sheet.iterator();
        while (rowIterator.hasNext()){
            row = rowIterator.next();
            Iterator<Cell> cellIterator = row.cellIterator();
            while (cellIterator.hasNext()){
                cell = cellIterator.next();
                System.out.println(cell.getStringCellValue());
            }
        }
    }
}

This is the error that I am getting.

The supplied data appears to be in the Office 2007+ XML. You are calling the part of POI that deals with OLE2 Office Documents. You need to call a different part of POI to process this data (eg XSSF instead of HSSF)

Question: What am I doing wrong?

Garry · Accepted Answer · 2015-06-30 10:13:38Z

3

As Rahul said, you are using HSSF part which is used to fetch info from old excel i.e. .xls (before 2007) format.

Workbook wb = WorkbookFactory.create(new File("/path/to/your/excel/file"));
    Sheet mySheet = wb.getSheetAt(0);
    Iterator<Row> rowIter = mySheet.rowIterator();
    System.out.println(mySheet.getRow(1).getCell(0));

Please try to convert to above, it will work for both .xls and .xlsx

answered Jun 30, 2015 at 10:13

Garry

4,5433 gold badges31 silver badges48 bronze badges

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

Comments

Shekhar Khairnar · Accepted Answer · 2015-06-30 09:39:01Z

2

This may help you:--

file = new File("/yourFile.xlsx");
workBook = WorkbookFactory.create(file);    
sheet  = workBook.getSheetAt(sheetNumber);

answered Jun 30, 2015 at 9:39

Shekhar Khairnar

2,7013 gold badges29 silver badges46 bronze badges

3 Comments

Shekhar Khairnar Over a year ago

org.apache.poi.ss.usermodel.Workbook

odbhut.shei.chhele Over a year ago

where is the WorkbookFactory package

Shekhar Khairnar Over a year ago

org.apache.poi.ss.usermodel.WorkbookFactory

Rahul Yadav · Accepted Answer · 2015-06-30 09:53:34Z

1

You are trying to access .xlsx file with HSSFWorkbook, you will need to use XSSFWorkbook instead of HSSFWorkbook. With HSSFWorkbook we can access .xls files.

For reference you can read POI

answered Jun 30, 2015 at 9:53

Rahul Yadav

1,5138 silver badges11 bronze badges

Comments

Sandeep Jain · Accepted Answer · 2023-11-29 10:17:49Z

File Reading any excel extension

try
        {
    // Getting file from local directory
    static final String FILE_NAME
        = "C:\\projects\\sprint124\\LMRiskOfficeService\\src\\main\\java\\com\\hsbc\\gbm\\amg\\util\\ExcelWorkbook.xlsx";
            //Create Workbook instance holding reference to .xlsx or .xls or .xlsm file
            Workbook wb = WorkbookFactory.create(new File(FILE_NAME));
            
            //Get first/desired sheet from the workbook
            Sheet ws = wb.getSheetAt(0);
 
            //Iterate through each rows one by one
            Iterator<Row> rowIterator = ws.iterator();
            while (rowIterator.hasNext()) 
            {
                Row row = rowIterator.next();
                //For each row, iterate through all the columns
                Iterator<Cell> cellIterator = row.cellIterator();
                while (cellIterator.hasNext()) 
                {
                    Cell cell = cellIterator.next();

                    switch (cell.getCellType().toString()) 
                    {
                        case "NUMERIC":
                            System.out.print(cell.getNumericCellValue()+" ");
                            break;
                        case "STRING":
                            System.out.print(cell.getStringCellValue()+" ");
                            break;
                    }
                }

            }
        }

Collectives™ on Stack Overflow

Cannot read from Excel file using apache poi

4 Answers 4

Comments

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related