Spring Boot+Mybatis+Easyexcel指定位置导出excel文件

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);
    }
}