sheetIterator() {
+ throw new UnsupportedOperationException("Not implemented yet. Use getSheetIterator()");
+ }
+
+ /**
+ *
+ * Returns the number of sheets,
+ *
+ * @return int representing the number of sheets in workbook
+ */
+ public int getNumberOfSheets() {
+ int sheetCount = 0;
+
+ if(this.sheetIterator != null){
+ while(sheetIterator.hasNext()){
+ sheetIterator.next();
+ sheetCount++;
+ }
+ }else{
+ logger.log(POILogger.ERROR, "Workbook already closed");
+ }
+
+
+ return sheetCount;
+ }
+
+ /**
+ *
+ * Returns sheet at specified index
+ *
+ * @return Sheet for the specified index
+ */
+ public Sheet getSheetAt(int index) {
+ if(sheetList == null && !isClosed){
+ getAllSheets();
+ }
+
+ if(sheetList != null){
+ for(StreamedSheet sheet: sheetList){
+ if(sheet.getSheetNumber() == index)return sheet;
+ }
+ }
+
+ return null;
+ }
+
+ /**
+ *
+ * Returns the sheet with specified name
+ *
+ *
+ */
+ public Sheet getSheet(String name) {
+
+ if(sheetList == null && !isClosed){
+ getAllSheets();
+ }
+
+ if(sheetList != null){
+ for(StreamedSheet sheet: sheetList){
+ if(sheet.getSheetName().equals(name))return sheet;
+ }
+ }
+
+ return null;
+ }
+
+ /**
+ *
+ * Not supported right now, as StreamedWorkbook
+ * supports only reading.
+ *
+ * @exception UnsupportedOperationException
+ */
+ public void removeSheetAt(int index) {
+ throw new UnsupportedOperationException("Not supported due to memory footprint");
+ }
+
+ /**
+ *
+ * Not supported right now, as StreamedWorkbook
+ * supports only reading.
+ *
+ * @exception UnsupportedOperationException
+ */
+ public Font createFont() {
+ throw new UnsupportedOperationException("Not supported due to memory footprint");
+ }
+
+
+ /**
+ *
+ * Will be supported in the future.
+ *
+ * @exception UnsupportedOperationException
+ */
+ public Font findFont(short boldWeight, short color, short fontHeight,
+ String name, boolean italic, boolean strikeout, short typeOffset,
+ byte underline) {
+ throw new UnsupportedOperationException("Not implemented yet");
+ }
+
+ /**
+ *
+ * Will be supported in the future.
+ *
+ * @exception UnsupportedOperationException
+ */
+ public Font findFont(boolean bold, short color, short fontHeight,
+ String name, boolean italic, boolean strikeout, short typeOffset,
+ byte underline) {
+ throw new UnsupportedOperationException("Not implemented yet");
+ }
+
+ /**
+ *
+ * Will be supported in the future.
+ *
+ * @exception UnsupportedOperationException
+ */
+ public short getNumberOfFonts() {
+ throw new UnsupportedOperationException("Not implemented yet");
+ }
+
+ /**
+ *
+ * Will be supported in the future.
+ *
+ * @exception UnsupportedOperationException
+ */
+ public Font getFontAt(short idx) {
+ throw new UnsupportedOperationException("Not implemented yet");
+ }
+
+ /**
+ *
+ * Not supported right now, as StreamedWorkbook
+ * supports only reading.
+ *
+ * @exception UnsupportedOperationException
+ */
+ public CellStyle createCellStyle() {
+ throw new UnsupportedOperationException("Not supported due to memory footprint");
+ }
+
+
+ /**
+ *
+ * Will be supported in the future.
+ *
+ * @exception UnsupportedOperationException
+ */
+ public int getNumCellStyles() {
+ throw new UnsupportedOperationException("Not implemented yet");
+ }
+
+ /**
+ *
+ * Not Suppored due to memory footprint.
+ *
+ * @exception UnsupportedOperationException
+ */
+ public CellStyle getCellStyleAt(int idx) {
+ throw new UnsupportedOperationException("Not supported due to memory footprint");
+ }
+
+
+ /**
+ *
+ * Not supported right now, as StreamedWorkbook
+ * supports only reading.
+ *
+ * @exception UnsupportedOperationException
+ */
+ public void write(OutputStream stream) throws IOException {
+ throw new UnsupportedOperationException("Not supported due to memory footprint");
+ }
+
+ /**
+ *
+ * Close the workbook
+ *
+ *
+ */
+ public void close() throws IOException {
+ if(sheetIterator != null){
+ while(sheetIterator.hasNext()){
+ sheetIterator.next().close();
+ }
+ sheetIterator = null;
+ }
+
+ if(sheetList != null){
+ sheetList.clear();
+ sheetList = null;
+ }
+
+ isClosed = true;
+ }
+
+ /**
+ *
+ * Will be supported in the future.
+ *
+ * @exception UnsupportedOperationException
+ */
+ public int getNumberOfNames() {
+ throw new UnsupportedOperationException("Not implemented yet");
+ }
+
+ /**
+ *
+ * Will be supported in the future.
+ *
+ * @exception UnsupportedOperationException
+ */
+ public Name getName(String name) {
+ throw new UnsupportedOperationException("Not implemented yet");
+ }
+
+ /**
+ *
+ * Will be supported in the future.
+ *
+ * @exception UnsupportedOperationException
+ */
+ public List getNames(String name) {
+ throw new UnsupportedOperationException("Not implemented yet");
+ }
+
+ /**
+ *
+ * Will be supported in the future.
+ *
+ * @exception UnsupportedOperationException
+ */
+ public List getAllNames() {
+ throw new UnsupportedOperationException("Not implemented yet");
+ }
+
+ /**
+ *
+ * Will be supported in the future.
+ *
+ * @exception UnsupportedOperationException
+ */
+ public Name getNameAt(int nameIndex) {
+ throw new UnsupportedOperationException("Not implemented yet");
+ }
+
+
+ /**
+ *
+ * Not supported right now, as StreamedWorkbook
+ * supports only reading.
+ *
+ * @exception UnsupportedOperationException
+ */
+ public Name createName() {
+ throw new UnsupportedOperationException("Not supported due to memory footprint");
+ }
+
+ /**
+ *
+ * Will be supported in the future.
+ *
+ * @exception UnsupportedOperationException
+ */
+ public int getNameIndex(String name) {
+ throw new UnsupportedOperationException("Not implemented yet");
+ }
+
+ /**
+ *
+ * Not supported right now, as StreamedWorkbook
+ * supports only reading.
+ *
+ * @exception UnsupportedOperationException
+ */
+ public void removeName(int index) {
+ throw new UnsupportedOperationException("Not supported due to memory footprint");
+ }
+
+ /**
+ *
+ * Not supported right now, as StreamedWorkbook
+ * supports only reading.
+ *
+ * @exception UnsupportedOperationException
+ */
+ public void removeName(String name) {
+ throw new UnsupportedOperationException("Not supported due to memory footprint");
+ }
+
+ /**
+ *
+ * Not supported right now, as StreamedWorkbook
+ * supports only reading.
+ *
+ * @exception UnsupportedOperationException
+ */
+ public void removeName(Name name) {
+ throw new UnsupportedOperationException("Not supported due to memory footprint");
+ }
+
+ /**
+ *
+ * Not supported right now, as StreamedWorkbook
+ * supports only reading.
+ *
+ * @exception UnsupportedOperationException
+ */
+ public int linkExternalWorkbook(String name, Workbook workbook) {
+ throw new UnsupportedOperationException("Not supported due to memory footprint");
+ }
+
+ /**
+ *
+ * Not supported right now, as StreamedWorkbook
+ * supports only reading.
+ *
+ *
+ */
+ public void setPrintArea(int sheetIndex, String reference) {
+ throw new UnsupportedOperationException("Not supported due to memory footprint");
+ }
+
+ /**
+ *
+ * Not supported right now, as StreamedWorkbook
+ * supports only reading.
+ *
+ * @exception UnsupportedOperationException
+ */
+ public void setPrintArea(int sheetIndex, int startColumn, int endColumn,
+ int startRow, int endRow) {
+ throw new UnsupportedOperationException("Not supported due to memory footprint");
+ }
+
+ /**
+ *
+ * Will be supported in the future.
+ *
+ * @exception UnsupportedOperationException
+ */
+ public String getPrintArea(int sheetIndex) {
+ throw new UnsupportedOperationException("Not implemented yet");
+ }
+
+ /**
+ *
+ * Not supported right now, as StreamedWorkbook
+ * supports only reading.
+ *
+ * @exception UnsupportedOperationException
+ */
+ public void removePrintArea(int sheetIndex) {
+ throw new UnsupportedOperationException("Not supported due to memory footprint");
+ }
+
+ /**
+ *
+ * Will be supported in the future.
+ *
+ * @exception UnsupportedOperationException
+ */
+ public MissingCellPolicy getMissingCellPolicy() {
+ throw new UnsupportedOperationException("Not implemented yet");
+ }
+
+ /**
+ *
+ * Not supported right now, as StreamedWorkbook
+ * supports only reading.
+ *
+ * @exception UnsupportedOperationException
+ */
+ public void setMissingCellPolicy(MissingCellPolicy missingCellPolicy) {
+ throw new UnsupportedOperationException("Not supported due to memory footprint");
+ }
+
+ /**
+ *
+ * Not supported right now, as StreamedWorkbook
+ * supports only reading.
+ *
+ * @exception UnsupportedOperationException
+ */
+ public DataFormat createDataFormat() {
+ throw new UnsupportedOperationException("Not supported due to memory footprint");
+ }
+
+ /**
+ *
+ * Not supported right now, as StreamedWorkbook
+ * supports only reading.
+ *
+ * @exception UnsupportedOperationException
+ */
+ public int addPicture(byte[] pictureData, int format) {
+ throw new UnsupportedOperationException("Not supported due to memory footprint");
+ }
+
+ /**
+ *
+ * Not supported due to memory footprint.
+ *
+ * @exception UnsupportedOperationException
+ */
+ public List getAllPictures() {
+ throw new UnsupportedOperationException("Not supported due to memory footprint");
+ }
+
+ /**
+ *
+ * Will be supported in the future.
+ *
+ * @exception UnsupportedOperationException
+ */
+ public CreationHelper getCreationHelper() {
+ throw new UnsupportedOperationException("Not implemented yet");
+ }
+
+ /**
+ *
+ * Will be supported in the future.
+ *
+ * @exception UnsupportedOperationException
+ */
+ public boolean isHidden() {
+ throw new UnsupportedOperationException("Not implemented yet");
+ }
+
+ /**
+ *
+ * Not supported right now, as StreamedWorkbook
+ * supports only reading.
+ *
+ * @exception UnsupportedOperationException
+ */
+ public void setHidden(boolean hiddenFlag) {
+ throw new UnsupportedOperationException("Not supported due to memory footprint");
+ }
+
+ /**
+ *
+ * Return true if the sheet with given index is hidden
+ *
+ * @return boolean
+ */
+ public boolean isSheetHidden(int sheetIx) {
+ //throw new UnsupportedOperationException("Not implemented yet");
+
+ Object[] hiddenSheetIdxAry = getValuesFromWorkbookData(HIDDEN_SHEET_PATH);
+
+ for(Object o: hiddenSheetIdxAry){
+ int index = (Integer.parseInt(o.toString())-1);
+ if(index == sheetIx)return true;
+ }
+
+ return false;
+ }
+
+ /**
+ *
+ * Return true if the sheet with given index is veryHidden
+ *
+ * @return boolean
+ */
+ public boolean isSheetVeryHidden(int sheetIx) {
+ Object[] veryHiddedSheetIdxAry = getValuesFromWorkbookData(VERY_HIDDEN_SHEET_PATH);
+
+ for(Object o: veryHiddedSheetIdxAry){
+ int index = (Integer.parseInt(o.toString())-1);
+ if(index == sheetIx)return true;
+ }
+
+
+ return false;
+ }
+
+ /**
+ *
+ * Not supported right now, as StreamedWorkbook
+ * supports only reading.
+ *
+ * @exception UnsupportedOperationException
+ */
+ public void setSheetHidden(int sheetIx, boolean hidden) {
+ throw new UnsupportedOperationException("Not supported due to memory footprint");
+ }
+
+ /**
+ *
+ * Not supported right now, as StreamedWorkbook
+ * supports only reading.
+ *
+ * @exception UnsupportedOperationException
+ */
+ public void setSheetHidden(int sheetIx, int hidden) {
+ throw new UnsupportedOperationException("Not supported due to memory footprint");
+ }
+
+ /**
+ *
+ * Not supported right now, as StreamedWorkbook
+ * supports only reading.
+ *
+ * @exception UnsupportedOperationException
+ */
+ public void addToolPack(UDFFinder toopack) {
+ throw new UnsupportedOperationException("Not supported due to memory footprint");
+ }
+
+ /**
+ *
+ * Not supported right now, as StreamedWorkbook
+ * supports only reading.
+ *
+ * @exception UnsupportedOperationException
+ */
+ public void setForceFormulaRecalculation(boolean value) {
+ throw new UnsupportedOperationException("Not supported due to memory footprint");
+ }
+
+ /**
+ *
+ * Will be supported in the future.
+ *
+ *
+ */
+ public boolean getForceFormulaRecalculation() {
+ throw new UnsupportedOperationException("Not implemented yet");
+ }
+
+ /**
+ *
+ * Will be supported in the future.
+ *
+ * @exception UnsupportedOperationException
+ */
+ public SpreadsheetVersion getSpreadsheetVersion() {
+ throw new UnsupportedOperationException("Not implemented yet");
+ }
+
+
+ /**
+ *
+ * Use Iterator getSheetIterator() instead.
+ *
+ * @exception UnsupportedOperationException
+ */
+ public Iterator iterator() {
+ throw new UnsupportedOperationException("Not implemented yet. Use getSheetIterator()");
+ }
+
+ /**
+ *
+ * Returns the visibility of sheet for the given index.
+ *
+ * @return SheetVisibility[Hidden/Visible/VeryHidden]
+ */
+ public SheetVisibility getSheetVisibility(int sheetIx) {
+ Object[] hiddenSheetIdxAry = getValuesFromWorkbookData(HIDDEN_SHEET_PATH);
+
+ for(Object o: hiddenSheetIdxAry){
+ int index = (Integer.parseInt(o.toString())-1);
+ if(index == sheetIx)return SheetVisibility.HIDDEN;
+ }
+
+ Object[] visibleSheetIdxAry = getValuesFromWorkbookData(VISIBLE_SHEET_PATH);
+
+ for(Object o: visibleSheetIdxAry){
+ int index = (Integer.parseInt(o.toString())-1);
+ if(index == sheetIx)return SheetVisibility.VISIBLE;
+ }
+
+ Object[] veryHiddedSheetIdxAry = getValuesFromWorkbookData(VERY_HIDDEN_SHEET_PATH);
+
+ for(Object o: veryHiddedSheetIdxAry){
+ int index = (Integer.parseInt(o.toString())-1);
+ if(index == sheetIx)return SheetVisibility.VERY_HIDDEN;
+ }
+
+ /**
+ * TO DO: Very Hidden
+ */
+
+ throw new IllegalArgumentException("Invalid sheet index");
+
+
+ }
+
+ /**
+ *
+ * Not supported right now, as StreamedWorkbook
+ * supports only reading.
+ *
+ * @exception UnsupportedOperationException
+ */
+ public void setSheetVisibility(int sheetIx, SheetVisibility visibility) {
+ throw new UnsupportedOperationException("Not supported due to memory footprint");
+ }
+
+ /**
+ *
+ * Not supported right now, as StreamedWorkbook
+ * supports only reading.
+ *
+ * @exception UnsupportedOperationException
+ */
+ public int addOlePackage(byte[] oleData, String label, String fileName,
+ String command) throws IOException {
+ throw new UnsupportedOperationException("Not supported due to memory footprint");
+ }
+
+ /**
+ * Strictly for reading any specific value from any node in workbook.xml
+ * DOM Parser is used here assuming that workbook.xml will never contribute
+ * towards the size of excel file.
+ *
+ * @param xml
+ * @param xpathQuery
+ * @return Object[]
+ * @throws Exception
+ */
+ private Object[] getValuesFromWorkbookData(String xpathQuery){
+ DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
+ List valueAry = new ArrayList();
+ DocumentBuilder builder;
+ Document doc = null;
+ NodeList nodeList = null;
+ try{
+ builder = factory.newDocumentBuilder();
+ doc = builder.parse(globalReader.getWorkbookData());
+ XPathFactory xPathfactory = XPathFactory.newInstance();
+ XPath xpath = xPathfactory.newXPath();
+ doc.normalize();
+
+
+ XPathExpression xPathExpr = xpath.compile(xpathQuery);
+ nodeList = (NodeList)xPathExpr.evaluate(doc, XPathConstants.NODESET);
+
+ for(int i=0; i streamedSheetIterator = streamedWorkbook.getSheetIterator();
+
+ while(streamedSheetIterator.hasNext()){
+ streamedSheetIterator.next();
+ streamedSheetcount++;
+ }
+
+ Iterator sheetIterator = workbook.sheetIterator();
+
+ while(sheetIterator.hasNext()){
+ sheetIterator.next();
+ count++;
+ }
+
+ assertEquals(count, streamedSheetcount);
+ } catch (Exception e) {
+
+ }
+
+ streamedWorkbook.close();
+ workbook.close();
+
+ }
+
+ @Test
+ public void testTotalNumberOfSheets() throws Exception{
+ POIDataSamples files = POIDataSamples.getSpreadSheetInstance();
+ File f= files.getFile("SpreadSheetSample04022017.xlsx");
+ StreamedWorkbook streamedWorkbook = new StreamedWorkbook(f.getAbsolutePath());
+ Workbook workbook = new XSSFWorkbook(f);
+
+ try {
+ int sheetCount = workbook.getNumberOfSheets();
+ int streamedSheetCount = streamedWorkbook.getNumberOfSheets();
+
+ assertEquals(sheetCount, streamedSheetCount);
+ } catch (Exception e) {
+ }
+
+ workbook.close();
+ streamedWorkbook.close();
+ }
+
+ @Test
+ public void testTotalRowCount() throws Exception{
+ POIDataSamples files = POIDataSamples.getSpreadSheetInstance();
+ File f= files.getFile("SpreadSheetSample04022017.xlsx");
+ StreamedWorkbook streamedWorkbook = new StreamedWorkbook(f.getAbsolutePath());
+ Workbook workbook = new XSSFWorkbook(f);
+ int streamedSheetCount = 0;
+
+ long streamedRowCount = 0;
+
+ Iterator streamedSheetIterator = streamedWorkbook.getSheetIterator();
+
+ while((streamedSheetIterator.hasNext()) && (streamedSheetCount == 0)){
+ StreamedSheet sheet = streamedSheetIterator.next();
+
+ Iterator rows = sheet.getAllRows();
+
+ while(rows.hasNext()){
+ rows.next();
+ streamedRowCount++;
+ }
+
+ streamedSheetCount++;
+ }
+
+ int sheetCount= 0;
+ int rowCount = 0;
+ Iterator sheetIterator = workbook.sheetIterator();
+ while(sheetIterator.hasNext() && sheetCount ==0){
+ Sheet sheet = sheetIterator.next();
+ Iterator rowIterator = sheet.iterator();
+
+ while(rowIterator.hasNext()){
+ rowIterator.next();
+ rowCount++;
+ }
+
+ sheetCount++;
+ }
+
+ assertEquals(rowCount, streamedRowCount);
+
+ streamedWorkbook.close();
+ workbook.close();
+ }
+
+ @Test
+ public void testNRowCount() throws Exception{
+ POIDataSamples files = POIDataSamples.getSpreadSheetInstance();
+ File f= files.getFile("SpreadSheetSample04022017.xlsx");
+ StreamedWorkbook workbook = new StreamedWorkbook(f.getAbsolutePath());
+
+ long count = 0;
+ int sheetCount = 0;
+
+ Iterator sheetIterator = workbook.getSheetIterator();
+
+ while((sheetIterator.hasNext()) && (sheetCount == 0)){
+ StreamedSheet sheet = sheetIterator.next();
+
+ Iterator rows = sheet.getNRows(4);
+
+ while(rows.hasNext()){
+ rows.next();
+ count++;
+ }
+
+ assertEquals(4, count);
+ sheetCount++;
+ }
+
+ workbook.close();
+ }
+
+ @Test
+ public void testTotalCellCount() throws Exception{
+ POIDataSamples files = POIDataSamples.getSpreadSheetInstance();
+ File f= files.getFile("SpreadSheetSample04022017.xlsx");
+ StreamedWorkbook streamedWorkbook = new StreamedWorkbook(f.getAbsolutePath());
+ Workbook workbook = new XSSFWorkbook(f);
+ int streamedSheetCount = 0;
+ long streamedCellCount = 0;
+ long cellCount = 0;
+
+ Iterator streamedSheetIterator = streamedWorkbook.getSheetIterator();
+
+ while((streamedSheetIterator.hasNext()) && (streamedSheetCount == 0)){
+ StreamedSheet streamedSheet = streamedSheetIterator.next();
+
+ Iterator streamedRows = streamedSheet.getAllRows();
+
+ while(streamedRows.hasNext()){
+ StreamedRow streamedRow = streamedRows.next();
+
+ Iterator streamedCellIterator = streamedRow.getCellIterator();
+
+ while(streamedCellIterator.hasNext()){
+ streamedCellIterator.next();
+ streamedCellCount++;
+ }
+
+ }
+
+ streamedSheetCount++;
+ }
+
+ int sheetCount = 0;
+ Iterator sheetIterator = workbook.sheetIterator();
+
+ while((sheetIterator.hasNext()) && (sheetCount == 0)){
+ Sheet sheet = sheetIterator.next();
+
+ Iterator rowIterator = sheet.iterator();
+ while(rowIterator.hasNext()){
+ Row row = rowIterator.next();
+
+ Iterator cellIterator = row.cellIterator();
+
+ while(cellIterator.hasNext()){
+ cellIterator.next();
+ cellCount++;
+ }
+ }
+
+ sheetCount++;
+ }
+
+ assertEquals(cellCount, streamedCellCount);
+
+ streamedWorkbook.close();
+ workbook.close();
+ }
+
+
+ @Test
+ public void testStartingRowAndCellNumber() throws Exception{
+ POIDataSamples files = POIDataSamples.getSpreadSheetInstance();
+ File f= files.getFile("SpreadSheetSample04022017.xlsx");
+ StreamedWorkbook streamedWorkbook = new StreamedWorkbook(f.getAbsolutePath());
+ Workbook workbook = new XSSFWorkbook(f);
+
+ List workbookStartingCellNumbers = new ArrayList();
+ List streamedWorkbookStartingCellNumbers = new ArrayList();
+
+ int workbookStartingRowNum = 0;
+ int streamedWorkbookStartingRowNumber = 0;
+
+ Sheet sheet = workbook.getSheetAt(0);
+ Iterator rowIterator = sheet.iterator();
+
+ int workbookRowCount = 0;
+
+ while(rowIterator.hasNext()){
+ Row row = rowIterator.next();
+ if(workbookRowCount == 0){
+ workbookStartingRowNum = row.getRowNum();
+ workbookRowCount++;
+ }
+
+ if(row != null)
+ workbookStartingCellNumbers.add(row.getFirstCellNum());
+
+ }
+
+ StreamedSheet streamedSheet = (StreamedSheet)streamedWorkbook.getSheetAt(0);
+ Iterator streamedRowIterator = streamedSheet.getAllRows();
+ int streamedWorkbookRowCount = 0;
+
+ while(streamedRowIterator.hasNext()){
+ StreamedRow row = streamedRowIterator.next();
+ if(streamedWorkbookRowCount == 0){
+ streamedWorkbookStartingRowNumber = row.getRowNum();
+ streamedWorkbookRowCount++;
+ }
+
+ if(row != null)
+ streamedWorkbookStartingCellNumbers.add(row.getFirstCellNum());
+ }
+
+ assertEquals(workbookStartingRowNum, streamedWorkbookStartingRowNumber);
+ assertEquals(workbookStartingCellNumbers, streamedWorkbookStartingCellNumbers);
+
+ workbook.close();
+ streamedWorkbook.close();
+ }
+
+ @Test
+ public void testSheetData() throws Exception{
+ POIDataSamples files = POIDataSamples.getSpreadSheetInstance();
+ File f= files.getFile("SpreadSheetSample04022017.xlsx");
+ StreamedWorkbook workbook = new StreamedWorkbook(f.getAbsolutePath());
+ int sheetCount = 0;
+
+
+ Iterator sheetIterator = workbook.getSheetIterator();
+
+ while((sheetIterator.hasNext()) && (sheetCount == 0)){
+ int rowCount = 0;
+
+ StreamedSheet sheet = sheetIterator.next();
+
+ Iterator rows = sheet.getAllRows();
+
+ while(rows.hasNext()){
+
+ StreamedRow row = rows.next();
+
+ int cellCount = 0;
+
+ Iterator cellIterator = row.getCellIterator();
+
+ while(cellIterator.hasNext()){
+ StreamedCell cell = cellIterator.next();
+ if(rowCount == 1){
+
+ if(cellCount == 0){
+ assertEquals("1", cell.getValue());
+ }else if(cellCount == 1){
+ assertEquals("Item1", cell.getValue());
+ }else if(cellCount == 2){
+ assertEquals("201", cell.getValue());
+ }else if(cellCount == 3){
+ assertEquals("100.11", cell.getValue());
+ }else if(cellCount == 4){
+ assertEquals("TRUE", cell.getValue());
+ }else if(cellCount == 5){
+ assertEquals("04/02/1917", cell.getValue());
+ }else if(cellCount == 6){
+ assertEquals("90.11", cell.getValue());
+ }
+ }else if(rowCount == 3){
+ if(cellCount == 4){
+ assertEquals(null, cell.getValue());
+ }
+ }
+
+ cellCount++;
+
+ }
+
+
+ rowCount++;
+
+ }
+
+ sheetCount++;
+ }
+
+ workbook.close();
+ }
+
+ @Test
+ public void testBatchData() throws Exception{
+
+ POIDataSamples files = POIDataSamples.getSpreadSheetInstance();
+ File f= files.getFile("SpreadSheetSample04022017.xlsx");
+ StreamedWorkbook workbook = new StreamedWorkbook(f.getAbsolutePath());
+ int sheetCount = 0;
+
+
+ Iterator sheetIterator = workbook.getSheetIterator();
+
+ while(sheetIterator.hasNext()){
+
+ StreamedSheet sheet = sheetIterator.next();
+
+ if(sheetCount == 1){
+ Iterator rows = sheet.getNRows(1);
+
+ while(rows.hasNext()){
+ StreamedRow row = rows.next();
+ assertEquals("Row Number:0 --> Item | item description | Strore | Price | Promotion applied | MFD | Discount rate |", row.toString().trim());
+ }
+
+
+ rows = sheet.getNRows(4);
+
+ while(rows.hasNext()){
+ StreamedRow row = rows.next();
+ assertEquals("Row Number:1 --> 1 | Item1 | 201 | 100.11 | TRUE | 04/02/1917 | 90.11 |", row.toString().trim());
+ row = rows.next();
+ assertEquals("Row Number:2 --> 2 | Item2 | 202 | 101.11 | TRUE | 05/02/1917 | 91.11 |", row.toString().trim());
+ row = rows.next();
+ assertEquals("Row Number:3 --> 3 | Item3 | 203 | 102.11 | TRUE | 06/02/1917 | 92.11 |", row.toString().trim());
+ row = rows.next();
+ assertEquals("Row Number:4 --> 4 | Item4 | 204 | 103.11 | TRUE | 07/02/1917 | 93.11 |", row.toString().trim());
+ }
+
+
+
+ rows = sheet.getNRows(4);
+
+ while(rows.hasNext()){
+ StreamedRow row = rows.next();
+ assertEquals("Row Number:5 --> 5 | Item5 | 205 | 104.11 | TRUE | 08/02/1917 | 94.11 |", row.toString().trim());
+ row = rows.next();
+ assertEquals("Row Number:6 --> 6 | Item6 | 206 | 105.11 | TRUE | 09/02/1917 | 95.11 |", row.toString().trim());
+ row = rows.next();
+ assertEquals("Row Number:7 --> 7 | Item7 | 207 | 106.11 | TRUE | 10/02/1917 | 96.11 |", row.toString().trim());
+ row = rows.next();
+ assertEquals("Row Number:8 --> 8 | Item8 | 208 | 107.11 | FALSE | 11/02/1917 | 97.11 |", row.toString().trim());
+ }
+
+ }
+
+
+ sheetCount++;
+
+ }
+
+ workbook.close();
+
+ }
+
+
+ @Test
+ public void testGetCell() throws Exception{
+ POIDataSamples files = POIDataSamples.getSpreadSheetInstance();
+ File f= files.getFile("SpreadSheetSample04022017.xlsx");
+ StreamedWorkbook workbook = new StreamedWorkbook(f.getAbsolutePath());
+ int sheetCount = 0;
+
+
+ Iterator sheetIterator = workbook.getSheetIterator();
+
+ while((sheetIterator.hasNext()) && (sheetCount == 0)){
+
+ StreamedSheet sheet = sheetIterator.next();
+
+ Iterator rows = sheet.getAllRows();
+
+ while(rows.hasNext()){
+
+ StreamedRow row = rows.next();
+
+ if(row.getRowNum() == 3){
+ StreamedCell cell = (StreamedCell)row.getCell(5);
+ assertEquals("06/02/1917", cell.getValue());
+
+ try{
+ cell = (StreamedCell)row.getCell(10);
+ }catch(Exception exception){
+ assertEquals(true, exception instanceof IndexOutOfBoundsException);
+ }
+ }
+
+
+ }
+
+ sheetCount++;
+ }
+
+ workbook.close();
+ }
+
+
+ @Test
+ public void testGetFirstAndLastCellNum() throws Exception{
+ POIDataSamples files = POIDataSamples.getSpreadSheetInstance();
+ File f= files.getFile("SpreadSheetSample04022017.xlsx");
+ StreamedWorkbook workbook = new StreamedWorkbook(f.getAbsolutePath());
+ int sheetCount = 0;
+
+
+ Iterator sheetIterator = workbook.getSheetIterator();
+
+ while((sheetIterator.hasNext()) && (sheetCount == 0)){
+
+ StreamedSheet sheet = sheetIterator.next();
+
+ Iterator rows = sheet.getAllRows();
+
+ while(rows.hasNext()){
+
+ StreamedRow row = rows.next();
+
+ if(row.getRowNum() == 3){
+ StreamedCell cell = (StreamedCell)row.getCell(row.getFirstCellNum());
+ assertEquals("3", cell.getValue());
+ assertEquals("3", cell.getStringCellValue());
+
+ cell = (StreamedCell)row.getCell(row.getLastCellNum()-1);
+ assertEquals("92.11", cell.getValue());
+ assertEquals("92.11", cell.getStringCellValue());
+ }
+ }
+
+ sheetCount++;
+ }
+
+ workbook.close();
+ }
+
+ @Test
+ public void testReopenClosedWorkbook(){
+
+ try{
+ POIDataSamples files = POIDataSamples.getSpreadSheetInstance();
+ File f= files.getFile("SpreadSheetSample04022017.xlsx");
+ StreamedWorkbook workbook = new StreamedWorkbook(f.getAbsolutePath());
+
+ workbook.close();
+
+ Iterator sheetIterator = workbook.getSheetIterator();
+ }catch(Exception e){
+ assertEquals("Workbook already closed", e.getMessage());
+ }
+
+
+ }
+
+ @Test
+ public void testGetActiveSheetIndex() throws Exception{
+ POIDataSamples files = POIDataSamples.getSpreadSheetInstance();
+ File f= files.getFile("SpreadSheetSample04022017.xlsx");
+ StreamedWorkbook streamedWorkbook = new StreamedWorkbook(f.getAbsolutePath());
+
+ Workbook workbook = new XSSFWorkbook(f);
+
+ assertEquals(workbook.getActiveSheetIndex(), streamedWorkbook.getActiveSheetIndex());
+ streamedWorkbook.close();
+ workbook.close();
+ }
+
+ @Test
+ public void testGetSheetNameWithSheetIndex() throws Exception{
+ POIDataSamples files = POIDataSamples.getSpreadSheetInstance();
+ File f= files.getFile("SpreadSheetSample04022017.xlsx");
+ StreamedWorkbook streamedWorkbook = new StreamedWorkbook(f.getAbsolutePath());
+
+ Workbook workbook = new XSSFWorkbook(f);
+
+ assertEquals(workbook.getSheetName(1), streamedWorkbook.getSheetName(1));
+ streamedWorkbook.close();
+ workbook.close();
+ }
+
+ @Test
+ public void testGetSheetIndex() throws Exception{
+ POIDataSamples files = POIDataSamples.getSpreadSheetInstance();
+ File f= files.getFile("SpreadSheetSample04022017.xlsx");
+ StreamedWorkbook streamedWorkbook = new StreamedWorkbook(f.getAbsolutePath());
+
+ Workbook workbook = new XSSFWorkbook(f);
+
+ assertEquals(workbook.getSheetIndex("Sheet3"), streamedWorkbook.getSheetIndex("Sheet3"));
+ streamedWorkbook.close();
+ workbook.close();
+ }
+
+ @Test
+ public void testGetFirstVisibleTab() throws Exception{
+ POIDataSamples files = POIDataSamples.getSpreadSheetInstance();
+ File f= files.getFile("SpreadSheetSample04022017.xlsx");
+ StreamedWorkbook streamedWorkbook = new StreamedWorkbook(f.getAbsolutePath());
+
+ Workbook workbook = new XSSFWorkbook(f);
+
+ assertEquals(workbook.getFirstVisibleTab(), streamedWorkbook.getFirstVisibleTab());
+ streamedWorkbook.close();
+ workbook.close();
+ }
+
+ @Test
+ public void testGetSheetName() throws Exception{
+ POIDataSamples files = POIDataSamples.getSpreadSheetInstance();
+ File f= files.getFile("SpreadSheetSample04022017.xlsx");
+ StreamedWorkbook streamedWorkbook = new StreamedWorkbook(f.getAbsolutePath());
+
+ List workbookSheetNames = new ArrayList();
+ List streamedWorkbookSheetNames = new ArrayList();
+
+ Workbook workbook = new XSSFWorkbook(f);
+
+ Iterator sheetIterator = workbook.sheetIterator();
+
+ while(sheetIterator.hasNext()){
+ workbookSheetNames.add(sheetIterator.next().getSheetName());
+ }
+
+
+
+ Iterator streamedSheetIterator = streamedWorkbook.getSheetIterator();
+
+ while(streamedSheetIterator.hasNext()){
+ streamedWorkbookSheetNames.add(streamedSheetIterator.next().getSheetName());
+ }
+
+
+ assertEquals(workbookSheetNames, streamedWorkbookSheetNames);
+
+ streamedWorkbook.close();
+ workbook.close();
+ }
+
+ @Test
+ public void testIsSheetHidden() throws Exception{
+ POIDataSamples files = POIDataSamples.getSpreadSheetInstance();
+ File f= files.getFile("SpreadSheetSample04022017.xlsx");
+ StreamedWorkbook streamedWorkbook = new StreamedWorkbook(f.getAbsolutePath());
+ Workbook workbook = new XSSFWorkbook(f);
+
+
+ assertEquals(workbook.isSheetHidden(2), streamedWorkbook.isSheetHidden(2));
+ assertEquals(workbook.isSheetHidden(1), streamedWorkbook.isSheetHidden(1));
+
+ streamedWorkbook.close();
+ workbook.close();
+
+ }
+
+ @Test
+ public void testGetSheetVisibility() throws Exception{
+ POIDataSamples files = POIDataSamples.getSpreadSheetInstance();
+ File f= files.getFile("SpreadSheetSample04022017.xlsx");
+ StreamedWorkbook streamedWorkbook = new StreamedWorkbook(f.getAbsolutePath());
+ Workbook workbook = new XSSFWorkbook(f);
+
+
+ assertEquals(workbook.getSheetVisibility(2), streamedWorkbook.getSheetVisibility(2));
+ assertEquals(workbook.getSheetVisibility(1), streamedWorkbook.getSheetVisibility(1));
+
+ streamedWorkbook.close();
+ workbook.close();
+
+ }
+
+ @Test
+ public void testGetSheet() throws Exception{
+ POIDataSamples files = POIDataSamples.getSpreadSheetInstance();
+ File f= files.getFile("SpreadSheetSample04022017.xlsx");
+ StreamedWorkbook streamedWorkbook = new StreamedWorkbook(f.getAbsolutePath());
+ Workbook workbook = new XSSFWorkbook(f);
+
+ Sheet sheet = workbook.getSheet("Sheet2");
+ Sheet streamedSheet = streamedWorkbook.getSheet("Sheet2");
+
+ assertEquals(sheet.getSheetName(), streamedSheet.getSheetName());
+
+ streamedWorkbook.close();
+ workbook.close();
+
+ }
+
+ @Test(expected=NullPointerException.class)
+ public void testGetSheetAfterClose() throws Exception{
+ POIDataSamples files = POIDataSamples.getSpreadSheetInstance();
+ File f= files.getFile("SpreadSheetSample04022017.xlsx");
+ StreamedWorkbook streamedWorkbook = new StreamedWorkbook(f.getAbsolutePath());
+ streamedWorkbook.close();
+ Sheet streamedSheet = streamedWorkbook.getSheet("Sheet2");
+ System.out.println(streamedSheet.getSheetName());
+ }
+
+ @Test
+ public void testGetSheetAt() throws Exception{
+ POIDataSamples files = POIDataSamples.getSpreadSheetInstance();
+ File f= files.getFile("SpreadSheetSample04022017.xlsx");
+ StreamedWorkbook streamedWorkbook = new StreamedWorkbook(f.getAbsolutePath());
+ Workbook workbook = new XSSFWorkbook(f);
+
+ Sheet sheet = workbook.getSheetAt(0);
+ Sheet streamedSheet = streamedWorkbook.getSheetAt(0);
+
+ assertEquals(sheet.getSheetName(), streamedSheet.getSheetName());
+
+ streamedWorkbook.close();
+ workbook.close();
+
+ }
+
+ @Test(expected=NullPointerException.class)
+ public void testGetSheetAtAfterClose() throws Exception{
+ POIDataSamples files = POIDataSamples.getSpreadSheetInstance();
+ File f= files.getFile("SpreadSheetSample04022017.xlsx");
+ StreamedWorkbook streamedWorkbook = new StreamedWorkbook(f.getAbsolutePath());
+ streamedWorkbook.close();
+
+ Sheet streamedSheet = streamedWorkbook.getSheetAt(0);
+ System.out.println(streamedSheet.getSheetName());
+
+ }
+
+
+ @Test(expected=NullPointerException.class)
+ public void testGetSheetException() throws Exception{
+ POIDataSamples files = POIDataSamples.getSpreadSheetInstance();
+ File f= files.getFile("SpreadSheetSample04022017.xlsx");
+ StreamedWorkbook streamedWorkbook = new StreamedWorkbook(f.getAbsolutePath());
+
+ Sheet sheet = streamedWorkbook.getSheet("Sheet200");
+
+ sheet.getSheetName();
+
+ streamedWorkbook.close();
+
+ }
+
+ @Test
+ public void testGetFirstCellNumber() throws Exception{
+ POIDataSamples files = POIDataSamples.getSpreadSheetInstance();
+ File f= files.getFile("SpreadSheetSample04022017.xlsx");
+ StreamedWorkbook streamedWorkbook = new StreamedWorkbook(f.getAbsolutePath());
+ Workbook workbook = new XSSFWorkbook(f);
+
+ List workbookFirstCellNumList = new ArrayList();
+ List sworkbookFirstCellNumList = new ArrayList();
+
+ Iterator workbookSheetIterator = workbook.iterator();
+ while(workbookSheetIterator.hasNext()){
+ Sheet sheet = workbookSheetIterator.next();
+ Iterator rowIterator = sheet.iterator();
+ while(rowIterator.hasNext()){
+ Row row = rowIterator.next();
+ workbookFirstCellNumList.add(row.getFirstCellNum());
+ }
+ }
+
+
+ Iterator sworkbookSheetIterator = streamedWorkbook.getSheetIterator();
+ while(sworkbookSheetIterator.hasNext()){
+ StreamedSheet sheet = sworkbookSheetIterator.next();
+ Iterator rowIterator = sheet.getAllRows();
+ while(rowIterator.hasNext()){
+ Row row = rowIterator.next();
+ sworkbookFirstCellNumList.add(row.getFirstCellNum());
+ }
+ }
+
+
+ assertEquals(workbookFirstCellNumList, sworkbookFirstCellNumList);
+
+ streamedWorkbook.close();
+ workbook.close();
+
+ }
+
+ @Test
+ public void testGetLastCellNumber() throws Exception{
+ POIDataSamples files = POIDataSamples.getSpreadSheetInstance();
+ File f= files.getFile("SpreadSheetSample04022017.xlsx");
+ StreamedWorkbook streamedWorkbook = new StreamedWorkbook(f.getAbsolutePath());
+ Workbook workbook = new XSSFWorkbook(f);
+
+ List workbookLastCellNumList = new ArrayList();
+ List sworkbookLastCellNumList = new ArrayList();
+
+ Iterator workbookSheetIterator = workbook.iterator();
+ while(workbookSheetIterator.hasNext()){
+ Sheet sheet = workbookSheetIterator.next();
+ Iterator rowIterator = sheet.iterator();
+ while(rowIterator.hasNext()){
+ Row row = rowIterator.next();
+ workbookLastCellNumList.add(row.getLastCellNum());
+ }
+ }
+
+
+ Iterator sworkbookSheetIterator = streamedWorkbook.getSheetIterator();
+ while(sworkbookSheetIterator.hasNext()){
+ StreamedSheet sheet = sworkbookSheetIterator.next();
+ Iterator rowIterator = sheet.getAllRows();
+ while(rowIterator.hasNext()){
+ Row row = rowIterator.next();
+ sworkbookLastCellNumList.add(row.getLastCellNum());
+ }
+ }
+
+
+ assertEquals(workbookLastCellNumList, sworkbookLastCellNumList);
+
+ streamedWorkbook.close();
+ workbook.close();
+
+ }
+
+ @Test
+ public void testGetPhysicalNumOfCells() throws Exception{
+ POIDataSamples files = POIDataSamples.getSpreadSheetInstance();
+ File f= files.getFile("SpreadSheetSample04022017.xlsx");
+ StreamedWorkbook streamedWorkbook = new StreamedWorkbook(f.getAbsolutePath());
+ Workbook workbook = new XSSFWorkbook(f);
+
+ List workbookPhysicalCellNums = new ArrayList();
+ List sworkbookPhysicalCellNums = new ArrayList();
+
+ Iterator workbookSheetIterator = workbook.iterator();
+ while(workbookSheetIterator.hasNext()){
+ Sheet sheet = workbookSheetIterator.next();
+ Iterator rowIterator = sheet.iterator();
+ while(rowIterator.hasNext()){
+ Row row = rowIterator.next();
+ workbookPhysicalCellNums.add(row.getPhysicalNumberOfCells());
+ }
+ }
+
+
+ Iterator sworkbookSheetIterator = streamedWorkbook.getSheetIterator();
+ while(sworkbookSheetIterator.hasNext()){
+ StreamedSheet sheet = sworkbookSheetIterator.next();
+ Iterator rowIterator = sheet.getAllRows();
+ while(rowIterator.hasNext()){
+ Row row = rowIterator.next();
+ sworkbookPhysicalCellNums.add(row.getPhysicalNumberOfCells());
+ }
+ }
+
+
+ assertEquals(workbookPhysicalCellNums, sworkbookPhysicalCellNums);
+
+ streamedWorkbook.close();
+ workbook.close();
+
+ }
+
+
+
+
+}
diff --git a/test-data/spreadsheet/SpreadSheetSample04022017.xlsx b/test-data/spreadsheet/SpreadSheetSample04022017.xlsx
new file mode 100644
index 0000000..e1da613
Binary files /dev/null and b/test-data/spreadsheet/SpreadSheetSample04022017.xlsx differ
|