实现概要：公司使用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;
@Data
public static class InputParameter {
/**
* 参数层级
*/
private String inputLevel = " ";
/**
* 元素名称
*/
private String inputElementName = " ";
/**
* 约束
*/
private String inputBind = "";
/**
* 类型
*/
private String inputType = " ";
/**
* 描述
*/
private String inputDescription = " ";
}
@Data
public 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;
}
@Override
public 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();需要注意的是如果模板中存在多个列表，需要加前缀区分
如果新模板中有下拉选项填充，则需要去掉告警的勾选
以上仅作为个人工作学习记录，如有不妥敬请指正。
823
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
