Make a start on being able to process formulas in the eventusermodel code

git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@671322 13f79535-47bb-0310-9956-ffa450edef68

3 files changed, 335 insertions, 0 deletions

diff --git a/src/java/org/apache/poi/hssf/eventusermodel/EventWorkbookBuilder.java b/src/java/org/apache/poi/hssf/eventusermodel/EventWorkbookBuilder.java
new file mode 100644
index 0000000000..6040bb5801
--- /dev/null
+++ b/src/java/org/apache/poi/hssf/eventusermodel/EventWorkbookBuilder.java
@@ -0,0 +1,195 @@
+/* ====================================================================
+   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.eventusermodel;
+
+import java.util.ArrayList;
+import java.util.List;
+
+import org.apache.poi.hssf.model.FormulaParser;
+import org.apache.poi.hssf.model.Workbook;
+import org.apache.poi.hssf.record.BoundSheetRecord;
+import org.apache.poi.hssf.record.EOFRecord;
+import org.apache.poi.hssf.record.ExternSheetRecord;
+import org.apache.poi.hssf.record.Record;
+import org.apache.poi.hssf.record.SSTRecord;
+import org.apache.poi.hssf.usermodel.HSSFWorkbook;
+
+/**
+ * When working with the EventUserModel, if you want to 
+ *  process formulas, you need an instance of
+ *  {@link Workbook} to pass to a {@link HSSFWorkbook},
+ *  to finally give to {@link FormulaParser}, 
+ *  and this will build you stub ones.
+ * Since you're working with the EventUserModel, you
+ *  wouldn't want to get a full {@link Workbook} and
+ *  {@link HSSFWorkbook}, as they would eat too much memory.
+ *  Instead, you should collect a few key records as they
+ *  go past, then call this once you have them to build a
+ *  stub {@link Workbook}, and from that a stub
+ *  {@link HSSFWorkbook}, to use with the {@link FormulaParser}.
+ * 
+ * The records you should collect are:
+ *  * {@link ExternSheetRecord}
+ *  * {@link BoundSheetRecord}
+ * You should probably also collect {@link SSTRecord}, 
+ *  but it's not required to pass this in.
+ *  
+ * To help, this class includes a HSSFListener wrapper
+ *  that will do the collecting for you.
+ */
+public class EventWorkbookBuilder {
+	/**
+	 * Wraps up your stub {@link Workbook} as a stub
+	 *  {@link HSSFWorkbook}, ready for passing to
+	 *  {@link FormulaParser}
+	 * @param workbook A stub {@link Workbook}
+	 */
+	public static HSSFWorkbook createStubHSSFWorkbook(Workbook workbook) {
+		return new StubHSSFWorkbook(workbook);
+	}
+	
+	/**
+	 * Creates a stub Workbook from the supplied records,
+	 *  suitable for use with the {@link FormulaParser}
+	 * @param externs The ExternSheetRecords in your file
+	 * @param bounds The BoundSheetRecords in your file
+	 * @param sst The SSTRecord in your file.
+	 * @return A stub Workbook suitable for use with {@link FormulaParser}
+	 */
+	public static Workbook createStubWorkbook(ExternSheetRecord[] externs,
+			BoundSheetRecord[] bounds, SSTRecord sst) {
+		List wbRecords = new ArrayList();
+		
+		// Core Workbook records go first
+		if(bounds != null) {
+			for(int i=0; i<bounds.length; i++) {
+				wbRecords.add(bounds[i]);
+			}
+		}
+		if(sst != null) {
+			wbRecords.add(sst);
+		}
+		
+		// Next we need an EoF record
+		wbRecords.add(new EOFRecord());
+		
+		// Now e can have the ExternSheetRecords
+		if(externs != null) {
+			for(int i=0; i<externs.length; i++) {
+				wbRecords.add(externs[i]);
+			}
+		}
+		
+		return Workbook.createWorkbook(wbRecords);
+	}
+	
+	/**
+	 * Creates a stub workbook from the supplied records,
+	 *  suitable for use with the {@link FormulaParser}
+	 * @param externs The ExternSheetRecords in your file
+	 * @param bounds The BoundSheetRecords in your file
+	 * @return A stub Workbook suitable for use with {@link FormulaParser}
+	 */
+	public static Workbook createStubWorkbook(ExternSheetRecord[] externs,
+			BoundSheetRecord[] bounds) {
+		return createStubWorkbook(externs, bounds, null);
+	}
+	
+	
+	/**
+	 * A wrapping HSSFListener which will collect 
+	 *  {@link BoundSheetRecord}s and {@link ExternSheetRecord}s as
+	 *  they go past, so you can create a Stub {@link Workbook} from
+	 *  them once required.
+	 */
+	public static class SheetRecordCollectingListener implements HSSFListener {
+		private HSSFListener childListener;
+		private List boundSheetRecords = new ArrayList();
+		private List externSheetRecords = new ArrayList();
+		private SSTRecord sstRecord = null;
+		
+		public SheetRecordCollectingListener(HSSFListener childListener) {
+			this.childListener = childListener;
+		}
+		
+		
+		public BoundSheetRecord[] getBoundSheetRecords() {
+			return (BoundSheetRecord[])boundSheetRecords.toArray(
+					new BoundSheetRecord[boundSheetRecords.size()]
+			);
+		}
+		public ExternSheetRecord[] getExternSheetRecords() {
+			return (ExternSheetRecord[])externSheetRecords.toArray(
+					new ExternSheetRecord[externSheetRecords.size()]
+			);
+		}
+		public SSTRecord getSSTRecord() {
+			return sstRecord;
+		}
+		
+		public HSSFWorkbook getStubHSSFWorkbook() {
+			return createStubHSSFWorkbook(
+					getStubWorkbook()
+			);
+		}
+		public Workbook getStubWorkbook() {
+			return createStubWorkbook(
+					getExternSheetRecords(), getBoundSheetRecords(), 
+					getSSTRecord()
+			);
+		}
+		
+		
+		/**
+		 * Process this record ourselves, and then
+		 *  pass it on to our child listener
+		 */
+		public void processRecord(Record record) {
+			// Handle it ourselves
+			processRecordInternally(record);
+			
+			// Now pass on to our child
+			childListener.processRecord(record);
+		}
+		
+		/**
+		 * Process the record ourselves, but do not
+		 *  pass it on to the child Listener.
+		 */
+		public void processRecordInternally(Record record) {
+			if(record instanceof BoundSheetRecord) {
+				boundSheetRecords.add(record);
+			}
+			else if(record instanceof ExternSheetRecord) {
+				externSheetRecords.add(record);
+			}
+			else if(record instanceof SSTRecord) {
+				sstRecord = (SSTRecord)record;
+			}
+		}
+	}
+	
+	/**
+	 * Let us at the {@link Workbook} constructor on
+	 *  {@link HSSFWorkbook}
+	 */
+	private static class StubHSSFWorkbook extends HSSFWorkbook {
+		private StubHSSFWorkbook(Workbook wb) {
+			super(wb);
+		}
+	}
+}
\ No newline at end of file
diff --git a/src/testcases/org/apache/poi/hssf/data/3dFormulas.xls b/src/testcases/org/apache/poi/hssf/data/3dFormulas.xls
new file mode 100644
index 0000000000..82519ed839
--- /dev/null
+++ b/src/testcases/org/apache/poi/hssf/data/3dFormulas.xls
diff --git a/src/testcases/org/apache/poi/hssf/eventusermodel/TestEventWorkbookBuilder.java b/src/testcases/org/apache/poi/hssf/eventusermodel/TestEventWorkbookBuilder.java
new file mode 100644
index 0000000000..7ea1e6a8a3
--- /dev/null
+++ b/src/testcases/org/apache/poi/hssf/eventusermodel/TestEventWorkbookBuilder.java
@@ -0,0 +1,140 @@
+/* ====================================================================
+   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.eventusermodel;
+import java.io.IOException;
+import java.io.InputStream;
+import java.util.ArrayList;
+import java.util.List;
+
+import junit.framework.TestCase;
+
+import org.apache.poi.hssf.HSSFTestDataSamples;
+import org.apache.poi.hssf.eventusermodel.EventWorkbookBuilder.SheetRecordCollectingListener;
+import org.apache.poi.hssf.model.FormulaParser;
+import org.apache.poi.hssf.model.Workbook;
+import org.apache.poi.hssf.record.FormulaRecord;
+import org.apache.poi.hssf.record.Record;
+import org.apache.poi.hssf.usermodel.HSSFWorkbook;
+import org.apache.poi.poifs.filesystem.POIFSFileSystem;
+/**
+ * Tests for {@link EventWorkbookBuilder}
+ */
+public final class TestEventWorkbookBuilder extends TestCase {
+	private MockHSSFListener mockListen;
+	private SheetRecordCollectingListener listener;
+	
+	public void setUp() {
+		HSSFRequest req = new HSSFRequest();
+		mockListen = new MockHSSFListener();
+		listener = new SheetRecordCollectingListener(mockListen);
+		req.addListenerForAllRecords(listener);
+		
+		HSSFEventFactory factory = new HSSFEventFactory();
+		try {
+			InputStream is = HSSFTestDataSamples.openSampleFileStream("3dFormulas.xls");
+			POIFSFileSystem fs = new POIFSFileSystem(is);
+			factory.processWorkbookEvents(req, fs);
+		} catch (IOException e) {
+			throw new RuntimeException(e);
+		}
+	} 
+	
+	public void testBasics() throws Exception {
+		assertNotNull(listener.getSSTRecord());
+		assertNotNull(listener.getBoundSheetRecords());
+		assertNotNull(listener.getExternSheetRecords());
+	}
+	
+	public void testGetStubWorkbook() throws Exception {
+		assertNotNull(listener.getStubWorkbook());
+	}
+	
+	public void testContents() throws Exception {
+		assertEquals(2, listener.getSSTRecord().getNumStrings());
+		assertEquals(3, listener.getBoundSheetRecords().length);
+		assertEquals(1, listener.getExternSheetRecords().length);
+	}
+	
+	public void testFormulas() throws Exception {
+		FormulaRecord fr;
+		
+		// Check our formula records
+		assertEquals(6, mockListen._frecs.size());
+		
+		Workbook stubWB = listener.getStubWorkbook();
+		assertNotNull(stubWB);
+		HSSFWorkbook stubHSSF = listener.getStubHSSFWorkbook();
+		assertNotNull(stubHSSF);
+		
+		// Check these stubs have the right stuff on them
+		assertEquals("Sheet1", stubWB.getSheetName(0));
+		assertEquals("S2", stubWB.getSheetName(1));
+		assertEquals("Sh3", stubWB.getSheetName(2));
+		
+		// Check we can get the formula without breaking
+		for(int i=0; i<mockListen._frecs.size(); i++) {
+			fr = (FormulaRecord)mockListen._frecs.get(i);
+			FormulaParser.toFormulaString(stubHSSF, fr.getParsedExpression());
+		}
+		
+		// Now check we get the right formula back for
+		//  a few sample ones
+		
+		// Sheet 1 A2 is on same sheet
+		fr = (FormulaRecord)mockListen._frecs.get(0);
+		assertEquals(1, fr.getRow());
+		assertEquals(0, fr.getColumn());
+		// TODO - why not Sheet1!A1 ?
+		assertEquals("A1", FormulaParser.toFormulaString(stubHSSF, fr.getParsedExpression()));
+		
+		// Sheet 1 A5 is to another sheet
+		fr = (FormulaRecord)mockListen._frecs.get(3);
+		assertEquals(4, fr.getRow());
+		assertEquals(0, fr.getColumn());
+		// TODO - why not 'S2'!A1 ?
+		assertEquals("A1", FormulaParser.toFormulaString(stubHSSF, fr.getParsedExpression()));
+		
+		// Sheet 1 A7 is to another sheet, range
+		fr = (FormulaRecord)mockListen._frecs.get(5);
+		assertEquals(6, fr.getRow());
+		assertEquals(0, fr.getColumn());
+		// TODO - why not SUM('Sh3'!A1:A4) ?
+		assertEquals("SUM(A1:A4)", FormulaParser.toFormulaString(stubHSSF, fr.getParsedExpression()));
+		
+		
+		// Now, load via Usermodel and re-check
+		InputStream is = HSSFTestDataSamples.openSampleFileStream("3dFormulas.xls");
+		POIFSFileSystem fs = new POIFSFileSystem(is);
+		HSSFWorkbook wb = new HSSFWorkbook(fs);
+		assertEquals("Sheet1!A1", wb.getSheetAt(0).getRow(1).getCell(0).getCellFormula());
+		assertEquals("SUM('Sh3'!A1:A4)", wb.getSheetAt(0).getRow(6).getCell(0).getCellFormula());
+	}
+	
+	private static final class MockHSSFListener implements HSSFListener {
+		public MockHSSFListener() {}
+		private final List _records = new ArrayList();
+		private final List _frecs = new ArrayList();
+
+		public void processRecord(Record record) {
+			_records.add(record);
+			if(record instanceof FormulaRecord) {
+				_frecs.add(record);
+			}
+		}
+	}
+}
\ No newline at end of file