SpringBoot集成阿里easyexcel（三）自定义EXCEL样式

最新推荐文章于 2024-07-11 11:34:52 发布

Lady伯德

最新推荐文章于 2024-07-11 11:34:52 发布

阅读量1.1k

点赞数

分类专栏： EasyExcel 文章标签： spring boot java 前端

本文链接：https://blog.csdn.net/her_he/article/details/125369115

版权

EasyExcel 专栏收录该内容

5 篇文章 4 订阅

订阅专栏

该博客介绍了如何使用Apache POI库为Excel表格设置全局样式，包括边框、对齐方式、千位分隔符等。同时，通过自定义WriteHandler实现行宽调整，并创建了专门的样式策略来区分表头和内容的样式。最后，在Controller中应用这些样式进行数据导出。

摘要由CSDN通过智能技术生成

Excel表格定义全局样式。
一、使用WriteHandler对行列进行定义

public class ExcelStyleHandler implements WriteHandler {

    @Override
    public void sheet(int i, Sheet sheet) {
    }

    @Override
    public void row(int i, Row row) {
    }

    @Override
    public void cell(int i, Cell cell) {
        //第一行是表头,从第二行开始设置格式
        Workbook workbook = cell.getSheet().getWorkbook();
        CellStyle cellStyle = workbook.createCellStyle();
        //下边框
        cellStyle.setBorderBottom(BorderStyle.THIN);
        //左边框
        cellStyle.setBorderLeft(BorderStyle.THIN);
        //上边框
        cellStyle.setBorderTop(BorderStyle.THIN);
        //右边框
        cellStyle.setBorderRight(BorderStyle.THIN);
        //水平对齐方式
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        //垂直对齐方式
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        if (cell.getRowIndex() > 2) {
              DataFormat dataFormat = workbook.createDataFormat();
              // 设置千位分隔符
              cellStyle.setDataFormat(dataFormat.getFormat("#,##0"));
        }
        cell.getRow().getCell(i).setCellStyle(cellStyle);
    }
}

二、使用AbstractColumnWidthStyleStrategy设置行宽

public class CellWriteHandler extends AbstractColumnWidthStyleStrategy {

    private static final Logger LOGGER = LoggerFactory.getLogger(CellWriteHandler.class);

    private Map<Integer, Map<Integer, Integer>> CACHE = new HashMap<>();

    @Override
    protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
        if (needSetWidth) {
            Map<Integer, Integer> maxColumnWidthMap = CACHE.get(writeSheetHolder.getSheetNo());
            if (maxColumnWidthMap == null) {
                maxColumnWidthMap = new HashMap<>();
                CACHE.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap);
            }

            Integer columnWidth = this.dataLength(cellDataList, cell, isHead);
            if (columnWidth >= 0) {
                if (columnWidth > 255) {
                    columnWidth = 255;
                }

                Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());
                if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
                    maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);
                    writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 140);
                }

            }
        }
    }

    private Integer dataLength(List<CellData> cellDataList, Cell cell, Boolean isHead) {
        if (isHead) {
            return cell.getStringCellValue().getBytes().length;
        } else {
            CellData cellData = cellDataList.get(0);
            CellDataTypeEnum type = cellData.getType();
            if (type == null) {
                return -1;
            } else {
                switch (type) {
                    case STRING:
                        return cellData.getStringValue().getBytes().length;
                    case BOOLEAN:
                        return cellData.getBooleanValue().toString().getBytes().length;
                    case NUMBER:
                        return cellData.getNumberValue().toString().getBytes().length;
                    default:
                        return -1;
                }
            }
        }
    }


}

三、使用poi的WriteCellStyle设置头部和内容样式

public class ResourceStatisticsStyleHelper {

    /**
     * 表头样式
     * @return
     */
    public static WriteCellStyle getHeadWriteCellStyle(){
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.index);
        headWriteCellStyle.setBottomBorderColor(IndexedColors.BLACK.index);
        headWriteCellStyle.setLeftBorderColor(IndexedColors.BLACK.index);
        headWriteCellStyle.setRightBorderColor(IndexedColors.BLACK.index);
        headWriteCellStyle.setTopBorderColor(IndexedColors.BLACK.index);
        return headWriteCellStyle;
    }

    /**
     * 内容样式
     * @return
     */
    public static WriteCellStyle getContentWriteCellStyle(){
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        contentWriteCellStyle.setWrapped(true);
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        return contentWriteCellStyle;
    }

}

controller

 // 样式
        WriteCellStyle headWriteCellStyle =  ResourceStatisticsStyleHelper.getHeadWriteCellStyle();
        WriteCellStyle contentWriteCellStyle = ResourceStatisticsStyleHelper.getContentWriteCellStyle();

        //这个策略是 头是头的样式 内容是内容的样式
        HorizontalCellStyleStrategy horizontalCellStyleStrategy =
                new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
        ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();
        WriteSheet writeSheet = EasyExcel.writerSheet(0, name).head(ProjectExpView.class).registerWriteHandler(horizontalCellStyleStrategy).registerWriteHandler(new CellWriteHandler()).build();
        writeSheet.setAutomaticMergeHead(true);
        excelWriter.write(data, writeSheet);
        excelWriter.finish();