struts2+hibernate+poi导出Excel实例

来源：互联网发布：热血三国范疆数据编辑：程序博客网时间：2024/04/27 03:49

本实例通过struts2+hibernate+poi实现导出数据导入到Excel的功能

用到的jar包：

poi 下载地址:http://poi.apache.org/

根据查询条件的选择显示相应数据到页面，并把数据可导入到Excel表中
首先根据查询条件显示数据
选择导出Excel将根据查询条件返回数据并通过流写入Excel文件中，核心代码如下：

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%><%@ taglib prefix="s" uri="/struts-tags" %><%String path = request.getContextPath();String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";%><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"><html> <head> <base href="<%=basePath%>"> <title>My JSP 'list_export.jsp' starting page</title> <meta http-equiv="pragma" content="no-cache"><meta http-equiv="cache-control" content="no-cache"><meta http-equiv="expires" content="0"> <meta http-equiv="keywords" content="keyword1,keyword2,keyword3"><meta http-equiv="description" content="This is my page"><mce:script type="text/javascript"></mce:script> </head> <body style="overflow-x:hidden;overflow-y:auto;" mce_style="overflow-x:hidden;overflow-y:auto;"><form name="form" method="post" id="form1" action="queryExport.action"><table align="center" width="%100"><tr><td>姓名:<s:textfield name="pram_name" value="%{#parameters.pram_name}" theme="simple"/></td> <td>年龄:<s:textfield name="pram_age" value="%{#parameters.pram_age}" theme="simple"/></td> <td>地址:<s:textfield name="pram_address" value="%{#parameters.pram_address}" theme="simple"/> </td></tr><tr align="right" ><td colspan="3"><input type="button" value=" 查询 " onclick="toQuery(this)"> </td></tr></table><input type="hidden" id="act" name="act" ></form> <table id="content" cellSpacing="1" cellPadding="0" width="100%" border="0"> <tr> <td align="center">姓名</td> <td align="center">年龄</td> <td align="center">地址</td> </tr> <s:iterator value="userInfos"> <tr> <td align="center"><s:property value="name" /></td> <td align="center"><s:property value="age" /></td> <td align="center"><s:property value="address" /></td> </tr> </s:iterator> </table> <table width="100%" border="0" cellspacing="0" cellpadding="0"> <tr> <td height="10"></td> </tr> <tr> <td height="25" align="left"> <a href="javascript:toExport(this);" mce_href="javascript:toExport(this);"><font color="#2469D7">导出...</font></a></td> </tr> </table></body></html>

struts.xml

<?xml version="1.0" encoding="UTF-8" ?><!DOCTYPE struts PUBLIC "-//Apache Software Foundation//DTD Struts Configuration 2.0//EN" "http://struts.apache.org/dtds/struts-2.0.dtd"><struts><constant name="struts.enable.DynamicMethodInvocation" value="false" /><constant name="struts.devMode" value="false" /><package name="index" namespace="/" extends="struts-default"><action name="listExport" class="com.ywjava.office.action.ListAction"><result>/WEB-INF/page/list_export.jsp </result></action><action name="criteriaQuery" class="com.ywjava.office.action.QueryExportAction"><result>/WEB-INF/page/list_export.jsp </result></action><action name="queryExport" class="com.ywjava.office.action.QueryExportAction"><result name="success" type="stream"> <param name="contentType"> application/vnd.ms-excel</param><param name= " inputName"> excelStream</param> <param name="contentDisposition">attachment;filename=" ${fileName}.xls"</param> <param name="bufferSize">1024</param> </result><result name="error">/WEB-INF/page/msg_error.jsp</result> </action></package></struts>

执行queryExport这acion 并设置属性包括操作流,文件名,文件类型等。具体见struts.xml的注释

QueryExportAction.java

此action主要用于返回相应数据并通过流写入到新创建的Excel中，具体操作Excel代码如下：

