基于POI和easyexcel实现excel文件读写-多列表加下拉选项框

实现概要：公司使用excel整理微服务基线，原来老的excel模板已经不适用，所以需要将微服务从老的excel转移到新的模板中，由于微服务数量较多而且都是重复的复制粘贴的工作，所以决定使用工具完成。

具体实现：使用POI读取旧exel模板中所需要的字段信息，使用easyexcel将读取的信息填充到新的excel模板中

1、创建maven项目引入依赖，主要是POI和easyexcel，其余的为辅助工具包

<dependencies><dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>3.1.1</version></dependency><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><version>1.18.24</version><exclusions><exclusion><groupId>org.slf4j</groupId><artifactId>slf4j-log4j12</artifactId></exclusion></exclusions></dependency><dependency><groupId>com.alibaba</groupId><artifactId>fastjson</artifactId><version>2.0.14</version></dependency><dependency><groupId>org.slf4j</groupId><artifactId>slf4j-api</artifactId><version>2.0.3</version></dependency><dependency><groupId>org.slf4j</groupId><artifactId>slf4j-log4j12</artifactId><version>2.0.3</version></dependency><dependency><groupId>org.apache.logging.log4j</groupId><artifactId>log4j-core</artifactId><version>2.19.0</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>5.2.2</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>5.2.2</version></dependency>
</dependencies>

2、读

旧模板如下，涉及到公司信息已将关键信息清空

实体类，用于接收读取出的字段

public class BaseServiceData {/*** 技术类型*/private String technicalType;/*** 业务类型*/private String businessType;/*** 服务所属系统*/private String serverSystem;/*** 服务英文名名称*/private String serverEnglishName;/*** 服务器中文名字*/private String serverChineseName;/*** 功能描述*/private String functionDescription;/*** 发布至ESB名称*/private String esbName;/*** 输入参数列表*/private List<InputParameter> inputParameterList;/*** 输出参数列表*/private List<OutputParameter> outputParameterList;/*** 输入参数例子*/private String inputParameterExample;/*** 输出参数例子*/private String outputParameterExample;@Datapublic static class InputParameter {/*** 参数层级*/private String inputLevel = " ";/*** 元素名称*/private String inputElementName = " ";/*** 约束*/private String inputBind = "";/*** 类型*/private String inputType = " ";/*** 描述*/private String inputDescription = " ";}@Datapublic static class OutputParameter {/*** 参数层级*/private String outputLevel = " ";/*** 元素名称*/private String outputElementName = " ";/*** 约束*/private String outputBind = "";/*** 类型*/private String outputType = " ";/*** 描述*/private String outputDescription = " ";}
}

读取excel代码如下

