diff --git a/src/ooxml/java/org/apache/poi/xssf/eventusermodel/XSSFSheetXMLHandler.java b/src/ooxml/java/org/apache/poi/xssf/eventusermodel/XSSFSheetXMLHandler.java index 2f5c1eb..982e1ba 100644 --- a/src/ooxml/java/org/apache/poi/xssf/eventusermodel/XSSFSheetXMLHandler.java +++ b/src/ooxml/java/org/apache/poi/xssf/eventusermodel/XSSFSheetXMLHandler.java @@ -18,8 +18,10 @@ package org.apache.poi.xssf.eventusermodel; import org.apache.poi.ss.usermodel.BuiltinFormats; import org.apache.poi.ss.usermodel.DataFormatter; +import org.apache.poi.xssf.model.CommentsTable; import org.apache.poi.xssf.model.StylesTable; import org.apache.poi.xssf.usermodel.XSSFCellStyle; +import org.apache.poi.xssf.usermodel.XSSFComment; import org.apache.poi.xssf.usermodel.XSSFRichTextString; import org.xml.sax.Attributes; import org.xml.sax.SAXException; @@ -50,6 +52,11 @@ public class XSSFSheetXMLHandler extends DefaultHandler { */ private StylesTable stylesTable; + /** + * Table with cell comments + */ + private CommentsTable commentsTable; + private ReadOnlySharedStringsTable sharedStringsTable; /** @@ -90,11 +97,13 @@ public class XSSFSheetXMLHandler extends DefaultHandler { */ public XSSFSheetXMLHandler( StylesTable styles, + CommentsTable comments, ReadOnlySharedStringsTable strings, SheetContentsHandler sheetContentsHandler, DataFormatter dataFormatter, boolean formulasNotResults) { this.stylesTable = styles; + this.commentsTable = comments; this.sharedStringsTable = strings; this.output = sheetContentsHandler; this.formulasNotResults = formulasNotResults; @@ -109,10 +118,11 @@ public class XSSFSheetXMLHandler extends DefaultHandler { */ public XSSFSheetXMLHandler( StylesTable styles, + CommentsTable comments, ReadOnlySharedStringsTable strings, SheetContentsHandler sheetContentsHandler, boolean formulasNotResults) { - this(styles, strings, sheetContentsHandler, new DataFormatter(), formulasNotResults); + this(styles, comments, strings, sheetContentsHandler, new DataFormatter(), formulasNotResults); } private boolean isTextTag(String name) { @@ -297,9 +307,9 @@ public class XSSFSheetXMLHandler extends DefaultHandler { thisStr = "(TODO: Unexpected type: " + nextDataType + ")"; break; } - + // Output - output.cell(cellRef, thisStr); + output.cell(cellRef, thisStr, commentsTable != null ? commentsTable.findCellComment(cellRef) : null); } else if ("f".equals(name)) { fIsOpen = false; } else if ("is".equals(name)) { @@ -346,7 +356,7 @@ public class XSSFSheetXMLHandler extends DefaultHandler { /** A row with the (zero based) row number has ended */ public void endRow(); /** A cell, with the given formatted value, was encountered */ - public void cell(String cellReference, String formattedValue); + public void cell(String cellReference, String formattedValue, XSSFComment comment); /** A header or footer has been encountered */ public void headerFooter(String text, boolean isHeader, String tagName); } diff --git a/src/ooxml/java/org/apache/poi/xssf/extractor/XSSFEventBasedExcelExtractor.java b/src/ooxml/java/org/apache/poi/xssf/extractor/XSSFEventBasedExcelExtractor.java index 7b0a223..e2fd09c 100644 --- a/src/ooxml/java/org/apache/poi/xssf/extractor/XSSFEventBasedExcelExtractor.java +++ b/src/ooxml/java/org/apache/poi/xssf/extractor/XSSFEventBasedExcelExtractor.java @@ -39,7 +39,9 @@ import org.apache.poi.xssf.eventusermodel.ReadOnlySharedStringsTable; import org.apache.poi.xssf.eventusermodel.XSSFReader; import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler; import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler.SheetContentsHandler; +import org.apache.poi.xssf.model.CommentsTable; import org.apache.poi.xssf.model.StylesTable; +import org.apache.poi.xssf.usermodel.XSSFComment; import org.apache.poi.xssf.usermodel.XSSFShape; import org.apache.poi.xssf.usermodel.XSSFSimpleShape; import org.apache.xmlbeans.XmlException; @@ -59,6 +61,7 @@ public class XSSFEventBasedExcelExtractor extends POIXMLTextExtractor { private Locale locale; private boolean includeSheetNames = true; private boolean formulasNotResults = false; + private boolean includeCellComments = false; private boolean includeHeadersFooters = true; private boolean includeTextBoxes = true; @@ -99,6 +102,13 @@ public class XSSFEventBasedExcelExtractor extends POIXMLTextExtractor { } /** + * Should cell comments be included? Default is false + */ + public void setIncludeCellComments(boolean includeCellComments) { + this.includeCellComments = includeCellComments; + } + + /** * Should headers and footers be included? Default is true */ public void setIncludeHeadersFooters(boolean includeHeadersFooters) { @@ -152,6 +162,7 @@ public class XSSFEventBasedExcelExtractor extends POIXMLTextExtractor { public void processSheet( SheetContentsHandler sheetContentsExtractor, StylesTable styles, + CommentsTable comments, ReadOnlySharedStringsTable strings, InputStream sheetInputStream) throws IOException, SAXException { @@ -169,7 +180,7 @@ public class XSSFEventBasedExcelExtractor extends POIXMLTextExtractor { SAXParser saxParser = saxFactory.newSAXParser(); XMLReader sheetParser = saxParser.getXMLReader(); ContentHandler handler = new XSSFSheetXMLHandler( - styles, strings, sheetContentsExtractor, formatter, formulasNotResults); + styles, comments, strings, sheetContentsExtractor, formatter, formulasNotResults); sheetParser.setContentHandler(handler); sheetParser.parse(sheetSource); } catch(ParserConfigurationException e) { @@ -196,7 +207,8 @@ public class XSSFEventBasedExcelExtractor extends POIXMLTextExtractor { text.append(iter.getSheetName()); text.append('\n'); } - processSheet(sheetExtractor, styles, strings, stream); + CommentsTable comments = includeCellComments ? iter.getSheetComments() : null; + processSheet(sheetExtractor, styles, comments, strings, stream); if (includeHeadersFooters) { sheetExtractor.appendHeaderText(text); } @@ -265,13 +277,17 @@ public class XSSFEventBasedExcelExtractor extends POIXMLTextExtractor { output.append('\n'); } - public void cell(String cellRef, String formattedValue) { + public void cell(String cellRef, String formattedValue, XSSFComment comment) { if(firstCellOfRow) { firstCellOfRow = false; } else { output.append('\t'); } output.append(formattedValue); + if (includeCellComments && comment != null) { + String commentText = comment.getString().getString().replace('\n', ' '); + output.append(" Comment by ").append(comment.getAuthor()).append(": ").append(commentText); + } } public void headerFooter(String text, boolean isHeader, String tagName) { diff --git a/src/ooxml/testcases/org/apache/poi/xssf/extractor/TestXSSFEventBasedExcelExtractor.java b/src/ooxml/testcases/org/apache/poi/xssf/extractor/TestXSSFEventBasedExcelExtractor.java index 52abe99..5c236d7 100644 --- a/src/ooxml/testcases/org/apache/poi/xssf/extractor/TestXSSFEventBasedExcelExtractor.java +++ b/src/ooxml/testcases/org/apache/poi/xssf/extractor/TestXSSFEventBasedExcelExtractor.java @@ -232,4 +232,33 @@ public class TestXSSFEventBasedExcelExtractor extends TestCase { fixture.setIncludeHeadersFooters(false); assertEquals(expectedOutputWithoutHeadersAndFooters, fixture.getText()); } + + /** + * Test that we return the output comments as the non-event-based + * XSSFExcelExtractor. + */ + public void testCommentsComparedToNonEventBasedExtractor() + throws Exception { + + String expectedOutputWithComments = + "Sheet1\n" + + "abc Comment by Shaun Kalley: Shaun Kalley: You gotta see this one!\t123\n"; + + String expectedOutputWithoutComments = + "Sheet1\n" + + "abc\t123\n"; + + XSSFExcelExtractor extractor = new XSSFExcelExtractor( + XSSFTestDataSamples.openSampleWorkbook("commentTest.xlsx")); + assertEquals(expectedOutputWithoutComments, extractor.getText()); + extractor.setIncludeCellComments(true); + assertEquals(expectedOutputWithComments, extractor.getText()); + + XSSFEventBasedExcelExtractor fixture = + new XSSFEventBasedExcelExtractor( + XSSFTestDataSamples.openSamplePackage("commentTest.xlsx")); + assertEquals(expectedOutputWithoutComments, fixture.getText()); + fixture.setIncludeCellComments(true); + assertEquals(expectedOutputWithComments, fixture.getText()); + } }