java easyexcel 基于excel模板填充数据 2023

假客套

已于 2023-10-26 10:13:24 修改

阅读量3.8k

点赞数 10

分类专栏： Exceleasy导出复杂模板文章标签： 1024程序员节 excel java springboot

于 2023-10-24 18:49:39 首次发布

本文链接：https://blog.csdn.net/m0_47484034/article/details/134018329

版权

Exceleasy导出复杂模板专栏收录该内容

2 篇文章 0 订阅

订阅专栏

基于excel模板填充数据，并下载，以及合计转大写工具类

【先看效果】

模板路径

模板样式

源文件链接，Java共享中

share/Java共享https://gitee.com/jiaketao/share.git

最终效果

废话少说上代码

1、pom依赖主要是easyexcel 和 poi

        <!--easyExcel-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.3.2</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel-core</artifactId>
            <version>3.3.2</version>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel-support</artifactId>
            <version>3.3.2</version>
        </dependency>
        <!-- POI导入导出 -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>4.1.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-collections4</artifactId>
            <version>4.1</version>
        </dependency>

        <dependency>
            <groupId>cglib</groupId>
            <artifactId>cglib</artifactId>
            <version>3.1</version>
        </dependency>

2、新建导出测试类



import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;
import lombok.NoArgsConstructor;

/**
 * 导出测试类
 */
@Data
@NoArgsConstructor
@HeadRowHeight(30)
@ContentRowHeight(22)
@TableName("excel_demo_data")
public class ExportTest {
    @ExcelProperty(index = 0, value = "序号")
    private Integer id;
    @ExcelProperty(index =1, value = "姓名")
    private String name;
    @ExcelProperty(index = 2, value = "数量")
    private Integer count;
    @ExcelProperty(index = 3, value = "价格")
    private float price;
}

3、接口测试类

//配置多组数据填充完后，需要换行，防止覆盖模板中的单组数据模板
FillConfig fillConfig = FillConfig.builder().forceNewRow(true).build();

这一行很重要，true是追加，false是覆盖，会覆盖主下边的合计



import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillConfig;
import com.alibaba.fastjson.JSON;
import com.sun.deploy.net.URLEncoder;
import com.xiaoqiu.juyihoutai.pojo.*;
import com.xiaoqiu.juyihoutai.utils.ChineseMoneyUtils;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Api("测试导出excel")
@RestController
@RequestMapping("/test")
public class TestController {

    @ApiOperation("导出excel")
    @GetMapping
    public void exportToExcel(HttpServletResponse response) throws IOException {

        //要导出的数据
        List<ExportTest> exportTestList = (List<ExportTest>) getData().get("exportTestList");
       
       //模板文件
        InputStream templateFile = Thread.currentThread().getContextClassLoader().getResourceAsStream("template/template.xlsx");

        //导出后的文件名
        String fileName = exportTestList.get(0).getName() + "模板";

        //写入
        ExcelWriter excelWriter = null;
        try {

            //本地导出
            //excelWriter = EasyExcel.write(fileName).withTemplate(templateFileName).build();
            //流输出
            excelWriter = EasyExcel.write(response.getOutputStream())
                    .withTemplate(templateFile )
                    .build();

            WriteSheet writeSheet = EasyExcel.writerSheet().build();

            //配置多组数据填充完后，需要换行，防止覆盖模板中的单组数据模板
            FillConfig fillConfig = FillConfig.builder().forceNewRow(true).build();
            // 直接写入list数据
            excelWriter.fill(exportTestList, fillConfig, writeSheet);


            // 写入自定义的表头
            Map<String, Object> map = new HashMap<>();
            map.put("template", "测试");
            map.put("date", "2023-12-15 12:12:00");
            map.put("sumAmountChinese", getData().get("sumAmountChinese"));
            map.put("sumAmount", getData().get("sumAmount"));
            excelWriter.fill(map, writeSheet);

            //浏览器下载操作
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            // 这里URLEncoder.encode可以防止中文乱码
            fileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            excelWriter.finish();

        } catch (Exception e) {
            // 重置response
            response.reset();
            response.setContentType("application/json");
            response.setCharacterEncoding("utf-8");
            Map<String, String> map = new HashMap<String, String>();
            map.put("status", "failure");
            map.put("message", "下载文件失败" + e.getMessage());
            response.getWriter().println(JSON.toJSONString(map));
        } finally {
            // 千万别忘记关闭流
            if (excelWriter != null) {
                excelWriter.finish();
            }
        }

    }


    /**
     * 获取要导出的数据
     */
    public HashMap getData() {

        HashMap hashMap = new HashMap<>();
        List<ExportTest> exportTestList = new ArrayList<>();
        for (int i = 1; i < 10; i++) {
            ExportTest exportTest = new ExportTest();
            exportTest.setId(i);
            exportTest.setName("小明" + i);
            exportTest.setCount(i);
            exportTest.setPrice(5);
            exportTestList.add(exportTest);
        }

        float sumAmount = 0;
        for (ExportTest exportTest : exportTestList) {
            sumAmount += exportTest.getPrice();
        }

        hashMap.put("exportTestList", exportTestList);
        //合计
        hashMap.put("sumAmount", sumAmount);
        //合计大写转换
        hashMap.put("sumAmountChinese", ChineseMoneyUtils.toChineseMoney(BigDecimal.valueOf(sumAmount)));

        return hashMap;
    }
}

