springboot整合jett实现模板excel数据导出

一 jett的介绍

1.1 介绍

jett是使用Excel电子表格模板快速创建Excel电子表格报告的工具。

1.2 使用方式

1.使用${}来标识我们需要填充的数据

2.使用<jt></jt> 遍历一个集合，我们对集合的每个元素取名为e ,每个元素的又可以使用${e.属性}来进行获取。

1.后端代码

 public  void test(){
        List<Student> studentsList=new ArrayList<>();
        //  List<Map> studentsList=new ArrayList<>();
        for(int k=0;k<5;k++){
            studentsList.add(new Student(k,"张三"+k,(k+1)*2));
           // resultMap.put("dataList",studentsList);
        }
    }

2.模板图 

3.导出数据：

 二 jett的案例操作

2.1 模板案例

2.2 代码实现

1.工程结构

 2.代码

package com.ljf.jett.demo.controller;

import com.ljf.jett.demo.model.Student;
import com.ljf.jett.demo.util.DateUtils;
import net.sf.jett.transform.ExcelTransformer;
import org.apache.poi.ss.usermodel.*;
import org.springframework.web.bind.annotation.CrossOrigin;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.*;

/**
 * @ClassName: SkMonthsReportController
 * @Description: TODO  导出测试类
 * @Author: liujianfu
 * @Date: 2022/11/08 10:22:23 
 * @Version: V1.0
 **/
