一、業(yè)務(wù)背景

業(yè)務(wù)需求：從數(shù)據(jù)庫查詢多個list集合信息封裝excel，每個excel都有2個sheet頁，填充不同的信息，最后將所有excel打包成zip文件，以流的形式返回給客戶端，供客戶端另存為窗口下載。

1. 只發(fā)出一次請求
2. 每個excel表中到數(shù)據(jù)記錄不能超過2條
3. excel文件或者zip包不會上傳服務(wù)器，而是查詢后直接封裝excel，然后把多個excel封裝成zip包直接返回

之前看過其他人的方案大概有2種：

方案1：打包成zip包后上傳到服務(wù)器某個路徑下，然后在讀取該路徑的zip文件，以流的形式返回給客戶端。
方案2：不上傳服務(wù)器，而是查詢后直接封裝excel，然后把多個excel封裝成zip包直接返回。（本人采用的就是第二種方案）

最終的效果，如圖

二、實現(xiàn)思路

1. 設(shè)置HttpServletResponse的參數(shù)，比如header、contentType
2. 新建一個Workbook對象并置為空，同時初始化相關(guān)對象，比如List、File等
3. 從數(shù)據(jù)庫查詢多條list
4. 以其中為主的一個list計算分頁數(shù)量
5. 循環(huán)遍歷list開始
6. 初始化新的Workbook對象，并設(shè)置相應(yīng)的Title
7. 將list拆分成多個段，分別寫到的Workbook對象中
8. 將Workbook對象填充到List<XSSFWorkbook>中
9. 重復(fù)步驟6至步驟8直至寫完所有數(shù)據(jù)
10. 最后統(tǒng)一封裝zip壓縮包并導(dǎo)出，調(diào)用downFileByStream方法
11. 初始化ZipOutputStream對象
12. 循環(huán)遍歷List<XSSFWorkbook>將每一個wb寫入ZipOutputStream對象中，并將內(nèi)存流寫入Zip文件，即：將每一個excel封裝到zip包中
13. 關(guān)閉ZipOutputStream

二、準(zhǔn)備工作

1.準(zhǔn)備data模板.xlsx

2.引入poi相關(guān)依賴，用于操作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壓縮輸入/輸出流，相關(guān)方法知識點要有所了解

Apache POI包中的HSSFWorkbook、XSSFWorkbook、SXSSFWorkbook的區(qū)別如下:

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;

/**
 * 導(dǎo)出【用戶、組、隸屬關(guān)系】工具
 * @Author 211145187
 * @Date 2023/4/13 16:29
 **/
public class ExportUtil {
    private static Logger logger = LoggerFactory.getLogger(ExportUtil.class);

