=== 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 getItemTypes() { List types = new ArrayList(); + 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 getDataEntryStatusDefaultByDataSets( Collection 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 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 orgUniGroupAtLevels, Collection exportReportItems, OrganisationUnit organisationUnit, Sheet sheet ) { + List childrens = new ArrayList( 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 organisationUnitsAtLevel = new ArrayList(); - - List childrenOrganisationUnits = new ArrayList( organisationUnit - .getChildren() ); - List organisationUnits = new ArrayList( organisationUnitGroup .getMembers() ); + OrganisationUnitLevel organisationUnitLevel = orgUniGroupAtLevels.get( organisationUnitGroup ); + if ( organisationUnitLevel != null ) { - organisationUnitsAtLevel = new ArrayList( organisationUnitService - .getOrganisationUnitsAtLevel( organisationUnitLevel.getLevel(), organisationUnit ) ); + List organisationUnitsAtLevel = new ArrayList( + 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( "" ); - xml.append( "" ); + xml.append( "" ); xml.append( "" ); } @@ -257,7 +269,7 @@ if ( (cell.getCellStyle() != null) || cell.getCellType() != Cell.CELL_TYPE_BLANK ) { xml.append( "" ); - xml.append( "" ); + xml.append( "" ); 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 = ""; @@ -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( "" ); + xml.append( "" ); 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 ) {