devbox
Cpp五条
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

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

作者：Cpp五条 | 2024-05-18 23:47:09

赞

踩

java excel模板填充

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

【先看效果】

模板路径

模板样式 

源文件链接 ，Java共享中

share/Java共享icon-default.png?t=N7T8https://gitee.com/jiaketao/share.git

最终效果

废话少说上代码

1、pom依赖 主要是easyexcel  和 poi

  1.         <!--easyExcel-->
  2.         <dependency>
  3.             <groupId>com.alibaba</groupId>
  4.             <artifactId>easyexcel</artifactId>
  5.             <version>3.3.2</version>
  6.         </dependency>
  7.         <dependency>
  8.             <groupId>com.alibaba</groupId>
  9.             <artifactId>easyexcel-core</artifactId>
  10.             <version>3.3.2</version>
  11.         </dependency>
  12.  
  13.         <dependency>
  14.             <groupId>com.alibaba</groupId>
  15.             <artifactId>easyexcel-support</artifactId>
  16.             <version>3.3.2</version>
  17.         </dependency>
  18.         <!-- POI导入导出 -->
  19.         <dependency>
  20.             <groupId>org.apache.poi</groupId>
  21.             <artifactId>poi</artifactId>
  22.             <version>4.1.2</version>
  23.         </dependency>
  24.         <dependency>
  25.             <groupId>org.apache.poi</groupId>
  26.             <artifactId>poi-ooxml</artifactId>
  27.             <version>4.1.2</version>
  28.         </dependency>
  29.         <dependency>
  30.             <groupId>org.apache.poi</groupId>
  31.             <artifactId>poi-ooxml-schemas</artifactId>
  32.             <version>4.1.2</version>
  33.         </dependency>
  34.         <dependency>
  35.             <groupId>org.apache.commons</groupId>
  36.             <artifactId>commons-collections4</artifactId>
  37.             <version>4.1</version>
  38.         </dependency>
  39.  
  40.         <dependency>
  41.             <groupId>cglib</groupId>
  42.             <artifactId>cglib</artifactId>
  43.             <version>3.1</version>
  44.         </dependency>

2、新建 导出测试类

  1.  
  2.  
  3. import com.alibaba.excel.annotation.ExcelProperty;
  4. import com.alibaba.excel.annotation.write.style.ContentRowHeight;
  5. import com.alibaba.excel.annotation.write.style.HeadRowHeight;
  6. import com.baomidou.mybatisplus.annotation.TableName;
  7. import lombok.Data;
  8. import lombok.NoArgsConstructor;
  9.  
  10. /**
  11.  * 导出测试类
  12.  */
  13. @Data
  14. @NoArgsConstructor
  15. @HeadRowHeight(30)
  16. @ContentRowHeight(22)
  17. @TableName("excel_demo_data")
  18. public class ExportTest {
  19.     @ExcelProperty(index = 0, value = "序号")
  20.     private Integer id;
  21.     @ExcelProperty(index =1, value = "姓名")
  22.     private String name;
  23.     @ExcelProperty(index = 2, value = "数量")
  24.     private Integer count;
  25.     @ExcelProperty(index = 3, value = "价格")
  26.     private float price;
  27. }

