导出简单的（zip）压缩包

导出一个简单的压缩包，笔记记录，样式效果看最后。

@RequestMapping(value = "/queryInspectionResults/export", method = RequestMethod.GET)
    public void queryInspectionResults(
            HttpServletResponse response,
            @RequestParam(value = "id", required = false) Long id,
    ) throws IOException {

		// 查询信息
        List inspectionResults = userService.getById(id);
        //  导出压缩包
        if (CollUtil.isEmpty(inspectionResults)) {
            throw new GlobalException("文件不存在");
        }
        ServletOutputStream outputStream = null;
        try {
            outputStream = response.getOutputStream();
            Integer index = 0;
            ZipOutputStream zipOutputStream = new ZipOutputStream(outputStream);
			// 响应体
            response.setContentType( "application/octet-stream ");
            response.setCharacterEncoding("utf-8");
            String fileName = URLEncoder.encode(itemName+"_汇总表_" + DateUtils.getFormatDateStr(DateUtils.YYYY_MM_DD), "UTF-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".zip");
            //详情
            for (InspectionResultsVo inspectionResult : inspectionResults) {
                index++;
                zipOutputStream.putNextEntry(new ZipEntry(index+"_"+inspectionResult.getName()+".xlsx"));
                // 创建 workbook 对象
                Workbook workbook = new XSSFWorkbook();
                PatrolTaskFormExcelBo excelBo = taskService.getTaskAllDevice(inspectionResult.getId(), null);
                // 构造 workbook ，
                generateTaskForm(workbook,excelBo);
                // 写入压缩包流
                workbook.write(zipOutputStream);
            }
            // 汇总
            zipOutputStream.putNextEntry(new ZipEntry(itemName+"列表汇总表"+".xlsx"));
            EasyExcel.write(zipOutputStream, InspectionResultsVo.class).sheet("列表汇总表").doWrite(inspectionResults);
            zipOutputStream.flush();
            zipOutputStream.close();
        } catch (Exception e) {
            response.reset();
            response.setContentType("application/json");
            response.setCharacterEncoding("utf-8");
            Map map = new HashMap();
            map.put("status", "failure");
            map.put("message", "下载文件失败" + e.getMessage());
            response.getWriter().println(JSON.toJSonString(map));
        }
    }

 @Override
    public List getTaskAllDevice(Long taskId, Integer type) {
        // 全部数据
        TaskAllDataVo allDataVo = taskMapper.getTaskAllDataVo(taskId, type);
        if (allDataVo == null) {
            throw new GlobalException("设备数据不存在！");
        }

        // 获取基本数据
        PatrolTaskFormExcelBo patrolTaskFormExcelBo = new PatrolTaskFormExcelBo();
        // 导出文件名
        patrolTaskFormExcelBo.setTaskName(allDataVo.getTaskName());
//        patrolTaskFormExcelBo.setFormName(allDataVo.getTaskName());
        patrolTaskFormExcelBo.setDateStr(allDataVo.getStartTime() + "~~" + allDataVo.getEndTime());
        patrolTaskFormExcelBo.setStationName(allDataVo.getStationName());
        patrolTaskFormExcelBo.setFinishPersonName(allDataVo.getWorkUserName());


        // 任务设备List
        List allDeviceVos = allDataVo.getTaskAllDeviceVos();
        // 设备按表单分组
        Map> allDeviceVoMap = allDeviceVos.stream().collect(Collectors.groupingBy(TaskAllDeviceVo::getFormId));
        // 填充表单内容,多种表单的模板
        List patrolTaskFormExcelBos = contentFill(allDeviceVoMap, patrolTaskFormExcelBo);

        return patrolTaskFormExcelBos;
    }

构造workbook,根据需求的表格的样式制作excel合并样式

public void generateExcel(Workbook workbook, List excelBoList) {
        if (CollUtil.isEmpty(excelBoList)) {
            throw new GlobalException("没有导出的巡视结果数据!");
        }
        for (PatrolTaskFormExcelBo excelBo : excelBoList) {
            List tableExcelBoList = excelBo.getTableExcelBoList();
            // 检测列表有没有数据
            if (CollUtil.isEmpty(tableExcelBoList)) {
                return;
            }
            // 设置sheet名称为表单名称
            Sheet sheet = workbook.createSheet(tableExcelBoList.get(0).getFormName());
            // 设置列宽
            sheet.setColumnWidth(1, 50 * 256);
            // 写excel的标题头特殊样式
            createAndSetSpecialTitle(excelBo, workbook, sheet);
            int currentRowIndex = 3;
            // 遍历数据
            for (PatrolTaskFormTableExcelBo patrolTaskFormTableExcelBo : tableExcelBoList) {
                // 表格的内嵌标题
                createAndSetTableTitle(patrolTaskFormTableExcelBo, workbook, sheet, currentRowIndex);
                currentRowIndex += 3;
                // 规整数据填充
                Collection> dataList = patrolTaskFormTableExcelBo.getDataList();
                if (CollUtil.isNotEmpty(dataList)) {
                    PoiUtils.createAndSetTableContent(dataList, workbook, sheet, currentRowIndex);
                    currentRowIndex += dataList.size();
                }
                currentRowIndex += 2;
            }
        }
    }

表头样式设置

 private void createAndSetSpecialTitle(PatrolTaskFormExcelBo patrolTaskFormExcelBo, Workbook workbook, Sheet sheet) {
        // 合并特殊标题单元格
        CellRangeAddress formNameTitleRange = new CellRangeAddress(0, 0, 0, 11);
        CellRangeAddress dateStrTitleRange = new CellRangeAddress(1, 1, 0, 11);
        CellRangeAddress stationNameTitleRange = new CellRangeAddress(2, 2, 0, 5);
        CellRangeAddress finishPersonNameTitleRange = new CellRangeAddress(2, 2, 6, 11);

        sheet.addMergedRegion(formNameTitleRange);
        sheet.addMergedRegion(dateStrTitleRange);
        sheet.addMergedRegion(stationNameTitleRange);
        sheet.addMergedRegion(finishPersonNameTitleRange);

        // 创建标题单元格并设置值
        Row formNameTitleRow = sheet.createRow(0);
        Row dateStrTitleRow = sheet.createRow(1);
        Row stationNameTitleRow = sheet.createRow(2);
        Cell formNameTitleCell = formNameTitleRow.createCell(0);
        Cell dateStrTitleCell = dateStrTitleRow.createCell(0);
        Cell stationNameTitleCell = stationNameTitleRow.createCell(0);
        Cell finishPersonNameTitleCell = stationNameTitleRow.createCell(6);

        // 设置样式
        CellStyle formNameTitleCellStyle = PoiUtils.createDefaultCellStyle(workbook);
        formNameTitleCellStyle.setFont(
                PoiUtils.setFont(PoiUtils.createFont(workbook), (short) 15, IndexedColors.BLACK.getIndex(), true));
        formNameTitleCell.setCellStyle(formNameTitleCellStyle);

        CellStyle dateStrTitleCellStyle = PoiUtils.createDefaultCellStyle(workbook);
        dateStrTitleCellStyle.setFont(
                PoiUtils.setFont(PoiUtils.createFont(workbook), (short) 10, IndexedColors.BLACK.getIndex(), true));
        dateStrTitleCell.setCellStyle(dateStrTitleCellStyle);

        CellStyle stationNameTitleCellStyle = PoiUtils.createDefaultCellStyle(workbook);
        stationNameTitleCellStyle.setAlignment(HorizontalAlignment.LEFT);
        stationNameTitleCellStyle.setFont(
                PoiUtils.setFont(PoiUtils.createFont(workbook), (short) 9, IndexedColors.BLACK.getIndex(), true));
        stationNameTitleCell.setCellStyle(stationNameTitleCellStyle);

        CellStyle finishPersonNameTitleCellStyle = PoiUtils.createDefaultCellStyle(workbook);
        finishPersonNameTitleCellStyle.setAlignment(HorizontalAlignment.RIGHT);
        finishPersonNameTitleCellStyle.setFont(
                PoiUtils.setFont(PoiUtils.createFont(workbook), (short) 9, IndexedColors.BLACK.getIndex(), true));
        finishPersonNameTitleCell.setCellStyle(finishPersonNameTitleCellStyle);

        // 设置值
        formNameTitleCell.setCellValue(patrolTaskFormExcelBo.getFormName());
        dateStrTitleCell.setCellValue(patrolTaskFormExcelBo.getDateStr());
        stationNameTitleCell.setCellValue("公交站：" + patrolTaskFormExcelBo.getStationName());
        finishPersonNameTitleCell.setCellValue("完成人：" + patrolTaskFormExcelBo.getFinishPersonName());

        // 设置合并单元格边框
        PoiUtils.setCellRangeDefaultStyle(BorderStyle.THIN, formNameTitleRange, sheet);
        PoiUtils.setCellRangeDefaultStyle(BorderStyle.THIN, dateStrTitleRange, sheet);
        PoiUtils.setCellRangeDefaultStyle(BorderStyle.THIN, stationNameTitleRange, sheet);
        PoiUtils.setCellRangeDefaultStyle(BorderStyle.THIN, finishPersonNameTitleRange, sheet);

    }

单个属性的样式赋值

private void createAndSetTableTitle(PatrolTaskFormTableExcelBo patrolTaskFormTableExcelBo, Workbook workbook, Sheet sheet, int currentRowIndex) {

        int firstRowIndex = currentRowIndex;
        int secondRowIndex = currentRowIndex + 1;
        int thirdRowIndex = currentRowIndex + 2;
        CellRangeAddress firstTitleRange = new CellRangeAddress(firstRowIndex, firstRowIndex, 2, 11);
        CellRangeAddress secondTitleRange = new CellRangeAddress(secondRowIndex, thirdRowIndex, 0, 0);
        CellRangeAddress thirdTitleRange = new CellRangeAddress(secondRowIndex, thirdRowIndex, 1, 1);
        CellRangeAddress fourthTitleRange = new CellRangeAddress(secondRowIndex, secondRowIndex, 2, 11);

        sheet.addMergedRegion(firstTitleRange);
        sheet.addMergedRegion(secondTitleRange);
        sheet.addMergedRegion(thirdTitleRange);
        sheet.addMergedRegion(fourthTitleRange);

        Row firstRow = sheet.createRow(currentRowIndex);
        Cell serialNumberCell = firstRow.createCell(0);
        serialNumberCell.setCellStyle(PoiUtils.createDefaultCellStyle(workbook));
        serialNumberCell.setCellValue("序号");

        Cell patrolContentCell = firstRow.createCell(1);
        patrolContentCell.setCellStyle(PoiUtils.createDefaultCellStyle(workbook));
        patrolContentCell.setCellValue("巡视内容");

        Cell patrolResultCell = firstRow.createCell(2);
        patrolResultCell.setCellStyle(PoiUtils.createDefaultCellStyle(workbook));
        patrolResultCell.setCellValue("巡视结果");

        Row secondRow = sheet.createRow(secondRowIndex);
        Cell itemNameCell = secondRow.createCell(1);
        // FIXME 应该不对
        itemNameCell.setCellStyle(PoiUtils.createDefaultCellStyle(workbook));
        itemNameCell.setCellValue("充电机");
        Cell itemName2Cell = secondRow.createCell(2);
        // FIXME 应该也不对
        itemName2Cell.setCellStyle(PoiUtils.createDefaultCellStyle(workbook));
        itemName2Cell.setCellValue("充电桩");

        List deviceCodeTitleList = patrolTaskFormTableExcelBo.getDeviceCodeTitleList();
        if (CollUtil.isEmpty(deviceCodeTitleList)) {
            return;
        }
        // 填充设备名称列表头
        int currentColumnIndex = 2;
        Row thirdRow = sheet.createRow(thirdRowIndex);
        for (String deviceCodeTitle : deviceCodeTitleList) {
            Cell cell = thirdRow.createCell(currentColumnIndex++);
            cell.setCellStyle(PoiUtils.createDefaultCellStyle(workbook));
            cell.setCellValue(deviceCodeTitle);
        }

        // 设置合并单元格边框
        PoiUtils.setCellRangeDefaultStyle(BorderStyle.THIN, firstTitleRange, sheet);
        PoiUtils.setCellRangeDefaultStyle(BorderStyle.THIN, secondTitleRange, sheet);
        PoiUtils.setCellRangeDefaultStyle(BorderStyle.THIN, thirdTitleRange, sheet);
        PoiUtils.setCellRangeDefaultStyle(BorderStyle.THIN, fourthTitleRange, sheet);

        // 设置没有值时候的边框
        int size = deviceCodeTitleList.size();
        if (size >= 10) {
            return;
        }

        for (int i = size; i < 10; i++) {
            Cell cell = thirdRow.createCell(currentColumnIndex++);
            CellStyle defaultCellStyle = PoiUtils.createDefaultCellStyle(workbook);
            cell.setCellStyle(defaultCellStyle);
        }

    }

createAndSetTableContent 方法，对数据进行赋值。

public  static void createAndSetTableContent(Collection> dataList, Workbook workbook, Sheet sheet, int currentRowIndex) {

        int currentColumnIndex = 0;
		// 行数据填充
        for (List data: dataList) {
            currentColumnIndex = 0;
            Row dataRow = sheet.createRow(currentRowIndex++);
            if (CollUtil.isEmpty(data)) {
                continue;
            }
            int size = data.size();
            // 列数据填充
            for (int i = 0; i < size; i++) {
                String dataStr = data.get(i);
                Cell cell = dataRow.createCell(currentColumnIndex++);
                CellStyle defaultCellStyle = createDefaultCellStyle(workbook);
                if (i == 1) {
                    defaultCellStyle.setAlignment(HorizontalAlignment.LEFT);
                }
                cell.setCellStyle(defaultCellStyle);
                cell.setCellValue(dataStr);
            }

            // 设置没有值时候的边框
            if (size >= 12) {
                continue;
            }
            // 剩余空白补全
            for (int i = size; i < 12; i++) {
                Cell cell = dataRow.createCell(currentColumnIndex++);
                CellStyle defaultCellStyle = createDefaultCellStyle(workbook);
                cell.setCellStyle(defaultCellStyle);
            }
        }
    }

样式结果展示：

导出简单的（zip）压缩包

Java相关栏目本月热门文章