From 6d283656c9f095380168097b91b9989a303496cc Mon Sep 17 00:00:00 2001 From: Nick Burch Date: Sun, 17 Jun 2007 15:54:52 +0000 Subject: [PATCH] Finish off the example use of MissingRecordAwareHSSFListener, which outputs a csv git-svn-id: https://svn.apache.org/repos/asf/jakarta/poi/trunk@548049 13f79535-47bb-0310-9956-ffa450edef68 --- .../eventusermodel/examples/XLS2CSVmra.java | 111 +++++++++++++++++- .../MissingRecordAwareHSSFListener.java | 2 +- 2 files changed, 108 insertions(+), 5 deletions(-) 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 b42632c12b..d54a091a36 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 @@ -20,6 +20,14 @@ import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.IOException; 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.HSSFEventFactory; import org.apache.poi.hssf.eventusermodel.HSSFListener; @@ -29,6 +37,9 @@ import org.apache.poi.hssf.eventusermodel.dummyrecord.LastCellOfRowDummyRecord; import org.apache.poi.hssf.eventusermodel.dummyrecord.MissingCellDummyRecord; 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; @@ -36,6 +47,9 @@ import org.apache.poi.hssf.record.NoteRecord; 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.usermodel.HSSFDateUtil; import org.apache.poi.poifs.filesystem.POIFSFileSystem; /** @@ -50,6 +64,14 @@ public class XLS2CSVmra implements HSSFListener { private int lastRowNumber; private int lastColumnNumber; + + /** Should we output the formula, or the value it has? */ + private boolean outputFormulaValues = true; + + // Records we pick up as we process + private SSTRecord sstRecord; + private Map customFormatRecords = new Hashtable(); + private List xfRecords = new ArrayList(); /** * Creates a new XLS -> CSV converter @@ -100,6 +122,18 @@ public class XLS2CSVmra implements HSSFListener { switch (record.getSid()) { + 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; @@ -119,7 +153,13 @@ public class XLS2CSVmra implements HSSFListener { thisRow = frec.getRow(); thisColumn = frec.getColumn(); - thisStr = '"' + frec.toString() + '"'; + + if(outputFormulaValues) { + thisStr = formatNumberDateCell(frec, frec.getValue()); + } else { + // TODO: Output the formula string + thisStr = '"' + frec.toString() + '"'; + } break; case LabelRecord.sid: LabelRecord lrec = (LabelRecord) record; @@ -133,13 +173,18 @@ public class XLS2CSVmra implements HSSFListener { thisRow = lsrec.getRow(); thisColumn = lsrec.getColumn(); - thisStr = '"' + "(TODO)" + '"'; + if(sstRecord == null) { + thisStr = '"' + "(No SST Record, can't identify string)" + '"'; + } else { + thisStr = '"' + sstRecord.getString(lsrec.getSSTIndex()).toString() + '"'; + } break; case NoteRecord.sid: NoteRecord nrec = (NoteRecord) record; thisRow = nrec.getRow(); thisColumn = nrec.getColumn(); + // TODO: Find object to match nrec.getShapeId() thisStr = '"' + "(TODO)" + '"'; break; case NumberRecord.sid: @@ -147,7 +192,9 @@ public class XLS2CSVmra implements HSSFListener { thisRow = numrec.getRow(); thisColumn = numrec.getColumn(); - thisStr = Double.toString(numrec.getValue()); // TODO: Formatting + + // Format + thisStr = formatNumberDateCell(numrec, numrec.getValue()); break; case RKRecord.sid: RKRecord rkrec = (RKRecord) record; @@ -160,6 +207,11 @@ public class XLS2CSVmra implements HSSFListener { break; } + // Handle new row + if(thisRow != -1 && thisRow != lastRowNumber) { + lastColumnNumber = -1; + } + // Handle missing column if(record instanceof MissingCellDummyRecord) { MissingCellDummyRecord mc = (MissingCellDummyRecord)record; @@ -186,15 +238,66 @@ public class XLS2CSVmra implements HSSFListener { if(record instanceof LastCellOfRowDummyRecord) { // Print out any missing commas if needed if(minColumns > 0) { - for(int i=lastColumnNumber; i<(minColumns-1); i++) { + // Columns are 0 based + if(lastColumnNumber == -1) { lastColumnNumber = 0; } + for(int i=lastColumnNumber; i<(minColumns); i++) { output.print(','); } } + // We're onto a new row + lastColumnNumber = -1; + // End the row output.println(); } } + + /** + * Formats a number or date cell, be that a real number, or the + * answer to a formula + */ + 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"); + 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) && + HSSFDateUtil.isValidExcelDate(value)) { + // Java wants M not m for month + format = format.replace('m','M'); + // Change \- into -, if it's there + format = format.replace("\\-","-"); + + // Format as a date + Date d = HSSFDateUtil.getJavaDate(value); + DateFormat df = new SimpleDateFormat(format); + return df.format(d); + } else { + if(format == "General") { + // Some sort of wierd default + return Double.toString(value); + } + + // Format as a number + DecimalFormat df = new DecimalFormat(format); + return df.format(value); + } + } + } public static void main(String[] args) throws Exception { diff --git a/src/scratchpad/src/org/apache/poi/hssf/eventusermodel/MissingRecordAwareHSSFListener.java b/src/scratchpad/src/org/apache/poi/hssf/eventusermodel/MissingRecordAwareHSSFListener.java index 7080bfa9af..a727d064b9 100644 --- a/src/scratchpad/src/org/apache/poi/hssf/eventusermodel/MissingRecordAwareHSSFListener.java +++ b/src/scratchpad/src/org/apache/poi/hssf/eventusermodel/MissingRecordAwareHSSFListener.java @@ -84,7 +84,7 @@ public class MissingRecordAwareHSSFListener implements HSSFListener { break; case BoundSheetRecord.sid: BoundSheetRecord bsr = (BoundSheetRecord) record; - System.out.println("New sheet named: " + bsr.getSheetname()); + //System.out.println("New sheet named: " + bsr.getSheetname()); break; case RowRecord.sid: RowRecord rowrec = (RowRecord) record; -- 2.39.5