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 { // Fields private 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 accessors public 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文件有多少个sheet 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; } }

 

显示导入的数据页面(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());