New api for reading xlsb (#920)
* Add new API
* Add testBasicXSSFBSheetContentsHandler
* Add testCommentsXSSFBSheetContentsHandler
* Add testDateXSSFBSheetContentsHandler
* Fix comment
* Code Review feedback
* Code review feedback
* Fix backwards compatibility
* rename helper method
* Organise imports
* Add @since POI 5.5.0
diff --git a/poi-ooxml/src/main/java/org/apache/poi/xssf/binary/XSSFBSheetHandler.java b/poi-ooxml/src/main/java/org/apache/poi/xssf/binary/XSSFBSheetHandler.java
index 7a24234..24676ac 100644
--- a/poi-ooxml/src/main/java/org/apache/poi/xssf/binary/XSSFBSheetHandler.java
+++ b/poi-ooxml/src/main/java/org/apache/poi/xssf/binary/XSSFBSheetHandler.java
@@ -23,6 +23,8 @@
import org.apache.poi.ss.usermodel.BuiltinFormats;
import org.apache.poi.ss.usermodel.DataFormatter;
+import org.apache.poi.ss.usermodel.ExcelNumberFormat;
+import org.apache.poi.ss.usermodel.FormulaError;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.ss.util.CellAddress;
import org.apache.poi.util.Internal;
@@ -41,10 +43,9 @@
private static final int CHECK_ALL_ROWS = -1;
private final SharedStrings stringsTable;
- private final XSSFSheetXMLHandler.SheetContentsHandler handler;
+ private final XSSFBSheetContentsHandler handler;
private final XSSFBStylesTable styles;
private final XSSFBCommentsTable comments;
- private final DataFormatter dataFormatter;
private final boolean formulasNotResults;//TODO: implement this
private int lastEndedRow = -1;
@@ -55,6 +56,51 @@
private StringBuilder xlWideStringBuffer = new StringBuilder();
private final XSSFBCellHeader cellBuffer = new XSSFBCellHeader();
+
+ /**
+ * Creates a handler that forwards native POI cell types to the supplied {@link
+ * XSSFBSheetContentsHandler}.
+ *
+ * <p>Select this overload when the consumer expects the raw cell representation rather than
+ * formatted strings.
+ *
+ * @param is XLSB worksheet stream to parse
+ * @param styles table providing cell style and number format metadata
+ * @param comments optional comments table, may be {@code null}
+ * @param strings shared strings table used by the sheet
+ * @param sheetContentsHandler callback receiving native cell events
+ * @since POI 5.5.0
+ */
+ public XSSFBSheetHandler(InputStream is,
+ XSSFBStylesTable styles,
+ XSSFBCommentsTable comments,
+ SharedStrings strings,
+ XSSFBSheetContentsHandler sheetContentsHandler,
+ boolean formulasNotResults) {
+ super(is);
+ this.styles = styles;
+ this.comments = comments;
+ this.stringsTable = strings;
+ this.handler = sheetContentsHandler;
+ this.formulasNotResults = formulasNotResults;
+ }
+
+ /**
+ * Creates a handler that converts numeric and date cells to formatted strings via {@link
+ * DataFormatter}.
+ *
+ * <p>Select this overload when the consumer expects formatted string values rather than raw
+ * cell representations.
+ *
+ * @param is XLSB worksheet stream to parse
+ * @param styles table providing cell style and number format metadata
+ * @param comments optional comments table, may be {@code null}
+ * @param strings shared strings table used by the sheet
+ * @param sheetContentsHandler callback receiving formatted string values
+ * @param dataFormatter formatter applied to numeric and date cells
+ * @see #XSSFBSheetHandler(InputStream, XSSFBStylesTable, XSSFBCommentsTable, SharedStrings,
+ * XSSFBSheetContentsHandler, boolean)
+ */
public XSSFBSheetHandler(InputStream is,
XSSFBStylesTable styles,
XSSFBCommentsTable comments,
@@ -66,11 +112,18 @@
this.styles = styles;
this.comments = comments;
this.stringsTable = strings;
- this.handler = sheetContentsHandler;
- this.dataFormatter = dataFormatter;
+ this.handler = new XSSFBSheetContentsHandlerWrapper(sheetContentsHandler, dataFormatter);
this.formulasNotResults = formulasNotResults;
}
+ /**
+ * Dispatches a parsed XLSB record to the appropriate specialised handler.
+ *
+ * @param id numeric record identifier supplied by {@link XSSFBParser}
+ * @param data raw record payload
+ * @throws XSSFBParseException if the record cannot be processed according to the XLSB spec
+ * @see XSSFBRecordType
+ */
@Override
public void handleRecord(int id, byte[] data) throws XSSFBParseException {
XSSFBRecordType type = XSSFBRecordType.lookup(id);
@@ -133,86 +186,117 @@
checkMissedComments(currentRow, cellBuffer.getColNum());
}
- private void handleCellValue(String formattedValue) {
- CellAddress cellAddress = new CellAddress(currentRow, cellBuffer.getColNum());
+ private void handleStringCellValue(String val) {
+ CellAddress cellAddress = getCellAddress();
+ XSSFBComment comment = getCellComment(cellAddress);
+ handler.stringCell(cellAddress.formatAsString(), val, comment);
+ }
+
+ private void handleDoubleCellValue(double val) {
+ CellAddress cellAddress = getCellAddress();
+ XSSFBComment comment = getCellComment(cellAddress);
+ ExcelNumberFormat nf = getExcelNumberFormat();
+ handler.doubleCell(cellAddress.formatAsString(), val, comment, nf);
+ }
+
+ private void handleErrorCellValue(int val) {
+ FormulaError fe;
+ try {
+ fe = FormulaError.forInt(val);
+ } catch (IllegalArgumentException e) {
+ fe = null;
+ }
+ CellAddress cellAddress = getCellAddress();
+ XSSFBComment comment = getCellComment(cellAddress);
+ handler.errorCell(cellAddress.formatAsString(), fe, comment);
+ }
+
+ private CellAddress getCellAddress() {
+ return new CellAddress(currentRow, cellBuffer.getColNum());
+ }
+
+ private XSSFBComment getCellComment(CellAddress cellAddress) {
XSSFBComment comment = null;
if (comments != null) {
comment = comments.get(cellAddress);
}
- handler.cell(cellAddress.formatAsString(), formattedValue, comment);
+ return comment;
+ }
+
+ private ExcelNumberFormat getExcelNumberFormat() {
+ int styleIdx = cellBuffer.getStyleIdx();
+ String formatString = styles.getNumberFormatString(styleIdx);
+ short styleIndex = styles.getNumberFormatIndex(styleIdx);
+ // for now, if formatString is null, silently punt
+ // and use "General". Not the best behavior,
+ // but we're doing it now in the streaming and non-streaming
+ // extractors for xlsx. See BUG-61053
+ if (formatString == null) {
+ formatString = BuiltinFormats.getBuiltinFormat(0);
+ styleIndex = 0;
+ }
+ return new ExcelNumberFormat(styleIndex, formatString);
}
private void handleFmlaNum(byte[] data) {
beforeCellValue(data);
//xNum
double val = LittleEndian.getDouble(data, XSSFBCellHeader.length);
- handleCellValue(formatVal(val, cellBuffer.getStyleIdx()));
+ handleDoubleCellValue(val);
}
private void handleCellSt(byte[] data) {
beforeCellValue(data);
xlWideStringBuffer.setLength(0);
XSSFBUtils.readXLWideString(data, XSSFBCellHeader.length, xlWideStringBuffer);
- handleCellValue(xlWideStringBuffer.toString());
+ handleStringCellValue(xlWideStringBuffer.toString());
}
private void handleFmlaString(byte[] data) {
beforeCellValue(data);
xlWideStringBuffer.setLength(0);
XSSFBUtils.readXLWideString(data, XSSFBCellHeader.length, xlWideStringBuffer);
- handleCellValue(xlWideStringBuffer.toString());
+ handleStringCellValue(xlWideStringBuffer.toString());
}
private void handleCellError(byte[] data) {
beforeCellValue(data);
- //TODO, read byte to figure out the type of error
- handleCellValue("ERROR");
+ int val = data[XSSFBCellHeader.length] & 0xFF;
+ handleErrorCellValue(val);
}
private void handleFmlaError(byte[] data) {
beforeCellValue(data);
- //TODO, read byte to figure out the type of error
- handleCellValue("ERROR");
+ int val = data[XSSFBCellHeader.length] & 0xFF;
+ handleErrorCellValue(val);
}
private void handleBoolean(byte[] data) {
beforeCellValue(data);
- String formattedVal = (data[XSSFBCellHeader.length] == 1) ? "TRUE" : "FALSE";
- handleCellValue(formattedVal);
+ boolean val = data[XSSFBCellHeader.length] == 1;
+ CellAddress cellAddress = getCellAddress();
+ XSSFBComment comment = getCellComment(cellAddress);
+ handler.booleanCell(cellAddress.formatAsString(), val, comment);
}
private void handleCellReal(byte[] data) {
beforeCellValue(data);
//xNum
double val = LittleEndian.getDouble(data, XSSFBCellHeader.length);
- handleCellValue(formatVal(val, cellBuffer.getStyleIdx()));
+ handleDoubleCellValue(val);
}
private void handleCellRk(byte[] data) {
beforeCellValue(data);
double val = rkNumber(data, XSSFBCellHeader.length);
- handleCellValue(formatVal(val, cellBuffer.getStyleIdx()));
- }
-
- private String formatVal(double val, int styleIdx) {
- String formatString = styles.getNumberFormatString(styleIdx);
- short styleIndex = styles.getNumberFormatIndex(styleIdx);
- //for now, if formatString is null, silently punt
- //and use "General". Not the best behavior,
- //but we're doing it now in the streaming and non-streaming
- //extractors for xlsx. See BUG-61053
- if (formatString == null) {
- formatString = BuiltinFormats.getBuiltinFormat(0);
- styleIndex = 0;
- }
- return dataFormatter.formatRawCellContents(val, styleIndex, formatString);
+ handleDoubleCellValue(val);
}
private void handleBrtCellIsst(byte[] data) {
beforeCellValue(data);
int idx = XSSFBUtils.castToInt(LittleEndian.getUInt(data, XSSFBCellHeader.length));
RichTextString rtss = stringsTable.getItemAt(idx);
- handleCellValue(rtss.getString());
+ handleStringCellValue(rtss.getString());
}
@@ -300,7 +384,7 @@
}
private void dumpEmptyCellComment(CellAddress cellAddress, XSSFBComment comment) {
- handler.cell(cellAddress.formatAsString(), null, comment);
+ handler.stringCell(cellAddress.formatAsString(), null, comment);
}
private double rkNumber(byte[] data, int offset) {
@@ -327,6 +411,174 @@
}
/**
+ * Receives streaming callbacks while {@link XSSFBSheetHandler} parses an XLSB sheet.
+ *
+ * @see XSSFBSheetHandler
+ * @since POI 5.5.0
+ */
+ public interface XSSFBSheetContentsHandler {
+ /**
+ * Signals that a row has started before any of its cells are delivered.
+ *
+ * @param rowNum zero-based row index
+ * @see #endRow(int)
+ */
+ void startRow(int rowNum);
+
+ /**
+ * Signals that a row has ended after all of its cells and comments were processed.
+ *
+ * @param rowNum zero-based row index
+ * @see #startRow(int)
+ */
+ void endRow(int rowNum);
+
+ /**
+ * Handles a cell that resolves to a string value, possibly representing a comment-only cell.
+ *
+ * @param cellReference A1-style cell address
+ * @param value string contents, or {@code null} if only a comment is present
+ * @param comment associated comment, or {@code null} if absent
+ * <p>Sheets that have missing or empty cells may result in sparse calls to <code>cell
+ * </code>. See the code in <code>
+ * poi-examples/src/main/java/org/apache/poi/xssf/eventusermodel/XLSX2CSV.java</code> for an
+ * example of how to handle this scenario.
+ * @see #doubleCell(String, double, XSSFComment, ExcelNumberFormat)
+ */
+ void stringCell(String cellReference, String value, XSSFComment comment);
+
+ /**
+ * Handles a numeric cell while providing the corresponding {@link ExcelNumberFormat}.
+ *
+ * @param cellReference A1-style cell address
+ * @param value numeric value extracted from the sheet
+ * @param comment associated comment, or {@code null} if absent
+ * @param nf number format describing how the value should be rendered
+ * <p>Sheets that have missing or empty cells may result in sparse calls to <code>cell
+ * </code>. See the code in <code>
+ * poi-examples/src/main/java/org/apache/poi/xssf/eventusermodel/XLSX2CSV.java</code> for an
+ * example of how to handle this scenario.
+ * @see #stringCell(String, String, XSSFComment)
+ */
+ void doubleCell(String cellReference, double value, XSSFComment comment, ExcelNumberFormat nf);
+
+ /**
+ * Handles a boolean cell.
+ *
+ * @param cellReference A1-style cell address
+ * @param value boolean value stored in the cell
+ * @param comment associated comment, or {@code null} if absent
+ * <p>Sheets that have missing or empty cells may result in sparse calls to <code>cell
+ * </code>. See the code in <code>
+ * poi-examples/src/main/java/org/apache/poi/xssf/eventusermodel/XLSX2CSV.java</code> for an
+ * example of how to handle this scenario.
+ * @see #stringCell(String, String, XSSFComment)
+ */
+ void booleanCell(String cellReference, boolean value, XSSFComment comment);
+
+ /**
+ * Handles a cell that evaluates to an error.
+ *
+ * @param cellReference A1-style cell address
+ * @param fe mapped {@link FormulaError}, or {@code null} when the error code is unknown
+ * @param comment associated comment, or {@code null} if absent
+ * <p>Sheets that have missing or empty cells may result in sparse calls to <code>cell
+ * </code>. See the code in <code>
+ * poi-examples/src/main/java/org/apache/poi/xssf/eventusermodel/XLSX2CSV.java</code> for an
+ * example of how to handle this scenario.
+ * @see FormulaError
+ */
+ void errorCell(String cellReference, FormulaError fe, XSSFComment comment);
+
+ /**
+ * Receives header or footer text encountered in the sheet.
+ *
+ * @param text resolved header or footer text
+ * @param isHeader {@code true} when the text belongs to a header, otherwise {@code false}
+ * @param tagName POI-internal tag representing the header or footer section
+ * @see #endSheet()
+ */
+ void headerFooter(String text, boolean isHeader, String tagName);
+
+ /**
+ * Signals that the sheet has been completely processed.
+ *
+ * @see #startRow(int)
+ */
+ void endSheet();
+ }
+
+ /**
+ * Bridges a {@link XSSFSheetXMLHandler.SheetContentsHandler} to the {@link
+ * XSSFBSheetContentsHandler} contract.
+ *
+ * @see XSSFSheetXMLHandler
+ */
+ private final class XSSFBSheetContentsHandlerWrapper implements XSSFBSheetContentsHandler {
+ private final XSSFSheetXMLHandler.SheetContentsHandler delegate;
+ private final DataFormatter dataFormatter;
+
+ /**
+ * Creates a wrapper that forwards events to the XML sheet handler while formatting numeric
+ * cells.
+ *
+ * @param delegate target handler compatible with the XML streaming API
+ * @param dataFormatter formatter used for numeric and date cell rendering
+ */
+ XSSFBSheetContentsHandlerWrapper(
+ XSSFSheetXMLHandler.SheetContentsHandler delegate, DataFormatter dataFormatter) {
+ this.delegate = delegate;
+ this.dataFormatter = dataFormatter;
+ }
+
+ @Override
+ public void startRow(int rowNum) {
+ delegate.startRow(rowNum);
+ }
+
+ @Override
+ public void endRow(int rowNum) {
+ delegate.endRow(rowNum);
+ }
+
+ @Override
+ public void stringCell(String cellReference, String value, XSSFComment comment) {
+ delegate.cell(cellReference, value, comment);
+ }
+
+ @Override
+ public void doubleCell(
+ String cellReference, double value, XSSFComment comment, ExcelNumberFormat nf) {
+ String formattedValue =
+ dataFormatter.formatRawCellContents(value, nf.getIdx(), nf.getFormat());
+ delegate.cell(cellReference, formattedValue, comment);
+ }
+
+ @Override
+ public void booleanCell(String cellReference, boolean value, XSSFComment comment) {
+ delegate.cell(cellReference, Boolean.toString(value), comment);
+ }
+
+ @Override
+ public void errorCell(String cellReference, FormulaError fe, XSSFComment comment) {
+ // For backward compatibility, we pass "ERROR" as the cell value.
+ // If you need the actual error code, you should implement
+ // XSSFBSheetContentsHandler directly
+ delegate.cell(cellReference, "ERROR", comment);
+ }
+
+ @Override
+ public void headerFooter(String text, boolean isHeader, String tagName) {
+ delegate.headerFooter(text, isHeader, tagName);
+ }
+
+ @Override
+ public void endSheet() {
+ delegate.endSheet();
+ }
+ }
+
+ /**
* You need to implement this to handle the results
* of the sheet parsing.
*/
diff --git a/poi-ooxml/src/test/java/org/apache/poi/xssf/eventusermodel/TestXSSFBReader.java b/poi-ooxml/src/test/java/org/apache/poi/xssf/eventusermodel/TestXSSFBReader.java
index 3fa9626..9ea4652 100644
--- a/poi-ooxml/src/test/java/org/apache/poi/xssf/eventusermodel/TestXSSFBReader.java
+++ b/poi-ooxml/src/test/java/org/apache/poi/xssf/eventusermodel/TestXSSFBReader.java
@@ -17,10 +17,6 @@
package org.apache.poi.xssf.eventusermodel;
-import static org.apache.poi.POITestCase.assertContains;
-import static org.junit.jupiter.api.Assertions.assertEquals;
-import static org.junit.jupiter.api.Assertions.assertNotNull;
-
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
@@ -28,11 +24,29 @@
import org.apache.poi.POIDataSamples;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.DataFormatter;
+import org.apache.poi.ss.usermodel.ExcelNumberFormat;
+import org.apache.poi.ss.usermodel.FormulaError;
import org.apache.poi.xssf.binary.XSSFBSharedStringsTable;
import org.apache.poi.xssf.binary.XSSFBSheetHandler;
import org.apache.poi.xssf.binary.XSSFBStylesTable;
import org.apache.poi.xssf.usermodel.XSSFComment;
import org.junit.jupiter.api.Test;
+import org.mockito.ArgumentCaptor;
+import org.mockito.ArgumentMatcher;
+import org.mockito.InOrder;
+import org.mockito.quality.Strictness;
+
+import static org.apache.poi.POITestCase.assertContains;
+import static org.junit.jupiter.api.Assertions.assertEquals;
+import static org.junit.jupiter.api.Assertions.assertNotNull;
+import static org.mockito.ArgumentMatchers.any;
+import static org.mockito.ArgumentMatchers.argThat;
+import static org.mockito.ArgumentMatchers.eq;
+import static org.mockito.ArgumentMatchers.isNull;
+import static org.mockito.ArgumentMatchers.notNull;
+import static org.mockito.Mockito.inOrder;
+import static org.mockito.Mockito.mock;
+import static org.mockito.Mockito.withSettings;
class TestXSSFBReader {
@@ -216,4 +230,234 @@
return sb.toString();
}
}
+
+ private static XSSFBSheetHandler.XSSFBSheetContentsHandler mockSheetContentsHandler() {
+ return mock(
+ XSSFBSheetHandler.XSSFBSheetContentsHandler.class,
+ withSettings().strictness(Strictness.STRICT_STUBS));
+ }
+
+ private static ArgumentMatcher<XSSFComment> commentWith(String author, String text) {
+ return comment -> comment != null
+ && author.equals(comment.getAuthor())
+ && comment.getString() != null
+ && text.equals(comment.getString().toString().trim());
+ }
+
+ private void readAllSheetsFromWorkbook(String fileName,
+ XSSFBSheetHandler.XSSFBSheetContentsHandler handler) throws Exception {
+ try (OPCPackage pkg = OPCPackage.open(_ssTests.openResourceAsStream(fileName))) {
+ XSSFBReader r = new XSSFBReader(pkg);
+ assertNotNull(r.getXSSFBStylesTable());
+ XSSFBSharedStringsTable sst = new XSSFBSharedStringsTable(pkg);
+ XSSFBStylesTable xssfbStylesTable = r.getXSSFBStylesTable();
+ XSSFBReader.SheetIterator it = (XSSFBReader.SheetIterator) r.getSheetsData();
+
+ while (it.hasNext()) {
+ InputStream is = it.next();
+ XSSFBSheetHandler sheetHandler = new XSSFBSheetHandler(is,
+ xssfbStylesTable,
+ it.getXSSFBSheetComments(),
+ sst,
+ handler,
+ false);
+ sheetHandler.parse();
+ }
+ }
+ }
+
+ @Test
+ void testBasicXSSFBSheetContentsHandler() throws Exception {
+ XSSFBSheetHandler.XSSFBSheetContentsHandler handler = mockSheetContentsHandler();
+ readAllSheetsFromWorkbook("testVarious.xlsb", handler);
+
+ InOrder ordered = inOrder(handler);
+ ordered.verify(handler).startRow(0);
+ ordered.verify(handler).stringCell(eq("A1"), eq("String"), isNull());
+ ordered.verify(handler).stringCell(eq("B1"), eq("This is a string"), isNull());
+ ordered.verify(handler).endRow(0);
+
+ ordered.verify(handler).startRow(1);
+ ordered.verify(handler).stringCell(eq("A2"), eq("integer"), isNull());
+ ordered.verify(handler).doubleCell(eq("B2"), eq(13.0d), isNull(), any(ExcelNumberFormat.class));
+ ordered.verify(handler).endRow(1);
+
+ ordered.verify(handler).startRow(2);
+ ordered.verify(handler).stringCell(eq("A3"), eq("float"), isNull());
+ ordered.verify(handler).doubleCell(eq("B3"), eq(13.1211231321d), isNull(), any(ExcelNumberFormat.class));
+ ordered.verify(handler).endRow(2);
+
+ ordered.verify(handler).startRow(3);
+ ordered.verify(handler).stringCell(eq("A4"), eq("currency"), isNull());
+ ordered.verify(handler).doubleCell(eq("B4"), eq(3.03d), isNull(), any(ExcelNumberFormat.class));
+ ordered.verify(handler).endRow(3);
+
+ ordered.verify(handler).startRow(4);
+ ordered.verify(handler).stringCell(eq("A5"), eq("percent"), isNull());
+ ordered.verify(handler).doubleCell(eq("B5"), eq(0.2d), isNull(), any(ExcelNumberFormat.class));
+ ordered.verify(handler).endRow(4);
+
+ ordered.verify(handler).startRow(5);
+ ordered.verify(handler).stringCell(eq("A6"), eq("float 2"), isNull());
+ ordered.verify(handler).doubleCell(eq("B6"), eq(13.12131231d), isNull(), any(ExcelNumberFormat.class));
+ ordered.verify(handler).endRow(5);
+
+ ordered.verify(handler).startRow(6);
+ ordered.verify(handler).stringCell(eq("A7"), eq("long int"), isNull());
+ ordered.verify(handler).doubleCell(eq("B7"), eq(1.23456789012345E14d), isNull(), any(ExcelNumberFormat.class));
+ ordered.verify(handler).endRow(6);
+
+ ordered.verify(handler).startRow(7);
+ ordered.verify(handler).stringCell(eq("A8"), eq("longer int"), isNull());
+ ordered.verify(handler).doubleCell(eq("B8"), eq(1.23456789012345E15d), isNull(), any(ExcelNumberFormat.class));
+ ordered.verify(handler).stringCell(eq("C8"), isNull(), notNull(XSSFComment.class));
+ ordered.verify(handler).endRow(7);
+
+ ordered.verify(handler).startRow(8);
+ ordered.verify(handler).stringCell(eq("A9"), eq("fraction"), isNull());
+ ordered.verify(handler).doubleCell(eq("B9"), eq(0.25d), isNull(), any(ExcelNumberFormat.class));
+ ordered.verify(handler).endRow(8);
+
+ ordered.verify(handler).startRow(9);
+ ordered.verify(handler).stringCell(eq("A10"), eq("date"), isNull());
+ ordered.verify(handler).doubleCell(eq("B10"), eq(42803.0d), isNull(), any(ExcelNumberFormat.class));
+ ordered.verify(handler).endRow(9);
+
+ ordered.verify(handler).startRow(10);
+ ordered.verify(handler).stringCell(eq("A11"), eq("comment"), isNull());
+ ordered.verify(handler).stringCell(eq("B11"), eq("contents"), notNull(XSSFComment.class));
+ ordered.verify(handler).endRow(10);
+
+ ordered.verify(handler).startRow(11);
+ ordered.verify(handler).stringCell(eq("A12"), eq("hyperlink"), isNull());
+ ordered.verify(handler).stringCell(eq("B12"), eq("tika_link"), isNull());
+ ordered.verify(handler).endRow(11);
+
+ ordered.verify(handler).startRow(12);
+ ordered.verify(handler).stringCell(eq("A13"), eq("formula"), isNull());
+ ordered.verify(handler).doubleCell(eq("B13"), eq(4.0d), isNull(), any(ExcelNumberFormat.class));
+ ordered.verify(handler).doubleCell(eq("C13"), eq(2.0d), isNull(), any(ExcelNumberFormat.class));
+ ordered.verify(handler).endRow(12);
+
+ ordered.verify(handler).startRow(13);
+ ordered.verify(handler).stringCell(eq("A14"), eq("formulaErr"), isNull());
+ ordered.verify(handler).errorCell(eq("B14"), eq(FormulaError.NAME), isNull());
+ ordered.verify(handler).endRow(13);
+
+ ordered.verify(handler).startRow(14);
+ ordered.verify(handler).stringCell(eq("A15"), eq("formulaFloat"), isNull());
+ ordered.verify(handler).doubleCell(eq("B15"), eq(0.5d), isNull(), any(ExcelNumberFormat.class));
+ ordered.verify(handler).stringCell(eq("D15"), eq("March"), isNull());
+ ordered.verify(handler).stringCell(eq("E15"), eq("April"), isNull());
+ ordered.verify(handler).endRow(14);
+
+ ordered.verify(handler).startRow(15);
+ ordered.verify(handler).stringCell(eq("A16"), eq("customFormat1"), isNull());
+ ordered.verify(handler).stringCell(eq("B16"), eq(" 46/1963"), isNull());
+ ordered.verify(handler).stringCell(eq("C16"), eq("merchant1"), isNull());
+ ordered.verify(handler).doubleCell(eq("D16"), eq(1.0d), isNull(), any(ExcelNumberFormat.class));
+ ordered.verify(handler).doubleCell(eq("E16"), eq(3.0d), isNull(), any(ExcelNumberFormat.class));
+ ordered.verify(handler).endRow(15);
+
+ ordered.verify(handler).startRow(16);
+ ordered.verify(handler).stringCell(eq("A17"), eq("customFormat2"), isNull());
+ ordered.verify(handler).stringCell(eq("B17"), eq(" 3/128"), isNull());
+ ordered.verify(handler).stringCell(eq("C17"), eq("merchant2"), isNull());
+ ordered.verify(handler).doubleCell(eq("D17"), eq(2.0d), isNull(), any(ExcelNumberFormat.class));
+ ordered.verify(handler).doubleCell(eq("E17"), eq(4.0d), isNull(), any(ExcelNumberFormat.class));
+ ordered.verify(handler).endRow(16);
+
+ ordered.verify(handler).startRow(20);
+ ordered.verify(handler).stringCell(eq("C21"), eq("text test"), isNull());
+ ordered.verify(handler).endRow(20);
+
+ ordered.verify(handler).startRow(22);
+ ordered.verify(handler).stringCell(eq("A23"), isNull(), notNull(XSSFComment.class));
+ ordered.verify(handler).endRow(22);
+
+ ordered.verify(handler).startRow(23);
+ ordered.verify(handler).stringCell(eq("C24"), isNull(), notNull(XSSFComment.class));
+ ordered.verify(handler).endRow(23);
+
+ ordered.verify(handler).startRow(27);
+ ordered.verify(handler).stringCell(eq("B28"), isNull(), notNull(XSSFComment.class));
+ ordered.verify(handler).endRow(27);
+
+ ordered.verify(handler).startRow(29);
+ ordered.verify(handler).stringCell(eq("B30"), eq("the"), isNull());
+ ordered.verify(handler).stringCell(eq("C30"), isNull(), notNull(XSSFComment.class));
+ ordered.verify(handler).endRow(29);
+
+ ordered.verify(handler).startRow(32);
+ ordered.verify(handler).stringCell(eq("B33"), eq("the"), isNull());
+ ordered.verify(handler).stringCell(eq("C33"), isNull(), notNull(XSSFComment.class));
+ ordered.verify(handler).stringCell(eq("D33"), eq("quick"), isNull());
+ ordered.verify(handler).endRow(32);
+
+ ordered.verify(handler).startRow(34);
+ ordered.verify(handler).stringCell(eq("B35"), eq("comment6"), notNull(XSSFComment.class));
+ ordered.verify(handler).endRow(34);
+
+ ordered.verify(handler).startRow(64);
+ ordered.verify(handler).stringCell(eq("I65"), isNull(), notNull(XSSFComment.class));
+ ordered.verify(handler).endRow(64);
+
+ ordered.verify(handler).startRow(65);
+ ordered.verify(handler).stringCell(eq("I66"), isNull(), notNull(XSSFComment.class));
+ ordered.verify(handler).endRow(65);
+
+ ordered.verify(handler).headerFooter(eq("OddLeftHeader OddCenterHeader OddRightHeader"), eq(true), eq("header"));
+ ordered.verify(handler).headerFooter(eq("OddLeftFooter OddCenterFooter OddRightFooter"), eq(false), eq("footer"));
+ ordered.verify(handler).headerFooter(eq("EvenLeftHeader EvenCenterHeader EvenRightHeader\n"), eq(true), eq("evenHeader"));
+ ordered.verify(handler).headerFooter(eq("EvenLeftFooter EvenCenterFooter EvenRightFooter"), eq(false), eq("evenFooter"));
+ ordered.verify(handler).headerFooter(eq("FirstPageLeftHeader FirstPageCenterHeader FirstPageRightHeader"), eq(true), eq("firstHeader"));
+ ordered.verify(handler).headerFooter(eq("FirstPageLeftFooter FirstPageCenterFooter FirstPageRightFooter"), eq(false), eq("firstFooter"));
+ ordered.verifyNoMoreInteractions();
+ }
+
+ @Test
+ void testCommentsXSSFBSheetContentsHandler() throws Exception {
+ XSSFBSheetHandler.XSSFBSheetContentsHandler handler = mockSheetContentsHandler();
+ readAllSheetsFromWorkbook("comments.xlsb", handler);
+
+ InOrder ordered = inOrder(handler);
+ ordered.verify(handler).startRow(0);
+ ordered.verify(handler).stringCell(eq("A1"), isNull(),
+ argThat(commentWith("Sven Nissel", "comment top row1 (index0)")));
+ ordered.verify(handler).stringCell(eq("B1"), eq("row1"), isNull());
+ ordered.verify(handler).endRow(0);
+ ordered.verify(handler).startRow(1);
+ ordered.verify(handler).stringCell(eq("A2"), isNull(),
+ argThat(commentWith("Allison, Timothy B.", "Allison, Timothy B.:\ncomment row2 (index1)")));
+ ordered.verify(handler).endRow(1);
+ ordered.verify(handler).startRow(2);
+ ordered.verify(handler).stringCell(eq("A3"), eq("row3"),
+ argThat(commentWith("Sven Nissel", "comment top row3 (index2)")));
+ ordered.verify(handler).stringCell(eq("B3"), eq("row3"), isNull());
+ ordered.verify(handler).endRow(2);
+ ordered.verify(handler).startRow(3);
+ ordered.verify(handler).stringCell(eq("A4"), isNull(),
+ argThat(commentWith("Sven Nissel", "comment top row4 (index3)")));
+ ordered.verify(handler).stringCell(eq("B4"), eq("row4"), isNull());
+ ordered.verify(handler).endRow(3);
+ }
+
+ @Test
+ void testDateXSSFBSheetContentsHandler() throws Exception {
+ XSSFBSheetHandler.XSSFBSheetContentsHandler handler = mockSheetContentsHandler();
+ readAllSheetsFromWorkbook("date.xlsb", handler);
+
+ InOrder ordered = inOrder(handler);
+ ArgumentCaptor<ExcelNumberFormat> numberFormat = ArgumentCaptor.forClass(ExcelNumberFormat.class);
+ ordered.verify(handler).startRow(0);
+ ordered.verify(handler).doubleCell(eq("A1"), eq(41286.0d), isNull(), numberFormat.capture());
+ ordered.verify(handler).endRow(0);
+ ExcelNumberFormat format = numberFormat.getValue();
+ assertNotNull(format);
+ assertEquals(14, format.getIdx());
+ assertEquals("m/d/yy", format.getFormat());
+ ordered.verifyNoMoreInteractions();
+ DataFormatter df = new DataFormatter();
+ assertEquals("1/12/13", df.formatRawCellContents(41286.0d, format.getIdx(), format.getFormat()));
+ }
}
