通过Spring反射机制，实现通用的Excel导出类

于 2016-11-26 16:05:06 发布
阅读量214 收藏
点赞数
CC 4.0 BY-SA版权
分类专栏： Java 文章标签： excel 反射
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/iteye_11315/article/details/82676370

Struts2.xml

<!-- 基本资料设置 -->
	<package name="exportExcel" namespace="/exportExcel" extends="default">
		 <!-- 附件信息  -->
		 <default-class-ref class="com.microcredit.common.action.ExportExcelAction">
		 </default-class-ref>
		 
		 <!-- 查询导出Excel列配置表-->
		 <action name="getColumnsByPage" method="getColumnsByPage">
			 <result type="json">
			   <param name="root">pageVo</param>
			 </result>
		 </action>
		 
		 <!-- 导出Excel-->
		 <action name="getDownloadExcel" method="getDownloadExcel">
			<result name="excel" type="stream">
			    <param name="contentType">application/vnd.ms-excel</param>
			    <param name="inputName">inputStreamExcel</param>
			    <param name="contentDisposition">attachment;filename="${fileName}"</param>
			    <param name="bufferSize">1024</param>
			</result>
        </action>
	</package>

 

 

导出Excel的工具Jsp

exportexcel.jsp

 

<%@ page language="java" contentType="text/html; charset=UTF-8"
	pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
  <head>
    <meta charset="utf-8">
    <title>导出excel</title>
    <script type="text/javascript">
    $(function(){
    	
		$("#open_win_excelGrid").click(function(){
			$("#win_excelGrid").window('open');
			$("#win_excelGrid").window('center');
			//加载excel导出datagrid
			$("#excelGrid").datagrid({
				  url:ctx + '/exportExcel/getColumnsByPage.do?MODELNAME=' + $("#MODELNAME").val(),//提供模块名
				  pagination:true,
				  idField:'REFCODE',
				  loadMsg:'加载中...',
				  singleSelect:false,
				  fitColumns:true,
				  striped:false,
				  border:false,
				  fit:true,
				  pageSize:10,
				  pageList:[10,15,20],
				  columns:[[
				     {checkbox:true,field:'check', width:100},
		             {title:'列名称',field:'COLNAME', width:120, align:'center'}
				 ]] 
			});
		});
		
		//下载Excel
		$("#downloadExcel").click(function(){
			var serviceName = $("#SERVICENAME").val();
			var methodName = $("#METHODNAME").val();
			var MODELNAME = $("#MODELNAME").val();
			var excelName = $("#EXCELNAME").val();
			var sheetName = $("#SHEETNAME").val();
			var title = $("#TITLE").val();
			var seledGrid = $('#excelGrid').datagrid('getSelections');
			if(seledGrid.length > 0){
				var refcodeParams = new Array();
				for (i = 0; i < seledGrid.length; i++) {
					refcodeParams[i] = seledGrid[i].REFCODE;
				};
				
				var url = "/exportExcel/getDownloadExcel.do?1=1";
					url += "&serviceName=" + serviceName,//bean名
					url += "&methodName=" + methodName,//方法名
					url += "&MODELNAME=" + MODELNAME,//模块名
					url += "&excelName=" + excelName;
					url += "&sheetName=" + sheetName;
					url += "&title=" + title;
					url += "&refcodeParams[]=" + refcodeParams;
					url += "&queryParams=" + JSON.stringify(queryParams);
				
				var form=$("<form>");//定义一个form表单
				form.attr("style","display:none");
				form.attr("target","");
				form.attr("method","post");
				form.attr("action",ctx + url);
				$("body").append(form);//将表单放置在web中
				form.submit();
				$(".btnExcelBack").click();
			} else {
				$.messager.alert('提示','请选择要下载的列！','info');
				return false;
			}
		});
		
		$(".btnExcelBack").click(function(){
			$("#win_excelGrid").window('close');
			$('#excelGrid').datagrid('clearSelections');
		});
		
    });
    </script>
