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

你的名字 2024-03-24 14:27 40阅读 0赞

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

### 1、pom依赖 ###

<dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>easyexcel</artifactId>
                <version>2.2.0-beta2</version>
            </dependency>
    
    		<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 = 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;
                //调用合并单元格工具
                ExcelMergeUtil.exportExcel(response, EXCEL_NAME, plotBaseVO, PlotBaseExcelVO.class, mergeColumeIndex, mergeRowIndex);
            } catch (Exception e) {
        
                e.printStackTrace();
            }
        }
    }

[a8dd52971c81449fa3bba587dc9fea5a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/526f29cbfd1042edb553087ee35b9900.png