Sheet index (0) is out of range (0.1) POI操作Excel异常

Sheet index (0) is out of range (0..-1) POI操作Excel错误
系统架构：Struts2.1.8、Spring2.5、Hibernate3.0
使用poi.jar包操作Excel报错！
poi.jar: 
poi-3.7-20101029.jar
poi-examples-3.7-20101029.jar
poi-ooxml-3.7-20101029.jar
poi-ooxml-schemas-3.7-20101029.jar
poi-scratchpad-3.7-20101029.jar

导入文件页面代码：

HTML code

<s:form action="userInfo" method="post" enctype="multipart/form-data">       导入Excel文件：<s:file name="excelFile"></s:file> <br/>      <s:submit value="导入"></s:submit> </s:form>

后台Userinfo类属性跟get/set方法省略。
ExcelWorkSheet类：

Java code

package com.boxun.bean;import java.util.ArrayList;import java.util.List;import org.apache.poi.hssf.record.formula.functions.T;public class ExcelWorkSheet<T> {    private String sheetName;    private List<T> data = new ArrayList<T>();  //数据行    private List<String> columns; //列名    public String getSheetName() {        return sheetName;    }    public void setSheetName(String sheetName) {        this.sheetName = sheetName;    }    public List<T> getData() {        return data;    }    public void setData(List<T> data) {        this.data = data;    }    public List<String> getColumns() {        return columns;    }    public void setColumns(List<String> columns) {        this.columns = columns;    }    }

UserAction：

Java code

package com.boxun.action;import java.io.File;import java.io.FileInputStream;import java.io.IOException;import java.io.InputStream;import java.util.ArrayList;import java.util.Iterator;import java.util.List;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.usermodel.Workbook;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import com.boxun.bean.ExcelWorkSheet;import com.boxun.bean.Userinfo;import com.boxun.biz.IUserBiz;import com.opensymphony.xwork2.ActionSupport;public class UserAction extends ActionSupport {    private IUserBiz userBiz;    public void setUserBiz(IUserBiz userBiz) {        this.userBiz = userBiz;    }        private File excelFile; //上传的文件        private String excelFileFileName; //保存原始文件名        //将Excel文件解析完毕后信息存放到这个对象中    private ExcelWorkSheet<Userinfo> excelWorkSheet;        public File getExcelFile() {        return excelFile;    }    public void setExcelFile(File excelFile) {        this.excelFile = excelFile;    }    public String getExcelFileFileName() {        return excelFileFileName;    }    public void setExcelFileFileName(String excelFileFileName) {        this.excelFileFileName = excelFileFileName;    }    public ExcelWorkSheet<Userinfo> getExcelWorkSheet() {        return excelWorkSheet;    }    public void setExcelWorkSheet(ExcelWorkSheet<Userinfo> excelWorkSheet) {        this.excelWorkSheet = excelWorkSheet;    }            //判断文件类型    public Workbook createWorkBook(InputStream is) throws IOException{        if(excelFileFileName.toLowerCase().endsWith("xls")){            return new HSSFWorkbook();        }        if(excelFileFileName.toLowerCase().endsWith("xlsx")){            return new XSSFWorkbook();        }        return null;    }        public String execute() throws Exception{        Workbook book = createWorkBook(new FileInputStream(excelFile));        Sheet sheet =  book.getSheetAt(0);  //在这一行的时候报错        excelWorkSheet = new ExcelWorkSheet<Userinfo>();        //保存工作单名称        Row firstRow = sheet.getRow(0);        Iterator<Cell> iterator = firstRow.iterator();                //保存列名        List<String> cellNames = new ArrayList<String>();        while (iterator.hasNext()) {            cellNames.add(iterator.next().getStringCellValue());        }        excelWorkSheet.setColumns(cellNames);                for (int i = 1; i <= sheet.getLastRowNum(); i++) {            Row ros = sheet.getRow(i);            Userinfo user = new Userinfo();            user.setId((int)ros.getCell(0).getNumericCellValue());            user.setName(ros.getCell(1).getStringCellValue());            user.setPass(ros.getCell(2).getStringCellValue());            user.setLastname(ros.getCell(3).getStringCellValue());            user.setAddres(ros.getCell(4).getStringCellValue());            user.setRemark(ros.getCell(5).getStringCellValue());            excelWorkSheet.getData().add(user);        }        for (int i = 0; i < excelWorkSheet.getData().size(); i++) {            Userinfo info = excelWorkSheet.getData().get(i);            System.out.println(info.getLastname());        }        return SUCCESS;    }                } 
 

Struts2配置文件：

XML code

<?xml version="1.0" encoding="UTF-8" ?><!DOCTYPE struts PUBLIC "-//Apache Software Foundation//DTD Struts Configuration 2.1//EN" "http://struts.apache.org/dtds/struts-2.1.dtd"><struts>    <constant name="struts.i18n.encoding" value="UTF-8"></constant>    <constant name="struts.ui.theme" value="simple"></constant>     <package name="excelOutPut" extends="struts-default">         <action name="userInfo" class="userAction">             <result>/excel/InputExcel.jsp</result>         </action>     </package></struts>    

打印异常：

Java code

java.lang.IllegalArgumentException: Sheet index (0) is out of range (0..-1)    org.apache.poi.hssf.usermodel.HSSFWorkbook.validateSheetIndex(HSSFWorkbook.java:403)    org.apache.poi.hssf.usermodel.HSSFWorkbook.getSheetAt(HSSFWorkbook.java:781)    org.apache.poi.hssf.usermodel.HSSFWorkbook.getSheetAt(HSSFWorkbook.java:88)    com.boxun.action.UserAction.execute(UserAction.java:76)    sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)    sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)    sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)    java.lang.reflect.Method.invoke(Method.java:597)

excel文件：

XML code

编号    用户名    密码    真实姓名    地址    备注5    3135    123    博讯    贵阳    诗人6    8634    456    一生    平安    好人7    7255    245    天天    向上    学习

Debug的时候、到Sheet sheet = book.getSheetAt(0); 这行就报错。

各位给看看！感激不尽！！！



[解决办法]
//判断文件类型
public Workbook createWorkBook(InputStream is) throws IOException{
if(excelFileFileName.toLowerCase().endsWith("xls")){
return new HSSFWorkbook();
}
if(excelFileFileName.toLowerCase().endsWith("xlsx")){
return new XSSFWorkbook();
}
return null;
}

这个方法写的有问题，InputStream is这个参数没有使用，应该打开文件，而不是new一个workbook,具体代码不记得了，自己看下api

查看更多 下一篇