3、接口测试类

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

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

  1.  
  2.  
  3. import com.alibaba.excel.EasyExcel;
  4. import com.alibaba.excel.ExcelWriter;
  5. import com.alibaba.excel.write.metadata.WriteSheet;
  6. import com.alibaba.excel.write.metadata.fill.FillConfig;
  7. import com.alibaba.fastjson.JSON;
  8. import com.sun.deploy.net.URLEncoder;
  9. import com.xiaoqiu.juyihoutai.pojo.*;
  10. import com.xiaoqiu.juyihoutai.utils.ChineseMoneyUtils;
  11. import io.swagger.annotations.Api;
  12. import io.swagger.annotations.ApiOperation;
  13. import org.springframework.web.bind.annotation.GetMapping;
  14. import org.springframework.web.bind.annotation.RequestMapping;
  15. import org.springframework.web.bind.annotation.RestController;
  16.  
  17. import javax.servlet.http.HttpServletResponse;
  18. import java.io.IOException;
  19. import java.math.BigDecimal;
  20. import java.util.ArrayList;
  21. import java.util.HashMap;
  22. import java.util.List;
  23. import java.util.Map;
  24.  
  25. @Api("测试导出excel")
  26. @RestController
  27. @RequestMapping("/test")
  28. public class TestController {
  29.  
  30.     @ApiOperation("导出excel")
  31.     @GetMapping
  32.     public void exportToExcel(HttpServletResponse response) throws IOException {
  33.  
  34.         //要导出的数据
  35.         List<ExportTest> exportTestList = (List<ExportTest>) getData().get("exportTestList");
  36.        
  37.        //模板文件
  38.         InputStream templateFile = Thread.currentThread().getContextClassLoader().getResourceAsStream("template/template.xlsx");
  39.  
  40.         //导出后的文件名
  41.         String fileName = exportTestList.get(0).getName() + "模板";
  42.  
  43.         //写入
  44.         ExcelWriter excelWriter = null;
  45.         try {
  46.  
  47.             //本地导出
  48.             //excelWriter = EasyExcel.write(fileName).withTemplate(templateFileName).build();
  49.             //流输出
  50.             excelWriter = EasyExcel.write(response.getOutputStream())
  51.                     .withTemplate(templateFile )
  52.                     .build();
  53.  
  54.             WriteSheet writeSheet = EasyExcel.writerSheet().build();
  55.  
  56.             //配置多组数据填充完后，需要换行，防止覆盖模板中的单组数据模板
  57.             FillConfig fillConfig = FillConfig.builder().forceNewRow(true).build();
  58.             // 直接写入list数据
  59.             excelWriter.fill(exportTestList, fillConfig, writeSheet);
  60.  
  61.  
  62.             // 写入自定义的表头
  63.             Map<String, Object> map = new HashMap<>();
  64.             map.put("template", "测试");
  65.             map.put("date", "2023-12-15 12:12:00");
  66.             map.put("sumAmountChinese", getData().get("sumAmountChinese"));
  67.             map.put("sumAmount", getData().get("sumAmount"));
  68.             excelWriter.fill(map, writeSheet);
  69.  
  70.             //浏览器下载操作
  71.             response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
  72.             response.setCharacterEncoding("utf-8");
  73.             // 这里URLEncoder.encode可以防止中文乱码
  74.             fileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");
  75.             response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
  76.             excelWriter.finish();
  77.  
  78.         } catch (Exception e) {
  79.             // 重置response
  80.             response.reset();
  81.             response.setContentType("application/json");
  82.             response.setCharacterEncoding("utf-8");
  83.             Map<String, String> map = new HashMap<String, String>();
  84.             map.put("status", "failure");
  85.             map.put("message", "下载文件失败" + e.getMessage());
  86.             response.getWriter().println(JSON.toJSONString(map));
  87.         } finally {
  88.             // 千万别忘记关闭流
  89.             if (excelWriter != null) {
  90.                 excelWriter.finish();
  91.             }
  92.         }
  93.  
  94.     }
  95.  
  96.  
  97.     /**
  98.      * 获取要导出的数据
  99.      */
  100.     public HashMap getData() {
  101.  
  102.         HashMap hashMap = new HashMap<>();
  103.         List<ExportTest> exportTestList = new ArrayList<>();
  104.         for (int i = 1; i < 10; i++) {
  105.             ExportTest exportTest = new ExportTest();
  106.             exportTest.setId(i);
  107.             exportTest.setName("小明" + i);
  108.             exportTest.setCount(i);
  109.             exportTest.setPrice(5);
  110.             exportTestList.add(exportTest);
  111.         }
  112.  
  113.         float sumAmount = 0;
  114.         for (ExportTest exportTest : exportTestList) {
  115.             sumAmount += exportTest.getPrice();
  116.         }
  117.  
  118.         hashMap.put("exportTestList", exportTestList);
  119.         //合计
  120.         hashMap.put("sumAmount", sumAmount);
  121.         //合计大写转换
  122.         hashMap.put("sumAmountChinese", ChineseMoneyUtils.toChineseMoney(BigDecimal.valueOf(sumAmount)));
  123.  
  124.         return hashMap;
  125.     }
  126. }
