在前一篇blog https://blog.csdn.net/u010714901/article/details/82691786 中说在直接生成多个excel打包下载的过程中,基于如下代码,会发现,模板文件中的template sheet有出现了。
/这是zos的输出流
ByteArrayOutputStream bos = new ByteArrayOutputStream();
ZipOutputStream zos = new ZipOutputStream(bos);
//循环
for (xxx:xxxxx) {
//****业务代码
//定义文件的输入流
InputStream input = EnvUtils.isDevFromSystem() ?
this.getClass().getClassLoader().getResourceAsStream(
WmsConstant.WMS_TEMPLATE_ROOT + filename) :
new FileInputStream(WmsPathUtil.getTemplateReportPath().resolve(filename).toFile());
//定义文件的输出流,但是这是作为zip流的一部分
ByteArrayOutputStream output = new ByteArrayOutputStream();
//用jxlsHelper来处理xlxs输出了流
JxlsHelper jxlsHelper = new WmsJxlsHelper();
jxlsHelper.processTemplate(input, output, context);
//命名文件
zos.putNextEntry(new ZipEntry(channelName + ".xlsx"));
//把xlxs输出流作为输入流输入到zip输出流中
zos.write(output.toByteArray());
zos.closeEntry();
output.close();
}
//关闭zos,但是outputStream流依然存在内存中
zos.close();
return genResponseEntityFromBytes(MediaType.APPLICATION_OCTET_STREAM,
downloadFileName, bos.toByteArray());
问题:excel生成时template不能删除,在查询jxlsHelper#processTemplate 官方文当的过程中有api
jxlsHelper.setDeleteTemplateSheet(isDeleteTemplateSheet);
jxlsHelper.setHideTemplateSheet(isDeleteTemplateSheet);
然而,设置后根本就没啥用。
参考源代码:
//调用的api
public JxlsHelper processTemplate(InputStream templateStream, OutputStream targetStream, Context context) throws IOException {
Transformer transformer = this.createTransformer(templateStream, targetStream);
this.processTemplate(context, transformer);
return this;
}
//实际执行的api函数
public void processTemplate(Context context, Transformer transformer) throws IOException {
this.areaBuilder.setTransformer(transformer);
List<Area> xlsAreaList = this.areaBuilder.build();
Iterator var4 = xlsAreaList.iterator();
Area xlsArea;
while(var4.hasNext()) {
xlsArea = (Area)var4.next();
xlsArea.applyAt(new CellRef(xlsArea.getStartCellRef().getCellName()), context);
}
if (this.processFormulas) {
var4 = xlsAreaList.iterator();
while(var4.hasNext()) {
xlsArea = (Area)var4.next();
this.setFormulaProcessor(xlsArea);
xlsArea.processFormulas();
}
}
//输出了excel,有删除吗?有用到deleteTemplateSheet或者hideTemplateSheet参数吗?没有!
transformer.write();
}
参考这篇博客:https://blog.csdn.net/u010447549/article/details/81008513 文章给出了修改jar中的源代码,我没有改(因为不会),但是思路是正确的。为了实现这个需求,我重写了一个JxlsHelper
public class CusJxlsHelper extends JxlsHelper {
private static Logger logger = LoggerFactory.getLogger(WmsJxlsHelper.class);
public static WmsJxlsHelper instance() {
return new WmsJxlsHelper();
}
//重写了processTemplate这个方法
@Override
public void processTemplate(Context context, Transformer transformer) throws IOException {
AreaBuilder areaBuilder = this.getAreaBuilder();
boolean processFormulas = this.isProcessFormulas();
areaBuilder.setTransformer(transformer);
List<Area> xlsAreaList = areaBuilder.build();
Iterator var4 = xlsAreaList.iterator();
Area xlsArea;
while (var4.hasNext()) {
xlsArea = (Area) var4.next();
xlsArea.applyAt(new CellRef(xlsArea.getStartCellRef().getCellName()), context);
}
if (processFormulas) {
var4 = xlsAreaList.iterator();
while (var4.hasNext()) {
xlsArea = (Area) var4.next();
this.setFormulaProcessor(xlsArea);
xlsArea.processFormulas();
}
}
//增加了删除template sheet的业务控制
logger.info("===================delete template sheet in WmsJxlsHelper");
if (this.isDeleteTemplateSheet()) {
transformer.deleteSheet("TEMPLATE");
}
transformer.write();
}
//这里不想写的,但是不写会报错
private Area setFormulaProcessor(Area xlsArea) {
FormulaProcessor fp = this.getFormulaProcessor();
if (fp == null) {
if (this.isUseFastFormulaProcessor()) {
fp = new FastFormulaProcessor();
} else {
fp = new StandardFormulaProcessor();
}
}
xlsArea.setFormulaProcessor((FormulaProcessor) fp);
return xlsArea;
}
}
最后把官方的jxlsHelper更换为自定义的CusJxlsHelper,ok,工作正常。
最终代码如下:
@RequestMapping(value = WmsUrlConstants.Shipment.RED_DOWNLOAD_SHIPPING_DATA_LIST, method = RequestMethod.GET)
public ResponseEntity<byte[]> downloadShippingDataList(@PathVariable("shipmentId") @NotNull Long shipmentId) {
String filename = "xxxxxxx.xlsx";
try {
SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
int listCount = shipmentDownloadService.getShippingDataCountByred(shipmentId);
ComStoreModel store = ComStore.getStoreById(getSelStoreId());
String downloadFileName = WmsConstant.ShippingDataList.FILE_NAME + store.getStoreName()
+ "_ShippingData_" + sdf.format(new Date()) + "-" + listCount + "件.zip";
List<ShipmentDisShippingDataListBean> shippingDataList =
shipmentDownloadService.getShippingDataListBeans(shipmentId);
ExcelZipBuilder zipBuilder = new ExcelZipBuilder();
//获取模板文件
Path defaultFile = EnvUtils.isDevFromSystem() ?
Paths.get(Objects.requireNonNull(this.getClass().getClassLoader().getResource(
WmsConstant.WMS_TEMPLATE_ROOT + filename)).getPath().replaceFirst("^/(.:/)", "$1")) :
WmsPathUtil.getTemplateReportPath().resolve(filename);
for (xxxxx x : xxxxxxxList) {
Context context = new Context();
//业务代码
zipBuilder.addEntry(context, defaultFile.toFile(), channelName + ".xlsx");
}
byte[] result = zipBuilder.build();
return genResponseEntityFromBytes(MediaType.APPLICATION_OCTET_STREAM,
downloadFileName, result);
} catch (Exception e) {
throw new CodeException(CodeConstant.Common.DOWNLOAD_FILE_ERROR, e);
}
}
ExcelZipBuilder 代码如下
package com.voyageone.ecerp.web.wms.helper;
import org.jxls.common.Context;
import org.jxls.util.JxlsHelper;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.nio.file.Path;
import java.util.ArrayList;
import java.util.List;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;
/**
* Created with IntelliJ IDEA.
*
* @author: jet.xie
* @Date: 2018/9/13
* @Time: 15:19
* @Description:
* @version: 1.0.0
*/
public class ExcelZipBuilder {
private static Logger logger = LoggerFactory.getLogger(WmsJxlsHelper.class);
private List<Entry> entries = new ArrayList<>();
public ExcelZipBuilder addEntry(Context context, File templateFile, String targetFileName) {
entries.add(new Entry(context, targetFileName, templateFile, true));
return this;
}
public ExcelZipBuilder addEntry(Context context, File templateFile, String targetFileName, boolean isDeleteTemplateSheet) {
entries.add(new Entry(context, targetFileName, templateFile, isDeleteTemplateSheet));
return this;
}
public byte[] build() throws IOException {
try (ByteArrayOutputStream bos = new ByteArrayOutputStream();
ZipOutputStream zos = new ZipOutputStream(bos)
) {
logger.info("====================压缩{}份文件", entries.size());
for (Entry entry : entries) {
ZipEntry zipEntry = new ZipEntry(entry.entryName);
zos.putNextEntry(zipEntry);
byte[] bytes = entry.toByte();
zos.write(bytes);
zos.closeEntry();
}
zos.close();
return bos.toByteArray();
}
}
private static class Entry {
/**
* 填充内容
*/
private Context context;
/**
* excel文件名称
*/
private String entryName;
/**
* 模板名称
*/
private File templateFile;
/**
* 是否删除templateSheet
*/
private boolean isDeleteTemplateSheet;
Entry(Context context, String entryName, File templateFile, boolean isDeleteTemplateSheet) {
this.context = context;
this.entryName = entryName;
this.templateFile = templateFile;
this.isDeleteTemplateSheet = isDeleteTemplateSheet;
}
byte[] toByte() throws IOException {
//读取文件流
FileInputStream input = new FileInputStream(templateFile);
ByteArrayOutputStream output = new ByteArrayOutputStream();
//用jxlsHelper来处理xlxs输出了流
JxlsHelper jxlsHelper = new WmsJxlsHelper();
jxlsHelper.setDeleteTemplateSheet(isDeleteTemplateSheet);
jxlsHelper.processTemplate(input, output, context);
return output.toByteArray();
}
public boolean isDeleteTemplateSheet() {
return isDeleteTemplateSheet;
}
public void setDeleteTemplateSheet(boolean deleteTemplateSheet) {
isDeleteTemplateSheet = deleteTemplateSheet;
}
}
}