基于easyexcel抽取成的公共导出接口

tianqi11

已于 2024-02-21 11:41:05 修改

阅读量111

点赞数

文章标签： java

于 2023-08-11 10:42:43 首次发布

本文链接：https://blog.csdn.net/tianqi11/article/details/132225767

版权

controller

package com.zhqc.cloud.wms.common.export.controller;

import cn.hutool.core.date.DateUtil;
import cn.hutool.core.util.ReflectUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.fastjson.JSONObject;
import com.alibaba.fastjson.TypeReference;
import com.zhqc.cloud.common.enums.IOWTBResponseEnum;
import com.zhqc.cloud.common.utils.ConvertUtil;
import com.zhqc.cloud.common.utils.ExceptionUtils;
import com.zhqc.cloud.wms.common.export.factory.ExportServiceFactory;
import com.zhqc.cloud.wms.common.export.handler.CustomerRowWriteHandler;
import com.zhqc.cloud.wms.common.export.model.ExportQuery;
import com.zhqc.cloud.wms.util.ExcelPOJOConvertUtil;
import com.zhqc.framerwork.common.exception.ZhqcValidateException;
import lombok.extern.slf4j.Slf4j;
import org.springframework.context.annotation.Lazy;
import org.springframework.validation.annotation.Validated;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.*;

/**
 * @author zdd
 */
@RestController
@Slf4j
@RequestMapping("/export/commonExport")
public class CommonExportController {

    @Resource
    @Lazy
    private ExportServiceFactory exportServiceFactory;

    /**
     * 导出
     */
    @PostMapping("/export")
    public void export(@RequestBody @Validated ExportQuery commonExportQuery) {
        replaceId2Name(commonExportQuery);
        long start = System.currentTimeMillis();
        log.info("开始导出，时间：{}", DateUtil.now());
        long l = System.currentTimeMillis();
        validParam(commonExportQuery);
        ExportServiceFactory.UploadServiceEntry serviceEntry = exportServiceFactory.getService(commonExportQuery.getModuleName());
        //执行方法
        Object result = invokeMethod(serviceEntry, commonExportQuery);
        log.info("查询数据耗时：{}ms", System.currentTimeMillis() - l);
        l = System.currentTimeMillis();
        Object resultObj = ReflectUtil.getFieldValue(result, "list");

        List<?> objects = ExcelPOJOConvertUtil.castList((List<?>) resultObj, serviceEntry.getReturnType());

        log.info("转换时间：{}ms", System.currentTimeMillis() - l);
        l = System.currentTimeMillis();

        log.info("查询需要导出的数据条数：{}", objects.size());

        ExceptionUtils.isNotEmpty(objects, "导出数据为空");

        String resultStr = JSONObject.toJSONString(objects);

        List<Map<String, Object>> resultMap = JSONObject.parseObject(resultStr, new TypeReference<List<Map<String, Object>>>() {
        });

        filterUnwantedFields(resultMap, commonExportQuery);
        log.info("处理时间：{}ms", System.currentTimeMillis() - l);
        l = System.currentTimeMillis();
        generateExcel(resultMap, commonExportQuery);
        log.info("导出时间：{}ms", System.currentTimeMillis() - l);
        log.info("结束导出，时间：{},耗时{}", DateUtil.now(), System.currentTimeMillis() - start);
    }

    private void replaceId2Name(ExportQuery commonExportQuery) {
        List<String> fieldList = commonExportQuery.getFieldList();
        List<String> newFieldList = new ArrayList<>(fieldList.size());
        for (String oldField : fieldList) {
            if (oldField.endsWith("Id")) {
                String newField = oldField.replace("Id", "Name");
                newFieldList.add(newField);
            } else {
                newFieldList.add(oldField);
            }
        }
        commonExportQuery.setFieldList(newFieldList);
    }


