private String cellToStr( KCell cell ) { String result = "null"; if ( cell != null ) { result = cell.getContents(); } return result; } }
private void jumpToNextFile() throws KettleException { data.sheetnr = 0; // Reset the start-row: data.rownr = -1; // no previous row yet, don't take it from the previous sheet! (that // whould be plain wrong!) data.previousRow = null; // Close the openFile! data.workbook.close(); data.workbook = null; // marker to open again. data.errorHandler.close(); // advance to the next file! data.filenr++; }
int nrSheets = workbook.getNumberOfSheets(); for ( int j = 0; j < nrSheets; j++ ) { KSheet sheet = workbook.getSheet( j ); sheetIndex = 0; } else { sheetIndex = Const.indexOfString( sheet.getName(), info.getSheetName() ); KCell cell = sheet.getCell( colnr, rownr ); if ( cell == null ) { stop = true; } else { if ( cell.getType() != KCellType.EMPTY ) { fieldname = cell.getContents(); KCell below = sheet.getCell( colnr, rownr + 1 ); if ( below.getType() == KCellType.BOOLEAN ) { fieldtype = ValueMetaInterface.TYPE_BOOLEAN; } else if ( below.getType() == KCellType.DATE ) { fieldtype = ValueMetaInterface.TYPE_DATE; } else if ( below.getType() == KCellType.LABEL ) { fieldtype = ValueMetaInterface.TYPE_STRING; } else if ( below.getType() == KCellType.NUMBER ) { fieldtype = ValueMetaInterface.TYPE_NUMBER; } else {
KCellType cellType = cell.getType(); if ( KCellType.BOOLEAN == cellType || KCellType.BOOLEAN_FORMULA == cellType ) { r[rowcolumn] = cell.getValue(); sourceMeta = data.valueMetaBoolean; } else { if ( KCellType.DATE.equals( cellType ) || KCellType.DATE_FORMULA.equals( cellType ) ) { Date date = (Date) cell.getValue(); long time = date.getTime(); int offset = TimeZone.getDefault().getOffset( time ); } else { if ( KCellType.LABEL == cellType || KCellType.STRING_FORMULA == cellType ) { String string = (String) cell.getValue(); switch ( meta.getField()[rowcolumn].getTrimType() ) { case ExcelInputMeta.TYPE_TRIM_LEFT: } else { if ( KCellType.NUMBER == cellType || KCellType.NUMBER_FORMULA == cellType ) { r[rowcolumn] = cell.getValue(); sourceMeta = data.valueMetaNumber; } else { if ( log.isDetailed() ) { KCellType ct = cell.getType(); logDetailed( BaseMessages.getString( PKG, "ExcelInput.Log.UnknownType", ( ( ct != null ) ? ct .toString() : "null" ), cell.getContents() ) ); ex = new KettleCellValueException( ex, this.data.sheetnr, cell.getRow(), i, field.getName() ); throw ex;
@Test public void testReadData() throws Exception { KSheet sheet1 = getSampleSheet(); assertEquals( 5, sheet1.getRows() ); KCell[] row = sheet1.getRow( 2 ); assertEquals( KCellType.LABEL, row[ 1 ].getType() ); assertEquals( "One", row[ 1 ].getValue() ); assertEquals( KCellType.DATE, row[ 2 ].getType() ); assertEquals( new Date( 1283817600000L ), row[ 2 ].getValue() ); assertEquals( KCellType.NUMBER, row[ 3 ].getType() ); assertEquals( Double.valueOf( "75" ), row[ 3 ].getValue() ); assertEquals( KCellType.BOOLEAN, row[ 4 ].getType() ); assertEquals( Boolean.TRUE, row[ 4 ].getValue() ); assertEquals( KCellType.NUMBER_FORMULA, row[ 5 ].getType() ); assertEquals( Double.valueOf( "75" ), row[ 5 ].getValue() ); row = sheet1.getRow( 3 ); assertEquals( KCellType.LABEL, row[ 1 ].getType() ); assertEquals( "Two", row[ 1 ].getValue() ); assertEquals( KCellType.DATE, row[ 2 ].getType() ); assertEquals( new Date( 1283904000000L ), row[ 2 ].getValue() ); assertEquals( KCellType.NUMBER, row[ 3 ].getType() ); assertEquals( Double.valueOf( "42" ), row[ 3 ].getValue() ); assertEquals( KCellType.BOOLEAN, row[ 4 ].getType() ); assertEquals( Boolean.FALSE, row[ 4 ].getValue() ); assertEquals( KCellType.NUMBER_FORMULA, row[ 5 ].getType() ); assertEquals( Double.valueOf( "117" ), row[ 5 ].getValue() ); row = sheet1.getRow( 4 );
data.sheetNames = data.workbook.getSheetNames(); data.startColumn = new int[data.sheetNames.length]; data.startRow = new int[data.sheetNames.length]; KSheet sheet = data.workbook.getSheet( sheetName ); if ( sheet != null ) { KCell[] line = sheet.getRow( data.rownr ); ExcelInputRow excelInputRow = new ExcelInputRow( sheet.getName(), lineNr, line ); Object[] r = fillRow( data.colnr, excelInputRow ); if ( log.isRowLevel() ) { if ( data.rownr > sheet.getRows() ) { nextsheet = true;
@Test public void testReadCells() throws Exception { KSheet sheet = getSampleSheet(); KCell cell = sheet.getCell( 1, 2 ); assertEquals( "One", cell.getValue() ); assertEquals( KCellType.LABEL, cell.getType() ); cell = sheet.getCell( 2, 2 ); assertEquals( KCellType.DATE, cell.getType() ); assertEquals( new Date( 1283817600000L ), cell.getValue() ); cell = sheet.getCell( 1, 3 ); assertEquals( "Two", cell.getValue() ); assertEquals( KCellType.LABEL, cell.getType() ); }
.getEncoding(), wPassword.getText() ); int nrSheets = workbook.getNumberOfSheets(); for ( int j = 0; j < nrSheets; j++ ) { KSheet sheet = workbook.getSheet( j ); String sheetname = sheet.getName(); workbook.close(); } catch ( Exception e ) { new ErrorDialog(
return; switch ( cell.getType() ) { case BOOLEAN: if ( !( v.getType() == ValueMetaInterface.TYPE_STRING || v.getType() == ValueMetaInterface.TYPE_NONE || v .getType() == ValueMetaInterface.TYPE_DATE ) ) { throw new KettleException( BaseMessages.getString( PKG, "ExcelInput.Exception.InvalidTypeDate", cell .getContents(), v.getTypeDesc() ) ); || v.getType() == ValueMetaInterface.TYPE_NUMBER ) { throw new KettleException( BaseMessages.getString( PKG, "ExcelInput.Exception.InvalidTypeLabel", cell .getContents(), v.getTypeDesc() ) ); || v.getType() == ValueMetaInterface.TYPE_BIGNUMBER || v.getType() == ValueMetaInterface.TYPE_NUMBER ) ) { throw new KettleException( BaseMessages.getString( PKG, "ExcelInput.Exception.InvalidTypeNumber", cell .getContents(), v.getTypeDesc() ) ); .getType().getDescription(), cell.getContents() ) );
@Test public void testInlineString() throws Exception { final String sheetId = "1"; final String sheetName = "Sheet 1"; XSSFReader reader = mockXSSFReader( sheetId, SHEET_INLINE_STRINGS, mock( SharedStringsTable.class ), mock( StylesTable.class ) ); StaxPoiSheet spSheet = new StaxPoiSheet( reader, sheetName, sheetId ); KCell[] rowCells = spSheet.getRow( 0 ); assertEquals( "Test1", rowCells[ 0 ].getValue() ); assertEquals( KCellType.STRING_FORMULA, rowCells[ 0 ].getType() ); assertEquals( "Test2", rowCells[ 1 ].getValue() ); assertEquals( KCellType.STRING_FORMULA, rowCells[ 1 ].getType() ); rowCells = spSheet.getRow( 1 ); assertEquals( "value 1 1", rowCells[ 0 ].getValue() ); assertEquals( KCellType.STRING_FORMULA, rowCells[ 0 ].getType() ); assertEquals( "value 2 1", rowCells[ 1 ].getValue() ); assertEquals( KCellType.STRING_FORMULA, rowCells[ 1 ].getType() ); rowCells = spSheet.getRow( 2 ); assertEquals( "value 1 2", rowCells[ 0 ].getValue() ); assertEquals( KCellType.STRING_FORMULA, rowCells[ 0 ].getType() ); assertEquals( "value 2 2", rowCells[ 1 ].getValue() ); assertEquals( KCellType.STRING_FORMULA, rowCells[ 1 ].getType() ); }
@Test public void testRowColumnsCount() { String sameRowWidthSheet = "SameRowWidth"; String diffRowWidthSheet = "DifferentRowWidth"; checkRowCount( (OdfSheet) ods341.getSheet( sameRowWidthSheet ), 3, "Row count mismatch for ODF v3.4.1" ); checkRowCount( (OdfSheet) ods24.getSheet( sameRowWidthSheet ), 2, "Row count mismatch for ODF v2.4" ); checkRowCount( (OdfSheet) ods341.getSheet( diffRowWidthSheet ), 3, "Row count mismatch for ODF v3.4.1" ); checkRowCount( (OdfSheet) ods24.getSheet( diffRowWidthSheet ), 2, "Row count mismatch for ODF v2.4" ); checkCellCount( (OdfSheet) ods341.getSheet( sameRowWidthSheet ), 15, "Cell count mismatch for ODF v3.4.1" ); checkCellCount( (OdfSheet) ods24.getSheet( sameRowWidthSheet ), 1, "Cell count mismatch for ODF v2.4" ); checkCellCount( (OdfSheet) ods341.getSheet( diffRowWidthSheet ), new int[] { 15, 15, 12 }, "Cell count mismatch for ODF v3.4.1" ); checkCellCount( (OdfSheet) ods24.getSheet( diffRowWidthSheet ), new int[] { 3, 2 }, "Cell count mismatch for ODF v2.4" ); }
@Test public void testNullDateCell() throws Exception { // cell had null value instead of being null final String sheetId = "1"; final String sheetName = "Sheet 1"; XSSFReader reader = mockXSSFReader( sheetId, SHEET_DATE_NO_V, mockSharedStringsTable( "Some Date" ), mockStylesTable( Collections.singletonMap( 2, 165 ), Collections.singletonMap( 165, "M/D/YYYY" ) ) ); StaxPoiSheet spSheet = spy( new StaxPoiSheet( reader, sheetName, sheetId ) ); doReturn( true ).when( spSheet ).isDateCell( any() ); KCell cell = spSheet.getRow( 1 )[ 0 ]; assertNotNull( cell ); assertEquals( KCellType.DATE, cell.getType() ); cell = spSheet.getRow( 2 )[ 0 ]; assertNull( "cell must be null", cell ); }
@Test public void testReadEmptyRow() throws Exception { KSheet sheet1 = getSampleSheet(); KCell[] row = sheet1.getRow( 0 ); assertEquals( "empty row expected", 0, row.length ); }
@Test public void testNoUsedRangeSpecified() throws Exception { final String sheetId = "1"; final String sheetName = "Sheet 1"; SharedStringsTable sharedStringsTableMock = mockSharedStringsTable( "Report ID", "Report ID", "Approval Status", "Total Report Amount", "Policy", "ReportIdValue_1", "ReportIdValue_1", "ApprovalStatusValue_1", "PolicyValue_1" ); XSSFReader reader = mockXSSFReader( sheetId, SHEET_NO_USED_RANGE_SPECIFIED, sharedStringsTableMock, mock( StylesTable.class ) ); StaxPoiSheet spSheet = new StaxPoiSheet( reader, sheetName, sheetId ); // The first row is empty - it should have empty rowCells KCell[] rowCells = spSheet.getRow( 0 ); assertEquals( 0, rowCells.length ); // The second row - is the header - just skip it rowCells = spSheet.getRow( 1 ); assertEquals( 0, rowCells.length ); // The row3 - is the first row with data - validating it rowCells = spSheet.getRow( 2 ); assertEquals( KCellType.LABEL, rowCells[ 0 ].getType() ); assertEquals( "ReportIdValue_1", rowCells[ 0 ].getValue() ); assertEquals( KCellType.LABEL, rowCells[ 1 ].getType() ); assertEquals( "ReportIdValue_1", rowCells[ 1 ].getValue() ); assertEquals( KCellType.LABEL, rowCells[ 2 ].getType() ); assertEquals( "ApprovalStatusValue_1", rowCells[ 2 ].getValue() ); assertEquals( KCellType.NUMBER, rowCells[ 3 ].getType() ); assertEquals( 2623.0, rowCells[ 3 ].getValue() ); assertEquals( KCellType.LABEL, rowCells[ 4 ].getType() ); assertEquals( "PolicyValue_1", rowCells[ 4 ].getValue() ); }
private boolean isLineEmpty( KCell[] line ) { if ( line.length == 0 ) { return true; } boolean isEmpty = true; for ( int i = 0; i < line.length && isEmpty; i++ ) { if ( line[i] != null && !Utils.isEmpty( line[i].getContents() ) ) { isEmpty = false; } } return isEmpty; }
public void dispose( StepMetaInterface smi, StepDataInterface sdi ) { meta = (ExcelInputMeta) smi; data = (ExcelInputData) sdi; if ( data.workbook != null ) { data.workbook.close(); } if ( data.file != null ) { try { data.file.close(); } catch ( Exception e ) { // Ignore close errors } } try { data.errorHandler.close(); } catch ( KettleException e ) { if ( log.isDebug() ) { logDebug( BaseMessages.getString( PKG, "ExcelInput.Error.CouldNotCloseErrorHandler", e.toString() ) ); logDebug( Const.getStackTracker( e ) ); } } super.dispose( smi, sdi ); } }
.getEncoding(), wPassword.getText() ); processingWorkbook( fields, info, workbook ); workbook.close(); } catch ( Exception e ) { new ErrorDialog( shell, BaseMessages.getString( PKG, "System.Dialog.Error.Title" ), BaseMessages