1.技术jxls

代码 https://gitee.com/J-summit/note-sty-blogs/tree/master/src/main/java/tech/cn/note/excel
可支持自定义函数,复杂字符替换，批量插入等功能

image.png

image.png

依赖引入

    implementation group: 'org.jxls', name: 'jxls', version: '2.9.0'
    implementation group: 'org.jxls', name: 'jxls-poi', version: '2.9.0'
    implementation group: 'org.jxls', name: 'jxls-jexcel', version: '1.0.9'
    implementation group: 'org.jxls', name: 'jxls-reader', version: '2.0.6'

2.代码实现

package tech.tongyu.bct.features.utils.poi.jxsl;

import org.apache.commons.collections4.MapUtils;
import org.apache.commons.io.IOUtils;
import org.apache.commons.jexl3.JexlBuilder;
import org.apache.commons.jexl3.JexlEngine;
import org.jxls.common.Context;
import org.jxls.transform.TransformationConfig;
import org.jxls.transform.Transformer;
import org.jxls.util.JxlsHelper;
import org.springframework.stereotype.Service;
import tech.tongyu.bct.common.exception.CustomException;

import javax.annotation.PostConstruct;
import java.io.*;
import java.util.HashMap;
import java.util.Map;

@Service
public class JXLSTemplateDataFillServiceImpl implements JXLSTemplateDataFillService {

    private JxlsHelper jxlsHelper;

    private JexlEngine jexlEngine;

    @PostConstruct
    private void JXLSProcessInit() {
        jxlsHelper = JxlsHelper.getInstance();
        Map<String, Object> functionMap = new HashMap<>();
        functionMap.put(JXLSFunction.FUNCTION_NAME, JXLSFunction.getInstance());
        JexlBuilder jexlBuilder = new JexlBuilder();
        jexlBuilder.namespaces(functionMap);
        jexlEngine = jexlBuilder.create();
    }

    @Override
    public byte[] processToByte(File templateFile, Map<String, Object> dataSource) throws IOException, CustomException {
        FileInputStream inputStream = new FileInputStream(templateFile);
        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        doProcess(inputStream, outputStream, dataSource);
        return outputStream.toByteArray();
    }

    @Override
    public byte[] processToByte(InputStream inputStream, Map<String, Object> dataSource) throws CustomException, IOException {
        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        doProcess(inputStream, outputStream, dataSource);
        return outputStream.toByteArray();
    }

    @Override
    public byte[] processToPdfByte(File templateFile, Map<String, Object> dataSource) throws Exception {
        byte[] dataByte = processToByte(templateFile, dataSource);
        return null; // todo FileUtil.doPdfProcessByAsposeOfExcel(dataByte);
    }

    @Override
    public void writeToFile(File templateFile, File outFile, Map<String, Object> dataSource) throws Exception {
        byte[] dataByte = processToByte(templateFile, dataSource);
        FileOutputStream fileOutputStream = new FileOutputStream(outFile);
        fileOutputStream.write(dataByte);
        fileOutputStream.close();
    }

    @Override
    public void writeToOutputStream(File templateFile, OutputStream outPut, Map<String, Object> dataSource)
            throws IOException, CustomException {
        FileInputStream inputStream = new FileInputStream(templateFile);
        doProcess(inputStream, outPut, dataSource);
    }

    @Override
    public void writeToOutputStream(InputStream inputStream, OutputStream outPut, Map<String, Object> dataSource)
            throws CustomException, IOException {
        doProcess(inputStream, outPut, dataSource);
    }

    /**
     * 根据模板将数据填充至输出流中
     *
     * @param inputStream
     * @param outputStream
     * @param dataSource
     * @throws CustomException
     */
    private synchronized void doProcess(InputStream inputStream,
                                        OutputStream outputStream,
                                        Map<String, Object> dataSource) throws CustomException, IOException {
        Context dataContext = new Context();
        if (MapUtils.isNotEmpty(dataSource)) {
            dataSource.forEach(dataContext::putVar);
        }
        Transformer transformer = jxlsHelper.createTransformer(inputStream, outputStream);
        transformer.getTransformationConfig().setExpressionEvaluator(new CustomJexlExpressionEvaluator());
        TransformationConfig transformationConfig = transformer.getTransformationConfig();
        CustomJexlExpressionEvaluator evaluator = (CustomJexlExpressionEvaluator) transformationConfig.getExpressionEvaluator();
        evaluator.setJexlEngine(jexlEngine);
        jxlsHelper.setUseFastFormulaProcessor(false).processTemplate(dataContext, transformer);
        IOUtils.closeQuietly(inputStream, outputStream);
    }
}

