java-classic-code/java-excel/ExcelExportService.java

package cn.exlive.credit.manager.modules.credit.service;

import cn.exlive.credit.manager.core.aspect.annotation.Dict;
import cn.exlive.credit.manager.core.aspect.annotation.Fill;
import cn.exlive.credit.manager.core.system.vo.DictModel;
import cn.exlive.credit.manager.modules.credit.service.impl.EnterpriseService;
import cn.exlive.credit.manager.modules.ledger.cache.MemCached;
import cn.exlive.credit.manager.modules.system.service.ISysDictItemService;
import cn.exlive.credit.manager.modules.system.service.ISysDictService;
import cn.exlive.credit.manager.modules.vehicle.service.IVehicleRecordService;
import cn.hutool.core.util.ReflectUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillWrapper;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.fasterxml.jackson.annotation.JsonFormat;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.time.FastDateFormat;
import org.springframework.core.io.ClassPathResource;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Excel 导出服务
 * <pre>
 *
 * Created by zhenqin.
 * User: zhenqin
 * Date: 2025/1/25
 * Time: 17:51
 * Vendor: yiidata.com
 *
 * </pre>
 *
 * @author zhenqin
 */
@Service
public class ExcelExportService {

    @Resource
    ISysDictService sysDictService;

    @Resource
    ISysDictItemService sysDictItemService;

    @Resource
    EnterpriseService enterpriseService;

    @Resource
    IVehicleRecordService vehicleRecordService;

    /**
     * 开始导出，导出为 excel 字节码
     * @param sheetName
     * @param templateName 采用的模板，传入空则不采用
     * @param dataset 数据集
     * @param extInfo 附加信息
     * @return
     * @throws IOException
     */
    public byte[] export(String sheetName,
                         String templateName,
                         List dataset,
                         Map<String, Object> extInfo) throws IOException {
        if(dataset == null || dataset.isEmpty()) {
            throw new IllegalArgumentException("没有有效的数据行。");
        }
        // 所有时间字段
        final Map<String, JsonFormat> dateFields = new HashMap<>();
        // 所有字典字段
        final Map<String, Dict> dictFields = new HashMap<>();
        // 所有需要转换的字段
        final Map<String, Fill> convertFields = new HashMap<>();
        // 所有字段字段，包含的字典
        final Map<String, Object> dictMap = new HashMap<>();
        final Class<? extends Object> aClass = dataset.get(0).getClass();
        // 不是 map 以及子类，则是 bean 类型，bean 上可能有注解字典
        if(!Map.class.isAssignableFrom(aClass)) {
            // 获取字段上的注解，并把注解 code 上的字典，put 到缓存中
            final Field[] fields = ReflectUtil.getFields(aClass);
            for (Field field : fields) {
                final Dict annos = field.getAnnotation(Dict.class);
                final JsonFormat df = field.getAnnotation(JsonFormat.class);
                final Fill fill = field.getAnnotation(Fill.class);
                if(field.getType() == Date.class && df != null) {
                    // 需要时间格式化的字段
                    dateFields.put(field.getName(), df);
                } else if(fill != null) {
                    // 需要转换填充的字段
                    convertFields.put(field.getName(), fill);
                } else if (annos != null) {
                    // 需要转换的字典码
                    final List<DictModel> dictModels = sysDictService.queryDictItemsByCode(annos.dicCode());
                    if (dictModels == null || dictModels.isEmpty()) {
                        throw new IllegalStateException("非法的字典编码：" + annos.dicCode());
                    }
                    dictFields.put(field.getName(), annos);
                    for (DictModel dictModel : dictModels) {
                        // 放入缓存
                        dictMap.put("DICT:" + annos.dicCode() + ":" + dictModel.getValue(), dictModel.getText());
                    }
                }
            }
        }

        //转换后的列表数据
        List<Map<String, Object>> list = new ArrayList<>(dataset.size());
        for (Object o : dataset) {
            if(o == null) {
                continue;
            } else if(o instanceof Map) {
                list.add((Map)o);
            } else {
                final JSONObject jsonObject = (JSONObject) JSON.toJSON(o);
                // 字典字段
                for (Map.Entry<String, Dict> entry : dictFields.entrySet()) {
                    final String dictVal = jsonObject.getString(entry.getKey());
                    if(StringUtils.isBlank(dictVal)) {
                        // 字典字段是空的，则不需替换
                        continue;
                    }
                    // 替换字典字段
                    final Dict annos = entry.getValue();
                    final Object o1 = dictMap.get("DICT:" + annos.dicCode() + ":" + dictVal);
                    jsonObject.put(entry.getKey(), o1);
                }

                // 日期字段
                for (Map.Entry<String, JsonFormat> entry : dateFields.entrySet()) {
                    final Date dateVal = jsonObject.getDate(entry.getKey());
                    if(dateVal == null) {
                        // 字典字段是空的，则不需替换
                        continue;
                    }

                    // 替换字典字段
                    final JsonFormat df = entry.getValue();
                    // 格式化时间
                    jsonObject.put(entry.getKey(), FastDateFormat.getInstance(df.pattern()).format(dateVal));
                }

                // 需要填充转换的字段
                for (Map.Entry<String, Fill> entry : convertFields.entrySet()) {
                    final String convertVal = jsonObject.getString(entry.getKey());
                    if(StringUtils.isBlank(convertVal)) {
                        // 字典字段是空的，则不需替换
                        continue;
                    }
                    String newVal = convertVal;
                    if("enterprise".equals(entry.getValue().type())) {
                        // 企业
                        newVal = MemCached.CLIENT_NAME.get(Integer.parseInt(convertVal));
                    } else if("vehicle".equals(entry.getValue().type())) {
                        // 车辆
                        newVal = MemCached.VEHICLE_CODE.get(convertVal);
                    }
                    jsonObject.put(entry.getKey(), newVal);
                }
                list.add(jsonObject);
            }
        }

        // dictMap 中已经有了 code 的缓存，下面如果遇到字典码，则直接获取
        ByteArrayOutputStream out = new ByteArrayOutputStream();
        // 有模板则使用模板上的，没有模板，则使用类上的标记
        final ExcelWriterBuilder builder = StringUtils.isNotBlank(templateName) ? EasyExcel.write(out) : EasyExcel.write(out, aClass);
        if(StringUtils.isNotBlank(templateName)) {
            // 有模板，采用模板写入
            final String realTemplate = "/excel/" + templateName;
            ClassPathResource templateStream = new ClassPathResource(realTemplate);
            try(final InputStream inputStream = templateStream.getInputStream();){
                try(final ExcelWriter excelWriter = builder.withTemplate(inputStream).build();) {
                    WriteSheet writeSheet = StringUtils.isNotBlank(sheetName) ?
                            EasyExcel.writerSheet(sheetName).build() : EasyExcel.writerSheet().build();
                    // ext info
                    if(extInfo != null && !extInfo.isEmpty()) {
                        excelWriter.fill(extInfo, writeSheet);
                    }
                    excelWriter.fill(new FillWrapper("dataset", list), writeSheet);
                }
            }
        } else {
            // 没有模板，则直接写入
            if(StringUtils.isNotBlank(sheetName)) {
                builder.sheet(sheetName).doWrite(dataset);
            } else {
                builder.sheet().doWrite(dataset);
            }
        }
        out.flush();
        return out.toByteArray();
    }
}