整合工具【easyexcel根据数据相同导出动态合并单元格实现自动换行】

ekkcole

已于 2024-02-22 13:38:54 修改

阅读量3k

点赞数 6

分类专栏： easyexcel java 文章标签： java easyexcel excel

于 2023-04-04 14:22:10 首次发布

本文链接：https://blog.csdn.net/weixin_45559862/article/details/129951671

版权

java 同时被 2 个专栏收录

101 篇文章 4 订阅

订阅专栏

easyexcel

2 篇文章 0 订阅

订阅专栏

导出接口不可以有返回值，否则会报错
在这里插入图片描述

1、pom依赖

<!-- 阿里开源easyexcel-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.0-beta2</version>
        </dependency>
<!-- lombok省去写get、set步骤-->
		<dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>

2、实体类


package org.springblade.modules.api.vo;

import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import io.swagger.annotations.ApiModel;
import lombok.Data;
import lombok.EqualsAndHashCode;

/**
 * 主体地块信息视图实体类
 * @since 2023-03-22
 */
@Data
@ColumnWidth(16)
@HeadRowHeight(20)
@ApiModel(value = "PlotBaseExcelVO对象", description = "地块主体导出信息")
@EqualsAndHashCode
public class PlotBaseExcelVO {
	private static final long serialVersionUID = 1L;
	
	/**
	 * 主体名称
	 */
	@ExcelProperty(value = {"地块信息","主体名称"})
	@ColumnWidth(value = 22)
	private String name;
	/**
	 * 所属区域
	 */
	@ExcelProperty(value = {"地块信息","所属地区"})
	@ColumnWidth(value = 35)
	private String regionName;
	
	/**
	 * 生产经营地址
	 */
	@ExcelProperty(value = {"地块信息","生产经营地址"})
	@ColumnWidth(value = 25)
	private String address;

	/**
	 * 地块编码
	 */
	@ExcelProperty(value = {"地块信息","地块编码"})
	private String plotCode;
	
	/**
	 * 地块类型
	 */
	@ExcelProperty(value = {"地块信息","地块类型"})
	private String typeName;
	/**
	 * 地块名称
	 */
	@ExcelProperty(value = {"地块信息","地块名称"})
	private String plotName;
	/**
	 * 地块面积
	 */
	@ExcelProperty(value = {"地块信息","地块面积"})
	private String area;
}

@ColumnWidth(15)注解是列宽
@ExcelIgnore注解是不参与导出的字段
@ExcelProperty(value ={“地块信息”,“地块名称”}, index = 1)主标题副标题等类推，index是展示的顺序
如果使用了自动换行策略，那么类上面就不需要@ContentRowHeight(15) //内容行高的注解，加了该注解不会自动换行

3、工具类

package org.springblade.modules.api.utils;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springblade.modules.api.vo.PlotBaseExcelVO;
import org.springblade.modules.api.service.impl.ExcelFillCellMergePrevColUtils;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.List;

public class ExcelMergeUtil implements CellWriteHandler {
    private int[] mergeColumnIndex;
    private int mergeRowIndex;

    public ExcelMergeUtil() {
    }

    public ExcelMergeUtil(int mergeRowIndex, int[] mergeColumnIndex) {
        this.mergeRowIndex = mergeRowIndex;
        this.mergeColumnIndex = mergeColumnIndex;
    }

    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {

    }

    @Override
    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {

    }

