该文章里面有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;@Datapublic class ExcelFillCellMergeStrategy implements CellWriteHandler {private int[] mergeColumnIndex;private int mergeRowIndex;public ExcelFillCellMergeStrategy() {}public ExcelFillCellMergeStrategy(int mergeRowIndex, int[] mergeColumnIndex) {this.mergeRowIndex = mergeRowIndex;this.mergeColumnIndex = mergeColumnIndex;}@Overridepublic void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {}@Overridepublic void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {}@Overridepublic void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {}@Overridepublic 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;}@Overridepublic void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {}@Overridepublic void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {}@Overridepublic void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {}@Overridepublic 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;@ExcelIgnoreprivate String createTime;@ColumnWidth(25)@ExcelProperty(value = {"区级问题台账","巡查时间"}, index = 3)private String checkTime;@ExcelIgnoreprivate String checkId;@ColumnWidth(25)@ExcelProperty(value = {"区级问题台账","工程名称"}, index = 1)private String projectName;@ExcelIgnoreprivate 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
@Overridepublic 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);}
不需要返回值，不然会报错
