CODEWITHSUNDEEP
javaexcelapacheapache-poi
odbhut.shei.chhele
odbhut.shei.chhele

Reputation: 6264

Cannot read from Excel file using apache poi

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?

Upvotes: 1

Views: 10157

Answers (4)

Sandeep Jain
Sandeep Jain

Reputation: 1261

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

            }
        }

Upvotes: 0

Garry
Garry

Reputation: 4533

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

Upvotes: 3

Rahul Yadav
Rahul Yadav

Reputation: 1513

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

Upvotes: 1

Shekhar Khairnar
Shekhar Khairnar

Reputation: 2691

This may help you:--

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

Upvotes: 2

Related Questions