package com.ywjava.office.action;import java.io.ByteArrayInputStream;import java.io.ByteArrayOutputStream;import java.io.InputStream;import java.util.Calendar;import java.util.HashMap;import java.util.Iterator;import java.util.List;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFCellStyle;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import com.opensymphony.xwork2.ActionContext;import com.opensymphony.xwork2.ActionSupport;import com.ywjava.office.domain.User;import com.ywjava.office.service.UserService;import com.ywjava.office.service.UserServiceImpl;import com.ywjava.office.utils.ExportTable;public class QueryExportAction extends ActionSupport {private static final String EXPORT = "export";private static final String QUERY = "query";private String act;private List<User> userInfos;private UserService us = new UserServiceImpl();private HashMap allParamsMap;private String qry_param_prefix = "pram_";// 这个输入流对应上面struts.xml中配置的那个excelStream，两者必须一致private InputStream excelStream;private String fileName; //文件名public String execute() throws Exception {//获取查询条件 allParamsMap = new HashMap();ActionContext ctx = ActionContext.getContext();String qryParamPrefix = qry_param_prefix.toUpperCase();Iterator it = ctx.getParameters().keySet().iterator();while (it.hasNext()) {String keyName = (String) it.next();if (keyName.toUpperCase().startsWith(qryParamPrefix)) {String[] vals = (String[]) (ctx.getParameters().get(keyName));if (vals != null && vals.length > 0) {allParamsMap.put(keyName, vals[0]); // name,value}}}if (EXPORT.equals(act)) {doExport(allParamsMap); // 根据查询条件 export } else if (QUERY.equals(act)) {doQuery(allParamsMap); // query}return SUCCESS;}/** * 导出方法 * * @return * @throws Exception */@SuppressWarnings("unchecked")private String doExport(HashMap paramsMap) throws Exception {userInfos = us.exportUserInfo(allParamsMap); //获取符合条件的信息if (userInfos == null) {return ERROR;}else {HSSFWorkbook workbook = this.getWorkbook(userInfos); if (workbook != null) {Calendar c = Calendar.getInstance();int year = c.get(Calendar.YEAR);int month = c.get(Calendar.MONTH) + 1;String month_ = new String("" + month);if (month < 10) {month_ = "0" + month;}int day = c.get(Calendar.DAY_OF_MONTH);String day_ = new String("" + day);if (day < 10) {day_ = "0" + day;}this.workbook2InputStream(workbook, year + "-" + month_ + "-"+ day_ + "");return SUCCESS;} else {return ERROR;}}}/** * 条件查询 * * @return */public String doQuery(HashMap paramsMap) {userInfos = us.getAllUser(paramsMap);if (userInfos == null) {return ERROR;}return SUCCESS;}public String getAct() {return act;}public InputStream getExcelStream() {return excelStream;}public String getFileName() {return fileName;}public UserService getUs() {return us;}public List<User> getUserInfos() {return userInfos;}/** * 创建一个excel文件。 * @param list * @return * @throws Exception */private HSSFWorkbook getWorkbook(List<User> list) throws Exception {HSSFWorkbook workbook = new HSSFWorkbook(); // 创建工作表HSSFCellStyle style = workbook.createCellStyle();style.setAlignment(HSSFCellStyle.VERTICAL_CENTER);style.setAlignment(HSSFCellStyle.ALIGN_CENTER);HSSFSheet sheet = workbook.createSheet("sheet1"); // 创建表单HSSFRow row = sheet.createRow(0); // 创建第一行 titleHSSFCell cell = null;for (int i = 0; i < ExportTable.columnNames.length; i++) {cell = row.createCell(i);cell.setCellValue(ExportTable.columnNames[i]);cell.setCellStyle(style);}// creatExportDatafor (int i = 0; i < userInfos.size(); i++) {row = sheet.createRow(i + 1);//cell = row.createCell(0);cell.setCellValue(userInfos.get(i).getId());cell = row.createCell(1);cell.setCellValue(userInfos.get(i).getName());cell = row.createCell(2);cell.setCellValue(userInfos.get(i).getAge());cell = row.createCell(3);cell.setCellValue(userInfos.get(i).getAddress());}return workbook;}public void setAct(String act) {this.act = act;}public void setExcelStream(InputStream excelStream) {this.excelStream = excelStream;}public void setFileName(String fileName) {this.fileName = fileName;}public void setUs(UserService us) {this.us = us;}public void setUserInfos(List<User> userInfos) {this.userInfos = userInfos;}/* * 写入流中 */public void workbook2InputStream(HSSFWorkbook workbook, String fileName)throws Exception {this.fileName = fileName; // 设置文件名ByteArrayOutputStream baos = new ByteArrayOutputStream();workbook.write(baos);baos.flush();byte[] aa = baos.toByteArray();excelStream = new ByteArrayInputStream(aa, 0, aa.length);baos.close();}}