使用EasyExcel合并单元格导出并使导出内容居中。

最新推荐文章于 2024-08-19 10:30:57 发布

m0_53479454

最新推荐文章于 2024-08-19 10:30:57 发布

阅读量7k

点赞数 8

文章标签： java excel 后端

本文链接：https://blog.csdn.net/m0_53479454/article/details/134421416

版权

1、依赖

这里使用的是3.1.1版本的EasyExcel

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.1.1</version>
</dependency>

2、需要导出的实体类对象

在实体类对象中使用注解@ExcelProperty，其中index表示最终导出时在第一行作为表头的顺序，value表示表头的名称。

@Data
@TableName(value = "company_info")
public class CompanyInfo {
    @ExcelProperty(index = 2,value = "企业Id")
    @TableField(value = "company_id")
    private int companyId;

    @ExcelProperty(index = 3,value = "企业名称")
    @TableField(value = "company_name")
    private String companyName;

    @ExcelProperty(index = 0,value = "股东Id")
    @TableField(value = "investor_id")
    private int investorId;

    @ExcelProperty(index = 1,value = "股东名称")
    @TableField(value = "investor_name")
    private String investorName;
}

3、合并单元格的工具类

/**
 * 合并单元格工具类
 *
 */
public class SimpleExcelMergeUtil implements CellWriteHandler {
    private int mergeStartRowIndex;
    private int[] mergeColumns;
    private List<Integer> mergeColumnList;
    public SimpleExcelMergeUtil() {
    }
    public SimpleExcelMergeUtil(int mergeStartRowIndex, int[] mergeColumns) {
        this.mergeStartRowIndex = mergeStartRowIndex;
        this.mergeColumns = mergeColumns;
        mergeColumnList = new ArrayList<>();
        for (int i : mergeColumns) {
            mergeColumnList.add(i);
        }
    }
    /**
     * 创建每个单元格之前执行
     *
     * @param writeSheetHolder
     * @param writeTableHolder
     * @param row
     * @param head
     * @param columnIndex
     * @param relativeRowIndex
     * @param isHead
     */
    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {
    }
    /**
     * 创建每个单元格之后执行
     *
     * @param writeSheetHolder
     * @param writeTableHolder
     * @param cell
     * @param head
     * @param relativeRowIndex
     * @param isHead
     */
    @Override
    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
    }
    /**
     * 每个单元格数据内容渲染之后执行
     *
     * @param writeSheetHolder
     * @param writeTableHolder
     * @param cellData
     * @param cell
     * @param head
     * @param relativeRowIndex
     * @param isHead
     */

    public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
    }
    /**
     * 每个单元格完全创建完之后执行
     *
     * @param writeSheetHolder
     * @param writeTableHolder
     * @param cellDataList
     * @param cell
     * @param head
     * @param relativeRowIndex
     * @param isHead
     */

    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        // 当前行
        int curRowIndex = cell.getRowIndex();
        // 当前列
        int curColIndex = cell.getColumnIndex();
        if (!isHead) {
            if (curRowIndex > mergeStartRowIndex && mergeColumnList.contains(curColIndex)) {
                // 从第二行数据行开始,获取当前行第二列数据
                Object curData = cell.getCellTypeEnum() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();
                // 获取上一行第二列数据
                Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex);
                Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();
                if (curData.equals(preData)) {
                    Sheet sheet = writeSheetHolder.getSheet();
                    List<CellRangeAddress> mergedRegions = sheet.getMergedRegions();
                    boolean isMerged = false;
                    for (int i = 0; i < mergedRegions.size() && !isMerged; i++) {
                        CellRangeAddress cellRangeAddr = mergedRegions.get(i);
                        // 若上一个单元格已经被合并，则先移出原有的合并单元，再重新添加合并单元
                        if (cellRangeAddr.isInRange(curRowIndex - 1, curColIndex)) {
                            sheet.removeMergedRegion(i);
                            cellRangeAddr.setLastRow(curRowIndex);
                            sheet.addMergedRegion(cellRangeAddr);
                            isMerged = true;
                        }
                    }
                    // 若上一个单元格未被合并，则新增合并单元
                    if (!isMerged) {
                        CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex, curColIndex);
                        sheet.addMergedRegion(cellRangeAddress);
                    }
                }
            }
        }
    }
}

4、代码实现

/**
 * 合并单元格导出excel
 */
