コード例 #1
0
 private void putGroupCell(HSSFSheet sheet, int rowIndex, String name) {
   cell = sheet.getRow(rowIndex).createCell(1);
   cell.setCellValue(name);
   CellRangeAddress region = new CellRangeAddress(rowIndex, rowIndex, 1, data.getNumbOfCols());
   sheet.addMergedRegion(utils.getBorderedRegion(region, sheet, wb));
   cell.setCellStyle(utils.getGroupStyle(wb));
 }
コード例 #2
0
  public IndicatorEntryCalcTemplate(final IndicatorEntryData data, final SpreadsheetDocument exDoc)
      throws Throwable {
    this.data = data;

    Table table = null;
    String tableName = data.getLocalizedVersion("flexibleElementIndicatorsList").replace(" ", "_");
    if (exDoc == null) {
      doc = SpreadsheetDocument.newSpreadsheetDocument();
      table = doc.getSheetByIndex(0);
      table.setTableName(tableName);
    } else {
      doc = exDoc;
      table = doc.appendSheet(tableName);
    }

    coreCellStyle = CalcUtils.prepareCoreStyle(doc);

    int rowIndex = -1;
    int cellIndex = 0;

    // skip row
    ++rowIndex;

    // title
    CalcUtils.putMainTitle(
        table,
        ++rowIndex,
        data.getNumbOfCols(),
        data.getLocalizedVersion("flexibleElementIndicatorsList").toUpperCase());

    // emptry row
    CalcUtils.putEmptyRow(table, ++rowIndex);

    // column headers
    row = table.getRowByIndex(++rowIndex);
    cellIndex = 0;
    CalcUtils.putHeader(row, ++cellIndex, data.getLocalizedVersion("name"));
    CalcUtils.putHeader(row, ++cellIndex, data.getLocalizedVersion("code"));
    CalcUtils.putHeader(row, ++cellIndex, data.getLocalizedVersion("targetValue"));
    CalcUtils.putHeader(row, ++cellIndex, data.getLocalizedVersion("value"));
    row.setHeight(5, false);

    // empty row
    row = table.getRowByIndex(++rowIndex);
    row.setHeight(3.8, false);
    row.getCellByIndex(1).setCellStyleName(null);
    row.getCellByIndex(2).setCellStyleName(null);
    row.getCellByIndex(3).setCellStyleName(null);
    row.getCellByIndex(4).setCellStyleName(null);

    for (final IndicatorGroup group : data.getIndicators().getGroups()) {
      row = table.getRowByIndex(++rowIndex);
      CalcUtils.putGroupCell(table, 1, rowIndex, group.getName());
      CalcUtils.mergeCell(table, 1, rowIndex, data.getNumbOfCols(), rowIndex);
      for (final IndicatorDTO indicator : group.getIndicators()) {
        // indicator's detail sheet
        createDetailSheet(indicator);
        row = table.getRowByIndex(++rowIndex);
        // ind name
        cell = CalcUtils.createBasicCell(table, 1, rowIndex, null);
        CalcUtils.applyLink(
            cell,
            indicator.getName(),
            ExportConstants.INDICATOR_SHEET_PREFIX + indicator.getName());
        // code
        CalcUtils.createBasicCell(table, 2, rowIndex, indicator.getCode());
        // target
        putValueCell(table, rowIndex, 3, indicator.getObjective(), true);
        // current value
        putValueCell(table, rowIndex, 4, data.getFormattedValue(indicator), true);
      }
    }

    table.getColumnByIndex(0).setWidth(3.8);
    table.getColumnByIndex(1).setWidth(83);
    table.getColumnByIndex(2).setWidth(55);
    table.getColumnByIndex(3).setWidth(55);
    table.getColumnByIndex(4).setWidth(55);
  }
