欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识！
您现在的位置是： 首页

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

程序员文章站 2022-05-28 17:00:10
...

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
  );

 

相关标签： excel 反射

上一篇： java之反射的基础概念

下一篇： 反射比较两个对象属性值是否有修改

推荐阅读