@PostMapping ("/exportMergeOneLine")
@ApiOperation(value = "导出excel(合并单元格）", notes = "导出excel导出excel(合并单元格）")
public void exportMergeOneLine() {
       QueryWrapper<CompanyInfo> queryWrapper = new QueryWrapper<>();
       queryWrapper.eq("investor_id", 3);
       List<CompanyInfo> companyInfos = companyInfoMapper.selectList(queryWrapper);
       log.info("companyInfos:{}", companyInfos);
       // 从第几行开始合并
       int mergeStartRowIndex = 1;
       // 需要合并哪些列
       int[] mergeColumns = {0,1};
       HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(StyleUtils.getHeadStyle(), StyleUtils.getContentStyle());
       String fileName = "C:\\Users\\a\\Desktop\\(单列合并）用户信息表.xlsx";
       EasyExcel.write(fileName, CompanyInfo.class)
               .registerWriteHandler(new SimpleExcelMergeUtil(mergeStartRowIndex,mergeColumns))
               .registerWriteHandler(horizontalCellStyleStrategy)
               .autoCloseStream(true)
               .sheet("sheet名称")
               .doWrite(companyInfos);
}

5、测试数据

理想得出数据（investor_id :3,investor_name:云赛智联能够进行单元格合并）

效果：

6、为保证导出的内容都在单元格中居中显示需要添加EasyExcel 样式工具类：

/**
 * EasyExcel 样式工具类
 */
public class StyleUtils {
    /**
     * 标题样式
     * @return
     */
    public static WriteCellStyle getHeadStyle(){
        // 头的策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 背景颜色
//        headWriteCellStyle.setFillForegroundColor(IndexedColors.LIGHT_TURQUOISE1.getIndex());
//        headWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);

        // 字体
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontName("宋体");//设置字体名字
        headWriteFont.setFontHeightInPoints((short)14);//设置字体大小
        headWriteFont.setBold(true);//字体加粗
        headWriteCellStyle.setWriteFont(headWriteFont); //在样式用应用设置的字体;

        // 样式
        headWriteCellStyle.setBorderBottom(BorderStyle.THIN);//设置底边框;
        headWriteCellStyle.setBottomBorderColor((short) 0);//设置底边框颜色;
        headWriteCellStyle.setBorderLeft(BorderStyle.THIN);  //设置左边框;
        headWriteCellStyle.setLeftBorderColor((short) 0);//设置左边框颜色;
        headWriteCellStyle.setBorderRight(BorderStyle.THIN);//设置右边框;
        headWriteCellStyle.setRightBorderColor((short) 0);//设置右边框颜色;
        headWriteCellStyle.setBorderTop(BorderStyle.THIN);//设置顶边框;
        headWriteCellStyle.setTopBorderColor((short) 0); //设置顶边框颜色;

        headWriteCellStyle.setWrapped(true);  //设置自动换行;

        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);//设置水平对齐的样式为居中对齐;
        headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);  //设置垂直对齐的样式为居中对齐;
        headWriteCellStyle.setShrinkToFit(true);//设置文本收缩至合适

        return headWriteCellStyle;
    }


    /**
     * 内容样式
     * @return
     */
    public static WriteCellStyle getContentStyle(){
        // 内容的策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();

        // 背景绿色
        // 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定
//        contentWriteCellStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
//        contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);

        // 设置字体
        WriteFont contentWriteFont = new WriteFont();
        contentWriteFont.setFontHeightInPoints((short) 12);//设置字体大小
        contentWriteFont.setFontName("宋体"); //设置字体名字
        contentWriteCellStyle.setWriteFont(contentWriteFont);//在样式用应用设置的字体;

        //设置样式;
        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);//设置底边框;
        contentWriteCellStyle.setBottomBorderColor((short) 0);//设置底边框颜色;
        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);  //设置左边框;
        contentWriteCellStyle.setLeftBorderColor((short) 0);//设置左边框颜色;
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);//设置右边框;
        contentWriteCellStyle.setRightBorderColor((short) 0);//设置右边框颜色;
        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);//设置顶边框;
        contentWriteCellStyle.setTopBorderColor((short) 0); ///设置顶边框颜色;

        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);// 水平居中
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中
        contentWriteCellStyle.setWrapped(true); //设置自动换行;

//        contentWriteCellStyle.setShrinkToFit(true);//设置文本收缩至合适

        return contentWriteCellStyle;
    }
}