Java实现导出多个excel表打包到zip文件中，供客户端另存为窗口下载

文章目录

• 一、业务背景
• 二、实现思路
• 二、准备工作
• • 1.准备data模板.xlsx
  • 2.引入poi相关依赖，用于操作excel
  • 3.针对WorkBook+ZIP压缩输入/输出流，相关方法知识点要有所了解
• 三、完整的项目代码
• 四、可能遇到的问题
• • 错误场景1：java.io.IOException: Stream closed
  • 错误场景2：调用接口没有另存为弹窗，但是F12查看接口结果返回一堆乱码

一、业务背景

业务需求：从数据库查询多个list集合信息封装excel，每个excel都有2个sheet页，填充不同的信息，最后将所有excel打包成zip文件，以流的形式返回给客户端，供客户端另存为窗口下载。

1. 只发出一次请求
2. 每个excel表中到数据记录不能超过2条
3. excel文件或者zip包不会上传服务器，而是查询后直接封装excel，然后把多个excel封装成zip包直接返回

之前看过其他人的方案大概有2种：

方案1：打包成zip包后上传到服务器某个路径下，然后在读取该路径的zip文件，以流的形式返回给客户端。
方案2：不上传服务器，而是查询后直接封装excel，然后把多个excel封装成zip包直接返回。（本人采用的就是第二种方案）

最终的效果，如图

二、实现思路

1. 设置HttpServletResponse的参数，比如header、contentType
2. 新建一个Workbook对象并置为空，同时初始化相关对象，比如List、File等
3. 从数据库查询多条list
4. 以其中为主的一个list计算分页数量
5. 循环遍历list开始
6. 初始化新的Workbook对象，并设置相应的Title
7. 将list拆分成多个段，分别写到的Workbook对象中
8. 将Workbook对象填充到List中
9. 重复步骤6至步骤8直至写完所有数据
10. 最后统一封装zip压缩包并导出，调用downFileByStream方法
11. 初始化ZipOutputStream对象
12. 循环遍历List将每一个wb写入ZipOutputStream对象中，并将内存流写入Zip文件，即：将每一个excel封装到zip包中
13. 关闭ZipOutputStream

二、准备工作

1.准备data模板.xlsx

2.引入poi相关依赖，用于操作excel

pom.xml

<dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>3.17</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>3.17</version></dependency>

3.针对WorkBook+ZIP压缩输入/输出流，相关方法知识点要有所了解

Apache POI包中的HSSFWorkbook、XSSFWorkbook、SXSSFWorkbook的区别如下:

ZipOutputStream类的常用方法如下表所示：

ZipInputStream类的常用方法如下表所示：

Workbook类提供的方法

三、完整的项目代码

ExportUtil工具类

