前言
最近接手一个需求,单组数据的显示,也有多组数据的显示,查了好多文章,都不是很明白.但是这篇文章和我的需求差不多非常相似(链接放在文末),根据这篇文章然后将自己的实现过程记录了下来,以防之后再用到.
这是我需要导出的excel格式开头是单条数据的展示之后是多条数据的展示
1.要想导出复杂的excel样式 需要用到自定义excel模板---->意思就是我们根据自己需要的excel格式,新建一个excel,然后将完整的格式先画出来,将固定的标题内容填好,单元格大小,背景色等等.
2.需要改变的数据用{}花括号包含起来
单个的数据直接{实体对应的属性名}例如:用户名{userName}密码{password}
多条数据{.实体对应的属性名} 例如: 时间{.date} 编号{.id} 加点.的意思就是显示多条数据
3.要注意:如果一个excel文件中需要显示多个list 则.之前需要加标识,用来区分哪个数据是谁的.例如: 地址{list1.address}编号{list2.id}
我的模板
因为我只需要一个list显示多条数据,所以我.之前没有加标识
导入依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.6</version>
</dependency>
前端
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对象 创建excel
if (!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 = href
downloadElement.download = fileName
document.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<? extends BaseRowModel> list, Map<String,Object> map,
String outFileName, String templateFileName ) throws Exception{
//远程获取文件
InputStream excelInputStream = HttpClientUtil.getExcelInputStream(templateFileName);
//告诉response下载的是excel文件
response.setContentType("application/vnd.ms-excel");
//告诉response使用utf-8编码格式
response.setCharacterEncoding("utf-8");
//.withTemplate(templateFileName)就是读取模板
//.write(ExcelUtil.getOutputStream(outFileName, response))是将数据写入文件，并交给response
ExcelWriter excelWriter = EasyExcel.write(ExcelUtils.getOutputStream(outFileName, response)).withTemplate(excelInputStream).build();
//创建Sheet
//设置excel Sheet为第几张并设置名称
//.writerSheet(0,"第一个")中前面的参数为sheetNo,就是第几张sheet
//第二参数为sheet名称
//不写就是默认
WriteSheet writeSheet = EasyExcel.writerSheet().build();
// 这里注意 入参用了forceNewRow 代表在写入list的时候不管list下面有没有空行 都会创建一行，然后下面的数据往后移动。默认 是false，会直接使用下一行，如果没有则创建。
// forceNewRow 如果设置了true,有个缺点 就是他会把所有的数据都放到内存了，所以慎用
// 简单的说 如果你的模板有list,且list不是最后一行，下面还有数据需要填充 就必须设置 forceNewRow=true 但是这个就会把所有数据放到内存 会很耗内存
//.direction(WriteDirectionEnum.VERTICAL)这个是设置list填入的时候是纵向填入
FillConfig fillConfig = FillConfig.builder().direction(WriteDirectionEnum.VERTICAL).forceNewRow(Boolean.FALSE).build();
//这里是将list填充到excel中。
//会去找模板上对应的数据填入，例如模板中的{list.getGoodsName}就是下面List集合中名为goodsName字段对应的数据
//new FillWrapper("list", selectOrderDTO.getSelectOrderGoodsDTOS())前面的参数是设置一个填入的list名
//后面的参数是获得的list，里面就包含了要填入的数据
//.fill()主要就是将数据填入excel中
excelWriter.fill(new FillWrapper(list), fillConfig, writeSheet);
//这里是将一些普通数据放到map中，方便填入，可以看getStringObjectMap()。
//map的String是对应的名称，Object就是数据了。
//将数据填入
excelWriter.fill(map, writeSheet);
//关闭
excelWriter.finish();
}
/**
* 这是ExcelUtil.getOutputStream
* 这里就是将文件下载交给了浏览器
* @return
*/
public static OutputStream getOutputStream(String Name, HttpServletResponse response) throws Exception {
//这里是对文件的重命名
SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmm");
String date = sdf.format(new Date());
String fileName = new String(Name.getBytes(), CommonConstants.UTF8) + date + ".xlsx";
// 这里文件名如果涉及中文一定要使用URL编码,否则会乱码
response.setContentType("application/force-download");
response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
return response.getOutputStream();
}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<ConnectionSocketFactory> socketFactoryRegistry = RegistryBuilder.<ConnectionSocketFactory>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<BasicNameValuePair> paramList = new ArrayList<>();
if (params != null) {
Iterator<String> 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<BasicNameValuePair> paramList = new ArrayList<>();
if (params != null) {
Iterator<String> 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";
@Override
public 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<DoppelgangerFollowRecordDto> doppelgangerFollowRecordDtos = doppelgangerProjectStatisticsService.exportFollowAction(followUpId, statisticalType, startTime, endTime);
//创建map将查询的数据get后方在map中 一一对应excel模板中的属性名
HashMap<String, Object> 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中编写
@Override
public List<DoppelgangerFollowRecordDto> 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<DoppelgangerFollowRecordDto> 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;
}结果展示:
文章来源：https://uudwc.com/A/Jwk15
参考文章：使用EasyExcel的模板导出复杂表头的Excel- 先单组数据填充，再多组数据填充_easyexcel导出复杂excel_暴走的山交的博客-CSDN博客文章来源地址https://uudwc.com/A/Jwk15