    private void generateExcel(List<Map<String, Object>> resultMap, ExportQuery commonExportQuery) {
        long l = System.currentTimeMillis();
        List<List<String>> resultData = transToExportData(resultMap, commonExportQuery);
        List<String> titles = commonExportQuery.getTitles();
        List<List<String>> titleList = transToHead(titles);
        log.info("转换数据耗时：{}ms", System.currentTimeMillis() - l);
        l = System.currentTimeMillis();

        HttpServletResponse response;
        try {
            response = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getResponse();
        } catch (Exception e) {
            throw new ZhqcValidateException(IOWTBResponseEnum.CUSTOMIZE_MSG, "获取response失败");
        }

        try {
            assert response != null;
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setHeader("Content-disposition", "attachment; filename=export.xlsx");
            EasyExcel
                    .write(response.getOutputStream())
                    .registerWriteHandler(new CustomerRowWriteHandler())
                    .head(titleList)
                    .sheet("sheet1")
                    .doWrite(resultData);
        } catch (IOException e) {
            throw new ZhqcValidateException(IOWTBResponseEnum.CUSTOMIZE_MSG, "数据导出失败");
        }
        log.info("写入数据耗时：{}ms", System.currentTimeMillis() - l);

    }

    private List<List<String>> transToHead(List<String> titles) {
        List<List<String>> head = new ArrayList<>();
        for (String title : titles) {
            List<String> headColumn = new ArrayList<>();
            headColumn.add(title);
            head.add(headColumn);
        }
        return head;
    }

    private List<List<String>> transToExportData(List<Map<String, Object>> resultMap, ExportQuery commonExportQuery) {
        List<String> fieldList = commonExportQuery.getFieldList();
        List<List<String>> resultData = new ArrayList<>();
        for (Map<String, Object> map : resultMap) {
            List<String> rowData = new ArrayList<>();
            for (String field : fieldList) {
                Object value = map.get(field);
                if (value instanceof Long){
                    value = DateUtil.format(new Date((Long) value), "yyyy-MM-dd HH:mm:ss");
                }
                rowData.add(StrUtil.isBlankIfStr(value) ? "" : value.toString());
            }
            resultData.add(rowData);
        }
        return resultData;
    }

    private void filterUnwantedFields(List<Map<String, Object>> resultMap, ExportQuery commonExportQuery) {
        List<String> fieldList = commonExportQuery.getFieldList();
        Map<String, List<String>> complexFieldMap = new HashMap<>();
        for (String field : fieldList) {
            if (field.contains(".")) {
                String[] split = field.split("\\.");
                if (split.length > 1) {
                    String complexField = split[0];
                    String complexFieldChild = split[1];
                    if (complexFieldMap.containsKey(complexField)) {
                        complexFieldMap.get(complexField).add(complexFieldChild);
                    } else {
                        List<String> childList = new ArrayList<>();
                        childList.add(complexFieldChild);
                        complexFieldMap.put(complexField, childList);
                    }
                }
            }
        }


        for (Map<String, Object> stringObjectMap : resultMap) {
            Map<String, Object> tempMap = new HashMap<>();
            for (Map.Entry<String, Object> stringObjectEntry : stringObjectMap.entrySet()) {
                if (stringObjectEntry.getValue() instanceof JSONObject && complexFieldMap.containsKey(stringObjectEntry.getKey())) {
                    JSONObject jsonObject = (JSONObject) stringObjectEntry.getValue();
                    for (String childField : complexFieldMap.get(stringObjectEntry.getKey())) {
                        tempMap.put(childField, jsonObject.get(childField));
                    }
                }
            }
            stringObjectMap.putAll(tempMap);
        }
        List<String> tempFieldList = new ArrayList<>();
        for (String originalString : fieldList) {
            int lastDotIndex = originalString.lastIndexOf('.');
            String processedString = (lastDotIndex >= 0) ? originalString.substring(lastDotIndex + 1) : originalString;
            tempFieldList.add(processedString);
        }
        fieldList = tempFieldList;
        for (Map<String, Object> map : resultMap) {
            List<String> finalFieldList = fieldList;
            map.entrySet().removeIf(entry -> !finalFieldList.contains(entry.getKey()));
        }
        commonExportQuery.setFieldList(fieldList);
    }

    private void validParam(ExportQuery commonExportQuery) {
        ExceptionUtils.isTrue(commonExportQuery.getFieldList().size() == commonExportQuery.getTitles().size(), "标题和字段数量不一致");

    }