</head>
<body>
	<div id="win_excelGrid" class="easyui-window" title="导出Excel" style="width:480px;height:385px"  
		data-options="modal:true,closed:true,resizable:false">
		<div id="cc" class="easyui-layout" fit="true">   
		    <div data-options="region:'center'" style="padding:2px;background:#eee;">
		    	<table id="excelGrid" toolbar='#northPanel'></table>
		    </div>   
		    <div data-options="region:'south'" style="padding:3px;background:#eee;height:35px">
		    	<div align="center">
	            	<a id="downloadExcel" href="javascript:void(0)" class="easyui-linkbutton"
						data-options="iconCls:'icon-ok'" style="width: 80px">下载</a>
		            <a href="javascript:void(0)" class="easyui-linkbutton btnExcelBack"
						data-options="iconCls:'icon-undo'" style="width: 80px">返回</a>
				</div>
		    </div>   
		</div>  
	</div>
</body>
</html>

 

 

报表页面需要引入  Excel导出工具Jsp

 

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ taglib prefix="s" uri="/struts-tags"%>
<%@include file="/common/in.jsp"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>贷后查询</title>
</head>
<body>
    <table id="qryDataGrid"></table>
    <div id="qyNorthPanel">
        <div align='center'>
            <a id="btnQuery" href="javascript:void(0)" style="width: 80px"
                class="easyui-linkbutton" data-options="iconCls:'icon-search'">查询</a>
            <span class="btnSplit"></span>
            <a id="open_win_excelGrid" href="javascript:void(0)" style="width: 100px"
                class="easyui-linkbutton" data-options="iconCls:'icon-print'">导出Excel</a>
            <span class="btnSplit"></span>
        </div>
    </div>
    <input type="hidden" id="SERVICENAME" value="loanQryInfoService" />
    <input type="hidden" id="METHODNAME" value="getLoanQryByPage" />
    <input type="hidden" id="MODELNAME" value="LOANFQRY" />
    <input type="hidden" id="EXCELNAME" value="贷后查询Excel" />
    <input type="hidden" id="SHEETNAME" value="贷后查询" />
    <input type="hidden" id="TITLE" value="贷后查询" />
    <%@include file="/common/exportexcel.jsp"%>
</body>
</html>

 

 

ExportExcelAction.java

package com.microcredit.common.action;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.microcredit.basicdata.entity.BPSAEXLM;
import com.microcredit.common.service.ExportExcelService;
import com.microcredit.common.util.ExcelUtil;
import com.microcredit.system.SpringUtils;
import com.microcredit.system.exception.MsException;
import com.microcredit.utils.CheckClass;

public class ExportExcelAction extends BaseAction {

	private static final long serialVersionUID = 4383815022290991861L;
	private static final Log log = LogFactory.getLog(ExportExcelAction.class);

	private SpringUtils springUtils;
	private InputStream inputStreamExcel;
	private String fileName;
	
	//注入相应的service
	private ExportExcelService exportExcelService;

	public String getColumnsByPage(){
		try {
			pageVo = this.exportExcelService.getExportExcelByPage(getRequestMap());
		} catch (Exception e) {
			log.error(e.getMessage(),e);
			this.getOpearteFailException(e);
		}
		return SUCCESS;
	}
	
	/**
	 * 导出Excel列表	
	 * @return
	 */
	public String getDownloadExcel(){

		//获取Excel名、页眉、标题
		String excelName = ExcelUtil.charGBK2Utf8(CheckClass.chcString(getRequestMap().get("excelName")));
		String sheetName = ExcelUtil.charGBK2Utf8(CheckClass.chcString(getRequestMap().get("sheetName")));
		String title = ExcelUtil.charGBK2Utf8(CheckClass.chcString(getRequestMap().get("title")));
		
		//获取数据
		List<Map<String,Object>> dataList = new ArrayList<Map<String,Object>>();
		try {
			this.getRequestMap().put("userId", this.getCurrentUser().getUSERID());
			dataList = this.exportExcelService.getDownExportExcelData(getRequestMap());
		} catch (Exception e1) {
			e1.printStackTrace();
		}
		
		//获取列表数据
		try {
			pageVo = this.exportExcelService.getExportExcelByPage(getRequestMap());
		} catch (Exception e) {
			log.error(e.getMessage(),e);
			this.getOpearteFailException(e);
		}
		List<BPSAEXLM> BPSAEXLMList = pageVo.getRows();//---没有对应实体

		try {
			ByteArrayOutputStream os = new ByteArrayOutputStream();

			ExcelUtil.createWorkBook(sheetName, title, dataList, BPSAEXLMList).write(os);
			
			byte[] content = os.toByteArray();
			
			String fileNameTemp = excelName + ".xls";
			fileName = ExcelUtil.charGB23122GBK(fileNameTemp);
			inputStreamExcel = new ByteArrayInputStream(content);
			
			os.flush();
			os.close();
			
		} catch (Exception e) {
			throw new MsException("0",new String[]{"导出Eecel异常！"},null,null);
		} finally {
			return "excel";
		}
		
	}

