POI EasyExcel 自定义行列样式 字体样式 宽高 动态头 实时头写入

周末好 有什么看不懂 可留言 如果我会的话 我会解答 如果帮助到你了 一键三联呦 爱你 笔芯

EasyExcel 官方文档：

效果图：

Model : History2014ExportModel

package com.proapplysvr.model.history;import java.util.Date;import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.BaseRowModel;//@Data
public class History2014ExportModel {
    @ExcelProperty(value = "xx1", index = 0)private String PERSON_JSZG_XZG;@ExcelProperty(value = "xx2", index = 1)private String PERSON_GWMC;@ExcelProperty(value = "xx3", index = 2)private String sbdw;public String getPERSON_JSZG_XZG() {
    return PERSON_JSZG_XZG;}public void setPERSON_JSZG_XZG(String PERSON_JSZG_XZG) {
    this.PERSON_JSZG_XZG = PERSON_JSZG_XZG;}public String getPERSON_GWMC() {
    return PERSON_GWMC;}public void setPERSON_GWMC(String PERSON_GWMC) {
    this.PERSON_GWMC = PERSON_GWMC;}public String getSbdw() {
    return sbdw;}public void setSbdw(String sbdw) {
    this.sbdw = sbdw;}
}

主要设计样式代码 History2014ExportCellWriteHandler

package com.proapplysvr.model.history;import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.ss.usermodel.*;import javax.swing.*;
import java.util.List;/*** @description:* @author: maofangfang* @time: 2020/9/20 9:54*/
public class History2014ExportCellWriteHandler implements CellWriteHandler {
    @Overridepublic void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {
    }@Overridepublic void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
    }@Overridepublic void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
    Workbook workbook = writeSheetHolder.getSheet().getWorkbook();CellStyle cellStyle = workbook.createCellStyle();Font font = workbook.createFont();if (cell.getRowIndex() == 0) {
    font.setFontHeightInPoints((short) 18);font.setFontName("宋体");font.setBold(true);cellStyle.setFont(font);cellStyle.setAlignment(HorizontalAlignment.CENTER);cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//设置 自动换行cellStyle.setWrapText(true);Row row = cell.getRow();row.setHeightInPoints(30);}if (cell.getRowIndex() == 1) {
    font.setFontHeightInPoints((short) 11);font.setFontName("宋体");font.setBold(true);cellStyle.setFont(font);cellStyle.setAlignment(HorizontalAlignment.LEFT);cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//设置 自动换行cellStyle.setWrapText(true);Row row = cell.getRow();row.setHeightInPoints(26);}if (cell.getRowIndex() == 2) {
    //设置字体大小font.setFontHeightInPoints((short) 11);//设置字体样式font.setFontName("宋体");//设置加粗font.setBold(true);//设置字体样式cellStyle.setFont(font);//设置 文字左右居中 【水平居中需要使用以下两行】cellStyle.setAlignment(HorizontalAlignment.CENTER);//设置文字居中 上下居中cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//设置 自动换行cellStyle.setWrapText(true);//获取行Row row = cell.getRow();//设计行高row.setHeightInPoints(31);}cell.setCellStyle(cellStyle);}
}

    public void exportExcel(Map<String, Object> map, HttpServletResponse response) throws IOException {
    Map<String, Object> params = this.params(map);//参数Map<String, Object> paramMap = (Map<String, Object>) params.get("paramMap");//结果集 List<Map<String, Object>> list = oracle2014Mapper.selectList(paramMap);// 头的策略WriteCellStyle headWriteCellStyle = new WriteCellStyle();// 内容的策略WriteCellStyle contentWriteCellStyle = new WriteCellStyle();// 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现HorizontalCellStyleStrategy horizontalCellStyleStrategy =new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);response.setContentType("application/vnd.ms-excel");response.setCharacterEncoding("utf-8");// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系String fileName = URLEncoder.encode("测试", "UTF-8").replaceAll("\\+", "%20");response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");List<History2014ExportModel> personnelPunchModels = list.stream().map(map1 -> BeanUtil.mapToBean(map1, History2014ExportModel.class)).collect(Collectors.toList());EasyExcel.write(response.getOutputStream(), History2014ExportModel.class).registerWriteHandler(horizontalCellStyleStrategy).registerWriteHandler(new History2014ExportCellWriteHandler()).head(head()) .head(head()).sheet("模板").doWrite(personnelPunchModels);}private List<List<String>> head() {
    List<List<String>> list = new ArrayList<List<String>>();List<String> head0 = new ArrayList<String>();head0.add("标题");head0.add("二标题");head0.add("xx");List<String> head1 = new ArrayList<String>();head1.add("标题");head1.add("二标题");head1.add("xx2");List<String> head2 = new ArrayList<String>();head2.add("标题");head2.add("二标题");head2.add("xx3");list.add(head0);list.add(head1);list.add(head2);return list;}

注意：