EASYEXCEL实现自定义表头demo

1、在maven加引入插件依赖

 <!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
 <dependency>
	<groupId>com.alibaba</groupId>
	<artifactId>easyexcel</artifactId>
	<version>2.1.1</version>
 </dependency>

 2、创建对应的映射实体

import java.time.LocalDate;

import org.springframework.format.annotation.DateTimeFormat;

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

import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.experimental.Accessors;

@NoArgsConstructor
@Accessors(chain = true)
@ContentRowHeight(40) // 设置内容高度
@HeadRowHeight(50)// 设置表头高度
@Data
public class ExportExaminationVO {

	
	@ExcelProperty(value = "序号",index = 0) // 设置表头字段名
	@ColumnWidth(8)// 设置行宽
	private Integer idx;
	
	@ExcelProperty(value = "姓名",index = 1)
	@ColumnWidth(20)
	private String name;
	
	@ExcelProperty(value="性别",index = 2)
	@ColumnWidth(8)
	private String genderName;

	@ExcelProperty(value="J号",index = 3)
	@ColumnWidth(15)
	private String policeNo;
	
	@ExcelProperty(value="出生年月",index = 4)
	@JsonFormat(pattern = "yyyy-MM", timezone = "GMT+8")
    @DateTimeFormat(pattern = "yyyy-MM")
	@ColumnWidth(20)
	private LocalDate birthday;
	
	@ExcelProperty(value = "单位名称",index = 5)
	@ColumnWidth(50)
    private String bzDwCodeName;

	@ExcelProperty(value = "人员类别",index = 6)
	@ColumnWidth(30)
    private String assessTypeName;
}

 3、在controller层写请求接口

import java.net.URLEncoder;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
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.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.szpsbnpt.hrs.annualappraisal.facade.AssessExaminationApprovalFacade;
import com.szpsbnpt.hrs.annualappraisal.util.MonthSheetWriteHandler;
import com.szpsbnpt.hrs.annualappraisal.web.vo.ExportExaminationVO;

import io.swagger.annotations.Api;
import io.swagger.annotations.ApiImplicitParam;
import io.swagger.annotations.ApiImplicitParams;
import io.swagger.annotations.ApiOperation;
import jade.core.dto.TableTagBean;
import jade.core.util.BeanHelper;

@Api(tags = "审定")
@RestController
@RequestMapping("/test/assessExaminationApproval")
public class TestController {
	

	@Autowired
	private AssessExaminationApprovalFacade assessExaminationApprovalFacade;
	
	@ApiOperation(value = "列表导出")
    @ApiImplicitParams({
    @ApiImplicitParam(paramType = "query", name = "query", value = "姓名/J号", required = false, dataType = "String")
    })
    @GetMapping(value = "/testList")
    public void exportExaminationTest(HttpServletRequest request, HttpServletResponse response) throws Exception {
        
        TableTagBean ttb = TableTagBean.getFromExt(request);
        StringBuffer bigTitle = new StringBuffer("审定名单");

        List<ExportExaminationVO> list = BeanHelper.copyProperties(assessExaminationApprovalFacade.findExaminationApproval(ttb), ExportExaminationVO.class);

        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        String fileName = URLEncoder.encode(bigTitle.toString(), "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");

        // 头的策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        // 默认设置为水平居中
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
        
        EasyExcel.write(response.getOutputStream(), ExportExaminationVO.class)
        //设置输出excel版本,不设置默认为xlsx
//        .excelType(ExcelTypeEnum.XLS).head(ExportExaminationVO.class)
        //设置拦截器或自定义样式
        .registerWriteHandler(new MonthSheetWriteHandler())
        .registerWriteHandler(new HorizontalCellStyleStrategy(headWriteCellStyle,contentWriteCellStyle))
        .sheet(bigTitle.toString())
        //设置默认样式及写入头信息开始的行数
        .useDefaultStyle(true).relativeHeadRowIndex(3)
        .doWrite(list);
    }
}

4、在拦截器中对表头的样式进行自定义的覆盖

import java.util.Date;

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;

import jade.core.util.DateUtil;

/**
 * 表头设置拦截器
 *
 */
public class MonthSheetWriteHandler 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) 900);
        Cell cell1 = row1.createCell(0);
        cell1.setCellValue("审定名单");
        // 设置水平、垂直居中
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 设置字体样式，bold加粗、fontHeight设置字体大小
        Font font = workbook.createFont();
        font.setBold(true);
        font.setFontHeight((short) 500);
        cellStyle.setFont(font);
        cell1.setCellStyle(cellStyle);
        // CellRangeAddress(1, 1, 0, 17) 用于合并表格，其中四个参数含义起始行号、终止行号、起始列号、终止列号
        sheet.addMergedRegionUnsafe(new CellRangeAddress(0, 0, 0, 10));
        //设置填表日期
        Row row2 = sheet.createRow(1);
        row2.setHeight((short) 500);
        // 在当前行的第10+1列处展示（时间：2021-05-31）
        row2.createCell(10).setCellValue("时间：" + DateUtil.format(new Date(), DateUtil.DATE_PATTERN_DEFAULT));
 
    }
}

最终生成的表格如下