1.1 背景
记录一下前面项目做了一个Excel
的单元格合并,用的是EasyPoi
,使用后发现比原生的方便些,现在记录一下,也给其他用到合并而且用的是EasyPoi
的小伙伴节省下时间。
1.2 导出模板
需求大致如下图所示:
1.3 环境准备
版本号,自己来定,可以去官网查看:EasyPoi
官网
<!-- easypoi 导入包 -->
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>4.0.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-annotation</artifactId>
<version>4.0.0</version>
</dependency>
1.4 实现代码
//表头设置
List<ExcelExportEntity> colList = new ArrayList<ExcelExportEntity>();
ExcelExportEntity colEntity = new ExcelExportEntity("经销商", "distributorName");
colEntity.setNeedMerge(true);
colEntity.setWidth(20);
colList.add(colEntity);
colEntity = new ExcelExportEntity("科室", "dept");
colEntity.setNeedMerge(true);
colList.add(colEntity);
colEntity = new ExcelExportEntity("部门", "region");
colEntity.setNeedMerge(true);
colList.add(colEntity);
colEntity = new ExcelExportEntity("省份", "province");
colEntity.setNeedMerge(true);
colList.add(colEntity);
colEntity = new ExcelExportEntity("门店数量", "storeNum");
colEntity.setNeedMerge(true);
colEntity.setStatistics(true);
colList.add(colEntity);
Map<String, Integer> map = DateUtils.getLastDayOfMonthByStr(request.getMonthStr());
Integer dayNum = map.get("dayNum");
for (int i = 1; i <= dayNum; i++) {
ExcelExportEntity group_1 = new ExcelExportEntity(i + "日", "day");
List<ExcelExportEntity> exportEntities = new ArrayList<>();
ExcelExportEntity appalyExcel = new ExcelExportEntity("申请数量", "applyNum" + i);
appalyExcel.setStatistics(true);
exportEntities.add(appalyExcel);
ExcelExportEntity adoptExcel = new ExcelExportEntity("通过数量", "adoptNum" + i);
adoptExcel.setStatistics(true);
exportEntities.add(adoptExcel);
group_1.setList(exportEntities);
colList.add(group_1);
}
//文件数据
List<Map<String, Object>> list = new ArrayList<>();
List<StoreNewAddReportVO.DistributorStoreNewAddReportVO> disList = register.getStoreNewAddReportVO().getDistributorStoreNewAddReportVOList();
int size = disList.size();
for (int i = 0; i < size; i++) {
StoreNewAddReportVO.DistributorStoreNewAddReportVO dis = disList.get(i);
Map<String, Object> valMap = new HashMap<>();
valMap.put("distributorName", dis.getDistributorName());
valMap.put("dept", dis.getDept());
valMap.put("region", dis.getRegion());
valMap.put("province", dis.getProvince());
valMap.put("storeNum", dis.getStoreNum());
List<StoreNewAddReportVO.dayData> dayDataList = dis.getDayDataList();
Map<String, List<StoreNewAddReportVO.dayData>> collectMap = Maps.newHashMap();
if (CollectionUtils.isNotEmpty(dayDataList)) {
collectMap = dayDataList.stream().collect(Collectors.groupingBy(StoreNewAddReportVO.dayData::getDayStr));
}
List<Map<String, Object>> list_1 = new ArrayList<>();
Map<String, Object> valMap_1 = new HashMap<>();
for (int j = 1; j <= dayNum; j++) {
List<StoreNewAddReportVO.dayData> dayData = collectMap.get(String.valueOf(j));
int applyflag = 0;
int adoptflag = 0;
if (CollectionUtils.isNotEmpty(dayData)) {
applyflag = dayData.get(0).getApplyNum();
adoptflag = dayData.get(0).getAdoptNum();
}
valMap_1.put("applyNum" + j, applyflag);
valMap_1.put("adoptNum" + j, adoptflag);
}
list_1.add(valMap_1);
valMap.put("day", list_1);
list.add(valMap);
}
//导出
Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("【" + request.getMonthStr() + "】门店注册日明细数据", "数据"), colList, list);
Sheet sheet = workbook.getSheet("数据");
Row row = sheet.getRow(sheet.getLastRowNum());
Cell cell = row.getCell(0);
cell.setCellValue("总计");
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
Font font = workbook.createFont();
font.setFontHeightInPoints((short) 15);
font.setFontName("Trebuchet MS");
cellStyle.setFont(font);
cell.setCellStyle(cellStyle);
CellRangeAddress range_0 = new CellRangeAddress(sheet.getLastRowNum(), sheet.getLastRowNum(), 0, 3);
sheet.addMergedRegion(range_0);
File file = new File("D:\\".concat(UUID.randomUUID().toString().concat(".xls")));
FileOutputStream fileOutputStream = null;
try {
fileOutputStream = new FileOutputStream(file);
workbook.write(fileOutputStream);
} catch (Exception e) {
log.error("门店注册日workbook写入到文件中失败,错误信息:{}", ExceptionUtils.getStackTrace(e));
} finally {
if (null != fileOutputStream) {
try {
fileOutputStream.close();
} catch (IOException e) {
//skip
}
}
}
具体的API
细节就不介绍了,可以去官网查询,关键在于ExcelExportEntity
这个类,它是以map
形式展现的,创建的时候设置key
,设置value
的根据key
进行设置,说明一下,上面一些StoreNewAddReportVO
还有其他是我的业务类。