制作模板
注意:在第一行第一列上添加批注
jx:area(lastCell="K4")
在表格里面添加下面代码
jx:each(items="personList" var="person" lastCell="K4")
maven依赖
<!-- Hutool是一个Java工具包 -->
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.6.0</version>
</dependency>
<!-- excel导出 -->
<dependency>
<groupId>org.jxls</groupId>
<artifactId>jxls-poi</artifactId>
<version>2.9.0</version>
</dependency>
创建工具类
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.io.resource.ClassPathResource;
import cn.hutool.core.util.CharsetUtil;
import cn.hutool.core.util.StrUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.jexl3.JexlBuilder;
import org.apache.commons.jexl3.JexlEngine;
import org.jxls.common.Context;
import org.jxls.expression.JexlExpressionEvaluator;
import org.jxls.transform.Transformer;
import org.jxls.util.JxlsHelper;
import org.springframework.http.HttpHeaders;
import org.springframework.http.MediaType;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.InputStream;
import java.net.URLEncoder;
import java.util.HashMap;
import java.util.Map;
/**
* @Author:dcy
* @Description:
* @Date: 2021/7/13 15:20
*/
@Slf4j
public class JxlsUtils {
/**
* 导出excel
*
* @param response
* @param fileName
* @param dataMap
* @param templateName
*/
public static void export(HttpServletResponse response, String fileName, Map<String, ?> dataMap, String templateName) {
try {
// 设置响应头信息
ServletOutputStream outputStream = response.getOutputStream();
response.setHeader(HttpHeaders.CONTENT_DISPOSITION, "attachment;filename=" + URLEncoder.encode(fileName, CharsetUtil.UTF_8));
response.setContentType(MediaType.APPLICATION_OCTET_STREAM_VALUE);
response.setCharacterEncoding(CharsetUtil.UTF_8);
// 读取模板路径
ClassPathResource classPathResource = new ClassPathResource(templateName);
final InputStream inputStream = classPathResource.getStream();
// 设置模板参数
Context context = new Context();
dataMap.forEach(context::putVar);
JxlsHelper jxlsHelper = JxlsHelper.getInstance();
Transformer transformer = jxlsHelper.createTransformer(inputStream, outputStream);
//获得配置 + 添加自定义功能
JexlExpressionEvaluator evaluator = (JexlExpressionEvaluator) transformer.getTransformationConfig().getExpressionEvaluator();
Map<String, Object> functions = new HashMap<>();
functions.put("str", new StrUtil());
functions.put("date", new DateUtil());
final JexlEngine jexlEngine = new JexlBuilder().silent(true).strict(true).namespaces(functions).create();
evaluator.setJexlEngine(jexlEngine);
jxlsHelper.processTemplate(context, transformer);
} catch (Exception ex) {
log.error("export {}", ex.getMessage());
}
}
}
测试方法
public class EquPerson {
/**
* 姓名
*/
private String name;
}
@GetMapping("/export")
public void export(HttpServletResponse response) {
Map<String, Object> map = new HashMap<>();
map.put("schoolName", "测试学校");
List<EquPerson> equPersonList = new ArrayList<>();
for (int i = 0; i < 10; i++) {
final EquPerson equPerson = new EquPerson();
equPerson.setName("张三");
equPersonList.add(equPerson);
}
map.put("personList", equPersonList);
JxlsUtils.export(response, "xx人员导出表.xlsx", map, "jxls/equ_person.xlsx");
}
展示效果
参考链接
https://www.cnblogs.com/klguang/p/6425422.html
https://www.cnblogs.com/dw3306/p/11098841.html