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

问题描述

我正在使用 NetBeans 6.9.1 处理 Spring/Hibernet.我正在尝试读取 Excel 文件 (.xlsx-Office 2007).读取 Excel 文件的代码如下，使用 Vector 来存储 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 Documents 的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屋！