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->工具栏->审阅->批注))