コード例 #3
0
  private void createDetailSheet(final IndicatorDTO indicator) throws Throwable {
    final boolean isQualitative = indicator.getAggregation() == IndicatorDTO.AGGREGATE_MULTINOMIAL;
    final Table tableEx =
        doc.appendSheet(
            CalcUtils.normalizeAsLink(
                ExportConstants.INDICATOR_SHEET_PREFIX + indicator.getName()));
    int rowIndex = -1;

    List<PivotTableData.Axis> leaves =
        data.getEntryMap().get(indicator.getId()).getRootColumn().getLeaves();
    int numbOfLeaves = leaves.size();
    int numbOfCols = 4;

    // back to list link
    row = tableEx.getRowByIndex(++rowIndex);
    cell = tableEx.getCellByPosition(1, rowIndex);
    CalcUtils.applyLink(
        cell,
        data.getLocalizedVersion("goToIndicatorsList"),
        data.getLocalizedVersion("flexibleElementIndicatorsList"));
    CalcUtils.mergeCell(tableEx, 1, rowIndex, data.getNumbOfCols(), rowIndex);

    // title
    CalcUtils.putMainTitle(tableEx, ++rowIndex, numbOfCols, indicator.getName());

    // empty row
    CalcUtils.putEmptyRow(tableEx, ++rowIndex);

    // put details
    putBasicInfo(
        tableEx, ++rowIndex, data.getLocalizedVersion("code"), indicator.getCode(), numbOfCols);

    putBasicInfo(
        tableEx,
        ++rowIndex,
        data.getLocalizedVersion("group"),
        data.getGroupMap().get(indicator.getGroupId()),
        numbOfCols);

    // type
    String type = null;
    ;
    if (isQualitative) {
      // qualitative
      type = data.getLocalizedVersion("qualitative");
    } else {
      // quantitative
      type = data.getLocalizedVersion("quantitative");
    }
    putBasicInfo(tableEx, ++rowIndex, data.getLocalizedVersion("type"), type, numbOfCols);

    // conditional
    if (isQualitative) {
      // qualitative

      // possible values
      row = tableEx.getRowByIndex(++rowIndex);

      // key
      cell = CalcUtils.putHeader(row, 1, data.getLocalizedVersion("possibleValues"));
      cell.setHorizontalAlignment(ExportConstants.ALIGH_HOR_RIGHT);

      // value
      final MultiItemText itemText = data.formatPossibleValues(indicator.getLabels());
      CalcUtils.createBasicCell(tableEx, 2, rowIndex, itemText.text);
      CalcUtils.mergeCell(tableEx, 2, rowIndex, numbOfCols, rowIndex);

    } else {
      // quantitative

      // aggregation method
      String aggrMethod = null;
      if (indicator.getAggregation() == IndicatorDTO.AGGREGATE_AVG)
        aggrMethod = data.getLocalizedVersion("average");
      else aggrMethod = data.getLocalizedVersion("sum");
      putBasicInfo(
          tableEx,
          ++rowIndex,
          data.getLocalizedVersion("aggregationMethod"),
          aggrMethod,
          numbOfCols);
      // units
      putBasicInfo(
          tableEx, ++rowIndex, data.getLocalizedVersion("units"), indicator.getUnits(), numbOfCols);

      // target value
      putBasicInfo(
          tableEx,
          ++rowIndex,
          data.getLocalizedVersion("targetValue"),
          indicator.getObjective(),
          numbOfCols);
    }

    // source of ver
    putBasicInfo(
        tableEx,
        ++rowIndex,
        data.getLocalizedVersion("sourceOfVerification"),
        indicator.getSourceOfVerification(),
        numbOfCols);

    // comment
    putBasicInfo(
        tableEx,
        ++rowIndex,
        data.getLocalizedVersion("indicatorComments"),
        indicator.getDescription(),
        numbOfCols);

    // value
    putBasicInfo(
        tableEx,
        ++rowIndex,
        data.getLocalizedVersion("value"),
        data.getFormattedValue(indicator),
        numbOfCols);
    // empty row
    CalcUtils.putEmptyRow(tableEx, ++rowIndex);

    row = tableEx.getRowByIndex(rowIndex);
    row.getCellByIndex(1).setCellStyleName(null);
    row.getCellByIndex(2).setCellStyleName(null);
    row.getCellByIndex(3).setCellStyleName(null);
    row.getCellByIndex(4).setCellStyleName(null);

    // data entry
    // header
    row = tableEx.getRowByIndex(++rowIndex);
    int cellIndex = 0;
    CalcUtils.putHeader(row, ++cellIndex, data.getLocalizedVersion("sideAndMonth"));
    Map<String, Integer> columnIndexMap = new HashMap<String, Integer>();
    for (PivotTableData.Axis axis : leaves) {
      CalcUtils.putHeader(row, ++cellIndex, axis.getLabel());
      columnIndexMap.put(axis.getLabel(), cellIndex);
    }

    // rows
    for (PivotTableData.Axis axis :
        data.getEntryMap().get(indicator.getId()).getRootRow().getChildren()) {
      row = tableEx.getRowByIndex(++rowIndex);
      CalcUtils.putHeader(row, 1, axis.getLabel());
      // populate empty cells
      for (int i = 0; i < numbOfLeaves; i++) {
        cell = CalcUtils.createBasicCell(tableEx, i + 2, rowIndex, "");
      }

      // insert values
      for (Map.Entry<PivotTableData.Axis, PivotTableData.Cell> entry : axis.getCells().entrySet()) {
        cellIndex = columnIndexMap.get(entry.getKey().getLabel());
        Object value = null;
        boolean rightAligned = false;
        if (isQualitative) {
          value = data.getLabelByIndex(indicator.getLabels(), entry.getValue().getValue());
        } else {
          value = new Long(Math.round(entry.getValue().getValue()));
          rightAligned = true;
        }
        putValueCell(tableEx, rowIndex, cellIndex, value, rightAligned);
      }
    }
    // col width
    tableEx.getColumnByIndex(0).setWidth(3.8);
    tableEx.getColumnByIndex(1).setWidth(60);
    for (int i = 2; i < 2 + numbOfLeaves; i++) {
      tableEx.getColumnByIndex(i).setWidth(30);
    }
  }
