dhis2-devs team mailing list archive
-
dhis2-devs team
-
Mailing list archive
-
Message #03894
[Branch ~dhis2-devs-core/dhis2/trunk] Rev 1297: Improved function in Excel Reporting module.
------------------------------------------------------------
revno: 1297
committer: hieu <hieu.hispvietnam@xxxxxxxxx>
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 );
+ }
+
}
