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

Example 11 with KSheet

use of org.pentaho.di.core.spreadsheet.KSheet 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 12 with KSheet

use of org.pentaho.di.core.spreadsheet.KSheet 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 13 with KSheet

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

the class ExcelInputDialog method getSheets.

/**
 * Get the names of the sheets from the Excel workbooks and let the user select some or all of them.
 */
public void getSheets() {
    List<String> sheetnames = new ArrayList<String>();
    ExcelInputMeta info = new ExcelInputMeta();
    getInfo(info);
    FileInputList fileList = info.getFileList(transMeta);
    for (FileObject fileObject : fileList.getFiles()) {
        try {
            KWorkbook workbook = WorkbookFactory.getWorkbook(info.getSpreadSheetType(), KettleVFS.getFilename(fileObject), info.getEncoding());
            int nrSheets = workbook.getNumberOfSheets();
            for (int j = 0; j < nrSheets; j++) {
                KSheet sheet = workbook.getSheet(j);
                String sheetname = sheet.getName();
                if (Const.indexOfString(sheetname, sheetnames) < 0) {
                    sheetnames.add(sheetname);
                }
            }
            workbook.close();
        } catch (Exception e) {
            new ErrorDialog(shell, BaseMessages.getString(PKG, "System.Dialog.Error.Title"), BaseMessages.getString(PKG, "ExcelInputDialog.ErrorReadingFile.DialogMessage", KettleVFS.getFilename(fileObject)), e);
        }
    }
    // Put it in an array:
    String[] lst = sheetnames.toArray(new String[sheetnames.size()]);
    // Let the user select the sheet-names...
    EnterListDialog esd = new EnterListDialog(shell, SWT.NONE, lst);
    String[] selection = esd.open();
    if (selection != null) {
        for (int j = 0; j < selection.length; j++) {
            wSheetnameList.add(new String[] { selection[j], "" });
        }
        wSheetnameList.removeEmptyRows();
        wSheetnameList.setRowNums();
        wSheetnameList.optWidth(true);
        checkAlerts();
    }
}

Example 14 with KSheet

use of org.pentaho.di.core.spreadsheet.KSheet 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 15 with KSheet

use of org.pentaho.di.core.spreadsheet.KSheet 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();
}