基于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();
}
}
5、生成接口，将接口复制到浏览器回车就会弹窗提示下载
点击1会跳转2，复制接口
打开效果如下
文章出处登录后可见！
已经登录？立即刷新