コード例 #4
0
  public IndicatorEntryExcelTemplate(final IndicatorEntryData data, final HSSFWorkbook wb) {
    this.data = data;
    this.wb = wb;
    final HSSFSheet sheet =
        wb.createSheet(data.getLocalizedVersion("flexibleElementIndicatorsList"));
    utils = new ExcelUtils(wb);
    int rowIndex = -1;
    int cellIndex = 0;

    // empty row
    utils.putEmptyRow(sheet, ++rowIndex, 8.65f);

    // title
    utils.putMainTitle(
        sheet,
        ++rowIndex,
        data.getLocalizedVersion("flexibleElementIndicatorsList").toUpperCase(),
        data.getNumbOfCols());

    // empty row
    utils.putEmptyRow(sheet, ++rowIndex, ExportConstants.EMPTY_ROW_HEIGHT);

    // column headers
    row = sheet.createRow(++rowIndex);
    row.setHeightInPoints(ExportConstants.TITLE_ROW_HEIGHT);
    cellIndex = 0;
    utils.putHeader(row, ++cellIndex, data.getLocalizedVersion("name"));
    utils.putHeader(row, ++cellIndex, data.getLocalizedVersion("code"));
    utils.putHeader(row, ++cellIndex, data.getLocalizedVersion("targetValue"));
    utils.putHeader(row, ++cellIndex, data.getLocalizedVersion("value"));

    // empty row
    utils.putEmptyRow(sheet, ++rowIndex, 8.65f);

    // freeze pane
    sheet.createFreezePane(0, rowIndex);

    for (final IndicatorGroup group : data.getIndicators().getGroups()) {
      row = sheet.createRow(++rowIndex);
      row.setHeightInPoints(ExportConstants.TITLE_ROW_HEIGHT);
      putGroupCell(sheet, rowIndex, group.getName());
      for (final IndicatorDTO indicator : group.getIndicators()) {
        // indicator's detail sheet
        createDetailSheet(indicator);
        row = sheet.createRow(++rowIndex);
        row.setHeightInPoints(ExportConstants.TITLE_ROW_HEIGHT);
        // ind name
        utils.createLinkCell(
            row.createCell(1),
            indicator.getName(),
            ExportConstants.INDICATOR_SHEET_PREFIX + indicator.getName(),
            true);
        // code
        utils.putBorderedBasicCell(sheet, rowIndex, 2, indicator.getCode());
        // target
        putRightAlignedCell(sheet, rowIndex, 3, indicator.getObjective());
        // current value
        putRightAlignedCell(sheet, rowIndex, 4, data.getFormattedValue(indicator));
      }
    }

    sheet.setColumnWidth(0, 256 * 2);
    sheet.setColumnWidth(1, 256 * 45);
    sheet.setColumnWidth(2, 256 * 27);
    sheet.setColumnWidth(3, 256 * 27);
    sheet.setColumnWidth(4, 256 * 27);
  }
