=== 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-11-11 11:09:23 +0000
+++ dhis-2/dhis-web/dhis-web-excel-reporting/src/main/java/org/hisp/dhis/reportexcel/preview/action/XMLStructureResponse.java 2009-11-16 04:13:04 +0000
@@ -42,6 +42,7 @@
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellReference;
+import org.hisp.dhis.reportexcel.utils.StringUtils;
/**
* Simple demo class which uses the api to present the contents of an excel 97
@@ -121,11 +122,11 @@
throws Exception
{
- this.PATH_FILE_NAME = pathFileName;
- this.WORKBOOK = Workbook.getWorkbook( new File( pathFileName ) );
this.ENCODING = enc;
this.bWRITE_DTD = bWriteDTD;
this.bWRITE_VERSION = bWriteVersion;
+ this.PATH_FILE_NAME = pathFileName;
+ this.WORKBOOK = Workbook.getWorkbook( new File( pathFileName ) );
this.STRUCTURE_DATA_RESPONSE = new StringBuffer();
if ( this.ENCODING == null || !this.ENCODING.equals( "UnicodeBig" ) )
@@ -135,6 +136,7 @@
if ( bFormat )
{
+ // recalculatedValueForCellFormula(sheetId);
writeFormattedXML( sheetId, bDetailed, bWriteDescription );
}
else
@@ -214,19 +216,7 @@
throws Exception
{
FileInputStream fis = new FileInputStream( this.PATH_FILE_NAME );
- org.apache.poi.ss.usermodel.Workbook wb = new HSSFWorkbook( fis );
-
- if ( sheetId > 0 )
- {
- this.recalculatedValueForCellFormula( wb, (sheetId - 1) );
- }
- else
- {
- for ( int sheet = 0; sheet < WORKBOOK.getNumberOfSheets(); sheet++ )
- {
- this.recalculatedValueForCellFormula( wb, sheet );
- }
- }
+ HSSFWorkbook hssfwb = new HSSFWorkbook( fis );
if ( bWriteDescription )
{
@@ -251,13 +241,13 @@
if ( sheetId > 0 )
{
- writeBySheetNo( wb, (sheetId - 1), bDetailed );
+ writeBySheetNo( hssfwb, (sheetId - 1), bDetailed );
}
else
{
for ( int sheet = 0; sheet < WORKBOOK.getNumberOfSheets(); sheet++ )
{
- writeBySheetNo( wb, sheet, bDetailed );
+ writeBySheetNo( hssfwb, sheet, bDetailed );
}
}
@@ -280,6 +270,11 @@
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++ )
{
@@ -294,22 +289,50 @@
// information
if ( (cell[j].getType() != CellType.EMPTY) || (cell[j].getCellFormat() != null) )
{
+ bFormula = false;
+
+ CellReference cellReference = new 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 = "";
+
+ switch ( evaluator.evaluateInCell( cellRef ).getCellType() )
+// switch ( evaluator.evaluateFormulaCell( cellRef ) )
+ {
+ case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC:
+
+ recalculatedValue = String.valueOf( cellRef.getNumericCellValue() );
+
+ break;
+
+ // CELL_TYPE_FORMULA will never occur
+ case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_FORMULA:
+ System.out.println( "Place of cell :: [" + cellRef.getRowIndex() + "]["
+ + cellRef.getColumnIndex() + "]" );
+ break;
+ }
+ }
// end of checking the cell formula
STRUCTURE_DATA_RESPONSE.append( " " );
STRUCTURE_DATA_RESPONSE.append( PRINT_END_LINE );
STRUCTURE_DATA_RESPONSE.append( " " );
- if ( cell[j].getContents().equals( NUMBER_OF_ZERO ) )
+ if ( bFormula )
{
-
- STRUCTURE_DATA_RESPONSE.append( "" );
+ STRUCTURE_DATA_RESPONSE.append( "" );
}
else
{
- STRUCTURE_DATA_RESPONSE.append( "" );
+ STRUCTURE_DATA_RESPONSE.append( "" );
}
- // }
STRUCTURE_DATA_RESPONSE.append( "" );
STRUCTURE_DATA_RESPONSE.append( PRINT_END_LINE );
@@ -336,7 +359,7 @@
if ( format != null )
{
STRUCTURE_DATA_RESPONSE.append( " 0 && column > 0 )
{
HSSFRow rowPOI = sheet.getRow( row - 1 );
-
+
if ( rowPOI == null )
{
rowPOI = sheet.createRow( row - 1 );
@@ -147,14 +147,7 @@
}
else if ( type.equalsIgnoreCase( ExcelUtils.NUMBER ) )
{
- if ( Double.parseDouble( value ) != 0 )
- {
- cellPOI.setCellValue( new HSSFRichTextString( value ) );
- }
- else
- {
- cellPOI.setCellValue( new HSSFRichTextString( ExcelUtils.NUMBER_OF_ZERO ) );
- }
+ cellPOI.setCellValue( Double.parseDouble( value ) );
}
}
}
=== added file 'dhis-2/dhis-web/dhis-web-excel-reporting/src/main/java/org/hisp/dhis/reportexcel/utils/StringUtils.java'
--- dhis-2/dhis-web/dhis-web-excel-reporting/src/main/java/org/hisp/dhis/reportexcel/utils/StringUtils.java 1970-01-01 00:00:00 +0000
+++ dhis-2/dhis-web/dhis-web-excel-reporting/src/main/java/org/hisp/dhis/reportexcel/utils/StringUtils.java 2009-11-16 04:13:04 +0000
@@ -0,0 +1,129 @@
+package org.hisp.dhis.reportexcel.utils;
+
+/*
+ * Copyright (c) 2004-2007, 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$
+ * @since 2009-11-14
+ */
+public class StringUtils
+{
+ private static final String DOT = ".";
+
+ private static final String MIDDLE_LINE = "-";
+
+ public static final String NUMBER_OF_ZERO = "0";
+
+ /* ---------------------------------------------------------------------- */
+ /* */
+ /* ---------------------------------------------------------------------- */
+
+ public static String convertAlignmentString( String s )
+ {
+ if ( s.equalsIgnoreCase( "centre" ) )
+ {
+ return "center";
+ }
+ else
+ {
+ return s;
+ }
+ }
+
+ /* ---------------------------------------------------------------------- */
+ /* */
+ /* ---------------------------------------------------------------------- */
+
+ public static String checkingNumberDecimal( String input )
+ {
+ try
+ {
+ String s1 = "";
+ String s2 = "";
+
+ if ( Double.parseDouble( input ) >= 0.0d )
+ {
+ if ( input.contains( "." ) )
+ {
+ s1 = input.split( "\\." )[0];
+ s2 = input.split( "\\." )[1];
+
+ if ( (new Double( s1 ) == 0.0d) && (new Double( s2 ) != 0.0d) )
+ {
+ input = (NUMBER_OF_ZERO + DOT).concat( splitZeroAtEndOfNumberic( s2 ) );
+ }
+ else if ( (new Double( s1 ) != 0.0d) && (new Double( s2 ) == 0.0d) )
+ {
+ input = String.valueOf( new Long( s1 ) );
+ }
+ else
+ {
+ input = String.valueOf( new Long( s1 ) ).concat( DOT + splitZeroAtEndOfNumberic( s2 ) );
+ }
+ }
+
+ if ( new Double( input ) == 0.0d )
+ {
+ input = MIDDLE_LINE;
+ }
+ }
+
+ return input;
+ }
+ catch ( NumberFormatException nfe )
+ {
+ return input;
+ }
+ }
+
+ /* ---------------------------------------------------------------------- */
+ /* */
+ /* ---------------------------------------------------------------------- */
+
+ public static String splitZeroAtEndOfNumberic( String number )
+ {
+ int counterZero = 0;
+ char[] ch = number.toCharArray();
+
+ for ( int i = (ch.length - 1); i >= 0; i-- )
+ {
+ if ( ch[i] == '0' )
+ {
+ counterZero++;
+ }
+ else
+ {
+ number = number.substring( 0, ch.length - counterZero );
+ break;
+ }
+ }
+
+ return number;
+ }
+}