如果模板数据（例如模板规定9行）超出，进行分组写入

  //存储前9条的数据列表
            List<ExportOrder> exportOrderList9 = new ArrayList<>();
            //存储9条以后的数据列表
            List<ExportOrder> exportOrderList10 = new ArrayList<>();
            // 如果列表数据数量大于9，分两次方式写入，前9条覆盖写入，9条以后追加不覆盖写入
            if (exportOrderList.size() > 9) {
                //前9条覆盖写入
                for (int i = 0; i < 9; i++) {
                    exportOrderList9.add(exportOrderList.get(i));
                }
                //9条以后追加不覆盖写入
                for (int i = 9; i < exportOrderList.size(); i++) {
                    exportOrderList10.add(exportOrderList.get(i));
                }
                //配置多组数据填充完后，需要换行，false防止覆盖模板中的单组数据模板
                FillConfig fillConfig = FillConfig.builder().forceNewRow(false).build();
                excelWriter.fill(exportOrderList9, fillConfig, writeSheet);
                //配置多组数据填充完后，需要换行，true覆盖模板中的单组数据模板
                FillConfig fillConfig2 = FillConfig.builder().forceNewRow(true).build();
                excelWriter.fill(exportOrderList10, fillConfig2, writeSheet);
            } else {
                //配置多组数据填充完后，需要换行，false防止覆盖模板中的单组数据模板
                FillConfig fillConfig = FillConfig.builder().forceNewRow(false).build();
                // 如果列表数据数量小于9，覆盖写入
                excelWriter.fill(exportOrderList, fillConfig, writeSheet);
            }

4、工具类：合计金额大写转换



import java.math.BigDecimal;
import java.math.RoundingMode;

/**
 * 合计金额 转中文大写工具类
 */
public class ChineseMoneyUtils {
    /**
     * 中文数字
     */
    final static private String[] CHINESE_NUMBER = {"零", "壹", "贰", "叁", "肆", "伍", "陆", "柒", "捌", "玖"};
    /**
     * 中文数字单位
     */
    final static private String[] CHINESE_NUMBER_UNIT = {"", "拾", "佰", "仟", "万", "拾", "佰", "仟", "亿", "拾", "佰", "仟"};
    /**
     * 人民币单位
     */
    final static private String[] CHINESE_MONEY_UNIT = {"圆", "角", "分"};

//    public static void main(String[] args) {
//        String chineseMoney = toChineseMoney(new BigDecimal("7068.52"));
//        System.out.println("chineseMoney = " + chineseMoney);
//    }

    /**
     * @param sourceMoney 要转换的数值，最多支持到亿
     * @return 结果
     */
    public static String toChineseMoney(BigDecimal sourceMoney) {
        if (new BigDecimal("1000000000000").compareTo(sourceMoney) <= 0
                && BigDecimal.ZERO.compareTo(sourceMoney) >= 0) {
            throw new RuntimeException("支持转换的金额范围为0~1万亿");
        }
        StringBuilder sb = new StringBuilder();
        // 整数部分
        BigDecimal intPart = sourceMoney.setScale(0, RoundingMode.DOWN);
        // 小数部分
        BigDecimal decimalPart = sourceMoney.subtract(intPart).multiply(new BigDecimal(100)).setScale(0,
                RoundingMode.DOWN);

        // 处理整数部分圆
        if (intPart.compareTo(BigDecimal.ZERO) > 0) {
            String intPartNumberString = intPart.toPlainString();
            int length = intPartNumberString.length();
            // 统计末尾的零，末尾零不做处理
            int zeroCount = 0;
            for (int i = length - 1; i >= 0; i--) {
                int number = Integer.parseInt(String.valueOf(intPartNumberString.charAt(i)));
                if (number == 0) {
                    zeroCount++;
                } else {
                    break;
                }
            }
            for (int i = 0; i < length; i++) {
                // 如果转换到末尾0，则停止转换
                if (i + zeroCount == length) {
                    break;
                }
                int number = Integer.parseInt(String.valueOf(intPartNumberString.charAt(i)));
                // 获取中文数字
                String chineseNumber = CHINESE_NUMBER[number];
                // 获取中文数字单位
                String chineseNumberUnit = CHINESE_NUMBER_UNIT[length - i - 1];
                sb.append(chineseNumber).append(chineseNumberUnit);
            }
            // 统计完后加上金额单位
            sb.append(CHINESE_MONEY_UNIT[0]);
        } else {
            sb.append(CHINESE_NUMBER[0]).append(CHINESE_MONEY_UNIT[0]);
        }

        // 处理小数部分
        if (decimalPart.compareTo(new BigDecimal(10)) >= 0) {
            // 角
            String jiao = decimalPart.toPlainString();
            int number = Integer.parseInt(String.valueOf(jiao.charAt(0)));
            if (number != 0) {
                String chineseNumber = CHINESE_NUMBER[number];
                sb.append(chineseNumber).append(CHINESE_MONEY_UNIT[1]);
            }

            // 分
            String fen = decimalPart.toPlainString();
            number = Integer.parseInt(String.valueOf(fen.charAt(1)));
            if (number != 0) {
                String chineseNumber = CHINESE_NUMBER[number];
                sb.append(chineseNumber).append(CHINESE_MONEY_UNIT[2]);
            }
        } else if (decimalPart.compareTo(BigDecimal.ZERO) > 0) {
            // 分
            String fen = decimalPart.toPlainString();
            int number = Integer.parseInt(String.valueOf(fen.charAt(0)));
            if (number != 0) {
                String chineseNumber = CHINESE_NUMBER[number];
                sb.append(chineseNumber).append(CHINESE_MONEY_UNIT[2]);
            }
        } else {
            sb.append("整");
        }
        return sb.toString();
    }

}