/*** 交易excel** @param inputStream 输入流* @return {@link BaseServiceData}*/public static BaseServiceData dealExcel(InputStream inputStream) {try {
//            XSSFWorkbook xssfWorkbook = new XSSFWorkbook(inputStream);HSSFWorkbook hssfWorkbook = new HSSFWorkbook(inputStream);//读第一个sheet页HSSFSheet sheetAt = hssfWorkbook.getSheetAt(0);BaseServiceData baseServiceData = new BaseServiceData();List<BaseServiceData.InputParameter> inputParameterList = new ArrayList<>();List<BaseServiceData.OutputParameter> outputParameterList = new ArrayList<>();//获取有效行数int rowsNum = sheetAt.getPhysicalNumberOfRows();//逐行读取数据for (int i = 0; i < rowsNum; i++) {log.info("读取第{}行", i);HSSFRow row = sheetAt.getRow(i);if (row == null) {continue;}HSSFCell firstCell = row.getCell(0);if (firstCell == null) {log.error("模板存在错误，第{}行第一列不能为空,跳过当前行", i);continue;}String value;//判断每一行的第一个cell元素类型switch (firstCell.getCellType()) {case STRING:if ("技术类别".equals(firstCell.getStringCellValue())) {HSSFCell cell = row.getCell(1);String cellValue = cell.getStringCellValue();cellValue = CONVERT_MAP.getOrDefault(cellValue, cellValue);baseServiceData.setTechnicalType(cellValue);baseServiceData.setBusinessType(cellValue);} else if ("服务英文名称".equals(firstCell.getStringCellValue())) {HSSFCell cell = row.getCell(1);baseServiceData.setServerEnglishName(cell.getStringCellValue());baseServiceData.setEsbName(cell.getStringCellValue());} else if ("服务中文名称".equals(firstCell.getStringCellValue())) {HSSFCell cell = row.getCell(1);baseServiceData.setServerChineseName(cell.getStringCellValue());} else if ("功能描述".equals(firstCell.getStringCellValue())) {HSSFCell cell = row.getCell(1);baseServiceData.setFunctionDescription(cell.getStringCellValue());} else if ("输入".equals(firstCell.getStringCellValue())) {for (int j = i + 1; j < rowsNum; j++) {HSSFRow sheetAtRow = sheetAt.getRow(j);if (sheetAtRow == null) {continue;}HSSFCell cell = sheetAtRow.getCell(0);if (cell.getCellType().equals(CellType.STRING) && "输出".equals(cell.getStringCellValue())) {break;}// 输入对象BaseServiceData.InputParameter inputParameter = new BaseServiceData.InputParameter();HSSFCell levelCell = sheetAtRow.getCell(1);if (levelCell == null) {inputParameter.setInputLevel("");} else {inputParameter.setInputLevel(sheetAtRow.getCell(1).getStringCellValue());}if (sheetAtRow.getCell(2) == null) {inputParameter.setInputElementName("");} else {inputParameter.setInputElementName(sheetAtRow.getCell(2).getStringCellValue());}HSSFCell cell4 = sheetAtRow.getCell(4);if (cell4 == null) {inputParameter.setInputType("");} else {String cellValue = cell4.getStringCellValue();cellValue = CONVERT_MAP.getOrDefault(cellValue, cellValue);inputParameter.setInputType(cellValue);}if (sheetAtRow.getCell(6) == null) {inputParameter.setInputDescription("");} else {inputParameter.setInputDescription(sheetAtRow.getCell(6).getStringCellValue());}inputParameterList.add(inputParameter);}baseServiceData.setInputParameterList(inputParameterList);} else if ("输出".equals(firstCell.getStringCellValue())) {for (int j = i + 1; j < rowsNum; j++) {HSSFRow sheetAtRow = sheetAt.getRow(j);if (sheetAtRow == null) {continue;}HSSFCell cell = sheetAtRow.getCell(0);if (cell.getCellType().equals(CellType.STRING) && "输入示例".equals(cell.getStringCellValue())) {break;}// 输出对象BaseServiceData.OutputParameter outputParameter = new BaseServiceData.OutputParameter();HSSFCell levelCell = sheetAtRow.getCell(1);if (levelCell == null) {outputParameter.setOutputLevel("");} else {outputParameter.setOutputLevel(sheetAtRow.getCell(1).getStringCellValue());}HSSFCell cell2 = sheetAtRow.getCell(2);if (cell2 == null) {outputParameter.setOutputElementName("");} else {outputParameter.setOutputElementName(sheetAtRow.getCell(2).getStringCellValue());}if (sheetAtRow.getCell(4) == null) {outputParameter.setOutputType("");} else {String cellValue = sheetAtRow.getCell(4).getStringCellValue();cellValue = CONVERT_MAP.getOrDefault(cellValue, cellValue);outputParameter.setOutputType(cellValue);}if (sheetAtRow.getCell(6) == null) {outputParameter.setOutputDescription("");} else {outputParameter.setOutputDescription(sheetAtRow.getCell(6).getStringCellValue());}outputParameterList.add(outputParameter);}baseServiceData.setOutputParameterList(outputParameterList);} else if ("输入示例".equals(firstCell.getStringCellValue())) {HSSFRow sheetAtRow = sheetAt.getRow(i + 1);HSSFCell sheetAtRowCell = sheetAtRow.getCell(0);baseServiceData.setInputParameterExample(sheetAtRowCell.getStringCellValue().trim());} else if ("输出示例".equals(firstCell.getStringCellValue())) {HSSFRow sheetAtRow = sheetAt.getRow(i + 1);HSSFCell sheetAtRowCell = sheetAtRow.getCell(0);baseServiceData.setOutputParameterExample(sheetAtRowCell.getStringCellValue().trim());}break;case NUMERIC:case BOOLEAN:case BLANK:default:break;}}return baseServiceData;} catch (IOException e) {log.error(e.getMessage());}return new BaseServiceData();}

3、写

新模板如下

下拉框模型对象

package com.cong.pojo;import lombok.Builder;
import lombok.Data;/*** 下拉框模型** @Author zhangyc* @Date 2022/10/17 11:10* @PackageName:com.cong.handles* @ClassName: SpinnerModel* @Description: TODO* @Version 1.0*/
@Data
public class SpinnerModel {private Integer startColumnIndex;private Integer endColumnIndex;private Integer startRowIndex;private Integer endRowIndex;/*** 下拉框选项*/private String[] spinnerData;
}

下拉框处理器

package com.cong.handles;import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import com.cong.pojo.SpinnerModel;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddressList;import java.util.List;/*** @Author zhangyc* @Date 2022/10/17 11:46* @PackageName:com.cong.handles* @ClassName: CustomSpinnerHandler* @Description: TODO* @Version 1.0*/
public class CustomSpinnerHandler implements SheetWriteHandler {/*** 下拉框信息列表*/private List<SpinnerModel> spinnerList;public CustomSpinnerHandler(List<SpinnerModel> spinnerList) {this.spinnerList = spinnerList;}@Overridepublic void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {Sheet sheet = writeSheetHolder.getSheet();DataValidationHelper dataValidationHelper = sheet.getDataValidationHelper();for (SpinnerModel spinnerModel : spinnerList) {//校验开始列索引大于结束列索引，或者开始行索引大于结束行索引if (spinnerModel.getStartColumnIndex() > spinnerModel.getEndColumnIndex() || spinnerModel.getStartRowIndex() > spinnerModel.getEndRowIndex()) {continue;}DataValidationConstraint constraint = dataValidationHelper.createExplicitListConstraint(spinnerModel.getSpinnerData());//设置单元格范围CellRangeAddressList addressList = new CellRangeAddressList(spinnerModel.getStartRowIndex(), spinnerModel.getEndRowIndex(), spinnerModel.getStartColumnIndex(), spinnerModel.getEndColumnIndex());DataValidation validation = dataValidationHelper.createValidation(constraint, addressList);sheet.addValidationData(validation);}}
}

填充模板代码

 //设置下拉框模型String[] spinnerData1 = new String[]{"string", "number", "integer", "object", "array", "boolean"};String[] spinnerData2 = new String[]{"string", "number", "integer", "object", "array", "boolean"};SpinnerModel spinnerModel1 = new SpinnerModel();spinnerModel1.setSpinnerData(spinnerData1);spinnerModel1.setStartRowIndex(35);spinnerModel1.setEndRowIndex(35 + inputParameterList.size()-1);spinnerModel1.setStartColumnIndex(3);spinnerModel1.setEndColumnIndex(3);SpinnerModel spinnerModel2 = new SpinnerModel();spinnerModel2.setSpinnerData(spinnerData2);spinnerModel2.setStartRowIndex(35 + inputParameterList.size() + 1);spinnerModel2.setEndRowIndex(35 + inputParameterList.size() + 1 + outputParameterList.size()-1);spinnerModel2.setStartColumnIndex(3);spinnerModel2.setEndColumnIndex(3);List<SpinnerModel> spinnerList = new ArrayList<>();spinnerList.add(spinnerModel1);spinnerList.add(spinnerModel2);//写入模板excelWriter = EasyExcel.write(newPath).excelType(ExcelTypeEnum.XLS).withTemplate(templatePath).registerWriteHandler(new CustomSpinnerHandler(spinnerList)).build();WriteSheet writeSheet = EasyExcel.writerSheet().build();//每写一行自动添加一行新空行FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();//写列表excelWriter.fill(new FillWrapper("a", inputParameterList), fillConfig, writeSheet);excelWriter.fill(new FillWrapper("b", outputParameterList), fillConfig, writeSheet);//写其他信息excelWriter.fill(baseServiceData, fillConfig, writeSheet);excelWriter.close();

需要注意的是如果模板中存在多个列表，需要加前缀区分

如果新模板中有下拉选项填充，则需要去掉告警的勾选