查询数据库数据生成多个poi excel，压缩成一个文件下载

package com.casic.smbss.ncp.application.common.util;

import io.swagger.annotations.ApiOperation;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.util.*;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

@RestController
@RequestMapping(value = "excelDown")
public class ExcelDown {

    private static final String ZIP_NAME = "myfile.zip";

    @ApiOperation(value = "导出")
    @GetMapping("down")
    public void down(HttpServletResponse response) throws Exception {
        Map<String, HSSFWorkbook> map = createExcel();
        downloadExcelForZip(response, map, ZIP_NAME);
    }


    /**
     * 以压缩包的方式下载excel
     *
     * @param response
     * @param workbookMap
     */
    public static void downloadExcelForZip(HttpServletResponse response, Map<String, HSSFWorkbook> workbookMap, String zipName) {
        // 文件名外的双引号处理firefox的空格截断问题
        try {
            response.setContentType("application/*");
            response.setHeader("content-disposition", "attachment;filename=" + new String(zipName.getBytes("gb2312"), "ISO8859-1"));
            response.setCharacterEncoding("UTF-8");
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        try {
            ServletOutputStream outputStream = response.getOutputStream();
            ZipOutputStream out = new ZipOutputStream(outputStream);
            for (String fileName : workbookMap.keySet()) {
                HSSFWorkbook workbook = workbookMap.get(fileName);
                ZipEntry entry = new ZipEntry(fileName);
                out.putNextEntry(entry);
                // 这里讲一下，workBook.write会指定关闭数据流，如果这里直接用workbook.write(out)，
                // 下次就会抛出out已被关闭的异常，所有用ByteArrayOutputStream来拷贝一下。
                ByteArrayOutputStream bos = new ByteArrayOutputStream();
                workbook.write(bos);
                bos.writeTo(out);
                // 关闭输入流
                out.closeEntry();
            }
            if (out != null) {
                out.flush();
                out.close();
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public Map<String, HSSFWorkbook> createExcel() throws Exception {
        // 模拟查询数据
        List<String> lists = getData();
        // excel tiltle,模拟一列
        List<String> titles = new ArrayList<>(Arrays.asList("数据"));
        Map<String, HSSFWorkbook> workbookMap = new HashMap<>();
        List<String> sp = new ArrayList<>();
        for (int i = 1; i <= lists.size(); i++) {
            sp.add(lists.get(i - 1));
            // 模拟2条数据一个excel文件
            if ( i % 2 ==0 || (i == lists.size())) {

                // 创建一个webbook，对应一个Excel文件
                HSSFWorkbook wk = new HSSFWorkbook();
                // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
                HSSFSheet sheet = wk.createSheet();

                // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
                HSSFRow row = sheet.createRow(0);
                // 添加excel title
                HSSFCell cell = null;
                for (int t = 0, size = titles.size(); t < size; t++) {
                    cell = row.createCell(t);
                    cell.setCellValue(titles.get(t));
                }
                HSSFRow r = null;
                HSSFCell c = null;
                for (int j = 1; j <= sp.size(); j++) {
                    r = sheet.createRow(j);
                    c = r.createCell(0);
                    c.setCellValue(sp.get(j - 1));
                }
                String excelName = UUID.randomUUID().toString() + ".xls";
                workbookMap.put(excelName, wk);
                sp = new ArrayList<>();
            }
        }
        return workbookMap;
    }

    public static List<String> getData() {
        List<String> strings = new ArrayList<>();
        for (int i = 0; i <= 5; i++) {
            strings.add(String.valueOf(i));
        }
        return strings;
    }

}