From 2b4a6011f17182709390c42cdc4e41aa983b43bc Mon Sep 17 00:00:00 2001 From: Nick Burch Date: Wed, 9 Apr 2008 15:36:39 +0000 Subject: [PATCH] Implement a proxy HSSFListener which tracks the format records, and lets you lookup the format string for a given cell. Convert the xls to csv example to use it git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@646405 13f79535-47bb-0310-9956-ffa450edef68 --- .../FormatTrackingHSSFListener.java | 117 ++++++++++++++++++ .../eventusermodel/examples/XLS2CSVmra.java | 45 +++---- .../TestFormatTrackingHSSFListener.java | 65 ++++++++++ 3 files changed, 197 insertions(+), 30 deletions(-) create mode 100644 src/java/org/apache/poi/hssf/eventusermodel/FormatTrackingHSSFListener.java create mode 100644 src/testcases/org/apache/poi/hssf/eventusermodel/TestFormatTrackingHSSFListener.java diff --git a/src/java/org/apache/poi/hssf/eventusermodel/FormatTrackingHSSFListener.java b/src/java/org/apache/poi/hssf/eventusermodel/FormatTrackingHSSFListener.java new file mode 100644 index 0000000000..b88143713d --- /dev/null +++ b/src/java/org/apache/poi/hssf/eventusermodel/FormatTrackingHSSFListener.java @@ -0,0 +1,117 @@ +/* ==================================================================== + 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.Hashtable; +import java.util.List; +import java.util.Map; + +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.Record; +import org.apache.poi.hssf.usermodel.HSSFDataFormat; + +/** + * A proxy HSSFListener that keeps track of the document + * formatting records, and provides an easy way to look + * up the format strings used by cells from their ids. + */ +public class FormatTrackingHSSFListener implements HSSFListener { + private HSSFListener childListener; + private Map customFormatRecords = new Hashtable(); + private List xfRecords = new ArrayList(); + + public FormatTrackingHSSFListener(HSSFListener childListener) { + this.childListener = childListener; + } + + /** + * 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. + * @param record + */ + public void processRecordInternally(Record record) { + if(record instanceof FormatRecord) { + FormatRecord fr = (FormatRecord) record; + customFormatRecords.put(new Integer(fr.getIndexCode()), fr); + } + if(record instanceof ExtendedFormatRecord) { + ExtendedFormatRecord xr = (ExtendedFormatRecord) record; + xfRecords.add(xr); + } + } + + /** + * Returns the format string, eg $##.##, for the + * given number format index. + */ + public String getFormatString(int formatIndex) { + String format = null; + if(formatIndex >= HSSFDataFormat.getNumberOfBuiltinBuiltinFormats()) { + FormatRecord tfr = (FormatRecord)customFormatRecords.get(new Integer(formatIndex)); + if(tfr == null) { + System.err.println("Requested format at index " + formatIndex + ", but it wasn't found"); + } else { + format = tfr.getFormatString(); + } + } else { + format = HSSFDataFormat.getBuiltinFormat((short)formatIndex); + } + return format; + } + + /** + * Returns the format string, eg $##.##, used + * by your cell + */ + public String getFormatString(CellValueRecordInterface cell) { + int formatIndex = getFormatIndex(cell); + if(formatIndex == -1) { + // Not found + return null; + } + return getFormatString(formatIndex); + } + + /** + * Returns the index of the format string, used by your cell, + * or -1 if none found + */ + public int getFormatIndex(CellValueRecordInterface cell) { + ExtendedFormatRecord xfr = (ExtendedFormatRecord) + xfRecords.get(cell.getXFIndex()); + if(xfr == null) { + System.err.println("Cell " + cell.getRow() + "," + cell.getColumn() + " uses XF with index " + cell.getXFIndex() + ", but we don't have that"); + return -1; + } + return xfr.getFormatIndex(); + } +} diff --git a/src/scratchpad/examples/src/org/apache/poi/hssf/eventusermodel/examples/XLS2CSVmra.java b/src/scratchpad/examples/src/org/apache/poi/hssf/eventusermodel/examples/XLS2CSVmra.java index 1c8d994bfe..1c53f1ecc2 100644 --- a/src/scratchpad/examples/src/org/apache/poi/hssf/eventusermodel/examples/XLS2CSVmra.java +++ b/src/scratchpad/examples/src/org/apache/poi/hssf/eventusermodel/examples/XLS2CSVmra.java @@ -29,6 +29,7 @@ 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; import org.apache.poi.hssf.eventusermodel.HSSFListener; import org.apache.poi.hssf.eventusermodel.HSSFRequest; @@ -70,8 +71,7 @@ public class XLS2CSVmra implements HSSFListener { // Records we pick up as we process private SSTRecord sstRecord; - private Map customFormatRecords = new Hashtable(); - private List xfRecords = new ArrayList(); + private FormatTrackingHSSFListener formatListener; /** * Creates a new XLS -> CSV converter @@ -104,9 +104,11 @@ public class XLS2CSVmra implements HSSFListener { */ public void process() throws IOException { MissingRecordAwareHSSFListener listener = new MissingRecordAwareHSSFListener(this); + formatListener = new FormatTrackingHSSFListener(listener); + HSSFEventFactory factory = new HSSFEventFactory(); HSSFRequest request = new HSSFRequest(); - request.addListenerForAllRecords(listener); + request.addListenerForAllRecords(formatListener); factory.processWorkbookEvents(request, fs); } @@ -125,14 +127,6 @@ public class XLS2CSVmra implements HSSFListener { case SSTRecord.sid: sstRecord = (SSTRecord) record; break; - case FormatRecord.sid: - FormatRecord fr = (FormatRecord) record; - customFormatRecords.put(new Integer(fr.getIndexCode()), fr); - break; - case ExtendedFormatRecord.sid: - ExtendedFormatRecord xr = (ExtendedFormatRecord) record; - xfRecords.add(xr); - break; case BlankRecord.sid: BlankRecord brec = (BlankRecord) record; @@ -259,41 +253,32 @@ public class XLS2CSVmra implements HSSFListener { */ private String formatNumberDateCell(CellValueRecordInterface cell, double value) { // Get the built in format, if there is one - ExtendedFormatRecord xfr = (ExtendedFormatRecord) - xfRecords.get(cell.getXFIndex()); - if(xfr == null) { - System.err.println("Cell " + cell.getRow() + "," + cell.getColumn() + " uses XF with index " + cell.getXFIndex() + ", but we don't have that"); + int formatIndex = formatListener.getFormatIndex(cell); + String formatString = formatListener.getFormatString(cell); + + if(formatString == null) { return Double.toString(value); } else { - int formatIndex = xfr.getFormatIndex(); - String format; - if(formatIndex >= HSSFDataFormat.getNumberOfBuiltinBuiltinFormats()) { - FormatRecord tfr = (FormatRecord)customFormatRecords.get(new Integer(formatIndex)); - format = tfr.getFormatString(); - } else { - format = HSSFDataFormat.getBuiltinFormat(xfr.getFormatIndex()); - } - // Is it a date? - if(HSSFDateUtil.isADateFormat(formatIndex,format) && + if(HSSFDateUtil.isADateFormat(formatIndex,formatString) && HSSFDateUtil.isValidExcelDate(value)) { // Java wants M not m for month - format = format.replace('m','M'); + formatString = formatString.replace('m','M'); // Change \- into -, if it's there - format = format.replaceAll("\\\\-","-"); + formatString = formatString.replaceAll("\\\\-","-"); // Format as a date Date d = HSSFDateUtil.getJavaDate(value, false); - DateFormat df = new SimpleDateFormat(format); + DateFormat df = new SimpleDateFormat(formatString); return df.format(d); } else { - if(format == "General") { + if(formatString == "General") { // Some sort of wierd default return Double.toString(value); } // Format as a number - DecimalFormat df = new DecimalFormat(format); + DecimalFormat df = new DecimalFormat(formatString); return df.format(value); } } diff --git a/src/testcases/org/apache/poi/hssf/eventusermodel/TestFormatTrackingHSSFListener.java b/src/testcases/org/apache/poi/hssf/eventusermodel/TestFormatTrackingHSSFListener.java new file mode 100644 index 0000000000..e52a3bc961 --- /dev/null +++ b/src/testcases/org/apache/poi/hssf/eventusermodel/TestFormatTrackingHSSFListener.java @@ -0,0 +1,65 @@ +/* ==================================================================== + 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.record.Record; +import org.apache.poi.poifs.filesystem.POIFSFileSystem; +/** + * Tests for FormatTrackingHSSFListener + */ +public final class TestFormatTrackingHSSFListener extends TestCase { + private FormatTrackingHSSFListener listener; + + public void setUp() { + HSSFRequest req = new HSSFRequest(); + MockHSSFListener mockListen = new MockHSSFListener(); + listener = new FormatTrackingHSSFListener(mockListen); + req.addListenerForAllRecords(listener); + + HSSFEventFactory factory = new HSSFEventFactory(); + try { + InputStream is = HSSFTestDataSamples.openSampleFileStream("MissingBits.xls"); + POIFSFileSystem fs = new POIFSFileSystem(is); + factory.processWorkbookEvents(req, fs); + } catch (IOException e) { + throw new RuntimeException(e); + } + } + + public void testFormats() throws Exception { + assertEquals("_(*#,##0_);_(*(#,##0);_(* \"-\"_);_(@_)", listener.getFormatString(41)); + assertEquals("_($*#,##0_);_($*(#,##0);_($* \"-\"_);_(@_)", listener.getFormatString(42)); + assertEquals("_(*#,##0.00_);_(*(#,##0.00);_(*\"-\"??_);_(@_)", listener.getFormatString(43)); + } + + private static final class MockHSSFListener implements HSSFListener { + public MockHSSFListener() {} + private final List _records = new ArrayList(); + + public void processRecord(Record record) { + _records.add(record); + } + } +} \ No newline at end of file -- 2.39.5