通过EasyExcel设置自定义表头及设置特定单元格样式、颜色

前言

   在项目开发中，我们会遇到各种文件导出的开发场景，但是这种情况并都不常用，于是本人将自己工作中所用的代码封装成工具类，旨在记录工具类使用方法和技术分享。

实战代码

导出效果：

1、导入依赖

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.1.0</version>
        </dependency>

2、导出代码

	/**
	 * 导出打卡报表
	 *
	 * @param request  请求参数
	 * @param response 返回参数
	 */
	@PostMapping("/export")
	public void exportOaPersonOpenCardRecord(@RequestBody OaPersonOpenCardRecordRequest request, HttpServletResponse response) {
		List<OaPersonOpenCardRecord> oaPersonOpenCardRecordList = oaOpenCardRecordService.findOaPersonOpenCardRecordExportList(request);
		List<List<String>> headList = new ArrayList<>();
		try {
			// 设置动态头
			buildExportHead(oaPersonOpenCardRecordList, headList);
			// 获取动态数据
			List<List<Object>> exportList = new ArrayList<>();
			for (int i = 0; i < oaPersonOpenCardRecordList.size(); i++) {
				List<Object> valueList = new ArrayList<>();
				valueList.add(i + 1);
				valueList.add(oaPersonOpenCardRecordList.get(i).getUserName());
				valueList.add(oaPersonOpenCardRecordList.get(i).getOverWordCount());
				List<OaOpenCardInfo> oaOpenCardInfoList = oaPersonOpenCardRecordList.get(i).getOpenCardInfoList();
				oaOpenCardInfoList.forEach(oaOpenCardInfo -> {
					StringBuilder stringBuilder = new StringBuilder();
					if (StringUtils.isNotBlank(oaOpenCardInfo.getStartTime()) && request.getOpenTimeStatus() == 1) {
						stringBuilder.append(oaOpenCardInfo.getStartTime()).append("\n");
					}
					if (StringUtils.isNotBlank(oaOpenCardInfo.getEndTime()) && request.getOpenTimeStatus() == 1) {
						stringBuilder.append(oaOpenCardInfo.getEndTime()).append("\n");
					}
					if (StringUtils.isNotBlank(oaOpenCardInfo.getOpenCardStatus())) {
						stringBuilder.append(oaOpenCardInfo.getOpenCardStatus());
					}
					valueList.add(stringBuilder.toString());
				});
				exportList.add(valueList);
			}
			response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
			response.setCharacterEncoding("utf-8");
			String fileName = URLEncoder.encode("考勤报表", "UTF-8").replaceAll("\\+", "%20");
			response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
			EasyExcel.write(response.getOutputStream())
				.head(headList)
				.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
				.registerWriteHandler(new CellWriteHandler() {
					@Override
                    //设置特定样式
					public void afterCellDispose(CellWriteHandlerContext context) {
						Workbook workbook = context.getWriteWorkbookHolder().getWorkbook();
						CellStyle cellStyle = workbook.createCellStyle();
						// 设置换行
						cellStyle.setWrapText(true);
						// 设置表格内容垂直居中
						cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
						// 设置表格内容水平居中
						cellStyle.setAlignment(HorizontalAlignment.CENTER);
						// 设置带框线
						cellStyle.setBorderTop(BorderStyle.THIN);
						cellStyle.setBorderRight(BorderStyle.THIN);
						cellStyle.setBorderBottom(BorderStyle.THIN);
						cellStyle.setBorderLeft(BorderStyle.THIN);
						context.getCell().setCellStyle(cellStyle);
						if (BooleanUtils.isNotTrue(context.getHead())) {
							List<String> headNameList = context.getHeadData().getHeadNameList();
							String headName = headNameList.get(NumberConstant.ZERO);
							if (!headName.contains("考勤")) {
								return;
							}
							Cell cell = context.getCell();
							String stringCellValue = cell.getStringCellValue();
							if (!stringCellValue.contains("加班") && !stringCellValue.contains("正常") && !stringCellValue.contains("休息")) {
								//红色
								setCellStyle(context, IndexedColors.RED);
							} else if (stringCellValue.contains("加班")) {
								//绿色
								setCellStyle(context, IndexedColors.GREEN);
							}
						}
					}
				})
				.sheet("考勤报表").doWrite(exportList);

		} catch (Exception e) {
			log.error("导出失败", e);
		}
	}

	/***
	 * 设置特定单元格的颜色及字体
	 * @param context
	 * @param color
	 */
	private void setCellStyle(CellWriteHandlerContext context, IndexedColors color) {
		Workbook workbook = context.getWriteWorkbookHolder().getWorkbook();
		CellStyle oldCellStyle = context.getCell().getCellStyle();
		CellStyle newCellStyle = workbook.createCellStyle();
		// Copy existing style properties
		newCellStyle.cloneStyleFrom(oldCellStyle);
		// Set new font color
		Font font = workbook.createFont();
		font.setColor(color.getIndex());
		newCellStyle.setFont(font);
		// Apply new style
		context.getCell().setCellStyle(newCellStyle);
	}

	private static void buildExportHead(List<OaPersonOpenCardRecord> oaPersonOpenCardRecordList, List<List<String>> headList) {
		List<String> head0 = new ArrayList<>();
		head0.add("序号");
		List<String> head1 = new ArrayList<>();
		head1.add("姓名");
		List<String> head2 = new ArrayList<>();
		head2.add("晚上19:30以后打卡次数");
		headList.add(head0);
		headList.add(head1);
		headList.add(head2);
		if (!oaPersonOpenCardRecordList.isEmpty()) {
			List<OaOpenCardInfo> openCardInfoList = oaPersonOpenCardRecordList.get(0).getOpenCardInfoList();
			openCardInfoList.forEach(openCardInfo -> {
				List<String> head = new ArrayList<>();
				head.add("考勤");
				head.add(openCardInfo.getTitle());
				headList.add(head);
			});
		}
	}

