dhis2-devs team mailing list archive
-
dhis2-devs team
-
Mailing list archive
-
Message #14728
[Branch ~dhis2-devs-core/dhis2/trunk] Rev 5089: local vn - Fixed Preview functionality and put export-item-type FORMULA_EXCEL back to Category an...
------------------------------------------------------------
revno: 5089
committer: Hieu <hieu.hispvietnam@xxxxxxxxx>
branch nick: dhis2
timestamp: Thu 2011-11-03 11:26:28 +0700
message:
local vn - Fixed Preview functionality and put export-item-type FORMULA_EXCEL back to Category and Org-unit-listing report.
added:
local/vn/dhis-web-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/utils/NumberUtils.java
modified:
local/vn/dhis-service-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/ExportReportCategory.java
local/vn/dhis-service-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/ExportReportOganiztionGroupListing.java
local/vn/dhis-service-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/ExportReportPeriodColumnListing.java
local/vn/dhis-service-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/hibernate/HibernateExportReportStore.java
local/vn/dhis-web-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/exporting/AbstractGenerateExcelReportSupport.java
local/vn/dhis-web-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/exporting/action/GenerateReportCategoryAction.java
local/vn/dhis-web-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/exporting/action/GenerateReportNormalAction.java
local/vn/dhis-web-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/exporting/action/GenerateReportOrgGroupListingAction.java
local/vn/dhis-web-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/exporting/action/GenerateReportPeriodColumnListingAction.java
local/vn/dhis-web-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/exporting/advance/action/GenerateAdvancedReportCategoryAction.java
local/vn/dhis-web-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/exporting/advance/action/GenerateAdvancedReportOrgGroupListingAction.java
local/vn/dhis-web-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/importing/ImportDataGeneric.java
local/vn/dhis-web-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/orgunitgrouplisting/action/UpdateOrgUnitGroupAtLevelAction.java
local/vn/dhis-web-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/preview/action/XMLStructureResponse.java
local/vn/dhis-web-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/preview/action/XMLStructureResponseImport.java
local/vn/dhis-web-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/utils/ExcelUtils.java
local/vn/dhis-web-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/utils/StringUtils.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 'local/vn/dhis-service-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/ExportReportCategory.java'
--- local/vn/dhis-service-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/ExportReportCategory.java 2011-07-28 09:50:39 +0000
+++ local/vn/dhis-service-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/ExportReportCategory.java 2011-11-03 04:26:28 +0000
@@ -98,6 +98,7 @@
types.add( ExportItem.TYPE.DATAELEMENT );
types.add( ExportItem.TYPE.DATAELEMENT_CODE );
types.add( ExportItem.TYPE.DATAELEMENT_NAME );
+ types.add( ExportItem.TYPE.FORMULA_EXCEL);
types.add( ExportItem.TYPE.SERIAL );
return types;
=== modified file 'local/vn/dhis-service-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/ExportReportOganiztionGroupListing.java'
--- local/vn/dhis-service-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/ExportReportOganiztionGroupListing.java 2011-07-28 09:50:39 +0000
+++ local/vn/dhis-service-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/ExportReportOganiztionGroupListing.java 2011-11-03 04:26:28 +0000
@@ -115,6 +115,7 @@
types.add( ExportItem.TYPE.DATAELEMENT );
types.add( ExportItem.TYPE.ORGANISATION );
types.add( ExportItem.TYPE.INDICATOR );
+ types.add( ExportItem.TYPE.FORMULA_EXCEL );
types.add( ExportItem.TYPE.SERIAL );
return types;
=== modified file 'local/vn/dhis-service-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/ExportReportPeriodColumnListing.java'
--- local/vn/dhis-service-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/ExportReportPeriodColumnListing.java 2011-08-04 08:39:55 +0000
+++ local/vn/dhis-service-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/ExportReportPeriodColumnListing.java 2011-11-03 04:26:28 +0000
@@ -105,8 +105,8 @@
public List<String> getItemTypes()
{
List<String> types = new ArrayList<String>();
+ types.add( ExportItem.TYPE.DATAELEMENT );
types.add( ExportItem.TYPE.INDICATOR );
- types.add( ExportItem.TYPE.DATAELEMENT );
return types;
}
=== modified file 'local/vn/dhis-service-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/hibernate/HibernateExportReportStore.java'
--- local/vn/dhis-service-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/hibernate/HibernateExportReportStore.java 2011-08-03 08:38:38 +0000
+++ local/vn/dhis-service-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/hibernate/HibernateExportReportStore.java 2011-11-03 04:26:28 +0000
@@ -276,7 +276,7 @@
Session session = sessionFactory.getCurrentSession();
SQLQuery sqlQuery = session
.createSQLQuery( "select DISTINCT(sheetno) from reportexcel_items where reportexcel_items.reportexcelid="
- + reportId.intValue() );
+ + reportId.intValue() + " order by sheetno" );
return sqlQuery.list();
}
@@ -290,7 +290,7 @@
query.executeUpdate();
}
-
+
// -------------------------------------------------------------------------
// Data Entry Status
// -------------------------------------------------------------------------
@@ -374,7 +374,7 @@
return result;
}
-
+
@SuppressWarnings( "unchecked" )
public Collection<DataEntryStatus> getDataEntryStatusDefaultByDataSets( Collection<DataSet> dataSets )
{
=== modified file 'local/vn/dhis-web-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/exporting/AbstractGenerateExcelReportSupport.java'
--- local/vn/dhis-web-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/exporting/AbstractGenerateExcelReportSupport.java 2011-10-13 03:03:58 +0000
+++ local/vn/dhis-web-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/exporting/AbstractGenerateExcelReportSupport.java 2011-11-03 04:26:28 +0000
@@ -172,8 +172,6 @@
this.installDefaultExcelFormat();
- this.initFormulaEvaluating();
-
if ( exportReport.getOrganisationRow() != null && exportReport.getOrganisationColumn() != null )
{
String value = "";
=== modified file 'local/vn/dhis-web-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/exporting/action/GenerateReportCategoryAction.java'
--- local/vn/dhis-web-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/exporting/action/GenerateReportCategoryAction.java 2011-10-25 08:52:49 +0000
+++ local/vn/dhis-web-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/exporting/action/GenerateReportCategoryAction.java 2011-11-03 04:26:28 +0000
@@ -67,13 +67,6 @@
this.generateOutPutFile( exportReportInstance, exportReportItems, organisationUnit, sheet );
}
-
- for ( Integer sheetNo : exportReportService.getSheets( selectionManager.getSelectedReportId() ) )
- {
- Sheet sheet = this.templateWorkbook.getSheetAt( sheetNo - 1 );
-
- this.recalculatingFormula( sheet );
- }
}
// -------------------------------------------------------------------------
@@ -85,6 +78,7 @@
{
for ( ExportItem reportItem : exportReportItems )
{
+ int run = 0;
int rowBegin = reportItem.getRow();
for ( DataElementGroupOrder dataElementGroup : exportReport.getDataElementOrders() )
@@ -102,6 +96,7 @@
ExcelUtils.TEXT, sheet, this.csText12BoldCenter );
}
+ run++;
rowBegin++;
int serial = 1;
@@ -122,6 +117,11 @@
ExcelUtils.writeValueByPOI( rowBegin, reportItem.getColumn(), String.valueOf( serial ),
ExcelUtils.NUMBER, sheet, this.csTextSerial );
}
+ else if ( reportItem.getItemType().equalsIgnoreCase( ExportItem.TYPE.FORMULA_EXCEL ) )
+ {
+ ExcelUtils.writeFormulaByPOI( rowBegin, reportItem.getColumn(), ExcelUtils
+ .generateExcelFormula( reportItem.getExpression(), run, run ), sheet, csFormula );
+ }
else
{
ExportItem newReportItem = new ExportItem();
@@ -142,13 +142,15 @@
ExcelUtils.NUMBER, sheet, this.csNumber );
}
+
rowBegin++;
serial++;
+ run++;
}
if ( reportItem.getItemType().equalsIgnoreCase( ExportItem.TYPE.DATAELEMENT ) )
{
- String columnName = ExcelUtils.convertColNumberToColName( reportItem.getColumn() );
+ String columnName = ExcelUtils.convertColumnNumberToName( reportItem.getColumn() );
String formula = "SUM(" + columnName + (beginChapter + 1) + ":" + columnName + (rowBegin - 1) + ")";
ExcelUtils.writeFormulaByPOI( beginChapter, reportItem.getColumn(), formula, sheet, this.csFormula );
=== modified file 'local/vn/dhis-web-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/exporting/action/GenerateReportNormalAction.java'
--- local/vn/dhis-web-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/exporting/action/GenerateReportNormalAction.java 2011-07-28 09:50:39 +0000
+++ local/vn/dhis-web-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/exporting/action/GenerateReportNormalAction.java 2011-11-03 04:26:28 +0000
@@ -65,13 +65,6 @@
this.generateOutPutFile( exportReportItems, organisationUnit, sheet );
}
-
- for ( Integer sheetNo : exportReportService.getSheets( selectionManager.getSelectedReportId() ) )
- {
- Sheet sheet = this.templateWorkbook.getSheetAt( sheetNo - 1 );
-
- this.recalculatingFormula( sheet );
- }
}
// -------------------------------------------------------------------------
=== modified file 'local/vn/dhis-web-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/exporting/action/GenerateReportOrgGroupListingAction.java'
--- local/vn/dhis-web-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/exporting/action/GenerateReportOrgGroupListingAction.java 2011-10-25 08:52:49 +0000
+++ local/vn/dhis-web-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/exporting/action/GenerateReportOrgGroupListingAction.java 2011-11-03 04:26:28 +0000
@@ -93,14 +93,6 @@
Collection<ExportItem> exportReportItems = exportReportInstance.getExportItemBySheet( sheetNo );
generateOutPutFile( exportReportInstance, orgUniGroupAtLevels, exportReportItems, organisationUnit, sheet );
-
- }
-
- for ( Integer sheetNo : exportReportService.getSheets( selectionManager.getSelectedReportId() ) )
- {
- Sheet sheet = this.templateWorkbook.getSheetAt( sheetNo - 1 );
-
- this.recalculatingFormula( sheet );
}
}
@@ -112,9 +104,11 @@
Map<OrganisationUnitGroup, OrganisationUnitLevel> orgUniGroupAtLevels,
Collection<ExportItem> exportReportItems, OrganisationUnit organisationUnit, Sheet sheet )
{
+ List<OrganisationUnit> childrens = new ArrayList<OrganisationUnit>( organisationUnit.getChildren() );
for ( ExportItem reportItem : exportReportItems )
{
+ int run = 0;
int chapperNo = 0;
int firstRow = reportItem.getRow();
int rowBegin = firstRow + 1;
@@ -123,26 +117,22 @@
for ( OrganisationUnitGroup organisationUnitGroup : exportReport.getOrganisationUnitGroups() )
{
- OrganisationUnitLevel organisationUnitLevel = orgUniGroupAtLevels.get( organisationUnitGroup );
-
- List<OrganisationUnit> organisationUnitsAtLevel = new ArrayList<OrganisationUnit>();
-
- List<OrganisationUnit> childrenOrganisationUnits = new ArrayList<OrganisationUnit>( organisationUnit
- .getChildren() );
-
List<OrganisationUnit> organisationUnits = new ArrayList<OrganisationUnit>( organisationUnitGroup
.getMembers() );
+ OrganisationUnitLevel organisationUnitLevel = orgUniGroupAtLevels.get( organisationUnitGroup );
+
if ( organisationUnitLevel != null )
{
- organisationUnitsAtLevel = new ArrayList<OrganisationUnit>( organisationUnitService
- .getOrganisationUnitsAtLevel( organisationUnitLevel.getLevel(), organisationUnit ) );
+ List<OrganisationUnit> organisationUnitsAtLevel = new ArrayList<OrganisationUnit>(
+ organisationUnitService.getOrganisationUnitsAtLevel( organisationUnitLevel.getLevel(),
+ organisationUnit ) );
organisationUnits.retainAll( organisationUnitsAtLevel );
}
else
{
- organisationUnits.retainAll( childrenOrganisationUnits );
+ organisationUnits.retainAll( childrens );
}
Collections.sort( organisationUnits, new OrganisationUnitNameComparator() );
@@ -163,6 +153,7 @@
chapperNo++;
}
+ run++;
rowBegin++;
int serial = 1;
@@ -192,7 +183,14 @@
ExcelUtils.writeValueByPOI( rowBegin, reportItem.getColumn(), value + "", ExcelUtils.NUMBER,
sheet, this.csNumber );
}
+ else
+ // FORMULA_EXCEL
+ {
+ ExcelUtils.writeFormulaByPOI( rowBegin, reportItem.getColumn(), ExcelUtils
+ .generateExcelFormula( reportItem.getExpression(), run, run ), sheet, this.csFormula );
+ }
+ run++;
rowBegin++;
serial++;
}
@@ -201,7 +199,7 @@
.getItemType().equalsIgnoreCase( ExportItem.TYPE.INDICATOR ))
&& (!organisationUnits.isEmpty()) )
{
- String columnName = ExcelUtils.convertColNumberToColName( reportItem.getColumn() );
+ String columnName = ExcelUtils.convertColumnNumberToName( reportItem.getColumn() );
String formula = "SUM(" + columnName + (beginChapter + 1) + ":" + columnName + (rowBegin - 1) + ")";
ExcelUtils.writeFormulaByPOI( beginChapter, reportItem.getColumn(), formula, sheet, this.csFormula );
=== modified file 'local/vn/dhis-web-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/exporting/action/GenerateReportPeriodColumnListingAction.java'
--- local/vn/dhis-web-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/exporting/action/GenerateReportPeriodColumnListingAction.java 2011-07-28 09:50:39 +0000
+++ local/vn/dhis-web-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/exporting/action/GenerateReportPeriodColumnListingAction.java 2011-11-03 04:26:28 +0000
@@ -70,13 +70,6 @@
this.generateOutPutFile( exportReportInstance.getPeriodColumns(), exportReportItems, organisationUnit,
sheet );
}
-
- for ( Integer sheetNo : exportReportService.getSheets( selectionManager.getSelectedReportId() ) )
- {
- Sheet sheet = this.templateWorkbook.getSheetAt( sheetNo - 1 );
-
- this.recalculatingFormula( sheet );
- }
}
// -------------------------------------------------------------------------
=== modified file 'local/vn/dhis-web-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/exporting/advance/action/GenerateAdvancedReportCategoryAction.java'
--- local/vn/dhis-web-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/exporting/advance/action/GenerateAdvancedReportCategoryAction.java 2011-08-04 08:39:55 +0000
+++ local/vn/dhis-web-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/exporting/advance/action/GenerateAdvancedReportCategoryAction.java 2011-11-03 04:26:28 +0000
@@ -157,7 +157,7 @@
else if ( exportItem.getItemType().equalsIgnoreCase( ExportItem.TYPE.FORMULA_EXCEL ) )
{
ExcelUtils.writeFormulaByPOI( rowBegin, exportItem.getColumn(), ExcelUtils
- .checkingExcelFormula( exportItem.getExpression(), iRow, iCol ), sheet, this.csFormula );
+ .generateExcelFormula( exportItem.getExpression(), iRow, iCol ), sheet, this.csFormula );
}
else
{
@@ -188,7 +188,7 @@
if ( exportItem.getItemType().equalsIgnoreCase( ExportItem.TYPE.DATAELEMENT ) )
{
- String columnName = ExcelUtils.convertColNumberToColName( exportItem.getColumn() );
+ String columnName = ExcelUtils.convertColumnNumberToName( exportItem.getColumn() );
String formula = "SUM(" + columnName + (beginChapter + 1) + ":" + columnName + (rowBegin - 1) + ")";
ExcelUtils.writeFormulaByPOI( beginChapter, exportItem.getColumn(), formula, sheet, this.csFormula );
}
=== modified file 'local/vn/dhis-web-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/exporting/advance/action/GenerateAdvancedReportOrgGroupListingAction.java'
--- local/vn/dhis-web-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/exporting/advance/action/GenerateAdvancedReportOrgGroupListingAction.java 2011-08-04 08:39:55 +0000
+++ local/vn/dhis-web-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/exporting/advance/action/GenerateAdvancedReportOrgGroupListingAction.java 2011-11-03 04:26:28 +0000
@@ -156,7 +156,7 @@
}
else if ( exportItem.getItemType().equalsIgnoreCase( ExportItem.TYPE.FORMULA_EXCEL ) )
{
- ExcelUtils.writeFormulaByPOI( rowBegin, exportItem.getColumn(), ExcelUtils.checkingExcelFormula(
+ ExcelUtils.writeFormulaByPOI( rowBegin, exportItem.getColumn(), ExcelUtils.generateExcelFormula(
exportItem.getExpression(), iRow, iCol ), sheet, this.csFormula );
}
@@ -168,7 +168,7 @@
if ( exportItem.getItemType().equalsIgnoreCase( ExportItem.TYPE.DATAELEMENT )
&& (!organisationUnits.isEmpty()) )
{
- String columnName = ExcelUtils.convertColNumberToColName( exportItem.getColumn() );
+ String columnName = ExcelUtils.convertColumnNumberToName( exportItem.getColumn() );
String formula = "SUM(" + columnName + (beginChapter + 1) + ":" + columnName + (rowBegin - 1) + ")";
ExcelUtils.writeFormulaByPOI( beginChapter, exportItem.getColumn(), formula, sheet, this.csFormula );
}
=== modified file 'local/vn/dhis-web-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/importing/ImportDataGeneric.java'
--- local/vn/dhis-web-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/importing/ImportDataGeneric.java 2011-10-24 05:42:25 +0000
+++ local/vn/dhis-web-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/importing/ImportDataGeneric.java 2011-11-03 04:26:28 +0000
@@ -171,6 +171,8 @@
protected void addDataValue( OrganisationUnit unit, Period period, String expression, String value )
{
+ value = value.replaceAll( "\\.", "" ).replace( ",", "." );
+
DataElementOperand operand = expressionService.getOperandsInExpression( expression ).iterator().next();
DataElement dataElement = dataElementService.getDataElement( operand.getDataElementId() );
=== modified file 'local/vn/dhis-web-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/orgunitgrouplisting/action/UpdateOrgUnitGroupAtLevelAction.java'
--- local/vn/dhis-web-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/orgunitgrouplisting/action/UpdateOrgUnitGroupAtLevelAction.java 2011-08-04 08:39:55 +0000
+++ local/vn/dhis-web-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/orgunitgrouplisting/action/UpdateOrgUnitGroupAtLevelAction.java 2011-11-03 04:26:28 +0000
@@ -111,7 +111,7 @@
OrganisationUnitGroup organisationUnitGroup = organisationUnitGroupService
.getOrganisationUnitGroup( orgUnitGroupId );
- if ( levelId != null )
+ if ( levelId != null && levelId != -1 )
{
OrganisationUnitLevel organisationUnitLevel = organisationUnitService.getOrganisationUnitLevel( levelId );
=== modified file 'local/vn/dhis-web-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/preview/action/XMLStructureResponse.java'
--- local/vn/dhis-web-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/preview/action/XMLStructureResponse.java 2011-08-04 08:39:55 +0000
+++ local/vn/dhis-web-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/preview/action/XMLStructureResponse.java 2011-11-03 04:26:28 +0000
@@ -30,17 +30,22 @@
import static org.apache.commons.io.FilenameUtils.getExtension;
import static org.hisp.dhis.reportsheet.utils.ExcelUtils.convertAlignmentString;
import static org.hisp.dhis.reportsheet.utils.ExcelUtils.convertVerticalString;
-import static org.hisp.dhis.reportsheet.utils.ExcelUtils.readSpecialValueByPOI;
+import static org.hisp.dhis.reportsheet.utils.ExcelUtils.readValueByPOI;
+import static org.hisp.dhis.reportsheet.utils.NumberUtils.PATTERN_DECIMAL_FORMAT1;
+import static org.hisp.dhis.reportsheet.utils.NumberUtils.applyPatternDecimalFormat;
+import static org.hisp.dhis.reportsheet.utils.NumberUtils.resetDecimalFormatByLocale;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.Collection;
+import java.util.Locale;
import org.apache.poi.hssf.usermodel.HSSFPatternFormatting;
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.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;
@@ -70,6 +75,8 @@
*/
private Workbook WORKBOOK;
+ private FormulaEvaluator evaluatorFormula;
+
private boolean bWRITE_VERSION;
private boolean bWRITE_DTD;
@@ -120,7 +127,7 @@
this.cleanUpForResponse();
this.bWRITE_DTD = bWriteDTD;
this.bWRITE_VERSION = bWriteVersion;
-
+
if ( getExtension( pathFileName ).equals( "xls" ) )
{
this.WORKBOOK = new HSSFWorkbook( new FileInputStream( pathFileName ) );
@@ -130,6 +137,11 @@
this.WORKBOOK = new XSSFWorkbook( new FileInputStream( pathFileName ) );
}
+ resetDecimalFormatByLocale( Locale.GERMAN );
+ applyPatternDecimalFormat( PATTERN_DECIMAL_FORMAT1 );
+
+ this.evaluatorFormula = WORKBOOK.getCreationHelper().createFormulaEvaluator();
+
if ( bFormat )
{
writeFormattedXML( collectSheets, bDetailed, bWriteDescription );
@@ -179,7 +191,7 @@
if ( cell.getCellType() != Cell.CELL_TYPE_BLANK )
{
xml.append( "<col number='" + j + "'>" );
- xml.append( "<![CDATA[" + readSpecialValueByPOI( i + 1, j + 1, sheet ) + "]]>" );
+ xml.append( "<![CDATA[" + readValueByPOI( i + 1, j + 1, sheet, evaluatorFormula ) + "]]>" );
xml.append( "</col>" );
}
@@ -257,7 +269,7 @@
if ( (cell.getCellStyle() != null) || cell.getCellType() != Cell.CELL_TYPE_BLANK )
{
xml.append( "<col no='" + j + "'><data>" );
- xml.append( "<![CDATA[" + readSpecialValueByPOI( i + 1, j + 1, s ) + "]]></data>" );
+ xml.append( "<![CDATA[" + readValueByPOI( i + 1, j + 1, s, evaluatorFormula ) + "]]></data>" );
this.readingDetailsFormattedCell( cell, bDetailed );
=== modified file 'local/vn/dhis-web-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/preview/action/XMLStructureResponseImport.java'
--- local/vn/dhis-web-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/preview/action/XMLStructureResponseImport.java 2011-10-24 05:42:25 +0000
+++ local/vn/dhis-web-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/preview/action/XMLStructureResponseImport.java 2011-11-03 04:26:28 +0000
@@ -29,19 +29,25 @@
import static org.apache.commons.io.FilenameUtils.getExtension;
import static org.hisp.dhis.reportsheet.utils.ExcelUtils.convertAlignmentString;
-import static org.hisp.dhis.reportsheet.utils.ExcelUtils.readSpecialValueByPOI;
+import static org.hisp.dhis.reportsheet.utils.ExcelUtils.readValueByPOI;
+import static org.hisp.dhis.reportsheet.utils.NumberUtils.PATTERN_DECIMAL_FORMAT1;
+import static org.hisp.dhis.reportsheet.utils.NumberUtils.applyPatternDecimalFormat;
+import static org.hisp.dhis.reportsheet.utils.NumberUtils.resetDecimalFormatByLocale;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.Collection;
import java.util.List;
+import java.util.Locale;
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.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
+import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.hisp.dhis.reportsheet.importitem.ImportItem;
@@ -62,7 +68,9 @@
/**
* The workbook we are reading from a given file
*/
- private org.apache.poi.ss.usermodel.Workbook WORKBOOK;
+ private Workbook WORKBOOK;
+
+ private FormulaEvaluator evaluatorFormula;
private static final String WORKBOOK_OPENTAG = "<workbook>";
@@ -111,6 +119,11 @@
{
this.WORKBOOK = new XSSFWorkbook( inputStream );
}
+
+ resetDecimalFormatByLocale( Locale.GERMAN );
+ applyPatternDecimalFormat( PATTERN_DECIMAL_FORMAT1 );
+
+ this.evaluatorFormula = WORKBOOK.getCreationHelper().createFormulaEvaluator();
this.writeFormattedXML( collectSheets, importItems, bWriteDescription );
}
@@ -200,7 +213,7 @@
xml.append( ">" );
} // end checking
- xml.append( "<data><![CDATA[" + readSpecialValueByPOI( i + 1, j + 1, s ) + "]]></data>" );
+ xml.append( "<data><![CDATA[" + readValueByPOI( i + 1, j + 1, s, evaluatorFormula ) + "]]></data>" );
this.readingDetailsFormattedCell( cell );
=== modified file 'local/vn/dhis-web-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/utils/ExcelUtils.java'
--- local/vn/dhis-web-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/utils/ExcelUtils.java 2011-07-28 09:50:39 +0000
+++ local/vn/dhis-web-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/utils/ExcelUtils.java 2011-11-03 04:26:28 +0000
@@ -46,8 +46,10 @@
import org.apache.poi.ss.formula.FormulaParser;
import org.apache.poi.ss.formula.FormulaParsingWorkbook;
import org.apache.poi.ss.usermodel.CellStyle;
+import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.ErrorConstants;
+import org.apache.poi.ss.usermodel.FormulaEvaluator;
/**
* @author Tran Thanh Tri
@@ -61,13 +63,15 @@
private static Matcher matcher = null;
+ private static DataFormatter dataFormatter = new DataFormatter();
+
private static FormulaParsingWorkbook evaluationWorkbook = HSSFEvaluationWorkbook.create( new HSSFWorkbook() );
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 String PATTERN_EXCELFORMULA = "(\\$?([a-zA-Z]{1,})\\$?(\\d{1,}!?))";
private static final Integer NUMBER_OF_LETTER = new Integer( 26 );
@@ -204,7 +208,8 @@
}
/* POI - Read the special value of given cell */
- public static String readSpecialValueByPOI( int row, int column, org.apache.poi.ss.usermodel.Sheet sheetPOI )
+ public static String readValueByPOI( int row, int column, org.apache.poi.ss.usermodel.Sheet sheetPOI,
+ FormulaEvaluator evaluator )
{
org.apache.poi.ss.usermodel.Cell cellPOI = getCellByPOI( row, column, sheetPOI );
@@ -229,7 +234,7 @@
case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_FORMULA:
try
{
- value = String.valueOf( cellPOI.getNumericCellValue() );
+ value = NumberUtils.getFormattedNumber( dataFormatter.formatCellValue( cellPOI, evaluator ) );
}
catch ( IllegalStateException ise )
{
@@ -243,7 +248,7 @@
}
else
{
- value = String.valueOf( cellPOI.getNumericCellValue() );
+ value = NumberUtils.getFormattedNumber( dataFormatter.formatCellValue( cellPOI ) );
}
break;
@@ -434,7 +439,7 @@
}
}
- public static String convertColNumberToColName( int column )
+ public static String convertColumnNumberToName( int column )
{
String ConvertToLetter = "";
@@ -453,7 +458,7 @@
return ConvertToLetter;
}
- public static int convertExcelColumnNameToNumber( String columnName )
+ public static int convertColumnNameToNumber( String columnName )
{
try
{
@@ -546,7 +551,7 @@
return valign;
}
- public static String checkingExcelFormula( String string_formula, int indexRow, int indexCol )
+ public static String generateExcelFormula( String string_formula, int indexRow, int indexCol )
{
Pattern pattern_formula = Pattern.compile( PATTERN_EXCELFORMULA );
Matcher matcher_formula = pattern_formula.matcher( string_formula );
@@ -622,8 +627,8 @@
if ( matcher.find() )
{
- sCell = ExcelUtils
- .convertColNumberToColName( (ExcelUtils.convertExcelColumnNameToNumber( matcher.group() ) + iCol) );
+ sCell = ExcelUtils.convertColumnNumberToName( ExcelUtils.convertColumnNameToNumber( matcher.group() )
+ + iCol );
matcher.appendReplacement( buffer, sCell );
}
=== added file 'local/vn/dhis-web-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/utils/NumberUtils.java'
--- local/vn/dhis-web-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/utils/NumberUtils.java 1970-01-01 00:00:00 +0000
+++ local/vn/dhis-web-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/utils/NumberUtils.java 2011-11-03 04:26:28 +0000
@@ -0,0 +1,74 @@
+package org.hisp.dhis.reportsheet.utils;
+
+import java.text.DecimalFormat;
+import java.text.NumberFormat;
+import java.util.Locale;
+
+/*
+ * Copyright (c) 2004-2011, University of Oslo
+ * All rights reserved.
+ *
+ * Redistribution and use in source and binary forms, with or without
+ * modification, are permitted provided that the following conditions are met:
+ * * Redistributions of source code must retain the above copyright notice, this
+ * list of conditions and the following disclaimer.
+ * * Redistributions in binary form must reproduce the above copyright notice,
+ * this list of conditions and the following disclaimer in the documentation
+ * and/or other materials provided with the distribution.
+ * * Neither the name of the HISP project nor the names of its contributors may
+ * be used to endorse or promote products derived from this software without
+ * specific prior written permission.
+ *
+ * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND
+ * ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
+ * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
+ * DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR
+ * ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
+ * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
+ * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON
+ * ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
+ * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
+ * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
+ */
+
+/**
+ * @author Dang Duy Hieu
+ * @version $Id$
+ */
+public class NumberUtils
+{
+ // DecimalFormatNumber for VN as same as Locale.GERMAN's one.
+ private static DecimalFormat df = null;
+
+ // This pattern used for VN
+ public static final String PATTERN_DECIMAL_FORMAT1 = "#,##0.############";
+
+ // This pattern is default in DHIS2
+ public static final String PATTERN_DECIMAL_FORMAT2 = "#0.############";
+
+ // -------------------------------------------------------------------------
+ //
+ // -------------------------------------------------------------------------
+
+ public static void resetDecimalFormatByLocale( Locale locale )
+ {
+ df = (DecimalFormat) NumberFormat.getInstance( locale );
+ }
+
+ public static void applyPatternDecimalFormat( String pattern )
+ {
+ df.applyPattern( pattern == null ? PATTERN_DECIMAL_FORMAT1 : pattern );
+ }
+
+ public static String getFormattedNumber( String input )
+ {
+ try
+ {
+ return df.format( Double.parseDouble( input ) );
+ }
+ catch ( NumberFormatException nfe )
+ {
+ return input;
+ }
+ }
+}
=== modified file 'local/vn/dhis-web-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/utils/StringUtils.java'
--- local/vn/dhis-web-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/utils/StringUtils.java 2011-08-04 08:39:55 +0000
+++ local/vn/dhis-web-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/utils/StringUtils.java 2011-11-03 04:26:28 +0000
@@ -1,7 +1,5 @@
package org.hisp.dhis.reportsheet.utils;
-import java.text.DecimalFormat;
-import java.text.NumberFormat;
/*
* Copyright (c) 2004-2011, University of Oslo
@@ -45,10 +43,6 @@
private static final String SEPARATE = "/";
- private static final String PATTERN_DECIMAL_FORMAT = "#0.##";
-
- private static final NumberFormat nf = new DecimalFormat( PATTERN_DECIMAL_FORMAT );
-
/* ---------------------------------------------------------------------- */
/* */
/* ---------------------------------------------------------------------- */
@@ -124,27 +118,6 @@
/* */
/* ---------------------------------------------------------------------- */
- public static String applyPatternDecimalFormat( String input )
- {
- try
- {
- if ( Double.parseDouble( input ) >= 0.0d )
- {
- input = nf.format( Double.parseDouble( input ) );
- }
- return input;
- }
- catch ( NumberFormatException nfe )
- {
- return input;
- }
-
- }
-
- /* ---------------------------------------------------------------------- */
- /* */
- /* ---------------------------------------------------------------------- */
-
@SuppressWarnings( "unused" )
private static final String replacedSeparateCharacter( String path )
{
