2021.02.27更新
可能有人对传入的数据不清楚是什么格式的,这里我说明一下
headerList格式:
传入的数据格式list:
字段对应上就行了,比如headerList的0是设备名称,那么list的0就要是设备名称
话不多说,直接上代码--------------之前是分开的,这次直接把class传进来,直接复制粘贴就行了
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.metadata.Table;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.WriteTable;
import com.tigercontrols.valveapigateway.api.exception.CodeException;
import com.tigercontrols.valveapigateway.api.exception.ErrorCode;
import com.tigercontrols.valveapigateway.newApi.util.ServletUtils;
import lombok.extern.slf4j.Slf4j;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;
import static java.lang.System.out;
/**
* @author :byH
* @date :Created 2021/1/7 8:15
* @description:Easyexcel导出excel
*/
@Slf4j
public class ExcelAgent {
/**
* 动态数据导出
*
* @param headerList 需要导出的表头名称
* @param list 需要导出的数据
* @param type 生成类型 1多sheet 2多table
*/
public static String exportDynami(List<String> headerList, List<List<Object>> list, int type, String fileName) throws CodeException {
if (list.size() >= 1000000) {
throw new CodeException(ErrorCode.UNEXISTED_ERROR, "The data Out of data range,The max value is 1000000");
}
try {
//获取response
HttpServletResponse response = ServletUtils.getResponse();
ExcelWriter excelWriter = new ExcelWriter(getOutputStream(fileName, response), ExcelTypeEnum.XLSX);
//获取名称
List<String> valveNameList = new ArrayList<>();
for (List<Object> objectList : list) {
valveNameList.add((String) objectList.get(0));
}
//去重
List<String> collectList = valveNameList.stream().distinct().collect(Collectors.toList());
// 创建一个表格
if (type == 1) {
Table table = new Table(1);
// 动态添加 表头 headList --> 所有表头行集合
List<List<String>> headList = new ArrayList<>();
// 第 n 行 的表头
for (String s : headerList) {
List<String> headTitle = new ArrayList<>();
headTitle.add(s);
headList.add(headTitle);
}
table.setHead(headList);
String ifValveName;
for (int i = 0; i < collectList.size(); i++) {
List<List<Object>> newList = new ArrayList<>();
for (List<Object> objects : list) {
ifValveName = (String) objects.get(0);
if (collectList.get(i).equals(ifValveName)) {
newList.add(objects);
}
}
Sheet sheet = new Sheet(i + 1);
sheet.setSheetName(collectList.get(i));
excelWriter.write1(newList, sheet, table);
}
} else {
//table
String ifValveName;
for (int i = 0; i < collectList.size(); i++) {
List<List<Object>> newList = new ArrayList<>();
for (List<Object> objects : list) {
ifValveName = (String) objects.get(0);
if (collectList.get(i).equals(ifValveName)) {
newList.add(objects);
}
}
// 创建一个表格
Table table = new Table(i + 1);
// 动态添加 表头 headList --> 所有表头行集合
List<List<String>> headList = new ArrayList<>();
// 第 n 行 的表头
for (String s : headerList) {
List<String> headTitle = new ArrayList<>();
headTitle.add(s);
headList.add(headTitle);
}
table.setHead(headList);
Sheet sheet = new Sheet(1);
sheet.setSheetName("汇总");
excelWriter.write1(newList, sheet, table);
}
}
//清理缓存
excelWriter.finish();
log.info("export success");
return "";
} catch (Exception e) {
e.printStackTrace();
log.info("export fail");
return e.getMessage();
} finally {
// 关闭流
out.close();
}
}
/**
* 导出文件时为Writer生成OutputStream
*
* @param fileName 文件名
* @param response response
* @return 流
*/
private static OutputStream getOutputStream(String fileName, HttpServletResponse response) throws Exception {
try {
fileName = URLEncoder.encode(fileName, "utf-8");
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf8");
response.setHeader("Content-Disposition", "attachment; filename=" + fileName + ".xlsx");
response.setHeader("Pragma", "public");
response.setHeader("Cache-Control", "no-store");
response.addHeader("Cache-Control", "max-age=0");
return response.getOutputStream();
} catch (IOException e) {
throw new Exception("导出excel表格失败!", e);
}
}
用到的工具类1
/**
* 获取response
*/
public static HttpServletResponse getResponse() {
return getRequestAttributes().getResponse();
}
另外有的人找不到pom,其实完全可以去官网粘贴最新的,我把我pom放上
<!--阿里easyExcel-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.6</version>
</dependency>
有问题可以留言或者私信都可以,但是我不可能随时随地都在看csdn,所以肯定会有延迟,只要看到我都会尽量为大家解答