Beispiel #1
0
 /**
  * @param wb
  * @param reviewerSheetName
  */
 public static void create(Workbook wb, String sheetName) {
   int sheetNum = wb.getSheetIndex(sheetName);
   if (sheetNum >= 0) {
     wb.removeSheetAt(sheetNum);
   }
   Sheet sheet = wb.createSheet(sheetName);
   CellStyle headerStyle = AbstractSheet.createHeaderStyle(wb);
   CellStyle defaultStyle = AbstractSheet.createLeftWrapStyle(wb);
   Row row = sheet.createRow(0);
   for (int i = 0; i < MultiDocumentSpreadsheet.MAX_DOCUMENTS; i++) {
     sheet.setColumnWidth(i, COL_WIDTH * 256);
     sheet.setDefaultColumnStyle(i, defaultStyle);
     Cell cell = row.createCell(i);
     cell.setCellStyle(headerStyle);
   }
 }
 public static void create(Workbook wb, String sheetName) {
   int sheetNum = wb.getSheetIndex(sheetName);
   if (sheetNum >= 0) {
     wb.removeSheetAt(sheetNum);
   }
   Sheet sheet = wb.createSheet(sheetName);
   CellStyle headerStyle = AbstractSheet.createHeaderStyle(wb);
   CellStyle defaultStyle = AbstractSheet.createLeftWrapStyle(wb);
   Row row = sheet.createRow(0);
   for (int i = 0; i < HEADER_TITLES.length; i++) {
     sheet.setColumnWidth(i, COLUMN_WIDTHS[i] * 256);
     sheet.setDefaultColumnStyle(i, defaultStyle);
     Cell cell = row.createCell(i);
     cell.setCellStyle(headerStyle);
     cell.setCellValue(HEADER_TITLES[i]);
   }
 }
  public void doDownload() throws Throwable {
    if (StringUtils.isBlank(selectedDateFrom) || StringUtils.isBlank(selectedDateTo)) {
      MessageUtils.getInstance().addErrorMessage("msgDialogDL", "Please select date");
      return;
    }

    if (selectedDateFrom.compareTo(selectedDateTo) < 0) {
      try {
        Date cycleFrom = DateUtil.convStringToDate(SIMPLE_DATE_PATTERN, selectedDateFrom);
        Date cycleTo = DateUtil.convStringToDate(SIMPLE_DATE_PATTERN, selectedDateTo);

        DetachedCriteria criteria = DetachedCriteria.forClass(ConfirmationRecord.class);
        criteria.add(Restrictions.between("cycleFrom", cycleFrom, cycleTo));
        criteria.add(Restrictions.between("cycleTo", cycleFrom, cycleTo));
        criteria.addOrder(Order.asc("cycleFrom"));
        criteria.addOrder(Order.asc("id"));
        List<ConfirmationRecord> list = confirmationRecordService.findByCriteria(criteria);

        Workbook wb = createExcel(list);

        if (wb != null) {
          wb.removeSheetAt(0);
          FileTransfer ft = new FileTransfer();
          String fileName =
              EXCEL_NAME_PATTERN
                  + TEMPLATE_EXCEL_CONF_REPORT_PATH.substring(
                      TEMPLATE_EXCEL_CONF_REPORT_PATH.lastIndexOf("."),
                      TEMPLATE_EXCEL_CONF_REPORT_PATH.length());
          fileName =
              fileName.replaceAll(
                  "#fdd-MMM-yyyy", DateUtil.convDateToString(DISPLAY_DATE_PATTERN, maxCycleFrom));
          fileName =
              fileName.replaceAll(
                  "#tdd-MMM-yyyy", DateUtil.convDateToString(DISPLAY_DATE_PATTERN, maxCycleTo));
          ft.fileDownload(fileName, wb);
        }

        init();
      } catch (Exception e) {
        e.printStackTrace();
      }
    } else {
      MessageUtils.getInstance().addErrorMessage("msgDialogDL", "Invalid date");
    }
  }
  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();
    }
  }
  /**
   * ワークブックの変換処理を実行する。
   *
   * @param reportBook ワークブックの置換情報
   */
  private void processBook(ReportBook reportBook) {

    if (reportBook == null) {
      return;
    }

    Workbook workbook = getTemplateWorkbook(reportBook.getInputStream());

    for (ReportProcessListener listener : listeners) {
      listener.preBookParse(workbook, reportBook);
    }

    checkReportBook(reportBook);

    // テンプレート展開
    Set<Integer> delTemplateIndexs = expandTemplate(workbook, reportBook);

    // 出力ファイル取得
    BookController controller = new BookController(workbook);

    // Parserの設定
    for (ReportsTagParser<?> tagParser : parsers.values()) {
      controller.addTagParser(tagParser);
    }
    // リスナーの設定
    controller.addSheetParseListener(new RemoveAdapter());
    for (ReportProcessListener listener : listeners) {
      controller.addSheetParseListener(listener);
    }

    // Exporterの設定
    for (ConvertConfiguration configuration : reportBook.getConfigurations()) {
      if (configuration == null) {
        continue;
      }
      for (ReportBookExporter reportExporter : exporters.values()) {
        if (configuration.getFormatType().equals(reportExporter.getFormatType())) {
          reportExporter.setConfiguration(configuration);
          reportExporter.setOutputStream(reportBook.getOutputStream());
          controller.addBookExporter(reportExporter);
        }
      }
    }

    ReportsParserInfo reportsParserInfo = new ReportsParserInfo();
    reportsParserInfo.setReportParsers(new ArrayList<ReportsTagParser<?>>(parsers.values()));
    reportsParserInfo.setReportBook(reportBook);

    BookData bookData = controller.getBookData();
    bookData.clear();
    for (String sheetName : controller.getSheetNames()) {
      if (sheetName.startsWith(BookController.COMMENT_PREFIX)) {
        continue;
      }
      ReportSheet reportSheet = ReportsUtil.getReportSheet(sheetName, reportBook);
      if (reportSheet != null) {

        reportsParserInfo.setParamInfo(reportSheet.getParamInfo());
        // 解析の実行
        SheetData sheetData = controller.parseSheet(sheetName, reportsParserInfo);
        // 結果の追加
        controller.getBookData().putSheetData(sheetName, sheetData);
      }
    }

    // 不要テンプレート削除
    for (Integer deleteSheetIndex : delTemplateIndexs) {
      workbook.removeSheetAt(deleteSheetIndex);
    }

    // 出力処理前にリスナー呼び出し
    for (ReportProcessListener listener : listeners) {
      listener.postBookParse(workbook, reportBook);
    }

    // 出力処理の実行
    for (BookExporter exporter : controller.getExporter()) {
      if (exporter != null) {
        exporter.setup();
        try {
          exporter.export(workbook, bookData);
        } finally {
          exporter.tearDown();
        }
      }
    }
  }
Beispiel #6
0
 public void removeSheetAt(int sheetNo) {
   hssfWorkbook.removeSheetAt(sheetNo);
   sheets.remove(sheetNo);
 }