3.自定义方法

package tech.tongyu.bct.features.utils.poi.jxsl;

import cn.hutool.core.convert.NumberChineseFormatter;
import cn.hutool.core.date.DatePattern;
import cn.hutool.core.text.CharSequenceUtil;
import cn.hutool.core.util.StrUtil;
import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.core.type.TypeReference;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.google.common.collect.Lists;
import org.apache.commons.lang3.StringUtils;
import tech.tongyu.bct.common.util.BigDecimalUtil;
import tech.tongyu.bct.common.util.DateTimeUtils;
import tech.tongyu.bct.common.util.EnumUtils;

import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.Collection;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.stream.Collectors;

import static cn.hutool.core.text.CharSequenceUtil.NULL;
import static tech.tongyu.bct.common.util.BigDecimalUtil.isZero;

/***JXLS模板填充自定义函数*/
public class JXLSFunction {

    public static final String FUNCTION_NAME = "Function";

    private static final JXLSFunction jxlsFunction = new JXLSFunction();

    private JXLSFunction() {
    }

    public static JXLSFunction getInstance() {
        return jxlsFunction;
    }

    public String doNothing(Object value) {
        return value == null ? "" : String.valueOf(value);
    }

    public String dateFormat(Object value) {
        LocalDate resultDate = DateTimeUtils.toLocalDate(value);
        return resultDate == null ? "" : resultDate.toString();
    }

    public String dateFormat(Object value, String formatter) {
        DateTimeFormatter dateTimeFormatter = DatePattern.NORM_DATE_FORMATTER;
        if (StringUtils.isNotBlank(formatter)) {
            dateTimeFormatter = DateTimeFormatter.ofPattern(formatter);
        }
        LocalDate resultDate = DateTimeUtils.toLocalDate(value);
        return resultDate == null ? "" : resultDate.format(dateTimeFormatter);
    }

    public Integer daysBetween(Object value1, Object value2) {
        LocalDate startLocalDate = DateTimeUtils.toLocalDate(value1);
        LocalDate endLocalDate = DateTimeUtils.toLocalDate(value2);
        if (Objects.isNull(startLocalDate) || Objects.isNull(endLocalDate)) {
            return null;
        }
        return (int) (startLocalDate.toEpochDay() - endLocalDate.toEpochDay());
    }

    public static Object nvl(Object value1, Object value2) {
        if (Objects.isNull(value1) || StrUtil.isEmpty(value1.toString())) {
            return value2;
        } else {
            return value1;
        }
    }

    public static String divide(Object value1, Object value2) {
        return divide(value1, value2, 100);
    }

    public static String divide(Object value1, Object value2, Object value3) {
        BigDecimal dividend = BigDecimalUtil.parse(value1);
        BigDecimal divisor = BigDecimalUtil.parse(value2);
        if (Objects.isNull(dividend) || isZero(divisor)) {
            return "0";
        }

        int scale = 100;
        if (Objects.nonNull(value3)) {
            scale = (int) value3;
        }
        return dividend.divide(divisor, scale, RoundingMode.HALF_UP).toPlainString();
    }

    public String numNegate(Object value) {
        return BigDecimalUtil.parseOrZero(value).negate().toPlainString();
    }

    public String numFormat(Object value) {
        BigDecimal num = BigDecimalUtil.parseOrZero(value);
        return num.stripTrailingZeros().toPlainString();
    }

    public String numFormat(Object value, int pointNum) {
        BigDecimal num = BigDecimalUtil.parseOrZero(value);
        return num.setScale(pointNum, RoundingMode.HALF_UP).toPlainString();
    }

