JAVA(EasyExcel）通过远程调用模板 导出数据 复杂表头

前言

最近接手一个需求,单组数据的显示,也有多组数据的显示,查了好多文章,都不是很明白.但是这篇文章和我的需求差不多非常相似(链接放在文末),根据这篇文章然后将自己的实现过程记录了下来,以防之后再用到.

这是我需要导出的excel格式
开头是单条数据的展示
之后是多条数据的展示

1.要想导出复杂的excel样式 需要用到自定义excel模板—->意思就是我们根据自己需要的excel格式,新建一个excel,然后将完整的格式先画出来,将固定的标题内容填好,单元格大小,背景色等等.
2.需要改变的数据用{}花括号包含起来
单个的数据直接{实体对应的属性名}例如:用户名{userName}密码{password}
多条数据{.实体对应的属性名} 例如: 时间{.date} 编号{.id} 加点.的意思就是显示多条数据
3.要注意:如果一个excel文件中需要显示多个list 则.之前需要加标识,用来区分哪个数据是谁的.例如: 地址{list1.address}编号{list2.id}

我的模板

因为我只需要一个list显示多条数据,所以我.之前没有加标识

导入依赖

com.alibabaeasyexcel2.2.6

前端

 async censusByProject() {let params = {statisticalType: this.statisticalType,followUpId: this.followValue}let fileName = '跟进信息统计.xlsx'const res = await exportExcalTrail(params)this.downloads(fileName, res)},

export function exportExcalTrail(params) {return request({url: '/xx/xx/xx/xx/exportFollowAction',method: 'get',responseType: 'blob',params})}

 downloads(fileName, response) { // 拿到数据以后 通过 new Blob对象 创建excelif (!response) {return}const blob = new Blob([response.data])const href = window.URL.createObjectURL(blob)const downloadElement = document.createElement('a')downloadElement.style.display = 'none'downloadElement.href = hrefdownloadElement.download = fileNamedocument.body.appendChild(downloadElement)downloadElement.click()document.body.removeChild(downloadElement) // 下载完成移除元素window.URL.revokeObjectURL(href) // 释放掉blob对象}

后端(借鉴改造)

ExcelUtils+HttpClientUtil

/** * 导出复杂表头的Excel 先单组数据填充，再多组数据填充 * @param response * @param list 多组数据List * @param map 单组数据Map * @param outFileName 导出的Excel名称 * @param templateFileName Excel模板的路径名称 * @throws Exception */public static void exportExcel(HttpServletResponse response, List

package com.yz.util;import com.alibaba.fastjson.JSONObject;import org.apache.http.Consts;import org.apache.http.HttpEntity;import org.apache.http.client.config.RequestConfig;import org.apache.http.client.entity.UrlEncodedFormEntity;import org.apache.http.client.methods.*;import org.apache.http.config.Registry;import org.apache.http.config.RegistryBuilder;import org.apache.http.conn.socket.ConnectionSocketFactory;import org.apache.http.conn.socket.LayeredConnectionSocketFactory;import org.apache.http.conn.socket.PlainConnectionSocketFactory;import org.apache.http.conn.ssl.SSLConnectionSocketFactory;import org.apache.http.entity.StringEntity;import org.apache.http.impl.client.CloseableHttpClient;import org.apache.http.impl.client.HttpClients;import org.apache.http.impl.conn.PoolingHttpClientConnectionManager;import org.apache.http.message.BasicNameValuePair;import org.apache.http.util.EntityUtils;import org.slf4j.Logger;import org.slf4j.LoggerFactory;import javax.net.ssl.SSLContext;import java.io.IOException;import java.io.InputStream;import java.net.HttpURLConnection;import java.net.URI;import java.net.URL;import java.security.NoSuchAlgorithmException;import java.util.ArrayList;import java.util.Iterator;import java.util.List;public class HttpClientUtil {private static Logger LOGGER = LoggerFactory.getLogger(HttpClientUtil.class);private static PoolingHttpClientConnectionManager cm = null;private static RequestConfig requestConfig = null;static {LayeredConnectionSocketFactory sslsf = null;try {sslsf = new SSLConnectionSocketFactory(SSLContext.getDefault());} catch (NoSuchAlgorithmException e) {LOGGER.error("创建SSL连接失败");}Registry socketFactoryRegistry = RegistryBuilder.create().register("https", sslsf).register("http", new PlainConnectionSocketFactory()).build();cm = new PoolingHttpClientConnectionManager(socketFactoryRegistry);//多线程调用注意配置，根据线程数设定cm.setMaxTotal(200);//多线程调用注意配置，根据线程数设定cm.setDefaultMaxPerRoute(300);requestConfig = RequestConfig.custom()//数据传输过程中数据包之间间隔的最大时间.setSocketTimeout(20000)//连接建立时间，三次握手完成时间.setConnectTimeout(20000)//重点参数.setExpectContinueEnabled(true).setConnectionRequestTimeout(10000)//重点参数，在请求之前校验链接是否有效.setStaleConnectionCheckEnabled(true).build();}public static CloseableHttpClient getHttpClient() {CloseableHttpClient httpClient = HttpClients.custom().setConnectionManager(cm).build();return httpClient;}public static void closeResponse(CloseableHttpResponse closeableHttpResponse) throws IOException {EntityUtils.consume(closeableHttpResponse.getEntity());closeableHttpResponse.close();}/** * get请求,params可为null,headers可为null * * @param url * @return * @throws IOException */public static String get( String url, JSONObject params) throws IOException {CloseableHttpClient httpClient = getHttpClient();CloseableHttpResponse closeableHttpResponse = null;// 创建get请求HttpGet httpGet = null;List paramList = new ArrayList();if (params != null) {Iterator iterator = params.keySet().iterator();while (iterator.hasNext()) {String paramName = iterator.next();paramList.add(new BasicNameValuePair(paramName, params.get(paramName).toString()));}}if (url.contains("?")) {httpGet = new HttpGet(url + "&" + EntityUtils.toString(new UrlEncodedFormEntity(paramList, Consts.UTF_8)));} else {httpGet = new HttpGet(url + "?" + EntityUtils.toString(new UrlEncodedFormEntity(paramList, Consts.UTF_8)));}httpGet.setConfig(requestConfig);httpGet.addHeader("Content-Type", "application/json");httpGet.addHeader("lastOperaTime", String.valueOf(System.currentTimeMillis()));closeableHttpResponse = httpClient.execute(httpGet);HttpEntity entity = closeableHttpResponse.getEntity();String response = EntityUtils.toString(entity);closeResponse(closeableHttpResponse);return response;}public static String shopGet( String url, JSONObject params,String appId,Long timestamp,String sign) throws IOException {CloseableHttpClient httpClient = getHttpClient();CloseableHttpResponse closeableHttpResponse = null;// 创建get请求HttpGet httpGet = null;List paramList = new ArrayList();if (params != null) {Iterator iterator = params.keySet().iterator();while (iterator.hasNext()) {String paramName = iterator.next();paramList.add(new BasicNameValuePair(paramName, params.get(paramName).toString()));}}if (url.contains("?")) {httpGet = new HttpGet(url + "&" + EntityUtils.toString(new UrlEncodedFormEntity(paramList, Consts.UTF_8)));} else {httpGet = new HttpGet(url + "?" + EntityUtils.toString(new UrlEncodedFormEntity(paramList, Consts.UTF_8)));}httpGet.setConfig(requestConfig);httpGet.addHeader("Content-Type", "application/json");httpGet.addHeader("lastOperaTime", String.valueOf(System.currentTimeMillis()));httpGet.addHeader("appId", appId);httpGet.addHeader("timestamp", Long.toString(timestamp));httpGet.addHeader("sign", sign);closeableHttpResponse = httpClient.execute(httpGet);HttpEntity entity = closeableHttpResponse.getEntity();String response = EntityUtils.toString(entity);closeResponse(closeableHttpResponse);return response;}/** * post请求,params可为null,headers可为null * * @param url * @param params * @return * @throws IOException */public static String post(String url, JSONObject params) throws IOException {CloseableHttpClient httpClient = getHttpClient();CloseableHttpResponse closeableHttpResponse = null;// 创建post请求HttpPost httpPost = new HttpPost(url);//if (headers != null) {//Iterator iterator = headers.keySet().iterator();//while (iterator.hasNext()) {//String headerName = iterator.next().toString();//httpPost.addHeader(headerName, headers.get(headerName).toString());//}//}httpPost.setConfig(requestConfig);httpPost.addHeader("Content-Type", "application/json");httpPost.addHeader("lastOperaTime", String.valueOf(System.currentTimeMillis()));if (params != null) {StringEntity stringEntity = new StringEntity(params.toJSONString(), "UTF-8");httpPost.setEntity(stringEntity);}closeableHttpResponse = httpClient.execute(httpPost);HttpEntity entity = closeableHttpResponse.getEntity();String response = EntityUtils.toString(entity);closeResponse(closeableHttpResponse);return response;}/** * delete,params可为null,headers可为null * * @param url * @param params * @return * @throws IOException */public static String delete(JSONObject headers, String url, JSONObject params) throws IOException {CloseableHttpClient httpClient = getHttpClient();CloseableHttpResponse closeableHttpResponse = null;// 创建delete请求，HttpDeleteWithBody 为内部类，类在下面HttpDeleteWithBody httpDelete = new HttpDeleteWithBody(url);if (headers != null) {Iterator iterator = headers.keySet().iterator();while (iterator.hasNext()) {String headerName = iterator.next().toString();httpDelete.addHeader(headerName, headers.get(headerName).toString());}}httpDelete.setConfig(requestConfig);httpDelete.addHeader("Content-Type", "application/json");httpDelete.addHeader("lastOperaTime", String.valueOf(System.currentTimeMillis()));if (params != null) {StringEntity stringEntity = new StringEntity(params.toJSONString(), "UTF-8");httpDelete.setEntity(stringEntity);}closeableHttpResponse = httpClient.execute(httpDelete);HttpEntity entity = closeableHttpResponse.getEntity();String response = EntityUtils.toString(entity);closeResponse(closeableHttpResponse);return response;}/** * put,params可为null,headers可为null * * @param url * @param params * @return * @throws IOException */public static String put(JSONObject headers, String url, JSONObject params) throws IOException {CloseableHttpClient httpClient = getHttpClient();CloseableHttpResponse closeableHttpResponse = null;// 创建put请求HttpPut httpPut = new HttpPut(url);if (headers != null) {Iterator iterator = headers.keySet().iterator();while (iterator.hasNext()) {String headerName = iterator.next().toString();httpPut.addHeader(headerName, headers.get(headerName).toString());}}httpPut.setConfig(requestConfig);httpPut.addHeader("Content-Type", "application/json");httpPut.addHeader("lastOperaTime", String.valueOf(System.currentTimeMillis()));if (params != null) {StringEntity stringEntity = new StringEntity(params.toJSONString(), "UTF-8");httpPut.setEntity(stringEntity);}// 从响应模型中获得具体的实体closeableHttpResponse = httpClient.execute(httpPut);HttpEntity entity = closeableHttpResponse.getEntity();String response = EntityUtils.toString(entity);closeResponse(closeableHttpResponse);return response;}public static class HttpDeleteWithBody extends HttpEntityEnclosingRequestBase {public static final String METHOD_NAME = "DELETE";@Overridepublic String getMethod() {return METHOD_NAME;}public HttpDeleteWithBody(final String uri) {super();setURI(URI.create(uri));}public HttpDeleteWithBody(final URI uri) {super();setURI(uri);}public HttpDeleteWithBody() {super();}}/** * 根据模板地址获取InputStream * * @param templateUrl: 模板url地址 * @date 2022-05-09 13:40 * @author*/public static InputStream getExcelInputStream(String templateUrl) throws IOException {URL url = new URL(templateUrl);HttpURLConnection conn = (HttpURLConnection) url.openConnection();conn.setRequestMethod("GET");//设置通用的请求属性conn.setRequestProperty("accept", "*/*");conn.setRequestProperty("connection", "Keep-Alive");conn.setRequestProperty("user-agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1)");conn.setRequestProperty("Content-Type", "application/json;charset=utf-8");return conn.getInputStream();}}

controller层

调用写好的工具类的 exportExcel 方法 参数: 第一个 response, 第二个list (需要在excel中显示的多条数据的list数据),第三个map (需要在excel中显示的单条数据的map), 第四个 outFileName (导出的excel的文件名)(文件名+时间) , 第五个templateFileName (自己建的excel模板路径)

需要显示的单条数据放在map中
需要显示的多条数据放在list中

@ApiOperation(value = "导出到excel", notes = "导出到excel")@SysLog("导出到excel")@GetMapping("/exportFollowAction")public void exportFollowAction(HttpServletResponse response, Integer followUpId, String statisticalType, String startTime, String endTime) {try {String filename = "市场人员客户跟踪信息统计表";List doppelgangerFollowRecordDtos = doppelgangerProjectStatisticsService.exportFollowAction(followUpId, statisticalType, startTime, endTime);//创建map将查询的数据get后方在map中 一一对应excel模板中的属性名HashMap map = new HashMap();if ("0".equals(statisticalType)) {map.put("startOrEndTime", "全部");} else if ("1".equals(statisticalType)) {map.put("startOrEndTime", "本季度");} else if ("2".equals(statisticalType)) {map.put("startOrEndTime", "本月");} else if ("3".equals(statisticalType)) {map.put("startOrEndTime", startTime + "——" + endTime);}//excel模板路径String templateFileName = ExcelDto.follow;ExcelUtils.exportExcel(response, doppelgangerFollowRecordDtos, map, filename, templateFileName);} catch (Exception e) {e.printStackTrace();}}

serviceImpl层

想要对查询的数据先一步进行处理,可以在serviceImpl中编写

@Overridepublic List exportFollowAction(Integer followUpId, String statisticalType, String startTime, String endTime) {SimpleDateFormat longSdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");//本季度if ("1".equals(statisticalType)) {startTime = longSdf.format(TimeUtil.getCurrentQuarterStartTime());endTime = longSdf.format(TimeUtil.getCurrentQuarterEndTime());}//本月if ("2".equals(statisticalType)) {startTime = longSdf.format(TimeUtil.getTimesMonthmorning());endTime = longSdf.format(TimeUtil.getTimesMonthnight());}List doppelgangerFollowRecordDtos = recordMapper.getFollowAction(followUpId, statisticalType, startTime, endTime);doppelgangerFollowRecordDtos.stream().forEach(doppelgangerFollowRecordDto -> {if (!"".equals(doppelgangerFollowRecordDto.getStaffNumRange()) && doppelgangerFollowRecordDto.getStaffNumRange() != null) {} else {ElsEnterpriseInfo elsEnterpriseInfo = doppelgangerCustomerService.getCompanyInfoByName(doppelgangerFollowRecordDto.getCompanyName());if (elsEnterpriseInfo != null) {if (!"".equals(elsEnterpriseInfo.getStaffNumRange()) && elsEnterpriseInfo.getStaffNumRange() != null) {doppelgangerFollowRecordDto.setStaffNumRange(elsEnterpriseInfo.getStaffNumRange());}}}});try {ListUtil.addRank(doppelgangerFollowRecordDtos);} catch (Exception e) {throw new RuntimeException(e);}return doppelgangerFollowRecordDtos;}

结果展示:

参考文章：使用EasyExcel的模板导出复杂表头的Excel- 先单组数据填充，再多组数据填充_easyexcel导出复杂excel_暴走的山交的博客-CSDN博客