    @Override
    public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {

    }

    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {

        //当前行
        int curRowIndex = cell.getRowIndex();
        //当前列
        int curColIndex = cell.getColumnIndex();

        if (curRowIndex > mergeRowIndex) {
            for (int i = 0; i < mergeColumnIndex.length; i++) {
                if (curColIndex == mergeColumnIndex[i]) {
                    mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex);
                    break;
                }
            }
        }
    }


    /**
     * 当前单元格向上合并
     *
     * @param writeSheetHolder
     * @param cell             当前单元格
     * @param curRowIndex      当前行
     * @param curColIndex      当前列
     */
    private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int 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();
        // 将当前单元格数据与上一个单元格数据比较
        Boolean dataBool = preData.equals(curData);
        //此处需要注意：因为我是按照主体名称确定是否需要合并的，所以获取每一行第二列数据和上一行第一列数据进行比较，如果相等合并，getCell里面的值，是主体名称所在列的下标，不能大于需要合并列数组的第一个下标
        Boolean bool = cell.getRow().getCell(0).getStringCellValue().equals(cell.getSheet().getRow(curRowIndex - 1).getCell(0).getStringCellValue());
        if (dataBool && bool) {
            Sheet sheet = writeSheetHolder.getSheet();
            List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();
            boolean isMerged = false;
            for (int i = 0; i < mergeRegions.size() && !isMerged; i++) {
                CellRangeAddress cellRangeAddr = mergeRegions.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);
            }
        }
    }

    /**
     * 头部样式
     *
     * @return
     */
    public static WriteCellStyle getHeadWriteCellStyle() {
        // 这里需要设置不关闭流
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        //设置背景颜色
        headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        //设置头字体
        WriteFont headWriteFont = new WriteFont();
        //字体大小
        headWriteFont.setFontHeightInPoints((short) 13);
        //是否加粗
        headWriteFont.setBold(true);
        headWriteCellStyle.setWriteFont(headWriteFont);
        //设置头居中
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        return headWriteCellStyle;
    }

    /**
     * 内容样式
     *
     * @return
     */
    public static WriteCellStyle getContentWriteCellStyle() {
        //内容策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        //设置 水平居中
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        //自动换行
        contentWriteCellStyle.setWrapped(true);
        //垂直居中
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //设置左边框
        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        //设置右边框
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
        //设置上边框
        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
        //设置下边框
        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
        return contentWriteCellStyle;
    }

    /**
     * 合并单元格导出excel工具
     *
     * @param response         响应头
     * @param fileName         文件名称
     * @param lsit             需要导出的数据
     * @param data             对应的excel导出类
     * @param mergeColumeIndex 需要合并的下标
     * @param mergeRowIndex    从第几行开始合并
     * @throws IOException
     */
    public static void exportExcel(HttpServletResponse response, String fileName, List lsit, Class data, int[] mergeColumeIndex, int mergeRowIndex) throws IOException {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        String fileNamePath = URLEncoder.encode(fileName + System.currentTimeMillis(), "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileNamePath + ".xlsx");
         // 调用合并单元格工具类，此工具类是根据工程名称相同则合并后面数据
        ExcelMergeUtil excelFillCellMergeStrategy = null;
        //是否需要合并
        if (Func.isNotEmpty(mergeColumeIndex) && mergeRowIndex != -1) {
            excelFillCellMergeStrategy = new ExcelMergeUtil(mergeRowIndex, mergeColumeIndex);
        }

        //头部样式
        WriteCellStyle headWriteCellStyle = ExcelMergeUtil.getHeadWriteCellStyle();
        //内容样式
        WriteCellStyle contentWriteCellStyle = ExcelMergeUtil.getContentWriteCellStyle();

        HorizontalCellStyleStrategy horizontalCellStyleStrategy =
                new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
		//合并列，可以实现最后一行合计的效果
        //ExcelFillCellMergePrevColUtils column = new ExcelFillCellMergePrevColUtils();
        //column.add(lsit.size() + 1, 0, 1);
        EasyExcel.write(response.getOutputStream(), data)
                .registerWriteHandler(horizontalCellStyleStrategy)
                .registerWriteHandler(excelFillCellMergeStrategy)
                //.registerWriteHandler(column)
                //sheet显示的名字
                .autoCloseStream(Boolean.TRUE).sheet(fileName)
                .doWrite(lsit);
    }
}

4、列合并工具类

package org.springblade.modules.api.service.impl;

import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

//列合并工具类
public class ExcelFillCellMergePrevColUtils implements CellWriteHandler {
    private static final String KEY = "%s-%s";
    //所有的合并信息都存在了这个map里面
    Map<String, Integer> mergeInfo = new HashMap<>();

