使用Java读取Excel表时出错

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.

我正在使用NetBeans 6.9.1处理Spring/Hibernet。我正在读取一个Excel文件。xlsx - Office 2007)。读取Excel文件的代码如下所示，使用Vactor从Excel表存储数据。

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

下面是我的控制器中的一个方法，它调用上面的方法来读取指定的Excel文件。

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

提供的数据显示在Office 2007+ XML中。您正在调用处理OLE2办公文档的POI部分。您需要调用POI的另一部分来处理这些数据(例如XSSF而不是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.

代码从这里开始。

2 个解决方案

#1

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

您的代码将显式地请求HSSF，因此将只使用旧的.xls(二进制)文件。

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)

如果需要，您可以要求POI自动检测您拥有的文件类型，并为您的情况选择适当的HSSF或XSSF。但是，要做到这一点，您需要稍微更改您的代码，并使用接口而不是具体的类(因此您的代码可以工作，无论您是否得到HSSF或XSSF对象)

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

POI网站上有一个关于如何进行这些改变的指南，可以指导你。

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

这将同时适用于.xls和.xlsx文件

#2

You can also just replace HSSF with XSSF.

你也可以用XSSF替换HSSF。

Make sure that you add the poi-ooxml .jar in addition to the poi .jar. poi-ooxml provides support for XSSF. Make sure you also have added the given dependencies as listed on the Apache POI components page.

确保在poi .jar之外添加了poi-ooxml .jar。poi-ooxml提供了对XSSF的支持。确保还添加了Apache POI组件页面上列出的给定依赖项。

#1