List<List<String>> headList= new ArrayList<>();
List<ProjectSSW> list =new ArrayList<>();
Map<String, List<RowRangeDto>> strategyMap = BizMergeStrategy.addAnnualMerStrategyDay11(list);
//实例化一个字节数组输出流
ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
EasyExcel.write(byteArrayOutputStream, ProjectSSW.class)
.head(headList)
.registerWriteHandler(new TitleSheetWriteHandler("十四五项目导入模板",25)) // 标题及样式,lastCol为标题第0列到底lastCol列的宽度
.relativeHeadRowIndex(1)
.registerWriteHandler(new BizMergeStrategy(strategyMap))
.registerWriteHandler(BizMergeStrategy.CellStyleStrategy())
.sheet("十四五项目导入模板")
.doWrite(list);
//通过流获得byte数组
//通过流获得byte数组
byte[] bytes = byteArrayOutputStream.toByteArray();
//以下文件下载方式固定写法
HttpHeaders httpHeaders = new HttpHeaders();
try {
httpHeaders.setContentDispositionFormData("attachment", URLEncoder.encode("十四五项目导入模板.xlsx","utf-8"));
} catch (Exception e) {
e.printStackTrace();
}
return new ResponseEntity<>(bytes,httpHeaders, HttpStatus.OK);
实体类
@Data
public class ProjectSSW implements Serializable {
private static final long serialVersionUID = 1L;
//项目所属行业 详见字典projectTypexx
@ExcelProperty(value = "项目类型",converter = ProjectTypeConverter.class)
private Integer projectType;
@ExcelProperty(value = "所属行业",converter = ProjectIndustryConverter.class)
private Integer projectIndustry;
//项目名称
@ExcelProperty("项目名称")
private String projectName;
//建设性质 1新建 2续建
@ExcelProperty(value = "建设性质",converter = ConstructionNatureConverter.class)
private Integer constructionNature;
//拟开工时间
@ExcelProperty(value = "拟开工时间", format="yyyy-MM")
private String proposedStartDate;
@ExcelProperty(value = "拟建成时间" , format="yyyy-MM")
//拟完工时间
private String proposedCompletionDate;
//项目类别 A/B/C/D
@ExcelProperty("项目类别")
private String projectCategory;
//建设内容
@ExcelProperty("主要建设内容")
private String constructionContent;
//总投资(拟)
@ExcelProperty("总投资")
private String investmentTotal;
//阶段投资(拟)
@ExcelProperty("“十四五”期间计划完成投资")
private String investmentTemp;
//目前投资
@ExcelProperty("截止9月底累计完成投资")
private String investmentNow;
//是否开工
@ExcelProperty(value = "是否开工",converter = StartStateConverter.class)
private Integer startState;
//建设地点
@ExcelProperty("建设地点")
private String constructionLocation;
//上报单位
@ExcelProperty("上报单位")
private String reportUnit;
@ExcelProperty("备注")
private String remark;
}
public class BizMergeStrategy extends AbstractMergeStrategy {
public static Map<String, List<RowRangeDto>> addAnnualMerStrategyDay11(List<ProjectSSW> projectDtoList) {
Map<String, List<RowRangeDto>> strategyMap = new HashMap<>();
ProjectSSW preUser = null;
for (int i = 0; i < projectDtoList.size(); i++) {
ProjectSSW curUser = projectDtoList.get(i);
//如果名字一样,将名字合并(真正开发中一般不会通过名字这样字段,而是通过一些关联的唯一值,比如父id)
if (preUser != null) {
// 名字相同则合并第一列
// if (curUser.getCategory() == preUser.getCategory()){
// 如果没有标题,只有表头的话,这里为
// BizMergeStrategy.fillStrategyMap(strategyMap, "1", i);
BizMergeStrategy.fillStrategyMap(strategyMap, "0", i+1);
// }
}
preUser = curUser;
}
return strategyMap;
}
}
合并策略和样式
项目开发过程中看前辈写的数据导出为Excel表代码,记录一下