java导出Excel(Jxls模板)

Jxls工具类

package cn.microvideo.aud.report.jxls.util;

import cn.microvideo.aud.report.export.rep.TruckTrafficRep;
import org.jxls.common.Context;
import org.jxls.expression.JexlExpressionEvaluator;
import org.jxls.transform.Transformer;
import org.jxls.util.JxlsHelper;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.util.*;


public class JxlsUtil {
    /**
     *
     * @param templatePath
     * @param os
     * @param datasource
     * @throws IOException
     */
    public static void exportExcel(String templatePath, OutputStream os,  List datasource) throws IOException{
        try(InputStream is = new FileInputStream(templatePath)) {
            Context context = new Context();
            context.putVar("strategyHandled", datasource);
            JxlsHelper.getInstance().processTemplate(is, os, context);
        }
    }

    /**
     *
     * @param is   模板地址
     * @param os    导出的空白文件
     * @param datasource  数据集合
     * @throws IOException
     */
    public static void exportExcel(InputStream is, OutputStream os,  List datasource) throws IOException{
        Context context = new Context();
        context.putVar("strategyHandled", datasource);
        JxlsHelper.getInstance().processTemplate(is, os, context);
    }

    public static void exportExcel(InputStream is, HttpServletResponse response, List datasource,Map<String,String> map,String fileName) throws IOException{
        response.setHeader("content-Type", "application/vnd.ms-excel");
        response.setHeader("Content-Disposition", "attachment;filename="+URLEncoder.encode(fileName,"utf-8"));
        Context context = new Context();
        context.putVar("strategyHandled", datasource);
        if(map!=null){
            for (String key : map.keySet()) {
                context.putVar(key,  map.get(key));
            }
        }

        JxlsHelper.getInstance().processTemplate(is, response.getOutputStream(), context);
    }
    public static void exportExcel(InputStream is, OutputStream os, Map<String, Object> model) throws IOException{
        Context context = new Context();
        if (model != null) {
            for (String key : model.keySet()) {
                context.putVar(key, model.get(key));
            }
        }
        JxlsHelper jxlsHelper = JxlsHelper.getInstance();
        Transformer transformer  = jxlsHelper.createTransformer(is, os);
        JexlExpressionEvaluator evaluator = (JexlExpressionEvaluator)transformer.getTransformationConfig().getExpressionEvaluator();
        Map<String, Object> funcs = new HashMap<String, Object>();
        jxlsHelper.processTemplate(context, transformer);
    }
    public static void main(String[] args) throws Exception {
        // 模板路径和输出流
        OutputStream os = new FileOutputStream("D:/gandong/project/svn_project/audreport/src/main/resources/jxls/out.xlsx");
        TruckTrafficRep rep = new TruckTrafficRep();
        rep.setNumber(225L);
        rep.setTime("2012-02-05");
        List<TruckTrafficRep> list = new ArrayList<>();
        list.add(rep);
        JxlsUtil.exportExcel(JxlsUtil.class.getResourceAsStream("/jxls/number_of_trucks_passing.xlsx"),os,list);
    }

   /* public static void main(String[] args) throws Exception  {
        // 模板路径和输出流
        OutputStream os = new FileOutputStream("D:/gandong/project/svn_project/audreport/src/main/resources/jxls/车辆入口通行数量每日统计.xlsx");
        TruckTrafficRep rep1 = new TruckTrafficRep();
        rep1.setNumber(1245L);
        rep1.setTime("2020-03-05");
        TruckTrafficRep rep2 = new TruckTrafficRep();
        rep2.setNumber(124445L);
        rep2.setTime("2020-03-06");
        List<TruckTrafficRep> list = new ArrayList<>();
        list.add(rep1);
        list.add(rep2);
        String templatePath = "D:/gandong/project/svn_project/audreport/src/main/resources/jxls/number_of_trucks_passing.xlsx";
        JxlsUtil.exportExcel(templatePath,os,list);
    }*/



}

service类

 
  /**
   * 
   * @param req  请求参数
   * @return
   */
void  exportData(Req req, HttpServletResponse response) throws Exception;

serviceImpl

     private String 模板地址="/jxls/file.xlsx";


    @Override
    public void exportData(Req req, HttpServletResponse response) throws Exception {
      
        List<Rep> list=selectRep(req);
        String name="导出文件名称";
        export( list, response, name, "模板地址");
    }

    /**
     * 导出
     * 
     * @param
     * @param rep
     * @param response
     * @param name
     * @throws Exception
     */
    private void export( List rep, HttpServletResponse response, String name, String url) throws Exception {
        String fileName = URLEncoder.encode(name + ".xlsx", "UTF-8");
        response.setHeader("Content-Disposition", "attachment;fileName=" + fileName);
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        response.setCharacterEncoding("UTF-8");
        JxlsUtil.exportExcel(JxlsUtil.class.getResourceAsStream(url), response.getOutputStream(), rep);
    }

 

pom依赖

  <!-- jxls@excel -->
        <dependency>
            <groupId>org.jxls</groupId>
            <artifactId>jxls</artifactId>
            <version>2.6.0</version>
        </dependency>
     <dependency>
            <groupId>org.jxls</groupId>
            <artifactId>jxls-poi</artifactId>
            <version>1.2.0</version>
        </dependency>
        <dependency>
            <groupId>org.jxls</groupId>
            <artifactId>jxls-jexcel</artifactId>
            <version>1.0.9</version>
        </dependency>
        <dependency>
            <groupId>org.jxls</groupId>
            <artifactId>jxls-reader</artifactId>
            <version>2.0.5</version>
        </dependency>

目录结构

示例模板

百度网盘
链接：https://pan.baidu.com/s/1afqUDNxee8vr4JI-4llkmw 
提取码：aqua 

模板图片（要添加2个批注（修改批注位置：excel->工具栏->审阅->批注））