	public ExportExcelService getExportExcelService() {
		return exportExcelService;
	}
	
	public void setExportExcelService(ExportExcelService exportExcelService) {
		this.exportExcelService = exportExcelService;
	}
	
	public InputStream getInputStreamExcel() {
		return inputStreamExcel;
	}

	public void setInputStreamExcel(InputStream inputStreamExcel) {
		this.inputStreamExcel = inputStreamExcel;
	}

	public String getFileName() {
		return fileName;
	}

	public void setFileName(String fileName) {
		this.fileName = fileName;
	}
	
	public SpringUtils getSpringUtils() {
		return springUtils;
	}

	public void setSpringUtils(SpringUtils springUtils) {
		this.springUtils = springUtils;
	}
	
	
}

 

ExportExcelService.java

package com.microcredit.common.service;

import java.lang.reflect.Method;
import java.util.List;
import java.util.Map;

import net.sf.json.JSONObject;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.beans.factory.BeanFactoryAware;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.cglib.core.ReflectUtils;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.microcredit.common.dao.ExportExcelDao;
import com.microcredit.common.util.ExcelUtil;
import com.microcredit.common.vo.PageVo;
import com.microcredit.system.SpringUtils;
import com.microcredit.utils.CheckClass;

@Service("exportExcelService")
@Transactional(readOnly = true)
public class ExportExcelService extends BaseService {
	
	private static final Log log = LogFactory.getLog(ExportExcelService.class);

	@Autowired
	@Qualifier("exportExcelDao")
	private ExportExcelDao exportExcelDao;
	
	@Autowired
	@Qualifier("springUtils")
	private SpringUtils springUtils;
	
	public PageVo getExportExcelByPage(Map map) throws Exception{
		return this.exportExcelDao.getExportExcelByPage(map);
	}
	
	public List<Map<String,Object>> getDownExportExcelData(Map map) throws Exception{
		String serviceName = CheckClass.chcString(map.get("serviceName"));
		String methodName = CheckClass.chcString(map.get("methodName"));
		String queryParams = ExcelUtil.charGBK2Utf8(CheckClass.chcString(map.get("queryParams")));
		String userId = CheckClass.chcString(map.get("userId"));
		
		//通过SpringUtils获取，对应的bean对象
		Object infoService = springUtils.beanFactory.getBean(serviceName);
		//通过bean对象创建的service，和已知的方法名，来获取方法对象
		Method method = ReflectUtils.findDeclaredMethod(infoService.getClass(),methodName, new Class[]{Map.class});
		Map queryMap = (Map)JSONObject.fromObject(queryParams);
		queryMap.put("userId", userId);
		//执行方法，将创建的service对象，以及方法所需参数传进去
		Object obj = method.invoke(infoService, queryMap);
		
		PageVo pageVo = new PageVo();
		if(obj instanceof PageVo){
			pageVo = (PageVo) obj;
		}
		List<Map<String,Object>> dataList = ExcelUtil.transListBean2Map(pageVo.getRows());
		return dataList;
	}
	
