大数据量分页查询导出以及多sheet导出

1、建立ExcelService注解

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
import org.springframework.stereotype.Component;


/***
 * @desc 使用ExcelService注解定义在service接口的实现类上
 * @author 
 * @date 2017-11-17 15:01
 */
@Target({ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
@Component // 表明可被 Spring 扫描
public @interface ExcelService {
Class<?> value();
}

2、建立ExcelServiceServer.java

import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;
import org.apache.commons.collections.MapUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.BeansException;
import org.springframework.beans.factory.InitializingBean;
import org.springframework.context.ApplicationContext;
import org.springframework.context.ApplicationContextAware;
import org.springframework.stereotype.Component;
import com.pingan.qhcs.map.loan.pg.util.annotation.ExcelService;
/***
 * @desc 获取所有带有 @ExcelService 注解的 Spring Bean
 * @author 
 * @date 2017-11-17 15:04
 */
@Component
public class ExcelServiceServer implements ApplicationContextAware, InitializingBean {

private static  Logger logger = LoggerFactory.getLogger(ExcelServiceServer.class);

private Map<String, Object> handlerMap = new ConcurrentHashMap<String, Object>(); // 存放接口名与对象之间的映射关系




@Override
public void afterPropertiesSet() throws Exception {
logger.info("进入了afterPropertiesSet....");
}


@Override
public void setApplicationContext(ApplicationContext ctx) throws BeansException {
//获取所有带有 ExcelService 注解的 Spring Bean
Map<String, Object> serviceBeanMap = ctx.getBeansWithAnnotation(ExcelService.class);
        if (MapUtils.isNotEmpty(serviceBeanMap)) {
            for (Object serviceBean : serviceBeanMap.values()) {
                String interfaceName = serviceBean.getClass().getAnnotation(ExcelService.class).value().getName();
                handlerMap.put(interfaceName, serviceBean);
            }
        }
}

public Map<String, Object> getHandlerMap() {
return handlerMap;
}


}

3、建立ExcelExportRequest.java

/***
 * @desc Excel导出请求类
 * @author 
 * @date 2017-11-17 15:41
 */
public class ExcelExportRequest {
private String requestId;    
private String className;
    private String methodName;
    private Class<?>[] parameterTypes;
    private Object[] parameters;
    
    
    public String getRequestId() {
return requestId;
}
public void setRequestId(String requestId) {
this.requestId = requestId;
}
public String getClassName() {
return className;
}
public void setClassName(String className) {
this.className = className;
}
public String getMethodName() {
return methodName;
}
public void setMethodName(String methodName) {
this.methodName = methodName;
}
public Class<?>[] getParameterTypes() {
return parameterTypes;
}
public void setParameterTypes(Class<?>[] parameterTypes) {
this.parameterTypes = parameterTypes;
}
public Object[] getParameters() {
return parameters;
}
public void setParameters(Object[] parameters) {
this.parameters = parameters;
}
}

4、GenericClassInvoke.java

import java.util.Map;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.cglib.reflect.FastClass;
import org.springframework.cglib.reflect.FastMethod;
import org.springframework.stereotype.Component;
/***
 * @desc 通过反射通用调用具体指定的方法
 * @author
 * @date 2017-11-17 15:50
 * @param <T>业务层传递过来的具体实体Bean类型
 */
@Component
public class GenericClassInvoke <R,T> {

@Autowired
private ExcelServiceServer excelServiceServer;

private static  Logger logger = LoggerFactory.getLogger(GenericClassInvoke.class);

@SuppressWarnings("unchecked")
public <T> T processMethodInvoke (R r) {
try {
if (r instanceof ExcelExportRequest) {
Map<String, Object> handlerMap = excelServiceServer.getHandlerMap();
ExcelExportRequest excelReq = (ExcelExportRequest)r;
String className = excelReq.getClassName();
Object serviceBean = handlerMap.get(className);
    Class<?> serviceClass = serviceBean.getClass();
String methodName =excelReq.getMethodName();
Class<?>[] parameterTypes = excelReq.getParameterTypes();
       Object[] parameters = excelReq.getParameters();
       FastClass serviceFastClass = FastClass.create(serviceClass);
       FastMethod serviceFastMethod = serviceFastClass.getMethod(methodName, parameterTypes);
    return (T) serviceFastMethod.invoke(serviceBean, parameters);
}
} catch (Exception e) {
logger.error("Invoke target Class -->GenericClassInvoke-->processMethodInvoke异常,请检查",e);
}
  return null;
}
}

5、GenericExcelQueryAdapter.java

import java.util.List;
import java.util.Map;
import java.util.UUID;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.cglib.reflect.FastClass;
import org.springframework.cglib.reflect.FastMethod;
import org.springframework.stereotype.Component;
import com.pingan.qhcs.map.loan.pg.util.ExcelExportRequest;
import com.pingan.qhcs.map.loan.pg.util.GenericClassInvoke;


/***
 * @desc 最终通用的查询接口查询适配器Adapter
 * @author 
 * @date 2017-11-17 16:36
 */
@Component(value="genericExcelQueryAdapter")
public class GenericExcelQueryAdapter implements IQuery<List<?>,Map<?, ?>>{


private final ThreadLocal<ExcelExportRequest> excelExportRequest = new  InheritableThreadLocal<ExcelExportRequest>();

@Autowired
private GenericClassInvoke<ExcelExportRequest, List<?>> genericClassInvoke ;

@Override
public <T> T queryForList() {
ExcelExportRequest excelReq = excelExportRequest.get();
return genericClassInvoke.processMethodInvoke(excelReq);
}

/***
* @author 
* @desc   add setExcelExportReq()方法
* @date   2017-11-17 16:44
* @param  req
*/
public void setExcelExportReq (ExcelExportRequest req) {
excelExportRequest.set(req);
}


/****
* @author 
* @date 2017-11-17 17:46 
* @desc 根据service不同,设置不同的业务实现类
* @param paramMap
* @param serviceClass
* @param query
* @throws Exception
*/
public void setDiffServiceType(Map<String, Object> paramMap,Class<?> serviceClass,IQuery<?, ?> query,String methodName) throws Exception {
ExcelExportRequest excelReq = new ExcelExportRequest();
excelReq.setRequestId(UUID.randomUUID().toString());
String className = serviceClass.getName();
//serviceClass.getClass().getAnnotation(ExcelService.class).value().getName();
excelReq.setClassName(className);
excelReq.setMethodName(methodName);//"findOrderDetailsList"
excelReq.setParameters(new Object[]{paramMap});
    excelReq.setParameterTypes(new Class<?>[]{Map.class});
    FastClass serviceFastClass = FastClass.create(query.getClass());
    Class<?>[] parameterTypes =new Class<?>[]{ExcelExportRequest.class};
        FastMethod serviceFastMethod = serviceFastClass.getMethod("setExcelExportReq", parameterTypes);
        Object[] parameters = {excelReq};
        serviceFastMethod.invoke(query, parameters);
}

}

6、IQuery.java

/***
 * 
 * @author 
 * @desc 目标接口
 */
public abstract interface IQuery <T,E> {
public <T> T queryForList ();
}

7、最终controller调用

       @Autowired
private DepositAccountMoniterService depositAccountMoniterService ;



@Autowired
@Qualifier(value="genericExcelQueryAdapter")
private IQuery<List<?>,Map<?, ?>> query;

@RequestMapping("test.do")
@ResponseBody
    public Map<String, Object> exportOrderDetailsList(HttpServletRequest request,HttpServletResponse response,ReportParamDto reportParam) throws Exception{
Map<String, Object> paramMap = this.buildParamMap(request, reportParam);
//修正查询时间区间
ReportUtils.calcReportDateTime(reportParam.getStartTime(), reportParam.getEndTime(), reportParam.getQueryType(), 
paramMap,MINUSDAYS_END,MINUSDAYS_START,MINUSMONTHS_END,MINUSMONTHS_START);
    logger.info("exportRedLoanDetailsList() 调用service入参 ：param="+paramMap);
   
    //createBy ---- createTime:2017-11-17 17:54 desc:根据不同业务类，调用这个业务类的哪个方法，最终方法调用是在JxlsExcelUtils的export方法里面 
    GenericExcelQueryAdapter queryAdapter = (GenericExcelQueryAdapter)query;
    queryAdapter.setDiffServiceType(paramMap,DepositAccountMoniterService.class,query,"findOrderDetailsList");//GenericExcelQueryAdapter.class
   
    List<String> columns = new ArrayList<String>();
    for (Entry<String, String> entrySet : orderDetailsColumnsMapping.entrySet()) {
columns.add(entrySet.getKey()+"_"+entrySet.getValue());
}
    String fileName = "报表管理 _"+System.currentTimeMillis()+".xlsx";//.xlsx
        String sheetName = "订单明细表";
    JxlsExcelUtils.exportBgDataExcelForSheetByPageSize(response, fileName, sheetName, paramMap, columns, query);    
    return null;
}