Java EasyExcel多个sheet导出

model

import java.math.BigDecimal;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.BaseRowModel;

import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.ToString;

/**
 * @Description: 学生成绩
 * @Auther: Wangtianming
 * @Date: 2021/12/21 10:18
 */
@Data
@ToString
@EqualsAndHashCode
public class StudentExcel extends BaseRowModel {

    @ApiModelProperty("姓名")
    @ExcelProperty(value = { "姓名", "姓名"}, index = 0)
    private String studentName;

    @ApiModelProperty("班级")
    @ExcelProperty(value = { "班级", "班级"}, index = 1)
    private String studentClass;

    @ApiModelProperty("数学")
    @ExcelProperty(value = { "成绩", "数学"}, index = 2)
    private BigDecimal math;

    @ApiModelProperty("语文")
    @ExcelProperty(value = { "成绩", "语文"}, index = 3)
    private BigDecimal language;

    @ApiModelProperty("英语")
    @ExcelProperty(value = { "成绩", "英语"}, index = 4)
    private BigDecimal english;

}

Handler

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;

import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;

/**
 * @Description:
 * @Auther: Wangtianming
 * @Date: 2022/2/25 16:47
 */
public class StudentHandler implements SheetWriteHandler {

    public String studentClass;
    public Integer sheetNum;
    public String headerName;

    public StudentHandler(String studentClass ,Integer sheetNum,String headerName) {
        this.studentClass = studentClass;
        this.sheetNum = sheetNum;
        this.headerName = headerName;
    }

    @Override
    public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {}

    @Override

    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {

        Workbook workbook = writeWorkbookHolder.getWorkbook();
        Sheet sheet = workbook.getSheetAt(sheetNum);

        // 设置标题
        Row row2 = sheet.createRow(0);

        row2.setHeight((short)800);

        Cell cell1 = row2.createCell(0);

        cell1.setCellValue(headerName);

        CellStyle cellStyle = workbook.createCellStyle();

        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        cellStyle.setAlignment(HorizontalAlignment.CENTER);

        Font font = workbook.createFont();

        font.setBold(true);

        font.setFontHeight((short)400);

        cellStyle.setFont(font);

        cell1.setCellStyle(cellStyle);

        sheet.addMergedRegionUnsafe(new CellRangeAddress(0, 0, 0, 4));

        // 设置填表日期,填报人,联系方式

        Row row3 = sheet.createRow(1);

        row3.setHeight((short)500);

        row3.createCell(0).setCellValue("学校（公章）：");

        row3.createCell(3).setCellValue("联系电话");

    }

}

Controller

import java.math.BigDecimal;
import java.net.URLEncoder;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.gemantic.cnooc.handler.StudentHandler;
import com.gemantic.cnooc.model.StudentExcel;
import com.google.common.collect.Lists;

import io.swagger.annotations.ApiOperation;
import lombok.extern.slf4j.Slf4j;

/**
 * @Auther: Wangtianming
 * @Date: 2021/10/27 14:35
 * @Description:
 */
@RestController
@RequestMapping(path = "/student")
@Slf4j
public class StudentExcelController {

    @PostMapping(value = "exportExcel", headers = "Accept=application/octet-stream")
    @ApiOperation(value = "导出excel", notes = "导出excel")
    public void exportExcel(HttpServletResponse response) throws Exception {

        Map<String, List<StudentExcel>> collect = getList();

        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");

        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        String fileName = URLEncoder.encode("学生成绩", "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xls");
        // 内容样式策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        // 垂直居中,水平居中
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);

        // 设置 自动换行
        contentWriteCellStyle.setWrapped(true);
        // 字体策略
        WriteFont contentWriteFont = new WriteFont();
        // 字体大小
        contentWriteFont.setFontHeightInPoints((short)12);
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        // 头策略使用默认
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // excel如需下载到本地,只需要将response.getOutputStream()换成File即可(注释掉以上response代码)
        ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(), StudentExcel.class).build();
        int num = 0;
        for (String key : collect.keySet()) {
            String sheetName = key;
            String headerName = sheetName + "成绩单";
            WriteSheet mainSheet = EasyExcel.writerSheet(num, sheetName).head(StudentExcel.class)
                .registerWriteHandler(new StudentHandler(sheetName, num, headerName))
                .registerWriteHandler(new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle))
                .useDefaultStyle(true).relativeHeadRowIndex(2).build();
            excelWriter.write(collect.get(key), mainSheet);
            num = num + 1;
        }
        excelWriter.finish();
    }

    public Map<String, List<StudentExcel>> getList() {
        List<StudentExcel> list = Lists.newArrayList();
        StudentExcel a = new StudentExcel();
        a.setStudentName("张三");
        a.setStudentClass("五年级");
        a.setMath(new BigDecimal(99.5));
        a.setLanguage(new BigDecimal(99.4));
        a.setEnglish(new BigDecimal(99.3));

        StudentExcel b = new StudentExcel();
        b.setStudentName("李四");
        b.setStudentClass("五年级");
        b.setMath(new BigDecimal(96.5));
        b.setLanguage(new BigDecimal(96.4));
        b.setEnglish(new BigDecimal(96.3));

        StudentExcel c = new StudentExcel();
        c.setStudentName("王五");
        c.setStudentClass("四年级");
        c.setMath(new BigDecimal(96.5));
        c.setLanguage(new BigDecimal(96.4));
        c.setEnglish(new BigDecimal(96.3));

        list.add(a);
        list.add(b);
        list.add(c);

        Map<String, List<StudentExcel>> map =
            list.stream().collect(Collectors.groupingBy(StudentExcel::getStudentClass));
        Map<String, List<StudentExcel>> sortMap = new LinkedHashMap(map.size());
        map.entrySet().stream().sorted(Map.Entry.<String, List<StudentExcel>>comparingByKey().reversed())
            .forEachOrdered(e -> sortMap.put(e.getKey(), e.getValue()));
        return sortMap;
    }
}

导出结果