    /**
     * 数据查询
     *
     * @param serviceEntry      服务
     * @param commonExportQuery 查询参数
     * @return 查询结果
     */
    private Object invokeMethod(ExportServiceFactory.UploadServiceEntry serviceEntry, ExportQuery commonExportQuery) {
        try {
            Object paramMap = commonExportQuery.getParamMap();
            Object paramObj = JSONObject.parseObject(JSONObject.toJSONString(paramMap), serviceEntry.getQueryType());

            Object service = serviceEntry.getUploadService();
            return service.getClass().getMethod(serviceEntry.getMethodName(), serviceEntry.getParamType()).invoke(service, paramObj);


        } catch (Exception e) {
            throw new ZhqcValidateException(IOWTBResponseEnum.CUSTOMIZE_MSG, StrUtil.format("下载失败：{}", e.getMessage()));
        }

    }
}

工厂类：

package com.zhqc.cloud.wms.export.factory;

import com.zhqc.cloud.common.utils.ExceptionUtils;
import com.zhqc.cloud.wms.gsp.model.query.DestroyInfoQueryExt;
import com.zhqc.cloud.wms.gsp.model.query.NcrQuery;
import com.zhqc.cloud.wms.gsp.model.query.RecheckQuery;
import com.zhqc.cloud.wms.gsp.model.query.UnsalableForewarnQuery;
import com.zhqc.cloud.wms.gsp.model.vo.NcrVOExt;
import com.zhqc.cloud.wms.gsp.model.vo.RecheckVOExt;
import com.zhqc.cloud.wms.gsp.model.vo.UnsalableForewarnVO;
import com.zhqc.cloud.wms.gsp.service.NcrService;
import com.zhqc.cloud.wms.gsp.service.RecheckService;
import com.zhqc.cloud.wms.gsp.service.UnsalableForewarnService;
import com.zhqc.cloud.wms.gspRecord.model.query.*;
import com.zhqc.cloud.wms.gspRecord.model.vo.*;
import com.zhqc.cloud.wms.gspRecord.service.*;
import com.zhqc.framerwork.common.model.query.BaseQuery;
import lombok.AllArgsConstructor;
import lombok.Data;
import org.springframework.context.annotation.DependsOn;
import org.springframework.context.annotation.Lazy;
import org.springframework.stereotype.Component;

import javax.annotation.PostConstruct;
import javax.annotation.Resource;
import java.util.HashMap;
import java.util.Map;

/**
 * @author zdd
 */
@Component
@DependsOn
public class ExportServiceFactory {

    @Resource
    @Lazy
    private GspRecInfoService gspRecInfoService;

    @Resource
    @Lazy
    private GspProductQcRecordService gspProductQcRecordService;


    @Resource
    @Lazy
    private GspPaRecordService gspPaRecordService;

    @Resource
    @Lazy
    private GspReviewRecordService gspReviewRecordService;


    @Resource
    @Lazy
    private GspDrpRecordService gspDrpRecordService;

    @Resource
    @Lazy
    private GspTransportTrackRecordService gspTransportTrackRecordService;


    @Resource
    @Lazy
    private GspTransportEntrustedRecordService gspTransportEntrustedRecordService;


    @Resource
    @Lazy
    private GspMaintainConfirmRecordService gspMaintainConfirmRecordService;

    @Resource
    @Lazy
    private GspProMaintainRecordService gspProMaintainRecordService;

    @Resource
    @Lazy
    private GspProRecheckRecordService gspProRecheckRecordService;

    @Resource
    @Lazy
    private GspNgProDestroyRecordService gspNgProDestroyRecordService;

    @Resource
    @Lazy
    private GspProRejectRecordService gspProRejectRecordService;

    @Resource
    @Lazy
    private GspNearTermUrgeSalesRecordService gspNearTermUrgeSalesRecordService;

    @Resource
    @Lazy
    private GspUnsalableUrgeSalesRecordService gspUnsalableUrgeSalesRecordService;

    @Resource
    @Lazy
    private NcrService ncrService;

    @Resource
    @Lazy
    private RecheckService recheckService;
    @Resource
    private GspUnqualifiedRecordService gspUnqualifiedRecordService;
    @Resource
    private GspNgProLossReportRecordService gspNgProLossReportRecordService;

    private Map<String, UploadServiceEntry> serviceMap;

