dhis2-devs team mailing list archive
-
dhis2-devs team
-
Mailing list archive
-
Message #03089
[Branch ~dhis2-devs-core/dhis2/trunk] Rev 1029: Improved Preview Report Excel func.
------------------------------------------------------------
revno: 1029
committer: hieu <hieu.hispvietnam@xxxxxxxxx>
branch nick: trunk
timestamp: Mon 2009-11-16 11:13:04 +0700
message:
Improved Preview Report Excel func.
added:
dhis-2/dhis-web/dhis-web-excel-reporting/src/main/java/org/hisp/dhis/reportexcel/utils/StringUtils.java
modified:
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/manager/DefaultInitializePOIStylesManager.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/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( " <col no=\"" + j + "\">" );
STRUCTURE_DATA_RESPONSE.append( PRINT_END_LINE );
STRUCTURE_DATA_RESPONSE.append( " <data>" );
- if ( cell[j].getContents().equals( NUMBER_OF_ZERO ) )
+ if ( bFormula )
{
-
- STRUCTURE_DATA_RESPONSE.append( "<![CDATA[-]]>" );
+ STRUCTURE_DATA_RESPONSE.append( "<![CDATA["
+ + StringUtils.checkingNumberDecimal( recalculatedValue ) + "]]>" );
}
else
{
- STRUCTURE_DATA_RESPONSE.append( "<![CDATA[" + cell[j].getContents() + "]]>" );
+ STRUCTURE_DATA_RESPONSE.append( "<![CDATA["
+ + StringUtils.checkingNumberDecimal( cell[j].getContents() ) + "]]>" );
}
- // }
STRUCTURE_DATA_RESPONSE.append( "</data>" );
STRUCTURE_DATA_RESPONSE.append( PRINT_END_LINE );
@@ -336,7 +359,7 @@
if ( format != null )
{
STRUCTURE_DATA_RESPONSE.append( " <format align=\""
- + this.convertAlignmentString( format.getAlignment().getDescription() ) + "\"" );
+ + StringUtils.convertAlignmentString( format.getAlignment().getDescription() ) + "\"" );
if ( bDetailed )
{
@@ -446,61 +469,4 @@
}
}
- // -------------------------------------------------------------------------
- // Supportive methods
- // -------------------------------------------------------------------------
-
- private String convertAlignmentString( String s )
- {
- if ( s.equalsIgnoreCase( "centre" ) )
- {
- return "center";
- }
- else
- {
- return s;
- }
- }
-
- private void recalculatedValueForCellFormula( org.apache.poi.ss.usermodel.Workbook wb, int sheetNo )
- throws Exception
- {
- org.apache.poi.ss.usermodel.Sheet sheet = wb.getSheetAt( sheetNo );
- org.apache.poi.ss.usermodel.FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
-
- String recalculatedValue = "";
-
- for ( org.apache.poi.ss.usermodel.Row rowPOI : sheet )
- {
- for ( org.apache.poi.ss.usermodel.Cell cellPOI : rowPOI )
- {
-
- CellReference cellReference = new CellReference( cellPOI.getRowIndex(), cellPOI.getColumnIndex() );
- 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) )
- {
- switch ( evaluator.evaluateInCell( cellRef ).getCellType() )
- {
- case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC:
-
- recalculatedValue = String.valueOf( cellRef.getNumericCellValue() );
-
- if ( new Double( recalculatedValue.split( "\\." )[1] ) == 0.0d )
- {
- recalculatedValue = recalculatedValue.split( "\\." )[0];
- }
- 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;
- }
- }
- }
- }
- }
-
}
=== modified file 'dhis-2/dhis-web/dhis-web-excel-reporting/src/main/java/org/hisp/dhis/reportexcel/preview/manager/DefaultInitializePOIStylesManager.java'
--- dhis-2/dhis-web/dhis-web-excel-reporting/src/main/java/org/hisp/dhis/reportexcel/preview/manager/DefaultInitializePOIStylesManager.java 2009-10-07 10:20:08 +0000
+++ dhis-2/dhis-web/dhis-web-excel-reporting/src/main/java/org/hisp/dhis/reportexcel/preview/manager/DefaultInitializePOIStylesManager.java 2009-11-16 04:13:04 +0000
@@ -53,11 +53,11 @@
private static final String STYLE_DEFAULT_FONT_NAME = "Tahoma";
- private static final short STYLE_DEFAULT_FONT_HEIGHT = 8;
-
- private static final short STYLE_DEFAULT_FONT_WEIGHT = HSSFFont.BOLDWEIGHT_NORMAL;
-
- private static final short STYLE_DEFAULT_FONT_COLOR = new HSSFColor.WHITE().getIndex();
+ private static final short STYLE_DEFAULT_FONT_HEIGHT = 10;
+
+ private static final short STYLE_DEFAULT_FONT_WEIGHT = HSSFFont.BOLDWEIGHT_BOLD;
+
+ private static final short STYLE_DEFAULT_FONT_COLOR = new HSSFColor.DARK_YELLOW().getIndex();
private static final short STYLE_DEFAULT_BACK_FORE_GROUND_COLOR = HSSFColor.WHITE.index;
=== 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-10-07 05:38:43 +0000
+++ dhis-2/dhis-web/dhis-web-excel-reporting/src/main/java/org/hisp/dhis/reportexcel/utils/ExcelUtils.java 2009-11-16 04:13:04 +0000
@@ -132,7 +132,7 @@
if ( row > 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;
+ }
+}
