使用excel.EasyExcel实现导出有自定义样式模板的excel数据文件，粘贴即用！！！

客户要求导出的excel文件是有好看格式的，当然本文举例模板文件比较简单，内容丰富的模板可以自行设置，话不多说，第一步设置一个"好看"的excel文件模板

上面要注意的地方是{.变量名} ，这里的变量名对应的就是导出数据对象里面的变量名，一定要有"."
对象属性，不写"."就是填充单个属性，写了就是填充列表数据
先看代码目录结构

application.yml配置文件：

server:port: 9009

springboot启动类代码：

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;@SpringBootApplicationpublic class BootstrapApp {public static void main(String[] args) {SpringApplication.run(BootstrapApp.class, args);}
}

数据对象代码：

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Getter;
import lombok.Setter;@Getter
@Setter
public class User {private int id;@ExcelProperty(value = "姓名")private String name;@ExcelProperty(value = "年龄")private String age;@ExcelProperty(value = "兴趣爱好")private String love;@ExcelProperty(value = "备注")private String remark;
}

然后controller代码：
 

import com.operation.excel.service.DoExcelService;
import lombok.extern.slf4j.Slf4j;
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 javax.servlet.http.HttpServletResponse;@Slf4j
@RestController
@RequestMapping("/api")
public class ExcelController {@Autowiredprivate DoExcelService doExcelService;@GetMapping("/export")public void export(HttpServletResponse response) throws Exception {log.info("开始导出自定义样式excel");doExcelService.export(response);}
}

然后是service代码

import javax.servlet.http.HttpServletResponse;public interface DoExcelService {void export(HttpServletResponse response) throws Exception;
}

service对应实现类代码：

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.operation.excel.dto.User;
import com.operation.excel.service.DoExcelService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.core.io.ClassPathResource;
import org.springframework.stereotype.Service;import javax.servlet.http.HttpServletResponse;
import java.io.InputStream;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;@Service
@Slf4j
public class DoExcelServiceImpl implements DoExcelService {@Overridepublic void export(HttpServletResponse response) throws Exception {// 1：设置响应参数setResponseHeader(response, "user_");// 2：获取待导出的数据合集List<User> productCoreParamList = getData();// 3：获取模板流InputStream templateStream = new ClassPathResource("template/export-user.xlsx").getInputStream();// 4：写入response导出excelEasyExcel.write(response.getOutputStream()).registerWriteHandler(setStyle()).withTemplate(templateStream).sheet().doFill(productCoreParamList);}//这里设置响应头的部分参数private void setResponseHeader(HttpServletResponse response, String fileName) throws Exception {try {// 修正文件扩展名为xlsx以匹配实际格式String fileNameStr = fileName + LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyyMMddHHmmss")) + ".xlsx";String encodedFileName = URLEncoder.encode(fileNameStr, StandardCharsets.UTF_8.toString());response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");response.setCharacterEncoding("UTF-8");response.setHeader("Content-Disposition", "attachment;filename=" + encodedFileName);} catch (Exception e) {log.error("set response header error", e);throw new Exception("设置响应头失败: " + e.getMessage());}}// 这个方法是模拟需要导出的数据（实际是从数据库获取）private List<User> getData() {List<User> students = new ArrayList<>();for (int i = 0; i < 10; i++) {User student = new User();student.setId(i);student.setAge("A" + i);student.setName("B" + i);student.setLove(UUID.randomUUID().toString());student.setRemark(UUID.randomUUID().toString());students.add(student);}return students;}//这这个方法是设置填充的数据内容字体样式，也可以不设置private HorizontalCellStyleStrategy setStyle(){// 定义样式：自动换行WriteCellStyle contentWriteCellStyle = new WriteCellStyle();contentWriteCellStyle.setWrapped(true); // 关键：开启自动换行WriteFont writeFont = new WriteFont();writeFont.setFontName("Microsoft YaHei"); // 字体writeFont.setFontHeightInPoints((short) 12);// 字体大小contentWriteCellStyle.setWriteFont(writeFont);// 注册样式策略（全局生效）HorizontalCellStyleStrategy styleStrategy = new HorizontalCellStyleStrategy(null, // 头样式（默认）contentWriteCellStyle // 内容样式（自动换行）);return styleStrategy;}

postman验证效果：
 

response直接展示会乱码，所以选择save response 然后选择保存为文件也就是 save file
查看保存的文件：
 

总结：就是先准备一个好看的模板(设置变量)，然后读模版流，然后向模板流中的sheet工作表填充数据，最后写入response前端获取