excel导出数据很多，拆分成多个，以Excel的方式导出

package com.komlin.modular.work.controller;

import com.komlin.component.consts.FilePathConsts;
import com.komlin.component.model.ResponseBase;
import com.komlin.component.page.Pager;
import com.komlin.component.utils.QrCodeUtils;
import com.komlin.component.utils.UUIDUtils;
import com.komlin.modular.draw.model.service.DrawQrcodeService;
import com.komlin.modular.draw.model.service.LuckyDrawService;
import com.komlin.modular.product.model.service.ProductBatchService;
import com.komlin.modular.product.model.service.ProductInfoService;
import com.komlin.modular.product.model.service.UnitCodeService;
import com.komlin.modular.work.bo.TraceQrcodeBo;
import com.komlin.modular.work.model.entity.TbTraceQrcode;
import com.komlin.modular.work.model.service.GenerateRecordService;
import com.komlin.modular.work.model.service.TraceQrcodeService;
import com.komlin.modular.work.model.service.WorkFlowService;
import com.komlin.modular.work.model.service.WorkTraceMappingService;
import org.apache.commons.io.FileUtils;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.util.StringUtils;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

/**
 * Created by zql on 2017/12/11.
 */
@Controller
@RequestMapping("works")
public class ExportController {
    @Autowired
    private TraceQrcodeService qrcodeService;
    @Autowired
    private ProductBatchService batchService;
    @Autowired
    private DrawQrcodeService drawQrcodeService;
    @Autowired
    private LuckyDrawService luckyDrawService;
    @Autowired
    private WorkFlowService workFlowService;
    @Autowired
    private WorkTraceMappingService workTraceMappingService;
    @Autowired
    private UnitCodeService unitCodeService;

    @Autowired
    private ProductInfoService productInfoService;

    @Autowired
    private GenerateRecordService generateRecordService;

   /* @ResponseBody
    @RequestMapping("exportCollectQrocde")
    public ResponseBase exportCollectQrocde(TraceQrcodeBo bo) {
        ResponseBase result;
        int MAX_PAGER = 500;
        try {
            if (bo == null) {
                bo = new TraceQrcodeBo();
            }
            bo.pagesize = 5000;
            File model = new File(FilePathConsts.rootPath
                    + "file/excel/export_collect_qrcode.xls");
            FileInputStream fis = new FileInputStream(model);
            POIFSFileSystem fs = new POIFSFileSystem(fis);
            HSSFWorkbook wb = new HSSFWorkbook(fs);
            HSSFSheet sheet; sheet = wb.getSheetAt(0);
            HSSFPatriarch patriarch = sheet.createDrawingPatriarch();

            int startRow = 2;

            int height = sheet.getRow(2).getHeight();

            Pager pager = qrcodeService.getCollectedQrcodeByPage(bo);

            if (pager.getPagecount() > MAX_PAGER) {
                return new ResponseBase(0, "数据量过多，导出失败！请明细筛选条件！");
            }

            File folder = new File(FilePathConsts.excel_temp);
            if (!folder.exists()) {
                folder.mkdirs();
            }
            String outFileName = UUIDUtils.getUniquekey() + ".xls";
            FileOutputStream fos = new FileOutputStream(new File(
                    FilePathConsts.excel_temp + outFileName));
            wb.write(fos);
            try {
                fos.flush();
            } catch (OutOfMemoryError e) {
                e.printStackTrace();
                return new ResponseBase(0, "数据量过多，导出失败！请明细筛选条件！");
            }
            fos.close();
           *//* for (int i = 0; i < pager.getPagecount(); i++) {*//*
            int rowNumber = 2;
            for (int i = 0; i < pager.getPagecount(); i++) {
                bo.page = i + 1;
                pager = qrcodeService.getCollectedQrcodeByPage(bo);
                List<TbTraceQrcode> qrcodeList = (List<TbTraceQrcode>) pager.getRows();
                if (qrcodeList == null) {
                    break;
                }

                //打开流
                model = new File(FilePathConsts.excel_temp + outFileName);
                fis = new FileInputStream(model);
                fs = new POIFSFileSystem(fis);
                wb = new HSSFWorkbook(fs);
                sheet = wb.getSheetAt(0);
                patriarch = sheet.createDrawingPatriarch();

                CellStyle style = sheet.getRow(1).getCell(1).getCellStyle();

                for (TbTraceQrcode qrcode : qrcodeList) {
                    Row row = sheet.createRow(startRow);
                    row.setHeight((short) height);

                    row.createCell(1).setCellStyle(style);
                    row.createCell(2).setCellStyle(style);
                    row.createCell(3).setCellStyle(style);
                    row.createCell(4).setCellStyle(style);
                    row.createCell(5).setCellStyle(style);
                    row.createCell(6).setCellStyle(style);
                    row.createCell(7).setCellStyle(style);
                    row.createCell(8).setCellStyle(style);

                   *//* row.getCell(1).setCellValue(qrcode.getTqQrcode());*//*
                    ByteArrayOutputStream byteArrayOut = QrCodeUtils.qrCode(FilePathConsts.qrcode_prefix + qrcode.getTqQrcode());
                    HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 255, (short) 1, rowNumber, (short) 1, rowNumber ++);
                    //  sheet.setColumnWidth(startCol, width);
                    anchor.setAnchorType(3);
                    //插入图片
                    patriarch.createPicture(anchor, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));

                    if (qrcode.getProductInfo() != null) {
                        row.getCell(2).setCellValue(qrcode.getProductInfo().getPiCode());
                        row.getCell(3).setCellValue(qrcode.getProductInfo().getPiName());
                        row.getCell(4).setCellValue(qrcode.getProductInfo().getPiSize());
                    }
                    if (qrcode.getProductBatch() != null) {
                        row.getCell(5).setCellValue(qrcode.getProductBatch().getPbStrCode());
                        if (!StringUtils.isEmpty(qrcode.getProductBatch().getPbPdate())) {
                            row.getCell(6).setCellValue(qrcode.getProductBatch().getPbPdate().substring(0, 10));
                        } else {
                            row.getCell(6).setCellValue("-");
                        }
                        row.getCell(7).setCellValue(qrcode.getProductBatch().getPbChecker());
                        row.getCell(8).setCellValue(qrcode.getProductBatch().getPbFlCode());
                    }
                    startRow++;
                }


                //关闭流
                fos = new FileOutputStream(new File(
                        FilePathConsts.excel_temp + outFileName));
                wb.write(fos);
                try {
                    fos.flush();
                } catch (OutOfMemoryError e) {
                    e.printStackTrace();
                    return new ResponseBase(0, "数据量过多，导出失败！请明细筛选条件！");
                }
                fos.close();
            }


            result = new ResponseBase(1, "", outFileName);

        } catch (Exception e) {
            result = new ResponseBase(0, "服务器发生异常");
            e.printStackTrace();
        }
        return result;
    }*/


