使用Java读Excel表时出错 [英] Error while reading Excel sheet using Java

问题描述

我正在使用NetBeans 6.9.1使用Spring / Hibernet。我正在读取一个Excel文件（.xlsx- Office 2007）。用于读取Excel文件的代码如下，使用 Vactor 存储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.

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

以下是我的 Controller 调用上述方法读取指定的Excel文件

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.

提供的数据似乎在Office 2007+ XML中。您是
调用处理OLE2 Office文档的POI部分。你
需要调用POI的不同部分来处理这些数据（例如XSSF
而不是HSSF）

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?

代码取自这里。

推荐答案

您的代码显然要求HSSF，所以只能工作使用较旧的.xls（二进制）文件。

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

如果需要，您可以要求POI自动检测您拥有的文件类型，并选择相应的HSSF或XSSF为您的情况。然而，为了做到这一点，你需要稍微改变代码，并使用接口而不是具体的类（所以你的代码可以工作，无论你获得一个HSSF还是XSSF对象）

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网站有一个进行这些更改的指南，这应该指导您完成。

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

将成为新系统：

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

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

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

这篇关于使用Java读Excel表时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！