@[toc]

一、业务背景

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

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

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

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

最终的效果，如图

二、实现思路

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

方法	返回值	说明
putNextEntry(ZipEntry e)	void	开始写一个新的ZipEntry，并将流内的位置移至此entry所指数据的开头
write(byte[] b, int off, int len)	void	将字节数组写入当前ZIP条目数据
finish()	void	完成写入ZIP输出流的内容，无须关闭它所配合的OutputStream
setComment(String comment)	void	可设置此ZIP文件的注释文字

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

方法	返回值	说明
read(byte[] b, int off, int len)	int	读取目标b数组内off偏移量的位置，长度是len字节
available()	int	判断是否已读完目前entry所指定的数据。已读完返回0，否则返回1
closeEntry()	void	关闭当前ZIP条目并定位流以读取下一个条目
skip(long n)	long	跳过当前ZIP条目中指定的字节数
getNextEntry()	ZipEntry	读取下一个ZipEntry，并将流内的位置移至该entry所指数据的开头
createZipEntry(String name)	ZipEntry	以指定的name参数新建一个ZipEntry对象

Workbook类提供的方法

方法	返回值	说明
getNumberOfSheets()	int	获得工作薄（Workbook）中工作表（Sheet）的个数
getSheets()	Sheet[]	返回工作薄（Workbook）中工作表（Sheet）对象数组
getVersion()	String	返回正在使用的API的版本号，好像是没什么太大的作用。
getName()	String	获取Sheet的名称
getColumns()	int	获取Sheet表中所包含的总列数
getColumn(int column)	Cell[]	获取某一列的所有单元格，返回的是单元格对象数组
getRows()	int	获取Sheet表中所包含的总行数
getRow(int row)	Cell[]	获取某一行的所有单元格，返回的是单元格对象数组
getCell(int column, int row)	Cell	获取指定单元格的对象引用，需要注意的是它的两个参数，第一个是列数，第二个是行数，这与通常的行、列组合有些不同。
write()		写入Exel工作表
close()		关闭Excel工作薄对象
getPhysicalNumberOfCells()	int	获取该行的总列数
getSheetAt(int index)	Sheet	根据索引index获取对应的sheet页
getBodyStyle(Workbook wb)	CellStyle	设置excel中比如第一行Title样式
setCellStyle(CellStyle var1)	void	跟getBodyStyle()方法搭配设置样式
setCellValue(String var1)	void	设置值
getStringCellValue()	String	获取对应列的值

三、完整的项目代码

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")
@Controller
public 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 data
        for(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 data
        for(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");

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