@RestController
@CrossOrigin
@RequestMapping("/test")
public class SkMonthsReportController {
    @RequestMapping("/export")
    public void  exportDataForExcel(HttpServletResponse response) {
        Map<String, Object> resultMap = new HashMap<String, Object>();
        resultMap.put("titleName","邵寨煤业公司选煤厂调度日报表");
        resultMap.put("reportDate", DateUtils.dateToStr(new Date(),"yyyy-MM-dd"));
        //中间列表
        //当班量
        Map<String,Object> currentDutyMap=new HashMap<>();
        currentDutyMap.put("rxCoal",7664);
        currentDutyMap.put("blockCoal",1576);
        currentDutyMap.put("wyCoal",0);
        currentDutyMap.put("wjCoal",3102);
        currentDutyMap.put("zongCoal",693);
        currentDutyMap.put("dryCoal",598);
        currentDutyMap.put("wetCoal",1);
        currentDutyMap.put("stone",0);
        currentDutyMap.put("mark","");
        resultMap.put("currentDutyMap",currentDutyMap);
        //回收率
        Map<String,Object> recycleMap=new HashMap<>();
        recycleMap.put("rxCoal",7664);
        recycleMap.put("blockCoal",1576);
        recycleMap.put("wyCoal",0);
        recycleMap.put("wjCoal",3102);
        recycleMap.put("zongCoal",693);
        recycleMap.put("dryCoal",598);
        recycleMap.put("wetCoal",1);
        recycleMap.put("stone",0);
        recycleMap.put("mark","精煤产率61.04");
        resultMap.put("recycleMap",recycleMap);
        //月类量
        Map<String,Object> monthMap=new HashMap<>();
        monthMap.put("rxCoal",7664);
        monthMap.put("blockCoal",1576);
        monthMap.put("wyCoal",0);
        monthMap.put("wjCoal",3102);
        monthMap.put("zongCoal",693);
        monthMap.put("dryCoal",598);
        monthMap.put("wetCoal",1);
        monthMap.put("stone",0);
        monthMap.put("mark","");
        resultMap.put("monthMap",monthMap);
        //年累量
        Map<String,Object> yearMap=new HashMap<>();
        yearMap.put("rxCoal",7664);
        yearMap.put("blockCoal",1576);
        yearMap.put("wyCoal",0);
        yearMap.put("wjCoal",3102);
        yearMap.put("zongCoal",693);
        yearMap.put("dryCoal",598);
        yearMap.put("wetCoal",1);
        yearMap.put("stone",0);
        yearMap.put("mark","");
        resultMap.put("yearMap",yearMap);

       //汇总栏
        resultMap.put("waitCoalTime",45.6);
        resultMap.put("productTime",34.2);
        resultMap.put("productCoalTime",1234.2);
      //阐述
        StringBuffer sb=new StringBuffer();
        sb.append("1.主洗各筛机筛板、轨座及固定筛检查更换。\r\n");
        sb.append("2.事故池更换508潜水泵。\r\n");
        sb.append("3.主洗车间二楼372粗中煤泥刮板机紧固链条。\r\n");
        sb.append("4.主洗车间三楼半307浅槽分选机紧固底板螺丝。\r\n");
        sb.append("5.产品仓上804块精煤皮带更换挡皮。\r\n");
        resultMap.put("checkItem",sb.toString());
       //仓库
        Map<String,Object>  coalHouse=new HashMap<>();
        coalHouse.put("yhouse",1);
        coalHouse.put("j1house",2);
        coalHouse.put("j2house",3);
        coalHouse.put("y3house",4);
        coalHouse.put("z4house",4);
        coalHouse.put("b5house",5);
        coalHouse.put("checkTime",DateUtils.dateToStr(new Date(),"yyyy-MM-dd"));
        resultMap.put("coalHouse",coalHouse);
       //煤质监测
        //原煤质检
        Map<String,Object>  yCoalMap=new HashMap<>();
        yCoalMap.put("water",10.8);
        yCoalMap.put("gray",29.27);
        yCoalMap.put("sulfur",0.39);
        yCoalMap.put("energy",4681);
        resultMap.put("yCoalMap",yCoalMap);
        //块晶煤质检
        Map<String,Object>  blockCoalMap=new HashMap<>();
        blockCoalMap.put("water",10.8);
        blockCoalMap.put("gray",29.27);
        blockCoalMap.put("sulfur",0.39);
        blockCoalMap.put("energy",4681);
        resultMap.put("blockCoalMap",blockCoalMap);
        //未晶煤质检
        Map<String,Object>  wCoalMap=new HashMap<>();
        wCoalMap.put("water",10.8);
        wCoalMap.put("gray",29.27);
        wCoalMap.put("sulfur",0.39);
        wCoalMap.put("energy",4681);
        resultMap.put("wCoalMap",wCoalMap);
        //中煤质检
        Map<String,Object>  zongCoalMap=new HashMap<>();
        zongCoalMap.put("water",10.8);
        zongCoalMap.put("gray",29.27);
        zongCoalMap.put("sulfur",0.39);
        zongCoalMap.put("energy",4681);
        resultMap.put("zongCoalMap",zongCoalMap);
        //煤泥质检
        Map<String,Object>  niCoalMap=new HashMap<>();
        niCoalMap.put("water",10.8);
        niCoalMap.put("gray",29.27);
        niCoalMap.put("sulfur",0.39);
        niCoalMap.put("energy",4681);
        resultMap.put("niCoalMap",niCoalMap);
        //陨石质检
        Map<String,Object>  stoneCoalMap=new HashMap<>();
        stoneCoalMap.put("water",10.8);
        stoneCoalMap.put("gray",29.27);
        stoneCoalMap.put("sulfur",0.39);
        stoneCoalMap.put("energy",4681);
        resultMap.put("stoneCoalMap",stoneCoalMap);
     //生产统计
        //晚上
        Map<String,Object>  nightMap=new HashMap<>();
        nightMap.put("washTotal",2944);
        nightMap.put("white",2);
        nightMap.put("yellow",7);
        nightMap.put("liquid",8);
        resultMap.put("nightMap",nightMap);
        //中午
        Map<String,Object>  pmMap=new HashMap<>();
        pmMap.put("washTotal",2944);
        pmMap.put("white",2);
        pmMap.put("yellow",7);
        pmMap.put("liquid",8);
        resultMap.put("pmMap",pmMap);
        //早上
        Map<String,Object>  amMap=new HashMap<>();
        amMap.put("washTotal",2944);
        amMap.put("white",2);
        amMap.put("yellow",7);
        amMap.put("liquid",8);
        resultMap.put("amMap",amMap);
        //消耗统计
        //晚上
        Map<String,Object>  nightConsumeMap=new HashMap<>();
        nightConsumeMap.put("blockTotal",2944);
        nightConsumeMap.put("blockConsume",2);
        resultMap.put("nightConsumeMap",nightConsumeMap);
        //中午
        Map<String,Object>  pmConsumeMap=new HashMap<>();
        pmConsumeMap.put("blockTotal",2944);
        pmConsumeMap.put("blockConsume",2);
        resultMap.put("pmConsumeMap",pmConsumeMap);
        //早上
        Map<String,Object>  amConsumeMap=new HashMap<>();
        amConsumeMap.put("blockTotal",2944);
        amConsumeMap.put("blockConsume",2);
        resultMap.put("amConsumeMap",amConsumeMap);
        //最后阐述
        resultMap.put("endMark","介耗数据每天自行调整，按0.01-0.03范围波动调整");
        buildExcelReport( resultMap, response);
    }
    public  void test(){
        List<Student> studentsList=new ArrayList<>();
        //  List<Map> studentsList=new ArrayList<>();
        for(int k=0;k<5;k++){
            studentsList.add(new Student(k,"张三"+k,(k+1)*2));
           // resultMap.put("dataList",studentsList);
        }
    }
    /**
    * @author liujianfu
    * @description       封装excel
    * @date 2022/11/8 10:42
    * @param [resultMap]
    * @return void
    */
    public void  buildExcelReport(Map<String, Object> resultMap,HttpServletResponse response){
        String modelFile="d:/10-model.xlsx";
        try (InputStream is = new FileInputStream(new File(modelFile));) {
                Workbook workbook = new ExcelTransformer().transform(is, resultMap);
                buildExcelDocument("邵寨10月_"+System.currentTimeMillis()+".xlsx", workbook, response);


        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    /**
    * @author liujianfu
    * @description       数据流的输出
    * @date 2022/11/8 10:42
    * @param [filename, workbook, response]
    * @return void
    */
    protected static void buildExcelDocument(String filename, Workbook workbook, HttpServletResponse response)
            throws Exception {
        response.setHeader("Pragma", "no-cache");
        response.setHeader("Cache-Control", "no-cache");
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(filename, "utf-8"));
        OutputStream outputStream = response.getOutputStream();
        workbook.write(outputStream);
        outputStream.flush();
        outputStream.close();
    }
}

3.导出效果

 代码地址：https://gitee.com/jurf-liu/jett-excel-demo.git