< 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 = http://www.kingceram.com/post/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 = http://www.kingceram.com/post/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*/@Datapublic 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 spinnerList;public CustomSpinnerHandler(List 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);}}}