    /**
     * 文件导出
     */
    @RequestMapping("exportCollectQrocde")
    public void exportCollectQrocde(HttpServletRequest request, TraceQrcodeBo bo, HttpServletResponse response) throws IOException {
        request.setCharacterEncoding("utf-8");
        ResponseBase result;
        String fileName = null;
        File zip = null;
        if (bo == null) {
            bo = new TraceQrcodeBo();
        }
        bo.setPiName(new String(bo.getPiName().getBytes("iso-8859-1"),"utf-8"));
        bo.pagesize = 5000;

        Pager pager = null;
        try {
            pager = qrcodeService.getCollectedQrcodeByPage(bo);

            //设置批次文件名
            String fileSuff = "";
            SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
            fileSuff = sdf.format(new Date());
            fileName = "导出TEST"+fileSuff;
            List<String> fileNames = new ArrayList<String>();  //存放生成的文件名称

            //设置相应头
            String filename = fileName;
            filename = new String(filename.getBytes("GBK"), "iso-8859-1");
            response.reset();
            response.setContentType("application/octet-stream;charset=UTF-8");
            response.setHeader("Content-Disposition", "attachment;filename="+filename+".zip");
            response.addHeader("pargam", "no-cache");
            response.addHeader("Cache-Control", "no-cache");

            File filePath = new File(FilePathConsts.excel_temp);
            if (!filePath.exists()) {
                filePath.mkdirs();
            }

            zip = new File(FilePathConsts.excel_temp+fileName+".zip");  //压缩文件路径

            //3.分批次生成excel
            for (int i = 0; i < pager.getPagecount(); i++) {
                bo.page = i + 1;
                pager = qrcodeService.getCollectedQrcodeByPage(bo);
                List<TbTraceQrcode> qrcodeList = (List<TbTraceQrcode>) pager.getRows();
                if (qrcodeList == null) {
                    break;
                }
                File model = new File(FilePathConsts.rootPath
                        + "file/excel/export_collect_qrcode.xls");
                FileInputStream fis = new FileInputStream(model);
                POIFSFileSystem fs = new POIFSFileSystem(fis);
                HSSFWorkbook wb = new HSSFWorkbook(fs);
                HSSFSheet sheet; sheet = wb.getSheetAt(0);
                HSSFPatriarch patriarch = sheet.createDrawingPatriarch();

                int startRow = 2;

                CellStyle style = sheet.getRow(1).getCell(1).getCellStyle();
                int height = sheet.getRow(2).getHeight();
                int rowNumber = 2;
                for (TbTraceQrcode qrcode : qrcodeList) {
                    Row row = sheet.createRow(startRow);
                    row.setHeight((short) height);

                    row.createCell(1).setCellStyle(style);
                    row.createCell(2).setCellStyle(style);
                    row.createCell(3).setCellStyle(style);
                    row.createCell(4).setCellStyle(style);
                    row.createCell(5).setCellStyle(style);
                    row.createCell(6).setCellStyle(style);
                    row.createCell(7).setCellStyle(style);
                    row.createCell(8).setCellStyle(style);

                    ByteArrayOutputStream byteArrayOut = QrCodeUtils.qrCode(FilePathConsts.qrcode_prefix + qrcode.getTqQrcode());
                    HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 255, (short) 1, rowNumber, (short) 1, rowNumber ++);
                    //  sheet.setColumnWidth(startCol, width);
                    anchor.setAnchorType(3);
                    //插入图片
                    patriarch.createPicture(anchor, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));

                    if (qrcode.getProductInfo() != null) {
                        row.getCell(2).setCellValue(qrcode.getProductInfo().getPiCode());
                        row.getCell(3).setCellValue(qrcode.getProductInfo().getPiName());
                        row.getCell(4).setCellValue(qrcode.getProductInfo().getPiSize());
                    }
                    if (qrcode.getProductBatch() != null) {
                        row.getCell(5).setCellValue(qrcode.getProductBatch().getPbStrCode());
                        if (!StringUtils.isEmpty(qrcode.getProductBatch().getPbPdate())) {
                            row.getCell(6).setCellValue(qrcode.getProductBatch().getPbPdate().substring(0, 10));
                        } else {
                            row.getCell(6).setCellValue("-");
                        }
                        row.getCell(7).setCellValue(qrcode.getProductBatch().getPbChecker());
                        row.getCell(8).setCellValue(qrcode.getProductBatch().getPbFlCode());
                    }
                    startRow++;
                }

                //生成excel文件
                File folder = new File(FilePathConsts.excel_temp);
                if (!folder.exists()) {
                    folder.mkdirs();
                }

                String outFileName = fileSuff + "_" + i + ".xls";
                FileOutputStream fos = new FileOutputStream(new File(
                        FilePathConsts.excel_temp + outFileName));
                wb.write(fos);
                fos.flush();
                fos.close();

                fileNames.add(outFileName);
            }

