// ------------------------------------------------------------------------- // Action Implementation // ------------------------------------------------------------------------- public String execute() throws Exception { statementManager.initialise(); // Initialization raFolderName = reportService.getRAFolderName(); String deCodesXMLFileName = ""; simpleDateFormat = new SimpleDateFormat("MMM-yyyy"); monthFormat = new SimpleDateFormat("MMMM"); yearFormat = new SimpleDateFormat("yyyy"); simpleMonthFormat = new SimpleDateFormat("MMM"); String parentUnit = ""; Report_in selReportObj = reportService.getReport(Integer.parseInt(reportList)); deCodesXMLFileName = selReportObj.getXmlTemplateName(); reportModelTB = selReportObj.getModel(); reportFileNameTB = selReportObj.getExcelTemplateName(); String inputTemplatePath = System.getenv("DHIS2_HOME") + File.separator + raFolderName + File.separator + "template" + File.separator + reportFileNameTB; // String outputReportFolderPath = System.getenv( "DHIS2_HOME" ) + File.separator + raFolderName // + File.separator + "output" + File.separator + UUID.randomUUID().toString(); String outputReportFolderPath = System.getenv("DHIS2_HOME") + File.separator + Configuration_IN.DEFAULT_TEMPFOLDER + File.separator + UUID.randomUUID().toString(); File newdir = new File(outputReportFolderPath); if (!newdir.exists()) { newdir.mkdirs(); } if (reportModelTB.equalsIgnoreCase("STATIC") || reportModelTB.equalsIgnoreCase("STATIC-DATAELEMENTS") || reportModelTB.equalsIgnoreCase("STATIC-FINANCIAL")) { orgUnitList = new ArrayList<OrganisationUnit>( organisationUnitService.getOrganisationUnitWithChildren(ouIDTB)); OrganisationUnitGroup orgUnitGroup = selReportObj.getOrgunitGroup(); orgUnitList.retainAll(orgUnitGroup.getMembers()); } else { return INPUT; } // System.out.println( "---Size of Org Unit List ----: " + orgUnitList.size() + ",Report Group // name is :---" + selReportObj.getOrgunitGroup().getName() + ", Size of Group member is ----:" // + selReportObj.getOrgunitGroup().getMembers().size() ); System.out.println(" ---- Size of OrgUnit List is ---- " + orgUnitList.size()); OrganisationUnit selOrgUnit = organisationUnitService.getOrganisationUnit(ouIDTB); System.out.println( selOrgUnit.getName() + " : " + selReportObj.getName() + " : Report Generation Start Time is : " + new Date()); selectedPeriod = periodService.getPeriod(availablePeriods); sDate = format.parseDate(String.valueOf(selectedPeriod.getStartDate())); eDate = format.parseDate(String.valueOf(selectedPeriod.getEndDate())); Workbook templateWorkbook = Workbook.getWorkbook(new File(inputTemplatePath)); // collect periodId by commaSepareted List<Period> tempPeriodList = new ArrayList<Period>(periodService.getIntersectingPeriods(sDate, eDate)); Collection<Integer> tempPeriodIds = new ArrayList<Integer>(getIdentifiers(Period.class, tempPeriodList)); String periodIdsByComma = getCommaDelimitedString(tempPeriodIds); // Getting DataValues List<Report_inDesign> reportDesignList = reportService.getReportDesign(deCodesXMLFileName); // collect dataElementIDs by commaSepareted String dataElmentIdsByComma = reportService.getDataelementIds(reportDesignList); int orgUnitCount = 0; Iterator<OrganisationUnit> it = orgUnitList.iterator(); while (it.hasNext()) { OrganisationUnit currentOrgUnit = (OrganisationUnit) it.next(); String outPutFileName = reportFileNameTB.replace(".xls", ""); outPutFileName += "_" + currentOrgUnit.getShortName(); outPutFileName += "_" + simpleDateFormat.format(selectedPeriod.getStartDate()) + ".xls"; String outputReportPath = outputReportFolderPath + File.separator + outPutFileName; WritableWorkbook outputReportWorkbook = Workbook.createWorkbook(new File(outputReportPath), templateWorkbook); Map<String, String> aggDeMap = new HashMap<String, String>(); if (aggData.equalsIgnoreCase(USEEXISTINGAGGDATA)) { aggDeMap.putAll( reportService.getResultDataValueFromAggregateTable( currentOrgUnit.getId(), dataElmentIdsByComma, periodIdsByComma)); } else if (aggData.equalsIgnoreCase(GENERATEAGGDATA)) { List<OrganisationUnit> childOrgUnitTree = new ArrayList<OrganisationUnit>( organisationUnitService.getOrganisationUnitWithChildren(currentOrgUnit.getId())); List<Integer> childOrgUnitTreeIds = new ArrayList<Integer>(getIdentifiers(OrganisationUnit.class, childOrgUnitTree)); String childOrgUnitsByComma = getCommaDelimitedString(childOrgUnitTreeIds); aggDeMap.putAll( reportService.getAggDataFromDataValueTable( childOrgUnitsByComma, dataElmentIdsByComma, periodIdsByComma)); } else if (aggData.equalsIgnoreCase(USECAPTUREDDATA)) { aggDeMap.putAll( reportService.getAggDataFromDataValueTable( "" + currentOrgUnit.getId(), dataElmentIdsByComma, periodIdsByComma)); } int count1 = 0; Iterator<Report_inDesign> reportDesignIterator = reportDesignList.iterator(); while (reportDesignIterator.hasNext()) { Report_inDesign report_inDesign = (Report_inDesign) reportDesignIterator.next(); String deType = report_inDesign.getPtype(); String sType = report_inDesign.getStype(); String deCodeString = report_inDesign.getExpression(); String tempStr = ""; Calendar tempStartDate = Calendar.getInstance(); Calendar tempEndDate = Calendar.getInstance(); List<Calendar> calendarList = new ArrayList<Calendar>(reportService.getStartingEndingPeriods(deType, selectedPeriod)); if (calendarList == null || calendarList.isEmpty()) { tempStartDate.setTime(selectedPeriod.getStartDate()); tempEndDate.setTime(selectedPeriod.getEndDate()); return SUCCESS; } else { tempStartDate = calendarList.get(0); tempEndDate = calendarList.get(1); } if (deCodeString.equalsIgnoreCase("FACILITY")) { tempStr = currentOrgUnit.getName(); } else if (deCodeString.equalsIgnoreCase("FACILITY-NOREPEAT")) { tempStr = parentUnit; } else if (deCodeString.equalsIgnoreCase("FACILITYP")) { tempStr = currentOrgUnit.getParent().getName(); } else if (deCodeString.equalsIgnoreCase("FACILITYPP")) { tempStr = currentOrgUnit.getParent().getParent().getName(); } else if (deCodeString.equalsIgnoreCase("FACILITYPPP")) { tempStr = currentOrgUnit.getParent().getParent().getParent().getName(); } else if (deCodeString.equalsIgnoreCase("FACILITYPPPP")) { tempStr = currentOrgUnit.getParent().getParent().getParent().getParent().getName(); } else if (deCodeString.equalsIgnoreCase("PERIOD") || deCodeString.equalsIgnoreCase("PERIOD-NOREPEAT")) { tempStr = simpleDateFormat.format(sDate); } else if (deCodeString.equalsIgnoreCase("PERIOD-MONTH")) { tempStr = monthFormat.format(sDate); } else if (deCodeString.equalsIgnoreCase("PERIOD-YEAR")) { tempStr = yearFormat.format(sDate); } else if (deCodeString.equalsIgnoreCase("MONTH-START-SHORT")) { tempStr = simpleMonthFormat.format(sDate); } else if (deCodeString.equalsIgnoreCase("MONTH-END-SHORT")) { tempStr = simpleMonthFormat.format(eDate); } else if (deCodeString.equalsIgnoreCase("MONTH-START")) { tempStr = monthFormat.format(sDate); } else if (deCodeString.equalsIgnoreCase("MONTH-END")) { tempStr = monthFormat.format(eDate); } else if (deCodeString.equalsIgnoreCase("SLNO")) { tempStr = "" + (orgUnitCount + 1); } else if (deCodeString.equalsIgnoreCase("NA")) { tempStr = " "; } else { if (sType.equalsIgnoreCase("dataelement")) { if (aggData.equalsIgnoreCase(USECAPTUREDDATA)) { tempStr = getAggVal(deCodeString, aggDeMap); // tempStr = reportService.getIndividualResultDataValue(deCodeString, // tempStartDate.getTime(), tempEndDate.getTime(), currentOrgUnit, reportModelTB ); } else if (aggData.equalsIgnoreCase(GENERATEAGGDATA)) { tempStr = getAggVal(deCodeString, aggDeMap); // tempStr = reportService.getResultDataValue( deCodeString, tempStartDate.getTime(), // tempEndDate.getTime(), currentOrgUnit, reportModelTB ); } else if (aggData.equalsIgnoreCase(USEEXISTINGAGGDATA)) { tempStr = getAggVal(deCodeString, aggDeMap); /* List<Period> periodList = new ArrayList<Period>( periodService.getPeriodsBetweenDates( tempStartDate.getTime(), tempEndDate.getTime() ) ); Collection<Integer> periodIds = new ArrayList<Integer>( getIdentifiers(Period.class, periodList ) ); tempStr = reportService.getResultDataValueFromAggregateTable( deCodeString, periodIds, currentOrgUnit, reportModelTB ); */ } } else if (sType.equalsIgnoreCase("dataelement-boolean")) { if (aggData.equalsIgnoreCase(USECAPTUREDDATA)) { tempStr = reportService.getBooleanDataValue( deCodeString, tempStartDate.getTime(), tempEndDate.getTime(), currentOrgUnit, reportModelTB); } else if (aggData.equalsIgnoreCase(GENERATEAGGDATA)) { tempStr = reportService.getBooleanDataValue( deCodeString, tempStartDate.getTime(), tempEndDate.getTime(), currentOrgUnit, reportModelTB); } else if (aggData.equalsIgnoreCase(USEEXISTINGAGGDATA)) { tempStr = reportService.getBooleanDataValue( deCodeString, tempStartDate.getTime(), tempEndDate.getTime(), currentOrgUnit, reportModelTB); } } else { if (aggData.equalsIgnoreCase(USECAPTUREDDATA)) { tempStr = reportService.getIndividualResultIndicatorValue( deCodeString, tempStartDate.getTime(), tempEndDate.getTime(), currentOrgUnit); } else if (aggData.equalsIgnoreCase(GENERATEAGGDATA)) { tempStr = reportService.getResultIndicatorValue( deCodeString, tempStartDate.getTime(), tempEndDate.getTime(), currentOrgUnit); } else if (aggData.equalsIgnoreCase(USEEXISTINGAGGDATA)) { // List<Period> periodList = new ArrayList<Period>( // periodService.getPeriodsBetweenDates( tempStartDate.getTime(), // tempEndDate.getTime() ) ); // Collection<Integer> periodIds = new ArrayList<Integer>( // getIdentifiers(Period.class, periodList ) ); tempStr = reportService.getResultIndicatorValue( deCodeString, tempStartDate.getTime(), tempEndDate.getTime(), currentOrgUnit); } } } int tempRowNo = report_inDesign.getRowno(); int tempColNo = report_inDesign.getColno(); int sheetNo = report_inDesign.getSheetno(); WritableSheet sheet0 = outputReportWorkbook.getSheet(sheetNo); if (tempStr == null || tempStr.equals(" ")) { tempColNo += orgUnitCount; WritableCellFormat wCellformat = new WritableCellFormat(); wCellformat.setBorder(Border.ALL, BorderLineStyle.THIN); wCellformat.setWrap(true); wCellformat.setAlignment(Alignment.CENTRE); sheet0.addCell(new Blank(tempColNo, tempRowNo, wCellformat)); } else { if (reportModelTB.equalsIgnoreCase("DYNAMIC-ORGUNIT")) { if (deCodeString.equalsIgnoreCase("FACILITYP") || deCodeString.equalsIgnoreCase("FACILITYPP") || deCodeString.equalsIgnoreCase("FACILITYPPP") || deCodeString.equalsIgnoreCase("FACILITYPPPP")) { } else if (deCodeString.equalsIgnoreCase("PERIOD") || deCodeString.equalsIgnoreCase("PERIOD-NOREPEAT") || deCodeString.equalsIgnoreCase("PERIOD-WEEK") || deCodeString.equalsIgnoreCase("PERIOD-MONTH") || deCodeString.equalsIgnoreCase("PERIOD-QUARTER") || deCodeString.equalsIgnoreCase("PERIOD-YEAR") || deCodeString.equalsIgnoreCase("MONTH-START") || deCodeString.equalsIgnoreCase("MONTH-END") || deCodeString.equalsIgnoreCase("MONTH-START-SHORT") || deCodeString.equalsIgnoreCase("MONTH-END-SHORT") || deCodeString.equalsIgnoreCase("SIMPLE-QUARTER") || deCodeString.equalsIgnoreCase("QUARTER-MONTHS-SHORT") || deCodeString.equalsIgnoreCase("QUARTER-MONTHS") || deCodeString.equalsIgnoreCase("QUARTER-START-SHORT") || deCodeString.equalsIgnoreCase("QUARTER-END-SHORT") || deCodeString.equalsIgnoreCase("QUARTER-START") || deCodeString.equalsIgnoreCase("QUARTER-END") || deCodeString.equalsIgnoreCase("SIMPLE-YEAR") || deCodeString.equalsIgnoreCase("YEAR-END") || deCodeString.equalsIgnoreCase("YEAR-FROMTO")) { } else { tempColNo += orgUnitCount; } } else if (reportModelTB.equalsIgnoreCase("dynamicwithrootfacility")) { if (deCodeString.equalsIgnoreCase("FACILITYP") || deCodeString.equalsIgnoreCase("FACILITY-NOREPEAT") || deCodeString.equalsIgnoreCase("FACILITYPP") || deCodeString.equalsIgnoreCase("FACILITYPPP") || deCodeString.equalsIgnoreCase("FACILITYPPPP")) { } else if (deCodeString.equalsIgnoreCase("PERIOD") || deCodeString.equalsIgnoreCase("PERIOD-NOREPEAT") || deCodeString.equalsIgnoreCase("PERIOD-WEEK") || deCodeString.equalsIgnoreCase("PERIOD-MONTH") || deCodeString.equalsIgnoreCase("PERIOD-QUARTER") || deCodeString.equalsIgnoreCase("PERIOD-YEAR") || deCodeString.equalsIgnoreCase("MONTH-START") || deCodeString.equalsIgnoreCase("MONTH-END") || deCodeString.equalsIgnoreCase("MONTH-START-SHORT") || deCodeString.equalsIgnoreCase("MONTH-END-SHORT") || deCodeString.equalsIgnoreCase("SIMPLE-QUARTER") || deCodeString.equalsIgnoreCase("QUARTER-MONTHS-SHORT") || deCodeString.equalsIgnoreCase("QUARTER-MONTHS") || deCodeString.equalsIgnoreCase("QUARTER-START-SHORT") || deCodeString.equalsIgnoreCase("QUARTER-END-SHORT") || deCodeString.equalsIgnoreCase("QUARTER-START") || deCodeString.equalsIgnoreCase("QUARTER-END") || deCodeString.equalsIgnoreCase("SIMPLE-YEAR") || deCodeString.equalsIgnoreCase("YEAR-END") || deCodeString.equalsIgnoreCase("YEAR-FROMTO")) { } else { tempRowNo += orgUnitCount; } } WritableCell cell = sheet0.getWritableCell(tempColNo, tempRowNo); CellFormat cellFormat = cell.getCellFormat(); WritableCellFormat wCellformat = new WritableCellFormat(); wCellformat.setBorder(Border.ALL, BorderLineStyle.THIN); wCellformat.setWrap(true); wCellformat.setAlignment(Alignment.CENTRE); if (cell.getType() == CellType.LABEL) { Label l = (Label) cell; l.setString(tempStr); l.setCellFormat(cellFormat); } else { try { sheet0.addCell( new Number(tempColNo, tempRowNo, Double.parseDouble(tempStr), wCellformat)); } catch (Exception e) { sheet0.addCell(new Label(tempColNo, tempRowNo, tempStr, wCellformat)); } } } count1++; } // inner while loop end outputReportWorkbook.write(); outputReportWorkbook.close(); orgUnitCount++; } // outer while loop end statementManager.destroy(); if (zipDirectory(outputReportFolderPath, outputReportFolderPath + ".zip")) { System.out.println( selOrgUnit.getName() + " : " + selReportObj.getName() + " Report Generation End Time is : " + new Date()); fileName = reportFileNameTB.replace(".xls", ""); fileName += "_" + selOrgUnit.getShortName(); fileName += "_" + simpleDateFormat.format(selectedPeriod.getStartDate()) + ".zip"; File outputReportFile = new File(outputReportFolderPath + ".zip"); inputStream = new BufferedInputStream(new FileInputStream(outputReportFile)); return SUCCESS; } else { return INPUT; } }
// ------------------------------------------------------------------------- // Action implementation // ------------------------------------------------------------------------- public String execute() throws Exception { statementManager.initialise(); // Initialization raFolderName = reportService.getRAFolderName(); String colArray[] = { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", "AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH", "AI", "AJ", "AK", "AL", "AM", "AN", "AO", "AP", "AQ", "AR", "AS", "AT", "AU", "AV", "AW", "AX", "AY", "AZ", "BA", "BB", "BC", "BD", "BE", "BF", "BG", "BH", "BI", "BJ", "BK", "BL", "BM", "BN", "BO", "BP", "BQ", "BR", "BS", "BT", "BU", "BV", "BW", "BX", "BY", "BZ" }; Report_in selReportObj = reportService.getReport(Integer.parseInt(reportList)); // OrgUnit Info OrganisationUnit currentOrgUnit = organisationUnitService.getOrganisationUnit(ouIDTB); System.out.println( currentOrgUnit.getName() + " : " + selReportObj.getName() + " : Report Generation Start Time is : " + new Date()); List<OrganisationUnit> childOrgUnitTree = new ArrayList<OrganisationUnit>( organisationUnitService.getOrganisationUnitWithChildren(ouIDTB)); List<Integer> childOrgUnitTreeIds = new ArrayList<Integer>(getIdentifiers(OrganisationUnit.class, childOrgUnitTree)); String childOrgUnitsByComma = getCommaDelimitedString(childOrgUnitTreeIds); // Report Info String deCodesXMLFileName = selReportObj.getXmlTemplateName(); String reportModelTB = selReportObj.getModel(); String reportFileNameTB = selReportObj.getExcelTemplateName(); String inputTemplatePath = System.getenv("DHIS2_HOME") + File.separator + raFolderName + File.separator + "template" + File.separator + reportFileNameTB; // String outputReportPath = System.getenv( "DHIS2_HOME" ) + File.separator + raFolderName + // File.separator + "output" + File.separator + UUID.randomUUID().toString() + ".xls"; String outputReportPath = System.getenv("DHIS2_HOME") + File.separator + Configuration_IN.DEFAULT_TEMPFOLDER; File newdir = new File(outputReportPath); if (!newdir.exists()) { newdir.mkdirs(); } outputReportPath += File.separator + UUID.randomUUID().toString() + ".xls"; Workbook templateWorkbook = Workbook.getWorkbook(new File(inputTemplatePath)); WritableWorkbook outputReportWorkbook = Workbook.createWorkbook(new File(outputReportPath), templateWorkbook); WritableCellFormat wCellformat = new WritableCellFormat(); wCellformat.setBorder(Border.ALL, BorderLineStyle.THIN); wCellformat.setAlignment(Alignment.CENTRE); wCellformat.setVerticalAlignment(VerticalAlignment.CENTRE); wCellformat.setWrap(true); // Period Info selectedPeriod = periodService.getPeriod(availablePeriods); selectedEndPeriod = periodService.getPeriod(availablePeriodsto); sDate = format.parseDate(String.valueOf(selectedPeriod.getStartDate())); eDate = format.parseDate(String.valueOf(selectedEndPeriod.getEndDate())); PeriodType periodType = periodService.getPeriodTypeByName(periodTypeId); List<Period> periodList = new ArrayList<Period>(periodService.getPeriodsBetweenDates(periodType, sDate, eDate)); // List<Period> periodList = new ArrayList<Period>( periodService.getIntersectingPeriods( sDate, // eDate ) ); Collections.sort(periodList, new PeriodStartDateComparator()); if (periodTypeId.equalsIgnoreCase("monthly")) { simpleDateFormat = new SimpleDateFormat("MMM-yyyy"); } else if (periodTypeId.equalsIgnoreCase("yearly")) { simpleDateFormat = new SimpleDateFormat("yyyy"); } else { simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd"); } // To get Aggregation Data List<Report_inDesign> reportDesignList = reportService.getReportDesign(deCodesXMLFileName); // String dataElmentIdsByComma = reportService.getDataelementIds( reportDesignList ); String dataElmentIdsByComma = reportService.getDataelementIdsByStype(reportDesignList, Report_inDesign.ST_DATAELEMENT); String nonNumberDataElementIdsByComma = reportService.getDataelementIdsByStype( reportDesignList, Report_inDesign.ST_NON_NUMBER_DATAELEMENT); // Collection<Integer> periodIds1 = new ArrayList<Integer>( getIdentifiers(Period.class, // periodList ) ); String periodsByComma = ""; // getCommaDelimitedString( periodIds1 ); int colCount = 0; for (Period period : periodList) { if (periodTypeId.equalsIgnoreCase("daily")) { periodsByComma = "" + period.getId(); } else { Collection<Integer> periodIds = new ArrayList<Integer>( getIdentifiers( Period.class, periodService.getIntersectingPeriods( period.getStartDate(), period.getEndDate()))); periodsByComma = getCommaDelimitedString(periodIds); } Map<String, String> aggDeMap = new HashMap<String, String>(); if (aggData.equalsIgnoreCase(USEEXISTINGAGGDATA)) { aggDeMap.putAll( reportService.getResultDataValueFromAggregateTable( currentOrgUnit.getId(), dataElmentIdsByComma, periodsByComma)); } else if (aggData.equalsIgnoreCase(GENERATEAGGDATA)) { aggDeMap.putAll( reportService.getAggDataFromDataValueTable( childOrgUnitsByComma, dataElmentIdsByComma, periodsByComma)); aggDeMap.putAll( reportService.getAggNonNumberDataFromDataValueTable( childOrgUnitsByComma, nonNumberDataElementIdsByComma, periodsByComma)); System.out.println( childOrgUnitsByComma + " \n " + dataElmentIdsByComma + " \n " + periodsByComma); } else if (aggData.equalsIgnoreCase(USECAPTUREDDATA)) { aggDeMap.putAll( reportService.getAggDataFromDataValueTable( "" + currentOrgUnit.getId(), dataElmentIdsByComma, periodsByComma)); aggDeMap.putAll( reportService.getAggNonNumberDataFromDataValueTable( "" + currentOrgUnit.getId(), nonNumberDataElementIdsByComma, periodsByComma)); } System.out.println("aggDeMap size : " + aggDeMap.size()); Iterator<Report_inDesign> reportDesignIterator = reportDesignList.iterator(); while (reportDesignIterator.hasNext()) { Report_inDesign reportDesign = reportDesignIterator.next(); String deCodeString = reportDesign.getExpression(); String sType = reportDesign.getStype(); String tempStr = ""; tempRowNo = reportDesign.getRowno(); tempColNo = reportDesign.getColno(); sheetNo = reportDesign.getSheetno(); if (deCodeString.equalsIgnoreCase("FACILITY")) { tempStr = currentOrgUnit.getName(); } else if (deCodeString.equalsIgnoreCase("PERIOD-RANGE")) { tempStr = simpleDateFormat.format(selectedPeriod.getStartDate()) + " To " + simpleDateFormat.format(selectedEndPeriod.getEndDate()); } else if (deCodeString.equalsIgnoreCase("PROGRESSIVE-PERIOD")) { tempStr = simpleDateFormat.format(period.getStartDate()); } else if (deCodeString.equalsIgnoreCase("NA")) { tempStr = " "; } else { if (sType.equalsIgnoreCase("dataelement")) { if (aggData.equalsIgnoreCase(USECAPTUREDDATA)) { // tempStr = reportService.getIndividualResultDataValue( deCodeString, // period.getStartDate(), period.getEndDate(), currentOrgUnit, reportModelTB ); tempStr = getAggVal(deCodeString, aggDeMap); } else if (aggData.equalsIgnoreCase(GENERATEAGGDATA)) { // tempStr = reportService.getResultDataValue( deCodeString, period.getStartDate(), // period.getEndDate(), currentOrgUnit, reportModelTB ); tempStr = getAggVal(deCodeString, aggDeMap); } else if (aggData.equalsIgnoreCase(USEEXISTINGAGGDATA)) { tempStr = getAggVal(deCodeString, aggDeMap); } } else if (sType.equalsIgnoreCase(Report_inDesign.ST_DATAELEMENT_NO_REPEAT)) { deCodeString = deCodeString.replaceAll(":", "\\."); deCodeString = deCodeString.replaceAll("[", ""); deCodeString = deCodeString.replaceAll("]", ""); System.out.println("deCodeString : " + deCodeString); tempStr = aggDeMap.get(deCodeString); } } if (tempStr == null || tempStr.equals(" ")) { tempColNo += colCount; WritableSheet sheet0 = outputReportWorkbook.getSheet(sheetNo); sheet0.addCell(new Blank(tempColNo, tempRowNo, wCellformat)); } else { if (reportModelTB.equalsIgnoreCase("PROGRESSIVE-PERIOD")) { if (deCodeString.equalsIgnoreCase("FACILITY") || deCodeString.equalsIgnoreCase("PERIOD-RANGE")) { } else { tempColNo += colCount; } WritableSheet sheet0 = outputReportWorkbook.getSheet(sheetNo); try { try { sheet0.addCell( new Number(tempColNo, tempRowNo, Double.parseDouble(tempStr), wCellformat)); } catch (Exception e) { sheet0.addCell(new Label(tempColNo, tempRowNo, tempStr, wCellformat)); } } catch (Exception e) { System.out.println("Cannot write to Excel"); } } } } // inner while loop end colCount++; } // outer while loop end // --------------------------------------------------------------------- // Writing Total Values // --------------------------------------------------------------------- Iterator<Report_inDesign> reportDesignIterator = reportDesignList.iterator(); while (reportDesignIterator.hasNext()) { Report_inDesign reportDesign = reportDesignIterator.next(); String deCodeString = reportDesign.getExpression(); if (deCodeString.equalsIgnoreCase("FACILITY") || deCodeString.equalsIgnoreCase("PERIOD-RANGE")) { continue; } tempRowNo = reportDesign.getRowno(); tempColNo = reportDesign.getColno(); sheetNo = reportDesign.getSheetno(); String colStart = "" + colArray[tempColNo]; String colEnd = "" + colArray[tempColNo + colCount - 1]; String tempFormula = "SUM(" + colStart + (tempRowNo + 1) + ":" + colEnd + (tempRowNo + 1) + ")"; WritableSheet totalSheet = outputReportWorkbook.getSheet(sheetNo); WritableFont arialBold = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD); WritableCellFormat totalCellformat = new WritableCellFormat(arialBold); totalCellformat.setBorder(Border.ALL, BorderLineStyle.THIN); totalCellformat.setAlignment(Alignment.CENTRE); totalCellformat.setVerticalAlignment(VerticalAlignment.CENTRE); totalCellformat.setWrap(true); if (deCodeString.equalsIgnoreCase("PROGRESSIVE-PERIOD")) { totalSheet.addCell(new Label(tempColNo + colCount, tempRowNo, "Total", totalCellformat)); } else if (deCodeString.equalsIgnoreCase("NA")) { totalSheet.addCell(new Label(tempColNo + colCount, tempRowNo, " ", totalCellformat)); } else { totalSheet.addCell( new Formula(tempColNo + colCount, tempRowNo, tempFormula, totalCellformat)); } } outputReportWorkbook.write(); outputReportWorkbook.close(); fileName = reportFileNameTB.replace(".xls", ""); fileName += "_" + currentOrgUnit.getShortName(); fileName += "_" + simpleDateFormat.format(selectedPeriod.getStartDate()) + ".xls"; File outputReportFile = new File(outputReportPath); inputStream = new BufferedInputStream(new FileInputStream(outputReportFile)); System.out.println( currentOrgUnit.getName() + " : " + selReportObj.getName() + " : Report Generation End Time is : " + new Date()); outputReportFile.deleteOnExit(); statementManager.destroy(); return SUCCESS; }