EasyExcel导出Excel时值转换处理（如：1：男，2：女等场景）
注意点
easyexcel版本3.3.2，低版本的Converter实现不一样Converter要引对，其他框架也会有这个，要用easyexcel的implements Converter<Long>中Long是 原值，比如上面的1、2等，而不是转换后的String男女convertToExcelData是写（下载、导出）convertToJavaData是读（导入）- 转换类需要有
无参构造，没有会报实例化失败异常 - 使用注解注入失败，我使用Hutool的SpringUtils直接获取Bean
import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.converters.ReadConverterContext;
import com.alibaba.excel.converters.WriteConverterContext;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.data.CellData;
import com.alibaba.excel.metadata.data.ReadCellData;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.metadata.property.ExcelContentProperty;
import com.ruoyi.common.core.domain.entity.SysUser;
import com.ruoyi.common.utils.spring.SpringUtils;
import com.ruoyi.system.service.ISysUserService;
import org.springframework.context.annotation.Configuration;
import org.springframework.stereotype.Component;
#### 编写转换类
public class UserConverter implements Converter<Long> {
private ISysUserService service;
public UserConverter() {
service = SpringUtils.getBean(ISysUserService.class);
}
@Override
public Class<?> supportJavaTypeKey() {
return Converter.super.supportJavaTypeKey();
}
@Override
public CellDataTypeEnum supportExcelTypeKey() {
return Converter.super.supportExcelTypeKey();
}
@Override
public Long convertToJavaData(ReadCellData<?> cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
return Converter.super.convertToJavaData(cellData, contentProperty, globalConfiguration);
}
@Override
public Long convertToJavaData(ReadConverterContext<?> context) throws Exception {
return Converter.super.convertToJavaData(context);
}
@Override
public WriteCellData<?> convertToExcelData(WriteConverterContext<Long> context) throws Exception {
Long value = context.getValue();
SysUser sysUser = service.selectUserById(value);
return new WriteCellData<>(sysUser.getNickName());
}
}
在导出实体中使用自定义转换类
@ExcelProperty(value = "团长",converter = UserConverter.class)
EasyExcel导出Excel(带自定义样式)
pom.xml引入依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.3.2</version>
</dependency>
编写样式工具类StyleUtils(可全局生效)
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import org.apache.poi.ss.usermodel.*;
public class StyleUtils {
/**
* 标题样式
* @return
*/
public static WriteCellStyle getHeadStyle(){
// 头的策略
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
// 背景颜色
// headWriteCellStyle.setFillForegroundColor(IndexedColors.LIGHT_TURQUOISE1.getIndex());
// headWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
// 字体
WriteFont headWriteFont = new WriteFont();
headWriteFont.setFontName("宋体");//设置字体名字
headWriteFont.setFontHeightInPoints((short)14);//设置字体大小
headWriteFont.setBold(true);//字体加粗
headWriteCellStyle.setWriteFont(headWriteFont); //在样式用应用设置的字体;
// 样式
headWriteCellStyle.setBorderBottom(BorderStyle.THIN);//设置底边框;
headWriteCellStyle.setBottomBorderColor((short) 0);//设置底边框颜色;
headWriteCellStyle.setBorderLeft(BorderStyle.THIN); //设置左边框;
headWriteCellStyle.setLeftBorderColor((short) 0);//设置左边框颜色;
headWriteCellStyle.setBorderRight(BorderStyle.THIN);//设置右边框;
headWriteCellStyle.setRightBorderColor((short) 0);//设置右边框颜色;
headWriteCellStyle.setBorderTop(BorderStyle.THIN);//设置顶边框;
headWriteCellStyle.setTopBorderColor((short) 0); //设置顶边框颜色;
headWriteCellStyle.setWrapped(true); //设置自动换行;
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);//设置水平对齐的样式为居中对齐;
headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER); //设置垂直对齐的样式为居中对齐;
headWriteCellStyle.setShrinkToFit(true);//设置文本收缩至合适
return headWriteCellStyle;
}
/**
* 内容样式
* @return
*/
public static WriteCellStyle getContentStyle(){
// 内容的策略
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
// 背景绿色
// 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定
// contentWriteCellStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
// contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
// 设置字体
WriteFont contentWriteFont = new WriteFont();
contentWriteFont.setFontHeightInPoints((short) 12);//设置字体大小
contentWriteFont.setFontName("宋体"); //设置字体名字
contentWriteCellStyle.setWriteFont(contentWriteFont);//在样式用应用设置的字体;
//设置样式;
contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);//设置底边框;
contentWriteCellStyle.setBottomBorderColor((short) 0);//设置底边框颜色;
contentWriteCellStyle.setBorderLeft(BorderStyle.THIN); //设置左边框;
contentWriteCellStyle.setLeftBorderColor((short) 0);//设置左边框颜色;
contentWriteCellStyle.setBorderRight(BorderStyle.THIN);//设置右边框;
contentWriteCellStyle.setRightBorderColor((short) 0);//设置右边框颜色;
contentWriteCellStyle.setBorderTop(BorderStyle.THIN);//设置顶边框;
contentWriteCellStyle.setTopBorderColor((short) 0); ///设置顶边框颜色;
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);// 水平居中
contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中
contentWriteCellStyle.setWrapped(true); //设置自动换行;
// contentWriteCellStyle.setShrinkToFit(true);//设置文本收缩至合适
return contentWriteCellStyle;
}
}
编写动态处理器CustomCellWriteHandler（判断单元格内容，根据内容做样式修改等）
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.util.BooleanUtils;
import com.alibaba.excel.write.handler.AbstractCellWriteHandler;
import com.alibaba.excel.write.handler.context.CellWriteHandlerContext;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
/**
* @Description: 描述
* @Author: coderzzc
* @CreateDate: 2023/8/1 17:43
* @UpdateUser: coderzzc
* @UpdateDate: 2023/8/1 17:43
* @UpdateRemark: 特殊说明
* @Version: 1.0
*/
public class CustomCellWriteHandler extends AbstractCellWriteHandler {
@Override
public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {
// 设置行高测试
int rowIndex = row.getRowNum();
System.out.println("当前行: " + rowIndex);
short height = 600;
row.setHeight(height);
}
@Override
public void afterCellDispose(CellWriteHandlerContext context) {
Cell cell = context.getCell();
int rowIndex = cell.getRowIndex();
int cellIndex = cell.getColumnIndex();
// 自定义宽度处理
// 自定义样式处理
// 当前事件会在 数据设置到poi的cell里面才会回调
// 判断不是头的情况 如果是fill 的情况 这里会==null 所以用not true
Head headData = context.getHeadData();
for (String s : headData.getHeadNameList()) {
if (s.contains("计算结果")) {
if (BooleanUtils.isNotTrue(context.getHead())) {
String stringCellValue = cell.getStringCellValue();
if (stringCellValue.chars().allMatch(Character::isDigit)) {
int parseInt = Integer.parseInt(stringCellValue);
if ((parseInt >= 15 && parseInt <= 30) || (parseInt >= 72 && parseInt <= 76) || (parseInt >= 88 && parseInt <= 96) || (parseInt >= 114 && parseInt <= 125) || (parseInt >= 167 && parseInt <= 178)) {
// 拿到poi的workbook
Workbook workbook = context.getWriteWorkbookHolder().getWorkbook();
// 这里千万记住 想办法能复用的地方把他缓存起来 一个表格最多创建6W个样式
// 不同单元格尽量传同一个 cellStyle
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
// 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
//设置样式;
// xlsx格式
Font font = workbook.createFont();
font.setColor(IndexedColors.RED.getIndex()); // 设置字体颜色为红色
cellStyle.setFont(font);
cellStyle.setBorderBottom(BorderStyle.THIN);//设置底边框;
cellStyle.setBottomBorderColor((short) 0);//设置底边框颜色;
cellStyle.setBorderLeft(BorderStyle.THIN); //设置左边框;
cellStyle.setLeftBorderColor((short) 0);//设置左边框颜色;
cellStyle.setBorderRight(BorderStyle.THIN);//设置右边框;
cellStyle.setRightBorderColor((short) 0);//设置右边框颜色;
cellStyle.setBorderTop(BorderStyle.THIN);//设置顶边框;
cellStyle.setTopBorderColor((short) 0); ///设置顶边框颜色;
cellStyle.setAlignment(HorizontalAlignment.CENTER);// 水平居中
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中
cellStyle.setWrapText(true); //设置自动换行;
cell.setCellStyle(cellStyle);
// 由于这里没有指定dataformat 最后展示的数据 格式可能会不太正确
// 这里要把 WriteCellData的样式清空， 不然后面还有一个拦截器 FillStyleCellWriteHandler 默认会将 WriteCellStyle 设置到
// cell里面去 会导致自己设置的不一样（很关键）
context.getFirstCellData().setWriteCellStyle(null);
}
}
}
}
break;
}
}
}
编写后端导出逻辑
import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.WriteTable;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import com.alibaba.fastjson.JSON;
import edu.hebut.bone.StyleUtils;
import edu.hebut.bone.aspect.Auth;
import edu.hebut.bone.aspect.AuthAspect;
import edu.hebut.bone.config.CustomCellWriteHandler;
import edu.hebut.bone.domain.dao.CaseInfo;
import edu.hebut.bone.domain.dao.ChildCase;
import edu.hebut.bone.domain.enums.UserRoleEnum;
import edu.hebut.bone.exception.BoneException;
import edu.hebut.bone.exception.ExceptionEnum;
import edu.hebut.bone.service.CaseService;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.assertj.core.util.Lists;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.lang.reflect.Field;
import java.time.format.DateTimeFormatter;
import java.util.*;
@Slf4j
@RestController
@CrossOrigin
@RequestMapping("bone/case")
@RequiredArgsConstructor(onConstructor = @__(@Autowired))
public class ExcelExport {
private final AuthAspect authAspect;
private final CaseService caseService;
@GetMapping("/exportExcel")
@Auth({UserRoleEnum.DOCTOR, UserRoleEnum.ADMIN})
public void ExportExcel(@RequestParam(value = "childCaseId")Long childCaseId, HttpServletResponse response) throws IOException, IllegalAccessException {
Long doctorId = authAspect.get("id", Long.class);
// 获取用户权限
UserRoleEnum role = UserRoleEnum.valueOf(authAspect.get("role", String.class));
ChildCase childCase = caseService.queryChildCase(childCaseId);
CaseInfo caseInfo = caseService.queryCase(childCase.getCaseId());
// 判断是否用户本人,非本人无权访问, 管理员可访问所有
if (!UserRoleEnum.ADMIN.equals(role)){
if (caseInfo == null || !caseInfo.getDoctor().equals(doctorId)){
throw new BoneException(ExceptionEnum.INSUFFICIENT_PRIVILEGES);
}
}
// 动态添加表头，适用一些表头动态变化的场景
WriteSheet sheet1 = new WriteSheet();
sheet1.setSheetName("诊断详情");
sheet1.setSheetNo(0);
response.setContentType("application/vnd.ms-excel;chartset=utf-8"); //文件扩展名为excel格式
response.setHeader("Content-Disposition", "attachment;filename=" + caseInfo.getPatient()); //触发文件名为filename的“另存为”对话框
// 设置单元格样式
HorizontalCellStyleStrategy horizontalCellStyleStrategy =
new HorizontalCellStyleStrategy(StyleUtils.getHeadStyle(), StyleUtils.getContentStyle());
// 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为用户表 然后文件流会自动关闭
ExcelWriter writer = EasyExcelFactory.write(response.getOutputStream())
.inMemory(true) // 富文本
.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()) // 字体居中显示
.registerWriteHandler(new CustomCellWriteHandler())
.registerWriteHandler(horizontalCellStyleStrategy)
.autoCloseStream(true)
.excelType(ExcelTypeEnum.XLSX)
.build();
List<List<String>> head1 = Lists.newArrayList();
head1.add(Lists.newArrayList("病例信息","患者姓名"));
ArrayList<String> strings = Lists.newArrayList( "患者年龄", "身份证号", "分组", "病例号", "患者性别", "手机号","诊断", "创建日期");
for (String string : strings) {
head1.add(Lists.newArrayList("病例信息",string));
}
List<List<Object>> contentList = Lists.newArrayList();
DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
String format1 = dateTimeFormatter.format(caseInfo.getCreateBy());
for (Field field : caseInfo.getClass().getDeclaredFields()) {
field.setAccessible(true);
if (field.get(caseInfo) == null && field.getType() == String.class) {
field.set(caseInfo, "空");
}
}
contentList.add(Lists.newArrayList(
caseInfo.getPatient(),
caseInfo.getPatientAge()+"岁",
caseInfo.getIdCard(),
caseInfo.getAffectedSide()+caseInfo.getAffectedLimb()+caseInfo.getCaseType(),
caseInfo.getHospitalCaseId(),
caseInfo.getGender(),
caseInfo.getPhone(),
caseInfo.getCaseInfo(),
format1));
// 创建一个表格，用于 Sheet 中使用
WriteTable table1 = new WriteTable( );
table1.setTableNo(1);
table1.setHead(head1);
writer.write(contentList, sheet1, table1);
List<List<String>> head2 = Lists.newArrayList();
head2.add(Lists.newArrayList("畸形参数","患侧"));
for (String s : Lists.newArrayList( "正位位移", "侧位位移", "轴向位移", "参照断裂处", "正位角度", "侧位角度", "轴向角度")) {
head2.add(Lists.newArrayList("畸形参数",s));
}
List<List<Object>> contentList2 = Lists.newArrayList();
contentList2.add(Lists.newArrayList(childCase.getAffectedSide(),
childCase.getZwwyfx()+childCase.getZwwy()+"mm",childCase.getCwwyfx()+childCase.getCwwy()+"mm",
childCase.getZwwyfx()+childCase.getZwwy()+"mm",
childCase.getCzdlc(),
childCase.getZwjdfx()+childCase.getCwjd()+"°",
childCase.getCwjdfx()+childCase.getZxwy()+"°",
childCase.getZxjdfx()+childCase.getZxjd()+"°"));
WriteTable table2= new WriteTable();
table2.setTableNo(2);
table2.setHead(head2);
writer.write(contentList2, sheet1, table2);
List<List<String>> head3 = Lists.newArrayList();
head3.add(Lists.newArrayList("框架参数","近侧环直径"));
for (String s : Lists.newArrayList( "远侧环直径", "参考环中心正位位移", "参考环中心侧位位移", "参考环中心轴向位移", "参考环旋转角度")) {
head3.add(Lists.newArrayList("框架参数",s));
}
List<List<Object>> contentList3 = Lists.newArrayList();
contentList3.add(Lists.newArrayList(
childCase.getNearSideDiameter()+"mm",
childCase.getFarSideDiameter()+"mm",
childCase.getZwwyfxRef()+childCase.getZwwyRef()+"mm",
childCase.getCwwyfxRef()+childCase.getCwwyRef()+"mm",
childCase.getZwwyRef()+"mm",childCase.getSenseRotationRef()+childCase.getRotationAngleRef()+"°"));
WriteTable table3= new WriteTable();
table3.setTableNo(3);
table3.setHead(head3);
writer.write(contentList3, sheet1, table3);
String result = childCase.getResult();
result = StringUtils.replace(result, "\\\"", "\"");
List<List> parseArray = JSON.parseArray(result, List.class);
List<List<String>> head4 = Lists.newArrayList();
head4.add(Lists.newArrayList("计算结果","日期"));
for (String s : Lists.newArrayList("杆1(红)", "杆2(橙)", "杆3(黄)", "杆4(绿)", "杆5(蓝)", "杆6(紫)")) {
head4.add(Lists.newArrayList("计算结果",s));
}
List<List<Object>> contentList4 = Lists.newArrayList();
DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");
// 获取当前时间
int day=0;
for (List list : parseArray) {
// 把日期往后增加一天,整数 往后推,负数往前移动
String format2 = formatter.format(caseInfo.getCreateBy().plusDays(day));
day++;
list.remove(0);
list.add(0,format2);
contentList4.add(list);
}
WriteTable table4= new WriteTable();
table4.setTableNo(4);
table4.setHead(head4);
writer.write(contentList4, sheet1, table4);
writer.finish();
}
}
编写前端下载逻辑
#api
export function exportCaseById(param) {
return request({
url: '/case/exportExcel',
method: 'get',
responseType: 'blob', //设置返回信息为二进制文件，默认为json
params: param
})
}
#页面中调用
exportCaseById({ childCaseId: this.recordId }).then(res => {
// debugger;
let blob = new Blob([res], { type: 'application/xlsx' });
let url = window.URL.createObjectURL(blob);
const link = document.createElement('a'); //创建a标签
link.href = url;
link.download = this.recordId + '.xlsx'; //重命名文件
link.click();
URL.revokeObjectURL(url);
})
