场景:例如导出文件过于大,几百万条。excel最大可就只有100万条,那这时候。我们可以分段处理。然后导出n个excel并压缩一下
maven依赖使用阿里的easyexcel
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.7</version>
<exclusions>
<exclusion>
<artifactId>commons-beanutils</artifactId>
<groupId>commons-beanutils</groupId>
</exclusion>
<exclusion>
<groupId>org.ow2.asm</groupId>
<artifactId>asm</artifactId>
</exclusion>
</exclusions>
</dependency>
实体类实例
@Data
@ApiModel(value="ContainerVO对象", description="容器")
public class ContainerVO implements Serializable {
@ExcelIgnore
@ApiModelProperty(value = "标识")
private String code;
@ExcelProperty("状态")
@ApiModelProperty(value = "状态")
private String status;
@ExcelIgnore
@ApiModelProperty(value = "是否可删除标识")
private Integer deleteFlag;
@ExcelIgnore
@ApiModelProperty(value = "备注")
private String remarks;
@ExcelProperty("创建人")
@ApiModelProperty(value = "创建人")
private String createdName;
/**
* 修改人
*/
@ExcelProperty("修改人")
@ApiModelProperty(value = "修改人")
private String modifiedName;
@ExcelIgnore
@ApiModelProperty(value = "总数")
private Long total;
@ExcelProperty("创建时间")
@ApiModelProperty(value = "创建时间")
@JSONField(format="yyyy-MM-dd HH:mm:ss")
private String gmtCreated;
@ExcelProperty("修改时间")
@ApiModelProperty(value = "修改时间")
@JSONField(format="yyyy-MM-dd HH:mm:ss")
private String gmtModified;
}
业务代码
package com.arpa;
import cn.hutool.core.date.DatePattern;
import cn.hutool.core.date.DateUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.fastjson.JSONObject;
import com.google.common.collect.Lists;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import java.io.*;
import java.util.Date;
import java.util.List;
import java.util.Properties;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;
@RunWith(SpringRunner.class)
@SpringBootTest
public class Test {
@Autowired
private IContainerService iContainerService;
@org.junit.Test
public void test() throws IOException, InterruptedException {
// 查询数据并保存到Excel文件中
List<com.arpa.wms.domain.report.vo.ContainerVO> vo = iContainerService.getExportData(new ContainerDTO());
ExportTask task = new ExportTask();
task.setDataType(ExportTaskDataTypeEnum.CONTAINER);//这里是枚举。用来存储文件名字的。你可以用你自己的方式。
String[] fileUrl = exportExcel(vo, task, ContainerVO.class);
// 打包Excel文件
zipExcelFiles(fileUrl);
// 删除Excel文件
Thread.sleep(10000);
deleteExcelFiles(fileUrl);
}
public String[] exportExcel(Object data, ExportTask task, Class head) throws IOException {
LogHelper.error(LogTypeEnum.EXCEPTION, "库存数据导出生成excel文件...");
List<Object> vo = (List<Object>) data;
Properties properties = System.getProperties();
String path = properties.getProperty("user.dir");
if (properties.getProperty("os.name").toLowerCase().contains("win")) {
path += "\\";
} else {
path += "/";
}
// 每个excel多少条数据
int pageNum = 100;
int num = vo.size() % pageNum > 0 ? vo.size() / pageNum + 1 : vo.size() / pageNum;
String[] res = new String[num + 1];
res[0] = path + task.getDataType().getDescp() + DateUtil.format(new Date(), DatePattern.PURE_DATETIME_PATTERN) + ".zip";
for (int i = 0; i < num; i++) {
String fileName = res[0] + "_" + (i + 1) + ".xlsx";
EasyExcel.write(fileName, head).sheet(task.getDataType().getDescp()).doWrite(vo.subList(i * pageNum, i * pageNum + pageNum));
res[i + 1] = fileName;
}
return res;
}
/**
* 压缩文件
* @param fileUrl
*/
private static void zipExcelFiles(String[] fileUrl) {
LogHelper.error(LogTypeEnum.EXCEPTION, "库存数据压缩生成 ZIP 文件..." + fileUrl[0]);
try {
String zipFileName = fileUrl[0];
File[] filesToZip = new File[fileUrl.length - 1]; // 假设有5个Excel文件
// 添加要打包的Excel文件
for (int i = 1; i <= filesToZip.length; i++) {
filesToZip[i - 1] = new File(fileUrl[i]);
}
// 创建ZIP文件
FileOutputStream fos = new FileOutputStream(zipFileName);
ZipOutputStream zos = new ZipOutputStream(fos);
// 将Excel文件添加到ZIP文件中
for (File file : filesToZip) {
ZipEntry zipEntry = new ZipEntry(file.getName());
zos.putNextEntry(zipEntry);
FileInputStream fis = new FileInputStream(file);
byte[] buffer = new byte[1024];
int length;
while ((length = fis.read(buffer)) > 0) {
zos.write(buffer, 0, length);
}
fis.close();
}
// 关闭ZIP文件流
zos.closeEntry();
zos.close();
} catch (IOException e) {
e.printStackTrace();
}
}
// 删除Excel文件
private static void deleteExcelFiles(String[] fileUrl) {
for (int i = 1; i <= fileUrl.length; i++) {
LogHelper.error(LogTypeEnum.EXCEPTION, "删除库存数据生成文件..." + fileUrl[i - 1]);
File file = new File(fileUrl[i - 1]);
if (file.exists()) {
file.delete();
}
}
}
}