    @Resource
    @Lazy
    private UnsalableForewarnService unsalableForewarnService;


    public UploadServiceEntry getService(String serviceType) {
        ExceptionUtils.isTrue(serviceMap.containsKey(serviceType), "服务类型错误");
        return serviceMap.get(serviceType);
    }

    @PostConstruct
    private void init() {
        serviceMap = new HashMap<>();
        //产品收货记录
        serviceMap.put("gspRecInfo", new UploadServiceEntry(gspRecInfoService, "pageInfo", BaseQuery.class, GspRecInfoQuery.class, GspRecInfoVO.class));
        //产品验收记录
        serviceMap.put("gspProductQcRecord", new UploadServiceEntry(gspProductQcRecordService, "pageInfo", BaseQuery.class, GspProductQcRecordQuery.class, GspProductQcRecordVO.class));
        //产品入库记录
        serviceMap.put("gspPaRecord", new UploadServiceEntry(gspPaRecordService, "pageInfo", BaseQuery.class, GspPaRecordQuery.class, GspPaRecordVO.class));
        //产品出库复核记录
        serviceMap.put("gspReviewRecord", new UploadServiceEntry(gspReviewRecordService, "pageInfo", BaseQuery.class, GspReviewRecordQuery.class, GspReviewRecordVO.class));
        //产品进销存记录
        serviceMap.put("gspDrpRecord", new UploadServiceEntry(gspDrpRecordService, "pageInfo", BaseQuery.class, GspDrpRecordQuery.class, GspDrpRecordVO.class));
        //产品运输追踪记录
        serviceMap.put("gspTransportTrackRecord", new UploadServiceEntry(gspTransportTrackRecordService, "pageInfo", BaseQuery.class, GspTransportTrackRecordQuery.class, GspTransportTrackRecordVO.class));
        //产品委托运输记录
        serviceMap.put("gspTransportEntrustedRecord", new UploadServiceEntry(gspTransportEntrustedRecordService, "pageInfo", BaseQuery.class, GspTransportEntrustedRecordQuery.class, GspTransportEntrustedRecordVO.class));
        //不合格产品记录
        serviceMap.put("gspUnqualifiedRecord", new UploadServiceEntry(gspUnqualifiedRecordService, "pageInfo", BaseQuery.class, GspUnqualifiedRecordQuery.class, GspUnqualifiedRecordVO.class));
        //不合格报损记录
        serviceMap.put("gspUnqualifiedReportingLossesRecord", new UploadServiceEntry(gspNgProLossReportRecordService, "pageInfo", BaseQuery.class, GspNgProLossReportRecordQuery.class, GspNgProLossReportRecordVO.class));

        serviceMap.put("gspMaintainConfirmRecord", new UploadServiceEntry(gspMaintainConfirmRecordService, "pageInfo", BaseQuery.class, GspMaintainConfirmRecordQuery.class, GspMaintainConfirmRecordVO.class));
        serviceMap.put("gspProMaintainRecord", new UploadServiceEntry(gspProMaintainRecordService, "pageInfo", BaseQuery.class, GspProMaintainRecordQuery.class, GspProMaintainRecordVO.class));
        serviceMap.put("gspProRecheckRecord", new UploadServiceEntry(gspProRecheckRecordService, "pageInfo", BaseQuery.class, GspProRecheckRecordQuery.class, GspProRecheckRecordVO.class));
        serviceMap.put("gspNgProDestroyRecord", new UploadServiceEntry(gspNgProDestroyRecordService, "pageInfo", BaseQuery.class, DestroyInfoQueryExt.class, GspNgProDestroyRecordVO.class));
        serviceMap.put("gspProRejectRecord", new UploadServiceEntry(gspProRejectRecordService, "pageInfo", BaseQuery.class, GspProRejectRecordQuery.class, GspProRejectRecordVO.class));
        serviceMap.put("gspNearTermUrgeSalesRecord", new UploadServiceEntry(gspNearTermUrgeSalesRecordService, "pageInfo", BaseQuery.class, GspNearTermUrgeSalesRecordQuery.class, GspNearTermUrgeSalesRecordVO.class));
        serviceMap.put("gspUnsalableUrgeSalesRecord", new UploadServiceEntry(gspUnsalableUrgeSalesRecordService, "pageInfo", BaseQuery.class, GspUnsalableUrgeSalesRecordQuery.class, GspUnsalableUrgeSalesRecordVO.class));

        serviceMap.put("reportPreparation", new UploadServiceEntry(ncrService, "pageInfo", BaseQuery.class, NcrQuery.class, NcrVOExt.class));
        serviceMap.put("qualityReInspectionNotice", new UploadServiceEntry(recheckService, "pageInfo", BaseQuery.class, RecheckQuery.class, RecheckVOExt.class));

        //限销期预警
        serviceMap.put("unsalableForewarn", new UploadServiceEntry(unsalableForewarnService, "pageInfo", BaseQuery.class, UnsalableForewarnQuery.class, UnsalableForewarnVO.class));
    }