    public String numFormat(Object value, int pointNum, int tag) {
        BigDecimal num = BigDecimalUtil.parseOrZero(value);
        BigDecimal numTag = BigDecimal.valueOf(tag);
        return BigDecimalUtil.multiply(num, numTag).setScale(pointNum, RoundingMode.HALF_UP).toPlainString();
    }

    public String thdNum(Object value) {
        BigDecimal num = BigDecimalUtil.parseOrZero(value);
        return BigDecimalUtil.formatThdByPara(num);
    }

    public String thdNum(Object value, int pointNum) {
        BigDecimal num = BigDecimalUtil.parseOrZero(value);
        return BigDecimalUtil.formatThd(num, pointNum);
    }

    public String thdNum(Object value, int pointNum, int tag) {
        BigDecimal num = BigDecimalUtil.parseOrZero(value);
        BigDecimal numTag = BigDecimal.valueOf(tag);
        return BigDecimalUtil.formatThd(BigDecimalUtil.multiply(num, numTag), pointNum);
    }

    public String chineseMoney(Object value) {
        return value == null ? "" : NumberChineseFormatter.format(Double.parseDouble(value.toString()), true, true);
    }

    public String percent(Object value) {
        BigDecimal num = BigDecimalUtil.parse(value);
        return num == null ? "" : BigDecimalUtil.percentFormat(num, num.scale());
    }

    public String percent(Object value, int pointNum) {
        BigDecimal num = BigDecimalUtil.parse(value);
        return num == null ? "" : BigDecimalUtil.percentFormat(num, pointNum);
    }

    public String percentWithoutZero(Object value, int pointNum) {
        BigDecimal num = BigDecimalUtil.parse(value);
        return isZero(num) ? "" : BigDecimalUtil.percentFormat(num, pointNum);
    }

    public String thousandth(Object value) {
        BigDecimal num = BigDecimalUtil.parse(value);
        return num == null ? "" : BigDecimalUtil.thousandthFormat(num, num.scale());
    }

    public String thousandth(Object value, int pointNum) {
        BigDecimal num = BigDecimalUtil.parse(value);
        return num == null ? "" : BigDecimalUtil.thousandthFormat(num, pointNum);
    }

    public String getEnumDesc(String enumPath, String name) throws ClassNotFoundException {
        if (StrUtil.hasBlank(enumPath, name)) {
            return null;
        }
        return EnumUtils.getDesc((Class<? extends Enum<?>>) Class.forName(enumPath), name);
    }

    public String beanToList(Object data, String field, String delimiter) {
        List<String> list = obj2List(data, field);
        return String.join(StrUtil.blankToDefault(delimiter, ","), list);
    }

    public String beanToList(Object data, String field) {
        List<String> list = obj2List(data, field);
        return String.join(",", list);
    }

    private static List<String> obj2List(Object data, String field) {
        List<String> list = Lists.newArrayList();
        // data为list
        if (data instanceof Collection) {
            list = ((List<?>) data).stream().map(v -> {
                try {
                    if (v instanceof Map) {
                        Map<String, Object> map = (Map<String, Object>) v;
                        Object mapField = map.get(field);
                        return mapField instanceof BigDecimal
                                ? ((BigDecimal) mapField).toPlainString()
                                : String.valueOf(mapField);
                    } else {
                        Field declaredField = v.getClass().getDeclaredField(field);
                        declaredField.setAccessible(true);
                        return declaredField.get(v).toString();
                    }
                } catch (NoSuchFieldException | IllegalAccessException ignored) {
                    // 不打日志了 避免刷屏
                    return null;
                }
            }).filter(v -> CharSequenceUtil.isNotBlank(v) && !NULL.equals(v)).collect(Collectors.toList());
        } else if (data instanceof String) {
            // data为字符串（由list转）
            ObjectMapper objectMapper = new ObjectMapper();
            try {
                List<Map<String, Object>> mapList = objectMapper.readValue(data.toString(),
                        new TypeReference<List<Map<String, Object>>>() {
                        });
                list = mapList.stream()
                        .filter(map -> map.containsKey(field))
                        .map(map -> map.get(field) instanceof BigDecimal
                                ? ((BigDecimal) map.get(field)).toPlainString()
                                : String.valueOf(map.get(field))
                        )
                        .filter(v -> CharSequenceUtil.isNotBlank(v) && !NULL.equals(v))
                        .collect(Collectors.toList());
            } catch (JsonProcessingException e) {
                // 不打日志了 避免刷屏
            }
        }
        return list;
    }
}

