当前位置：首页 > article >正文

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

article 2025/3/22 12:34:04

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

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

application.yml配置文件：

server:
  port: 9009

springboot启动类代码：

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication

public 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 {

    @Autowired
    private 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 {
    @Override
    public 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导出excel
        EasyExcel.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验证效果：