前言
最近接手一個(gè)需求,單組數(shù)據(jù)的顯示,也有多組數(shù)據(jù)的顯示,查了好多文章,都不是很明白.但是這篇文章和我的需求差不多非常相似(鏈接放在文末),根據(jù)這篇文章然后將自己的實(shí)現(xiàn)過(guò)程記錄了下來(lái),以防之后再用到.
這是我需要導(dǎo)出的excel格式開(kāi)頭是單條數(shù)據(jù)的展示
之后是多條數(shù)據(jù)的展示
1.要想導(dǎo)出復(fù)雜的excel樣式 需要用到自定義excel模板---->意思就是我們根據(jù)自己需要的excel格式,新建一個(gè)excel,然后將完整的格式先畫(huà)出來(lái),將固定的標(biāo)題內(nèi)容填好,單元格大小,背景色等等.
2.需要改變的數(shù)據(jù)用{}花括號(hào)包含起來(lái)
單個(gè)的數(shù)據(jù)直接{實(shí)體對(duì)應(yīng)的屬性名}例如:用戶(hù)名{userName}密碼{password}
多條數(shù)據(jù){.實(shí)體對(duì)應(yīng)的屬性名} 例如: 時(shí)間{.date} 編號(hào){.id} 加點(diǎn).的意思就是顯示多條數(shù)據(jù)
3.要注意:如果一個(gè)excel文件中需要顯示多個(gè)list 則.之前需要加標(biāo)識(shí),用來(lái)區(qū)分哪個(gè)數(shù)據(jù)是誰(shuí)的.例如: 地址{list1.address}編號(hào){list2.id}
我的模板
因?yàn)槲抑恍枰粋€(gè)list顯示多條數(shù)據(jù),所以我.之前沒(méi)有加標(biāo)識(shí)
?
導(dǎo)入依賴(lài)
<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 = '跟進(jìn)信息統(tǒng)計(jì).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) { // 拿到數(shù)據(jù)以后 通過(guò) new Blob對(duì)象 創(chuàng)建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對(duì)象
}
后端(借鑒改造)
ExcelUtils+HttpClientUtil
/**
* 導(dǎo)出復(fù)雜表頭的Excel 先單組數(shù)據(jù)填充,再多組數(shù)據(jù)填充
* @param response
* @param list 多組數(shù)據(jù)List
* @param map 單組數(shù)據(jù)Map
* @param outFileName 導(dǎo)出的Excel名稱(chēng)
* @param templateFileName Excel模板的路徑名稱(chēng)
* @throws Exception
*/
public static void exportExcel(HttpServletResponse response, List<? extends BaseRowModel> list, Map<String,Object> map,
String outFileName, String templateFileName ) throws Exception{
//遠(yuǎn)程獲取文件
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))是將數(shù)據(jù)寫(xiě)入文件,并交給response
ExcelWriter excelWriter = EasyExcel.write(ExcelUtils.getOutputStream(outFileName, response)).withTemplate(excelInputStream).build();
//創(chuàng)建Sheet
//設(shè)置excel Sheet為第幾張并設(shè)置名稱(chēng)
//.writerSheet(0,"第一個(gè)")中前面的參數(shù)為sheetNo,就是第幾張sheet
//第二參數(shù)為sheet名稱(chēng)
//不寫(xiě)就是默認(rèn)
WriteSheet writeSheet = EasyExcel.writerSheet().build();
// 這里注意 入?yún)⒂昧薴orceNewRow 代表在寫(xiě)入list的時(shí)候不管list下面有沒(méi)有空行 都會(huì)創(chuàng)建一行,然后下面的數(shù)據(jù)往后移動(dòng)。默認(rèn) 是false,會(huì)直接使用下一行,如果沒(méi)有則創(chuàng)建。
// forceNewRow 如果設(shè)置了true,有個(gè)缺點(diǎn) 就是他會(huì)把所有的數(shù)據(jù)都放到內(nèi)存了,所以慎用
// 簡(jiǎn)單的說(shuō) 如果你的模板有l(wèi)ist,且list不是最后一行,下面還有數(shù)據(jù)需要填充 就必須設(shè)置 forceNewRow=true 但是這個(gè)就會(huì)把所有數(shù)據(jù)放到內(nèi)存 會(huì)很耗內(nèi)存
//.direction(WriteDirectionEnum.VERTICAL)這個(gè)是設(shè)置list填入的時(shí)候是縱向填入
FillConfig fillConfig = FillConfig.builder().direction(WriteDirectionEnum.VERTICAL).forceNewRow(Boolean.FALSE).build();
//這里是將list填充到excel中。
//會(huì)去找模板上對(duì)應(yīng)的數(shù)據(jù)填入,例如模板中的{list.getGoodsName}就是下面List集合中名為goodsName字段對(duì)應(yīng)的數(shù)據(jù)
//new FillWrapper("list", selectOrderDTO.getSelectOrderGoodsDTOS())前面的參數(shù)是設(shè)置一個(gè)填入的list名
//后面的參數(shù)是獲得的list,里面就包含了要填入的數(shù)據(jù)
//.fill()主要就是將數(shù)據(jù)填入excel中
excelWriter.fill(new FillWrapper(list), fillConfig, writeSheet);
//這里是將一些普通數(shù)據(jù)放到map中,方便填入,可以看getStringObjectMap()。
//map的String是對(duì)應(yīng)的名稱(chēng),Object就是數(shù)據(jù)了。
//將數(shù)據(jù)填入
excelWriter.fill(map, writeSheet);
//關(guān)閉
excelWriter.finish();
}
/**
* 這是ExcelUtil.getOutputStream
* 這里就是將文件下載交給了瀏覽器
* @return
*/
public static OutputStream getOutputStream(String Name, HttpServletResponse response) throws Exception {
//這里是對(duì)文件的重命名
SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmm");
String date = sdf.format(new Date());
String fileName = new String(Name.getBytes(), CommonConstants.UTF8) + date + ".xlsx";
// 這里文件名如果涉及中文一定要使用URL編碼,否則會(huì)亂碼
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("創(chuàng)建SSL連接失敗");
}
Registry<ConnectionSocketFactory> socketFactoryRegistry = RegistryBuilder.<ConnectionSocketFactory>create()
.register("https", sslsf)
.register("http", new PlainConnectionSocketFactory())
.build();
cm = new PoolingHttpClientConnectionManager(socketFactoryRegistry);
//多線(xiàn)程調(diào)用注意配置,根據(jù)線(xiàn)程數(shù)設(shè)定
cm.setMaxTotal(200);
//多線(xiàn)程調(diào)用注意配置,根據(jù)線(xiàn)程數(shù)設(shè)定
cm.setDefaultMaxPerRoute(300);
requestConfig = RequestConfig.custom()
//數(shù)據(jù)傳輸過(guò)程中數(shù)據(jù)包之間間隔的最大時(shí)間
.setSocketTimeout(20000)
//連接建立時(shí)間,三次握手完成時(shí)間
.setConnectTimeout(20000)
//重點(diǎn)參數(shù)
.setExpectContinueEnabled(true)
.setConnectionRequestTimeout(10000)
//重點(diǎn)參數(shù),在請(qǐng)求之前校驗(yàn)鏈接是否有效
.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請(qǐng)求,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;
// 創(chuàng)建get請(qǐng)求
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;
// 創(chuàng)建get請(qǐng)求
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請(qǐng)求,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;
// 創(chuàng)建post請(qǐng)求
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;
// 創(chuàng)建delete請(qǐng)求,HttpDeleteWithBody 為內(nèi)部類(lèi),類(lèi)在下面
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;
// 創(chuàng)建put請(qǐng)求
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);
}
// 從響應(yīng)模型中獲得具體的實(shí)體
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();
}
}
/**
* 根據(jù)模板地址獲取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");
//設(shè)置通用的請(qǐng)求屬性
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層
調(diào)用寫(xiě)好的工具類(lèi)的 exportExcel 方法 參數(shù): 第一個(gè) response, 第二個(gè)list (需要在excel中顯示的多條數(shù)據(jù)的list數(shù)據(jù)),第三個(gè)map (需要在excel中顯示的單條數(shù)據(jù)的map), 第四個(gè) outFileName (導(dǎo)出的excel的文件名)(文件名+時(shí)間) , 第五個(gè)templateFileName (自己建的excel模板路徑)
需要顯示的單條數(shù)據(jù)放在map中
需要顯示的多條數(shù)據(jù)放在list中
?
@ApiOperation(value = "導(dǎo)出到excel", notes = "導(dǎo)出到excel")
@SysLog("導(dǎo)出到excel")
@GetMapping("/exportFollowAction")
public void exportFollowAction(HttpServletResponse response, Integer followUpId, String statisticalType, String startTime, String endTime) {
try {
String filename = "市場(chǎng)人員客戶(hù)跟蹤信息統(tǒng)計(jì)表";
List<DoppelgangerFollowRecordDto> doppelgangerFollowRecordDtos = doppelgangerProjectStatisticsService.exportFollowAction(followUpId, statisticalType, startTime, endTime);
//創(chuàng)建map將查詢(xún)的數(shù)據(jù)get后方在map中 一一對(duì)應(yīng)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層
想要對(duì)查詢(xún)的數(shù)據(jù)先一步進(jìn)行處理,可以在serviceImpl
中編寫(xiě)
@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;
}
結(jié)果展示:
文章來(lái)源:http://www.zghlxwxcb.cn/news/detail-768438.html
?參考文章:使用EasyExcel的模板導(dǎo)出復(fù)雜表頭的Excel- 先單組數(shù)據(jù)填充,再多組數(shù)據(jù)填充_easyexcel導(dǎo)出復(fù)雜excel_暴走的山交的博客-CSDN博客文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-768438.html
到了這里,關(guān)于JAVA(EasyExcel)通過(guò)遠(yuǎn)程調(diào)用模板 導(dǎo)出數(shù)據(jù) 復(fù)雜表頭的文章就介紹完了。如果您還想了解更多內(nèi)容,請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章,希望大家以后多多支持TOY模板網(wǎng)!