spring集成excel导出

xml文件导入jar

 <dependency>
            <groupId>com.wuwenze</groupId>
            <artifactId>ExcelKit</artifactId>
            <version>2.0.7</version>
        </dependency>

建立下载mode类 会根据字段顺序在excel中排列

@Data
@AllArgsConstructor
@NoArgsConstructor
@Excel("发送城市统计")
public class MongodbPhoneCount implements Serializable {

    //名称
    private  String  _id;

    //省名称
    @ExcelField("省名称")
    private  String  provinceName;

    //市名称
    @ExcelField("市名称")
    private  String  cityName;

    //数量
    @ExcelField("数量")
    private Long num;
}

控制器直接调用

 public void  selectCountExcel(HttpServletResponse response) {
    List<MongodbPhoneCount> mongodbPhoneCounts = new ArrayList<MongodbPhoneCount>();
    ExcelKit.$Export(MongodbPhoneCount.class, response).downXlsx(mongodbPhoneCountList, false);
}

 集成easypoi-base-3.3.0jar包生产导出工具类

1.需要jar

easypoi-base-3.3.0

2.controller

 @PostMapping("listRegisterExport")
    @ApiOperation(value = "案件登记列表导出", notes = "案件登记列表导出")
    public void listRegisterExport(@ApiParam(value = "参数", required = true) @RequestBody CaseRegisterExportVo caseRegisterExportVo, HttpServletResponse response) throws IOException {
        List<CaseRegisterExportPo> list = this.caseSourceService.listRegisterExport(caseRegisterExportVo);
        String[] noParams = caseRegisterExportVo.getNoParams() == null ? new String[]{} : caseRegisterExportVo.getNoParams();//不需要字段，对应导出类
        ExportUtil exportUtil = new ExportUtil();
        IExport proxy = new ExportProxy().getInstance(exportUtil);
        proxy.exportExcel(noParams, list, "案件登记信息", "案件登记信息", CaseRegisterExportPo.class, "案件登记信息", response);
    }

3.IExport代理类方法

void exportExcel(String [] hiddenFields, List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName,
                     HttpServletResponse response) throws IOException;

4.代理类

package cn.com.taiji.lawenforcement.util.export.proxy;

import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.com.taiji.lawenforcement.util.export.ExportUtil;
import cn.com.taiji.lawenforcement.util.export.IExport;

import java.lang.reflect.Field;
import java.lang.reflect.InvocationHandler;
import java.lang.reflect.Method;
import java.lang.reflect.Proxy;
import java.util.Map;


public class ExportProxy implements InvocationHandler {

    private ExportUtil exportUtil;

    public IExport getInstance(ExportUtil exportUtil){
        this.exportUtil = exportUtil;
        return (IExport) Proxy.newProxyInstance(exportUtil.getClass().getClassLoader(),
                exportUtil.getClass().getInterfaces(), this);
    }

    @Override
    public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
        Object result = null;
        if("exportExcel".equals(method.getName())){
            // 对隐藏字段进行设置
            String [] hiddenFields = (String [])args[0];
            Class<?> pojoClass = (Class<?>)args[4];
            // 隐藏导出字段
            columnHidden(hiddenFields, pojoClass);
            result = method.invoke(exportUtil, args);
        }
        return result;
    }

    /**
     * @Author GQ
     * @Description 设置隐藏字段
     * @Date 15:52 2021/7/21
     * @param hiddenFileds 隐藏字段
     * @Param pojoClass 目标类
     * @return void
     * @Version 1.0
     * Modified by : GQ 15:52 2021/7/21
    **/
    void columnHidden(String [] hiddenFileds, Class<?> pojoClass) throws NoSuchFieldException, IllegalAccessException {
        for (Field field : pojoClass.getDeclaredFields()) {
            // 获取该字段的 注解
            Excel annotation = field.getAnnotation(Excel.class);
            //获取 Excel 这个注解所持有的 InvocationHandler
            if(null != annotation){
                InvocationHandler h = Proxy.getInvocationHandler(annotation);
                Field hField = h.getClass().getDeclaredField("memberValues");
                // 设置私有可访问
                hField.setAccessible(true);
                Map memberValues = (Map) hField.get(h);
                // 修改属性值
                memberValues.put("isColumnHidden", false);
            }
        }
        for (String param : hiddenFileds) {
            // 通过反射 获取目标实体类的目标字段
            Field file = pojoClass.getDeclaredField(param);
            // 获取该字段的 注解
            Excel annotation = file.getAnnotation(Excel.class);
            //获取 Excel 这个注解所持有的 InvocationHandler
            InvocationHandler h = Proxy.getInvocationHandler(annotation);
            Field hField = h.getClass().getDeclaredField("memberValues");
            // 设置私有可访问
            hField.setAccessible(true);
            Map memberValues = (Map) hField.get(h);
            // 修改属性值
            memberValues.put("isColumnHidden", true);
        }

    }
}

5.实现接口类

public class ExportUtil implements IExport{
    @Override
    public void exportExcel(String [] hiddenFields, List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, HttpServletResponse response) throws IOException {
        ExcelUtil.exportExcel(list, title, sheetName, pojoClass, fileName, response);
    }
}

6.工具类方法

public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName,
                                   HttpServletResponse response) throws IOException {
        list = Optional.ofNullable(list).orElseGet(() -> new ArrayList<>());
        defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName, ExcelType.XSSF));
    }




private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response,
                                      ExportParams exportParams) throws IOException {
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
        downLoadExcel(fileName, response, workbook);
    }




//数据传入流中respone返回
private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook)
            throws IOException {
        try {
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition",
                    "attachment;filename=" + URLEncoder.encode(fileName + "." + ExcelTypeEnum.XLSX.getValue(), "UTF-8"));
            workbook.write(response.getOutputStream());
        } catch (Exception e) {
            throw new IOException(e.getMessage());
        }
    }