【easyexcel根据数据相同导出动态合并单元格】
·
该文章里面有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);
}
不需要返回值,不然会报错
更多推荐
已为社区贡献5条内容
所有评论(0)