【基于POI和easyexcel实现excel文件读写-多列表加下拉选项框】实现概要：公司使用excel整理微服务基线，原来老的excel模板已经不适用，所以需要将微服务从老的excel转移到新的模板中，由于微服务数量较多而且都是重复的复制粘贴的工作，所以决定使用工具完成 。
具体实现：使用POI读取旧exel模板中所需要的字段信息，使用将读取的信息填充到新的excel模板中
1、创建maven项目引入依赖，主要是POI和，其余的为辅助工具包
com.alibabaeasyexcel3.1.1org.projectlomboklombok1.18.24org.slf4jslf4j-log4j12com.alibabafastjson2.0.14org.slf4jslf4j-api2.0.3org.slf4jslf4j-log4j122.0.3org.apache.logging.log4jlog4j-core2.19.0org.apache.poipoi5.2.2org.apache.poipoi-ooxml5.2.2
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 inputParameterList;/*** 输出参数列表*/private List 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 = http://www.kingceram.com/post/new BaseServiceData();List inputParameterList = new ArrayList<>();List 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 = http://www.kingceram.com/post/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