默认效果图
![](https://i-blog.csdnimg.cn/blog_migrate/7316722147ba4ed6db8cb9cd3e91321e.png)
设置背景色
![](https://i-blog.csdnimg.cn/blog_migrate/84679059beee2d4d858b531aefede0f8.png)
1.导入依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.2.0</version>
</dependency>
2.Excel/Load代码
import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.util.ListUtils;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.WriteTable;
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.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;
@RestController
@RequestMapping("excel")
public class MultistageHeaderExcelController {
@GetMapping("load")
public void downLoad(HttpServletResponse response) throws IOException {
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
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");
ExcelWriter writer = EasyExcelFactory.write(response.getOutputStream())
// 核心代码:表头和正文的样式在此
.registerWriteHandler(setConfigure()).build();
// 动态添加表头,适用一些表头动态变化的场景
WriteSheet sheet1 = new WriteSheet();
sheet1.setSheetName("sheet001");
sheet1.setSheetNo(0);
// 创建一个表格,用于 Sheet 中使用
WriteTable table = new WriteTable();
table.setTableNo(1);
// 核心代码:设置表头
table.setHead(head());
// 写数据
writer.write(contentData(), sheet1, table);
writer.finish();
}
private static List<List<String>> head() {
List<List<String>> headTitles = ListUtils.newArrayList();
String empty = " ";
//表头可以根据实际情况进行修改
//第一列,1/2/3行
headTitles.add(ListUtils.newArrayList( "测点编号"));
//第二列,1/2/3行
ArrayList<String> foodList = ListUtils.newArrayList("渗压水位 (m)", "日期", "库水位 (m)");
ArrayList<String> mealList = ListUtils.newArrayList("历史最高渗压水位", "历史最低渗压水位");
// 根据实际需要,决定要渲染多少列
mealList.forEach(meal -> {
foodList.forEach(food -> {
headTitles.add(ListUtils.newArrayList(meal, food));
});
});
headTitles.add(ListUtils.newArrayList("变化量 (m)"));
return headTitles;
}
private static List<List<Object>> contentData() {
List<List<Object>> contentList = ListUtils.newArrayList();
//这里一个List<Object>才代表一行数据,需要映射成每行数据填充,横向填充(把实体数据的字段设置成一个List<Object>)
//数据根据实际获取进行填充
contentList.add(ListUtils.newArrayList("0001", "190.76", "2023-05-10", "null", "6.78", "2023-02-15","null","183.97"));
contentList.add(ListUtils.newArrayList("0001", "190.76", "2023-05-10", "null", "6.78", "2023-02-15","null","183.97"));
return contentList;
}
//配置字体,表头背景等
private static HorizontalCellStyleStrategy setConfigure() {
// 头的策略
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
// 背景色
headWriteCellStyle.setFillForegroundColor(IndexedColors.YELLOW1.getIndex()); // 黄色背景
WriteFont headWriteFont = new WriteFont();
// 加粗
headWriteFont.setBold(true);
// headWriteFont.setFontHeightInPoints((short) 14); // 设置行高,不重要
headWriteCellStyle.setWriteFont(headWriteFont);
// 内容的策略
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
// 字体策略
WriteFont contentWriteFont = new WriteFont();
// 字体大小
// contentWriteFont.setFontHeightInPoints((short) 14);
contentWriteCellStyle.setWriteFont(contentWriteFont);
//边框
//导出数据垂直居中
contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
//导出数据水平居中
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
contentWriteCellStyle.setBorderLeft(BorderStyle.NONE);
contentWriteCellStyle.setBorderTop(BorderStyle.NONE);
contentWriteCellStyle.setBorderRight(BorderStyle.NONE);
contentWriteCellStyle.setBorderBottom(BorderStyle.NONE);
//设置 自动换行
contentWriteCellStyle.setWrapped(true);
//设置
// 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
}
}