Examples with KCell org.pentaho.di.core.spreadsheet.KCell used on opensource projects

Example 16 with KCell

use of org.pentaho.di.core.spreadsheet.KCell in project pentaho-kettle by pentaho.

the class StaxPoiSheetTest method testReadData.

@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);
    assertEquals(KCellType.LABEL, row[1].getType());
    assertEquals("Three", row[1].getValue());
    assertEquals(KCellType.DATE, row[2].getType());
    assertEquals(new Date(1283990400000L), row[2].getValue());
    assertEquals(KCellType.NUMBER, row[3].getType());
    assertEquals(Double.valueOf("93"), 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("210"), row[5].getValue());
    try {
        row = sheet1.getRow(5);
        fail("No out of bounds exception thrown when expected");
    } catch (ArrayIndexOutOfBoundsException e) {
    // OK!
    }
}

Example 17 with KCell

use of org.pentaho.di.core.spreadsheet.KCell in project pentaho-kettle by pentaho.

the class StaxPoiSheetTest method testNullDateCell.

@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);
}

Example 18 with KCell

use of org.pentaho.di.core.spreadsheet.KCell in project pentaho-kettle by pentaho.

the class ExcelInputDialogTest method getFieldsTest.

@Test
public /**
 * http://jira.pentaho.com/browse/PDI-13930
 */
void getFieldsTest() throws Exception {
    ExcelInputDialog dialog = Mockito.mock(ExcelInputDialog.class);
    RowMeta fields = new RowMeta();
    ExcelInputMeta info = Mockito.mock(ExcelInputMeta.class);
    Mockito.doReturn(true).when(info).readAllSheets();
    int[] startColumn = { 0 };
    Mockito.doReturn(startColumn).when(info).getStartColumn();
    int[] startRow = { 0 };
    Mockito.doReturn(startRow).when(info).getStartRow();
    KWorkbook workbook = Mockito.mock(KWorkbook.class);
    Mockito.doReturn(1).when(workbook).getNumberOfSheets();
    KSheet sheet = Mockito.mock(KSheet.class);
    Mockito.doReturn(sheet).when(workbook).getSheet(0);
    KCell cell = Mockito.mock(KCell.class);
    int fieldCount = 400;
    for (int i = 0; i <= fieldCount - 1; i++) {
        Mockito.doReturn(cell).when(sheet).getCell(i, 0);
        Mockito.doReturn(cell).when(sheet).getCell(i, 1);
    }
    Mockito.doReturn("testValue").when(cell).getContents();
    Mockito.doReturn(KCellType.NUMBER).when(cell).getType();
    PluginRegistry pluginRegistry = Mockito.mock(PluginRegistry.class);
    PluginInterface stringPlugin = Mockito.mock(PluginInterface.class);
    Mockito.doReturn(stringPlugin).when(pluginRegistry).getPlugin(ValueMetaPluginType.class, "1");
    Mockito.doReturn(Mockito.mock(ValueMetaInterface.class)).when(pluginRegistry).loadClass(stringPlugin, ValueMetaInterface.class);
    ValueMetaFactory.pluginRegistry = pluginRegistry;
    Method processingWorkbookMethod = ExcelInputDialog.class.getDeclaredMethod("processingWorkbook", RowMetaInterface.class, ExcelInputMeta.class, KWorkbook.class);
    processingWorkbookMethod.setAccessible(true);
    processingWorkbookMethod.invoke(dialog, fields, info, workbook);
    Assert.assertEquals(fieldCount, fields.size());
}

Example 19 with KCell

use of org.pentaho.di.core.spreadsheet.KCell in project pentaho-kettle by pentaho.

the class ExcelInputDialog method processingWorkbook.

/**
 * Processing excel workbook, filling fields
 *
 * @param fields RowMetaInterface for filling fields
 * @param info ExcelInputMeta
 * @param workbook excel workbook for processing
 * @throws KettlePluginException
 */
