EasyExcel合并单元格策略样例
运行示例
处理前:
处理后:
说明
本次使用的是一次性绝对合并策略: List<OnceAbsoluteMergeStrategy> mergeStrategyList = new ArrayList<>();
OnceAbsoluteMergeStrategy
四个参数:合并开始行,合并结束行,合并开始列,合并结束列
添加策略:builder.registerWriteHandler(strategy);
代码
1、模型层
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 lombok.EqualsAndHashCode;
import lombok.experimental.Accessors;
import java.io.Serializable;
import java.math.BigDecimal;
/**
* @author CC
* @description 工时导出excel实体对象
* @className RespManHoursVO
* @date 2022/6/21 14:21
*/
@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
@ContentRowHeight(18) //内容行高
@HeadRowHeight(25) //标题行高
public class RespManHoursVO implements Serializable {
@ExcelProperty(value = {"项目名称"},index = 0)
@ColumnWidth(18)
private String project;
@ExcelProperty(value = {"姓名"},index = 1)
@ColumnWidth(7)
private String name;
@ExcelProperty(value = {"工时占比"},index = 2)
@ColumnWidth(12)
private BigDecimal manHour;
@ExcelProperty(value = {"所属部门"},index = 3)
@ColumnWidth(19)
private String dept;
@ExcelProperty(value = {"是否实习"},index = 4)
@ColumnWidth(12)
private String isIntern;
}
2、服务层
import cn.hutool.core.date.DateTime;
import cn.hutool.core.date.DateUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.write.builder.ExcelWriterSheetBuilder;
import com.alibaba.excel.write.merge.OnceAbsoluteMergeStrategy;
import com.baomidou.mybatisplus.core.toolkit.CollectionUtils;
import com.meb.imis.fiscal.api.IHrUserArchiveService;
import com.meb.imis.fiscal.pojo.vo.resp.RespManHoursVO;
import com.meb.imis.fiscal.service.FileExportService;
import com.meb.imis.framework.api.workteam.service.IWorkTeamService;
import lombok.SneakyThrows;
import lombok.extern.log4j.Log4j;
import org.springframework.stereotype.Service;
import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.net.URLEncoder;
import java.util.*;
/**
* @author CC
* @description 文件导出服务实现类
* @className FileExportServiceImpl
* @date 2022/6/21 14:25
*/
@Log4j
@Service
public class FileExportServiceImpl implements FileExportService {
@Resource
private IWorkTeamService workTeamService;
@Resource
private IHrUserArchiveService hrUserArchiveService;
@Override
@SneakyThrows
public void exportWorkTime(String date, HttpServletResponse response) {
// 初始化数据
List<RespManHoursVO> sheet = new ArrayList<RespManHoursVO>(); // 导出列表
DateTime dateTime = DateUtil.parse(date, "yyyy-MM"); // 格式化日期
// 获取sheet
/**
* 略,自行生成数据
* eg:
* RespManHoursVO raw1 = new RespManHoursVO();
* raw1.setProject("项目1");
* raw1.setName("张三");
* ...
* sheet.add(raw1);
*/
// 设置response参数
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
String fileName = URLEncoder.encode( date.substring(0,7)+ "月数据", "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
// 生成合并策略
List<OnceAbsoluteMergeStrategy> mergeStrategyList = new ArrayList<>(); // 合并策略
int position = 0; // 需合并列(project)下标
int index = 0; // 遍历下标
int curStartRowNum = 1; // 开始合并行
int rowNum = 0; // 合并行数
String tempProject = null; // 临时项目名
boolean curMerge = false; // 当前行是否合并结束
for (RespManHoursVO manHours : sheet) {
if(index == 0){ // 第一次遍历
tempProject = manHours.getProject();
}else{
if (tempProject.equals(manHours.getProject())){ // 当前项目名同上一行项目名相等
rowNum++;
if (index == sheet.size()-1){ // 最后一行
curMerge = true;
}
}else{
if(rowNum > 0){
curMerge = true; // 开始合并
}else {
curStartRowNum = index+1; // 无需合并,且更新开始合并行
}
}
tempProject = manHours.getProject(); // 更新临时项目名
}
// 以项目名为单位的合并单元格
if (curMerge) {
// OnceAbsoluteMergeStrategy参数:合并开始行,结束行,开始列,结束列
// log.info("cc log======>合并策略坐标: "+"【"+curStartRowNum+","+(curStartRowNum + rowNum)+"】");
OnceAbsoluteMergeStrategy strategyByName = new OnceAbsoluteMergeStrategy(curStartRowNum, curStartRowNum + rowNum, position, position);
mergeStrategyList.add(strategyByName);
curMerge = false; // 合并结束
rowNum = 0 ;
curStartRowNum = index+1;
}
index++;
}
// 生成excel
ExcelWriterSheetBuilder builder = EasyExcel.write(response.getOutputStream(), RespManHoursVO.class).sheet("sheet1");
if (CollectionUtils.isNotEmpty(mergeStrategyList)) { // 添加策略
for (OnceAbsoluteMergeStrategy strategy : mergeStrategyList) {
builder.registerWriteHandler(strategy);
}
}
builder.doWrite(sheet); // 写入数据生成excel
}
}
3、控制层
import com.meb.imis.fiscal.service.FileExportService;
import io.swagger.annotations.ApiOperation;
import lombok.SneakyThrows;
import lombok.extern.log4j.Log4j;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
/**
* @author CC
* @description 导出文件
* @className FileExportController
* @date 2022/6/21 10:02
*/
@Log4j
@RestController
@RequestMapping("export")
public class FileExportController {
@Resource
private FileExportService fileExportService;
/**
* 内部结算项目管理界面导出人员工时功能
* @param date
* @param response
*/
@GetMapping("exportWorkTime")
@ApiOperation("导出")
@SneakyThrows
public void exportWorkTime(@RequestParam(value = "date") String date, HttpServletResponse response){
// 调用服务层生成excel
fileExportService.exportWorkTime(date,response);
}
}