版本建议4.1.2及以上。
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-scratchpad</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
由于poi高版本对低版本并不兼容，所以可能会出现低版本中的某些类，在升级后的高版本中找不到，所以低版本升级时，需考虑对系统的影响面。
<!-- 阿里巴巴读取excel工具包 -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.0-beta1</version>
<exclusions>
<exclusion>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
</exclusion>
<exclusion>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
</exclusion>
<exclusion>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
</exclusion>
</exclusions>
</dependency>
<!--easypoi-base 导入导出的工具包 -->
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>4.1.3</version>
<exclusions>
<exclusion>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
</exclusion>
<exclusion>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
</exclusion>
<exclusion>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>org.jsoup</groupId>
<artifactId>jsoup</artifactId>
<version>1.8.3</version>
</dependency>
如果本身pom文件中，有引入poi，需要使用exclusion，排除easyExcel自身引入的poi，防止冲突。
更习惯于手动引入一下poi，这样可以更加清晰的看到对应的版本。
关于easyexcel和easypoi冲突解决办法，可参考：
https://blog.csdn.net/qq_38254635/article/details/115177103
URL：localhost:8080/springboot-demo/exportExcel
大多数情况下，这种列表导出就已经足够使用了
该方法包含导出文件流，及导出文件直接生成在服务器上。
URL：localhost:8080/springboot-demo/exportExcelZip
该方式适用于一次性需要导出多种文件，采用压缩导出。或者一次性导出大量的数据，并且对速度有一定的要求。
该方法中包含单线程拆分压缩导出，及多线程拆分压缩导出。
URL：localhost:8080/springboot-demo/exportExcelError
该方法一般配合导入使用，数据校验后，对错误的数据进行批注标记处理，方便业务进行对应数据修改重新导入。
URL：localhost:8080/springboot-demo/exportExcelByHtml
如果后端开发想偷懒的话，并且导出的页面是固定的，那就可以直接让前端开发把整个table表格传过来，直接做导出操作。
需要注意的是，导出的table需要增加一个 sheetname 属性，用于导出Excel设置sheet表格的名字。
URL：localhost:8080/springboot-demo/exportExcelByTemplate
有些结论报告，业务报表，可能会涉及固定的模式导出，如果使用poi手写的话，单元格合并，数据填充会比较麻烦，可使用固定的模板导出。
URL：localhost:8080/springboot-demo/exportExcelByTemplateAndList
涉及固定的模板数据，以及列表可以使用该方式
URL：localhost:8080/springboot-demo/exportExcelByTemplateAndExtendList
涉及表格导出，并且表格上下都有固定的参数，需要表格按照数据量填充。
URL：localhost:8080/springboot-demo/exportExcelByTemplateAndSheet
URL：localhost:8080/springboot-demo/exportExcelByTemplateAndSheetMap
根据模板导出，并且当数据超过一定数据，另起一个sheet表格，使用相同的模板继续顺序填充数据。
两种实现效果相同，传参方式不同。
URL：localhost:8080/springboot-demo/importExcel
基础导入操作，如果为null自动补充为空串。
一般配合错误数据导出使用
测试工具类：ExcelUtil.java
package com.util;
import cn.afterturn.easypoi.excel.ExcelXorHtmlUtil;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillConfig;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.time.DateFormatUtils;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Comment;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.StringUtils;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.security.SecureRandom;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.concurrent.ArrayBlockingQueue;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.ThreadPoolExecutor;
import java.util.concurrent.TimeUnit;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;
@Slf4j
public class ExcelUtil {
public static final String INCLINED_ROD = "/";
private static Sheet initSheet;
static {
initSheet = new Sheet(1, 0);
initSheet.setSheetName("sheet");
//设置自适应宽度
initSheet.setAutoWidth(Boolean.TRUE);
}
public static class SheetBean<T> {
private Map<String, Object> beanMap;
private List<T> beanList;
public Map<String, Object> getBeanMap() {
return beanMap;
}
public void setBeanMap(Map<String, Object> beanMap) {
this.beanMap = beanMap;
}
public List<T> getBeanList() {
return beanList;
}
public void setBeanList(List<T> beanList) {
this.beanList = beanList;
}
}
public static class ExcelListener extends AnalysisEventListener {
private List<Object> data = new ArrayList<>();
//逐行解析，object : 当前行的数据
@Override
public void invoke(Object object, AnalysisContext context) {
//当前行 context.getCurrentRowNum()
if (object != null) {
data.add(object);
}
}
//解析完所有数据后会调用该方法
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
//解析结束销毁不用的资源
}
public List<Object> getData() {
return data;
}
public void setData(List<Object> data) {
this.data = data;
}
}
/**
* 设置返回前端文件名
* @param response response
* @param fileName 文件名,包含后缀
* @return OutputStream
* @throws Exception Exception
*/
public static OutputStream getOutputStreamFileName(HttpServletResponse response, String fileName) throws Exception{
response.reset();
String fileType = fileName.split("\\.")[1].toLowerCase();
switch (fileType){
case "doc":
response.setContentType("application/msword");//设置生成的文件类型
break;
case "docx":
response.setContentType("application/msword");//设置生成的文件类型
break;
case "xls":
response.setContentType("application/vnd.ms-excel");//设置生成的文件类型
break;
case "xlsx":
response.setContentType("application/vnd.ms-excel");//设置生成的文件类型
break;
case "pdf":
response.setContentType("application/pdf");//设置生成的文件类型
break;
case "zip":
response.setContentType("application/zip");//设置生成的文件类型
break;
case "dbf":
response.setContentType("application/x-dbf");//设置生成的文件类型
break;
default:
return response.getOutputStream();
}
response.setCharacterEncoding("UTF-8");//设置文件头编码方式和文件名
response.setHeader("Content-Disposition", "attachment;filename=" +
new String(URLEncoder.encode(fileName, "UTF-8").getBytes("utf-8"), "ISO8859-1"));
return response.getOutputStream();
}
/**
* 按指定大小，分隔集合，将集合按规定个数分为n个部分
* @param list 集合
* @param len 拆分个数
* @param <T> 泛型
* @return List<List<T>>
*/
public static <T> List<List<T>> splitList(List<T> list, int len) {
if (list == null || list.isEmpty() || len < 1) {
return Collections.emptyList();
}
List<List<T>> result = new ArrayList<>();
int size = list.size();
int count = (size + len - 1) / len;
for (int i = 0; i < count; i++) {
List<T> subList = list.subList(i * len, ((i + 1) * len > size ? size : len * (i + 1)));
result.add(subList);
}
return result;
}
/**
* 获取文件输出流
* @param filePath 文件路径，不需加/
* @param fileName 文件名称
* @return 输出流
* @throws FileNotFoundException file not found
*/
public static OutputStream getFileOutputStream(String filePath, String fileName) throws FileNotFoundException {
return new FileOutputStream(filePath + INCLINED_ROD + fileName);
}
/**
* 获取文件输出流
* @param filePathAndName 文件路径+文件名称
* @return 输出流
* @throws FileNotFoundException file not found
*/
public static OutputStream getFileOutputStream(String filePathAndName) throws FileNotFoundException {
return new FileOutputStream(filePathAndName);
}
/**
* 生成Excel表格
* @param outputStream 流
* @param data 数据
* @param head 表头
*/
public static void writeExcelByList(OutputStream outputStream, List<List<Object>> data, List<String> head){
writeExcelByList(outputStream, data, head,null);
}
/**
* 生成Excel表格，生成文件
* @param filePath 文件路径
* @param fileName 文件名称
* @param data 数据
* @param head 表头
*/
public static void writeExcelByList(String filePath, String fileName, List<List<Object>> data, List<String> head) throws Exception{
writeExcelByList(getFileOutputStream(filePath, fileName), data, head,null);
}
/**
* 生成Excel表格
* @param outputStream 流
* @param data 数据
* @param head 表头
* @param sheet sheet
*/
public static void writeExcelByList(OutputStream outputStream, List<List<Object>> data, List<String> head, Sheet sheet){
sheet = (sheet != null) ? sheet : initSheet;
if(head != null){
List<List<String>> list = new ArrayList<>();
head.forEach(h -> list.add(Collections.singletonList(h)));
sheet.setHead(list);
}
ExcelWriter writer = null;
try {
writer = EasyExcelFactory.getWriter(outputStream);
writer.write1(data,sheet);
} catch (Exception e) {
log.error("Excel File Generation Failed", e);
} finally {
try {
if(writer != null){
writer.finish();
}
if(outputStream != null){
outputStream.close();
}
} catch (IOException e) {
log.error("Stream Close Failed", e);
}
}
}
/**
* 生成压缩后的Excel压缩包，单线程拆分压缩导出
* @param response 用于输入zip输出流
* @param filePath 文件在服务器生成的路径，临时路径，文件会删除
* @param fileName 文件生成的名称
* @param data excel文件的表格内容
* @param head excel文件的表头内容
* @param splitCount 列表切分阈值
* @throws Exception 异常
*/
public static void writeExcelZipByList(HttpServletResponse response, String filePath, String fileName, String fileType,
List<List<Object>> data, List<String> head, Integer splitCount) throws Exception{
List<List<List<Object>>> lists = splitList(data, splitCount);
filePath = filePath + INCLINED_ROD + DateFormatUtils.format(new Date(), "yyyyMMddHHmmssSSS") +
(new SecureRandom().nextInt(1000));
for(int i = 0; i < lists.size(); i++){
createFilePath(filePath);
OutputStream fileOutputStream = getFileOutputStream(filePath, (fileName + "(" + i + ")" + "." + fileType));
writeExcelByList(fileOutputStream, lists.get(i), head, null);
}
zipDateFile(response, filePath, fileName + ".zip", true);
deleteFilePath(new File(filePath));
}
/**
* 生成压缩后的Excel压缩包，使用多线程拆分压缩导出
* @param response 用于输入zip输出流
* @param filePath 文件在服务器生成的路径，临时路径，文件会删除
* @param fileName 文件生成的名称
* @param data excel文件的表格内容
* @param head excel文件的表头内容
* @param splitCount 列表切分阈值
* @throws Exception 异常
*/
public static void writeExcelZipByListThread(HttpServletResponse response, String filePath, String fileName, String fileType,
List<List<Object>> data, List<String> head, Integer splitCount) throws Exception {
List<List<List<Object>>> lists = splitList(data, splitCount);
final String actualFilePath = filePath + INCLINED_ROD + DateFormatUtils.format(new Date(), "yyyyMMddHHmmssSSS") +
(new SecureRandom().nextInt(1000));
Thread thread = new Thread(() -> {
new ExcelUtil().writeExcelPool(actualFilePath, fileName, fileType, lists, head);
});
thread.start();
thread.join();
zipDateFile(response, actualFilePath, fileName + ".zip", true);
deleteFilePath(new File(actualFilePath));
}
//批量生成文件线程池
private void writeExcelPool(String filePath, String fileName, String fileType, List<List<List<Object>>> lists, List<String> head) {
Integer basePoolSize = lists.size();
ExecutorService pool = new ThreadPoolExecutor(basePoolSize, 2 * basePoolSize, basePoolSize, TimeUnit.MILLISECONDS,
new ArrayBlockingQueue<Runnable>(1), Executors.defaultThreadFactory(), new ThreadPoolExecutor.CallerRunsPolicy());
for(int i = 0; i < lists.size(); i++){
pool.execute(new writeExcelThread(i, filePath, fileName, fileType, lists.get(i), head));
}
try {
pool.shutdown();
boolean loop = true;
do {
loop = !pool.awaitTermination(200, TimeUnit.MILLISECONDS);
} while (loop);
} catch (InterruptedException e) {
log.error("Current Thread Pool Error", e);
}
}
//生成文件线程
private class writeExcelThread implements Runnable {
private Integer index;
private String filePath;
private String fileName;
private String fileType;
private List<List<Object>> list;
private List<String> head;
private writeExcelThread(Integer index, String filePath, String fileName, String fileType, List<List<Object>> list, List<String> head) {
this.index = index;
this.filePath = filePath;
this.fileName = fileName;
this.fileType = fileType;
this.list = list;
this.head = head;
}
@Override
public void run() {
try {
createFilePath(filePath);
OutputStream fileOutputStream = getFileOutputStream(filePath, (fileName + "(" + index + ")" + "." + fileType));
writeExcelByList(fileOutputStream, list, head, null);
} catch (Exception e){
log.error("Current Thread Write Excel Error", e);
}
}
}
/**
* 生成错误Excel表格
* @param outputStream 流
* @param data 数据
* @param head 表头
* @throws IOException 文件生成IO异常
*/
@SuppressWarnings("unchecked")
public static void writeExcelByError(OutputStream outputStream, List<List<Object>> data, List<Object> head) throws IOException{
Workbook workbook = new XSSFWorkbook();
org.apache.poi.ss.usermodel.Sheet sheet = (org.apache.poi.ss.usermodel.Sheet) workbook.createSheet("sheet");
if (data != null) {
CreationHelper factory = workbook.getCreationHelper();
data.add(0, head);
//遍历行数据
for (int i = 0; i < data.size(); i++) {
Row row = sheet.createRow(i);
List<Object> columns = data.get(i);
Integer columnsSize = columns.size();
Map<Integer,String> map = null;
if(i > 0){
columnsSize = columnsSize - 1;
map = (Map<Integer, String>) columns.get(columns.size() - 1);
}
//遍历列数据
for (int j = 0; j < columnsSize; j++) {
Cell cell = row.createCell(j);
Object columnsObject = columns.get(j);
XSSFCellStyle bodyStyle = (XSSFCellStyle) workbook.createCellStyle();
bodyStyle.setBorderBottom(BorderStyle.THIN);//下边框
bodyStyle.setBorderLeft(BorderStyle.THIN);//左边框
bodyStyle.setBorderRight(BorderStyle.THIN);//右边框
bodyStyle.setBorderTop(BorderStyle.THIN);//上边框
bodyStyle.setWrapText(true);//自动换行
bodyStyle.setAlignment(HorizontalAlignment.CENTER); // 垂直居中
bodyStyle.setVerticalAlignment(VerticalAlignment.CENTER);//水平居中
if(i == 0){
org.apache.poi.ss.usermodel.Font font = workbook.createFont();
font.setBold(true);
font.setFontHeightInPoints((short) 14);
bodyStyle.setFont(font);//设置标题字体放大
}
if(null != map && null != map.get(j)){//当前列有错误信息
bodyStyle.setFillBackgroundColor(IndexedColors.YELLOW.index);//设置背景颜色
bodyStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);//设置前景填充样式
bodyStyle.setFillForegroundColor(IndexedColors.RED.getIndex());//前景填充
Comment comment = sheet.createDrawingPatriarch().createCellComment(factory.createClientAnchor());
RichTextString richTextString = factory.createRichTextString(map.get(j));
comment.setString(richTextString);//添加批注
comment.setAuthor("admin");//添加作者
cell.setCellComment(comment);
}
cell.setCellStyle(bodyStyle);
if(null == columnsObject || "".equals(columnsObject)) {
continue;
}
cell.setCellValue(columnsObject.toString());//设置单元格的值
byte[] columnsByte = columnsObject.toString().getBytes();
if(columnsByte.length > 8 && columnsByte.length < 255){// 设置单元格宽度
if(((columnsByte.length) * 256) > sheet.getColumnWidth(j)){
sheet.setColumnWidth(j, (columnsByte.length) * 256);
}
} else if(columnsByte.length >= 255){
sheet.setColumnWidth(j, 255 * 256);
}
}
}
}
workbook.write(outputStream);
}
/**
* 根据html元素导出文件
* 注意事项：
* 1、使用该方法需要引入pom。groupId：org.jsoup；artifactId：jsoup；version:1.8.3或以上版本
* 2、html字符串表格需要配置属性。sheetname=""，配置的内容，即导出的Excel中sheet名称
* html 参考参数
<table sheetname="sheet名称" style="table-layout:fixed; word-wrap:break-word; word-break:break-all">
<thead> <tr>
<th style="text-align: center; width:33%;">序号</th>
<th style="text-align: center; width:33%;">编号</th>
<th style="text-align: center; width:34%;">名称</th>
</tr> </thead>
<tbody> <tr> <td style="text-align: center; width:33%;">id</td>
<td style="text-align: center; width:33%;">code</td>
<td style="text-align: center; width:34%;">name</td>
</tr> </tbody>
</table>
* @param response response
* @param fileName 文件名
* @param html html元素
* @throws Exception Exception
*/
public static void writeExcelByHtml(HttpServletResponse response, String fileName, String html) throws Exception{
OutputStream outputStream = getOutputStreamFileName(response, fileName);
Workbook workbook = ExcelXorHtmlUtil.htmlToExcel(html, ExcelType.HSSF);
workbook.write(outputStream);
}
/**
* 导出Excel单模板单元素填充
* @param templateFile 模板地址加模板名称
* @param out 流
* @param data 单数据
*/
public static void writeExcelByTemplate(String templateFile, OutputStream out, Object data){
EasyExcel.write(out).withTemplate(templateFile).sheet().doFill(data);
}
/**
* 导出Excel单模板单元素和多列混合
* @param templateFile 模板地址加模板名称
* @param out 流
* @param data Object 对象值，字段名与模板匹配
* @param dataList 列表集合
*/
public static <T> void writeExcelByTemplate(String templateFile, OutputStream out, Object data, List<T> dataList){
ExcelWriter build = EasyExcel.write(out).withTemplate(templateFile).build();
WriteSheet sheet = EasyExcel.writerSheet().build();
build.fill(dataList, sheet).fill(data, sheet).finish();
}
/**
* 导出Excel单模板单元素和多列混合
* @param templateFile 模板地址加模板名称
* @param out 流
* @param data 单元素，Key：配置的元素，Object：数据值
* @param dataList 列表集合
*/
public static <T> void writeExcelByTemplate(String templateFile, OutputStream out, Map<String, Object> data, List<T> dataList){
ExcelWriter build = EasyExcel.write(out).withTemplate(templateFile).build();
WriteSheet sheet = EasyExcel.writerSheet().build();
build.fill(dataList, sheet).fill(data, sheet).finish();
}
/**
* 导出Excel单个和多列混合,移动行填充
* （模板附带表头表尾，中间列表自动扩充）
* @param templateFile 模板地址加模板名称
* @param out 流
* @param data 单元素，Key：配置的元素，Object：数据值
* @param dataList 列表集合
*/
public static <T> void writeExcelByTemplateRows(String templateFile, OutputStream out, Map<String,Object> data, List<T> dataList){
ExcelWriter build = EasyExcel.write(out).withTemplate(templateFile).build();
WriteSheet sheet = EasyExcel.writerSheet().build();
FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
build.fill(dataList, fillConfig, sheet).fill(data,sheet).finish();
}
/**
* 根据模板导出多个sheet页面
* 说明：该方法，data和dataList，元素的list下标需对应。即大小需相等
* @param templateFile 模板地址
* @param out 输出流
* @param data 单个填充数据源
* @param dataList 便利填充数据源
* @param sheetNum sheet页数量
* @param flag 是否复制填充
*/
public static void writeExcelByTemplate(String templateFile, OutputStream out, List<Map<String,Object>> data,
List<List<Object>> dataList, int sheetNum, boolean flag){
//根据模板构造输出Excel
ExcelWriter build = EasyExcel.write(out).withTemplate(templateFile).build();
// 通过上下文获取excel对象,不能使用原始对象，SXSSF poi中没有实现copy方法
WriteWorkbookHolder writeWorkbookHolder = build.writeContext().writeWorkbookHolder();
Workbook workbook = build.writeContext().writeWorkbookHolder().getCachedWorkbook();
//copy sheet页,排除本模板中已有的页码
for(int i = 0; i < (sheetNum -1); i++){
workbook.cloneSheet(i);
}
//更新workbook
writeWorkbookHolder.setWorkbook(workbook);
FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
for(int i = 0; i < sheetNum; i++){
Map<String, Object> cellMap = data.get(i);
List<Object> fillList = dataList.get(i);
WriteSheet sheet = EasyExcel.writerSheet(i).build();
sheet.setSheetName(String.valueOf(i + 1));
if(flag) {
build.fill(fillList, fillConfig, sheet);
} else {
build.fill(fillList, sheet);
}
build.fill(cellMap, sheet);
}
build.finish();
}
/**
* 根据模板导出多个sheet页面
* @param templateFile 模板地址
* @param out 输出流
* @param map 单个填充数据源 Key从0开始
* @param flag 是否复制填充
*/
public static void writeExcelByTemplate(String templateFile, OutputStream out, Map<Integer, SheetBean> map, boolean flag){
//根据模板构造输出Excel
ExcelWriter build = EasyExcel.write(out).withTemplate(templateFile).build();
// 通过上下文获取excel对象,不能使用原始对象，SXSSF poi中没有实现copy方法
WriteWorkbookHolder writeWorkbookHolder = build.writeContext().writeWorkbookHolder();
Workbook workbook = build.writeContext().writeWorkbookHolder().getCachedWorkbook();
//copy sheet页,排除本模板中已有的页码
if(null == map || map.size() == 0) return;
for(int i = 0; i < (map.size() -1); i++){
workbook.cloneSheet(i);
}
//更新workbook
writeWorkbookHolder.setWorkbook(workbook);
FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
int sheetNo = 0;
for (Map.Entry<Integer, SheetBean> entry : map.entrySet()) {
SheetBean sheetBean = entry.getValue();
Map cellMap = sheetBean.getBeanMap();
List fillList = sheetBean.getBeanList();
WriteSheet sheet = EasyExcel.writerSheet(sheetNo).build();
sheet.setSheetName(entry.getKey().toString());
if(flag) {
build.fill(fillList, fillConfig, sheet);
} else {
build.fill(fillList, sheet);
}
build.fill(cellMap, sheet);
sheetNo++;
}
build.finish();
}
/**
* 读取少于1000行数据
* @param inputStream 流
* @return List<Object>
*/
public static List<Object> readLessThan1000Row(InputStream inputStream){
return readLessThan1000Row(inputStream, null);
}
/**
* 读小于1000行数据, 带样式
* @param inputStream 流
* @param sheet initSheet ：
* sheetNo: sheet页码，默认为1
* headLineMun: 从第几行开始读取数据，默认为0, 表示从第一行开始读取
* clazz: 返回数据List<Object> 中Object的类名
* @return List<Object>
*/
public static List<Object> readLessThan1000Row(InputStream inputStream, Sheet sheet){
sheet = sheet != null ? sheet : initSheet;
InputStream fileStream = null;
try {
fileStream = inputStream;
return EasyExcelFactory.read(fileStream, sheet);
} catch (Exception e) {
log.error("File Read Failed", e);
} finally {
try {
if(fileStream != null){
fileStream.close();
}
} catch (IOException e) {
log.error("Stream Close Failed", e);
}
}
return null;
}
/**
* 读大于1000行数据
* @param inputStream 流
* @return List<Object>
*/
public static List<Object> readMoreThan1000Row(InputStream inputStream){
return readMoreThan1000Row(inputStream, null);
}
/**
* 读大于1000行数据, 带样式
* @param inputStream 流
* @return List<Object>
*/
public static List<Object> readMoreThan1000Row(InputStream inputStream, Sheet sheet){
sheet = sheet != null ? sheet : initSheet;
InputStream fileStream = null;
try {
fileStream = inputStream;
ExcelListener excelListener = new ExcelUtil.ExcelListener();
EasyExcelFactory.readBySax(fileStream, sheet, excelListener);
return excelListener.getData();
} catch (Exception e) {
log.error("File Read Failed", e);
} finally {
try {
if(fileStream != null){
fileStream.close();
}
} catch (IOException e) {
log.error("Stream Close Failed", e);
}
}
return null;
}
/**
* List转String数组
* @param list list集合
* @return 数组
*/
public static String[] getStringArray(List<Object> list){
if(null == list || list.size() == 0){
return new String []{};
}
List<String> array = new ArrayList<>();
for(int i = 0; i < list.size(); i++){
Object obj = list.get(i);
if(StringUtils.isEmpty(obj)){
array.add("");
} else {
array.add(obj.toString());
}
}
return array.toArray(new String[list.size()]);
}
/**
* 压缩文件
* @param response response
* @param filePath 文件路径
* @param fileName 压缩生成文件名
* @param deleteSourceFile 是否删除原文件
* @throws IOException IOException
*/
public static void zipDateFile(HttpServletResponse response, String filePath, String fileName, boolean deleteSourceFile) throws Exception {
if (StringUtils.isEmpty(filePath) || !new File(filePath).exists()) return;
zipDateFile(response, getAllFile(filePath), fileName, deleteSourceFile);
}
/**
* 压缩文件
* @param response response
* @param fileList 文件集合
* @param fileName 压缩生成文件名
* @param deleteSourceFile 是否删除原文件
* @throws IOException IOException
*/
public static void zipDateFile(HttpServletResponse response, List<File> fileList, String fileName, boolean deleteSourceFile) throws Exception {
getOutputStreamFileName(response, fileName);
ServletOutputStream servletOutputStream = response.getOutputStream();
ZipOutputStream zipOutputStream = new ZipOutputStream(servletOutputStream);
zipFile(fileList, zipOutputStream, deleteSourceFile);
try {
zipOutputStream.close();
} catch (IOException e) {
log.error("Stream Close Failed", e);
}
}
/**
* 压缩导出
* @param fileList 文件列表
* @param zipOutputStream zip流
* @param deleteSourceFile 是否删除原文件
* @throws IOException IOException
*/
public static void zipFile(List<File> fileList, ZipOutputStream zipOutputStream, boolean deleteSourceFile) throws IOException {
byte[] buffer = new byte[1024];
for (File file : fileList) {
if (file.exists()) {
if (file.isFile()) {
try (BufferedInputStream bis = new BufferedInputStream(new FileInputStream(file));) {
zipOutputStream.putNextEntry(new ZipEntry(file.getName()));
int size = 0;
while ((size = bis.read(buffer)) > 0) {
zipOutputStream.write(buffer, 0, size);
}
zipOutputStream.closeEntry();
} finally {
if(deleteSourceFile) file.delete();
}
} else {
File[] files = file.listFiles();
if(null == files) continue;
List<File> childrenFileList = Arrays.asList(files);
zipFile(childrenFileList, zipOutputStream, deleteSourceFile);
}
}
}
}
/**
* 获取指定文件夹下所有文件，不含文件夹里的文件
* @param filePath 文件路径
* @return fileList
*/
public static List<File> getAllFile(String filePath) {
if (StringUtils.isEmpty(filePath)) return null;
return getAllFile(new File(filePath));
}
/**
* 获取指定文件夹下所有文件，不含文件夹里的文件
* @param dirFile 文件夹
* @return fileList
*/
public static List<File> getAllFile(File dirFile) {
// 如果文件夹不存在或着不是文件夹，则返回 null
if (Objects.isNull(dirFile) || !dirFile.exists() || dirFile.isFile()){
return null;
}
File[] childrenFiles = dirFile.listFiles();
if (Objects.isNull(childrenFiles) || childrenFiles.length == 0){
return null;
}
List<File> files = new ArrayList<>();
for (File childFile : childrenFiles) {
// 如果是文件，直接添加到结果集合
if (childFile.isFile()) {
files.add(childFile);
}
//以下几行代码取消注释后可以将所有子文件夹里的文件也获取到列表里
// else {
// // 如果是文件夹，则将其内部文件添加进结果集合
// List<File> cFiles = getAllFile(childFile);
// if (Objects.isNull(cFiles) || cFiles.isEmpty()) continue;
// files.addAll(cFiles);
// }
}
return files;
}
public static boolean createFilePath(String path){
try {
File filePath = new File(path);
if (!filePath.exists()) {
if(!filePath.mkdirs()){
return false;
}
}
} catch (Exception e) {
log.error("Error Creating Folder On Server", e);
return false;
}
return true;
}
public static boolean deleteFilePath(File file) {
if (file.isDirectory()) {
String[] children = file.list();
if(null != children && children.length > 0){
File file1 = null;
//递归删除目录中的子目录下
for(String str : children){
file1 = new File(file, str);
log.info(file1.getPath());
deleteFilePath(file1);
}
}
}
return file.delete();
}
}
测试实体类：ExcelVO.java
package com.bean.vo;
import java.util.List;
import java.util.Map;
public class ExcelVO {
private Integer id;
private String code;
private String name;
private Map<Integer, String> errorMap;
private Map<String, Object> templateMap;
private List<Object> templateList;
private String html;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getCode() {
return code;
}
public void setCode(String code) {
this.code = code;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Map<Integer, String> getErrorMap() {
return errorMap;
}
public void setErrorMap(Map<Integer, String> errorMap) {
this.errorMap = errorMap;
}
public Map<String, Object> getTemplateMap() {
return templateMap;
}
public void setTemplateMap(Map<String, Object> templateMap) {
this.templateMap = templateMap;
}
public List<Object> getTemplateList() {
return templateList;
}
public void setTemplateList(List<Object> templateList) {
this.templateList = templateList;
}
public String getHtml() {
return html;
}
public void setHtml(String html) {
this.html = html;
}
public ExcelVO() {
}
public ExcelVO(Integer id, String code, String name) {
this.id = id;
this.code = code;
this.name = name;
}
}
测试实体类：ExcelDTO.java
package com.bean.vo;
public class ExcelDTO extends ExcelVO {
public ExcelDTO() {
}
public ExcelDTO(Integer id, String code, String name) {
super(id, code, name);
}
}
测试接口类：IExcelService.java
package com.service;
import com.bean.Result;
import com.web.excel.vo.ExcelVO;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
public interface IExcelService {
void exportExcel(HttpServletResponse response, ExcelVO excelVO, boolean zip) throws Exception;
void exportExcelError(HttpServletResponse response, ExcelVO excelVO) throws Exception;
void exportExcelByHtml(HttpServletResponse response, ExcelVO excelVO) throws Exception;
void exportExcelByTemplate(HttpServletResponse response, ExcelVO excelVO) throws Exception;
void exportExcelByTemplateAndList(HttpServletResponse response, ExcelVO excelVO) throws Exception;
void exportExcelByTemplateAndExtendList(HttpServletResponse response, ExcelVO excelVO) throws Exception;
void exportExcelByTemplateAndSheet(HttpServletResponse response, ExcelVO excelVO) throws Exception;
void exportExcelByTemplateAndSheetMap(HttpServletResponse response, ExcelVO excelVO) throws Exception;
Result importExcel(MultipartFile multipartFile, HttpServletResponse response) throws Exception;
}
测试接口实现类：ExcelServiceImpl.java
package com.service.impl;
import com.bean.Result;
import com.util.ExcelUtil;
import com.web.excel.service.IExcelService;
import com.web.excel.vo.ExcelDTO;
import com.web.excel.vo.ExcelVO;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.BufferedInputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.UUID;
@Service
public class ExcelServiceImpl implements IExcelService {
public static Log log = LogFactory.getLog(ExcelServiceImpl.class);
/**
* 导出Excel基础方法
* @param response response
* @param excelVO 接口查询参数，用于查询业务数据
* @throws Exception Exception
*/
@Override
public void exportExcel(HttpServletResponse response, ExcelVO excelVO, boolean zip) throws Exception {
//查询数据
List<ExcelDTO> exportResult = this.getResultList(excelVO);
//导出数据
this.exportExcel(response, exportResult, zip);
}
private List<ExcelDTO> getResultList(ExcelVO excelVO) {
List<ExcelDTO> excelDTOList = new ArrayList<>();
ExcelDTO excelDTO;
Integer count = StringUtils.isEmpty(excelVO.getId()) ? 10 : excelVO.getId();
for(int i = 0; i < count; i++){
excelDTO = new ExcelDTO(i + 1, getValue(), getValue());
excelDTOList.add(excelDTO);
}
return excelDTOList;
}
private void exportExcel(HttpServletResponse response, List<ExcelDTO> excelDTOList, boolean zip) throws Exception {
if (!Objects.isNull(excelDTOList) && excelDTOList.size() > 0) {
List<List<Object>> data = new ArrayList<>();
List<String> head = Arrays.asList("序号", "编码", "名称");
List<Object> list;
for (ExcelDTO excel : excelDTOList) {
list = new ArrayList<>();
list.add(!StringUtils.isEmpty(excel.getId()) ? excel.getId() : "");
list.add(!StringUtils.isEmpty(excel.getCode()) ? excel.getCode() : "");
list.add(!StringUtils.isEmpty(excel.getName()) ? excel.getName() : "");
data.add(list);
}
if(zip){
//单线程拆分压缩导出
// ExcelUtil.writeExcelZipByList(response, "E:\\war\\a", "a", "xlsx", data, head, 1000);
//多线程拆分压缩导出
ExcelUtil.writeExcelZipByListThread(response, "E:\\war\\a", "a", "xlsx", data, head, 100);
} else {
// ExcelUtil.writeExcelByList(ExcelUtil.getOutputStreamFileName(response, "exportExcel.xlsx"), data, head);
ExcelUtil.writeExcelByList("E:\\war\\a", "exportExcel.xlsx", data, head);
}
}
}
/**
* 导出Excel方法，含错误Map，自动标注
* @param response response
* @param excelVO 接口查询参数，用于查询业务数据
* @throws Exception Exception
*/
@Override
public void exportExcelError(HttpServletResponse response, ExcelVO excelVO) throws Exception {
//查询数据
List<ExcelDTO> exportResult = this.getResultListError(excelVO);
//设置响应文件类型
OutputStream outputStream = ExcelUtil.getOutputStreamFileName(response, "exportExcelError.xlsx");
//导出数据
this.exportExcelError(outputStream, exportResult);
}
private List<ExcelDTO> getResultListError(ExcelVO excelVO) {
List<ExcelDTO> excelDTOList = new ArrayList<>();
ExcelDTO excelDTO;
Map<Integer,String> resultMap;
Integer count = StringUtils.isEmpty(excelVO.getId()) ? 10 : excelVO.getId();
for(int i = 0; i < count; i++){
excelDTO = new ExcelDTO(i + 1, getValue(), getValue());
resultMap = new HashMap<Integer,String>();//错误信息map集合
if(excelDTO.getId() > 10){
resultMap.put(0, "大于10");
}
if(excelDTO.getCode().contains("5")){
resultMap.put(1, "包含5");
}
if(excelDTO.getName().contains("a")){
resultMap.put(2, "包含a");
}
excelDTO.setErrorMap(resultMap);
excelDTOList.add(excelDTO);
}
return excelDTOList;
}
private void exportExcelError(OutputStream outputStream, List<ExcelDTO> excelDTOList) throws Exception {
if (!Objects.isNull(excelDTOList) && excelDTOList.size() > 0) {
List<List<Object>> data = new ArrayList<>();
List<Object> head = Arrays.asList("序号", "编码", "名称");
List<Object> list;
for (ExcelDTO excel : excelDTOList) {
list = new ArrayList<>();
list.add(!StringUtils.isEmpty(excel.getId()) ? excel.getId() : "");
list.add(!StringUtils.isEmpty(excel.getCode()) ? excel.getCode() : "");
list.add(!StringUtils.isEmpty(excel.getName()) ? excel.getName() : "");
list.add(excel.getErrorMap());
data.add(list);
}
ExcelUtil.writeExcelByError(outputStream, data, head);
}
}
/**
* 根据html元素导出文件
* @param response response
* @param excelVO 参数html
* @throws Exception Exception
*/
@Override
public void exportExcelByHtml(HttpServletResponse response, ExcelVO excelVO) throws Exception {
ExcelUtil.writeExcelByHtml(response, "excelByHtml.xlsx", excelVO.getHtml());
}
/**
* 根据模板导出表格
* @param response response
* @param excelVO 接口查询参数，用于查询业务数据
* @throws Exception Exception
*/
@Override
public void exportExcelByTemplate(HttpServletResponse response, ExcelVO excelVO) throws Exception {
ExcelDTO excelDTO = new ExcelDTO(StringUtils.isEmpty(excelVO.getId()) ? 10 : excelVO.getId(), getValue(), getValue());
OutputStream outputStream = ExcelUtil.getOutputStreamFileName(response, "simpleTemplateExcel.xlsx");
ExcelUtil.writeExcelByTemplate("/app/template/simpleTemplate.xlsx", outputStream, excelDTO);
}
/**
* 根据模板导出表格
* @param response response
* @param excelVO 接口查询参数，用于查询业务数据
* @throws Exception Exception
*/
@Override
public void exportExcelByTemplateAndList(HttpServletResponse response, ExcelVO excelVO) throws Exception {
List<ExcelDTO> exportResult = this.getResultList(excelVO);
Map<String,Object> data = new HashMap<>();
data.put("id", StringUtils.isEmpty(excelVO.getId()) ? 10 : excelVO.getId());
data.put("code", getValue());
data.put("name", getValue());
OutputStream outputStream = ExcelUtil.getOutputStreamFileName(response, "exportExcelByTemplateAndList.xlsx");
ExcelUtil.writeExcelByTemplate("/app/template/simpleTemplateList.xlsx", outputStream, data, exportResult);
/*ExcelDTO excelDTO = new ExcelDTO();
excelDTO.setId(StringUtils.isEmpty(excelVO.getId()) ? 10 : excelVO.getId());
excelDTO.setCode(getValue());
excelDTO.setName(getValue());
ExcelUtil.writeExcelByTemplate("/app/template/simpleTemplateList.xlsx", outputStream, excelDTO, exportResult);*/
}
/**
* 根据模板导出表格（模板附带表头表尾，中间列表自动扩充）
* @param response response
* @param excelVO 接口查询参数，用于查询业务数据
* @throws Exception Exception
*/
@Override
public void exportExcelByTemplateAndExtendList(HttpServletResponse response, ExcelVO excelVO) throws Exception {
List<ExcelDTO> exportResult = this.getResultList(excelVO);
Map<String,Object> data = new HashMap<>();
data.put("id", StringUtils.isEmpty(excelVO.getId()) ? 10 : excelVO.getId());
data.put("code", getValue());
data.put("name", getValue());
OutputStream outputStream = ExcelUtil.getOutputStreamFileName(response, "exportExcelByTemplateAndExtendList.xlsx");
ExcelUtil.writeExcelByTemplateRows("/app/template/simpleTemplateExtendList.xlsx", outputStream, data, exportResult);
}
/**
* 根据模板导出表格,分多个sheet页面
* @param response
* @param excelVO
* @throws Exception
*/
@Override
public void exportExcelByTemplateAndSheet(HttpServletResponse response, ExcelVO excelVO) throws Exception {
List<ExcelDTO> exportResult = this.getResultList(excelVO);
OutputStream outputStream = ExcelUtil.getOutputStreamFileName(response, "exportExcelByTemplateAndSheet.xlsx");
List<Map<String,Object>> data = new ArrayList<>();
List<List<Object>> dataList = new ArrayList<>();
List<List<ExcelDTO>> splitList = ExcelUtil.splitList(exportResult, 10);
for (List<ExcelDTO> line : splitList) {
// 重新放入序号
for(int i = 0 ; i < line.size(); i++){
ExcelDTO excel = line.get(i);
excel.setId(i + 1);
}
Map<String,Object> dataMap = new HashMap<>();
dataMap.put("id", StringUtils.isEmpty(excelVO.getId()) ? 10 : excelVO.getId());
dataMap.put("code", getValue());
dataMap.put("name", getValue());
line.get(0).setTemplateMap(dataMap);
data.add(line.get(0).getTemplateMap());
dataList.add(new ArrayList<>(line));
}
ExcelUtil.writeExcelByTemplate("/app/template/simpleTemplateSheet.xlsx",
outputStream, data, dataList, data.size(),true);
}
/**
* 根据模板导出表格,分多个sheet页面
* @param response response
* @param excelVO excelVO
* @throws Exception Exception
*/
@Override
public void exportExcelByTemplateAndSheetMap(HttpServletResponse response, ExcelVO excelVO) throws Exception {
List<ExcelDTO> exportResult = this.getResultList(excelVO);
Map<String, Object> data = new HashMap<>();
data.put("code", getValue());
data.put("name", getValue());
OutputStream outputStream = ExcelUtil.getOutputStreamFileName(response, "exportExcelByTemplateAndSheet.xlsx");
Map<Integer, ExcelUtil.SheetBean> map = new HashMap<>();
ExcelUtil.SheetBean<ExcelDTO> sheetBean;
List<List<ExcelDTO>> lists = ExcelUtil.splitList(exportResult, 1000);
for (int i = 0; i < lists.size(); i++) {
// 重新放入序号
for (int num = 0; num < lists.get(i).size(); num++) {
ExcelDTO excel = (ExcelDTO) lists.get(i).get(num);
excel.setId(num + 1);
}
sheetBean = new ExcelUtil.SheetBean<ExcelDTO>();
sheetBean.setBeanMap(data);
sheetBean.setBeanList(lists.get(i));
map.put(i, sheetBean);
}
ExcelUtil.writeExcelByTemplate("/app/template/simpleTemplateSheetMap.xlsx", outputStream, map,true);
}
/**
* 导入excel表格
* @param multipartFile 文件
* @param response response
* @return 导入结果
* @throws Exception Exception
*/
@SuppressWarnings("unchecked")
@Override
public Result importExcel(MultipartFile multipartFile, HttpServletResponse response) throws Exception {
InputStream inputStream = new BufferedInputStream(multipartFile.getInputStream());
List<Object> objects = ExcelUtil.readLessThan1000Row(inputStream);
//排除第一行
for(int i = 1; i < objects.size(); i++){
String[] split = ExcelUtil.getStringArray((List<Object>) objects.get(i));
log.info(Arrays.toString(split));
}
return null;
}
private String getValue(){
return UUID.randomUUID().toString().substring(0, 20);
}
}
测试Controller类：ExcelController.java
package com.controller;
import com.alibaba.fastjson.JSON;
import com.bean.Result;
import com.web.excel.service.IExcelService;
import com.web.excel.vo.ExcelVO;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;
import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
@Slf4j
@Controller
public class ExcelController {
@Resource
private IExcelService excelService;
@RequestMapping("/exportExcel")
@ResponseBody
public void exportExcel(HttpServletResponse response, @RequestBody String json) throws Exception {
ExcelVO excelVO = JSON.parseObject(json, ExcelVO.class);
excelService.exportExcel(response, excelVO, false);
}
@RequestMapping("/exportExcelZip")
@ResponseBody
public void exportExcelZip(HttpServletResponse response, @RequestBody String json) throws Exception {
ExcelVO excelVO = JSON.parseObject(json, ExcelVO.class);
excelService.exportExcel(response, excelVO, true);
}
@RequestMapping("/exportExcelError")
@ResponseBody
public void exportExcelError(HttpServletResponse response, @RequestBody String json) throws Exception {
ExcelVO excelVO = JSON.parseObject(json, ExcelVO.class);
excelService.exportExcelError(response, excelVO);
}
@RequestMapping("/exportExcelByHtml")
@ResponseBody
public void exportExcelByHtml(HttpServletResponse response, @RequestBody String json) throws Exception {
ExcelVO excelVO = JSON.parseObject(json, ExcelVO.class);
excelService.exportExcelByHtml(response, excelVO);
}
@RequestMapping("/exportExcelByTemplate")
@ResponseBody
public void exportExcelByTemplate(HttpServletResponse response, @RequestBody String json) throws Exception {
ExcelVO excelVO = JSON.parseObject(json, ExcelVO.class);
excelService.exportExcelByTemplate(response, excelVO);
}
@RequestMapping("/exportExcelByTemplateAndList")
@ResponseBody
public void exportExcelByTemplateAndList(HttpServletResponse response, @RequestBody String json) throws Exception {
ExcelVO excelVO = JSON.parseObject(json, ExcelVO.class);
excelService.exportExcelByTemplateAndList(response, excelVO);
}
@RequestMapping("/exportExcelByTemplateAndExtendList")
@ResponseBody
public void exportExcelByTemplateAndExtendList(HttpServletResponse response, @RequestBody String json) throws Exception {
ExcelVO excelVO = JSON.parseObject(json, ExcelVO.class);
excelService.exportExcelByTemplateAndExtendList(response, excelVO);
}
@RequestMapping("/exportExcelByTemplateAndSheet")
@ResponseBody
public void exportExcelByTemplateAndSheet(HttpServletResponse response, @RequestBody String json) throws Exception {
ExcelVO excelVO = JSON.parseObject(json, ExcelVO.class);
excelService.exportExcelByTemplateAndSheet(response, excelVO);
}
@RequestMapping("/exportExcelByTemplateAndSheetMap")
@ResponseBody
public void exportExcelByTemplateAndSheetMap(HttpServletResponse response, @RequestBody String json) throws Exception {
ExcelVO excelVO = JSON.parseObject(json, ExcelVO.class);
excelService.exportExcelByTemplateAndSheetMap(response, excelVO);
}
@RequestMapping("/importExcel")
@ResponseBody
public Result importExcel(MultipartFile multipartFile, HttpServletResponse response) throws Exception{
return excelService.importExcel(multipartFile, response);
}
}
源码，导出模板，导入模板，pom文件等。
https://download.csdn.net/download/qq_38254635/86782421
https://pan.baidu.com/s/1K3hb_OxJLGdUbnI4Qu-3pw
提取码: vxxn
既然可以多线程导出，并压缩下载。又可以多sheet页面导出。
那是否可以多线程导出多sheet页面呢？
验证之前，我认为并不可以，因为会对同一个文件进行IO操作，试试吧！
建一下测试类demo，直接堆代码吧。类：TextController.java
package com.web.excel.controller;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillConfig;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import com.alibaba.fastjson.JSON;
import com.util.ExcelUtil;
import com.web.excel.vo.ExcelDTO;
import com.web.excel.vo.ExcelVO;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.stereotype.Controller;
import org.springframework.util.StringUtils;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;
import java.util.concurrent.ArrayBlockingQueue;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.ThreadPoolExecutor;
import java.util.concurrent.TimeUnit;
@SuppressWarnings("all")
@Slf4j
@Controller
public class TextController {
@RequestMapping("/exportCommonSheet")
@ResponseBody
public void exportExcelByTemplateAndSheet(HttpServletResponse response, @RequestBody String json) throws Exception {
ExcelVO excelVO = JSON.parseObject(json, ExcelVO.class);
//生成随机列表
List<ExcelDTO> exportResult = new ArrayList<>();
ExcelDTO excelDTO;
Integer count = StringUtils.isEmpty(excelVO.getId()) ? 10 : excelVO.getId();
for(int i = 0; i < count; i++){
excelDTO = new ExcelDTO(i + 1, getValue(), getValue());
exportResult.add(excelDTO);
}
//拆分数据
OutputStream outputStream = ExcelUtil.getOutputStreamFileName(response, "exportExcelByTemplateAndSheet.xlsx");
List<Map<String,Object>> data = new ArrayList<>();
List<List<Object>> dataList = new ArrayList<>();
List<List<ExcelDTO>> splitList = ExcelUtil.splitList(exportResult, 10);
for (List<ExcelDTO> line : splitList) {
// 重新放入序号
for(int i = 0 ; i < line.size(); i++){
ExcelDTO excel = line.get(i);
excel.setId(i + 1);
}
Map<String,Object> dataMap = new HashMap<>();
dataMap.put("id", StringUtils.isEmpty(excelVO.getId()) ? 10 : excelVO.getId());
dataMap.put("code", getValue());
dataMap.put("name", getValue());
line.get(0).setTemplateMap(dataMap);
data.add(line.get(0).getTemplateMap());
dataList.add(new ArrayList<>(line));
}
writeExcelByTemplate("/app/template/simpleTemplateSheet.xlsx",
outputStream, data, dataList, data.size(),true);
}
public static void writeExcelByTemplate(String templateFile, OutputStream out, List<Map<String,Object>> data,
List<List<Object>> dataList, int sheetNum, boolean flag) throws InterruptedException {
//根据模板构造输出Excel
ExcelWriter build = EasyExcel.write(out).withTemplate(templateFile).build();
// 通过上下文获取excel对象,不能使用原始对象，SXSSF poi中没有实现copy方法
WriteWorkbookHolder writeWorkbookHolder = build.writeContext().writeWorkbookHolder();
Workbook workbook = build.writeContext().writeWorkbookHolder().getCachedWorkbook();
//copy sheet页,排除本模板中已有的页码
for(int i = 0; i < (sheetNum -1); i++){
workbook.cloneSheet(i);
}
//更新workbook
writeWorkbookHolder.setWorkbook(workbook);
FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
Thread thread = new Thread(() -> {
new TextController().writeExcelPool(sheetNum, data, dataList, build, fillConfig, flag);
});
thread.start();
thread.join();
}
private void writeExcelPool(Integer sheetNum, List<Map<String,Object>> data, List<List<Object>> dataList, ExcelWriter build, FillConfig fillConfig, boolean flag) {
ExecutorService pool = new ThreadPoolExecutor(sheetNum, 2 * sheetNum, sheetNum, TimeUnit.MILLISECONDS,
new ArrayBlockingQueue<Runnable>(1), Executors.defaultThreadFactory(), new ThreadPoolExecutor.CallerRunsPolicy());
for(int i = 0; i < sheetNum; i++){
pool.execute(new writeExcelThread(i, data.get(i), dataList.get(i), build, fillConfig, flag));
}
try {
pool.shutdown();
boolean loop = true;
do {
loop = !pool.awaitTermination(200, TimeUnit.MILLISECONDS);
} while (loop);
} catch (InterruptedException e) {
log.error("Current Thread Pool Error", e);
}
build.finish();
}
private class writeExcelThread implements Runnable {
private Integer index;
private Map<String, Object> cellMap;
private List<Object> fillList;
private ExcelWriter build;
private FillConfig fillConfig;
private boolean flag;
public writeExcelThread(Integer index, Map<String, Object> cellMap, List<Object> fillList, ExcelWriter build, FillConfig fillConfig, boolean flag) {
this.index = index;
this.cellMap = cellMap;
this.fillList = fillList;
this.build = build;
this.fillConfig = fillConfig;
this.flag = flag;
}
@Override
public void run() {
try {
log.info("Current Thread Index Value：" + index);
WriteSheet sheet = EasyExcel.writerSheet(index).build();
sheet.setSheetName(String.valueOf(index + 1));
if(flag) {
build.fill(fillList, fillConfig, sheet);
} else {
build.fill(fillList, sheet);
}
build.fill(cellMap, sheet);
build.finish();
} catch (Exception e){
log.error("Current Thread Write Excel Error", e);
}
}
}
private String getValue(){
return UUID.randomUUID().toString().substring(0, 20);
}
}
这里为了方便就直接用10为基数拆分sheet页
啊吼，不出所料，还是报错了。
2022-11-02 10:28:05.040 INFO 131292 --- [pool-6-thread-1] com.web.excel.controller.TextController : Current Thread Index Value：0
2022-11-02 10:28:05.041 INFO 131292 --- [pool-6-thread-2] com.web.excel.controller.TextController : Current Thread Index Value：1
2022-11-02 10:28:05.043 INFO 131292 --- [pool-6-thread-5] com.web.excel.controller.TextController : Current Thread Index Value：4
2022-11-02 10:28:05.044 INFO 131292 --- [pool-6-thread-4] com.web.excel.controller.TextController : Current Thread Index Value：3
2022-11-02 10:28:05.044 INFO 131292 --- [pool-6-thread-3] com.web.excel.controller.TextController : Current Thread Index Value：2
2022-11-02 10:28:05.064 ERROR 131292 --- [pool-6-thread-5] com.web.excel.controller.TextController : Current Thread Write Excel Error
java.lang.NullPointerException: null
at org.apache.xmlbeans.impl.store.Cur.next(Cur.java:1420) ~[xmlbeans-3.1.0.jar:na]
at org.apache.xmlbeans.impl.store.Xobj.store_text(Xobj.java:1943) ~[xmlbeans-3.1.0.jar:na]
at org.apache.xmlbeans.impl.values.XmlObjectBase.set_String(XmlObjectBase.java:1132) ~[xmlbeans-3.1.0.jar:na]
at org.apache.xmlbeans.impl.values.XmlObjectBase.setStringValue(XmlObjectBase.java:1725) ~[xmlbeans-3.1.0.jar:na]
at org.openxmlformats.schemas.spreadsheetml.x2006.main.impl.CTCellImpl.setR(Unknown Source) ~[poi-ooxml-schemas-4.1.2.jar:4.1.2]
at org.apache.poi.xssf.usermodel.XSSFCell.updateCellReferencesForShifting(XSSFCell.java:1266) ~[poi-ooxml-4.1.2.jar:4.1.2]
at org.apache.poi.xssf.usermodel.XSSFRow.shift(XSSFRow.java:635) ~[poi-ooxml-4.1.2.jar:4.1.2]
at org.apache.poi.xssf.usermodel.XSSFSheet.shiftCommentsAndRows(XSSFSheet.java:3185) ~[poi-ooxml-4.1.2.jar:4.1.2]
at org.apache.poi.xssf.usermodel.XSSFSheet.shiftRows(XSSFSheet.java:3033) ~[poi-ooxml-4.1.2.jar:4.1.2]
at com.alibaba.excel.write.executor.ExcelWriteFillExecutor.shiftRows(ExcelWriteFillExecutor.java:158) ~[easyexcel-2.2.0-beta1.jar:na]
at com.alibaba.excel.write.executor.ExcelWriteFillExecutor.fill(ExcelWriteFillExecutor.java:115) ~[easyexcel-2.2.0-beta1.jar:na]
at com.alibaba.excel.write.ExcelBuilderImpl.fill(ExcelBuilderImpl.java:84) ~[easyexcel-2.2.0-beta1.jar:na]
at com.alibaba.excel.ExcelWriter.fill(ExcelWriter.java:185) ~[easyexcel-2.2.0-beta1.jar:na]
at com.web.excel.controller.TextController$writeExcelThread.run(TextController.java:136) ~[classes/:na]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [na:1.8.0_131]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [na:1.8.0_131]
at java.lang.Thread.run(Thread.java:748) [na:1.8.0_131]
2022-11-02 10:28:05.065 ERROR 131292 --- [pool-6-thread-2] com.web.excel.controller.TextController : Current Thread Write Excel Error
java.lang.IndexOutOfBoundsException: null
at org.apache.xmlbeans.impl.store.Xobj.removeElement(Xobj.java:2206) ~[xmlbeans-3.1.0.jar:na]
at org.apache.xmlbeans.impl.store.Xobj.remove_element(Xobj.java:2236) ~[xmlbeans-3.1.0.jar:na]
at org.openxmlformats.schemas.spreadsheetml.x2006.main.impl.CTSheetDataImpl.removeRow(Unknown Source) ~[poi-ooxml-schemas-4.1.2.jar:4.1.2]
at org.apache.poi.xssf.usermodel.XSSFSheet.removeOverwritten(XSSFSheet.java:3095) ~[poi-ooxml-4.1.2.jar:4.1.2]
at org.apache.poi.xssf.usermodel.XSSFSheet.shiftRows(XSSFSheet.java:3032) ~[poi-ooxml-4.1.2.jar:4.1.2]
at com.alibaba.excel.write.executor.ExcelWriteFillExecutor.shiftRows(ExcelWriteFillExecutor.java:158) ~[easyexcel-2.2.0-beta1.jar:na]
at com.alibaba.excel.write.executor.ExcelWriteFillExecutor.fill(ExcelWriteFillExecutor.java:115) ~[easyexcel-2.2.0-beta1.jar:na]
at com.alibaba.excel.write.ExcelBuilderImpl.fill(ExcelBuilderImpl.java:84) ~[easyexcel-2.2.0-beta1.jar:na]
at com.alibaba.excel.ExcelWriter.fill(ExcelWriter.java:185) ~[easyexcel-2.2.0-beta1.jar:na]
at com.web.excel.controller.TextController$writeExcelThread.run(TextController.java:136) ~[classes/:na]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [na:1.8.0_131]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [na:1.8.0_131]
at java.lang.Thread.run(Thread.java:748) [na:1.8.0_131]
2022-11-02 10:28:05.071 ERROR 131292 --- [pool-6-thread-4] com.web.excel.controller.TextController : Current Thread Write Excel Error
java.util.ConcurrentModificationException: null
at java.util.TreeMap$NavigableSubMap$SubMapIterator.nextEntry(TreeMap.java:1703) ~[na:1.8.0_131]
at java.util.TreeMap$NavigableSubMap$SubMapEntryIterator.next(TreeMap.java:1751) ~[na:1.8.0_131]
at java.util.TreeMap$NavigableSubMap$SubMapEntryIterator.next(TreeMap.java:1745) ~[na:1.8.0_131]
at java.util.TreeMap$NavigableSubMap$EntrySetView.size(TreeMap.java:1637) ~[na:1.8.0_131]
at java.util.TreeMap$NavigableSubMap.size(TreeMap.java:1507) ~[na:1.8.0_131]
at org.apache.poi.xssf.usermodel.XSSFSheet.createRow(XSSFSheet.java:783) ~[poi-ooxml-4.1.2.jar:4.1.2]
at org.apache.poi.xssf.usermodel.XSSFSheet.createRow(XSSFSheet.java:148) ~[poi-ooxml-4.1.2.jar:4.1.2]
at com.alibaba.excel.write.executor.ExcelWriteFillExecutor.getOneCell(ExcelWriteFillExecutor.java:290) ~[easyexcel-2.2.0-beta1.jar:na]
at com.alibaba.excel.write.executor.ExcelWriteFillExecutor.doFill(ExcelWriteFillExecutor.java:178) ~[easyexcel-2.2.0-beta1.jar:na]
at com.alibaba.excel.write.executor.ExcelWriteFillExecutor.fill(ExcelWriteFillExecutor.java:118) ~[easyexcel-2.2.0-beta1.jar:na]
at com.alibaba.excel.write.ExcelBuilderImpl.fill(ExcelBuilderImpl.java:84) ~[easyexcel-2.2.0-beta1.jar:na]
at com.alibaba.excel.ExcelWriter.fill(ExcelWriter.java:185) ~[easyexcel-2.2.0-beta1.jar:na]
at com.web.excel.controller.TextController$writeExcelThread.run(TextController.java:136) ~[classes/:na]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [na:1.8.0_131]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [na:1.8.0_131]
at java.lang.Thread.run(Thread.java:748) [na:1.8.0_131]
2022-11-02 10:28:05.076 ERROR 131292 --- [pool-6-thread-3] com.web.excel.controller.TextController : Current Thread Write Excel Error
java.util.ConcurrentModificationException: null
at java.util.TreeMap$NavigableSubMap$SubMapIterator.nextEntry(TreeMap.java:1703) ~[na:1.8.0_131]
at java.util.TreeMap$NavigableSubMap$SubMapEntryIterator.next(TreeMap.java:1751) ~[na:1.8.0_131]
at java.util.TreeMap$NavigableSubMap$SubMapEntryIterator.next(TreeMap.java:1745) ~[na:1.8.0_131]
at java.util.TreeMap$NavigableSubMap$EntrySetView.size(TreeMap.java:1637) ~[na:1.8.0_131]
at java.util.TreeMap$NavigableSubMap.size(TreeMap.java:1507) ~[na:1.8.0_131]
at org.apache.poi.xssf.usermodel.XSSFSheet.createRow(XSSFSheet.java:783) ~[poi-ooxml-4.1.2.jar:4.1.2]
at org.apache.poi.xssf.usermodel.XSSFSheet.createRow(XSSFSheet.java:148) ~[poi-ooxml-4.1.2.jar:4.1.2]
at com.alibaba.excel.write.executor.ExcelWriteFillExecutor.getOneCell(ExcelWriteFillExecutor.java:290) ~[easyexcel-2.2.0-beta1.jar:na]
at com.alibaba.excel.write.executor.ExcelWriteFillExecutor.doFill(ExcelWriteFillExecutor.java:178) ~[easyexcel-2.2.0-beta1.jar:na]
at com.alibaba.excel.write.executor.ExcelWriteFillExecutor.fill(ExcelWriteFillExecutor.java:118) ~[easyexcel-2.2.0-beta1.jar:na]
at com.alibaba.excel.write.ExcelBuilderImpl.fill(ExcelBuilderImpl.java:84) ~[easyexcel-2.2.0-beta1.jar:na]
at com.alibaba.excel.ExcelWriter.fill(ExcelWriter.java:185) ~[easyexcel-2.2.0-beta1.jar:na]
at com.web.excel.controller.TextController$writeExcelThread.run(TextController.java:136) ~[classes/:na]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [na:1.8.0_131]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [na:1.8.0_131]
at java.lang.Thread.run(Thread.java:748) [na:1.8.0_131]
大致报错分为以下三种：
1、java.lang.NullPointerException: null
2、java.lang.IndexOutOfBoundsException: null
3、java.util.ConcurrentModificationException: null
其中1、2这两个BUG，都是比较常见的问题了。
来说说3这个问题，字面意思可直接翻译过来，就是并发修改异常。
这个问题其实就是两个人同时修改同一个地方，类似于SVN提交代码，两个人或者多个人，同时对同一个地方做了修改，就会出现冲突问题。
这里的同一个地方，无非就是同一个IO，虽然对sheet进行了细分，但未对IO细分。
假设这里对IO进行细分，再进行IO操作，那其实就回到了多表格生成并压缩导出的代码了。
当然了，如果对细分后的IO进行汇总操作，并汇总进同一个Excel的话，这样是否在效率上会大打折扣呢？
某一方式导出，总会有一个阈值。
当想要突破这个阈值，必定需要更换处理方式，以寻求更高的阈值。
JAVA生成Zip文件并导出：
https://blog.csdn.net/qq_38254635/article/details/127364398
JAVA根据模板生成WORD文件并导出：
https://blog.csdn.net/qq_38254635/article/details/103952823
JAVA根据PDF文件生成图片：
https://blog.csdn.net/qq_38254635/article/details/112029941
JAVA根据模板生成PDF文件并导出：
https://blog.csdn.net/qq_38254635/article/details/103919024
在做导出时，即 二：Excel相关操作 中的 3、错误Excel导出
会提示上述错误，故代码优化如下。
OK，整理到这吧！
如有不正确之处，还望指正！书写不易，觉得有帮助就点个赞吧！☺☺☺