poi-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From n...@apache.org
Subject svn commit: r639231 - in /poi/trunk/src: scratchpad/src/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.java scratchpad/testcases/org/apache/poi/hssf/usermodel/TestFormulaEvaluatorBugs.java testcases/org/apache/poi/hssf/data/44636.xls
Date Thu, 20 Mar 2008 10:07:00 GMT
Author: nick
Date: Thu Mar 20 03:06:59 2008
New Revision: 639231

URL: http://svn.apache.org/viewvc?rev=639231&view=rev
Log:
Test relating to bug #44636

Added:
    poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/usermodel/TestFormulaEvaluatorBugs.java
  (with props)
    poi/trunk/src/testcases/org/apache/poi/hssf/data/44636.xls   (with props)
Modified:
    poi/trunk/src/scratchpad/src/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.java

Modified: poi/trunk/src/scratchpad/src/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/scratchpad/src/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.java?rev=639231&r1=639230&r2=639231&view=diff
==============================================================================
--- poi/trunk/src/scratchpad/src/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.java (original)
+++ poi/trunk/src/scratchpad/src/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.java Thu
Mar 20 03:06:59 2008
@@ -19,6 +19,7 @@
 
 import java.lang.reflect.Constructor;
 import java.util.HashMap;
+import java.util.Iterator;
 import java.util.Map;
 import java.util.Stack;
 
@@ -251,6 +252,35 @@
         }
         return cell;
     }
+    
+    /**
+     * Loops over all cells in all sheets of the supplied
+     *  workbook.
+     * For cells that contain formulas, their formulas are
+     *  evaluated, and the results are saved. These cells
+     *  remain as formula cells.
+     * For cells that do not contain formulas, no changes
+     *  are made.
+     * This is a helpful wrapper around looping over all 
+     *  cells, and calling evaluateFormulaCell on each one.
+     */
+	public static void evaluateAllFormulaCells(HSSFWorkbook wb) {
+		for(int i=0; i<wb.getNumberOfSheets(); i++) {
+			HSSFSheet sheet = wb.getSheetAt(i);
+			HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(sheet, wb);
+
+			for (Iterator rit = sheet.rowIterator(); rit.hasNext();) {
+				HSSFRow r = (HSSFRow)rit.next();
+				evaluator.setCurrentRow(r);
+
+				for (Iterator cit = r.cellIterator(); cit.hasNext();) {
+					HSSFCell c = (HSSFCell)cit.next();
+					if (c.getCellType() == HSSFCell.CELL_TYPE_FORMULA)
+						evaluator.evaluateFormulaCell(c);
+				}
+			}
+		}
+	}
         
     
     /**

Added: poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/usermodel/TestFormulaEvaluatorBugs.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/usermodel/TestFormulaEvaluatorBugs.java?rev=639231&view=auto
==============================================================================
--- poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/usermodel/TestFormulaEvaluatorBugs.java
(added)
+++ poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/usermodel/TestFormulaEvaluatorBugs.java
Thu Mar 20 03:06:59 2008
@@ -0,0 +1,88 @@
+/* ====================================================================
+   Licensed to the Apache Software Foundation (ASF) under one or more
+   contributor license agreements.  See the NOTICE file distributed with
+   this work for additional information regarding copyright ownership.
+   The ASF licenses this file to You under the Apache License, Version 2.0
+   (the "License"); you may not use this file except in compliance with
+   the License.  You may obtain a copy of the License at
+
+       http://www.apache.org/licenses/LICENSE-2.0
+
+   Unless required by applicable law or agreed to in writing, software
+   distributed under the License is distributed on an "AS IS" BASIS,
+   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+   See the License for the specific language governing permissions and
+   limitations under the License.
+==================================================================== */
+package org.apache.poi.hssf.usermodel;
+
+import java.io.File;
+import java.io.FileInputStream;
+import java.io.FileOutputStream;
+import java.util.Iterator;
+
+import junit.framework.TestCase;
+
+public final class TestFormulaEvaluatorBugs extends TestCase {
+	private String dirName;
+	private String tmpDirName;
+
+	protected void setUp() throws Exception {
+		super.setUp();
+		dirName = System.getProperty("HSSF.testdata.path");
+		tmpDirName = System.getProperty("java.io.tmpdir");
+	}
+	
+	/**
+	 * An odd problem with evaluateFormulaCell giving the
+	 *  right values when file is opened, but changes
+	 *  to the source data in some versions of excel 
+	 *  doesn't cause them to be updated. However, other
+	 *  versions of excel, and gnumeric, work just fine
+	 * WARNING - tedious bug where you actually have to
+	 *  open up excel
+	 */
+	public void test44636() throws Exception {
+		// Open the existing file, tweak one value and
+		//  re-calculate
+		FileInputStream in = new FileInputStream(new File(dirName,"44636.xls"));
+		HSSFWorkbook wb = new HSSFWorkbook(in);
+		HSSFSheet sheet = wb.getSheetAt (0);
+		HSSFRow row = sheet.getRow (0);
+		
+		row.getCell((short)0).setCellValue(4.2);
+		row.getCell((short)2).setCellValue(25);
+		
+		HSSFFormulaEvaluator.evaluateAllFormulaCells(wb);
+		assertEquals(4.2*25, row.getCell((short)3).getNumericCellValue());
+		
+		// Save
+		File existing = new File(tmpDirName,"44636-existing.xls");
+		FileOutputStream out = new FileOutputStream(existing);
+		wb.write(out);
+		out.close();
+		System.err.println("Existing file for bug #44636 written to " + existing.toString());
+		
+		
+		// Now, do a new file from scratch
+		wb = new HSSFWorkbook();
+		sheet = wb.createSheet();
+		
+		row = sheet.createRow(0);
+		row.createCell((short)0).setCellValue(1.2);
+		row.createCell((short)1).setCellValue(4.2);
+		
+		row = sheet.createRow(1);
+		row.createCell((short)0).setCellFormula("SUM(A1:B1)");
+		
+		HSSFFormulaEvaluator.evaluateAllFormulaCells(wb);
+		assertEquals(5.4, row.getCell((short)0).getNumericCellValue());
+				
+		// Save
+		File scratch = new File(tmpDirName,"44636-scratch.xls");
+		out = new FileOutputStream(scratch);
+		wb.write(out);
+		out.close();
+		System.err.println("New file for bug #44636 written to " + scratch.toString());
+	}
+}

Propchange: poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/usermodel/TestFormulaEvaluatorBugs.java
------------------------------------------------------------------------------
    svn:eol-style = native

Added: poi/trunk/src/testcases/org/apache/poi/hssf/data/44636.xls
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/data/44636.xls?rev=639231&view=auto
==============================================================================
Binary file - no diff available.

Propchange: poi/trunk/src/testcases/org/apache/poi/hssf/data/44636.xls
------------------------------------------------------------------------------
    svn:mime-type = application/octet-stream



---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org


Mime
View raw message