コード例 #5
0
  private void createDetailSheet(final IndicatorDTO indicator) {
    final boolean isQualitative = indicator.getAggregation() == IndicatorDTO.AGGREGATE_MULTINOMIAL;
    final HSSFSheet sheetEx =
        wb.createSheet(
            utils.normalizeAsLink(ExportConstants.INDICATOR_SHEET_PREFIX + indicator.getName()));
    int rowIndex = -1;

    final List<PivotTableData.Axis> leaves =
        data.getEntryMap().get(indicator.getId()).getRootColumn().getLeaves();
    int numbOfLeaves = leaves.size();
    int numbOfCols = 4;

    // back to list link
    row = sheetEx.createRow(++rowIndex);
    utils.createLinkCell(
        row.createCell(1),
        data.getLocalizedVersion("goToIndicatorsList"),
        data.getLocalizedVersion("flexibleElementIndicatorsList"),
        false);
    sheetEx.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 1, numbOfCols));

    // title
    utils.putMainTitle(sheetEx, ++rowIndex, indicator.getName(), numbOfCols);

    // empty row
    utils.putEmptyRow(sheetEx, ++rowIndex, ExportConstants.EMPTY_ROW_HEIGHT);
    sheetEx.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 1, numbOfCols));

    // put details
    putBasicInfo(
        sheetEx, ++rowIndex, data.getLocalizedVersion("code"), indicator.getCode(), numbOfCols);

    putBasicInfo(
        sheetEx,
        ++rowIndex,
        data.getLocalizedVersion("group"),
        data.getGroupMap().get(indicator.getGroupId()),
        numbOfCols);

    // type
    String type = null;
    ;
    if (isQualitative) {
      // qualitative
      type = data.getLocalizedVersion("qualitative");
    } else {
      // quantitative
      type = data.getLocalizedVersion("quantitative");
    }
    putBasicInfo(sheetEx, ++rowIndex, data.getLocalizedVersion("type"), type, numbOfCols);

    // conditional
    if (isQualitative) {
      // qualitative

      // possible values
      row = sheetEx.createRow(++rowIndex);

      // key
      cell = utils.putHeader(row, 1, data.getLocalizedVersion("possibleValues"));
      cell.getCellStyle().setAlignment(CellStyle.ALIGN_RIGHT);

      // value
      final MultiItemText itemText = data.formatPossibleValues(indicator.getLabels());
      utils.putBorderedBasicCell(sheetEx, rowIndex, 2, itemText.text);
      row.setHeightInPoints(itemText.lineCount * defHeight);
      region = new CellRangeAddress(rowIndex, rowIndex, 2, numbOfCols);
      sheetEx.addMergedRegion(utils.getBorderedRegion(region, sheetEx, wb));
    } else {
      // quantitative

      // aggregation method
      String aggrMethod = null;
      if (indicator.getAggregation() == IndicatorDTO.AGGREGATE_AVG)
        aggrMethod = data.getLocalizedVersion("average");
      else aggrMethod = data.getLocalizedVersion("sum");
      putBasicInfo(
          sheetEx,
          ++rowIndex,
          data.getLocalizedVersion("aggregationMethod"),
          aggrMethod,
          numbOfCols);
      // units
      putBasicInfo(
          sheetEx, ++rowIndex, data.getLocalizedVersion("units"), indicator.getUnits(), numbOfCols);

      // target value
      putBasicInfo(
          sheetEx,
          ++rowIndex,
          data.getLocalizedVersion("targetValue"),
          indicator.getObjective(),
          numbOfCols);
    }

    // source of ver
    putBasicInfo(
        sheetEx,
        ++rowIndex,
        data.getLocalizedVersion("sourceOfVerification"),
        indicator.getSourceOfVerification(),
        numbOfCols);
    row.setHeightInPoints(
        utils.calculateLineCount(indicator.getSourceOfVerification(), 3 * 18) * defHeight);

    // comment
    putBasicInfo(
        sheetEx,
        ++rowIndex,
        data.getLocalizedVersion("indicatorComments"),
        indicator.getDescription(),
        numbOfCols);
    row.setHeightInPoints(utils.calculateLineCount(indicator.getDescription(), 3 * 18) * defHeight);

    // value
    putBasicInfo(
        sheetEx,
        ++rowIndex,
        data.getLocalizedVersion("value"),
        data.getFormattedValue(indicator),
        numbOfCols);
    // empty row
    utils.putEmptyRow(sheetEx, ++rowIndex, ExportConstants.EMPTY_ROW_HEIGHT);
    sheetEx.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 1, numbOfCols));

    // data entry
    // header
    row = sheetEx.createRow(++rowIndex);
    row.setHeightInPoints(defHeight);
    int cellIndex = 0;
    utils.putHeader(row, ++cellIndex, data.getLocalizedVersion("sideAndMonth"));
    Map<String, Integer> columnIndexMap = new HashMap<String, Integer>();
    for (PivotTableData.Axis axis : leaves) {
      utils.putHeader(row, ++cellIndex, axis.getLabel());
      columnIndexMap.put(axis.getLabel(), cellIndex);
    }

    // rows
    for (PivotTableData.Axis axis :
        data.getEntryMap().get(indicator.getId()).getRootRow().getChildren()) {
      row = sheetEx.createRow(++rowIndex);
      row.setHeightInPoints(defHeight);
      utils.putHeader(row, 1, axis.getLabel());

      // populate empty cells
      for (int i = 0; i < numbOfLeaves; i++) {
        cell = utils.putBorderedBasicCell(sheetEx, rowIndex, i + 2, "");
      }

      // insert values
      for (Map.Entry<PivotTableData.Axis, PivotTableData.Cell> entry : axis.getCells().entrySet()) {
        cellIndex = columnIndexMap.get(entry.getKey().getLabel());
        Object value = null;
        boolean rightAligned = false;
        if (isQualitative) {
          value = data.getLabelByIndex(indicator.getLabels(), entry.getValue().getValue());
        } else {
          value = new Long(Math.round(entry.getValue().getValue()));
          rightAligned = true;
        }
        putValueCell(sheetEx, rowIndex, cellIndex, value, rightAligned);
      }
    }
    // col width
    sheetEx.setColumnWidth(0, 256 * 2);
    sheetEx.autoSizeColumn(1);
    for (int i = 2; i < 2 + numbOfLeaves; i++) {
      sheetEx.setColumnWidth(i, 256 * 16);
    }
  }