Creating Excel with multiple worksheets results in a corrupted excel file

[jaimemin]

I tried to create an excel file with multiple worksheets. If I create an excel file with single worksheet, it works fine. However if I try to create multiple worksheets the file gets corrupt and tells me that no data can be recovered. Also, I've noticed that if I try to create multiple sheet I get an error log "not current zip current". I've noticed that other people had similar problem ex) issue #153 Can anyone tell me the solution for this kind of problem? thx in advance.

Below is the code I've written.

@GetMapping(value = "/download/excel/multisheet")
public void downloadExcelMultiSheet(HttpServletResponse response) throws UnsupportedEncodingException {
    log.info("### buildExcelDocument start !!!");
    response.setContentType("application/vnd.ms-excel");
    response.setCharacterEncoding("utf-8");
    String fileNameUtf8 = URLEncoder.encode("test", "UTF-8");
    response.setHeader("Content-Disposition", "attachment; filename=" + fileNameUtf8 + ".xlsx");

    try (OutputStream os = response.getOutputStream()) {
        Workbook wb
                = new Workbook(os, "excel", "1.0");
        Worksheet ws = wb.newWorksheet("Sheet1");
        ws.keepInActiveTab();
        createWorksheet(ws);

        Worksheet ws2 = wb.newWorksheet("Sheet2");
        createWorksheet(ws2);

        wb.finish();
    } catch (Exception e) {
        log.error("[downloadExcelMultiSheet] ERROR {}", e.getMessage());
    }

    log.info("### buildExcelDocument end !!!");
}

private void createWorksheet(Worksheet ws) throws IOException {
  for (int i = 0; i <= 7; i++) {
      ws.width(i, 50);
  }

  ws.value(0, 0, "header1");
  ws.value(0, 1, "header2");
  ws.value(0, 2, "header3");
  ws.value(0, 3, "header4");
  ws.value(0, 4, "header5");
  ws.value(0, 5, "header6");
  ws.value(0, 6, "header7");
  ws.value(0, 7, "header8");
  ws.range(0, 0, 0, 7).style().horizontalAlignment("center").set();
  ws.range(0, 0, 0, 7).style().fillColor(Color.LIGHT_GREEN).set();
  ws.range(0, 0, 0, 7).setName("header");
  ws.flush();

  int row = 1;
  int no = 1;

  for (int i = 0; i < 1; i++) {
      ExcelSearchCondition searchCondition = new ExcelSearchCondition();
      int count = excelService.count(searchCondition);
      int maxPageNum = count / 10000 + (count % 10000 > 0 ? 1 : 0);

      for (int pageNum = 1; pageNum <= maxPageNum; pageNum++) {
          PageHelper.startPage(pageNum, PAGE_SIZE, "REG_DT DESC");
          Page<ExcelVO> page = excelService.list(searchCondition);

          for (ExcelVO excelVO : page) {
              ws.value(row, 0, excelVO.getSampleColumn1());
              ws.value(row, 1, excelVO.getSampleColumn2());
              ws.value(row, 2, excelVO.getSampleColumn3());
              ws.value(row, 3, excelVO.getSampleColumn4());
              ws.value(row, 4, excelVO.getSampleColumn5());
              ws.value(row, 5, excelVO.getSampleColumn6());
              ws.value(row, 6, excelVO.getSampleColumn7());
              ws.value(row, 7, excelVO.getSampleColumn8());
              ws.range(row, 0, row, 7).style().horizontalAlignment("center").set();

              if (++row % 100 == 0) {
                  ws.flush();
              }
          }
      }
  }

  ws.flush();
}

[jaimemin]

it seems that if the flush method is executed on second sheet the file gets corrupted

[jaimemin]

nevermind I forgot to finish previous worksheet before flushing second worksheet.

Below is the code that works perfectly

@GetMapping(value = "/download/excel/multisheet")
public void downloadExcelMultiSheet(HttpServletResponse response) throws UnsupportedEncodingException {
    log.info("### buildExcelDocument start !!!");
    response.setContentType("application/vnd.ms-excel");
    response.setCharacterEncoding("utf-8");
    String fileNameUtf8 = URLEncoder.encode("test", "UTF-8");
    response.setHeader("Content-Disposition", "attachment; filename=" + fileNameUtf8 + ".xlsx");

    try (OutputStream os = response.getOutputStream()) {
        Workbook wb
                = new Workbook(os, "excel", "1.0");
        Worksheet ws = wb.newWorksheet("Sheet1");
        ws.keepInActiveTab();
        createWorksheet(ws);

        Worksheet ws2 = wb.newWorksheet("Sheet2");
        createWorksheet(ws2);

        wb.finish();
    } catch (Exception e) {
        log.error("[downloadExcelMultiSheet] ERROR {}", e.getMessage());
    }

    log.info("### buildExcelDocument end !!!");
}

private void createWorksheet(Worksheet ws) throws IOException {
  for (int i = 0; i <= 7; i++) {
      ws.width(i, 50);
  }

  ws.value(0, 0, "header1");
  ws.value(0, 1, "header2");
  ws.value(0, 2, "header3");
  ws.value(0, 3, "header4");
  ws.value(0, 4, "header5");
  ws.value(0, 5, "header6");
  ws.value(0, 6, "header7");
  ws.value(0, 7, "header8");
  ws.range(0, 0, 0, 7).style().horizontalAlignment("center").set();
  ws.range(0, 0, 0, 7).style().fillColor(Color.LIGHT_GREEN).set();
  ws.range(0, 0, 0, 7).setName("header");
  ws.flush();

  int row = 1;
  int no = 1;

  for (int i = 0; i < 1; i++) {
      ExcelSearchCondition searchCondition = new ExcelSearchCondition();
      int count = excelService.count(searchCondition);
      int maxPageNum = count / 10000 + (count % 10000 > 0 ? 1 : 0);

      for (int pageNum = 1; pageNum <= maxPageNum; pageNum++) {
          PageHelper.startPage(pageNum, PAGE_SIZE, "REG_DT DESC");
          Page<ExcelVO> page = excelService.list(searchCondition);

          for (ExcelVO excelVO : page) {
              ws.value(row, 0, excelVO.getSampleColumn1());
              ws.value(row, 1, excelVO.getSampleColumn2());
              ws.value(row, 2, excelVO.getSampleColumn3());
              ws.value(row, 3, excelVO.getSampleColumn4());
              ws.value(row, 4, excelVO.getSampleColumn5());
              ws.value(row, 5, excelVO.getSampleColumn6());
              ws.value(row, 6, excelVO.getSampleColumn7());
              ws.value(row, 7, excelVO.getSampleColumn8());
              ws.range(row, 0, row, 7).style().horizontalAlignment("center").set();

              if (++row % 100 == 0) {
                  ws.flush();
              }
          }
      }
  }

  ws.flush();
  ws.finish();
}