Java中使用EasyExcel写excel文件

 1、公式

package com.web.report.handler;import com.alibaba.excel.context.WriteContext;
import com.alibaba.excel.metadata.csv.CsvCellStyle;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.handler.context.CellWriteHandlerContext;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;import java.util.List;@Slf4j
public class CustomCellWriteHandler implements CellWriteHandler {@Overridepublic void afterCellDispose(CellWriteHandlerContext context) {Cell cell = context.getCell();// 这里可以对cell进行任何操作int rowIndex = cell.getRowIndex();String cellValue = cell.getStringCellValue();if (cellValue.startsWith("$$") && cellValue.endsWith("$$")) {String statisType = cellValue.substring(2, cellValue.length() - 2);String columnTitle = convertToTitle(cell.getColumnIndex() + 1);switch (statisType) {case "min":cell.setCellFormula("min(" + columnTitle + (rowIndex + 5) + ":" + columnTitle + 1000000 + ")");break;case "max":cell.setCellFormula("max(" + columnTitle + (rowIndex + 4) + ":" + columnTitle + 1000000 + ")");break;
//                case "testNum":
//                    cell.setCellFormula("COUNTA(A" + (rowIndex + 1 + 3) + ":A" + 1000000 + ")");
//                    break;
//                case "median":
//                    cell.setCellFormula("MEDIAN(" + columnTitle + (rowIndex + 3) + ":" + columnTitle + 1000000 + ")");
//                    break;case "avg":cell.setCellFormula("IF(COUNT(" + columnTitle + (rowIndex + 3) + ":" + columnTitle + 1000000 + ")>0,AVERAGE(" + columnTitle + (rowIndex + 3) + ":" + columnTitle + 1000000 + "),0)");break;case "stddev":cell.setCellFormula("IF(COUNT(" + columnTitle + (rowIndex + 2) + ":" + columnTitle + 1000000 + ")>1,STDEV(" + columnTitle + (rowIndex + 2) + ":" + columnTitle + 1000000 + "),0)");break;}} else {try {Double value = Double.valueOf(cellValue);cell.setCellValue(value);} catch (Exception e) {}}}public String convertToTitle(int n) {StringBuilder sb = new StringBuilder();while(n > 0){n--; // 重点sb.append((char)(n % 26 + 'A'));n /= 26;}sb.reverse();return sb.toString();}}

2、合并

package com.web.report.handler;import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.merge.AbstractMergeStrategy;
import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;public class CustomMergeStrategy extends AbstractMergeStrategy {private Integer columnLength;private Sheet sheet;public CustomMergeStrategy(Integer columnLength) {this.columnLength = columnLength;}// 合并成一个单元格private void mergeCommonColumn(Integer rowIndexStart, Integer rowIndexEnd,Integer columnIndexStart,Integer columnIndexEnd ) {CellRangeAddress cellRangeAddress = new CellRangeAddress(rowIndexStart, rowIndexEnd, columnIndexStart, columnIndexEnd);sheet.addMergedRegionUnsafe(cellRangeAddress);}@Overrideprotected void merge(Sheet sheet, Cell cell, Head head, Integer integer) {this.sheet = sheet;if (cell.getRowIndex() < 13) {this.mergeCommonColumn(cell.getRowIndex(),cell.getRowIndex(),1,this.columnLength-1);}}}

3、样式

package com.web.report.style;import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import org.apache.poi.ss.usermodel.*;public class StyleUtils {/*** 标题样式* @return*/public static WriteCellStyle getHeadStyle(){// 头的策略WriteCellStyle headWriteCellStyle = new WriteCellStyle();// 背景颜色
//        headWriteCellStyle.setFillForegroundColor(IndexedColors.LIGHT_TURQUOISE1.getIndex());
//        headWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);// 字体
//        WriteFont headWriteFont = new WriteFont();
//        headWriteFont.setFontName("宋体");//设置字体名字
//        headWriteFont.setFontHeightInPoints((short)14);//设置字体大小
//        headWriteFont.setBold(true);//字体加粗
//        headWriteCellStyle.setWriteFont(headWriteFont); //在样式用应用设置的字体;
//
//        // 样式
//        headWriteCellStyle.setBorderBottom(BorderStyle.THIN);//设置底边框;
//        headWriteCellStyle.setBottomBorderColor((short) 0);//设置底边框颜色;
//        headWriteCellStyle.setBorderLeft(BorderStyle.THIN);  //设置左边框;
//        headWriteCellStyle.setLeftBorderColor((short) 0);//设置左边框颜色;
//        headWriteCellStyle.setBorderRight(BorderStyle.THIN);//设置右边框;
//        headWriteCellStyle.setRightBorderColor((short) 0);//设置右边框颜色;
//        headWriteCellStyle.setBorderTop(BorderStyle.THIN);//设置顶边框;
//        headWriteCellStyle.setTopBorderColor((short) 0); //设置顶边框颜色;
//
//        headWriteCellStyle.setWrapped(true);  //设置自动换行;headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);//设置水平对齐的样式为居中对齐;headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);  //设置垂直对齐的样式为居中对齐;headWriteCellStyle.setShrinkToFit(true);//设置文本收缩至合适return headWriteCellStyle;}/*** 内容样式* @return*/public static WriteCellStyle getContentStyle(){// 内容的策略WriteCellStyle contentWriteCellStyle = new WriteCellStyle();// 背景绿色// 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定
//        contentWriteCellStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
//        contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);// 设置字体
//        WriteFont contentWriteFont = new WriteFont();
//        contentWriteFont.setFontHeightInPoints((short) 12);//设置字体大小
//        contentWriteFont.setFontName("宋体"); //设置字体名字
//        contentWriteCellStyle.setWriteFont(contentWriteFont);//在样式用应用设置的字体;
//
//        //设置样式;
//        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);//设置底边框;
//        contentWriteCellStyle.setBottomBorderColor((short) 0);//设置底边框颜色;
//        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);  //设置左边框;
//        contentWriteCellStyle.setLeftBorderColor((short) 0);//设置左边框颜色;
//        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);//设置右边框;
//        contentWriteCellStyle.setRightBorderColor((short) 0);//设置右边框颜色;
//        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);//设置顶边框;
//        contentWriteCellStyle.setTopBorderColor((short) 0); ///设置顶边框颜色;contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);// 水平居中contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中contentWriteCellStyle.setWrapped(true); //设置自动换行;//        contentWriteCellStyle.setShrinkToFit(true);//设置文本收缩至合适return contentWriteCellStyle;}}

4、 注解

package com.web.report.annotation;import com.web.report.enums.HeaderLabelTypeEnum;
import com.web.report.enums.HeaderTypeEnum;import java.lang.annotation.*;@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
@Inherited
public @interface TableHeader {/*** 表头显示名称* @return*/String[] label() default {"."};/*** 正数加在对开头，负数加在结尾* @return*/int index() default 1;/*** 表头类型* @return*/HeaderTypeEnum headerType() default HeaderTypeEnum.NORMAL;HeaderLabelTypeEnum labelType() default HeaderLabelTypeEnum.NORMAL;}

 5、枚举

package com.web.report.enums;public enum HeaderLabelTypeEnum {NORMAL, CONFIG
}package com.web.report.enums;public enum HeaderTypeEnum {NORMAL, FLAT
}

6、各种DTO

（1）TableHeaderConfigDTO 

package com.web.report.dto;import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;import java.util.List;@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
public class TableHeaderConfigDTO {private List<String> labels;private String value;
}

 （2）WorkReportConditionDTO 

package com.web.report.dto;import com.web.enums.WorkProcedureResultEnum;
import com.web.report.annotation.TableHeader;
import com.web.report.enums.HeaderLabelTypeEnum;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
public class WorkReportConditionDTO {@TableHeader(labelType = HeaderLabelTypeEnum.CONFIG, index = 1)private TableHeaderConfigDTO ConditionDTO;}

（3）WorkReportDTOT

package com.web.report.dto;import com.web.report.annotation.TableHeader;
import com.web.report.enums.HeaderTypeEnum;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;import java.util.List;@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
public class WorkReportDTOT {@TableHeader(index = 1, headerType= HeaderTypeEnum.FLAT)private List<WorkReportConditionDTO> serialNo;@TableHeader(index = 2, headerType= HeaderTypeEnum.FLAT)private List<WorkReportConditionDTO> sequence;@TableHeader(index = 3, headerType= HeaderTypeEnum.FLAT)private  List<WorkReportConditionDTO> bin;@TableHeader(index = 4, headerType= HeaderTypeEnum.FLAT)private List<WorkReportProcedureDTO> workReportProcedureDTOS;}

4、使用

@Component
@Slf4j
public class WorkReportUtilT {@Autowiredprivate ProjectManageService projectManageService;@Autowiredprivate WorkManageService workManageService;@Autowiredprivate WorkResultService WorkResultService;private static final Map<String, Integer> code2Index = new HashMap<>();private static final Map<String, String> tmMap = new HashMap<>();private static final List<String> conditionsList = new ArrayList<>();private static final List<String> conditionValueList = new ArrayList<>();public void buildReport(WorkManageVO work, String filePath) {// 1.初始话报告内容XinGanXianWorkReportDTOT workReportDTO = null;// 2.判断文件是否存在，如果没有文件那就创建文件并添加表头，表头与数据分开写，防止表头合并File file = new File(filePath);if (!file.exists()) {// 测试结果和头部workReportDTO = buildWorkReportDTO(work,  false);List<List<String>> listHead = buildReportHead(workReportDTO);//ExcelWriter excelWriter = EasyExcel.write(file).build();// 合并单元格CustomMergeStrategy customMergeStrategy = new CustomMergeStrategy(listHead.size());// 只写测试结果头部excelWriter.write(new ArrayList<>(),EasyExcel.writerSheet(0).head(listHead).registerWriteHandler(new CustomCellWriteHandler()).registerWriteHandler(customMergeStrategy).build());//这一步很关键，不然文件会损坏excelWriter.finish();excelWriter.close();}// 3. 报告内容为空的话，构建if(workReportDTO == null){workReportDTO = buildWorkReportDTO(work,  false);}// 4.往文件中追加数据List<Object> oldData = EasyExcel.read(file).sheet(0).headRowNumber(0).doReadSync();List<XinGanXianWorkReportConditionDTO> serialNo = workReportDTO.getSerialNo();serialNo.get(0).getConditionDTO().setValue(String.valueOf(oldData.size() - 21));List<String> procedureDataList = buildReportData(workReportDTO);oldData.add(procedureDataList);// 5.写入文件ExcelWriter excelWriter = EasyExcel.write(file).build();// 合并单元格CustomMergeStrategy customMergeStrategy = new CustomMergeStrategy(procedureDataList.size());// 设置单元格样式HorizontalCellStyleStrategy horizontalCellStyleStrategy =new HorizontalCellStyleStrategy(StyleUtils.getHeadStyle(), StyleUtils.getContentStyle());excelWriter.write(oldData,EasyExcel.writerSheet(0).registerWriteHandler(new CustomColumnWidthHandler()).registerWriteHandler(new CustomCellWriteHandler()).registerWriteHandler(customMergeStrategy).registerWriteHandler(horizontalCellStyleStrategy).build());excelWriter.finish();excelWriter.close();}private List<String> buildReportData(Object o) {List<Field> fields = ReflectionUtil.getAllField(o);List<String> collectDataList = fields.stream().filter(field -> field.isAnnotationPresent(TableHeader.class)).sorted(Comparator.comparingInt(f -> f.getAnnotation(TableHeader.class).index())).flatMap(field -> {TableHeader tableHeader = field.getAnnotation(TableHeader.class);Object value;try {field.setAccessible(true);value = field.get(o);} catch (IllegalAccessException e) {throw new ServiceException("导出报告获取值错误");}if (tableHeader.headerType() == HeaderTypeEnum.FLAT) {return ((List<Object>) value).stream().flatMap(v -> buildReportData(v).stream());}if (tableHeader.labelType() == HeaderLabelTypeEnum.CONFIG) {if (!TableHeaderConfigDTO.class.equals(field.getType())) {throw new ServiceException("导出报告配置错误");}if (value == null) {return Stream.of();}return Stream.of(((TableHeaderConfigDTO) value).getValue());}return Stream.of(Optional.ofNullable(value).map(String::valueOf).orElse(""));}).collect(Collectors.toList());return collectDataList;}private List<List<String>> buildReportHead(Object o) {List<Field> fields = ReflectionUtil.getAllField(o);List<List<String>> collectHeadList = fields.stream().filter(field -> field.isAnnotationPresent(TableHeader.class)).sorted(Comparator.comparingInt(f -> f.getAnnotation(TableHeader.class).index())).flatMap(field -> {TableHeader tableHeader = field.getAnnotation(TableHeader.class);Object value;try {field.setAccessible(true);value = field.get(o);} catch (IllegalAccessException e) {throw new ServiceException("导出报告获取值错误");}if (HeaderTypeEnum.FLAT.equals(tableHeader.headerType())) {if (value == null) {return Stream.of();}if (value instanceof List) {return ((List<Object>) value).stream().flatMap(v -> buildReportHead(v).stream());} else {throw new ServiceException("导出报告配置错误");}}if (HeaderLabelTypeEnum.CONFIG.equals(tableHeader.labelType())) {if (!TableHeaderConfigDTO.class.equals(field.getType())) {throw new ServiceException("导出报告配置错误");}if (value == null) {return Stream.of();}return Stream.of(((TableHeaderConfigDTO) value).getLabels());}return Stream.of(Arrays.asList(tableHeader.label()));}).collect(Collectors.toList());return collectHeadList;}private JSONArray sortCompareRules(JSONArray chooseStandardJsonArray){if(code2Index.size() == 0){List<UniscCommonDictionary> dsaTypes = CommonDictionaryService.searchAll(CommonDictionaryVO.builder().typeLikeRight("device-standard-attribute").build());code2Index.putAll(dsaTypes.stream().collect(Collectors.toMap(CommonDictionary::getCode, CommonDictionary::getIndex, (v1, v2) -> v1)));}// 将JSONArray转换为List后排序List<JSONObject> jsonList = new ArrayList<>();for (int j = 0; j < chooseStandardJsonArray.size(); j++) {jsonList.add(chooseStandardJsonArray.getJSONObject(j));}// 对标规则排序if(jsonList.size() > 0){jsonList.sort((obj1, obj2) -> {Integer r1Index = code2Index.get(obj1.getString("label"));Integer r2Index = code2Index.get(obj2.getString("label"));if (r1Index == null && r2Index == null) {return 0;} else if (r1Index == null) {return 1;} else if (r2Index == null) {return -1;} else {return r1Index.compareTo(r2Index);}});}// 排序后再转回 JSONArrayreturn new JSONArray(Collections.singletonList(jsonList));}private WorkReportDTOT buildWorkReportDTO(WorkManageVO work, boolean offline)  {log.info("开始构建buildWorkReportDTO");WorkManageVO detail = workManageService.getDetail(work.getId(), false);List<WorkProcedureVO> procedures = detail.getProcedures();// 不是离线的延时查询结果if(!offline){try {Thread.sleep(2000); // 延时2秒} catch (InterruptedException error) {error.printStackTrace();}}//一次全部查询List<WorkResultVO> ProcedureResultList = WorkResultService.searchAllVO(WorkResultVO.builder().workId(work.getId()).resultMode(WorkResultModeEnum.COMPARE).build());Map<Long, List<WorkResultVO>>  procedureIdAndResultMap = ProcedureResultList.stream().collect(Collectors.groupingBy(WorkResultVO::getProcedureId));//工序数据List<WorkReportProcedureDTO> procedureDTOList = new ArrayList<>();// 第一列List<WorkReportConditionDTO>  serialNoList = new ArrayList<>();// 第二列List<WorkReportConditionDTO>  sequenceList = new ArrayList<>();// 第三列List<WorkReportConditionDTO>  binList = new ArrayList<>();//if(procedures != null && procedures.size() > 0){log.info("procedures:" + procedures.size());procedures.forEach(e->{//管子名称String pipeName = e.getPipeName();// 工序参数JSONObject params = e.getParams();//构造条件if(conditionsList.isEmpty()){conditionsList.addAll(Arrays.asList("CreateTime","DataFileName","TestFileName",".","TestIndex","TestPipe","TestMode","condition1","condition2","condition3","condition4","condition5","condition6",".","TestResult","MinLimit","MaxLimit","MinResult","MaxResult","Average","STD DEV","Serial#"));conditionValueList.addAll(Arrays.asList(DateUtil.format(work.getCreateTime(), DateUtils.DATE_FORMAT_19),"",work.getName(),".",Optional.ofNullable(String.valueOf(e.getIdx())).orElse(""),Optional.ofNullable(String.valueOf(e.getTsName())).orElse(""),Optional.ofNullable(String.valueOf(e.getTmName())).orElse(""),Optional.ofNullable(params.getString("T1")).orElse(""),Optional.ofNullable(params.getString("T2")).orElse(""),Optional.ofNullable(params.getString("T3")).orElse(""),Optional.ofNullable(params.getString("correctT1")).orElse(""),Optional.ofNullable(params.getString("targetIntensity")).orElse(""),Optional.ofNullable(params.getString("targetVoltage")).orElse(""),".","","","","","","","","S#"));}if(serialNoList.isEmpty()){XinGanXianWorkReportConditionDTO serialNo = new XinGanXianWorkReportConditionDTO();serialNo.setConditionDTO(TableHeaderConfigDTO.builder().labels(conditionsList).value(".").build());serialNoList.add(serialNo);}if(sequenceList.isEmpty()){XinGanXianWorkReportConditionDTO sequence = new XinGanXianWorkReportConditionDTO();sequence.setConditionDTO(TableHeaderConfigDTO.builder().labels(conditionValueList).value(detail.getSequence()).build());sequenceList.add(sequence);}if(binList.isEmpty()){XinGanXianWorkReportConditionDTO bin = new XinGanXianWorkReportConditionDTO();List<String> binconditionList = new ArrayList<>(Collections.nCopies(conditionsList.size() - 1, ""));binconditionList.add("Bin#");bin.setConditionDTO(TableHeaderConfigDTO.builder().labels(binconditionList).value(String.valueOf(detail.getBinResult())).build());binList.add(bin);}// 对标规则JSONArray chooseStandardJsonArray = new JSONArray();if(params.containsKey("chooseStandardList")){chooseStandardJsonArray.addAll((JSONArray)params.get("chooseStandardList"));log.info("工序 "+ e.getId() + " ,有对标规则");// 排序对标规则JSONArray sortedChooseStandardJsonArray = sortCompareRules(chooseStandardJsonArray);// 结果集合初始化Map<String, List<WorkResultVO>> collectMap = new HashMap<>();// 有结果if(e.getResult() != null){//log.info("结果:" + e.getResult());// 查找工序结果//List<WorkResultVO> ProcedureResults = WorkResultService.searchAllVO(WorkResultVO.builder().procedureId(e.getId()).resultMode(WorkResultModeEnum.COMPARE).build());List<WorkResultVO> ProcedureResults = procedureIdAndResultMap.get(e.getId());if(ProcedureResults != null && ProcedureResults.size() > 0){log.info("工序："+ e.getId() + "查到结果:" + ProcedureResults.size());collectMap.putAll(ProcedureResults.stream().collect(Collectors.groupingBy(WorkResultVO::getName)));}else{log.info("工序："+ e.getId()+ "没有查到结果，重新查一次");try {Thread.sleep(2000); // 延时2秒} catch (InterruptedException error) {error.printStackTrace();}List<WorkResultVO> ProcedureResultListAgain = WorkResultService.searchAllVO(WorkResultVO.builder().workId(work.getId()).resultMode(WorkResultModeEnum.COMPARE).build());Map<Long, List<WorkResultVO>>  procedureIdAndResultMapAgain = ProcedureResultListAgain.stream().collect(Collectors.groupingBy(WorkResultVO::getProcedureId));List<WorkResultVO> ProcedureResultsAgain = procedureIdAndResultMapAgain.get(e.getId());if(ProcedureResultsAgain != null && ProcedureResultsAgain.size() > 0){log.info("工序："+ e.getId() + "查到结果:" + ProcedureResultListAgain.size());collectMap.putAll(ProcedureResultsAgain.stream().collect(Collectors.groupingBy(WorkResultVO::getName)));}else{log.info("工序："+ e.getId()+ "没有查到结果");}}}// 遍历每个对标规则ArrayList jsonArrayList = (ArrayList) sortedChooseStandardJsonArray.get(0);log.info("工序id: "+ e.getId() +" ,对标规则数量: " + jsonArrayList.size());jsonArrayList.forEach(ele->{JSONObject jsonObject = (JSONObject)ele;String unit = jsonObject.getString("unit").replace("(","").replace(")","");String label = jsonObject.getString("label");String maxValue = jsonObject.getString("maxValue");String minValue = jsonObject.getString("minValue");Double actual = 0.0;if(collectMap.size() > 0){List<WorkResultVO> WorkResultVOS = collectMap.get(label);List<WorkResultVO> collectList = WorkResultVOS.stream().sorted(Comparator.comparing(WorkResultVO::getId).reversed()).collect(Collectors.toList());actual = collectList.get(0).getActual();}XinGanXianWorkReportProcedureDTO workReportProcedureDTO = new XinGanXianWorkReportProcedureDTO();ArrayList<String> conditionList = new ArrayList<>(Collections.nCopies(conditionsList.size()-8, ""));List<String> list = Arrays.asList(label, minValue, maxValue, "$$min$$", "$$max$$", "$$avg$$", "$$stddev$$", unit);conditionList.addAll(list);workReportProcedureDTO.setProcedureValue(TableHeaderConfigDTO.builder().labels(conditionList).value(String.valueOf(actual)).build());procedureDTOList.add(workReportProcedureDTO);});}else {log.info("工序 "+  e.getId() + " ,没有对标规则");}});}WorkReportDTOT workReportDTO = new WorkReportDTOT();workReportDTO.setWorkReportProcedureDTOS(procedureDTOList);workReportDTO.setSerialNo(serialNoList);workReportDTO.setSequence(sequenceList);workReportDTO.setBin(binList);return workReportDTO;}}