2024年6月28日
easyexcel单元格下拉选项，填充点背景色
示例程序参考官网，自定义过滤器实现，生成如图excel

不合适的请评论指正
点个赞再走👍
点个赞再走👍
点个赞再走👍

public class testExcel {

    /**
     * 下拉选项
     */
    static String[] typeList = new String[]{
            "水果店",
            "五金店",
            "便利店"
    };

    /**
     * 第一页 填充数据
     * @return
     */
    static List<TestExcelData> data() {
        List<TestExcelData> list = ListUtils.newArrayList();
        {
            TestExcelData data = new TestExcelData();
            data.setDate(new Date());
            data.setPlaceName("西瓜店");
            list.add(data);
        }
        {
            TestExcelData data = new TestExcelData();
            data.setDate(new Date());
            data.setPlaceName("公牛店").setPlaceType(typeList[2]);
            list.add(data);
        }
        {
            TestExcelData data = new TestExcelData();
            data.setDate(new Date());
            data.setPlaceName("华联中百");
            list.add(data);
        }
        return list;
    }

    /**
     * 过滤器
     */
    static class CustomSheetWriteHandler implements SheetWriteHandler {
        private int lastRow;
        public CustomSheetWriteHandler(int lastRow) {
            this.lastRow = lastRow;
        }

        @Override
        public void afterSheetCreate(SheetWriteHandlerContext context) {
            log.info("第{}个Sheet写入成功。", context.getWriteSheetHolder().getSheetNo());

            // 区间设置 第一列第一行和第二行的数据。由于第一行是头，所以第一、二行的数据实际上是第二三行
            CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(1, lastRow, 2, 2);
            DataValidationHelper helper = context.getWriteSheetHolder().getSheet().getDataValidationHelper();
            DataValidationConstraint constraint = helper.createExplicitListConstraint(typeList);
            DataValidation dataValidation = helper.createValidation(constraint, cellRangeAddressList);
            context.getWriteSheetHolder().getSheet().addValidationData(dataValidation);
        }
    }

    /**
     * 单元格过滤器
     * 控制台输出，afterCellDispose在SheetWriteHandler.afterSheetCreate之后执行
     */
    static class CustomCellWriteHandler implements CellWriteHandler {
        @Override
        public void afterCellDispose(CellWriteHandlerContext context) {
            Cell cell = context.getCell();
            log.info("第{}行，第{}列写入完成。", cell.getRowIndex(), cell.getColumnIndex());
        }
    }



    public static void main(String[] args) throws IOException {
        File file = new File("./testExcel.xlsx");

        if (!file.exists()) file.createNewFile();
        
        try(OutputStream out = new FileOutputStream(file, false);) {

            ExcelWriter build = EasyExcel.write(out).build();

            /**
             * 第一页
             */
            WriteSheet sheet0 = EasyExcel.writerSheet(0, "模板")
                    .registerWriteHandler(new CustomCellWriteHandler())//指定过滤器
                    .registerWriteHandler(new CustomSheetWriteHandler(5))//指定过滤器 自定义lastRow
                    .head(TestExcelData.class)
                    .build();
            build.write(data(), sheet0);

            /**
             * 第二页
             */
            List<List<String>> head = new ArrayList<>();
            List<String> h0 = new ArrayList();
            h0.add("可选类型");
            head.add(h0);

            //表头style
            WriteCellStyle headStyle = new WriteCellStyle();
            headStyle.setFillForegroundColor((short) 44);//表头背景填充色
            WriteFont font = new WriteFont();
            font.setFontHeightInPoints((short) 12);//表头字号
            headStyle.setWriteFont(font);
            WriteCellStyle contentStyle = new WriteCellStyle();//列样式
            HorizontalCellStyleStrategy strategy = new HorizontalCellStyleStrategy(headStyle, contentStyle);

            //数据
            List<List<Object>> data = new ArrayList<>();
            List<String> nameList = Arrays.asList(typeList);
            nameList.forEach(name -> data.add(Arrays.asList(new String[]{name})));

            WriteSheet sheet1 = EasyExcel.writerSheet(1, "场所类型")
                    .registerWriteHandler(strategy)
                    .registerWriteHandler(new SimpleColumnWidthStyleStrategy(30))
                    .registerWriteHandler(new SimpleRowHeightStyleStrategy((short)22,(short)20))
                    .head(head)
                    .build();
            
            build.write(data, sheet1);
            build.finish();
            out.flush();
            out.close();
            
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


}