java下载图片和表格的压缩包(图片和表格在同级目录)

@Override
    public void downloadWorkerZip(Integer hospitalId, String searchStr, Date entryDate, Integer serviceAge, Integer state, Integer sex, Integer photo) {
        Page page = this.workerRecordList(hospitalId, searchStr, entryDate, serviceAge, state, sex, photo, 0, 0);
        List<WorkerRecordVo> data = page.getData(); //所有护工
        if(org.apache.commons.collections.CollectionUtils.isEmpty(data)){
            throw new ServiceException(ExtendedErrorCode.E201.code(),"无可导出数据");
        }
        String nursingIds = data.stream().map(WorkerDO::getNursingId).map(String::valueOf).collect(Collectors.joining(","));
        String sql="select id,attendant_id,cert_name,cert_type,acquisition_time,expire_time,pictures from ns_attendant_cert " +
                "where attendant_id in (" + nursingIds +") and is_delete =0";
        //所有护工的职业信息
         List<AttendantCertDO> allCertDOs = extendDaoSupport.queryForList(sql, AttendantCertDO.class);

        //所有护工的工作经历
        sql="select id,attendant_id,company_name,entry_date,leave_date,leave_reason,labels " +
                "from ns_attendant_work_history where attendant_id in (" + nursingIds +") and is_delete=0";
        List<AttendantWorkHistoryVO> allWorkHistory = extendDaoSupport.queryForList(sql, AttendantWorkHistoryVO.class);

        data.forEach(vo->{
            if (!CollectionUtils.isEmpty(allCertDOs)) {
                List<AttendantCertDO> attendantCertDOs = allCertDOs.stream().filter(item -> item.getAttendantId().equals(vo.getNursingId())).collect(Collectors.toList());
                vo.setAttendantCertDOList(attendantCertDOs);
            }
            if (!CollectionUtils.isEmpty(allWorkHistory)) {
                List<AttendantWorkHistoryVO> historyVOS = allWorkHistory.stream().filter(item -> item.getAttendantId().equals(vo.getNursingId())).collect(Collectors.toList());
                vo.setWorkHistoryVOList(historyVOS);
            }
        });

        this.exportWorkerZip(data);
    }
    private void exportWorkerZip(List<WorkerRecordVo> data){
        HttpServletResponse response = ThreadContextHolder.getHttpResponse();
        String[] baseColumns = {"护工姓名", "护工编号",
                "所属医院", "手机号码",
                "身份证号", "民族",
                "性别", "出生日期",
                "籍贯", "户籍地址",
                "家庭地址", "星级","是否有保险",
                "文化程度", "婚姻状况",
                "语言能力", "入职日期",
                "紧急联系人", "紧急联系电话",
                "开户行名称", "开户行代码",
                "银行卡号", "账户类型",
                "开户证件类型", "账户名",
                "证件号","银行预留号码","体检时间","有效期"};
        List<String> list = Arrays.asList(baseColumns);
        List<String> columnNames = new ArrayList<>();
        columnNames.addAll(list);

         //护理证书最多的记录
        WorkerRecordVo certWorkerRecord = data.stream().filter(vo->vo.getAttendantCertDOList()!=null).max(Comparator.comparingInt(vo -> vo.getAttendantCertDOList().size())).orElse(null);
       if (!ObjectUtils.isEmpty(certWorkerRecord)) {
           List<AttendantCertDO> attendantCertDOList = certWorkerRecord.getAttendantCertDOList().stream().filter(vo->Integer.valueOf(0).equals(vo.getCertType())).collect(Collectors.toList());
           for (int i = 1; i <= attendantCertDOList.size(); i++) {
               columnNames.add("护理证书"+i);
               columnNames.add("获取时间"+i);
           }
       }

        //筛选出工作经历最多的护工
        WorkerRecordVo workerRecordVo = data.stream().filter(vo->vo.getWorkHistoryVOList()!=null).max(Comparator.comparingInt(vo -> vo.getWorkHistoryVOList().size())).orElse(null);
        if (!ObjectUtils.isEmpty(workerRecordVo)) {
            List<AttendantWorkHistoryVO> workHistoryList = workerRecordVo.getWorkHistoryVOList();
            for (int i = 1; i <= workHistoryList.size(); i++) {
                columnNames.add("入职公司"+i);
                columnNames.add("入职时间"+i);
                columnNames.add("离职时间"+i);
                columnNames.add("离职原因"+i);
            }
        }

        String sheetName = "护工档案信息表";
        String[] headers = columnNames.toArray(new String[columnNames.size()]);
        // 创建excel工作簿
        HSSFWorkbook workBook = CreateWorkerSheetUtil.createWorkerSheet(headers, sheetName);
        //获取工作表sheet
        HSSFSheet sheet = workBook.getSheet(sheetName);
        //填充数据

        for (int i = 0; i < data.size(); i++) {
            WorkerRecordVo entity = data.get(i);
            HSSFRow nextRow = sheet.createRow(i + 2);
            Integer column = 0;
            nextRow.createCell(column++).setCellValue(entity.getName());
            nextRow.createCell(column++).setCellValue(entity.getEmployeeNumber());
            nextRow.createCell(column++).setCellValue(entity.getHospitalName());
            nextRow.createCell(column++).setCellValue(entity.getMobile());
            nextRow.createCell(column++).setCellValue(entity.getIdCardNumber());
            nextRow.createCell(column++).setCellValue(entity.getNation());
            nextRow.createCell(column++).setCellValue(entity.getSex()==null?"":entity.getSex()==1?"男":"女");
            nextRow.createCell(column++).setCellValue(entity.getBirthday()==null?"":DateUtil.toString(entity.getBirthday(),"yyyy-MM-dd"));
            nextRow.createCell(column++).setCellValue(entity.getNativePlace());// "籍贯
            nextRow.createCell(column++).setCellValue(entity.getPermanentAddress());//户籍地址
            nextRow.createCell(column++).setCellValue(entity.getResidentialAddress());//家庭地址
            nextRow.createCell(column++).setCellValue(entity.getStarLevel()==null?"":getStartLevel(entity.getStarLevel())); //星级 0：普通护工；4:四星护工；5:五星护工
            nextRow.createCell(column++).setCellValue(entity.getInsurance()==null?"":entity.getInsurance()==1?"有":"无");//是否有保险
            nextRow.createCell(column++).setCellValue(getEducation(entity.getEducation()==null?0:entity.getEducation()));//文化程度 0：无；1:小学,2:初中,3:高中,4:大学',
            nextRow.createCell(column++).setCellValue(entity.getMaritalStatus()==null?"":getMaritalStatus(entity.getMaritalStatus()));//婚姻状况 0：未婚 1:已婚 2:离异
            nextRow.createCell(column++).setCellValue(entity.getLanguageAbility());
            nextRow.createCell(column++).setCellValue(entity.getEntryDate()==null?"":DateUtil.toString(entity.getEntryDate(),"yyyy-MM-dd"));
            nextRow.createCell(column++).setCellValue(entity.getEmergencyContact());
            nextRow.createCell(column++).setCellValue(entity.getEmergencyPhone());
            nextRow.createCell(column++).setCellValue(entity.getBankName());//开户行名称
            nextRow.createCell(column++).setCellValue(entity.getBankCode());
            nextRow.createCell(column++).setCellValue(entity.getAccountNo());//银行卡号
            nextRow.createCell(column++).setCellValue(entity.getAccountType());//账户类型
            nextRow.createCell(column++).setCellValue(entity.getIdType());
            nextRow.createCell(column++).setCellValue(entity.getAccountName());//账户名
            nextRow.createCell(column++).setCellValue(entity.getLicenseNum());//证件号
            nextRow.createCell(column++).setCellValue(entity.getTelephone());//银行预留号码

            List<AttendantCertDO> entityCertDOList = entity.getAttendantCertDOList(); //所有职业信息
            if (!CollectionUtils.isEmpty(entityCertDOList)) {
                //健康证
                AttendantCertDO attendantCertDO = entityCertDOList.stream().filter(vo -> Integer.valueOf(1).equals(vo.getCertType())).findFirst().orElse(null);
                if (!ObjectUtils.isEmpty(attendantCertDO)) {
                    nextRow.createCell(column++).setCellValue(attendantCertDO.getAcquisitionTime()==null?"":DateUtil.toString(attendantCertDO.getAcquisitionTime(),"yyyy-MM-dd"));//体检时间(时间戳)
                    nextRow.createCell(column++).setCellValue(attendantCertDO.getExpireTime()==null?"":DateUtil.toString(attendantCertDO.getExpireTime(),"yyyy-MM-dd"));//有效期(时间戳)
                }

                //职业证书
                List<AttendantCertDO> certList = entityCertDOList.stream().filter(vo -> Integer.valueOf(0).equals(vo.getCertType())).collect(Collectors.toList());
                if (!CollectionUtils.isEmpty(certList)) {
                    for (AttendantCertDO certDO : certList) {
                        nextRow.createCell(column++).setCellValue(certDO.getCertName());//证书名
                        nextRow.createCell(column++).setCellValue(certDO.getAcquisitionTime()==null?"":DateUtil.toString(certDO.getAcquisitionTime(),"yyyy-MM-dd"));//有效期(时间戳)
                    }
                }
            }

            int maxCertSize = certWorkerRecord==null?0:certWorkerRecord.getAttendantCertDOList().size();
            int certSize = entityCertDOList == null ? 0 : entityCertDOList.size();
            int sub = maxCertSize * 2 - certSize * 2;
            column=column+sub;

            List<AttendantWorkHistoryVO> workHistoryVOList = entity.getWorkHistoryVOList();//所有工作经历
            if (!CollectionUtils.isEmpty(workHistoryVOList)) {
                for (AttendantWorkHistoryVO historyVO : workHistoryVOList) {
                    nextRow.createCell(column++).setCellValue(historyVO.getCompanyName());//公司名
                    nextRow.createCell(column++).setCellValue(historyVO.getEntryDate()==null?"":DateUtil.toString(historyVO.getEntryDate(),"yyyy-MM-dd"));//入职(时间戳)
                    nextRow.createCell(column++).setCellValue(historyVO.getLeaveDate()==null?"":DateUtil.toString(historyVO.getLeaveDate(),"yyyy-MM-dd"));//离职
                    nextRow.createCell(column++).setCellValue(historyVO.getLeaveReason());//原因
                }
            }
        }

        Map<String,String[]> workerPics = getWorkerPics(data);

     response.setContentType("application/octet-stream");
        response.setHeader("Access-Control-Expose-Headers", "downloadFileName, Content-Disposition");
        try {
            response.setHeader("downloadFileName", URLEncoder.encode("护工档案.zip", "UTF-8"));
            response.setHeader("Content-Disposition", "attachment;filename*=UTF-8''" + URLEncoder.encode("护工档案.zip", "UTF-8"));
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }

        try {
            /* ServletOutputStream out = response.getOutputStream();
             workBook.write(out);
             out.close();*/这段代码可以单独下载一个表格使用
             //将图片放入压缩包
            ZipOutputStream zos = new    ZipOutputStream(response.getOutputStream());
            workerPics.forEach((key,value)->{
                for (int i = 0; i < value.length; i++) {
                    try {
                        URL url = new URL(value[i]);
                        zos.putNextEntry(new ZipEntry(key+i+".jpg"));
                        InputStream fis = url.openConnection().getInputStream();
                        byte[] buffer = new byte[1024];
                        int r = 0;
                        while ((r = fis.read(buffer)) != -1) {
                            zos.write(buffer, 0, r);
                        }
                        fis.close();
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                }
            });

            //将表格放入压缩包
            ByteArrayOutputStream os = new ByteArrayOutputStream();
            try {
                workBook.write(os);
            } catch (IOException e) {
                e.printStackTrace();
            }
            byte[] content = os.toByteArray();
            //写入输入流
            InputStream is = new ByteArrayInputStream(content);
            // 用于将数据压缩成Zip文件格式
            zos.putNextEntry(new ZipEntry("护工档案信息表.xls"));
            int bytesRead = 0;
            while ((bytesRead = is.read()) != -1) {
                zos.write(bytesRead);
            }
            zos.flush();
            zos.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

package com.enation.app.javashop.framework.util;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
/**
 * @program: javashop-b2b2c
 * @description: 创建表头工具类
 * @author: Xu.YanPing
 * @create: 2020-08-03 16:30
 **/
public class CreateWorkerSheetUtil {
    public static HSSFWorkbook createWorkerSheet(String[] headers, String sheetName) {
        HSSFWorkbook workBook = new HSSFWorkbook();// 创建excel工作簿

        // 设置表头字体样式
        HSSFFont headfont = workBook.createFont();
        headfont.setFontName("黑体");
        headfont.setFontHeightInPoints((short) 22);// 字体大小
        headfont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗
        HSSFCellStyle headstyle = workBook.createCellStyle();
        headstyle.setFont(headfont);
        headstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
        headstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
        headstyle.setLocked(true);
        headstyle.setWrapText(true);// 自动换行

        // 设置列头字体样式
        HSSFFont columnHeadFont = workBook.createFont();
        columnHeadFont.setFontName("宋体");
        columnHeadFont.setFontHeightInPoints((short) 11);
        columnHeadFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

        // 设置列头的样式
        HSSFCellStyle columnHeadStyle = workBook.createCellStyle();
        columnHeadStyle.setFont(columnHeadFont);
        columnHeadStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
        columnHeadStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
        columnHeadStyle.setLocked(true);
        columnHeadStyle.setWrapText(true);
        HSSFFont font = workBook.createFont();
        font.setFontName("Arial");
        font.setFontHeightInPoints((short) 11);
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

        // 设置普通单元格样式
        HSSFCellStyle style = workBook.createCellStyle();
        style.setFont(font);
        style.setAlignment(HSSFCellStyle.ALIGN_LEFT);// 左右居中
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);// 上下居中
        style.setWrapText(true);
        HSSFCellStyle centerstyle = workBook.createCellStyle();
        centerstyle.setFont(font);
        centerstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
        centerstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
        centerstyle.setWrapText(true);

        HSSFSheet sheet = workBook.createSheet(sheetName);// 创建一个工作表sheet,名为sheetName

        // 对第一行做处理，标题的创建
        HSSFRow row = sheet.createRow(0);// 创建第一行
        row.setHeight((short) 600);// 设置表格行的高度
        HSSFCell cell0 = row.createCell(0);
        cell0.setCellValue(new HSSFRichTextString(sheetName));// 设置表名
        cell0.setCellStyle(headstyle);

        //合并第一行单元格
        CellRangeAddress range = new CellRangeAddress(0, 0, 0, 16);
        sheet.addMergedRegion(range);
        //第二行列名
        HSSFRow row2 = sheet.createRow(1);
        row2.setHeight((short) 500);
        HSSFCell cell = null;

        // 插入第一行数据标题
        for (int i = 0; i < headers.length; i++) {
            cell = row2.createCell(i);
            cell.setCellValue(headers[i]);
            cell.setCellStyle(columnHeadStyle);
            sheet.setColumnWidth(i, 5500);
        }
        return workBook;
    }
}

 /**
     * 获取护工的照片：获取一个护工对应的所有图片的url路径
     * @param data
     * @return
     */
    private Map<String,String[]> getWorkerPics(List<WorkerRecordVo> data) {
        Map<String,String[]> map = new HashMap<>();
        for (WorkerRecordVo item : data) {
            List<String> picUrls = new ArrayList<>();
            if (null != item.getIdCardBack())
                picUrls.add(item.getIdCardBack());
            if (null != item.getIdCardFront())
                picUrls.add(item.getIdCardFront());
            if (null != item.getPhoto())
                picUrls.add(item.getPhoto());

            List<AttendantCertDO> attendantCertDOList = item.getAttendantCertDOList();
            if (!CollectionUtils.isEmpty(attendantCertDOList)) {
                String pictures = attendantCertDOList.stream().filter(vo -> vo.getPictures() != null).map(AttendantCertDO::getPictures).collect(Collectors.joining(","));
                if (!StringUtil.isEmpty(pictures))
                picUrls.addAll(Arrays.asList(pictures.split(",")));
            }
            map.put(item.getName()+"("+item.getEmployeeNumber()+")", picUrls.toArray(new String[picUrls.size()]));
        }
        return map;
    }