hutool excel工具类初体验

需求：查询不同集合，根据正常异常list进行变色

导出

  @RequestMapping(value = "courtExport/download", method = RequestMethod.GET)
    public void export(TopoQo qo, HttpServletResponse response) {
        String format = DateUtil.format(DateUtil.date(), "yyMMddHHmmss");
        try (
                ExcelWriter writer = ExcelUtil.getWriter();
                ServletOutputStream out = response.getOutputStream();
                HSSFWorkbook hssfworkbook = (HSSFWorkbook) writer.getWorkbook();
        ) {
            //存放异常list
            List<TopoExportVo> exportListExec = new ArrayList<>();
            //存放正常list
            List<TopoExportVo> exportListNor = new ArrayList<>();
            List<TopoExportVo> execList = new ArrayList<>();
            List<TopoExportVo> collect = new ArrayList<>();
            if (StringUtils.isNotEmpty(qo.getSubIds())) {
                qo.setSubstationIds(Stream.of(qo.getSubIds().split(",")).collect(Collectors.toList()));
            }
            //异常拓扑列表集合
            List<TopoExportVo> execTopologyExportVos = vUsingTopoService.selectExecList(qo);

            execTopologyExportVos.forEach(a -> {
                if (com.zz.common.utils.StringUtils.isNotEmpty(a.getCourtName())) {
                    a.setHouseId(a.getCourtName() + a.getHouseId());
                }
            });

            if (CollectionUtil.isNotEmpty(execTopologyExportVos)) {
                //用户三相会出现数据重复，此处去重
                Map<String, List<TopoExportVo>> execMap = execTopologyExportVos.stream().distinct().collect(Collectors.groupingBy(topoExportVo -> topoExportVo.getSubstainid() + topoExportVo.getConsumerno() + topoExportVo.getMeterid()));
                for (Map.Entry<String, List<TopoExportVo>> stringListEntry : execMap.entrySet()) {
                    exportListExec.addAll(stringListEntry.getValue());
                }
                execList = exportListExec.stream().sorted(Comparator.comparing(TopoExportVo::getSubstationName, Comparator.nullsLast(String::compareTo))).collect(Collectors.toList());
            }

            //正常拓扑列表集合
            List<TopoExportVo> normalTopologyExportVos = vUsingTopoService.selectNormalList(qo);
            normalTopologyExportVos.forEach(a -> {
                if (com.zz.common.utils.StringUtils.isNotEmpty(a.getCourtName())) {
                    a.setHouseId(a.getCourtName() + a.getHouseId());
                }
            });
            if (CollectionUtil.isNotEmpty(normalTopologyExportVos)) {
                Map<String, List<TopoExportVo>> normalMap = normalTopologyExportVos.stream().distinct().collect(Collectors.groupingBy(topoExportVo -> topoExportVo.getSubstainid() + topoExportVo.getConsumerno() + topoExportVo.getMeterid()));
                for (Map.Entry<String, List<TopoExportVo>> normalEntry : normalMap.entrySet()) {
                    exportListNor.addAll(normalEntry.getValue());
                }
                collect = exportListNor.stream().sorted(Comparator.comparing(TopoExportVo::getSubstationName, Comparator.nullsLast(String::compareTo))).collect(Collectors.toList());
            }

            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            //解决中文乱码
            String name = "小区箱变汇总信息" + format;
            response.setHeader("Content-Disposition", "attachment;filename*=utf-8'zh_cn'" + URLEncoder.encode(name, "UTF-8") + ".xls");

            //自定义单元格样式,获取头部样式编辑
            CellStyle headCellStyle = writer.getHeadCellStyle();
            //预定义填充样式
            headCellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.LIME.getIndex());
            //拿到palette颜色板
            HSSFPalette palette = hssfworkbook.getCustomPalette();
            //把预填充的HSSFColor.HSSFColorPredefined.LIME.getIndex()替换为期望RGB颜色
            palette.setColorAtIndex(HSSFColor.HSSFColorPredefined.LIME.getIndex(), (byte) 192, (byte) 0, (byte) 0);
            //设置字体
            Font font = writer.createFont();
            font.setColor(IndexedColors.WHITE.getIndex());
            font.setBold(true);
            font.setFontName("微软雅黑");
            headCellStyle.setFont(font);

            //设置对应列间距,不设置默认10,即最终达到9个字符时会自动换行
            writer.setColumnWidth(0, 40);
            writer.setColumnWidth(1, 20);
            writer.setColumnWidth(2, 30);
            writer.setColumnWidth(3, 40);
            writer.setColumnWidth(4, 40);
            writer.setColumnWidth(5, 40);
            //设置所有单元格超过设置列间距自动换行
            StyleSet ss = writer.getStyleSet();
            ss.setWrapText();
            writer.setStyleSet(ss);

            //自定义标题别名
            writer.addHeaderAlias("substainid", "台区编号");
            writer.addHeaderAlias("substationName", "台区名称");
            writer.addHeaderAlias("meterid", "电表表号");
            writer.addHeaderAlias("consumerno", "用户户号");
            writer.addHeaderAlias("userName", "用户名称");
            writer.addHeaderAlias("houseId", "用电地址");
            writer.setOnlyAlias(true);

            // 一次性写出内容，使用默认样式，强制输出标题
            writer.write(collect, true);
            writer.write(execList, false);

            CellStyle cellStyle = writer.createCellStyle();
            cellStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
            cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            //下边框
            cellStyle.setBorderBottom(BorderStyle.THIN);
            //左边框
            cellStyle.setBorderLeft(BorderStyle.THIN);
            //右边框
            cellStyle.setBorderRight(BorderStyle.THIN);
            cellStyle.setBorderTop(BorderStyle.THIN);
            for (int i = 0; i < execList.size(); i++) {
                for (int j = 0; j < writer.getColumnCount(); j++) {
                    writer.setStyle(cellStyle, j, collect.size() + 1 + i);
                }
            }
            writer.flush(out, true);
            writer.close();
            IoUtil.close(out);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }