Userinfo.java
public class Userinfo { private Integer id; private String name; private String pass; private String lastname; private String addres; private String remark; //... }
ExcelWorkSheet.java
package com.excel; import java.util.ArrayList; import java.util.List; 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
package com.excel; 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.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()); System.out.println(user); 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; } }
OutAction.java
package com.excel; import java.io.IOException; import java.io.OutputStream; import java.util.ArrayList; import java.util.List; import javax.servlet.http.HttpServletResponse; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; 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.ss.usermodel.Workbook; import org.apache.struts2.interceptor.ServletResponseAware; 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 = "导出数据.xls"; 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=UTF-8"); 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 = new ArrayList<Userinfo>();// biz.getAll(); list.add(new Userinfo(1, "umgsai1", "123", "s", "湖北", "1000")); list.add(new Userinfo(2, "umgsai2", "123", "s", "湖北", "1000")); list.add(new Userinfo(3, "umgsai3", "123", "s", "湖北", "1000")); list.add(new Userinfo(4, "umgsai4", "123", "s", "湖北", "1000j")); for (int i = 0; i < list.size(); i++) { Userinfo user = list.get(i); row = sheet.createRow(i + 1); Cell newCell = row.createCell(0); newCell.setCellType(Cell.CELL_TYPE_STRING); newCell.setCellValue(user.getId()); row.createCell(1).setCellValue(user.getName()); newCell = row.createCell(2); newCell.setCellType(Cell.CELL_TYPE_STRING); newCell.setCellValue(user.getPass()); row.createCell(3).setCellValue(user.getLastname()); row.createCell(4).setCellValue(user.getAddres()); newCell = row.createCell(5); newCell.setCellType(Cell.CELL_TYPE_STRING); newCell.setCellValue(user.getRemark()); } try{ book.write(os); }catch(Exception ex){ ex.printStackTrace(); }finally{ book.close(); } } 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; } }
struts.xml
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE struts PUBLIC "-//Apache Software Foundation//DTD Struts Configuration 2.3//EN" "http://struts.apache.org/dtds/struts-2.3.dtd"> <struts> <constant name="struts.enable.DynamicMethodInvocation" value="false" /> <constant name="struts.devMode" value="true" /> <package name="default" namespace="/" extends="struts-default"> <action name="userInfo" class="com.excel.UserAction"> <result>InputExcel.jsp</result> </action> <action name="outPut" class="com.excel.OutAction"> <result>InputExcel.jsp</result> </action> </package> </struts>
InputExcel.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <%@taglib prefix="s" uri="/struts-tags"%> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"> <title>Insert title here</title> </head> <body> <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> </body> </html>
OutExcel.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <%@taglib prefix="s" uri="/struts-tags"%> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"> <title>Insert title here</title> </head> <body> <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> </body> </html>
用到的jar包
asm-3.3.jar
asm-commons-3.3.jar
asm-tree-3.3.jar
commons-fileupload-1.3.1.jar
commons-io-2.2.jar
commons-lang3-3.1.jar
commons-logging-1.1.3.jar
freemarker-2.3.19.jar
javassist-3.11.0.GA.jar
log4j-1.2.17.jar
ognl-3.0.6.jar
poi-3.11-beta2-20140822.jar
poi-examples-3.11-beta2-20140822.jar
poi-excelant-3.11-beta2-20140822.jar
poi-ooxml-3.11-beta2-20140822.jar
poi-ooxml-schemas-3.11-beta2-20140822.jar
poi-scratchpad-3.11-beta2-20140822.jar
struts2-core-2.3.16.3.jar
xwork-core-2.3.16.3.jar
相关推荐
程序使用Struts2(SSH即Struts2+Hibernate+Spring)+POI+MySQL实现Excel动态数据导入和导出,程序源码文件附带运行说明
运用struts2将数据库中的数据导入和导出,同时会用到poi的jar包
struts2+poi实现excel文件的导出,虽然有很多jxl库实现的方案,而本方案是使用struts2 mvc, apache poi库实现excel的导入导出,文档很详细
1.本动态导入导出Excel工程导入Eclipse即可用行,支持所有版本的Excel导入导出。 2.程序使用Struts2+POI+SQLSever(SSH即Struts2+Hibernate+Spring)实现Excel动态数据导入和导出,压缩包内含程序源码文件。
poi即Excel导入导出,在开发过程中了,为了提高数据录入速度, 一些系统都会使用导入功能,导入目前只能通过流的方式读写,那么为了此操作的便利性,提供此工具类,是原有的excel数据读取到泛型集合中,然后再对集合...
web中使用POI导入导出EXCEL文件的例子 struts1.x的例子,struts2.x可以参考自己修改 1.action的写法 import java.io.*; import java.sql.*; import java.util.ArrayList; import javax.servlet....
自己写一个可直接导出List<Object>数据的Excel工具类,使用起来很方便。
程序使用Struts2(SSH即Struts2+Hibernate+Spring)+POI+MySQL实现Excel动态数据导入和导出,程序源码文件附带运行说明
程序使用Struts2(SSH即Struts2+Hibernate+Spring)+POI+MySQL实现Excel动态数据导入和导出,程序源码文件附带运行说明
jsp通过POI将数据从数据库导出至EXCEL,可与CSDN中资源 【jsp中使用poi导入导出Excel http://download.csdn.net/detail/guan_xiu/5978843】对比学习; 前者是使用最原始的方法jsp+jdbc+poi; 后者加入了struts处理...
使用Spring集成struts2、ibatis、poi实现的增删改查功能,包括采用jquery实现的无刷新查询机分页、dwr实现的两级联动、以及采用poi动态将数据库数据导出成excel,本demo采用mysql数据库,附有建表sql,项目导入...
9、用POI实现对数据的导入导出功能,及POI对excel的操作。 10、后台权限采用流行的shiro权限管理框架,通过本项目可以深入了解shiro权限框架的应用及原理。 11、项目部署采用tomcat+Nginx的集群部署方式,在...
采用技术: SSH2(Struts2 + Spring + Hibernate),标准SSH2框架,采用了基于注解方式管理bean和事务,使用poi进行excel批量导出 图书类型:图书类别,类别名称,可借阅天数 图书:图书条形码,图书名称,图书所在...
2、后台:Struts2、动态代理、Mybatis框架、log4J日志框架、jstl 、jstl自定义分页标签、代码机器人、Dwr、POI(实现Excel文件的导入导出)等 3、数据库:Mysql 4、服务器:Tomcat 项目中涉及的功能: 1、项目以及...