Struts2.18 Poi-3.7 Excel 导入导出

欢迎技术交流。 QQ：138986722

Struts的jar包就不说了~~~！！！

工程.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

 

有些jar包的用不着的、但是我为了以防万一全部都倒进来了！

我的导入页面(OutExcel.jsp)：

<%-- 记住这里需要设置enctype="multipart/form-data"--%> <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>

 

Struts2.xml配置:

<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> <action name="outPut" class="outAction"> <result>/excel/InputExcel.jsp</result> </action> </package>

 

Userinfo类：

package com.boxun.bean;import java.math.BigDecimal;/** * Userinfo entity. @author MyEclipse Persistence Tools */public class Userinfo implements java.io.Serializable {// Fieldsprivate Integer id;private String name;private String pass;private String lastname;private String addres;private String remark;// Constructors/** default constructor */public Userinfo() {}/** full constructor */public Userinfo(String name, String pass, String lastname, String addres,String remark) {this.name = name;this.pass = pass;this.lastname = lastname;this.addres = addres;this.remark = remark;}// Property accessorspublic Integer getId() {return this.id;}public void setId(Integer id) {this.id = id;}public String getName() {return this.name;}public void setName(String name) {this.name = name;}public String getPass() {return this.pass;}public void setPass(String pass) {this.pass = pass;}public String getLastname() {return this.lastname;}public void setLastname(String lastname) {this.lastname = lastname;}public String getAddres() {return this.addres;}public void setAddres(String addres) {this.addres = addres;}public String getRemark() {return this.remark;}public void setRemark(String remark) {this.remark = remark;}}

 

 

ExcelWorkSheet类:

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

 

 

导入的Action类：

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(is);}if(excelFileFileName.toLowerCase().endsWith("xlsx")){return new XSSFWorkbook(is);}return null;}public String execute() throws Exception{Workbook book = createWorkBook(new FileInputStream(excelFile));//book.getNumberOfSheets(); 判断Excel文件有多少个sheetSheet 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;}}

 

显示导入的数据页面(InputExcel.jsp)：

<h1><s:property value="excelWorkSheet.sheetName" /> </h1> <p> <s:iterator value="excelWorkSheet.columns"> <s:property />  ||  </s:iterator> </p> <s:iterator var="user" value="excelWorkSheet.data"> <p> <s:property value="#user.id"/>    <s:property value="#user.name"/>    <s:property value="#user.pass"/>    <s:property value="#user.lastname"/>    <s:property value="#user.addres"/>    <s:property value="#user.remark"/>    </p> </s:iterator>

 

导出数据页面(Out.jsp)：

<s:form name="form1" action="outPut.action" method="post"> <input type="hidden" name="format" value="xls" /> <s:submit name="sub" value="导出数据"></s:submit> </s:form>

 

导出数据Action:

package com.boxun.action;import java.io.IOException;import java.io.OutputStream;import java.util.List;import javax.servlet.http.HttpServletResponse;import org.apache.poi.ss.usermodel.CellStyle;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.Workbook;import org.apache.struts2.interceptor.ServletResponseAware;import com.boxun.bean.Userinfo;import com.boxun.biz.IUserBiz;import com.opensymphony.xwork2.ActionSupport;public class OutAction extends ActionSupport implements ServletResponseAware{/** * */private static final long serialVersionUID = 1L;private IUserBiz biz ;public void setBiz(IUserBiz biz) {this.biz = biz;}private String format = "xls";private HttpServletResponse response;private String fileName;public String execute(){@SuppressWarnings("all")List la = biz.getAllSql(); setResponseHeader();try {exportExcel(response.getOutputStream());response.getOutputStream().flush();response.getOutputStream().close();} catch (IOException e) {e.printStackTrace();}return null;}/** 设置响应头*/public void setResponseHeader(){try{//response.setContentType("application/msexcel;charset=UTF-8"); //两种方法都可以response.setContentType("application/octet-stream;charset=iso-8859-1");response.setHeader("Content-Disposition", "attachment;filename="+java.net.URLEncoder.encode(this.fileName, "UTF-8"));//客户端不缓存response.addHeader("Pargam", "no-cache");response.addHeader("Cache-Control", "no-cache");}catch(Exception ex){ex.printStackTrace();}}/**导出数据*/private void exportExcel(OutputStream os) throws IOException{Workbook book = new HSSFWorkbook();Sheet sheet = book.createSheet("导出信息");Row row = sheet.createRow(0);row.createCell(0).setCellValue("编号");row.createCell(1).setCellValue("用户名");row.createCell(2).setCellValue("密码");row.createCell(3).setCellValue("真实姓名");row.createCell(4).setCellValue("地址");row.createCell(5).setCellValue("备注");CellStyle sty = book.createCellStyle();List<Userinfo> list = biz.getAll();for (int i = 1; i < list.size(); i++) {Userinfo user = list.get(i-1);row = sheet.createRow(i);row.createCell(0).setCellValue(user.getId());row.createCell(1).setCellValue(user.getName());row.createCell(2).setCellValue(user.getPass());row.createCell(3).setCellValue(user.getLastname());row.createCell(4).setCellValue(user.getAddres());row.createCell(5).setCellValue(user.getRemark());}try{book.write(os);}catch(Exception ex){ex.printStackTrace();}}public String getFormat() {return format;}public void setFormat(String format) {this.format = format;this.fileName = "导出数据.xls";}public String getFileName() {return fileName;}public void setFileName(String fileName) {this.fileName = fileName;}/**记住一定有该属性的set方法*/public void setServletResponse(HttpServletResponse response) {this.response = response;}}

 

我用的是sql语句导出、先从数据库把数据查询出来、在导出查询出来的数据。

我的Excel数据格式：

 

我在导入过程中碰到一个问题~~~就是在用户名和密码这一项的时候、不能够完全的写数字~~~

如果全部是Number(Integer或者int)类型那么在转换这一属性数据的时候就会出错~~~搞不懂！ 必须要加上一个字符才能正确转换！

o(︶︿︶)o 唉！！！

---------------------------------------------2012-11-26更新------------------------------------------------

找到了、我在导入过程中碰到一个问题~~~就是在用户名和密码这一项的时候、不能够完全的写数字~~~

这个问题、很简单：在java代码中把该单元格设置成String类型

如我的Name跟Pass字段：

HSSFCell cell1 = (HSSFCell) ros.getCell(1); HSSFCell cell2 = (HSSFCell) ros.getCell(2);cell1.setCellType(Cell.CELL_TYPE_STRING); //把该单元格设置成String类型cell2.setCellType(Cell.CELL_TYPE_STRING); //把该单元格设置成String类型user.setName(cell1.getStringCellValue());  user.setPass(cell2.getStringCellValue());