当前位置：首页 > news >正文

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

news 来源：原创 2024/9/19 21:23:52

前言

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

实战代码

导出效果：

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 propertiesnewCellStyle.cloneStyleFrom(oldCellStyle);// Set new font colorFont font = workbook.createFont();font.setColor(color.getIndex());newCellStyle.setFont(font);// Apply new stylecontext.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;
}