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;@RestControllerpublic class TestController {@Autowiredprivate 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 .URLEncoder;import java.util.ArrayList;import java.util.Arrays;import java.util.Date;import java.util.List;import java.util.concurrent.atomic.AtomicInteger;@Servicepublic class TestServiceImpl implements TestService {@Overridepublic 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();//设置写到第几个sheetwriteSheet.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;}//设置头样式@Overrideprotected 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());}//设置填充数据样式@Overrideprotected 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 {@Overrideprotected void setHeadColumnHeight(Row row, int relativeRowIndex) {//设置主标题行高为17.7if(relativeRowIndex == 0){//如果excel需要显示行高为15，那这里就要设置为15*20=300row.setHeight((short) (354));}}@Overrideprotected void setContentColumnHeight(Row row, int relativeRowIndex) {}}