    /**
     * 自定義獲取分頁總頁數(shù)的方法
     * @param count 查詢集合數(shù)量
     * @param pageSize 配置文件中設(shè)置的單文件存儲最大條數(shù)
     * @return 總頁數(shù)
     */
    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數(shù)據(jù)
     * @param pageNum 頁碼
     * @param pageSize 頁容量
     * @param pageCount 總頁數(shù)
     * @return 分頁后的list數(shù)據(jù)
     */
    public static <T> List<T> getPageList(List<T> list, Integer pageNum, Integer pageSize, Integer pageCount){

        /**開始索引*/
        int beginIndex = 0;
        /**結(jié)束索引*/
        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轉(zhuǎn)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寫入內(nèi)存流
                excels.get(i).write(baos);
                ZipEntry zipEntry = new ZipEntry("data" + i + ".xlsx");
                zipOutputStream.putNextEntry(zipEntry);
                // 將內(nèi)存流寫入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
#導(dǎo)出excel配置，單文件存儲最大數(shù)量
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;

    //構(gòu)建教師集合數(shù)據(jù)
    public List<Teacher> buildTeacherList1() {
        List<Teacher> teacherList = new ArrayList<>();
        Teacher teacher1 = new Teacher();
        teacher1.setName("周杰倫");
        teacher1.setClasses("三年二班");
        teacher1.setCollege("魔法學(xué)院");
        teacher1.setAlias("Jay Chou");
        teacherList.add(teacher1);
        Teacher teacher2 = new Teacher();
        teacher2.setName("陳奕迅");
        teacher2.setClasses("三年二班");
        teacher2.setCollege("魔法學(xué)院");
        teacher2.setAlias("Eason");
        teacherList.add(teacher2);
        Teacher teacher3 = new Teacher();
        teacher3.setName("林俊杰");
        teacher3.setClasses("三年二班");
        teacher3.setCollege("魔法學(xué)院");
        teacher3.setAlias("Eason");
        teacherList.add(teacher3);
        Teacher teacher4 = new Teacher();
        teacher4.setName("張杰");
        teacher4.setClasses("三年二班");
        teacher4.setCollege("魔法學(xué)院");
        teacher4.setAlias("Eason");
        teacherList.add(teacher4);
        return teacherList;
    }
    //構(gòu)建分?jǐn)?shù)集合數(shù)據(jù)
    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導(dǎo)出多個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");

        //創(chuàng)建HSSFWorkbook對象(excel的文檔對象)
        XSSFWorkbook wb = null;
        List<Teacher> teacherList = new ArrayList<>();
        //構(gòu)建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的分頁數(shù)量
        Integer pageCount = ExportUtil.getPageCount(buildTeacherList.size(), pageLimitSize);
        List<XSSFWorkbook> excels = new ArrayList<>();
        try {
            for(Integer pageNum = 1; pageNum < pageCount + 1; pageNum++) {
                //注意：每次循環(huán)遍歷前都需要初始化新的wb對象
                //注意情況1：如果是初始化wb空對象然后手動添加title，下方三行代碼不會報錯
//                wb = new XSSFWorkbook();
//                buildScoreSheetTitle(wb);
//                buildTeacherSheetTitle(wb);

                //注意情況2：如果是初始化wb對象，并且以流的形式初始化，那么io流必須放在里面才行，如果放在for循環(huán)外面會報“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);
            }
            //最后統(tǒng)一封裝zip壓縮包并導(dǎo)出
            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;
    }
    /**
     * 填充分?jǐn)?shù)頁信息
     * @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;
    }

    /**
     * 構(gòu)建分?jǐn)?shù)表excel的標(biāo)頭
     * @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);
        //創(chuàng)建單元格并設(shè)置單元格內(nèi)容
        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));
    }
    /**
     * 構(gòu)建教師表excel的標(biāo)頭
     * @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);
        //創(chuàng)建單元格并設(shè)置單元格內(nèi)容
        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("所屬學(xué)院");
        cell2.setCellStyle(getHeadStyle(wb));
        XSSFCell cell3 = row.createCell(3);
        cell3.setCellValue("別名");
        cell3.setCellStyle(getHeadStyle(wb));
    }

    /**
     * 設(shè)置樣式
     * @Author 211145187
     * @Date 2022/2/22 20:15
     * @Param wb wb
     * @Return CellStyle
     **/
    private CellStyle getHeadStyle(Workbook wb) {
        CellStyle cellStyle = wb.createCellStyle();
        //用于設(shè)置前景顏色
        cellStyle.setFillForegroundColor(IndexedColors.SKY_BLUE.getIndex());
        /**
         * setFillPattern用于設(shè)置單元格填充樣式
         * 注意：
         *      1）setFillPattern必須設(shè)置否則光設(shè)置setFillForegroundColor無效
         *      2）3.10.1版本支持short類型參數(shù)，而3.17版本支持FillPatternType類型參數(shù)
         */
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        return cellStyle;
    }
}

四、可能遇到的問題

錯誤場景1：java.io.IOException: Stream closed

原因分析：

我這里導(dǎo)致流關(guān)閉會有2種場景觸發(fā)：
場景1：工具類方法downFileByStream()中的for循環(huán)執(zhí)行write()方法。
場景2：wb = new XSSFWorkbook(io);通過IO流初始化wb對象時。

解決方案：

針對場景1中的解決方案就是：Workbook的write()方法最終會關(guān)閉它寫入的輸出流。如果只是一次性全部輸出那就不會碰到這種問題，但是現(xiàn)在的場景是工具類方法downFileByStream()中的for循環(huán)執(zhí)行write()方法，所以解決方案就是用一個ByteArrayOutputStream去接收存儲當(dāng)前Workbook的內(nèi)容，這樣哪怕Workbook關(guān)閉了，但是內(nèi)容已經(jīng)提前存入了ByteArrayOutputStream中，只要讀取了就可以繼續(xù)使用。因此才有這樣的如下寫法：

/**
     * 直接下載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寫入內(nèi)存流
                excels.get(i).write(baos);
                ZipEntry zipEntry = new ZipEntry("data" + i + ".xlsx");
                zipOutputStream.putNextEntry(zipEntry);
                // 將內(nèi)存流寫入Zip文件
                zipOutputStream.write(baos.toByteArray());
            }
            zipOutputStream.closeEntry();
            zipOutputStream.flush();
            zipOutputStream.close();
        }catch (Exception e){
            logger.error("downFileByStream==========fail:{}", e.getMessage());
        }
    }

針對場景2中的解決方案就是：把IO流初始化提到for循環(huán)里面，如實有了如下寫法：

for(Integer pageNum = 1; pageNum < pageCount + 1; pageNum++) {
                //注意：每次循環(huán)遍歷前都需要初始化新的wb對象
                //注意情況1：如果是初始化wb空對象然后手動添加title，下方三行代碼不會報錯
//                wb = new XSSFWorkbook();
//                buildScoreSheetTitle(wb);
//                buildTeacherSheetTitle(wb);

                //注意情況2：如果是初始化wb對象，并且以流的形式初始化，那么io流必須放在里面才行，如果放在for循環(huán)外面會報“Stream Closed”錯誤
                InputStream io = new FileInputStream(templateFile);
                wb = new XSSFWorkbook(io);
                ...
            }

錯誤場景2：調(diào)用接口沒有另存為彈窗，但是F12查看接口結(jié)果返回一堆亂碼

錯誤原因分析：可能是返回結(jié)果HttpServletResponse設(shè)置的setContentType格式不對。

正確的應(yīng)該設(shè)置為如下，一定要設(shè)置為application/zip：文章來源地址http://www.zghlxwxcb.cn/news/detail-740863.html

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

到了這里，關(guān)于Java實現(xiàn)導(dǎo)出多個excel表打包到zip文件中，供客戶端另存為窗口下載的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！