------------------------------------------------------------
revno: 1297
committer: hieu <hieu.hispviet...@gmail.com>
branch nick: trunk
timestamp: Tue 2010-01-05 14:36:05 +0700
message:
  Improved function in Excel Reporting module.
    + Added a new method which is used to check ReportItem that is known as 
EXCEL FORMULA.
    + REQUIREMENT/RECOMMENDATION for creating a new report item ( EXCEL FORMULA 
) MUST use '$' simple in front of either any column or any row which is fixed 
from now, ie, $A$1 , $B10 , CT$36 , etc...
modified:
  
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/GenerateReportOrganizationGroupListingAction.java
  
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/GenerateAdvancedReportOrgGroupListingAction.java
  
dhis-2/dhis-web/dhis-web-excel-reporting/src/main/java/org/hisp/dhis/reportexcel/utils/ExcelUtils.java


--
lp:dhis2
https://code.launchpad.net/~dhis2-devs-core/dhis2/trunk

Your team DHIS 2 developers is subscribed to branch lp:dhis2.
To unsubscribe from this branch go to 
https://code.launchpad.net/~dhis2-devs-core/dhis2/trunk/+edit-subscription.
=== 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 );
+    }
+
 }

_______________________________________________
Mailing list: https://launchpad.net/~dhis2-devs
Post to     : dhis2-devs@lists.launchpad.net
Unsubscribe : https://launchpad.net/~dhis2-devs
More help   : https://help.launchpad.net/ListHelp

Reply via email to