=== modified file 'dhis-2/dhis-web/dhis-web-excel-reporting/src/main/java/org/hisp/dhis/reportexcel/export/action/GenerateReportCategoryAction.java' --- dhis-2/dhis-web/dhis-web-excel-reporting/src/main/java/org/hisp/dhis/reportexcel/export/action/GenerateReportCategoryAction.java 2009-12-21 11:09:00 +0000 +++ dhis-2/dhis-web/dhis-web-excel-reporting/src/main/java/org/hisp/dhis/reportexcel/export/action/GenerateReportCategoryAction.java 2010-01-05 07:36:05 +0000 @@ -84,6 +84,8 @@ { for ( ReportExcelItem reportItem : reportExcelItems ) { + int iRow = 0; + int iCol = 0; int rowBegin = reportItem.getRow(); for ( DataElementGroupOrder dataElementGroup : reportExcel.getDataElementOrders() ) @@ -94,12 +96,12 @@ if ( reportItem.getItemType().equalsIgnoreCase( ReportExcelItem.TYPE.DATAELEMENT_NAME ) ) { ExcelUtils.writeValueByPOI( rowBegin, reportItem.getColumn(), String.valueOf( dataElementGroup - .getName() ), ExcelUtils.TEXT, sheet, this.csText12BoldCenter ); + .getName() ), ExcelUtils.TEXT, sheet, this.csText12BoldCenter ); } else if ( reportItem.getItemType().equalsIgnoreCase( ReportExcelItem.TYPE.DATAELEMENT_CODE ) ) { ExcelUtils.writeValueByPOI( rowBegin, reportItem.getColumn(), String.valueOf( dataElementGroup - .getCode() ), ExcelUtils.TEXT, sheet, this.csText12BoldCenter ); + .getCode() ), ExcelUtils.TEXT, sheet, this.csText12BoldCenter ); } rowBegin++; @@ -125,8 +127,8 @@ } else if ( reportItem.getItemType().equalsIgnoreCase( ReportExcelItem.TYPE.FORMULA_EXCEL ) ) { - ExcelUtils.writeFormulaByPOI( rowBegin, reportItem.getColumn(), reportItem.getExpression(), - sheet, this.csFormula ); + ExcelUtils.writeFormulaByPOI( rowBegin, reportItem.getColumn(), ExcelUtils + .checkingExcelFormula( reportItem.getExpression(), iRow, iCol ), sheet, this.csFormula ); } else { @@ -143,26 +145,25 @@ newReportItem.setExpression( expression ); double value = this.getDataValue( newReportItem, organisationUnit ); - + ExcelUtils.writeValueByPOI( rowBegin, reportItem.getColumn(), String.valueOf( value ), ExcelUtils.NUMBER, sheet, this.csNumber ); - + } rowBegin++; serial++; + iRow++; } if ( reportItem.getItemType().equalsIgnoreCase( ReportExcelItem.TYPE.DATAELEMENT ) ) { String columnName = ExcelUtils.convertColNumberToColName( reportItem.getColumn() ); String formula = "SUM(" + columnName + (beginChapter + 1) + ":" + columnName + (rowBegin - 1) + ")"; - - ExcelUtils.writeFormulaByPOI( beginChapter, reportItem.getColumn(), formula, - sheet, this.csFormula ); - + + ExcelUtils.writeFormulaByPOI( beginChapter, reportItem.getColumn(), formula, sheet, this.csFormula ); + } } } } - } === modified file 'dhis-2/dhis-web/dhis-web-excel-reporting/src/main/java/org/hisp/dhis/reportexcel/export/action/GenerateReportOrganizationGroupListingAction.java' --- dhis-2/dhis-web/dhis-web-excel-reporting/src/main/java/org/hisp/dhis/reportexcel/export/action/GenerateReportOrganizationGroupListingAction.java 2009-12-21 11:09:00 +0000 +++ dhis-2/dhis-web/dhis-web-excel-reporting/src/main/java/org/hisp/dhis/reportexcel/export/action/GenerateReportOrganizationGroupListingAction.java 2010-01-05 07:36:05 +0000 @@ -107,8 +107,10 @@ for ( ReportExcelItem reportItem : reportExcelItems ) { + int iRow = 0; + int iCol = 0; + int chapperNo = 0; int rowBegin = reportItem.getRow(); - int chapperNo = 0; for ( OrganisationUnitGroup organisationUnitGroup : reportExcel.getOrganisationUnitGroups() ) { @@ -185,12 +187,13 @@ } else if ( reportItem.getItemType().equalsIgnoreCase( ReportExcelItem.TYPE.FORMULA_EXCEL ) ) { - ExcelUtils.writeFormulaByPOI( rowBegin, reportItem.getColumn(), reportItem.getExpression(), - sheet, this.csFormula ); + ExcelUtils.writeFormulaByPOI( rowBegin, reportItem.getColumn(), ExcelUtils + .checkingExcelFormula( reportItem.getExpression(), iRow, iCol ), sheet, this.csFormula ); } rowBegin++; serial++; + iRow++; } if ( reportItem.getItemType().equalsIgnoreCase( ReportExcelItem.TYPE.DATAELEMENT ) @@ -205,5 +208,4 @@ } } - } === modified file 'dhis-2/dhis-web/dhis-web-excel-reporting/src/main/java/org/hisp/dhis/reportexcel/export/advance/action/GenerateAdvancedReportCategoryAction.java' --- dhis-2/dhis-web/dhis-web-excel-reporting/src/main/java/org/hisp/dhis/reportexcel/export/advance/action/GenerateAdvancedReportCategoryAction.java 2009-12-21 11:09:00 +0000 +++ dhis-2/dhis-web/dhis-web-excel-reporting/src/main/java/org/hisp/dhis/reportexcel/export/advance/action/GenerateAdvancedReportCategoryAction.java 2010-01-05 07:36:05 +0000 @@ -123,6 +123,8 @@ for ( ReportExcelItem reportItem : reportExcelItems ) { + int iRow = 0; + int iCol = 0; int rowBegin = reportItem.getRow(); for ( DataElementGroupOrder dataElementGroup : reportExcel.getDataElementOrders() ) @@ -143,7 +145,7 @@ rowBegin++; int serial = 1; - + for ( DataElement dataElement : dataElementGroup.getDataElements() ) { @@ -164,8 +166,8 @@ } else if ( reportItem.getItemType().equalsIgnoreCase( ReportExcelItem.TYPE.FORMULA_EXCEL ) ) { - ExcelUtils.writeFormulaByPOI( rowBegin, reportItem.getColumn(), reportItem.getExpression(), - sheet, this.csFormula ); + ExcelUtils.writeFormulaByPOI( rowBegin, reportItem.getColumn(), ExcelUtils + .checkingExcelFormula( reportItem.getExpression(), iRow, iCol ), sheet, this.csFormula ); } else { @@ -191,6 +193,7 @@ } rowBegin++; serial++; + iRow++; } if ( reportItem.getItemType().equalsIgnoreCase( ReportExcelItem.TYPE.DATAELEMENT ) ) === modified file 'dhis-2/dhis-web/dhis-web-excel-reporting/src/main/java/org/hisp/dhis/reportexcel/export/advance/action/GenerateAdvancedReportOrgGroupListingAction.java' --- dhis-2/dhis-web/dhis-web-excel-reporting/src/main/java/org/hisp/dhis/reportexcel/export/advance/action/GenerateAdvancedReportOrgGroupListingAction.java 2009-12-21 11:09:00 +0000 +++ dhis-2/dhis-web/dhis-web-excel-reporting/src/main/java/org/hisp/dhis/reportexcel/export/advance/action/GenerateAdvancedReportOrgGroupListingAction.java 2010-01-05 07:36:05 +0000 @@ -94,8 +94,8 @@ Period period = periodDatabaseService.getSelectedPeriod(); this.installPeriod( period ); - ReportExcelOganiztionGroupListing reportExcel = (ReportExcelOganiztionGroupListing) reportService.getReportExcel( selectionManager - .getSelectedReportId() ); + ReportExcelOganiztionGroupListing reportExcel = (ReportExcelOganiztionGroupListing) reportService + .getReportExcel( selectionManager.getSelectedReportId() ); this.installReadTemplateFile( reportExcel, period, organisationUnitGroup ); @@ -126,9 +126,10 @@ { for ( ReportExcelItem reportItem : reportExcelItems ) { + int iRow = 0; + int iCol = 0; + int chapperNo = 0; int rowBegin = reportItem.getRow(); - int chapperNo = 0; - int beginChapter = rowBegin; chapperNo++; @@ -163,12 +164,13 @@ } else if ( reportItem.getItemType().equalsIgnoreCase( ReportExcelItem.TYPE.FORMULA_EXCEL ) ) { - ExcelUtils.writeFormulaByPOI( rowBegin, reportItem.getColumn(), reportItem.getExpression(), sheet, - this.csFormula ); + ExcelUtils.writeFormulaByPOI( rowBegin, reportItem.getColumn(), ExcelUtils.checkingExcelFormula( + reportItem.getExpression(), iRow, iCol ), sheet, this.csFormula ); } rowBegin++; serial++; + iRow++; } if ( reportItem.getItemType().equalsIgnoreCase( ReportExcelItem.TYPE.DATAELEMENT ) === modified file 'dhis-2/dhis-web/dhis-web-excel-reporting/src/main/java/org/hisp/dhis/reportexcel/utils/ExcelUtils.java' --- dhis-2/dhis-web/dhis-web-excel-reporting/src/main/java/org/hisp/dhis/reportexcel/utils/ExcelUtils.java 2009-12-21 11:09:00 +0000 +++ dhis-2/dhis-web/dhis-web-excel-reporting/src/main/java/org/hisp/dhis/reportexcel/utils/ExcelUtils.java 2010-01-05 07:36:05 +0000 @@ -27,6 +27,9 @@ * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. */ +import java.util.regex.Matcher; +import java.util.regex.Pattern; + import jxl.Cell; import jxl.Sheet; import jxl.write.Blank; @@ -46,6 +49,11 @@ */ public class ExcelUtils { + + private static Pattern pattern = null; + + private static Matcher matcher = null; + public static final String ZERO = "0.0"; public static final String TEXT = "TEXT"; @@ -54,6 +62,12 @@ public static final String EXTENSION_XLS = ".xls"; + private static final String PATTERN_FOR_ROW = "(\\d{1,})"; + + private static final String PATTERN_FOR_COLUMN = "([a-zA-Z])"; + + private static final String PATTERN_EXCELFORMULA = "(\\W?([a-zA-Z]{1,2}.?\\d{1,}!?))"; + private static final Integer NUMBER_OF_LETTER = new Integer( 26 ); private static final Integer POI_CELLSTYLE_BLANK = new Integer( org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BLANK ); @@ -356,4 +370,83 @@ } } + public static String checkingExcelFormula( String string_formula, int indexRow, int indexCol ) + { + Pattern pattern_formula = Pattern.compile( PATTERN_EXCELFORMULA ); + Matcher matcher_formula = pattern_formula.matcher( string_formula ); + + String s = null; + String sTemp = null; + StringBuffer buffer = null; + + while ( matcher_formula.find() ) + { + buffer = new StringBuffer(); + + s = matcher_formula.group().replaceAll( " ", "" ); + + if ( !s.endsWith( "!" ) ) + { + sTemp = s; + + if ( s.startsWith( "$" ) ) + { + if ( s.lastIndexOf( '$' ) > 0 ) + { + buffer = new StringBuffer( s ); + } + else + { + applyingPatternForRow( s, buffer, indexRow ); + } + } + else if ( s.lastIndexOf( '$' ) > 0 ) + { + applyingPatternForColumn( s, buffer, indexCol ); + } + else + { + applyingPatternForColumn( s, buffer, indexCol ); + + s = buffer.toString(); + buffer = new StringBuffer(); + + applyingPatternForRow( s, buffer, indexRow ); + } + + string_formula = string_formula.replace( sTemp, buffer.substring( 0 ) ); + } + } + + return string_formula; + } + + private static void applyingPatternForColumn( String sCell, StringBuffer buffer, int iCol ) + { + pattern = Pattern.compile( PATTERN_FOR_COLUMN ); + matcher = pattern.matcher( sCell ); + + if ( matcher.find() ) + { + sCell = ExcelUtils + .convertColNumberToColName( (ExcelUtils.convertExcelColumnNameToNumber( matcher.group() ) + iCol) ); + matcher.appendReplacement( buffer, sCell ); + } + + matcher.appendTail( buffer ); + } + + private static void applyingPatternForRow( String sCell, StringBuffer buffer, int iRow ) + { + pattern = Pattern.compile( PATTERN_FOR_ROW ); + matcher = pattern.matcher( sCell ); + + if ( matcher.find() ) + { + sCell = Integer.parseInt( matcher.group() ) + iRow + ""; + matcher.appendReplacement( buffer, sCell ); + } + matcher.appendTail( buffer ); + } + }