EasyExcel复杂表头的导出样式自定义（字体，大小，主副标题不同底色，行高）

效果预览

设置表头的行高有0.1的误差，按理说我设置的应该是17.7，但是实际出来excel中是17.8

准备工作

• 项目结构
  

• 导入easyExcel的依赖

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

具体代码实现

• controller层

为了方便，我把需要导的包也放进来了

package com.easyexcel.test.controller;

import com.easyexcel.test.service.TestService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletResponse;

@RestController
public class TestController {

    @Autowired
    private TestService testService;
    @GetMapping("/download")
    public void windowsClientDownload(HttpServletResponse response, Long procedureId) {
        testService.doDownload(response);
    }
}

• service层

ipackage com.easyexcel.test.service;

import javax.servlet.http.HttpServletResponse;

public interface TestService {
    void doDownload(HttpServletResponse response);
}

• service实现类

package com.easyexcel.test.service.impl;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.util.ListUtils;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.easyexcel.test.service.TestService;
import com.easyexcel.test.strategy.CellRowHeightStyleStrategy;
import com.easyexcel.test.strategy.CellStyleStrategy;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.Resource;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;
import java.util.concurrent.atomic.AtomicInteger;

@Service
public class TestServiceImpl implements TestService {

    @Override
    public void doDownload(HttpServletResponse response) {
        //设置响应头
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        //中文文件名编码
        String fileName = null;
        try {
            //中文文件名编码要用URLEncoder.encode编码
            fileName = URLEncoder.encode("模板", "UTF-8").replaceAll("\\+", "%20");
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        response.setHeader("Content-Disposition", "attachment;fileName=" + fileName + ".xlsx");
        try {
            //主标题和副标题在excel中分别是是第0和第1行
            List<Integer> columnIndexes = Arrays.asList(0,1);
            //自定义标题和内容策略(具体定义在下文)
            CellStyleStrategy cellStyleStrategy =
                    new CellStyleStrategy(columnIndexes,new WriteCellStyle(), new WriteCellStyle());

            ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream())
                    .registerWriteHandler(new CellRowHeightStyleStrategy())   //设置行高的策略
                    .registerWriteHandler(cellStyleStrategy)        //设置表头和内容的策略
                    .build();

            //填入数据
            writeData(excelWriter);
            // 千万别忘记关闭流
            excelWriter.finish();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }
    /**
     * 写入数据
     * @param excelWriter excelWriter
     */
    private void writeData(ExcelWriter excelWriter){

        WriteSheet writeSheet = new WriteSheet();
        //设置写到第几个sheet
        writeSheet.setSheetNo(0);
        writeSheet.setSheetName("测试");
        //造数据
        List<List<Object>> list = ListUtils.newArrayList();
        for (int i = 0; i < 10; i++) {
            List<Object> data = ListUtils.newArrayList();
            data.add("字符串" + i);
            data.add(new Date());
            data.add(0.56);
            list.add(data);
        }
        //设置表头
        List<List<String>> headList = new ArrayList<>();
        String name = "********表";
        headList.add(Arrays.asList(name,"序号"));
        headList.add(Arrays.asList(name,"名称"));
        for (int i = 1; i <3 ; i++) {
            headList.add(Arrays.asList(name,"单位"+i));
        }
        writeSheet.setHead(headList);

        //（设置数据）
        //第一列序号从1开始增加
        AtomicInteger orderNumber = new AtomicInteger(1);
        ArrayList<List<Object>> dataList = new ArrayList<>();
        for (int i = 0; i < 10; i++) {
            List<Object> data = ListUtils.newArrayList();
            data.add(String.valueOf(orderNumber.getAndIncrement()));
            data.add("名称" + i);
            data.add("单元"+i);
            data.add(0.56);
            dataList.add(data);
        }
        excelWriter.write(dataList, writeSheet);
    }
}

• 设置表头和填充内容的样式

package com.easyexcel.test.strategy;

import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.handler.context.CellWriteHandlerContext;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.IndexedColors;

import java.util.List;

/**
 - 设置表头和填充内容的样式
 */
public class CellStyleStrategy extends HorizontalCellStyleStrategy {

    private final WriteCellStyle headWriteCellStyle;
    private final WriteCellStyle contentWriteCellStyle;

    /**
     * 操作列
     */
    private final List<Integer> columnIndexes;

    public CellStyleStrategy(List<Integer> columnIndexes,WriteCellStyle headWriteCellStyle, WriteCellStyle contentWriteCellStyle) {
        this.columnIndexes = columnIndexes;
        this.headWriteCellStyle = headWriteCellStyle;
        this.contentWriteCellStyle = contentWriteCellStyle;
    }

	//设置头样式
    @Override
    protected void setHeadCellStyle( CellWriteHandlerContext context) {
        // 获取字体实例
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontName("宋体");
        if (columnIndexes.get(0).equals(context.getRowIndex())) {
            headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
            headWriteFont.setFontHeightInPoints((short) 14);
            headWriteFont.setBold(true);
        }else{
            headWriteCellStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
            headWriteFont.setFontHeightInPoints((short) 11);
            headWriteFont.setBold(false);
        }
        headWriteCellStyle.setWriteFont(headWriteFont);
        if (stopProcessing(context)) {
            return;
        }
        WriteCellData<?> cellData = context.getFirstCellData();
        WriteCellStyle.merge(headWriteCellStyle, cellData.getOrCreateStyle());
    }

	//设置填充数据样式
    @Override
    protected void setContentCellStyle(CellWriteHandlerContext context) {
        WriteFont contentWriteFont = new WriteFont();
        contentWriteFont.setFontName("宋体");
        contentWriteFont.setFontHeightInPoints((short) 11);
        //设置数据填充后的实线边框
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
        WriteCellData<?> cellData = context.getFirstCellData();
        WriteCellStyle.merge(contentWriteCellStyle, cellData.getOrCreateStyle());
    }
}

• 设置表头行高策略

如果excel需要显示行高为15，那这里就要设置为15*20=300

package com.easyexcel.test.strategy;

import com.alibaba.excel.write.style.row.AbstractRowHeightStyleStrategy;
import org.apache.poi.ss.usermodel.Row;

/**
 * 设置表头的自动调整行高策略
 */
public class CellRowHeightStyleStrategy extends AbstractRowHeightStyleStrategy {

    @Override
    protected void setHeadColumnHeight(Row row, int relativeRowIndex) {
        //设置主标题行高为17.7
        if(relativeRowIndex == 0){
        	//如果excel需要显示行高为15，那这里就要设置为15*20=300
            row.setHeight((short) (354));
        }
    }

    @Override
    protected void setContentColumnHeight(Row row, int relativeRowIndex) {
    }
}

访问

启动项目后访问 http://localhost:8411/download 开始下载文件 （8411是我自己在application.yml中设置的项目启动端口）

写在最后

如果有什么不对的可以评论交流