如果模板数据（例如模板规定9行）超出，进行分组写入
  1.   //存储前9条的数据列表
  2.             List<ExportOrder> exportOrderList9 = new ArrayList<>();
  3.             //存储9条以后的数据列表
  4.             List<ExportOrder> exportOrderList10 = new ArrayList<>();
  5.             // 如果列表数据数量大于9，分两次方式写入，前9条覆盖写入，9条以后追加不覆盖写入
  6.             if (exportOrderList.size() > 9) {
  7.                 //前9条覆盖写入
  8.                 for (int i = 0; i < 9; i++) {
  9.                     exportOrderList9.add(exportOrderList.get(i));
  10.                 }
  11.                 //9条以后追加不覆盖写入
  12.                 for (int i = 9; i < exportOrderList.size(); i++) {
  13.                     exportOrderList10.add(exportOrderList.get(i));
  14.                 }
  15.                 //配置多组数据填充完后，需要换行，false防止覆盖模板中的单组数据模板
  16.                 FillConfig fillConfig = FillConfig.builder().forceNewRow(false).build();
  17.                 excelWriter.fill(exportOrderList9, fillConfig, writeSheet);
  18.                 //配置多组数据填充完后，需要换行，true覆盖模板中的单组数据模板
  19.                 FillConfig fillConfig2 = FillConfig.builder().forceNewRow(true).build();
  20.                 excelWriter.fill(exportOrderList10, fillConfig2, writeSheet);
  21.             } else {
  22.                 //配置多组数据填充完后，需要换行，false防止覆盖模板中的单组数据模板
  23.                 FillConfig fillConfig = FillConfig.builder().forceNewRow(false).build();
  24.                 // 如果列表数据数量小于9，覆盖写入
  25.                 excelWriter.fill(exportOrderList, fillConfig, writeSheet);
  26.             }

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

  1.  
  2.  
  3. import java.math.BigDecimal;
  4. import java.math.RoundingMode;
  5.  
  6. /**
  7.  * 合计金额 转中文大写工具类
  8.  */
  9. public class ChineseMoneyUtils {
  10.     /**
  11.      * 中文数字
  12.      */
  13.     final static private String[] CHINESE_NUMBER = {"零", "壹", "贰", "叁", "肆", "伍", "陆", "柒", "捌", "玖"};
  14.     /**
  15.      * 中文数字单位
  16.      */
  17.     final static private String[] CHINESE_NUMBER_UNIT = {"", "拾", "佰", "仟", "万", "拾", "佰", "仟", "亿", "拾", "佰", "仟"};
  18.     /**
  19.      * 人民币单位
  20.      */
  21.     final static private String[] CHINESE_MONEY_UNIT = {"圆", "角", "分"};
  22.  
  23. //    public static void main(String[] args) {
  24. //        String chineseMoney = toChineseMoney(new BigDecimal("7068.52"));
  25. //        System.out.println("chineseMoney = " + chineseMoney);
  26. //    }
  27.  
  28.     /**
  29.      * @param sourceMoney 要转换的数值，最多支持到亿
  30.      * @return 结果
  31.      */
  32.     public static String toChineseMoney(BigDecimal sourceMoney) {
  33.         if (new BigDecimal("1000000000000").compareTo(sourceMoney) <= 0
  34.                 && BigDecimal.ZERO.compareTo(sourceMoney) >= 0) {
  35.             throw new RuntimeException("支持转换的金额范围为0~1万亿");
  36.         }
  37.         StringBuilder sb = new StringBuilder();
  38.         // 整数部分
  39.         BigDecimal intPart = sourceMoney.setScale(0, RoundingMode.DOWN);
  40.         // 小数部分
  41.         BigDecimal decimalPart = sourceMoney.subtract(intPart).multiply(new BigDecimal(100)).setScale(0,
  42.                 RoundingMode.DOWN);
  43.  
  44.         // 处理整数部分圆
  45.         if (intPart.compareTo(BigDecimal.ZERO) > 0) {
  46.             String intPartNumberString = intPart.toPlainString();
  47.             int length = intPartNumberString.length();
  48.             // 统计末尾的零，末尾零不做处理
  49.             int zeroCount = 0;
  50.             for (int i = length - 1; i >= 0; i--) {
  51.                 int number = Integer.parseInt(String.valueOf(intPartNumberString.charAt(i)));
  52.                 if (number == 0) {
  53.                     zeroCount++;
  54.                 } else {
  55.                     break;
  56.                 }
  57.             }
  58.             for (int i = 0; i < length; i++) {
  59.                 // 如果转换到末尾0，则停止转换
  60.                 if (i + zeroCount == length) {
  61.                     break;
  62.                 }
  63.                 int number = Integer.parseInt(String.valueOf(intPartNumberString.charAt(i)));
  64.                 // 获取中文数字
  65.                 String chineseNumber = CHINESE_NUMBER[number];
  66.                 // 获取中文数字单位
  67.                 String chineseNumberUnit = CHINESE_NUMBER_UNIT[length - i - 1];
  68.                 sb.append(chineseNumber).append(chineseNumberUnit);
  69.             }
  70.             // 统计完后加上金额单位
  71.             sb.append(CHINESE_MONEY_UNIT[0]);
  72.         } else {
  73.             sb.append(CHINESE_NUMBER[0]).append(CHINESE_MONEY_UNIT[0]);
  74.         }
  75.  
  76.         // 处理小数部分
  77.         if (decimalPart.compareTo(new BigDecimal(10)) >= 0) {
  78.             // 角
  79.             String jiao = decimalPart.toPlainString();
  80.             int number = Integer.parseInt(String.valueOf(jiao.charAt(0)));
  81.             if (number != 0) {
  82.                 String chineseNumber = CHINESE_NUMBER[number];
  83.                 sb.append(chineseNumber).append(CHINESE_MONEY_UNIT[1]);
  84.             }
  85.  
  86.             // 分
  87.             String fen = decimalPart.toPlainString();
  88.             number = Integer.parseInt(String.valueOf(fen.charAt(1)));
  89.             if (number != 0) {
  90.                 String chineseNumber = CHINESE_NUMBER[number];
  91.                 sb.append(chineseNumber).append(CHINESE_MONEY_UNIT[2]);
  92.             }
  93.         } else if (decimalPart.compareTo(BigDecimal.ZERO) > 0) {
  94.             // 分
  95.             String fen = decimalPart.toPlainString();
  96.             int number = Integer.parseInt(String.valueOf(fen.charAt(0)));
  97.             if (number != 0) {
  98.                 String chineseNumber = CHINESE_NUMBER[number];
  99.                 sb.append(chineseNumber).append(CHINESE_MONEY_UNIT[2]);
  100.             }
  101.         } else {
  102.             sb.append("整");
  103.         }
  104.         return sb.toString();
  105.     }
  106.  
  107. }

5、生成接口，将接口复制到浏览器回车就会弹窗提示下载

点击1会跳转2，复制接口

打开效果如下

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/Cpp五条/article/detail/590449
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号