Java ExcelWriter自定义表格构建

Dvvvvvvvvvvvv

已于 2023-02-28 10:31:03 修改

阅读量1.3k

点赞数

文章标签： java ide Powered by 金山文档

于 2023-02-28 10:29:16 首次发布

本文链接：https://blog.csdn.net/Dvvvvvvvvvvvv/article/details/129255189

版权

最终效果

自定义构建表信息（实体类中）

构建表样式

    public static HorizontalCellStyleStrategy getStyleStrategy() {
        // 头的策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 设置对齐
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
        // 背景色
        headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        //设置边框样式
        headWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        headWriteCellStyle.setBorderTop(BorderStyle.THIN);
        headWriteCellStyle.setBorderRight(BorderStyle.THIN);
        headWriteCellStyle.setBorderBottom(BorderStyle.THIN);
        // 字体
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short) 14);
        headWriteCellStyle.setWriteFont(headWriteFont);

        // 内容的策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        // 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定
        // contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
        // contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);

        // 背景绿色
        //contentWriteCellStyle.setFillForegroundColor(IndexedColors.GREEN.getIndex());
        // 字体策略
        WriteFont contentWriteFont = new WriteFont();
        contentWriteFont.setFontHeightInPoints((short) 12);
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        //设置 自动换行
        contentWriteCellStyle.setWrapped(true);
        //设置 垂直居中
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //设置 水平居中
        // contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);


        // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
        return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
    }

构建表头

在相邻格add相同内容时会自动合并单元格

    public static List<List<String>> buildHeaders(String guid,String equipmentCode,String address) {
        String pointCodeHeader = "管道编号:"+guid;
        String equipmentCodeHeader = "设备编号:"+equipmentCode;
        String addressHeader = "地址:"+address;

        List<List<String>> list = new ArrayList<>();
        List<String> time = new ArrayList<>();
        time.add(pointCodeHeader);
        time.add(addressHeader);
        time.add("监测时间");


        List<String> ch4Value = new ArrayList<>();
        ch4Value.add(pointCodeHeader);
        ch4Value.add(addressHeader);
        ch4Value.add("通电点位(V)");


        List<String> temperature = new ArrayList<>();
        temperature.add(pointCodeHeader);
        temperature.add(addressHeader);
        temperature.add("断电点位(V)");

        List<String> humidity = new ArrayList<>();
        humidity.add(equipmentCodeHeader);
        humidity.add(addressHeader);
        humidity.add("交流电压(V)");

        List<String> voltage = new ArrayList<>();
        voltage.add(equipmentCodeHeader);
        voltage.add(addressHeader);
        voltage.add("电池电压(V)");

        list.add(time);
        list.add(ch4Value);
        list.add(temperature);
        list.add(humidity);
        list.add(voltage);
        return list;
    }

自定义表头样式

    public static SheetWriteHandler getSheetWriteHandler() {
        return new SheetWriteHandler() {
            //配置参数
            public int colSplit = 0, rowSplit = 3, leftmostColumn = 0, topRow = 4;
            //表头大概范围
            public String autoFilterRange = "A3:E3";

            @Override
            public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {

            }

            @Override
            public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
                Sheet sheet = writeSheetHolder.getSheet();
                sheet.createFreezePane(colSplit, rowSplit, leftmostColumn, topRow);
                sheet.setAutoFilter(CellRangeAddress.valueOf(autoFilterRange));
            }
        };
    }

4.自定义输出宽度

    public static AbstractColumnWidthStyleStrategy getColumnWidthStyleStrategy() {
        return new AbstractColumnWidthStyleStrategy() {
            @Override
            protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<WriteCellData<?>> list, Cell cell, Head head, Integer integer, Boolean isHead) {
                // 测试为 COLUMN 宽度定制.
                if (isHead && cell.getRowIndex() == 2) {
                    int columnWidth = cell.getStringCellValue().getBytes().length;
                    int cellIndex = cell.getColumnIndex();
                    //第一列宽度为18，其他列为10
                    switch (cellIndex) {
                        case 0:
                            columnWidth = 18;
                            break;
                        default:
                            columnWidth = 10;
                            break;
                    }
                    writeSheetHolder.getSheet().setColumnWidth(cellIndex, columnWidth * 256);
                }
        };
    };
    }

