好久不见,最近在做一个导出批量excel的功能,因为不希望通过先写出单个excel到本地,然后再压缩成zip后导出。
因此随手百度,找到倒流进ZipOutputStream,然后变成zip导出。
demo代码如下
@RequestMapping(value = "/poizip")
public void poizip(HttpServletResponse response) throws IOException {
//response 输出流
ServletOutputStream out = response.getOutputStream();
//压缩输出流---将response输出流填入压缩输出流
ZipOutputStream zipOutputStream = new ZipOutputStream(out);
try {
for (int i = 0; i < 6; i++) {
//创建工作簿
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("sheet" + i);
HSSFRow row = sheet.createRow(0);
HSSFCell cell = row.createCell(0);
cell.setCellValue("内容" + i);
response.setContentType("application/octet-stream; charset=utf-8");
response.setHeader("Content-Disposition", "attachment; filename=test.zip");
//重点开始,创建压缩文件,并进行打包
ZipEntry z = new ZipEntry(i + ".xls");
zipOutputStream.putNextEntry(z);
//写入一个压缩文件---最后写文件
wb.write(zipOutputStream);
}
zipOutputStream.flush();
} catch (IOException e) {
e.printStackTrace();
} finally {
//注意关闭顺序,否则可能文件错误,先开后关
if (zipOutputStream != null) {
zipOutputStream.close();
}
if (out != null) {
out.close();
}
}
}
这个方法在导入poi的前提下可直接使用,但是用到我的环境内的时候,它每次在处理第一个excel后就直接导出了。
百思不得其解,仔细观察代码后,发现我们的区别是:
我是使用 XSSFWorkbook
而参考的博主代码是 HSSFWorkbook
两者的区别是
那问题到这里就很明显,肯定是这两个封装的方法出了问题。
那我们直接进入源码查看问题
public void saveImpl(OutputStream outputStream) {
this.throwExceptionIfReadOnly();
try {
ZipOutputStream zos;
if (!(outputStream instanceof ZipOutputStream)) {
zos = new ZipOutputStream(outputStream);
} else {
zos = (ZipOutputStream)outputStream;
}
if (this.getPartsByRelationshipType("http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties").size() == 0 && this.getPartsByRelationshipType("http://schemas.openxmlformats.org/officedocument/2006/relationships/metadata/core-properties").size() == 0) {
logger.log(1, new Object[]{"Save core properties part"});
this.getPackageProperties();
this.addPackagePart(this.packageProperties);
this.relationships.addRelationship(this.packageProperties.getPartName().getURI(), TargetMode.INTERNAL, "http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties", (String)null);
if (!this.contentTypeManager.isContentTypeRegister("application/vnd.openxmlformats-package.core-properties+xml")) {
this.contentTypeManager.addContentType(this.packageProperties.getPartName(), "application/vnd.openxmlformats-package.core-properties+xml");
}
}
logger.log(1, new Object[]{"Save package relationships"});
ZipPartMarshaller.marshallRelationshipPart(this.getRelationships(), PackagingURIHelper.PACKAGE_RELATIONSHIPS_ROOT_PART_NAME, zos);
logger.log(1, new Object[]{"Save content types part"});
this.contentTypeManager.save(zos);
Iterator i$ = this.getParts().iterator();
while(i$.hasNext()) {
PackagePart part = (PackagePart)i$.next();
if (!part.isRelationshipPart()) {
logger.log(1, new Object[]{"Save part '" + ZipHelper.getZipItemNameFromOPCName(part.getPartName().getName()) + "'"});
PartMarshaller marshaller = (PartMarshaller)this.partMarshallers.get(part._contentType);
if (marshaller != null) {
if (!marshaller.marshall(part, zos)) {
throw new OpenXML4JException("The part " + part.getPartName().getURI() + " fail to be saved in the stream with marshaller " + marshaller);
}
} else if (!this.defaultPartMarshaller.marshall(part, zos)) {
throw new OpenXML4JException("The part " + part.getPartName().getURI() + " fail to be saved in the stream with marshaller " + this.defaultPartMarshaller);
}
}
}
zos.close();
} catch (OpenXML4JRuntimeException var6) {
throw var6;
} catch (Exception var7) {
throw new OpenXML4JRuntimeException("Fail to save: an error occurs while saving the package : " + var7.getMessage(), var7);
}
}
如下图,他会在写出的时候,判断是否是zip压缩流,如果是的话,会直接转换
很快,找到问题的根源,影响我需要的功能操作的是
在代码的最后它会关闭zip压缩流,导致无法后续的excel流继续写入到zip压缩流中
而HSSFWorkbook 是没有做close操作的。因此后续的excel在这里插入代码片
流可以继续往zip中写入。
找到问题后,就是XSSFWorkbook写入zip流后,会直接输出然后关闭zip流,那么我们只要将XSSFWorkbook流写入到别的流内,然后再写入到zip中即可,这样XSSFWorkbook.write关闭流就不会影响到zip了。
如下
bos = new ByteArrayOutputStream();
wb.write(bos);
bos.writeTo(zipOutputStream);
参考:
https://blog.csdn.net/qq_26576683/article/details/89736528
https://blog.csdn.net/qq_36168749/article/details/86289293
https://blog.csdn.net/cs373616511/article/details/80325458