该文章里面有2种方法的工具类
在这里插入图片描述
在这里插入图片描述

添加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>

该工具类是把相同数据合并没有进行判断

合并单元格工具类–该工具类是把相同数据合并没有进行判断

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 lombok.Data;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;

import java.util.List;

@Data
public class ExcelFillCellMergeStrategy implements CellWriteHandler {
    private int[] mergeColumnIndex;
    private int mergeRowIndex;

    public ExcelFillCellMergeStrategy() {
    }

    public ExcelFillCellMergeStrategy(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 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();

        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) {
        //获取当前行的第一列的数据和上一行的第一列数据,通过第一行数据是否相同进行合并
//        Cell preCell_now = cell.getSheet().getRow(curRowIndex ).getCell(curColIndex);
//        Object curData = preCell_now.getCellTypeEnum() == CellType.STRING ? preCell_now.getStringCellValue() : preCell_now.getNumericCellValue();
//        Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex - 1);
  //      Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();
        //获取当前行的当前列的数据和上一行的当前列列数据,通过上一行数据是否相同进行合并
        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> 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);
            }
        }
    }
}

导出的excel是这样的
在这里插入图片描述
下面这个工具类是根据工程名称相同合并后面的数据

根据指定数据合并

package com.ph.rfwg.util;

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.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
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(1).getStringCellValue().equals(cell.getSheet().getRow(curRowIndex - 1).getCell(1).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);
        }
    }
}
}


在这里插入图片描述

实体类

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 lombok.Data;
import java.io.Serializable;
import java.util.Date;
@Data
@ContentRowHeight(15) //内容行高
@HeadRowHeight(20)//表头行高
public class ProbQuVo implements Serializable {
    public static final long serialVersionUID = 1L;
    @ColumnWidth(15)
    @ExcelProperty(value = {"区级问题台账","该项等级"}, index = 5)
    private String grade;
    @ColumnWidth(20)
    @ExcelProperty(value = {"区级问题台账","问题类型"}, index = 4)
    private String typeName;
    @ExcelIgnore
    private String createTime;
    @ColumnWidth(25)
    @ExcelProperty(value = {"区级问题台账","巡查时间"}, index = 3)
    private String checkTime;
    @ExcelIgnore
    private String checkId;
    @ColumnWidth(25)
    @ExcelProperty(value = {"区级问题台账","工程名称"}, index = 1)
    private String projectName;
    @ExcelIgnore
    private String projId;
    @ColumnWidth(12)
    @ExcelProperty(value = {"区级问题台账","区域"}, index = 2)
    private String distname;
    @ColumnWidth(12)
    @ExcelProperty(value = {"区级问题台账","序号"}, index = 0)
    private Integer number;
}

@ColumnWidth(15)注解是列宽
@ExcelIgnore注解是不参与导出的字段
@ExcelProperty(value = {“区级问题台账”,“巡查时间”}, index = 3)主标题副标题等类推,index是展示的顺序
如果使用了自动换行策略,那么类上面就不需要@ContentRowHeight(15) //内容行高的注解,加了该注解不会自动换行

service

import com.baomidou.mybatisplus.core.metadata.IPage;
import org.apache.ibatis.annotations.Param;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.List;

public interface ProblemService {
    // 导出excel,条件筛选
    void exportQuExcel(String projId,String areaId,String year,String type,String district,HttpServletResponse response);
}

serviceipml

@Override
    public void exportQuExcel(String projId,String areaId,String year,String type,String district,HttpServletResponse response) {
    	// 获取数据
        List<ProbQuVo> probVos = problemMapper.listQuProblem(projId,areaId,year,type,district);
        // 生成自增序号
        for (int i = 0; i < probVos.size(); i++) {
            probVos.get(i).setNumber(i + 1);
        }
        try {
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
            String fileName = URLEncoder.encode("问题台账", "UTF-8");
            response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
            //需要合并的列
            int[] mergeColumeIndex = {1, 3};
            //从第二行后开始合并
            int mergeRowIndex = 2;
            // 调用合并单元格工具类,此工具类是没有根据合并,数据相同就合并了
            // ExcelFillCellMergeStrategy excelFillCellMergeStrategy = new ExcelFillCellMergeStrategy(mergeRowIndex,mergeColumeIndex);
            
			 // 调用合并单元格工具类,此工具类是根据工程名称相同则合并后面数据
			ExcelMergeUtil excelFillCellMergeStrategy = new ExcelMergeUtil(mergeRowIndex,mergeColumeIndex);
            // 这里需要设置不关闭流
            WriteCellStyle headWriteCellStyle = new WriteCellStyle();
            //设置背景颜色
            headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
            //设置头字体
            WriteFont headWriteFont = new WriteFont();
            headWriteFont.setFontHeightInPoints((short)13);
            headWriteFont.setBold(true);
            headWriteCellStyle.setWriteFont(headWriteFont);
            //设置头居中
            headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
            //内容策略
            WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
            //设置 水平居中
            contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
            //内容自动换行,实体类不能加@ContentRowHeight() 注解,否则会挤压数据,造成行高固定
            contentWriteCellStyle.setWrapped(true);
            contentWriteCellStyle.setShrinkToFit(true);//设置文本收缩至合适
            // 垂直居中
            contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            //设置左边框
            contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
            //设置右边框
            contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
            //设置上边框
            contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
            //设置下边框
            contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
            
            HorizontalCellStyleStrategy horizontalCellStyleStrategy =
                    new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
			
            EasyExcel.write(response.getOutputStream(), ProbQuVo.class)
                    .registerWriteHandler(horizontalCellStyleStrategy)
                    .registerWriteHandler(excelFillCellMergeStrategy)
                    // 导出文件名
                    .autoCloseStream(Boolean.TRUE).sheet("问题台账")
                    .doWrite(probVos);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

controller

    @GetMapping("/test")
    public void exportQuExcel( @ApiParam(name = "projId",value = "工程id")@RequestParam(value = "projId",required = false)String projId,
                             @ApiParam(name = "areaId",value = "区域id")@RequestParam(value = "areaId",required = false)String areaId,
                             @ApiParam(name = "year",value = "年份")@RequestParam(value = "year",required = false)String year,
                             @ApiParam(name = "type",value = "问题类型")@RequestParam(value = "type",required = false)String type,
                               @ApiParam(name = "district",value = "区域id")@RequestParam(value = "district",required = false)String district,
                             HttpServletResponse response) {
        problemService.exportQuExcel(projId,areaId,year,type,district,response);
    }

不需要返回值,不然会报错
在这里插入图片描述

Logo

旨在为数千万中国开发者提供一个无缝且高效的云端环境,以支持学习、使用和贡献开源项目。

更多推荐