使用easyexcel+poi实现excel模板,动态sheet页导出需求。
一、使用的依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.3.1</version>
</dependency>
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.8.0</version>
</dependency>
二、需求
sheet0页需求是导出的数据为固定的样式。sheet1页的需求是导出多条数据。基于此需求,考虑的是用easyexcel模版导出的方式实现此需求较为简单。
sheet0页样式
sheet1页样式
三、模版的设计
四、问题点
在多公司的情况下,第二页需要动态多sheet页,比如两个公司的就需要两个sheet1页,并导出每个公司下的人员详情。因为easyexcel不支持动态sheet页,要是重写导出又比较复杂,综合考虑使用poi+easyexcel一起实现多公司的导出。
五、功能的实现
导出模版在项目中的位置
导出excel实例代码
import cn.hutool.core.io.resource.ClassPathResource;
import cn.hutool.core.util.NumberUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.util.MapUtils;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillConfig;
import com.alibaba.fastjson2.JSON;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* @author 小红花
* @version 1.0
* @date 2023/12/28
* @description mytest
*/
@RestController
@RequestMapping("test")
@Slf4j
public class MyTestController {
@PostMapping(value = "export")
public void exportExcel(HttpServletRequest request, HttpServletResponse response) throws IOException {
long startTime = System.currentTimeMillis();
System.out.println("导出开始时间:" + startTime + "ms");
//设置输出流格式以及文件名:
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
String fileName = URLEncoder.encode("计提表-导出", "UTF-8");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
ExcelWriter excelWriter = null;
InputStream stream = null;
ByteArrayOutputStream baos = null;
try {
//1.找出模板文件,并转化为输入流;
ClassPathResource classPathResource = new ClassPathResource("export/test/jitinew.xlsx");
stream = classPathResource.getStream();
baos = new ByteArrayOutputStream();
OutputStream outputStream = response.getOutputStream();
//2.数据准备
//sheet0页数据
Map<String, Object> sheet0Data = new HashMap<>();
sheet0Data.put("deptName", "测试部门");
sheet0Data.put("settlementCycle", "2023-12");
sheet0Data.put("contractName", "测试合同名称");
sheet0Data.put("contractNum", "12345678");
sheet0Data.put("predictMoneyWithoutRate", "20000.00");
sheet0Data.put("predictMoney", "21000.00");
sheet0Data.put("attendanceDays", "21");
sheet0Data.put("companyNames", "测试供应商");
//动态sheet页数据
List<Map<String, Object>> sheet1Data = new ArrayList<>();
for (int i = 0; i < 2; i++) {
BigDecimal serveDaysAll = BigDecimal.ZERO;
BigDecimal usrServicePriceAll = BigDecimal.ZERO;
BigDecimal coefficientAssessPriceAll = BigDecimal.ZERO;
BigDecimal predictPriceWithoutRateAll = BigDecimal.ZERO;
BigDecimal predictPriceAll = BigDecimal.ZERO;
//准备sheet1页集合数据
List<Map<String, Object>> sheetListData = new ArrayList<>();
for (int j = 0; j < 5; j++) {
Map<String, Object> sheetList = new HashMap<>();
BigDecimal serveDays = new BigDecimal(21);
BigDecimal usrServicePrice = new BigDecimal(10000);
BigDecimal coefficientAssessPrice = new BigDecimal(20000);
BigDecimal predictPriceWithoutRate = new BigDecimal(30000);
BigDecimal predictPrice = new BigDecimal(31000);
sheetList.put("month", "2023-12");
sheetList.put("realName", "测试人员" + i + j);
sheetList.put("personNum", "1000" + i + j);
sheetList.put("deptName", "测试部门");
sheetList.put("projName", "测试项目");
sheetList.put("serveName", "测试");
sheetList.put("levelName", "中级");
sheetList.put("serveDays", serveDays);
sheetList.put("usrServicePrice", usrServicePrice);
sheetList.put("coefficientAssessPrice", coefficientAssessPrice);
sheetList.put("predictPriceWithoutRate", predictPriceWithoutRate);
sheetList.put("predictPrice", predictPrice);
serveDaysAll = NumberUtil.add(serveDaysAll, serveDays);
usrServicePriceAll = NumberUtil.add(usrServicePriceAll, usrServicePrice);
coefficientAssessPriceAll = NumberUtil.add(coefficientAssessPriceAll, coefficientAssessPrice);
predictPriceWithoutRateAll = NumberUtil.add(predictPriceWithoutRateAll, predictPriceWithoutRate);
predictPriceAll = NumberUtil.add(predictPriceAll, predictPrice);
sheetListData.add(sheetList);
}
//准备sheet1页基础数据
Map<String, Object> sheet1Map = new HashMap<>();
sheet1Map.put("title", "测试sheet" + i);
sheet1Map.put("companyName", "测试公司" + i);
sheet1Map.put("deptName", "2023-12");
sheet1Map.put("serveDaysAll", serveDaysAll);
sheet1Map.put("usrServicePriceAll", usrServicePriceAll);
sheet1Map.put("coefficientAssessPriceAll", coefficientAssessPriceAll);
sheet1Map.put("predictPriceWithoutRateAll", predictPriceWithoutRateAll);
sheet1Map.put("predictPriceAll", predictPriceAll);
sheet1Map.put("sheetListData", sheetListData);
sheet1Data.add(sheet1Map);
}
//3.根据sheet1集合长度,复制模版sheet1页
XSSFWorkbook workbook = new XSSFWorkbook(stream);
for (int i = 1; i < sheet1Data.size(); i++) {
//开始复制sheet1页 ,新的sheet以sheet2开始。(复制sheet页名字不可重复)
workbook.cloneSheet(1, "sheet" + (i + 1));
}
//把workbook写到流里
workbook.write(baos);
byte[] bytes = baos.toByteArray();
stream = new ByteArrayInputStream(bytes);
excelWriter = EasyExcel.write(outputStream).withTemplate(stream).build();
//4.填充模版数据
//写入sheet0
WriteSheet sheetDataWrite = EasyExcel.writerSheet(0).build();
excelWriter.fill(sheet0Data, sheetDataWrite);
//写入动态sheet页基础数据
for (int i = 0; i < sheet1Data.size(); i++) {
//获取sheeti写入对象
sheetDataWrite = EasyExcel.writerSheet(i + 1).build();
//获取数据
Map<String, Object> sheet1Map = sheet1Data.get(i);
List<Map<String, Object>> sheetListData = (List<Map<String, Object>>) sheet1Map.get("sheetListData");
//写入excel
FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
excelWriter.fill(sheetListData, fillConfig, sheetDataWrite);
excelWriter.fill(sheet1Map, sheetDataWrite);
}
} catch (Exception e) {
e.printStackTrace();
// 重置response
response.reset();
response.setContentType("application/json");
response.setCharacterEncoding("utf-8");
Map<String, Object> map = MapUtils.newHashMap();
map.put("code", 500);
map.put("message", "下载文件失败:" + e.getMessage());
response.getWriter().println(JSON.toJSONString(map));
} finally {
//关流
if (excelWriter != null) {
excelWriter.finish();
}
if (baos != null) {
baos.close();
}
if (stream != null) {
stream.close();
}
}
long endTime = System.currentTimeMillis();
System.out.println("导出结束时间:" + endTime + "ms");
System.out.println("导出所用时间:" + (endTime - startTime) / 1000 + "秒");
}
}
实现效果如图