package com.excel.utils;import org.apache.poi.ss.usermodel.Workbook;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import org.slf4j.Logger;import org.slf4j.LoggerFactory;import javax.servlet.http.HttpServletResponse;import java.io.BufferedOutputStream;import java.io.ByteArrayOutputStream;import java.io.File;import java.io.FileOutputStream;import java.io.OutputStream;import java.util.List;import java.util.zip.ZipEntry;import java.util.zip.ZipOutputStream;/** * 导出【用户、组、隶属关系】工具 * @Author 211145187 * @Date 2023/4/13 16:29 **/public class ExportUtil {private static Logger logger = LoggerFactory.getLogger(ExportUtil.class);/** * 自定义获取分页总页数的方法 * @param count 查询集合数量 * @param pageSize 配置文件中设置的单文件存储最大条数 * @return 总页数 */public static Integer getPageCount(Integer count, Integer pageSize){Integer pageCount = 0;if(count.equals(0)){return pageCount;}pageCount = count/pageSize;if(count % pageSize != 0){pageCount++;}return pageCount;}/** * 自定义List分页工具 * @param list 待分页的list数据 * @param pageNum 页码 * @param pageSize 页容量 * @param pageCount 总页数 * @return 分页后的list数据 */public static <T> List<T> getPageList(List<T> list, Integer pageNum, Integer pageSize, Integer pageCount){/**开始索引*/int beginIndex = 0;/**结束索引*/int endIndex = 0;Integer compare = pageNum.compareTo(pageCount);if(!compare.equals(0)){beginIndex = (pageNum - 1) * pageSize;endIndex = beginIndex + pageSize;}else{beginIndex = (pageNum - 1) * pageSize;endIndex = list.size();}List pageList = list.subList(beginIndex, endIndex);return pageList;}/** * HSSFWorkbook转file * @param wb wb * @param name 文件名称 * @return File */public static File xssfWorkbookToFile(Workbook wb, String name) {File toFile = new File(name);try {OutputStream os = new FileOutputStream(toFile);wb.write(os);os.close();} catch (Exception e) {e.printStackTrace();}return toFile;}/** * 直接下载zip包 * @param response response * @param excels wb集合 */public static void downFileByStream(HttpServletResponse response, List<XSSFWorkbook> excels){try {OutputStream toClient = new BufferedOutputStream(response.getOutputStream());ZipOutputStream zipOutputStream = new ZipOutputStream(toClient);for(int i=0; i<excels.size(); i++){ByteArrayOutputStream baos = new ByteArrayOutputStream();// 将Workbook写入内存流excels.get(i).write(baos);ZipEntry zipEntry = new ZipEntry("data" + i + ".xlsx");zipOutputStream.putNextEntry(zipEntry);// 将内存流写入Zip文件zipOutputStream.write(baos.toByteArray());}zipOutputStream.closeEntry();zipOutputStream.flush();zipOutputStream.close();}catch (Exception e){logger.error("downFileByStream==========fail:{}", e.getMessage());}}}

application.properties

server.port=8001#导出excel配置，单文件存储最大数量export.num=2

OperateExcelController

package com.excel.controller;import com.excel.bean.Score;import com.excel.bean.Teacher;import com.excel.utils.ExportUtil;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.CellStyle;import org.apache.poi.ss.usermodel.FillPatternType;import org.apache.poi.ss.usermodel.IndexedColors;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.usermodel.Workbook;import org.apache.poi.xssf.usermodel.XSSFCell;import org.apache.poi.xssf.usermodel.XSSFRow;import org.apache.poi.xssf.usermodel.XSSFSheet;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import org.slf4j.Logger;import org.slf4j.LoggerFactory;import org.springframework.beans.factory.annotation.Value;import org.springframework.web.bind.annotation.RequestMapping;import javax.servlet.http.HttpServletResponse;import java.io.File;import java.io.FileInputStream;import java.io.IOException;import java.io.InputStream;import java.util.ArrayList;import java.util.List;/** * 测试3.17版本操作Excel * @Author 211145187 * @Date 2022/2/22 19:43 **/@RequestMapping("/excel")@Controllerpublic class OperateExcelController {private static Logger logger = LoggerFactory.getLogger(OperateExcelController.class);@Value("${export.num:10000}")private Integer exportLimitNum;//构建教师集合数据public List<Teacher> buildTeacherList1() {List<Teacher> teacherList = new ArrayList<>();Teacher teacher1 = new Teacher();teacher1.setName("周杰伦");teacher1.setClasses("三年二班");teacher1.setCollege("魔法学院");teacher1.setAlias("Jay Chou");teacherList.add(teacher1);Teacher teacher2 = new Teacher();teacher2.setName("陈奕迅");teacher2.setClasses("三年二班");teacher2.setCollege("魔法学院");teacher2.setAlias("Eason");teacherList.add(teacher2);Teacher teacher3 = new Teacher();teacher3.setName("林俊杰");teacher3.setClasses("三年二班");teacher3.setCollege("魔法学院");teacher3.setAlias("Eason");teacherList.add(teacher3);Teacher teacher4 = new Teacher();teacher4.setName("张杰");teacher4.setClasses("三年二班");teacher4.setCollege("魔法学院");teacher4.setAlias("Eason");teacherList.add(teacher4);return teacherList;}//构建分数集合数据public List<Score> buildScoreList1() {List<Score> scoreList = new ArrayList<>();Score score1 = new Score();score1.setName("流川枫");score1.setClasses("三年二班");score1.setWriteScore("6");score1.setComputerScore("4");scoreList.add(score1);Score score2 = new Score();score2.setName("樱木花道");score2.setClasses("三年二班");score2.setWriteScore("6");score2.setComputerScore("4");scoreList.add(score2);Score score3 = new Score();score3.setName("大猩猩");score3.setClasses("三年二班");score3.setWriteScore("6");score3.setComputerScore("4");scoreList.add(score3);Score score4 = new Score();score4.setName("三井");score4.setClasses("三年二班");score4.setWriteScore("6");score4.setComputerScore("4");scoreList.add(score4);return scoreList;}//方法5：java导出多个Excel为zip包@RequestMapping("/exportMultipleExcelToZip")public void exportMultipleExcelToZip(HttpServletResponse response) throws IOException {response.setHeader("Content-disposition", "attachment; filename=" + "test.zip");response.setContentType("application/zip; charset=utf-8");//创建HSSFWorkbook对象(excel的文档对象)XSSFWorkbook wb = null;List<Teacher> teacherList = new ArrayList<>();//构建sheet页集合List<Score> scoreList = new ArrayList<>();File templateFile = new File("C:\\Users\\211145187\\Desktop\\data模板.xlsx");//.....省略部分代码List<Teacher> buildTeacherList = buildTeacherList1();List<Score> buildScoreList = buildScoreList1();Integer pageLimitSize = exportLimitNum;//计算list的分页数量Integer pageCount = ExportUtil.getPageCount(buildTeacherList.size(), pageLimitSize);List<XSSFWorkbook> excels = new ArrayList<>();try {for(Integer pageNum = 1; pageNum < pageCount + 1; pageNum++) {//注意：每次循环遍历前都需要初始化新的wb对象//注意情况1：如果是初始化wb空对象然后手动添加title，下方三行代码不会报错//wb = new XSSFWorkbook();//buildScoreSheetTitle(wb);//buildTeacherSheetTitle(wb);//注意情况2：如果是初始化wb对象，并且以流的形式初始化，那么io流必须放在里面才行，如果放在for循环外面会报“Stream Closed”错误InputStream io = new FileInputStream(templateFile);wb = new XSSFWorkbook(io);teacherList = ExportUtil.getPageList(buildTeacherList, pageNum, pageLimitSize, pageCount);scoreList = ExportUtil.getPageList(buildScoreList, pageNum, pageLimitSize, pageCount);buildScoreSheetParams(wb, scoreList);buildTeacherSheetParams(wb, teacherList);excels.add(wb);}//最后统一封装zip压缩包并导出ExportUtil.downFileByStream(response, excels);} catch (Exception e) {logger.error("IOException:", e);}}/** * 填充教师页信息 * @param wb wb * @param bodyData bodyData */private void buildTeacherSheetParams(Workbook wb, List<Teacher> bodyData){int teacherColumnCount = wb.getSheetAt(1).getRow(0).getPhysicalNumberOfCells();Sheet sheet = wb.getSheetAt(1);// build datafor(int j=0; j<bodyData.size(); j++){Teacher itm = bodyData.get(j);Row rowData = sheet.createRow(j+1);for(int k=0;k<teacherColumnCount; k++){Cell cell = rowData.createCell(k);cell.setCellValue(getValueByTeacher(k, itm));}}}private String getValueByTeacher(int columnIndex,Teacher itm){String cellValue;switch (columnIndex){case 0:cellValue = itm.getName(); break;case 1:cellValue = itm.getClasses()+""; break;case 2:cellValue = itm.getCollege(); break;case 3:cellValue = itm.getAlias(); break;default:cellValue=""; break;}return cellValue;}/** * 填充分数页信息 * @param wb wb * @param bodyData bodyData */private void buildScoreSheetParams(Workbook wb, List<Score> bodyData){int scoreColumnCount = wb.getSheetAt(0).getRow(0).getPhysicalNumberOfCells();Sheet sheet = wb.getSheetAt(0);// build datafor(int j=0; j<bodyData.size(); j++){Score itm = bodyData.get(j);Row rowData = sheet.createRow(j+1);for(int k=0;k<scoreColumnCount; k++){Cell cell = rowData.createCell(k);cell.setCellValue(getValueByScore(k, itm));}}}private String getValueByScore(int columnIndex,Score itm){String cellValue;switch (columnIndex){case 0:cellValue = itm.getName(); break;case 1:cellValue = itm.getClasses()+""; break;case 2:cellValue = itm.getWriteScore(); break;case 3:cellValue = itm.getComputerScore(); break;default:cellValue=""; break;}return cellValue;}/** * 构建分数表excel的标头 * @Author 211145187 * @Date 2022/2/22 20:20 * @Param wb wb **/private void buildScoreSheetTitle(XSSFWorkbook wb) {//建立新的sheet对象（excel的表单）XSSFSheet sheet=wb.createSheet("成绩表");XSSFRow row=sheet.createRow(0);//创建单元格并设置单元格内容XSSFCell cell0 = row.createCell(0);cell0.setCellValue("姓名");cell0.setCellStyle(getHeadStyle(wb));XSSFCell cell1 = row.createCell(1);cell1.setCellValue("班级");cell1.setCellStyle(getHeadStyle(wb));XSSFCell cell2 = row.createCell(2);cell2.setCellValue("笔试成绩");cell2.setCellStyle(getHeadStyle(wb));XSSFCell cell3 = row.createCell(3);cell3.setCellValue("机试成绩");cell3.setCellStyle(getHeadStyle(wb));}/** * 构建教师表excel的标头 * @Author 211145187 * @Date 2022/2/22 20:20 * @Param wb wb **/private void buildTeacherSheetTitle(XSSFWorkbook wb) {//建立新的sheet对象（excel的表单）XSSFSheet sheet=wb.createSheet("教师表");XSSFRow row=sheet.createRow(0);//创建单元格并设置单元格内容XSSFCell cell0 = row.createCell(0);cell0.setCellValue("姓名");cell0.setCellStyle(getHeadStyle(wb));XSSFCell cell1 = row.createCell(1);cell1.setCellValue("班级");cell1.setCellStyle(getHeadStyle(wb));XSSFCell cell2 = row.createCell(2);cell2.setCellValue("所属学院");cell2.setCellStyle(getHeadStyle(wb));XSSFCell cell3 = row.createCell(3);cell3.setCellValue("别名");cell3.setCellStyle(getHeadStyle(wb));}/** * 设置样式 * @Author 211145187 * @Date 2022/2/22 20:15 * @Param wb wb * @Return CellStyle **/private CellStyle getHeadStyle(Workbook wb) {CellStyle cellStyle = wb.createCellStyle();//用于设置前景颜色cellStyle.setFillForegroundColor(IndexedColors.SKY_BLUE.getIndex());/** * setFillPattern用于设置单元格填充样式 * 注意： *1）setFillPattern必须设置否则光设置setFillForegroundColor无效 *2）3.10.1版本支持short类型参数，而3.17版本支持FillPatternType类型参数 */cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);return cellStyle;}}

四、可能遇到的问题

错误场景1：java.io.IOException: Stream closed

原因分析：

我这里导致流关闭会有2种场景触发：
场景1：工具类方法downFileByStream()中的for循环执行write()方法。
场景2：wb = new XSSFWorkbook(io);通过IO流初始化wb对象时。

解决方案：

针对场景1中的解决方案就是：Workbook的write()方法最终会关闭它写入的输出流。如果只是一次性全部输出那就不会碰到这种问题，但是现在的场景是工具类方法downFileByStream()中的for循环执行write()方法，所以解决方案就是用一个ByteArrayOutputStream去接收存储当前Workbook的内容，这样哪怕Workbook关闭了，但是内容已经提前存入了ByteArrayOutputStream中，只要读取了就可以继续使用。因此才有这样的如下写法：

/** * 直接下载zip包 * @param response response * @param excels wb集合 */public static void downFileByStream(HttpServletResponse response, List<XSSFWorkbook> excels){try {OutputStream toClient = new BufferedOutputStream(response.getOutputStream());ZipOutputStream zipOutputStream = new ZipOutputStream(toClient);for(int i=0; i<excels.size(); i++){ByteArrayOutputStream baos = new ByteArrayOutputStream();// 将Workbook写入内存流excels.get(i).write(baos);ZipEntry zipEntry = new ZipEntry("data" + i + ".xlsx");zipOutputStream.putNextEntry(zipEntry);// 将内存流写入Zip文件zipOutputStream.write(baos.toByteArray());}zipOutputStream.closeEntry();zipOutputStream.flush();zipOutputStream.close();}catch (Exception e){logger.error("downFileByStream==========fail:{}", e.getMessage());}}

针对场景2中的解决方案就是：把IO流初始化提到for循环里面，如实有了如下写法：

for(Integer pageNum = 1; pageNum < pageCount + 1; pageNum++) {//注意：每次循环遍历前都需要初始化新的wb对象//注意情况1：如果是初始化wb空对象然后手动添加title，下方三行代码不会报错//wb = new XSSFWorkbook();//buildScoreSheetTitle(wb);//buildTeacherSheetTitle(wb);//注意情况2：如果是初始化wb对象，并且以流的形式初始化，那么io流必须放在里面才行，如果放在for循环外面会报“Stream Closed”错误InputStream io = new FileInputStream(templateFile);wb = new XSSFWorkbook(io);...}

错误场景2：调用接口没有另存为弹窗，但是F12查看接口结果返回一堆乱码

错误原因分析：可能是返回结果HttpServletResponse设置的setContentType格式不对。

正确的应该设置为如下，一定要设置为application/zip：

response.setHeader("Content-disposition", "attachment; filename=" + "test.zip");response.setContentType("application/zip; charset=utf-8");