dhis2-devs team mailing list archive
-
dhis2-devs team
-
Mailing list archive
-
Message #03897
[Branch ~dhis2-devs-core/dhis2/trunk] Rev 1298: Improvement in generating/previewing report excel for Excel Reporting module
------------------------------------------------------------
revno: 1298
committer: hieu <hieu.hispvietnam@xxxxxxxxx>
branch nick: trunk
timestamp: Wed 2010-01-06 15:24:42 +0700
message:
Improvement in generating/previewing report excel for Excel Reporting module
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/GenerateReportNormalAction.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/action/GenerateReportPeriodColumnListingAction.java
dhis-2/dhis-web/dhis-web-excel-reporting/src/main/java/org/hisp/dhis/reportexcel/export/action/GenerateReportSupport.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/GenerateAdvancedReportNormalAction.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/export/advance/action/GenerateAdvancedReportPeriodColumnListingAction.java
dhis-2/dhis-web/dhis-web-excel-reporting/src/main/java/org/hisp/dhis/reportexcel/preview/action/XMLStructureResponse.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 2010-01-05 07:36:05 +0000
+++ dhis-2/dhis-web/dhis-web-excel-reporting/src/main/java/org/hisp/dhis/reportexcel/export/action/GenerateReportCategoryAction.java 2010-01-06 08:24:42 +0000
@@ -72,6 +72,14 @@
}
+ for ( Integer sheetNo : reportService.getSheets( selectionManager.getSelectedReportId() ) )
+ {
+ Sheet sheet = this.templateWorkbook.getSheetAt( sheetNo - 1 );
+
+ this.recalculatingFormula( sheet );
+
+ }
+
this.complete();
statementManager.destroy();
=== modified file 'dhis-2/dhis-web/dhis-web-excel-reporting/src/main/java/org/hisp/dhis/reportexcel/export/action/GenerateReportNormalAction.java'
--- dhis-2/dhis-web/dhis-web-excel-reporting/src/main/java/org/hisp/dhis/reportexcel/export/action/GenerateReportNormalAction.java 2009-12-21 17:13:33 +0000
+++ dhis-2/dhis-web/dhis-web-excel-reporting/src/main/java/org/hisp/dhis/reportexcel/export/action/GenerateReportNormalAction.java 2010-01-06 08:24:42 +0000
@@ -70,13 +70,21 @@
}
- complete();
+ for ( Integer sheetNo : reportService.getSheets( selectionManager.getSelectedReportId() ) )
+ {
+ Sheet sheet = this.templateWorkbook.getSheetAt( sheetNo - 1 );
+
+ this.recalculatingFormula( sheet );
+ }
+
+
+ this.complete();
statementManager.destroy();
return SUCCESS;
}
-
+
private void generateOutPutFile( Collection<ReportExcelItem> reportExcelItems, OrganisationUnit organisationUnit,
Sheet sheet )
{
=== 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 2010-01-05 07:36:05 +0000
+++ dhis-2/dhis-web/dhis-web-excel-reporting/src/main/java/org/hisp/dhis/reportexcel/export/action/GenerateReportOrganizationGroupListingAction.java 2010-01-06 08:24:42 +0000
@@ -93,6 +93,13 @@
}
+ for ( Integer sheetNo : reportService.getSheets( selectionManager.getSelectedReportId() ) )
+ {
+ Sheet sheet = this.templateWorkbook.getSheetAt( sheetNo - 1 );
+
+ this.recalculatingFormula( sheet );
+ }
+
this.complete();
statementManager.destroy();
=== modified file 'dhis-2/dhis-web/dhis-web-excel-reporting/src/main/java/org/hisp/dhis/reportexcel/export/action/GenerateReportPeriodColumnListingAction.java'
--- dhis-2/dhis-web/dhis-web-excel-reporting/src/main/java/org/hisp/dhis/reportexcel/export/action/GenerateReportPeriodColumnListingAction.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/GenerateReportPeriodColumnListingAction.java 2010-01-06 08:24:42 +0000
@@ -74,7 +74,15 @@
}
- complete();
+ for ( Integer sheetNo : reportService.getSheets( selectionManager.getSelectedReportId() ) )
+ {
+ Sheet sheet = this.templateWorkbook.getSheetAt( sheetNo - 1 );
+
+ this.recalculatingFormula( sheet );
+
+ }
+
+ this.complete();
statementManager.destroy();
=== modified file 'dhis-2/dhis-web/dhis-web-excel-reporting/src/main/java/org/hisp/dhis/reportexcel/export/action/GenerateReportSupport.java'
--- dhis-2/dhis-web/dhis-web-excel-reporting/src/main/java/org/hisp/dhis/reportexcel/export/action/GenerateReportSupport.java 2009-12-22 07:49:08 +0000
+++ dhis-2/dhis-web/dhis-web-excel-reporting/src/main/java/org/hisp/dhis/reportexcel/export/action/GenerateReportSupport.java 2010-01-06 08:24:42 +0000
@@ -43,10 +43,13 @@
import org.amplecode.quick.StatementManager;
import org.apache.poi.hssf.usermodel.HSSFHeader;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
+import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Font;
+import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.IndexedColors;
+import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.hisp.dhis.aggregation.AggregationService;
@@ -109,35 +112,35 @@
// Dependency
// -------------------------------------------
- protected OrganisationUnitSelectionManager organisationUnitSelectionManager;
+ AggregationService aggregationService;
CurrentUserService currentUserService;
- AggregationService aggregationService;
+ DataMartStore dataMartStore;
IndicatorService indicatorService;
+ InitializePOIStylesManager initPOIStylesManager;
+
protected DataElementCategoryService categoryService;
protected DataElementService dataElementService;
+ protected I18nFormat format;
+
+ protected PeriodService periodService;
+
+ protected PeriodDatabaseService periodDatabaseService;
+
+ protected ReportExcelService reportService;
+
protected ReportLocationManager reportLocationManager;
- protected I18nFormat format;
-
- DataMartStore dataMartStore;
-
- InitializePOIStylesManager initPOIStylesManager;
-
protected StatementManager statementManager;
protected SelectionManager selectionManager;
- protected ReportExcelService reportService;
-
- protected PeriodService periodService;
-
- protected PeriodDatabaseService periodDatabaseService;
+ protected OrganisationUnitSelectionManager organisationUnitSelectionManager;
// -------------------------------------------
// Input & Output
@@ -305,6 +308,8 @@
protected CellStyle csText12BoldCenter;
+ protected FormulaEvaluator evaluatorFormula;
+
SimpleDateFormat dateformatter = new SimpleDateFormat( "dd.MM.yyyy.h.mm.ss.a" );
protected void initExcelFormat()
@@ -442,6 +447,8 @@
this.initExcelFormat();
this.installDefaultExcelFormat();
+
+ this.initFormulaEvaluating();
ExcelUtils.writeValueByPOI( reportExcel.getOrganisationRow(), reportExcel.getOrganisationColumn(),
organisationUnit.getName(), ExcelUtils.TEXT, templateWorkbook.getSheetAt( 0 ), csText );
@@ -668,6 +675,8 @@
this.installDefaultExcelFormat();
+ this.initFormulaEvaluating();
+
ExcelUtils.writeValueByPOI( reportExcel.getOrganisationRow(), reportExcel.getOrganisationColumn(),
organisationUnitGroup.getName(), ExcelUtils.TEXT, templateWorkbook.getSheetAt( 0 ), csText );
@@ -680,14 +689,18 @@
// Supporting method(s)
// ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
- protected void complete()
- throws IOException
+ protected void recalculatingFormula( Sheet sheet )
{
- this.templateWorkbook.write( outputStreamExcelTemplate );
-
- this.outputStreamExcelTemplate.close();
-
- selectionManager.setDownloadFilePath( outputReportFile.getPath() );
+ for ( Row row : sheet )
+ {
+ for ( Cell cell : row )
+ {
+ if ( (cell != null) && (cell.getCellType() == Cell.CELL_TYPE_FORMULA) )
+ {
+ this.evaluatorFormula.evaluateFormulaCell( cell );
+ }
+ }
+ }
}
private boolean checkingExtensionExcelFile( String fileName )
@@ -708,11 +721,30 @@
}
else
{
- /* DO NOT DELETE THIS STATEMENT */
-
- // this.templateWorkbook = new XSSFWorkbook(
- // reportLocationManager.getReportExcelTemplateDirectory()
- // + File.separator + reportExcel.getExcelTemplateFile() );
+ /**
+ * DO NOT DELETE THIS STATEMENT
+ *
+ * this.templateWorkbook = new XSSFWorkbook(
+ * reportLocationManager.getReportExcelTemplateDirectory() +
+ * File.separator + reportExcel.getExcelTemplateFile() );
+ *
+ */
}
}
+
+ private void initFormulaEvaluating()
+ {
+ this.evaluatorFormula = this.templateWorkbook.getCreationHelper().createFormulaEvaluator();
+ }
+
+ protected void complete()
+ throws IOException
+ {
+ this.templateWorkbook.write( outputStreamExcelTemplate );
+
+ this.outputStreamExcelTemplate.close();
+
+ selectionManager.setDownloadFilePath( outputReportFile.getPath() );
+ }
+
}
=== 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 2010-01-05 07:36:05 +0000
+++ dhis-2/dhis-web/dhis-web-excel-reporting/src/main/java/org/hisp/dhis/reportexcel/export/advance/action/GenerateAdvancedReportCategoryAction.java 2010-01-06 08:24:42 +0000
@@ -108,6 +108,14 @@
}
+ for ( Integer sheetNo : reportService.getSheets( selectionManager.getSelectedReportId() ) )
+ {
+ Sheet sheet = this.templateWorkbook.getSheetAt( sheetNo - 1 );
+
+ this.recalculatingFormula( sheet );
+
+ }
+
this.complete();
statementManager.destroy();
=== modified file 'dhis-2/dhis-web/dhis-web-excel-reporting/src/main/java/org/hisp/dhis/reportexcel/export/advance/action/GenerateAdvancedReportNormalAction.java'
--- dhis-2/dhis-web/dhis-web-excel-reporting/src/main/java/org/hisp/dhis/reportexcel/export/advance/action/GenerateAdvancedReportNormalAction.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/GenerateAdvancedReportNormalAction.java 2010-01-06 08:24:42 +0000
@@ -27,7 +27,6 @@
package org.hisp.dhis.reportexcel.export.advance.action;
-
import java.util.Collection;
import java.util.Iterator;
import java.util.Set;
@@ -121,6 +120,14 @@
}
+ for ( Integer sheetNo : reportService.getSheets( selectionManager.getSelectedReportId() ) )
+ {
+ Sheet sheet = this.templateWorkbook.getSheetAt( sheetNo - 1 );
+
+ this.recalculatingFormula( sheet );
+
+ }
+
this.complete();
statementManager.destroy();
=== 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 2010-01-05 07:36:05 +0000
+++ dhis-2/dhis-web/dhis-web-excel-reporting/src/main/java/org/hisp/dhis/reportexcel/export/advance/action/GenerateAdvancedReportOrgGroupListingAction.java 2010-01-06 08:24:42 +0000
@@ -113,7 +113,15 @@
this.generateOutPutFile( reportExcel, reportExcelItems, organisationUnits, sheet );
}
-
+
+ for ( Integer sheetNo : reportService.getSheets( selectionManager.getSelectedReportId() ) )
+ {
+ Sheet sheet = this.templateWorkbook.getSheetAt( sheetNo - 1 );
+
+ this.recalculatingFormula( sheet );
+
+ }
+
this.complete();
statementManager.destroy();
=== modified file 'dhis-2/dhis-web/dhis-web-excel-reporting/src/main/java/org/hisp/dhis/reportexcel/export/advance/action/GenerateAdvancedReportPeriodColumnListingAction.java'
--- dhis-2/dhis-web/dhis-web-excel-reporting/src/main/java/org/hisp/dhis/reportexcel/export/advance/action/GenerateAdvancedReportPeriodColumnListingAction.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/GenerateAdvancedReportPeriodColumnListingAction.java 2010-01-06 08:24:42 +0000
@@ -108,6 +108,14 @@
}
+ for ( Integer sheetNo : reportService.getSheets( selectionManager.getSelectedReportId() ) )
+ {
+ Sheet sheet = this.templateWorkbook.getSheetAt( sheetNo - 1 );
+
+ this.recalculatingFormula( sheet );
+
+ }
+
this.complete();
statementManager.destroy();
=== modified file 'dhis-2/dhis-web/dhis-web-excel-reporting/src/main/java/org/hisp/dhis/reportexcel/preview/action/XMLStructureResponse.java'
--- dhis-2/dhis-web/dhis-web-excel-reporting/src/main/java/org/hisp/dhis/reportexcel/preview/action/XMLStructureResponse.java 2009-12-17 02:15:50 +0000
+++ dhis-2/dhis-web/dhis-web-excel-reporting/src/main/java/org/hisp/dhis/reportexcel/preview/action/XMLStructureResponse.java 2010-01-06 08:24:42 +0000
@@ -265,11 +265,6 @@
Cell[] cell = null;
CellFormat format = null;
- boolean bFormula = false;
- String recalculatedValue = "";
-
- org.apache.poi.ss.usermodel.Sheet sheet = wb.getSheetAt( sheetNo );
- org.apache.poi.ss.usermodel.FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
for ( int i = 0; i < s.getRows(); i++ )
{
@@ -284,66 +279,12 @@
// information
if ( (cell[j].getType() != CellType.EMPTY) || (cell[j].getCellFormat() != null) )
{
- bFormula = false;
-
- org.apache.poi.hssf.util.CellReference cellReference = new org.apache.poi.hssf.util.CellReference(
- i, j );
- org.apache.poi.ss.usermodel.Row rowRef = sheet.getRow( cellReference.getRow() );
- org.apache.poi.ss.usermodel.Cell cellRef = rowRef.getCell( cellReference.getCol() );
-
- if ( (cellRef != null)
- && (cellRef.getCellType() == org.apache.poi.ss.usermodel.Cell.CELL_TYPE_FORMULA) )
- {
- bFormula = true;
- recalculatedValue = "";
-
- // CELL_TYPE_NUMERIC if this cell is an
- // NumbericCell
- switch ( evaluator.evaluateInCell( cellRef ).getCellType() )
- {
- case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC:
-
- recalculatedValue = String.valueOf( cellRef.getNumericCellValue() );
-
- break;
-
- // CELL_TYPE_STRING will occur if the formula return a
- // string value
- case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING:
-
- recalculatedValue = cellRef.getRichStringCellValue().getString();
-
- break;
-
- // CELL_TYPE_FORMULA will never occur
- case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_FORMULA:
- System.out.println( "Formula into cell :: [" + cellRef.getRowIndex() + "]["
- + cellRef.getColumnIndex() + "]" );
- break;
-
- // CELL_TYPE_ERROR if this cell is an ErrorCell
- case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_ERROR:
-
- recalculatedValue = cell[j].getContents();
- break;
- }
- }
-
- // end of checking the cell formula
STRUCTURE_DATA_RESPONSE.append( " <col no=\"" + j + "\">" );
STRUCTURE_DATA_RESPONSE.append( PRINT_END_LINE );
STRUCTURE_DATA_RESPONSE.append( " <data>" );
- if ( bFormula )
- {
- STRUCTURE_DATA_RESPONSE.append( "<![CDATA["
- + StringUtils.applyPatternDecimalFormat( recalculatedValue ) + "]]>" );
- }
- else
- {
- STRUCTURE_DATA_RESPONSE.append( "<![CDATA["
- + StringUtils.applyPatternDecimalFormat( cell[j].getContents() ) + "]]>" );
- }
+ STRUCTURE_DATA_RESPONSE.append( "<![CDATA["
+ + StringUtils.applyPatternDecimalFormat( cell[j].getContents() ) + "]]>" );
STRUCTURE_DATA_RESPONSE.append( "</data>" );
STRUCTURE_DATA_RESPONSE.append( PRINT_END_LINE );
