From 65bc36fb364b9047814225a104cf79e6886a0645 Mon Sep 17 00:00:00 2001 From: Nick Burch Date: Sun, 13 Apr 2008 15:09:42 +0000 Subject: [PATCH] Finish off eventusermodel based Excel Extractor, and update the xls to csv converter (moved to correct place) based on discoveries for the text extractor git-svn-id: https://svn.eu.apache.org/repos/asf/poi/trunk@647576 13f79535-47bb-0310-9956-ffa450edef68 --- .../hssf/eventusermodel/examples/XLS2CSVmra.java | 40 ++++++++++++++++------ .../hssf/extractor/EventBasedExcelExtractor.java | 18 +++++++++- .../poi/hssf/extractor/TestExcelExtractor.java | 4 +-- 3 files changed, 48 insertions(+), 14 deletions(-) rename src/{scratchpad => }/examples/src/org/apache/poi/hssf/eventusermodel/examples/XLS2CSVmra.java (90%) diff --git a/src/scratchpad/examples/src/org/apache/poi/hssf/eventusermodel/examples/XLS2CSVmra.java b/src/examples/src/org/apache/poi/hssf/eventusermodel/examples/XLS2CSVmra.java similarity index 90% rename from src/scratchpad/examples/src/org/apache/poi/hssf/eventusermodel/examples/XLS2CSVmra.java rename to src/examples/src/org/apache/poi/hssf/eventusermodel/examples/XLS2CSVmra.java index 1c53f1e..9bebd3a 100644 --- a/src/scratchpad/examples/src/org/apache/poi/hssf/eventusermodel/examples/XLS2CSVmra.java +++ b/src/examples/src/org/apache/poi/hssf/eventusermodel/examples/XLS2CSVmra.java @@ -23,11 +23,7 @@ import java.io.PrintStream; import java.text.DateFormat; import java.text.DecimalFormat; import java.text.SimpleDateFormat; -import java.util.ArrayList; import java.util.Date; -import java.util.Hashtable; -import java.util.List; -import java.util.Map; import org.apache.poi.hssf.eventusermodel.FormatTrackingHSSFListener; import org.apache.poi.hssf.eventusermodel.HSSFEventFactory; @@ -36,11 +32,10 @@ import org.apache.poi.hssf.eventusermodel.HSSFRequest; import org.apache.poi.hssf.eventusermodel.MissingRecordAwareHSSFListener; import org.apache.poi.hssf.eventusermodel.dummyrecord.LastCellOfRowDummyRecord; import org.apache.poi.hssf.eventusermodel.dummyrecord.MissingCellDummyRecord; +import org.apache.poi.hssf.model.FormulaParser; import org.apache.poi.hssf.record.BlankRecord; import org.apache.poi.hssf.record.BoolErrRecord; import org.apache.poi.hssf.record.CellValueRecordInterface; -import org.apache.poi.hssf.record.ExtendedFormatRecord; -import org.apache.poi.hssf.record.FormatRecord; import org.apache.poi.hssf.record.FormulaRecord; import org.apache.poi.hssf.record.LabelRecord; import org.apache.poi.hssf.record.LabelSSTRecord; @@ -49,7 +44,7 @@ import org.apache.poi.hssf.record.NumberRecord; import org.apache.poi.hssf.record.RKRecord; import org.apache.poi.hssf.record.Record; import org.apache.poi.hssf.record.SSTRecord; -import org.apache.poi.hssf.usermodel.HSSFDataFormat; +import org.apache.poi.hssf.record.StringRecord; import org.apache.poi.hssf.usermodel.HSSFDateUtil; import org.apache.poi.poifs.filesystem.POIFSFileSystem; @@ -72,6 +67,11 @@ public class XLS2CSVmra implements HSSFListener { // Records we pick up as we process private SSTRecord sstRecord; private FormatTrackingHSSFListener formatListener; + + // For handling formulas with string results + private int nextRow; + private int nextColumn; + private boolean outputNextStringRecord; /** * Creates a new XLS -> CSV converter @@ -142,6 +142,7 @@ public class XLS2CSVmra implements HSSFListener { thisColumn = berec.getColumn(); thisStr = ""; break; + case FormulaRecord.sid: FormulaRecord frec = (FormulaRecord) record; @@ -149,12 +150,31 @@ public class XLS2CSVmra implements HSSFListener { thisColumn = frec.getColumn(); if(outputFormulaValues) { - thisStr = formatNumberDateCell(frec, frec.getValue()); + if(Double.isNaN( frec.getValue() )) { + // Formula result is a string + // This is stored in the next record + outputNextStringRecord = true; + nextRow = frec.getRow(); + nextColumn = frec.getColumn(); + } else { + thisStr = formatNumberDateCell(frec, frec.getValue()); + } } else { - // TODO: Output the formula string - thisStr = '"' + frec.toString() + '"'; + thisStr = '"' + + FormulaParser.toFormulaString(null, frec.getParsedExpression()) + '"'; } break; + case StringRecord.sid: + if(outputNextStringRecord) { + // String for formula + StringRecord srec = (StringRecord)record; + thisStr = srec.getString(); + thisRow = nextRow; + thisColumn = nextColumn; + outputNextStringRecord = false; + } + break; + case LabelRecord.sid: LabelRecord lrec = (LabelRecord) record; diff --git a/src/java/org/apache/poi/hssf/extractor/EventBasedExcelExtractor.java b/src/java/org/apache/poi/hssf/extractor/EventBasedExcelExtractor.java index 718c025..8f3eebb 100644 --- a/src/java/org/apache/poi/hssf/extractor/EventBasedExcelExtractor.java +++ b/src/java/org/apache/poi/hssf/extractor/EventBasedExcelExtractor.java @@ -42,6 +42,7 @@ import org.apache.poi.hssf.record.NoteRecord; import org.apache.poi.hssf.record.NumberRecord; import org.apache.poi.hssf.record.Record; import org.apache.poi.hssf.record.SSTRecord; +import org.apache.poi.hssf.record.StringRecord; import org.apache.poi.hssf.usermodel.HSSFDateUtil; import org.apache.poi.poifs.filesystem.POIFSFileSystem; @@ -142,6 +143,9 @@ public class EventBasedExcelExtractor extends POIOLE2TextExtractor { private int sheetNum = -1; private int rowNum; + private boolean outputNextStringValue = false; + private int nextRow = -1; + public void processRecord(Record record) { String thisText = null; int thisRow = -1; @@ -175,12 +179,24 @@ public class EventBasedExcelExtractor extends POIOLE2TextExtractor { thisText = FormulaParser.toFormulaString(null, frec.getParsedExpression()); } else { if(Double.isNaN( frec.getValue() )) { - thisText = "(todo - string formulas)"; + // Formula result is a string + // This is stored in the next record + outputNextStringValue = true; + nextRow = frec.getRow(); } else { thisText = formatNumberDateCell(frec, frec.getValue()); } } break; + case StringRecord.sid: + if(outputNextStringValue) { + // String for formula + StringRecord srec = (StringRecord)record; + thisText = srec.getString(); + thisRow = nextRow; + outputNextStringValue = false; + } + break; case LabelRecord.sid: LabelRecord lrec = (LabelRecord) record; thisRow = lrec.getRow(); diff --git a/src/testcases/org/apache/poi/hssf/extractor/TestExcelExtractor.java b/src/testcases/org/apache/poi/hssf/extractor/TestExcelExtractor.java index b93bc65..63d67ee 100644 --- a/src/testcases/org/apache/poi/hssf/extractor/TestExcelExtractor.java +++ b/src/testcases/org/apache/poi/hssf/extractor/TestExcelExtractor.java @@ -136,9 +136,7 @@ public final class TestExcelExtractor extends TestCase { extractor.setIncludeSheetNames(true); String text = extractor.getText(); - // TODO - assertEquals("Sheet1\nreplaceme\nreplaceme\n(todo - string formulas)\nSheet2\nSheet3\n", text); -// assertEquals("Sheet1\nreplaceme\nreplaceme\nreplacemereplaceme\nSheet2\nSheet3\n", text); + assertEquals("Sheet1\nreplaceme\nreplaceme\nreplacemereplaceme\nSheet2\nSheet3\n", text); extractor.setIncludeSheetNames(false); extractor.setFormulasNotResults(true); -- 2.11.4.GIT