struts2+hibernate+poi导出Excel实例

最新推荐文章于 2021-04-07 15:41:51 发布

不善^

最新推荐文章于 2021-04-07 15:41:51 发布

阅读量1.4w

点赞数

分类专栏： SSH 文章标签： excel struts hibernate string exception hashmap

本文链接：https://blog.csdn.net/java_cxrs/article/details/6077549

版权

SSH 专栏收录该内容

16 篇文章 0 订阅

订阅专栏

本实例通过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=" 查询 " οnclick="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); // 创建第一行 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(); } }

不善^

关注

0
点赞
踩
12

收藏

觉得还不错? 一键收藏
11
评论
struts2+hibernate+poi导出Excel实例

<br />本实例通过struts2+hibernate+poi实现导出数据导入到Excel的功能<br /> <br />用到的jar包：<br /> <br /><br /> <br />poi 下载地址:http://poi.apache.org/<br />根据查询条件的选择显示相应数据到页面，并把数据可导入到Excel表中<br />首先根据查询条件显示数据<br /> 选择导出Excel将根据查询条件返回数据并通过流写入Excel文件中，核心代码如下：<br /><%@ page languag
复制链接

扫一扫