项目中需要导出并合并指定的单元格,直接上代码:
/**
* 项目列表导出
*/
@GetMapping("v1/export_excel.do")
@ApiOperation(value = "项目导出", httpMethod = "GET")
@CrossOrigin
public void excel(@RequestParam("id") String id, HttpServletResponse response) throws IOException {
//查询需要导出的数据
List<ExcelInfo> excelInfos = projectService.getExcelInfo(Long.valueOf(id));
try {
//需要合并的列
int[] mergeColumeIndex = {0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15};
//从哪一列开始合并
int mergeRowIndex = 0;
//项目名称
String projectName = "空项目";
if (DggCollectionUtils.isNotEmpty(excelInfos)){
projectName = excelInfos.get(0).getProjectName();
}
response.setHeader("content-Type", "application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(projectName + LocalDate.now(), "UTF-8") + ".xlsx");
response.setCharacterEncoding("UTF-8");
EasyExcel.write(response.getOutputStream(), ExcelInfo.class)
.excelType(ExcelTypeEnum.XLSX).head(ExcelInfo.class)
//这里如果需要设置标题请放开,设置自定义标题
//.registerWriteHandler(new TitleSheetWriteHandler("我是一个小标题",2)) // 标题及样式,lastCol为标题第0列到底lastCol列的宽度
//设置默认样式及写入头信息开始的行数
.relativeHeadRowIndex(0)
.registerWriteHandler(new ExcelFillCellMergeStrategy(mergeRowIndex, mergeColumeIndex))
.sheet(projectName)
.doWrite(excelInfos);
}catch (Exception e) {
e.printStackTrace();
response.reset();
response.setCharacterEncoding("utf-8");
response.setContentType("application/json");
response.getWriter().println("打印失败");
}
}
/**
* @program: pms-service
* @Description: excel导出数据
* @Author: zwx
* @Date: 2021/7/9 13:44
*/
@Data
public class ExcelInfo {
/**
* 项目编号
*/
@ExcelProperty(value = {"项目编号"}, index = 0)
private String projectCode;
/**
* 项目名称
*/
@ExcelProperty(value = {"项目名称"}, index = 1)
private String projectName;
/**
* 所属板块
*/
@ExcelProperty(value = {"所属板块"}, index = 2)
private String projectSectionName;
/**
* 项目来源
*/
@ExcelProperty(value = {"项目来源"}, index = 3)
private String projectSourceName;
/**
* 项目级别
*/
@ExcelProperty(value = {"项目级别"}, index = 4)
private String projectLevelName;
/**
* 项目周期
*/
@ExcelProperty(value = {"项目周期"}, index = 5)
private String projectCycleName;
/**
* 关联OKR
*/
@ExcelProperty(value = {"关联OKR"}, index = 6)
private String relatedOkr;
/**
* 项目进度
*/
@ExcelProperty(value = {"项目进度百分比"}, index = 7)
private String projectRateName;
/**
* 项目计划开始日期
*/
@ExcelProperty(value = {"项目计划开始日期"}, index = 8)
private String projectStartDate;
/**
* 项目计划结束日期
*/
@ExcelProperty(value = {"项目计划结束日期"}, index = 9)
private String projectEndDate;
/**
* 项目完成时间
*/
@ExcelProperty(value = {"项目完成时间"}, index = 10)
private String projectRealTime;
/**
* 项目进度情况
*/
@ExcelProperty(value = {"项目进度情况"}, index = 11)
private String progressName;
/**
* 项目负责人/工号
*/
@ExcelProperty(value = {"项目负责人/工号"}, index = 12)
private String projectManagerName;
/**
* 项目状态
*/
@ExcelProperty(value = {"项目状态"}, index = 13)
private String projectStatusName;
/**
* 项目是否超期
*/
@ExcelProperty(value = {"项目是否超期"}, index = 14)
private String projectOverdueName;
/**
* 项目里程碑名称
*/
@ExcelProperty(value = {"里程碑"}, index = 15)
private String milepostName;
/**
* 任务标题
*/
@ExcelProperty(value = {"任务标题"}, index = 16)
private String title;
/**
* 任务内容
*/
@ExcelProperty(value = {"任务内容"}, index = 17)
private String content;
/**
* 交付标准
*/
@ExcelProperty(value = {"交付标准"}, index = 18)
private String deliverSdd;
/**
* 交付成果
*/
@ExcelProperty(value = {"交付成果"}, index = 19)
private String deliverAch;
/**
* 是否关键任务
*/
@ExcelProperty(value = {"是否关键任务"}, index = 20)
private String taskCruxName;
/**
* 是否需要评审
*/
@ExcelProperty(value = {"是否需要评审"}, index = 21)
private String taskDemandReviewName;
/**
* 任务进度
*/
@ExcelProperty(value = {"任务进度"}, index = 22)
private String taskProgress;
/**
* 任务负责人/工号
*/
@ExcelProperty(value = {"任务负责人/工号"}, index = 23)
private String taskDirectorName;
/**
* 任务计划开始时间
*/
@ExcelProperty(value = {"任务计划开始时间"}, index = 24)
private String startDate;
/**
* 任务计划结束时间
*/
@ExcelProperty(value = {"任务计划结束时间"}, index = 25)
private String endDate;
/**
* 任务实际完成时间
*/
@ExcelProperty(value = {"任务实际完成时间"}, index = 26)
private String finishDate;
/**
* 任务是否超期
*/
@ExcelProperty(value = {"任务是否超期"}, index = 27)
private String taskOvertime;
/**
* 任务创建时间
*/
@ExcelProperty(value = {"任务创建时间"}, index = 28)
private String taskCreateTime;
/**
* 任务创建人
*/
@ExcelProperty(value = {"任务创建人"}, index = 29)
private String taskCreateName;
}
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;
/**
* @program: pms-service
* @Description: 自定义合并单元格列
* @Author: zwx
* @Date: 2021/7/11 17:12
*/
@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) {
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 (preData.equals(curData)) {
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);
}
}
}
}