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); } }
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); } }