4.自定义异常处理

package tech.tongyu.bct.features.utils.poi.jxsl;

import java.util.HashMap;
import java.util.Map;

import lombok.extern.slf4j.Slf4j;
import org.apache.commons.jexl3.JexlContext;
import org.apache.commons.jexl3.JexlExpression;
import org.apache.commons.jexl3.MapContext;
import org.jxls.expression.JexlExpressionEvaluator;

/**
 * jxls填充占位符时，JexlExpressionEvaluator在catch到Exception后处理方式为
 * throw new EvaluationException("An error occurred when evaluating expression " + expression, e);
 * 错误日志会刷屏，为了避免刷屏，重写JexlExpressionEvaluator的异常处理
 */
@Slf4j
public class CustomJexlExpressionEvaluator extends JexlExpressionEvaluator {
    private static final ThreadLocal<Map<String, JexlExpression>> expressionMapThreadLocal = ThreadLocal.withInitial(HashMap::new);

    @Override
    public Object evaluate(String expression, Map<String, Object> context) {
        JexlContext jexlContext = new MapContext(context);
        try {
            Map<String, JexlExpression> expressionMap = expressionMapThreadLocal.get();
            JexlExpression jexlExpression = expressionMap.get(expression);
            if (jexlExpression == null) {
                jexlExpression = getJexlEngine().createExpression(expression);
                expressionMap.put(expression, jexlExpression);
            }
            return jexlExpression.evaluate(jexlContext);
        } catch (Exception e) {
            log.warn("An error occurred when evaluating expression[{}]：{}", expression, e.getMessage());
            return null;
        }
    }

}

5.占位符填充

1.在Excel表格第一单元格即A1，添加批注如下:jx:area(lastCell="I2")，锁定填充模板作用域范围

image.png

2.在输出行第一个单元格，添加批注如下: jx:each(items="contractList1" var="itemValue" lastCell="I2"),

image.png

lastCell表示锁定作用域范围。

5.1项目实战案例

image.png

    @Operation(summary = "导出持仓明细")
    @PostMapping("/export")
    public ResponseEntity<byte[]> exportToExcel(@RequestBody @Validated ReportQueryDTO reportDTO) throws Exception {
        List<CustomPositionReportDTO> positionReportList = positionReportService.listPositionReport(reportDTO);
        List<String> tradeIds = positionReportList.stream().map(CustomPositionReportDTO::getTradeId).collect(Collectors.toList());
        TradeCriteria build = TradeCriteria.builder().tradeIds(tradeIds).build();
        List<TradeDTO> trades = tradeQueryExternalClient.listTrade(build);
        Map<String, TradeDTO> tradeMap = trades.stream().collect(Collectors.toMap(TradeDTO::getTradeId, Function.identity(), (k1, k2) -> k1));
        for (CustomPositionReportDTO prl : positionReportList) {
            prl.setTrade(tradeMap.get(prl.getTradeId()));
        }
        HttpHeaders headers = new HttpHeaders();
        String fileName = String.format("日终-%s.xlsx", reportDTO.getValuationDate().format(DateTimeFormatter.ofPattern("yyyy-MM-dd")));
        headers.add("Content-Disposition", String.format("attachment; filename=%s", UriUtils.encode(fileName, "UTF-8")));
        InputStream stream = Files.newInputStream(Paths.get(tmpPath + positionFileName));
        Map<String, Object> map = new HashMap<>();
        map.put("list", positionReportList);
        byte[] bytes = jxlsTemplateDataFillService.processToByte(stream, map);
        return ResponseEntity.ok().headers(headers).body(bytes);
    }

6.其他占位符demo