    public ExcelFillCellMergePrevColUtils() {
    }

    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) {

    }

    @Override
    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer integer, Boolean aBoolean) {

    }

    @Override
    public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {

    }

    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {
        //当前行
        int curRowIndex = cell.getRowIndex();
        //当前列
        int curColIndex = cell.getColumnIndex();

        Integer num = mergeInfo.get(String.format(KEY, curRowIndex, curColIndex));
        if (null != num) {
            // 合并最后一行 ,列
            mergeWithPrevCol(writeSheetHolder, cell, curRowIndex, curColIndex, num);
        }
    }

    public void mergeWithPrevCol(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex, int num) {
        Sheet sheet = writeSheetHolder.getSheet();
        CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex, curRowIndex, curColIndex, curColIndex + num);
        sheet.addMergedRegion(cellRangeAddress);
    }

    //num从第几列开始增加多少列,(6,2,7)代表的意思就是第6行的第2列至第2+7也就是9列开始合并
    public void add(int curRowIndex, int curColIndex, int num) {
        mergeInfo.put(String.format(KEY, curRowIndex, curColIndex), num);
    }

}

5、controller

/*
 *      Copyright (c) 2018-2028, Chill Zhuang All rights reserved.
 *
 *  Redistribution and use in source and binary forms, with or without
 *  modification, are permitted provided that the following conditions are met:
 *
 *  Redistributions of source code must retain the above copyright notice,
 *  this list of conditions and the following disclaimer.
 *  Redistributions in binary form must reproduce the above copyright
 *  notice, this list of conditions and the following disclaimer in the
 *  documentation and/or other materials provided with the distribution.
 *  Neither the name of the dreamlu.net developer nor the names of its
 *  contributors may be used to endorse or promote products derived from
 *  this software without specific prior written permission.
 *  Author: Chill 庄骞 (smallchill@163.com)
 */
package org.springblade.modules.api.controller;

import com.google.protobuf.ServiceException;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import io.swagger.annotations.ApiParam;
import com.github.xiaoymin.knife4j.annotations.ApiOperationSupport;
import lombok.AllArgsConstructor;

import javax.servlet.http.HttpServletResponse;
import javax.validation.Valid;

import org.springblade.common.utils.CommonUtil;
import org.springblade.core.api.crypto.annotation.crypto.ApiCrypto;
import org.springblade.core.api.crypto.annotation.decrypt.ApiDecryptAes;
import org.springblade.core.api.crypto.annotation.encrypt.ApiEncryptAes;
import org.springblade.core.mp.support.Condition;
import org.springblade.core.mp.support.Query;
import org.springblade.core.secure.utils.AuthUtil;
import org.springblade.core.tool.api.R;
import org.springblade.core.tool.utils.Func;
import org.springblade.modules.api.dto.BaseCompanyNameDTO;
import org.springblade.modules.api.entity.Plot;
import org.springblade.modules.api.service.IPlotService;
import org.springblade.modules.api.vo.PlotBaseVO;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.*;
import com.baomidou.mybatisplus.core.metadata.IPage;
import org.springblade.core.boot.ctrl.BladeController;

import java.util.List;


/**控制器
 * @since 2023-03-21
 */
@RestController
@AllArgsConstructor
@RequestMapping("/plot")
@Api(value = "地块信息", tags = "地块信息接口")
public class PlotController extends BladeController {

    private final IPlotService plotService;

    @GetMapping("exportPlotManage")
    @ApiOperationSupport(order = 4)
    @ApiOperation(value = "导出地块信息管理excel", notes = "")
    public void exportPlotManage(PlotBaseVO plot,  Query query,HttpServletResponse response) {
        List<PlotBaseExcelVO> plotBaseVO =plotService.excelPlotManage(plot, query,response);
         try {
            //需要合并的列
            int[] mergeColumeIndex = {0, 1, 2};
            //从第二行后开始合并
            int mergeRowIndex = 1;
            //调用合并单元格工具，当mergeColumeIndex 为空，mergeRowIndex为-1时候，代表不合并
            ExcelMergeUtil.exportExcel(response, EXCEL_NAME, plotBaseVO, PlotBaseExcelVO.class, mergeColumeIndex, mergeRowIndex);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}