Search in sources :

Example 6 with KWorkbook

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

the class StaxWorkBookIT method readData.

private void readData(String file, String password) throws KettleException {
    KWorkbook workbook = getWorkbook(file, null, password);
    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 {
        row = sheet1.getRow(5);
        fail("No out of bounds exception thrown when expected");
    } catch (ArrayIndexOutOfBoundsException e) {
    // OK!
    }
}
Also used : KWorkbook(org.pentaho.di.core.spreadsheet.KWorkbook) KSheet(org.pentaho.di.core.spreadsheet.KSheet) KCell(org.pentaho.di.core.spreadsheet.KCell) Date(java.util.Date)

Example 7 with KWorkbook

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

the class StaxWorkBookIT method testReadSameRow.

@Test
public void testReadSameRow() throws Exception {
    KWorkbook workbook = getWorkbook(sample, null);
    KSheet sheet1 = workbook.getSheet(0);
    KCell[] row = sheet1.getRow(3);
    assertEquals("Two", row[1].getValue());
    row = sheet1.getRow(3);
    assertEquals("Two", row[1].getValue());
}
Also used : KWorkbook(org.pentaho.di.core.spreadsheet.KWorkbook) KSheet(org.pentaho.di.core.spreadsheet.KSheet) KCell(org.pentaho.di.core.spreadsheet.KCell) Test(org.junit.Test)

Example 8 with KWorkbook

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

the class ExcelInputDialog method getFields.

/**
 * Get the list of fields in the Excel workbook and put the result in the fields table view.
 */
public void getFields() {
    RowMetaInterface fields = new RowMeta();
    ExcelInputMeta info = new ExcelInputMeta();
    getInfo(info);
    int clearFields = SWT.YES;
    if (wFields.nrNonEmpty() > 0) {
        MessageBox messageBox = new MessageBox(shell, SWT.YES | SWT.NO | SWT.CANCEL | SWT.ICON_QUESTION);
        messageBox.setMessage(BaseMessages.getString(PKG, "ExcelInputDialog.ClearFieldList.DialogMessage"));
        messageBox.setText(BaseMessages.getString(PKG, "ExcelInputDialog.ClearFieldList.DialogTitle"));
        clearFields = messageBox.open();
        if (clearFields == SWT.CANCEL) {
            return;
        }
    }
    FileInputList fileList = info.getFileList(transMeta);
    for (FileObject file : fileList.getFiles()) {
        try {
            KWorkbook workbook = WorkbookFactory.getWorkbook(info.getSpreadSheetType(), KettleVFS.getFilename(file), info.getEncoding(), wPassword.getText());
            processingWorkbook(fields, info, workbook);
            workbook.close();
        } catch (Exception e) {
            new ErrorDialog(shell, BaseMessages.getString(PKG, "System.Dialog.Error.Title"), BaseMessages.getString(PKG, "ExcelInputDialog.ErrorReadingFile2.DialogMessage", KettleVFS.getFilename(file), e.toString()), e);
        }
    }
    if (fields.size() > 0) {
        if (clearFields == SWT.YES) {
            wFields.clearAll(false);
        }
        for (int j = 0; j < fields.size(); j++) {
            ValueMetaInterface field = fields.getValueMeta(j);
            wFields.add(new String[] { field.getName(), field.getTypeDesc(), "", "", "none", "N" });
        }
        wFields.removeEmptyRows();
        wFields.setRowNums();
        wFields.optWidth(true);
    } else {
        MessageBox mb = new MessageBox(shell, SWT.OK | SWT.ICON_WARNING);
        mb.setMessage(BaseMessages.getString(PKG, "ExcelInputDialog.UnableToFindFields.DialogMessage"));
        mb.setText(BaseMessages.getString(PKG, "ExcelInputDialog.UnableToFindFields.DialogTitle"));
        mb.open();
    }
    checkAlerts();
}
Also used : ExcelInputMeta(org.pentaho.di.trans.steps.excelinput.ExcelInputMeta) RowMeta(org.pentaho.di.core.row.RowMeta) KWorkbook(org.pentaho.di.core.spreadsheet.KWorkbook) ErrorDialog(org.pentaho.di.ui.core.dialog.ErrorDialog) RowMetaInterface(org.pentaho.di.core.row.RowMetaInterface) FileObject(org.apache.commons.vfs2.FileObject) FileInputList(org.pentaho.di.core.fileinput.FileInputList) KettleStepException(org.pentaho.di.core.exception.KettleStepException) KettlePluginException(org.pentaho.di.core.exception.KettlePluginException) MessageBox(org.eclipse.swt.widgets.MessageBox) ValueMetaInterface(org.pentaho.di.core.row.ValueMetaInterface)

Example 9 with KWorkbook

use of org.pentaho.di.core.spreadsheet.KWorkbook 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();
}
Also used : KWorkbook(org.pentaho.di.core.spreadsheet.KWorkbook) KSheet(org.pentaho.di.core.spreadsheet.KSheet) KCell(org.pentaho.di.core.spreadsheet.KCell) Date(java.util.Date)

Example 10 with KWorkbook

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

the class StaxWorkBookIT method readData.

private void readData() throws KettleException {
    KWorkbook workbook = getWorkbook("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 {
        row = sheet1.getRow(5);
        fail("No out of bounds exception thrown when expected");
    } catch (ArrayIndexOutOfBoundsException e) {
    // OK!
    }
}
Also used : KWorkbook(org.pentaho.di.core.spreadsheet.KWorkbook) KSheet(org.pentaho.di.core.spreadsheet.KSheet) KCell(org.pentaho.di.core.spreadsheet.KCell) Date(java.util.Date)

Aggregations

KWorkbook (org.pentaho.di.core.spreadsheet.KWorkbook)14 KSheet (org.pentaho.di.core.spreadsheet.KSheet)13 KCell (org.pentaho.di.core.spreadsheet.KCell)11 Date (java.util.Date)7 Test (org.junit.Test)6 ExcelInputMeta (org.pentaho.di.trans.steps.excelinput.ExcelInputMeta)3 FileObject (org.apache.commons.vfs2.FileObject)2 KettlePluginException (org.pentaho.di.core.exception.KettlePluginException)2 KettleStepException (org.pentaho.di.core.exception.KettleStepException)2 FileInputList (org.pentaho.di.core.fileinput.FileInputList)2 RowMeta (org.pentaho.di.core.row.RowMeta)2 ValueMetaInterface (org.pentaho.di.core.row.ValueMetaInterface)2 ErrorDialog (org.pentaho.di.ui.core.dialog.ErrorDialog)2 Method (java.lang.reflect.Method)1 ArrayList (java.util.ArrayList)1 MessageBox (org.eclipse.swt.widgets.MessageBox)1 PluginInterface (org.pentaho.di.core.plugins.PluginInterface)1 PluginRegistry (org.pentaho.di.core.plugins.PluginRegistry)1 RowMetaInterface (org.pentaho.di.core.row.RowMetaInterface)1 ValueMetaString (org.pentaho.di.core.row.value.ValueMetaString)1