Java EasyExcel 带表头导出

maven

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.2.7</version>
</dependency>

model

import java.math.BigDecimal;
import java.util.Date;

import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.fasterxml.jackson.annotation.JsonFormat;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

/**
 * @Description:
 * @Auther: Wangtianming
 * @Date: 2022/4/19 23:09
 */
@Data
@NoArgsConstructor
@AllArgsConstructor
public class StudentExcelDto {
    @ExcelProperty(value = "序号",index = 0)
    private Long id;

    @ExcelProperty(value = "姓名",index = 1)
    private String studentName;

    @ExcelProperty(value = "班级",index = 2)
    @ColumnWidth(20)
    private String studentClass;

    @ExcelProperty(value = "住址",index = 3)
    private String address;

    @ExcelProperty(value = "入学日期",index = 4)
    @JsonFormat(pattern = "yyyy-MM-dd")
    private Date admissionDate;

    @ExcelProperty(value = "分数",index = 5)
    private BigDecimal score;

    @ExcelIgnore//导出忽略此字段
    private int gender;

    @ExcelProperty(value = "姓别",index = 6)
    private String genderName;

}

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 {

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

    @Override

    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {

        Workbook workbook = writeWorkbookHolder.getWorkbook();
        Sheet sheet = workbook.getSheetAt(0);
        // Row row1 = sheet.createRow(0);
        // row1.setHeight((short) 500);
        // Cell cell = row1.createCell(0);
        //
        // //设置单元格内容
        // cell.setCellValue("附件2");

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

        row2.setHeight((short)800);

        Cell cell1 = row2.createCell(0);

        cell1.setCellValue("学生信息明细");

        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, 6));

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

        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.Date;
import java.util.List;

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.support.ExcelTypeEnum;
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.StudentExcelDto;
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 {

        List<StudentExcelDto> pageList = Lists.newArrayList();
        StudentExcelDto a = new StudentExcelDto();
        a.setId(1l);
        a.setStudentName("小王");
        a.setStudentClass("五年纪");
        a.setAddress("北京小学");
        a.setAdmissionDate(new Date());
        a.setScore(new BigDecimal(100));
        a.setGender(1);
        pageList.add(a);
        pageList.forEach(b -> {
            if (a.getGender() == 1) {
                a.setGenderName("男");
            } else {
                a.setGenderName("女");
            }
        });
        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代码)
        try {
            EasyExcel.write(response.getOutputStream(), StudentExcelDto.class)
                // 设置输出excel版本,不设置默认为xlsx
                .excelType(ExcelTypeEnum.XLS).head(StudentExcelDto.class)
                // 设置拦截器或自定义样式
                .registerWriteHandler(new StudentHandler())
                .registerWriteHandler(new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle))
                .sheet("学生信息明细")
                // 设置默认样式及写入头信息开始的行数
                .useDefaultStyle(true).relativeHeadRowIndex(2)
                // 这里的addsumColomn方法是个添加合计的方法,可删除
                .doWrite(pageList);
        } catch (Exception e) {
            log.error("导出详情excel 出现错误", e);
        }
    }

}

导出结果