当前位置： 首页 > article >正文

java-poi实现自定义注解excel生成合并单元格数据导出

article 2024/10/19 6:21:30

        在之前的一篇中，简述了如何导出简单的exce表格。但是，有些时候我们需要导出一些带有合并单元格的数据。如以下所示：

依赖导入

 <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-ooxml</artifactId>
      <version>4.0.1</version>
</dependency>
<dependency>
     <groupId>org.apache.poi</groupId>
     <artifactId>poi</artifactId>
     <version>4.0.1</version>
</dependency>

实体定义

标题栏注解

@Target({ElementType.METHOD, ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelTitle {

    /**
     * 标题名称
     * @return 默认空
     */
    String titleName() ;

    /**
     * 标题背景
     * @return 默认空
     */
    IndexedColors titleBack() default IndexedColors.WHITE;

    /**
     * 标题文字大小
     * @return 默认空
     */
    short titleSize() default 14;

    /**
     * 标题文字颜色
     * @return 黑色
     */
    HSSFColor.HSSFColorPredefined titleColor() default HSSFColor.HSSFColorPredefined.BLACK;

    /**
     * 边框格式
     * @return 细
     */
    BorderStyle borderStyle() default BorderStyle.THIN;

    /**
     * 边框颜色
     * @return 默认
     */
    IndexedColors borderColor() default IndexedColors.AUTOMATIC;

    /**
     * 标题文字加粗
     * @return 黑色
     */
    boolean boldFont() default true;

    /**
     * 是否忽略
     * @return 黑色
     */
    boolean ignore() default false;

    /**
     * 排序
     * @return 0
     */
    int order() default 0;
}

数据栏注解

/**
 * @author ZSC
 * @date 2024/7/19 - 9:33
 * @description： excel导出结果配置
 */
@Target({ElementType.METHOD, ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelProperty {

    /**
     * 背景
     * @return 默认空
     */
    IndexedColors textBack() default IndexedColors.WHITE;

    /**
     * 内容类型,查看
     * @link org.apache.poi.ss.usermodel.BuiltinFormats
     * @return 默认TEXT
     */
    String textType() default "TEXT";

    /**
     * 文字大小
     * @return 默认18
     */
    short textSize() default 12;

    /**
     * 数据属于key:value时，可以自定义转换,配置格式为：key1=value;key2=value2;.....
     * @return 默认空
     */
    String textKv() default "";

    /**
     * 文字颜色
     * @return 黑色
     */
    HSSFColor.HSSFColorPredefined textColor() default HSSFColor.HSSFColorPredefined.BLACK;

    /**
     * 水平位置
     * @return 水平居中
     */
    HorizontalAlignment horizontal() default HorizontalAlignment.CENTER;

    /**
     * 垂直位置
     * @return 垂直居中
     */
    VerticalAlignment vertical() default VerticalAlignment.CENTER;

    /**
     * 文字加粗
     * @return 不加粗
     */
    boolean boldFont() default false;
}

合并单元格实体定义

/**
 * @date 2024/7/23 - 16:24
 * @description： excel单元格合并数据定义实体
 */
@Data
public class ExcelMergeVo {

    /**
     * 默认的单元配置
     */
    private ExcelCellStyleDto excelCellStyleDto;


    /**
     * 水平占据的单元各数量，默认1
     */
    private int cellHorizontalSize = 1;

    /**
     * 占据的单元格数量，默认1
     */
    private int cellVerticalSize = 1;

    @ExcelProperty()
    private String value;
}

合并单元格样式实体

@Data
public class ExcelCellStyleDto {
    /**
     * 背景 默认空
     */
    private IndexedColors textBack = IndexedColors.WHITE;

    /**
     * 内容类型,查看 默认TEXT
     * @link org.apache.poi.ss.usermodel.BuiltinFormats
     */
    private String textType =  "TEXT";

    /**
     * 文字大小  默认18
     */
    private short textSize = 12;

    /**
     * 文字颜色 默认 黑色
     */
    private HSSFColor.HSSFColorPredefined textColor = HSSFColor.HSSFColorPredefined.BLACK;

    /**
     * 水平位置 默认 水平居中
     */
    private HorizontalAlignment horizontal = HorizontalAlignment.CENTER;

    /**
     * 垂直位置 垂直居中
     */
    private VerticalAlignment vertical = VerticalAlignment.CENTER;

    /**
     * 文字加粗 默认 不加粗
     */
    private boolean boldFont = true;

    /**
     * 边框
     */
    private BorderStyle borderStyle;
}

每个sheet的数据定义

@Data
public class ExcelSheetComplexVo {

    /**
     * 每一个sheet页得名称
     */
    private String sheetName;

    /**
     * 每个sheet里面得数据
     * 其中Object中得注解必须时包含 @ExcelTitle 和 @ExcelProperties
     */
    private List<ExcelComplexVo> sheetData;

    /**
     * 数据对象得类型
     */
    private Class dataClass;
}


@Data
public class ExcelComplexVo<T> {

    /**
     * 需要合并的单元格信息处理信息，需要排序好
     */
    private List<ExcelMergeVo> orderExcelMergeVos;

    /**
     * 每个正常的数据
     * 其中Object中得注解必须时包含 @ExcelTitle 和 @ExcelProperties
     */
    private List<T> subData;
}

数据生成实现

合并行主要代码

/**
     * 生成每一个sheet数据
     * @param wb 问价实体
     * @param sxssfSheet 每个sheet
     * @param excelSheetComplexVo 每个sheet的数据定义
     * @param dataStartLine 起始行数据
     */
    private void initSheetData(SXSSFWorkbook wb, SXSSFSheet sxssfSheet, ExcelSheetComplexVo excelSheetComplexVo, int dataStartLine) {
        if (CollectionUtils.isEmpty(excelSheetComplexVo.getSheetData())) {
            return;
        }
        try {
            for (ExcelComplexVo excelComplexVo : excelSheetComplexVo.getSheetData()) {

                if (CollectionUtils.isNotEmpty(excelComplexVo.getOrderExcelMergeVos())) {
                    //是否有合并的行数据
                    dataStartLine = createMergeRow(wb, sxssfSheet, excelComplexVo.getOrderExcelMergeVos(), dataStartLine);
                }

                for (Object dataObject : excelComplexVo.getSubData()) {
                    SXSSFRow row = sxssfSheet.createRow(dataStartLine);
                    //获取所有有效字段，并排序
                    Field[] fields = dataObject.getClass().getDeclaredFields();
                    List<Field> fieldList = Arrays.stream(fields)
                            .filter(item -> item.getAnnotation(ExcelTitle.class) != null && !item.getAnnotation(ExcelTitle.class).ignore())
                            .sorted(Comparator.comparingInt(item -> {
                                ExcelTitle excelTitle = item.getAnnotation(ExcelTitle.class);
                                return excelTitle.order();
                            })).collect(Collectors.toList());

                    for (int i = 0; i < fieldList.size(); i++) {
                        Field field = fieldList.get(i);
                        //根据title的值对应的值
                        SXSSFCell cell = row.createCell(i);
                        cell.setCellStyle(initCellStyle(wb, field));
                        field.setAccessible(true);
                        cell.setCellValue(dealValue(field, dataObject));
                    }
                    dataStartLine++;
                }
            }
        } catch (Exception e) {
            LOGGER.error("生成数据异常", e);
        }
    }
 
/**
     * 解析并设置合并的单元格
     * @param wb 问价实体
     * @param sxssfSheet sheet实体
     * @param orderExcelMergeVos 需要设置的合并单元格定义
     * @param dataStartLine 合并单元格的开始设置的起始行
     * @return 后面数据的起始设置行
     */ 
private int createMergeRow(SXSSFWorkbook wb, SXSSFSheet sxssfSheet, List<ExcelMergeVo> orderExcelMergeVos, int dataStartLine) {
        if (CollectionUtils.isEmpty(orderExcelMergeVos)) {
            return dataStartLine;
        }

        int maxCellRowSize = 0;
        int index = 0;

        //创建合并行数据
        SXSSFRow row = sxssfSheet.createRow(dataStartLine);

        for (ExcelMergeVo excelMergeVo : orderExcelMergeVos) {

            if (maxCellRowSize < excelMergeVo.getCellHorizontalSize()) {
                maxCellRowSize = excelMergeVo.getCellHorizontalSize();
            }

            //计算需要合并的单元格信息
            int endRowIndex = dataStartLine + excelMergeVo.getCellVerticalSize();
            int cellIndex = index + excelMergeVo.getCellHorizontalSize();

            CellRangeAddress cellRangeAddress = new CellRangeAddress(dataStartLine, endRowIndex - 1, index, cellIndex - 1);
            sxssfSheet.addMergedRegion(cellRangeAddress);

            CellStyle cellStyle = initCellStyle(wb, excelMergeVo.getExcelCellStyleDto());
            SXSSFCell cell1 = row.createCell(index);
            cell1.setCellStyle(cellStyle);
            cell1.setCellValue(excelMergeVo.getValue());

            index = cellIndex;
        }
        return dataStartLine + maxCellRowSize;
    }

private CellStyle initCellStyle(SXSSFWorkbook wb, ExcelCellStyleDto excelCellStyleDto) {
        // 单元格样式（垂直居中）
        XSSFCellStyle cellStyle = (XSSFCellStyle) wb.createCellStyle();


        //水平居中
        if (excelCellStyleDto.getHorizontal() != null) {
            cellStyle.setAlignment(excelCellStyleDto.getHorizontal());
        }
        //垂直居中
        if (excelCellStyleDto.getVertical() != null) {
            cellStyle.setVerticalAlignment(excelCellStyleDto.getVertical());
        }
        //背景颜色
        if (excelCellStyleDto.getTextBack() != null) {
            cellStyle.setFillForegroundColor(excelCellStyleDto.getTextBack().getIndex());
        }

        //文字的设置字体颜色
        Font font = wb.createFont();
        if (excelCellStyleDto.getTextColor() != null) {
            font.setColor(excelCellStyleDto.getTextColor().getIndex());
        }

        font.setBold(excelCellStyleDto.isBoldFont());
        font.setFontHeightInPoints(excelCellStyleDto.getTextSize() != 0 ? excelCellStyleDto.getTextSize() : 12);
        cellStyle.setFont(font);

        if (excelCellStyleDto.getBorderStyle() != null) {
            cellStyle.setBorderTop(excelCellStyleDto.getBorderStyle());
            cellStyle.setBorderBottom(excelCellStyleDto.getBorderStyle());
            cellStyle.setBorderLeft(excelCellStyleDto.getBorderStyle());
            cellStyle.setBorderRight(excelCellStyleDto.getBorderStyle());
        }

        //设置为文本格式
        cellStyle.setDataFormat(BuiltinFormats.getBuiltinFormat(excelCellStyleDto.getTextType() != null ? excelCellStyleDto.getTextType() : "TEXT"));

        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        return cellStyle;
    }

完整的代码如下：

import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.lang.reflect.Field;
import java.net.URLDecoder;
import java.nio.charset.StandardCharsets;
import java.util.*;
import java.util.stream.Collectors;

public class ExcelComplexCreateUtil {

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

    /**
     * 导出excel数据
     *
     * @param response 亲求返回
     * @param list     每个sheet页的数据，一个elist表示一个sheet页
     * @param fileName 导出的名称
     * @return 结果
     */
    public boolean creatExcel(HttpServletResponse response, List<ExcelSheetComplexVo> list, String fileName) {
        SXSSFWorkbook wb = creatBook(list);
        //导出数据
        try {
            //设置Http响应头告诉浏览器下载这个附件
            response.reset();
            response.setCharacterEncoding("utf-8");
            response.setContentType("application/vnd.ms-excel");
            //名称要从新进行 ISO8859-1 编码否则会文件名称会乱码
            response.setHeader("Content-Disposition", "attachment;Filename=" + encodeFileName(fileName) + ".xlsx");
            OutputStream outputStream = response.getOutputStream();
            ByteArrayOutputStream baos = new ByteArrayOutputStream();
            wb.write(baos);
            outputStream.write(baos.toByteArray());
            baos.flush();
            baos.close();
            outputStream.close();
        } catch (Exception ex) {
            LOGGER.error("导出excel失败", ex);
        }
        return true;
    }

    private SXSSFWorkbook creatBook(List<ExcelSheetComplexVo> list) {
        //创建工作簿
        SXSSFWorkbook wb = new SXSSFWorkbook();
        for (ExcelSheetComplexVo excelSheetComplexVo : list) {
            createSXSSFSheet(excelSheetComplexVo, wb);
        }
        return wb;
    }

    private SXSSFSheet createSXSSFSheet(ExcelSheetComplexVo excelSheetComplexVo, SXSSFWorkbook wb) {
        //创建工作簿
        SXSSFSheet sxssfSheet = wb.createSheet(excelSheetComplexVo.getSheetName());
        //设置默认的行宽
        sxssfSheet.setDefaultColumnWidth(20);
        //设置morning的行高（不能设置太小，可以不设置）
        sxssfSheet.setDefaultRowHeight((short) 300);
        //初始化标题栏
        initTitle(wb, sxssfSheet, excelSheetComplexVo.getDataClass());
        initSheetData(wb, sxssfSheet, excelSheetComplexVo, 1);

        return sxssfSheet;
    }

    /**
     * 生成每一个sheet数据
     * @param wb 问价实体
     * @param sxssfSheet 每个sheet
     * @param excelSheetComplexVo 每个sheet的数据定义
     * @param dataStartLine 起始行数据
     */
    private void initSheetData(SXSSFWorkbook wb, SXSSFSheet sxssfSheet, ExcelSheetComplexVo excelSheetComplexVo, int dataStartLine) {
        if (CollectionUtils.isEmpty(excelSheetComplexVo.getSheetData())) {
            return;
        }
        try {
            for (ExcelComplexVo excelComplexVo : excelSheetComplexVo.getSheetData()) {

                if (CollectionUtils.isNotEmpty(excelComplexVo.getOrderExcelMergeVos())) {
                    //是否有合并的行数据
                    dataStartLine = createMergeRow(wb, sxssfSheet, excelComplexVo.getOrderExcelMergeVos(), dataStartLine);
                }

                for (Object dataObject : excelComplexVo.getSubData()) {
                    SXSSFRow row = sxssfSheet.createRow(dataStartLine);
                    //获取所有有效字段，并排序
                    Field[] fields = dataObject.getClass().getDeclaredFields();
                    List<Field> fieldList = Arrays.stream(fields)
                            .filter(item -> item.getAnnotation(ExcelTitle.class) != null && !item.getAnnotation(ExcelTitle.class).ignore())
                            .sorted(Comparator.comparingInt(item -> {
                                ExcelTitle excelTitle = item.getAnnotation(ExcelTitle.class);
                                return excelTitle.order();
                            })).collect(Collectors.toList());

                    for (int i = 0; i < fieldList.size(); i++) {
                        Field field = fieldList.get(i);
                        //根据title的值对应的值
                        SXSSFCell cell = row.createCell(i);
                        cell.setCellStyle(initCellStyle(wb, field));
                        field.setAccessible(true);
                        cell.setCellValue(dealValue(field, dataObject));
                    }
                    dataStartLine++;
                }
            }
        } catch (Exception e) {
            LOGGER.error("生成数据异常", e);
        }
    }

    /**
     * 解析并设置合并的单元格
     * @param wb 问价实体
     * @param sxssfSheet sheet实体
     * @param orderExcelMergeVos 需要设置的合并单元格定义
     * @param dataStartLine 合并单元格的开始设置的起始行
     * @return 后面数据的起始设置行
     */
    private int createMergeRow(SXSSFWorkbook wb, SXSSFSheet sxssfSheet, List<ExcelMergeVo> orderExcelMergeVos, int dataStartLine) {
        if (CollectionUtils.isEmpty(orderExcelMergeVos)) {
            return dataStartLine;
        }

        int maxCellRowSize = 0;
        int index = 0;

        //创建合并行数据
        SXSSFRow row = sxssfSheet.createRow(dataStartLine);

        for (ExcelMergeVo excelMergeVo : orderExcelMergeVos) {

            if (maxCellRowSize < excelMergeVo.getCellHorizontalSize()) {
                maxCellRowSize = excelMergeVo.getCellHorizontalSize();
            }

            //计算需要合并的单元格信息
            int endRowIndex = dataStartLine + excelMergeVo.getCellVerticalSize();
            int cellIndex = index + excelMergeVo.getCellHorizontalSize();

            CellRangeAddress cellRangeAddress = new CellRangeAddress(dataStartLine, endRowIndex - 1, index, cellIndex - 1);
            sxssfSheet.addMergedRegion(cellRangeAddress);

            CellStyle cellStyle = initCellStyle(wb, excelMergeVo.getExcelCellStyleDto());
            SXSSFCell cell1 = row.createCell(index);
            cell1.setCellStyle(cellStyle);
            cell1.setCellValue(excelMergeVo.getValue());

            index = cellIndex;
        }
        return dataStartLine + maxCellRowSize;
    }

    private void initTitle(SXSSFWorkbook wb, SXSSFSheet sxssfSheet, Class dataClass) {
        //在第0行 设置标题
        SXSSFRow sxssfRow = sxssfSheet.createRow(0);

        Field[] fields = dataClass.getDeclaredFields();

        List<Field> fieldList = Arrays.stream(fields)
                .filter(item -> item.getAnnotation(ExcelTitle.class) != null && !item.getAnnotation(ExcelTitle.class).ignore())
                .sorted(Comparator.comparingInt(item -> {
                    ExcelTitle excelTitle = item.getAnnotation(ExcelTitle.class);
                    return excelTitle.order();
                })).collect(Collectors.toList());

        //设置每列的标题数据
        int i = 0;
        for (Field item : fieldList) {
            //设置列的默认格式为 String
            sxssfSheet.setDefaultColumnStyle(i, initDefaultCellStyle(wb));
            //设置单元格 和单元格的内容格式 - string
            SXSSFCell sxssfCell = sxssfRow.createCell(i, CellType.STRING);

            ExcelTitle excelTitle = item.getAnnotation(ExcelTitle.class);
            sxssfCell.setCellValue(StringUtils.isEmpty(excelTitle.titleName()) ? item.getName() : excelTitle.titleName());
            //设置默认的行格式
            CellStyle titleStyle = initTitleCellStyle(wb, item);
            sxssfCell.setCellStyle(titleStyle);
            i++;
        }
    }

    private CellStyle initCellStyle(SXSSFWorkbook wb, Field titleField) {
        // 单元格样式（垂直居中）
        XSSFCellStyle cellStyle = (XSSFCellStyle) wb.createCellStyle();

        ExcelProperty excelProperty = titleField.getAnnotation(ExcelProperty.class);

        //水平居中
        if (excelProperty != null && excelProperty.horizontal() != null) {
            cellStyle.setAlignment(excelProperty.horizontal());
        }
        //垂直居中
        if (excelProperty != null && excelProperty.vertical() != null) {
            cellStyle.setVerticalAlignment(excelProperty.vertical());
        }
        //背景颜色
        if (excelProperty != null && excelProperty.textBack() != null) {
            cellStyle.setFillForegroundColor(excelProperty.textBack().getIndex());
        }

        //文字的设置字体颜色
        Font font = wb.createFont();
        if (excelProperty != null && excelProperty.textColor() != null) {
            font.setColor(excelProperty.textColor().getIndex());
        }

        font.setBold(excelProperty != null && excelProperty.boldFont());
        font.setFontHeightInPoints(excelProperty != null && excelProperty.textSize() != 0 ? excelProperty.textSize() : 12);
        cellStyle.setFont(font);

        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);

        //设置为文本格式
        cellStyle.setDataFormat(wb.createDataFormat().getFormat(excelProperty != null && excelProperty.textType() != null ? excelProperty.textType() : "TEXT"));

        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        return cellStyle;
    }

    /**
     * 设置合并行的单元格格式
     * @param wb 文件实体
     * @param excelCellStyleDto 样式定义
     * @return 单元格样式
     */
    private CellStyle initCellStyle(SXSSFWorkbook wb, ExcelCellStyleDto excelCellStyleDto) {
        // 单元格样式（垂直居中）
        XSSFCellStyle cellStyle = (XSSFCellStyle) wb.createCellStyle();


        //水平居中
        if (excelCellStyleDto.getHorizontal() != null) {
            cellStyle.setAlignment(excelCellStyleDto.getHorizontal());
        }
        //垂直居中
        if (excelCellStyleDto.getVertical() != null) {
            cellStyle.setVerticalAlignment(excelCellStyleDto.getVertical());
        }
        //背景颜色
        if (excelCellStyleDto.getTextBack() != null) {
            cellStyle.setFillForegroundColor(excelCellStyleDto.getTextBack().getIndex());
        }

        //文字的设置字体颜色
        Font font = wb.createFont();
        if (excelCellStyleDto.getTextColor() != null) {
            font.setColor(excelCellStyleDto.getTextColor().getIndex());
        }

        font.setBold(excelCellStyleDto.isBoldFont());
        font.setFontHeightInPoints(excelCellStyleDto.getTextSize() != 0 ? excelCellStyleDto.getTextSize() : 12);
        cellStyle.setFont(font);

        if (excelCellStyleDto.getBorderStyle() != null) {
            cellStyle.setBorderTop(excelCellStyleDto.getBorderStyle());
            cellStyle.setBorderBottom(excelCellStyleDto.getBorderStyle());
            cellStyle.setBorderLeft(excelCellStyleDto.getBorderStyle());
            cellStyle.setBorderRight(excelCellStyleDto.getBorderStyle());
        }

        //设置为文本格式
        cellStyle.setDataFormat(BuiltinFormats.getBuiltinFormat(excelCellStyleDto.getTextType() != null ? excelCellStyleDto.getTextType() : "TEXT"));

        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        return cellStyle;
    }

    private CellStyle initTitleCellStyle(SXSSFWorkbook wb, Field titleField) {
        // 单元格样式
        XSSFCellStyle cellStyle = (XSSFCellStyle) wb.createCellStyle();
        //水平居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        //垂直居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        ExcelTitle excelTitle = titleField.getAnnotation(ExcelTitle.class);

        //背景颜色
        if (excelTitle != null && excelTitle.titleBack() != null) {
            cellStyle.setFillForegroundColor(excelTitle.titleBack().getIndex());
        } else {
            cellStyle.setFillForegroundColor(IndexedColors.WHITE1.getIndex());
        }

        //文字的设置字体颜色
        Font font = wb.createFont();
        if (excelTitle != null && excelTitle.titleColor() != null) {
            font.setColor(excelTitle.titleColor().getIndex());
        } else {
            font.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        }
        font.setBold(excelTitle != null && excelTitle.boldFont());
        font.setFontHeightInPoints(excelTitle != null && excelTitle.titleSize() != 0 ? excelTitle.titleSize() : 12);
        cellStyle.setFont(font);

        //设置为文本格式
        cellStyle.setDataFormat(BuiltinFormats.getBuiltinFormat("TEXT"));

        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        //边框
        if (excelTitle != null && excelTitle.borderStyle() != null) {
            cellStyle.setBorderTop(excelTitle.borderStyle());
            cellStyle.setBorderBottom(excelTitle.borderStyle());
            cellStyle.setBorderLeft(excelTitle.borderStyle());
            cellStyle.setBorderRight(excelTitle.borderStyle());
        } else {
            cellStyle.setBorderTop(BorderStyle.THIN);
            cellStyle.setBorderBottom(BorderStyle.THIN);
            cellStyle.setBorderLeft(BorderStyle.THIN);
            cellStyle.setBorderRight(BorderStyle.THIN);
        }

        //边框
        if (excelTitle != null && excelTitle.borderColor() != null) {
            cellStyle.setTopBorderColor(excelTitle.borderColor().getIndex());
            cellStyle.setBottomBorderColor(excelTitle.borderColor().getIndex());
            cellStyle.setLeftBorderColor(excelTitle.borderColor().getIndex());
            cellStyle.setRightBorderColor(excelTitle.borderColor().getIndex());
        } else {
            cellStyle.setTopBorderColor(IndexedColors.RED.getIndex());
            cellStyle.setBottomBorderColor(IndexedColors.RED.getIndex());
            cellStyle.setLeftBorderColor(IndexedColors.RED.getIndex());
            cellStyle.setRightBorderColor(IndexedColors.RED.getIndex());
        }

        return cellStyle;
    }

    private CellStyle initDefaultCellStyle(SXSSFWorkbook wb) {
        // 单元格样式（垂直居中）
        XSSFCellStyle cellStyle = (XSSFCellStyle) wb.createCellStyle();
        //水平居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        //垂直居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);

        //文字的设置
        Font font = wb.createFont();
        font.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());   //设置字体颜色
        cellStyle.setFont(font);
        return cellStyle;
    }

    /**
     * 处理数据
     *
     * @param field      字段属性
     * @param dataObject 数据对象
     * @return 具体的属性值
     */
    private String dealValue(Field field, Object dataObject) throws IllegalAccessException {
        ExcelProperty excelProperty = field.getAnnotation(ExcelProperty.class);

        Object value = field.get(dataObject);
        if (value == null) {
            return null;
        }
        if (excelProperty != null && StringUtils.isNotEmpty(excelProperty.textKv())) {
            String[] kvs = excelProperty.textKv().split(";");

            Map<String, String> map = new HashMap<>();
            for (String str : kvs) {
                map.put(str.split("=")[0], str.split("=")[1]);
            }
            return map.get(value.toString());
        }
        return value.toString();
    }

    private String encodeFileName(String fileName) {
        try {
            //fileName = java.net.URLEncoder.encode(fileName, "UTF-8");
            fileName = URLDecoder.decode(fileName, "UTF-8");
            return new String(fileName.getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1);
        } catch (UnsupportedEncodingException e) {
            return "未命名";
        }
    }
}

导出测试

@RestController
@RequestMapping(value = "exceExport")
public class ExcelExportController {

    @GetMapping("testComplexExport")
    public void testComplexExport(HttpServletResponse response){

        String fileName = "测试sheet";
        List<ExcelSheetComplexVo> sheetVoList = new ArrayList<>();

        for(int i = 0; i < 3; i++){
            sheetVoList.add(createComplexSheetData(i));
        }

        new ExcelComplexCreateUtil().creatExcel(response, sheetVoList, fileName);
    }

    private List<TestExcelVo> createTestExcelVo(int size, String sheetName){

        List<TestExcelVo> testExcelVos = new ArrayList<>();
        for(int i = 0; i < size; i++){
            TestExcelVo testExcelVo = new TestExcelVo();
            testExcelVo.setName(sheetName + "-" + i);
            testExcelVo.setAge(i);
            testExcelVo.setSex(i % 2);
            testExcelVo.setDes("哈哈哈哈哈" + i);
            testExcelVos.add(testExcelVo);
        }
        return testExcelVos;
    }


    private ExcelSheetComplexVo createComplexSheetData(int index){
        ExcelSheetComplexVo excelSheetVo = new ExcelSheetComplexVo();
        excelSheetVo.setDataClass(TestExcelVo.class);
        excelSheetVo.setSheetName("sheet" + index);

        List<ExcelComplexVo> excelComplexVos = createTestComplexExcelVo(5, "sheet" + index);
        excelSheetVo.setSheetData(excelComplexVos);
        return excelSheetVo;
    }


    private List<ExcelComplexVo> createTestComplexExcelVo(int size, String sheetName){

        List<ExcelComplexVo> excelComplexVos = new ArrayList<>();
        for(int i = 0; i < size; i++){
            ExcelComplexVo<TestExcelVo> excelComplexVo = new ExcelComplexVo<>();

            ExcelMergeVo excelMergeVo = new ExcelMergeVo();
            excelMergeVo.setExcelCellStyleDto(new ExcelCellStyleDto());
            excelMergeVo.setCellHorizontalSize(2);
            excelMergeVo.setCellVerticalSize(2);
            excelMergeVo.setValue(sheetName + "-合并单元格" + i + "-1");

            ExcelMergeVo excelMergeVo2 = new ExcelMergeVo();
            excelMergeVo2.setExcelCellStyleDto(new ExcelCellStyleDto());
            excelMergeVo2.setCellHorizontalSize(2);
            excelMergeVo2.setCellVerticalSize(2);
            excelMergeVo2.setValue(sheetName + "-合并单元格-" + i + "-2");

            excelComplexVo.setOrderExcelMergeVos(Lists.newArrayList(excelMergeVo, excelMergeVo2));

            List<TestExcelVo> testExcelVos = createTestExcelVo(new Random().nextInt(5) + 5, "sheet" + i);
            excelComplexVo.setSubData(testExcelVos);

            excelComplexVos.add(excelComplexVo);

        }
        return excelComplexVos;
    }

}

测试得到的结果如下：


http://www.kler.cn/news/355482.html

相关文章：

  • VScode运行C语言终端输出中文乱码问题解决方案
  • qt+opengl 实现一个3D立体体
  • [LeetCode 题3] 没有重复字符的最长的子字符串
  • Excel制作工资表
  • Server-Sent Event(SSE) GPT场景实现
  • Python脚本实现发送QQ邮件
  • RabbitMQ 入门(八)SpringAMQP消息转换器
  • jmeter中对于有中文内容的csv文件怎么保存
  • C语言复习第4章 数组
  • webm格式怎么转换成mp4？几个操作简单的视频格式转换方法
  • 华为OD机试真题---勾股数元组
  • css 如何根据子元素给他的父元素设置样式
  • 【Vue】Vue3.0（十一）Vue 3.0 中 computed 计算属性概念、使用及示例
  • 还在滥用模糊查找？这类场景下 MySQL 多值索引性能更加强悍！
  • Leetcode|209.长度最小的子数组 And 59.螺旋矩阵||
  • 【C++】哈希表的封装——同时实现unordered_map和unordered_set
  • 【Vue】Vue3.0 （十二）、watchEffect 和watch的区别及使用
  • 【电商项目】1分布式基础篇
  • ASPICE在国内应用的挑战与改进空间
  • 奥比中光opencv显示可见光图片