本实例通过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>
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>
- <!-- excelStream 与对应action中的输入流的名字要一致 -->
- <param name= " inputName"> excelStream</param>
- <!-- 文件名 与action中fileName一致 -->
- <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();
- }
- }