Spring Boot使用Easyexcel指定位置导出excel文件
导出结果
在这里插入图片描述
整体结构
数据库表
实体类:student.java
@Data
public class student {
@ColumnWidth(value = 20)//指定单元格宽度
@ExcelProperty(value = "学号")//单元格表头名称
private Integer id;
@ColumnWidth(value = 20)
@ExcelProperty(value = "姓名")
private String name;
@ColumnWidth(value = 20)
@ExcelProperty(value = "年龄")
private Integer age;
@ColumnWidth(value = 20)
@ExcelProperty(value = "性别",converter = genderConverter.class)//将数据库中1,2转换为男,女
private Integer sex;
}
性别枚举类:gender.java
将数据库中1,2转换为男,女
@Getter
public enum gender{
UNKNOWN(0,"未知"),
MALE(1,"男"),
FEMALE(2,"女");
private final Integer value;
@JsonFormat
private final String description;
gender(Integer value, String description){
this.value=value;
this.description=description;
}
public static gender convert(Integer value) {
// 用于为给定元素创建顺序流
// values:获取枚举类型的对象数组
return Stream.of(values())
.filter(bean -> bean.value.equals(value))
.findAny()
.orElse(UNKNOWN);
}
public static gender convert(String description) {
return Stream.of(values())
.filter(bean -> bean.description.equals(description))
.findAny()
.orElse(UNKNOWN);
}
转换类:genderConverter
//类型转换
public class genderConverter implements Converter<Integer> {
// 实体类中对象属性类型
@Override
public Class<?> supportJavaTypeKey() {
return Integer.class;
}
// excel中对应列属性
@Override
public CellDataTypeEnum supportExcelTypeKey() {
return CellDataTypeEnum.STRING;
}
// excel转domain
@Override
public Integer convertToJavaData(ReadConverterContext<?> context) throws Exception {
return gender.convert(context.getReadCellData().getStringValue()).getValue();
}
// domain转excel
@Override
public WriteCellData<?> convertToExcelData(WriteConverterContext<Integer> context) throws Exception {
return new WriteCellData<>(gender.convert(context.getValue()).getDescription());
}
}
service层:StuService
public interface StuService {
List<student> getall();
void export(HttpServletResponse reponse) throws IOException;
}
@Service
public class StuServiceImpl implements StuService {
@Autowired
private StudentMapper sm;
@Override
public List<student> getall() {
return sm.getAll();
}
@Override
public void export(HttpServletResponse response) throws IOException {
OutputStream ops=response.getOutputStream();
// 设置响应头
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
//设置表头居中对齐
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
headWriteCellStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
// 字体
WriteFont headWriteFont = new WriteFont();
headWriteFont.setFontHeightInPoints((short) 10);
headWriteCellStyle.setWriteFont(headWriteFont);
headWriteCellStyle.setWrapped(true);
// 内容居中
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle,contentWriteCellStyle);
String fileName = URLEncoder.encode("学生", "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
EasyExcel.write(ops,student.class).sheet("学生").registerWriteHandler(horizontalCellStyleStrategy).doWrite(this::getall);
ops.flush();
ops.close();
}
mapper层:StudentMapper
@Mapper
public interface StudentMapper {
List<student> getAll();
}
mapper.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.easyexcel.mapper.StudentMapper">
<select id="getAll" resultType="com.easyexcel.domain.student">
select *
from user;
</select>
</mapper>
controller层:StuController
@RestController
@RequestMapping("/student")
public class StuController {
@Autowired
private StuService ss;
@GetMapping("/export")
public void get(HttpServletResponse response) throws IOException {
ss.export(response);
}
}