            //4.导出zip压缩文件
            exportZip(response, fileNames, zip);

          /*  result = new ResponseBase(1, "导出成功", FilePathConsts.excel_temp+fileName+".zip");*/
        }  catch (Exception e) {
            e.printStackTrace();
        }
    }


    /**
     * 文件压缩并导出到客户端
     * @param response
     * @param fileNames
     * @param zip
     * @throws FileNotFoundException
     * @throws IOException
     */
    private void exportZip(HttpServletResponse response, List<String> fileNames, File zip)
            throws IOException {
        OutputStream  outPut = response.getOutputStream();

        //1.压缩文件
        File srcFile[] = new File[fileNames.size()];
        for (int i = 0; i < fileNames.size(); i++) {
            srcFile[i] = new File(FilePathConsts.excel_temp + fileNames.get(i));
        }
        byte[] byt = new byte[1024];
        ZipOutputStream out = new ZipOutputStream(new FileOutputStream(zip));
       /* out.setEncoding("UTF-8");*/
        for (int i = 0; i < srcFile.length; i++) {
            FileInputStream in = new FileInputStream(srcFile[i]);
            out.putNextEntry(new ZipEntry(srcFile[i].getName()));
            int length;
            while((length=in.read(byt)) > 0){
                out.write(byt,0,length);
            }
            out.closeEntry();
            in.close();
        }
        out.close();

        //2.删除服务器上的临时文件(excel)
        for (int i = 0; i < srcFile.length; i++) {
            File temFile = srcFile[i];
            if(temFile.exists() && temFile.isFile()){
                temFile.delete();
            }
        }

        //3.返回客户端压缩文件
        FileInputStream inStream = new FileInputStream(zip);
        byte[] buf = new byte[4096];
        int readLenght;
        while((readLenght = inStream.read(buf)) != -1 ){
            outPut.write(buf,0,readLenght);
        }
        inStream.close();
        outPut.close();

        //4.删除压缩文件
        if(zip.exists() && zip.isFile()){
            zip.delete();
        }
    }
}