struts2+hibernate+poi导出Excel实例

本实例通过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"><!--
function toExport(obj){
obj.form.act.value = "export";
obj.form.submit();
}
function toQuery(obj) {
obj.form.act.value = "query";
var v =document.getElementById ('form1');
// alert(v);
v.action="criteriaQuery.action";
//alert(v.action);
obj.form.submit();
}
// --></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>

<%@ 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>

<mce:script type="text/javascript"><!--
function toExport(obj){
obj.form.act.value = "export";
obj.form.submit();
}

function toQuery(obj) {
obj.form.act.value = "query";
var v =document.getElementById ('form1');
// alert(v);
v.action="criteriaQuery.action";
//alert(v.action);
obj.form.submit();
}
// --></mce:script>
</head>

</form>

</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>

<?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">

<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); // 创建第一行 title
HSSFCell cell = null;
for (int i = 0; i < ExportTable.columnNames.length; i++) {
cell = row.createCell(i);
cell.setCellValue(ExportTable.columnNames[i]);
cell.setCellStyle(style);
}
// creatExportData
for (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();
}
}

秒客网

struts2+hibernate+poi导出Excel实例

相关文章