1.需求描述
将MySQL多个表转excel, 打包下载为zip.
2. 修改ExcelUtil.java
新增如下方法:将Workbook写入字节流ByteOutputStream
public void byteOutputStreamExcel(ByteOutputStream byteOutputStream, List<T> list, String sheetName, String title) {
this.init(list, sheetName, title);
try {
writeSheet();
wb.write(byteOutputStream);
} catch (Exception e) {
log.error("导出Excel异常{}", e.getMessage());
} finally {
IOUtils.closeQuietly(wb);
}
}
3. Service层方法
没什么好说的,就是这么写, 当作模板使用,自己优化.
public void exportZip(HttpServletResponse response, int[] ids) {
String fileName = "Mapping_Tables.zip";
response.setContentType("application/zip");
response.setHeader("content-disposition", "attachment;filename=" + fileName);
ZipOutputStream zos = null;
try {
zos = new ZipOutputStream(response.getOutputStream());
for (int tableId : ids) {
IMappingTableBaseService service = mappingTableFactory.getMappingTableService(tableId);
MappingTable mappingTable = this.selectMappingTableById(tableId);
List list = service.list(null);
ExcelUtil util = service.getExcelUtil();
ZipEntry entry = new ZipEntry(mappingTable.getDescription() + ".xls");
zos.putNextEntry(entry);
ByteOutputStream bos = new ByteOutputStream();
util.byteOutputStreamExcel(bos, list,"Date List", "");
bos.writeTo(zos);
bos.close();
zos.closeEntry();
}
} catch (IOException e) {
throw new RuntimeException(e);
} finally {
if(zos != null){
try{
zos.close();
} catch(Exception e){
e.printStackTrace();
}
}
}
}
4. Controller方法
根据请求判断是要下载多个文件还是单个文件. 如果是单个文件则无需打包.
@PostMapping("/export")
public void export(HttpServletResponse response, @RequestBody int[] ids) {
if (ids != null) {
if (ids.length == 1) {
IMappingTableBaseService service = mappingTableFactory.getMappingTableService(ids[0]);
MappingTable mappingTable = mappingTableService.selectMappingTableById(ids[0]);
List list = service.list(null);
ExcelUtil util = service.getExcelUtil();
util.exportExcel(response, list, "Date List", mappingTable.getDescription() + ".xls");
} else {
mappingTableService.exportZip(response, ids);
}
}
}
结束~