EasyExcel根据模板生成excel文件

本文介绍用的数据格式是map类型的数，下载的效果如下图

填充的模板（ list类型的写法{.name} ）

 

最终效果

代码如下，直接用流返回

private static void extracted(HttpServletResponse response,List<HashMap<String, Long>> maps) throws FileNotFoundException {
File templateFileName
        = ResourceUtils.getFile("classpath:fill/exceltemplate/模板表.xls");

String fileNamedownload = "生成表" + System.currentTimeMillis() + ".xls";

     try(ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).withTemplate(templateFileName).build()){
         if(maps!=null){
             maps.forEach(map->{
                WriteSheet writeSheet1 = EasyExcel.writerSheet("报表").build();
                excelWriter.fill(map, writeSheet1);
           });
         }
       excelWriter.finish();
 response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
 String fileName1= URLEncoder.encode(fileNamedownload,"UTF-8").replaceAll("\\+","%20");
 response.setHeader("Content-disposition","attachment;filename*=utf-8''"+fileName1);
 } catch (IOException e) {
     throw new RuntimeException(e);
 }
}

 其他人的写法

package com.example.fengqing.ExcelTemplates.controller;

import cn.hutool.core.date.DateUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillConfig;
import com.alibaba.excel.write.metadata.fill.FillWrapper;
import com.example.fengqing.ExcelTemplates.ExcelGenerator;
import com.example.fengqing.ExcelTemplates.entity.HistoryData;
import lombok.extern.slf4j.Slf4j;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import javax.servlet.http.HttpServletResponse;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author FengQing
 * @program fengqing
 * @description
 * @date 2024/03/20
 */
@Slf4j
@RestController
@RequestMapping("/template")
public class TemplatesController {

    /**
     * 指定模板下载
     * @param response
     * @throws IOException
     */
    @PostMapping("/downFrozenTemplate")
    public void downFrozenTemplate(HttpServletResponse response) throws IOException {
        try {
            String reportDate = DateUtil.date().toString("yyyy年MM月dd日");
　　　　　　　// 获取resources/templates目录下的模板文件
            InputStream templateStream = TemplatesController.class.getResourceAsStream("/templates/模板.xlsx");
            if (templateStream == null) {
                throw new FileNotFoundException("未找到模板文件");
            }
            String resultFileName = "生成的文件.xlsx";
            // 生成目标文件
            ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).withTemplate(templateStream).build();
            WriteSheet writeSheet = EasyExcel.writerSheet().build();
            // 每次都会重新生成新的一行，而不是使用下面的空行
            FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
            // 替换第一种占位符
            Map<String, Object> map = new HashMap<>();
            map.put("theUser", "测试有限公司");
            map.put("reportDate", reportDate);
            excelWriter.fill(map, writeSheet);
            // 第二种占位符替换，这里定义了 hisData
            excelWriter.fill(new FillWrapper("thsData", hisData()), fillConfig, writeSheet);
            excelWriter.finish();
            // 设置响应头
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            response.setHeader("Content-Disposition", "attachment; filename=\"" + resultFileName + "\"");
            // 关闭模板流
            templateStream.close();
        } catch (FileNotFoundException e) {
            // 处理文件未找到异常
            response.setStatus(HttpServletResponse.SC_NOT_FOUND);
            // 返回适当的错误消息
            response.getWriter().write("未找到模板文件");
        } catch (Exception e) {
            // 处理其他异常
            response.setStatus(HttpServletResponse.SC_INTERNAL_SERVER_ERROR);
            // 返回适当的错误消息
            response.getWriter().write("内部服务器错误");
        }
    }

    private static List<HistoryData> hisData(){
        List<HistoryData> resList = new ArrayList<>();
        String today = DateUtil.now();
        String yesterday = DateUtil.yesterday().toString();
        HistoryData yesData = HistoryData.builder()
                .time(today)
                .temp(34.211)
                .press(1.222)
                .insFlow(34.211)
                .accFlow(233.125)
                .insHeat(20.532)
                .accHeat(112.562)
                .build();
        HistoryData nowData = HistoryData.builder()
                .time(yesterday)
                .temp(34.211)
                .press(1.222)
                .insFlow(34.211)
                .accFlow(233.125)
                .insHeat(20.532)
                .accHeat(112.562)
                .build();
        resList.add(yesData);
        resList.add(nowData);
        return resList;
    }

}