/**
   * テンプレートワークブックのシートを、帳票出力単位に変換する。
   *
   * @param workbook テンプレートワークブック
   * @param reportBook 帳票ワークブック情報
   * @return 削除が必要なテンプレートシートインデックス
   */
  private Set<Integer> expandTemplate(Workbook workbook, ReportBook reportBook) {

    Set<Integer> delTemplateIndexs = new TreeSet<Integer>(Collections.reverseOrder());
    Set<Integer> useTemplateIndexs = new HashSet<Integer>();

    // 出力シート単位にコピーする
    for (ReportSheet reportSheet : reportBook.getReportSheets()) {

      if (reportSheet != null) {
        if (reportSheet.getSheetName().equals(reportSheet.getTemplateName())) {
          // テンプレート名=出力シート名
          int lastSheetIndex = workbook.getNumberOfSheets() - 1;
          workbook.setSheetOrder(reportSheet.getSheetName(), lastSheetIndex);
          useTemplateIndexs.add(lastSheetIndex);
        } else {
          int tempIdx = workbook.getSheetIndex(reportSheet.getTemplateName());

          Sheet sheet = workbook.cloneSheet(tempIdx);
          workbook.setSheetName(workbook.getSheetIndex(sheet), reportSheet.getSheetName());
          delTemplateIndexs.add(tempIdx);
        }
      }
    }

    // 出力対象外シートを削除インデックスに追加
    for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
      Sheet sheet = workbook.getSheetAt(i);
      if (!isOutputSheet(sheet, reportBook)) {
        delTemplateIndexs.add(workbook.getSheetIndex(sheet));
      }
    }

    delTemplateIndexs.removeAll(useTemplateIndexs);

    return delTemplateIndexs;
  }
  public static void generate(
      String teacherName, String discipline, String groupName, String month, String year)
      throws Exception {

    ArrayList<Lesson> lessons =
        LessonManager.takeLessons(teacherName, discipline, groupName, month, year);

    try {

      FileInputStream sample =
          new FileInputStream("E:\\workspace\\excelgeneration\\report_sample.xls");
      FileOutputStream output = new FileOutputStream("E:\\workspace\\excelgeneration\\report.xls");

      // URL path1 = GenerateExcel.class.getResource("/");
      // System.out.println(path1);

      Workbook teacherReport = new HSSFWorkbook(sample);
      Sheet timesheet = teacherReport.cloneSheet(0);
      Sheet sheet = teacherReport.cloneSheet(1);

      CellStyle cellStyle = teacherReport.createCellStyle();
      cellStyle = teacherReport.createCellStyle();
      int[] typesCount = new int[12];
      // cout number of each type of lesson
      // 0 - Керівництво кафедрою
      // 1 - Лекції
      // 2 - Практичні заняття
      // 3 - Семінари
      // 4 - Лабораторні заняття
      // 5 - Розрахунково- графічні роботи
      // 6 - Курсове проектування
      // 7 - Заліки
      // 8 - Екзамени
      // 9 - Консультації
      // 10 - Дипломне проектування
      // 11 - Керівництво аспірантами

      timesheet.getRow(13).getCell(0).setCellValue(teacherName); // teacher name
      sheet.getRow(2).getCell(6).setCellValue(teacherName);

      Calendar current = Calendar.getInstance();
      current.set(
          Integer.parseInt(year, 10),
          Integer.parseInt(month, 10) - 1,
          current.getActualMaximum(Calendar.DAY_OF_MONTH));

      timesheet.getRow(15).getCell(3).setCellValue("01." + month + "." + year);
      timesheet
          .getRow(15)
          .getCell(6)
          .setCellValue(current.getActualMaximum(Calendar.DAY_OF_MONTH) + "." + month + "." + year);
      String substring = year.length() > 2 ? year.substring(year.length() - 2) : year;
      timesheet.getRow(15).getCell(9).setCellValue(substring + "р.");

      // timesheet.getRow(16).getCell(8).setCellValue(discipline);
      // System.out.println(discipline);

      int cl = 0, rw = 0; // lesson table row and cell counters
      for (int i = 0; i < lessons.size(); i++) {
        if (i > 19) {
          cl = 5;
          rw = 0;
        }

        timesheet
            .getRow(21 + rw)
            .getCell(cl)
            .setCellValue(
                lessons.get(i).getLessonDay() + "." + lessons.get(i).getLessonMonth() + "." + year);
        timesheet
            .getRow(21 + rw)
            .getCell(cl + 1)
            .setCellValue(getTimePair(lessons.get(i).getLessonNum()));
        timesheet.getRow(21 + rw).getCell(cl + 2).setCellValue(lessons.get(i).getType());
        timesheet.getRow(21 + rw).getCell(cl + 3).setCellValue(groupName);
        timesheet.getRow(21 + rw).getCell(cl + 4).setCellValue(2);
        rw++;
      }

      typesCount = countNumLesTypes(lessons);
      int res = 0;
      for (int i = 0; i < typesCount.length; i++) {

        sheet.getRow(13).getCell(1 + i).setCellValue(typesCount[i]);
        res += typesCount[i];
      }
      sheet.getRow(13).getCell(13).setCellValue(res);

      teacherReport.removeSheetAt(0);
      teacherReport.removeSheetAt(0);
      teacherReport.setSheetName(0, "timesheet");
      teacherReport.setSheetName(1, "sheet");

      teacherReport.write(output);
      output.close();
    } catch (Exception e) {
      e.printStackTrace();
    }
  }
  /** @see ReportRenderer#render(ReportData, String, OutputStream) */
  public void render(ReportData reportData, String argument, OutputStream out)
      throws IOException, RenderingException {

    try {
      log.debug("Attempting to render report with ExcelTemplateRenderer");
      ReportDesign design = getDesign(argument);
      Workbook wb = getExcelTemplate(design);

      if (wb == null) {
        XlsReportRenderer xlsRenderer = new XlsReportRenderer();
        xlsRenderer.render(reportData, argument, out);
      } else {
        Map<String, String> repeatSections = getRepeatingSections(design);

        // Put together base set of replacements.  Any dataSet with only one row is included.
        Map<String, Object> replacements = getBaseReplacementData(reportData, design);

        // Iterate across all of the sheets in the workbook, and configure all those that need to be
        // added/cloned
        List<SheetToAdd> sheetsToAdd = new ArrayList<SheetToAdd>();

        Set<String> usedSheetNames = new HashSet<String>();
        int numberOfSheets = wb.getNumberOfSheets();

        for (int sheetNum = 0; sheetNum < numberOfSheets; sheetNum++) {

          Sheet currentSheet = wb.getSheetAt(sheetNum);
          String originalSheetName = wb.getSheetName(sheetNum);

          String dataSetName = getRepeatingSheetProperty(sheetNum, repeatSections);
          if (dataSetName != null) {

            DataSet repeatingSheetDataSet = getDataSet(reportData, dataSetName, replacements);
            int dataSetRowNum = 0;
            for (Iterator<DataSetRow> rowIterator = repeatingSheetDataSet.iterator();
                rowIterator.hasNext(); ) {
              DataSetRow dataSetRow = rowIterator.next();
              dataSetRowNum++;
              Map<String, Object> newReplacements =
                  getReplacementData(
                      replacements, reportData, design, dataSetName, dataSetRow, dataSetRowNum);
              Sheet newSheet = (dataSetRowNum == 1 ? currentSheet : wb.cloneSheet(sheetNum));
              sheetsToAdd.add(
                  new SheetToAdd(newSheet, sheetNum, originalSheetName, newReplacements));
            }
          } else {
            sheetsToAdd.add(
                new SheetToAdd(currentSheet, sheetNum, originalSheetName, replacements));
          }
        }

        // Then iterate across all of these and add them in
        for (int i = 0; i < sheetsToAdd.size(); i++) {
          addSheet(wb, sheetsToAdd.get(i), usedSheetNames, reportData, design, repeatSections);
        }

        wb.write(out);
      }
    } catch (Exception e) {
      throw new RenderingException("Unable to render results due to: " + e, e);
    }
  }