实体类

package com.sansint.oa.param;

import com.sansint.oa.domain.OaPersonOpenCardRecord;
import lombok.Data;

/**
 * @author DJY
 * @date 2024/8/29
 */
@Data
public class OaPersonOpenCardRecordRequest extends OaPersonOpenCardRecord {

	/***
	 * 开始时间
	 */
	private String startDate;
	/***
	 * 结束时间
	 */
	private String endDate;
	/**
	 * 上班时间
	 */
	String startWorkTime = "";
	/**
	 * 下班时间
	 */
	String endWorkTime = "";
	/**
	 * 加班时刻
	 */
	String overWorkTime = "";

	/**
	 * 打开时间状态
	 */
	 Integer openTimeStatus;
}

package com.sansint.oa.domain;

import lombok.Data;

import java.io.Serializable;
import java.util.List;
import java.util.Map;

/**
 * @author DJY
 * @date 2024/8/29
 */
@Data
public class OaPersonOpenCardRecord implements Serializable {
	private static final long serialVersionUID = 1L;
	/****
	 * 姓名
	 */
	private String userName;
	/****
	 * 晚上7.30以后打卡次数
	 */
	private long overWordCount;
	/***
	 * 考勤信息
	 */
	private List<OaOpenCardInfo> openCardInfoList;

	/***
	 * 考勤信息
	 */
	private Map<String,Object> openCardInfoMap;
}

package com.sansint.oa.domain;

import lombok.Data;

import java.io.Serializable;

/**
 * @author DJY
 * @date 2024/8/29
 */
@Data
public class OaOpenCardInfo implements Serializable {
	private static final long serialVersionUID = 1L;
	/****
	 * 姓名
	 */
	private String userName;
	/****
	 * 最早打卡时间
	 */
	private String startTime;
	/****
	 * 最晚打卡时间
	 */
	private String endTime;
	/****
	 * 标题
	 */
	private String title;
	/****
	 * 打卡状态
	 */
	private String openCardStatus;
	/****
	 * 打卡状态颜色
	 */
	private String openCardColor;
	/***
	 * 排序
	 */
	private Long sort;
}