系统架构: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; } }