做菠菜网站多少钱,wordpress根目录403,重庆网站建设子沃科技,中铁建设集团有限公司地址前言
公司有个需求本来只涉及到两个下拉选项#xff0c;后面就想能不能实现多个下拉选#xff0c;当然我这里说的多个下拉选是联动的#xff0c;比如省、地市、区县这种。
实现步骤
1、添加EasyExcel的Maven依赖 dependencygroupIdcom.alibaba/group…
前言
公司有个需求本来只涉及到两个下拉选项后面就想能不能实现多个下拉选当然我这里说的多个下拉选是联动的比如省、地市、区县这种。
实现步骤
1、添加EasyExcel的Maven依赖 dependencygroupIdcom.alibaba/groupIdartifactIdeasyexcel/artifactIdversion3.3.2/version/dependency
2、一个具有多级关联的数据项 /*** excel下拉框数据项* author lcy*/
Data
public class SelectItem {public SelectItem(Integer columnIndex) {this.columnIndex columnIndex;}/*** 下拉框所在列的索引从0开始*/private Integer columnIndex;/*** 下拉框的值列表*/private ListDataItem dataItems;/*** 子级对应的下拉框数据*/private SelectItem subSelect;public void addDataItem(String mappingKey,ListString values){if (this.dataItems null){this.dataItems new ArrayList();}this.dataItems.add(new DataItem(mappingKey,values));}public void addDataItem(ListString values){this.addDataItem(_UUID.randomUUID().toString().replaceAll(-,),values);}Datapublic static class DataItem{/*** 关联上级的key*/private String mappingKey;/*** 当前下拉框的值*/private ListString values;/*** 当前下拉框的引用隐藏页单元格地址*/private String hiddenFormulaRef;public DataItem(String mappingKey, ListString values) {Assert.notBlank(mappingKey,mappingKey is not blank);Assert.notEmpty(values,values is not empty);this.mappingKey mappingKey;this.values values;}}3、定义一个SheetWriteHandler这是EasyExcel提供的一个组件允许我们在sheet页生成前后做一些干预动作。 /*** author lcy*/
public class SelectWriteHandler implements SheetWriteHandler , CellWriteHandler {private static final int ROW_SIZE 10000;private final WriteFont redFont;private final ListSelectItem selectItems;private final String HIDDEN_SHEET_NAME hidden_sheet;private final SetInteger selectColumns new HashSet();private boolean isLoadSelectColumns false;private int rowIndex 0;public SelectWriteHandler(ListSelectItem selectItems) {Assert.notEmpty(selectItems, selectItems can not be empty);this.selectItems selectItems;redFont getRedFont();}Overridepublic void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {Workbook workbook writeWorkbookHolder.getWorkbook();Sheet hiddenSheet workbook.getSheet(HIDDEN_SHEET_NAME);if (hiddenSheet ! null){return ;}hiddenSheet workbook.createSheet(HIDDEN_SHEET_NAME);workbook.setSheetHidden(workbook.getSheetIndex(hiddenSheet),true);Sheet sheet writeSheetHolder.getSheet();for (SelectItem selectItem : selectItems) {buildHiddenSheetSelectRef(workbook,sheet,hiddenSheet,selectItem,null);}if (!isLoadSelectColumns){isLoadSelectColumns true;}}private void buildHiddenSheetSelectRef(Workbook workbook,Sheet sheet,Sheet hiddenSheet, SelectItem selectItem,String formulaRef ) {if (!isLoadSelectColumns){selectColumns.add(selectItem.getColumnIndex());}ListSelectItem.DataItem dataItems selectItem.getDataItems();for (SelectItem.DataItem dataItem : dataItems) {setDataAndName(workbook, hiddenSheet, dataItem);}// 单元格地址引用if (formulaRef null || formulaRef.isEmpty()){formulaRef dataItems.get(0).getHiddenFormulaRef();}// 创建检验器DataValidation dataValidation getDataValidation(sheet, selectItem, formulaRef);sheet.addValidationData(dataValidation);SelectItem subSelect selectItem.getSubSelect();if (subSelect ! null){buildHiddenSheetSelectRef(workbook,sheet,hiddenSheet,subSelect,getInDirectFormulaRef(selectItem.getColumnIndex()));}}private DataValidation getDataValidation(Sheet sheet, SelectItem selectItem, String formulaRef) {DataValidationHelper helper sheet.getDataValidationHelper();DataValidationConstraint constraint helper.createFormulaListConstraint(formulaRef);CellRangeAddressList rangeAddressList new CellRangeAddressList(1,ROW_SIZE, selectItem.getColumnIndex(), selectItem.getColumnIndex());DataValidation dataValidation helper.createValidation(constraint, rangeAddressList);dataValidation.setShowErrorBox(true);return dataValidation;}private void setDataAndName(Workbook workbook, Sheet hiddenSheet, SelectItem.DataItem dataItem) {// 构建隐藏数据Row row hiddenSheet.createRow(rowIndex);ListString values dataItem.getValues();for (int i 0; i values.size(); i) {row.createCell(i).setCellValue(values.get(i));}// 创建名称命名器Name name workbook.createName();name.setNameName(dataItem.getMappingKey());name.setRefersToFormula(getFormulaRef(row));dataItem.setHiddenFormulaRef(name.getRefersToFormula());rowIndex;}private String getInDirectFormulaRef(Integer columnIndex){CellReference slectCellReference new CellReference(1, columnIndex);return INDIRECT(joinFormulaRef(slectCellReference, false));}Overridepublic void afterCellDispose(CellWriteHandlerContext context) {if (!context.getHead()){Integer columnIndex context.getColumnIndex();if (selectColumns.contains(columnIndex)){// 设置红色字体context.getFirstCellData().getOrCreateStyle().setWriteFont(redFont);}}CellWriteHandler.super.afterCellDispose(context);}private String getFormulaRef(Row prvRow) {Cell startCell prvRow.getCell(prvRow.getFirstCellNum());Cell endCell prvRow.getCell(prvRow.getLastCellNum() - 1);return HIDDEN_SHEET_NAME ! joinFormulaRef(new CellReference(startCell),true) : joinFormulaRef(new CellReference(endCell),true);}public String joinFormulaRef(CellReference cellReference,boolean isAbsolute){StringBuilder sb new StringBuilder();String[] refs cellReference.getCellRefParts();for (int i refs.length -1 ; i 1; i--) {if (isAbsolute){sb.append($);}sb.append(refs[i]);}return sb.toString();}/*** 返回一个红色字体* return*/private WriteFont getRedFont() {WriteFont redFont new WriteFont();redFont.setColor(IndexedColors.RED.getIndex());return redFont;}
}
4、准备数据 // 准备数据SelectItem selectItem new SelectItem(0);selectItem.addDataItem(List.of(浙江省,河南省));SelectItem subSelectItem new SelectItem(1);subSelectItem.addDataItem(浙江省,List.of(杭州市,宁波市));subSelectItem.addDataItem(河南省,List.of(郑州市,洛阳市,开封市));selectItem.setSubSelect(subSelectItem);SelectItem selectItem3 new SelectItem(2);selectItem3.addDataItem(杭州市,List.of(滨江区,西湖区));selectItem3.addDataItem(宁波市,List.of(宁波市1,宁波市2));selectItem3.addDataItem(郑州市,List.of(金水区,二七区));selectItem3.addDataItem(洛阳市,List.of(洛阳市1,洛阳市2));selectItem3.addDataItem(开封市,List.of(开封市1,开封市2));subSelectItem.setSubSelect(selectItem3);
5、测试
EasyExcel.write(d:\\5555.xlsx).registerWriteHandler(new SelectWriteHandler(List.of(selectItem))).sheet().doWrite(Collections.emptyList());
完整的测试代码
public class SelectExcelTest {public static void main(String[] args) {// 准备数据SelectItem selectItem new SelectItem(0);selectItem.addDataItem(List.of(浙江省,河南省));SelectItem subSelectItem new SelectItem(1);subSelectItem.addDataItem(浙江省,List.of(杭州市,宁波市));subSelectItem.addDataItem(河南省,List.of(郑州市,洛阳市,开封市));selectItem.setSubSelect(subSelectItem);SelectItem selectItem3 new SelectItem(2);selectItem3.addDataItem(杭州市,List.of(滨江区,西湖区));selectItem3.addDataItem(宁波市,List.of(宁波市1,宁波市2));selectItem3.addDataItem(郑州市,List.of(金水区,二七区));selectItem3.addDataItem(洛阳市,List.of(洛阳市1,洛阳市2));selectItem3.addDataItem(开封市,List.of(开封市1,开封市2));subSelectItem.setSubSelect(selectItem3);EasyExcel.write(d:\\5555.xlsx).registerWriteHandler(new SelectWriteHandler(List.of(selectItem))).sheet().doWrite(Collections.emptyList());}}6、结果
