本实例通过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(); } }
转载自:http://blog.csdn.net/java_cxrs/article/details/6077549