private void processingWorkbook(RowMetaInterface fields, ExcelInputMeta info, KWorkbook workbook) throws KettlePluginException {
    int nrSheets = workbook.getNumberOfSheets();
    for (int j = 0; j < nrSheets; j++) {
        KSheet sheet = workbook.getSheet(j);
        // See if it's a selected sheet:
        int sheetIndex;
        if (info.readAllSheets()) {
            sheetIndex = 0;
        } else {
            sheetIndex = Const.indexOfString(sheet.getName(), info.getSheetName());
        }
        if (sheetIndex >= 0) {
            // We suppose it's the complete range we're looking for...
            // 
            int rownr = 0;
            int startcol = 0;
            if (info.readAllSheets()) {
                if (info.getStartColumn().length == 1) {
                    startcol = info.getStartColumn()[0];
                }
                if (info.getStartRow().length == 1) {
                    rownr = info.getStartRow()[0];
                }
            } else {
                rownr = info.getStartRow()[sheetIndex];
                startcol = info.getStartColumn()[sheetIndex];
            }
            boolean stop = false;
            for (int colnr = startcol; !stop; colnr++) {
                try {
                    String fieldname = null;
                    int fieldtype = ValueMetaInterface.TYPE_NONE;
                    KCell cell = sheet.getCell(colnr, rownr);
                    if (cell == null) {
                        stop = true;
                    } else {
                        if (cell.getType() != KCellType.EMPTY) {
                            // We found a field.
                            fieldname = cell.getContents();
                        }
                        // System.out.println("Fieldname = "+fieldname);
                        KCell below = sheet.getCell(colnr, rownr + 1);
                        if (below != null) {
                            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 {
                                fieldtype = ValueMetaInterface.TYPE_STRING;
                            }
                        } else {
                            fieldtype = ValueMetaInterface.TYPE_STRING;
                        }
                        if (Utils.isEmpty(fieldname)) {
                            stop = true;
                        } else {
                            if (fieldtype != ValueMetaInterface.TYPE_NONE) {
                                ValueMetaInterface field = ValueMetaFactory.createValueMeta(fieldname, fieldtype);
                                fields.addValueMeta(field);
                            }
                        }
                    }
                } catch (ArrayIndexOutOfBoundsException aioobe) {
                    // System.out.println("index out of bounds at column "+colnr+" : "+aioobe.toString());
                    stop = true;
                }
            }
        }
    }
}

Example 20 with KCell

use of org.pentaho.di.core.spreadsheet.KCell in project pentaho-kettle by pentaho.

the class PoiWorkBookIT method readData.

private void readData() throws KettleException {
    KWorkbook workbook = WorkbookFactory.getWorkbook(SpreadSheetType.POI, "src/it/resources/sample-file.xlsx", null);
    int numberOfSheets = workbook.getNumberOfSheets();
    assertEquals(3, numberOfSheets);
    KSheet sheet1 = workbook.getSheet(0);
    assertEquals("Sheet1", sheet1.getName());
    sheet1 = workbook.getSheet("Sheet1");
    assertEquals("Sheet1", sheet1.getName());
    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.valueOf(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.valueOf(false), row[4].getValue());
    assertEquals(KCellType.NUMBER_FORMULA, row[5].getType());
    assertEquals(Double.valueOf("117"), row[5].getValue());
    row = sheet1.getRow(4);
    assertEquals(KCellType.LABEL, row[1].getType());
    assertEquals("Three", row[1].getValue());
    assertEquals(KCellType.DATE, row[2].getType());
    assertEquals(new Date(1283990400000L), row[2].getValue());
    assertEquals(KCellType.NUMBER, row[3].getType());
    assertEquals(Double.valueOf("93"), row[3].getValue());
    assertEquals(KCellType.BOOLEAN, row[4].getType());
    assertEquals(Boolean.valueOf(true), row[4].getValue());
    assertEquals(KCellType.NUMBER_FORMULA, row[5].getType());
    assertEquals(Double.valueOf("210"), row[5].getValue());
    try {
        sheet1.getRow(5);
        fail("No out of bounds exception thrown when expected");
    } catch (ArrayIndexOutOfBoundsException e) {
    // OK!
    }
    workbook.close();
}