加载表实体数据（逻辑代码中）

加载表头数据

    public void deviceHistoryData(DeviceHistoryQuery query, HttpServletResponse response) throws IOException {
        //数据获取
        List<StrayCurrentHistoryVO> strayCurrentHistoryVos = new ArrayList<>();
        //不同数据分表处理
        List<String> guids = query.getGuids();
        for (String guid : guids) {
            query.setGuids(Collections.singletonList(guid));
            List<StrayCurrentHistoryVO> list = strayCurrentDeviceMapper.getHistoryByGuids(query);
            if (list.size() == 0) {
                List<StrayCurrentDeviceDO> strayCurrentDeviceDOS = strayCurrentDeviceMapper.getByGuid(guid);
                StrayCurrentHistoryVO strayCurrentHistoryVO = new StrayCurrentHistoryVO();
                strayCurrentHistoryVO.setGuid(guid);
                strayCurrentHistoryVO.setReportAddr(strayCurrentDeviceDOS.get(0).getAddress());
                strayCurrentHistoryVO.setReportTime(LocalDateTime.now());
                strayCurrentHistoryVO.setEquipmentCode(strayCurrentDeviceDOS.get(0).getEquipmentCode());
                list.add(strayCurrentHistoryVO);
            }
            strayCurrentHistoryVos.addAll(list);
        }
        if (CollectionUtil.isNotEmpty(strayCurrentHistoryVos)) {
            log.info("[监测数据导出] 查询到监测数据[{}]条", strayCurrentHistoryVos.size());
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");

            String fileName = URLEncoder.encode("杂散电流数据-" + DateUtil.formatChineseDate(new Date(), false, true), "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            response.setHeader("fileName", fileName);
            // ExcelWriter build = EasyExcel.write(response.getOutputStream(), DeviceHistoryDataVO.class).build();
            ExcelWriter build = EasyExcel.write(response.getOutputStream())
                    .registerWriteHandler(DeviceHistoryDataVO.getStyleStrategy())
                    .registerWriteHandler(DeviceHistoryDataVO.getSheetWriteHandler())
                    .registerWriteHandler(DeviceHistoryDataVO.getColumnWidthStyleStrategy())
                    .build();

            Map<String, List<StrayCurrentHistoryVO>> map = strayCurrentHistoryVos.stream().sorted(Comparator.comparing(StrayCurrentHistoryVO::getReportTime).reversed()).collect(Collectors.groupingBy(StrayCurrentHistoryVO::getGuid));
            Set<String> strings = map.keySet();
            int j = 0;
            for (String string : strings) {
                List<StrayCurrentHistoryVO> VOS = map.get(string);
                List<List<String>> headers = DeviceHistoryDataVO.buildHeaders(VOS.get(0).getGuid(), VOS.get(0).getEquipmentCode(), VOS.get(0).getReportAddr());
                WriteSheet writeSheet = EasyExcel.writerSheet(j, VOS.get(0).getGuid()).head(headers).build();
                List<DeviceHistoryDataVO> rows = new ArrayList<>();
                for (StrayCurrentHistoryVO vo : VOS) {
                    DeviceHistoryDataVO row = new DeviceHistoryDataVO();
                    row.setReportTime(vo.getReportTime().toString());
                    row.setBatteryVoltage(String.valueOf(vo.getBatteryVoltage()));
                    row.setEnergizingPotential(String.valueOf(vo.getEnergizingPotential()));
                    row.setInterferenceVoltage(String.valueOf(vo.getInterferenceVoltage()));
                    row.setOffPotential(String.valueOf(vo.getOffPotential()));

                    rows.add(row);
                }
                build.write(rows, writeSheet);
                j++;
            }
            build.close();
            response.flushBuffer();
        } else {
            log.warn("[监测数据导出] 无法根据查询条件[{}]获取到数据", query);
        }

    }