Error while reading Excel sheet using Java

Question

I'm working with Spring/Hibernet using NetBeans 6.9.1. I'm trying to read an Excel file (.xlsx- Office 2007). The code for reading an Excel file is as follows using a Vactor to store data from the Excel sheet.

import java.io.FileInputStream;
import java.util.Iterator;
import java.util.Vector;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import model.NewHibernateUtil;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.hibernate.Session;
import org.springframework.validation.BindException;
import org.springframework.web.servlet.ModelAndView;
import org.springframework.web.servlet.mvc.SimpleFormController;

private Vector importExcelSheet(ModelAndView mv)
{
    Vector cellVectorHolder = new Vector();
    try
    {         
        HSSFWorkbook myWorkBook = new HSSFWorkbook(new POIFSFileSystem(new FileInputStream("E:/Project/SpringHibernet/MultiplexTicketBookingNew/web/excelSheets/Country.xlsx")));
        HSSFSheet mySheet = myWorkBook.getSheetAt(0);
        Iterator rowIter = mySheet.rowIterator();
        System.out.println(mySheet.getRow(1).getCell(0));
        while(rowIter.hasNext())
        {
            HSSFRow myRow = (HSSFRow) rowIter.next();
            Iterator cellIter = myRow.cellIterator();
            Vector cellStoreVector=new Vector();
            while(cellIter.hasNext())
            {
                HSSFCell myCell = (HSSFCell) cellIter.next();
                cellStoreVector.addElement(myCell);
            }
            cellVectorHolder.addElement(cellStoreVector);
        }
    }
    catch (Exception e)
    {
        mv.addObject("msg", e.getMessage());
    }
    return cellVectorHolder;
}

The following is a method in my Controller that calls the above method to read the specified Excel file

---

@Override
protected ModelAndView onSubmit(HttpServletRequest request, HttpServletResponse response, Object command, BindException errors) throws Exception
{
    ModelAndView mv=new ModelAndView();

    try
    {
        if(request.getParameter("import")!=null)
        {
            session=NewHibernateUtil.getSessionFactory().getCurrentSession();
            session.beginTransaction();

            Vector dataHolder=importExcelSheet(mv);
            for (int i=0;i<dataHolder.size(); i++)
            {
                Vector cellStoreVector=(Vector)dataHolder.elementAt(i);
                for (int j=0; j < cellStoreVector.size();j++)
                {
                    HSSFCell myCell = (HSSFCell)cellStoreVector.elementAt(j);
                    String st = myCell.toString();
                    System.out.println(st.substring(0,1)+"\t");
                }
                System.out.println();
            }

            session.flush();
            session.getTransaction().commit();
        }
    }
    catch (Exception e)
    {
        mv.addObject("msg", e.getMessage());
    }
    return mv;
}

On executing this code, the following exception is thrown.

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)

Am I using a wrong source or something else is wrong with the above code? What is the solution?

The code is taken from here.

Answer 1

Your code as it stands explicitly requests HSSF, so will only work with the older .xls (binary) files.

If you want, you can ask POI to auto-detect which file type you have, and pick the appropriate one of HSSF or XSSF for your case. However, to do that you need to change your code slightly, and use interfaces rather than concrete classes (so your code works whether you get a HSSF or XSSF object)

The POI website has a guide to making these changes which should guide you through.

As an example, when you follow this, your first few lines which were:

    HSSFWorkbook myWorkBook = new HSSFWorkbook(new POIFSFileSystem(new FileInputStream("E:/Project/SpringHibernet/MultiplexTicketBookingNew/web/excelSheets/Country.xlsx")));
    HSSFSheet mySheet = myWorkBook.getSheetAt(0);
    Iterator rowIter = mySheet.rowIterator();
    System.out.println(mySheet.getRow(1).getCell(0));

Will become in the new system:

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

This will then work for both .xls and .xlsx files