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

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

【先看效果】

模板路径

模板样式

源文件链接 ，Java共享中

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

最终效果

废话少说上代码

1、pom依赖 主要是easyexcel 和 poi

com.alibabaeasyexcel3.3.2com.alibabaeasyexcel-core3.3.2com.alibabaeasyexcel-support3.3.2org.apache.poipoi4.1.2org.apache.poipoi-ooxml4.1.2org.apache.poipoi-ooxml-schemas4.1.2org.apache.commonscommons-collections44.1cglibcglib3.1

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")@GetMappingpublic void exportToExcel(HttpServletResponse response) throws IOException {//要导出的数据List exportTestList = (List) 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 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) {// 重置responseresponse.reset();response.setContentType("application/json");response.setCharacterEncoding("utf-8");Map map = new HashMap();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 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 exportOrderList9 = new ArrayList();//存储9条以后的数据列表List 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) {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 = 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();}}

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

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

打开效果如下