    @Data
    @AllArgsConstructor
    public static class UploadServiceEntry {

        /**
         * 服务bean
         */
        private Object uploadService;

        /**
         * 服务方法
         */
        private String methodName;

        /**
         * 方法参数类型（只支持一个）
         */
        private Class<?> paramType;

        /**
         * 真正的查询参数类型
         */
        private Class<?> queryType;

        /**
         * 返回值类型
         */
        private Class<?> returnType;

    }
}

接口查询类：

package com.zhqc.cloud.wms.export.model;

import com.zhqc.framerwork.common.component.excel.vo.ExportRequestVo;
import lombok.Data;

import javax.validation.constraints.NotEmpty;
import java.util.List;
import java.util.Objects;

/**
 * @author zdd
 */
@Data
public class CommonExportQuery extends ExportRequestVo {

    /**
     * 模块名称
     */
    @NotEmpty(message = "模块名称不能为空")
    private String moduleName;

    /**
     * 字段集合
     */
    @NotEmpty(message = "字段集合不能为空")
    private List<String> fieldList;

    /**
     * 字段名集合
     */
    @NotEmpty(message = "字段名集合不能为空")
    private List<String> titles;

    public List<String> getFieldList() {
        fieldList.removeIf(Objects::isNull);
        return fieldList;
    }

    public List<String> getTitles() {
        titles.removeIf(Objects::isNull);
        return titles;
    }
}

自使用列宽工具

package com.zhqc.cloud.wms.util.Excelutils;

import com.alibaba.excel.write.handler.RowWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;

/**
 * @author zdd
 */
@Slf4j
public class CustomerRowWriteHandler implements RowWriteHandler {

    @Override
    public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer relativeRowIndex, Boolean isHead) {
        // 自适应列宽
        int lastColumn = row.getLastCellNum();
        for (int i = 0; i < lastColumn; i++) {
            try {
                String stringCellValue = row.getCell(i).getStringCellValue();
                //获取stringCellValue有多少汉字和多少非汉字
                int count = 0;
                for (int j = 0; j < stringCellValue.length(); j++) {
                    char charAt = stringCellValue.charAt(j);
                    if (charAt >= 0x4E00 && charAt <= 0x9FA5) {
                        count++;
                    }
                }
                //设置列宽
                int columnWidth = row.getSheet().getColumnWidth(i);
                int currentWidth;
                if (count > 0) {
                    currentWidth = isHead ? (count + stringCellValue.length())  << 9 : (count + stringCellValue.length()) << 8;
                } else {
                    currentWidth = isHead ? stringCellValue.length() << 9 : stringCellValue.length() << 8;
                }
                if (columnWidth > currentWidth) {
                    currentWidth = columnWidth;
                }
                row.getSheet().setColumnWidth(i, currentWidth);
            } catch (Exception e) {
                log.info("自适应列宽异常");
            }
        }

        //居中
        CellStyle cellStyle = writeSheetHolder.getSheet().getWorkbook().createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        row.setRowStyle(cellStyle);
    }

}

tianqi11

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
基于easyexcel抽取成的公共导出接口

controllerpackage com.zhqc.cloud.wms.export.controller;import cn.hutool.core.date.DateUtil;import cn.hutool.core.util.ObjectUtil;import cn.hutool.core.util.StrUtil;import cn.hutool.poi.excel.ExcelPicUtil;import cn.hutool.poi.excel.ExcelUtil;import
复制链接

扫一扫