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

问题描述

我和春天/ Hibernet使用NetBeans 6.9.1工作。我试图读取Excel文件（.xlsx-的Office 2007）。在code读取Excel文件是使用如下：a 向量子​​从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;
}

下面是一个方法，我的控制器调用上面的方法读取指定的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;
}

在执行此code，以下异常。

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)

我使用了错误的源或别的东西是错误的上述code？该解决方案是什么？

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

在code从这里服用。

推荐答案

您code，因为它代表明确要求HSSF，所以将只与旧的.xls（二进制）的文件。

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

如果你愿意，你可以问POI自动检测您的文件类型，并挑选HSSF为您的情况下，适当的一个或XSSF。然而，要做到这一点，你需要改变你的code略显不足，使用的界面，而不是具体类（所以你的code工作是否得到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));

将成为新的系统：

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

那么这将会为.xls和的.xlsx文件的工作。

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

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