	//	public Object getDownExportExcelData(Map map) throws Exception{
	//	String serviceName = CheckClass.chcString(map.get("serviceName"));
	//	String methodName = CheckClass.chcString(map.get("methodName"));
	//	String queryParams = CheckClass.chcString(map.get("queryParams"));
	//	Object infoService = springUtils.beanFactory.getBean(serviceName);
	//	
	//	//查询条件需要带入分页的参数
	//	
	//	Method method = ReflectUtils.findDeclaredMethod(infoService.getClass(),methodName, new Class[]{Map.class});
	//	Map queryMap = (Map)JSONObject.fromObject(queryParams);
	//	Object obj = method.invoke(infoService, queryMap);
	//	List list = new ArrayList();
	//	if(obj instanceof PageVo){
	//		//使用分页查询，需要循环查询分页，每页查询出来的结果拼接到一个List中，
	//		PageVo pageVo = (PageVo) obj;
	//		new BigDecimal(pageVo.getTotal()).divideAndRemainder(new BigDecimal(pageVo.getRows().size()));
	//	} else if(obj instanceof List){
	//		
	//	}
	//}
}

 

 

ExportExcelDaoImpl.java

package com.microcredit.common.dao.impl;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.math.NumberUtils;
import org.springframework.stereotype.Repository;

import com.microcredit.basicdata.entity.BPSAEXLM;
import com.microcredit.common.dao.ExportExcelDao;
import com.microcredit.common.vo.PageVo;
import com.microcredit.utils.CheckClass;

@Repository("exportExcelDao")
public class ExportExcelDaoImpl extends BaseDaoImpl implements ExportExcelDao	 {

	@Override
	public PageVo getExportExcelByPage(Map map) throws Exception{
		StringBuffer selectSql = new StringBuffer();
		StringBuffer pageSql = new StringBuffer();
		StringBuffer whereSql = new StringBuffer();
		Map params = new HashMap();
		String MODELNAME = CheckClass.chcString(map.get("MODELNAME"));
		String refcodeParams = CheckClass.chcString(map.get("refcodeParams[]"));
		List<String> REFCODE_list = new ArrayList<String>();
		if(!CheckClass.isBlank(refcodeParams)){
			String[] REFCODE = refcodeParams.split(",");
			for (int i = 0; i < REFCODE.length; i++) {
				REFCODE_list.add(REFCODE[i]);
			}
		}

		selectSql.append(" select t.* ");
		pageSql.append( "from BPSAEXLM t "); 
		whereSql.append(" where 1=1 and t.enable = 'Y' ");
		
		if(!CheckClass.isBlank(MODELNAME)){
			whereSql.append(" and t.MODELNAME = :MODELNAME");
			params.put("MODELNAME", MODELNAME);
		}
		if(REFCODE_list.size() > 0){
			whereSql.append(" and t.REFCODE in (:REFCODE_list)");
			params.put("REFCODE_list", REFCODE_list);
		}
		
		int page = NumberUtils.toInt((String)map.get("page"));
		int rows = NumberUtils.toInt((String)map.get("rows"));
		int start = (page-1)*rows;
		PageVo pageVo = new PageVo();
		List rowsList = this.queryEntitesByPageForSql(start, rows,selectSql.append(pageSql).append(whereSql).append(" order by t.REMARK asc ").toString(), params,BPSAEXLM.class);
		int total = this.getEntitiesCountForSql(pageSql.append(whereSql).toString(), params);
		pageVo.setTotal(total);
		pageVo.setRows(rowsList);
		return pageVo;
	}
	
}

 

导出列表的建表语句

-- Create table
create table BPSAEXLM
(
  refcode     NUMBER(10) default 0 not null,
  modelname   NVARCHAR2(100) not null,
  colname     NVARCHAR2(100) not null,
  colcode     NVARCHAR2(100) not null,
  coltype     NVARCHAR2(100),
  colfarmat   NVARCHAR2(100),
  enable      CHAR(1),
  remark      NVARCHAR2(200),
  lastmoduser NVARCHAR2(20),
  lastmoddate DATE
)
tablespace HEAD
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table BPSAEXLM
  add constraint PK_BPSAEXLM primary key (REFCODE)
  using index 
  tablespace HEAD
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

 

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值