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

Example 1 with KCell

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

the class ExcelInput method fillRow.

/**
 * Build an empty row based on the meta-data...
 *
 * @return
 */
private Object[] fillRow(int startcolumn, ExcelInputRow excelInputRow) throws KettleException {
    Object[] r = new Object[data.outputRowMeta.size()];
    // Keep track whether or not we handled an error for this line yet.
    boolean errorHandled = false;
    // Set values in the row...
    KCell cell = null;
    for (int i = startcolumn; i < excelInputRow.cells.length && i - startcolumn < meta.getField().length; i++) {
        cell = excelInputRow.cells[i];
        int rowcolumn = i - startcolumn;
        if (cell == null) {
            r[rowcolumn] = null;
            continue;
        }
        ValueMetaInterface targetMeta = data.outputRowMeta.getValueMeta(rowcolumn);
        ValueMetaInterface sourceMeta = null;
        try {
            checkType(cell, targetMeta);
        } catch (KettleException ex) {
            if (!meta.isErrorIgnored()) {
                ex = new KettleCellValueException(ex, this.data.sheetnr, this.data.rownr, i, "");
                throw ex;
            }
            if (log.isBasic()) {
                logBasic(BaseMessages.getString(PKG, "ExcelInput.Log.WarningProcessingExcelFile", "" + targetMeta, "" + data.filename, ex.getMessage()));
            }
            if (!errorHandled) {
                data.errorHandler.handleLineError(excelInputRow.rownr, excelInputRow.sheetName);
                errorHandled = true;
            }
            if (meta.isErrorLineSkipped()) {
                return null;
            }
        }
        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);
                r[rowcolumn] = new Date(time - offset);
                sourceMeta = data.valueMetaDate;
            } else {
                if (KCellType.LABEL == cellType || KCellType.STRING_FORMULA == cellType) {
                    String string = (String) cell.getValue();
                    switch(meta.getField()[rowcolumn].getTrimType()) {
                        case ExcelInputMeta.TYPE_TRIM_LEFT:
                            string = Const.ltrim(string);
                            break;
                        case ExcelInputMeta.TYPE_TRIM_RIGHT:
                            string = Const.rtrim(string);
                            break;
                        case ExcelInputMeta.TYPE_TRIM_BOTH:
                            string = Const.trim(string);
                            break;
                        default:
                            break;
                    }
                    r[rowcolumn] = string;
                    sourceMeta = data.valueMetaString;
                } 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()));
                        }
                        r[rowcolumn] = null;
                    }
                }
            }
        }
        ExcelInputField field = meta.getField()[rowcolumn];
        // 
        try {
            // 
            if (sourceMeta != null && sourceMeta.getType() != targetMeta.getType() && r[rowcolumn] != null) {
                ValueMetaInterface sourceMetaCopy = sourceMeta.clone();
                sourceMetaCopy.setConversionMask(field.getFormat());
                sourceMetaCopy.setGroupingSymbol(field.getGroupSymbol());
                sourceMetaCopy.setDecimalSymbol(field.getDecimalSymbol());
                sourceMetaCopy.setCurrencySymbol(field.getCurrencySymbol());
                switch(targetMeta.getType()) {
                    // 
                    case ValueMetaInterface.TYPE_NUMBER:
                    case ValueMetaInterface.TYPE_INTEGER:
                        switch(field.getType()) {
                            case ValueMetaInterface.TYPE_DATE:
                                // number to string conversion (20070522.00 --> "20070522")
                                // 
                                ValueMetaInterface valueMetaNumber = new ValueMetaNumber("num");
                                valueMetaNumber.setConversionMask("#");
                                Object string = sourceMetaCopy.convertData(valueMetaNumber, r[rowcolumn]);
                                // String to date with mask...
                                // 
                                r[rowcolumn] = targetMeta.convertData(sourceMetaCopy, string);
                                break;
                            default:
                                r[rowcolumn] = targetMeta.convertData(sourceMetaCopy, r[rowcolumn]);
                                break;
                        }
                        break;
                    // 
                    default:
                        r[rowcolumn] = targetMeta.convertData(sourceMetaCopy, r[rowcolumn]);
                }
            }
        } catch (KettleException ex) {
            if (!meta.isErrorIgnored()) {
                ex = new KettleCellValueException(ex, this.data.sheetnr, cell.getRow(), i, field.getName());
                throw ex;
            }
            if (log.isBasic()) {
                logBasic(BaseMessages.getString(PKG, "ExcelInput.Log.WarningProcessingExcelFile", "" + targetMeta, "" + data.filename, ex.toString()));
            }
            if (!errorHandled) {
                // check if we didn't log an error already for this one.
                data.errorHandler.handleLineError(excelInputRow.rownr, excelInputRow.sheetName);
                errorHandled = true;
            }
            if (meta.isErrorLineSkipped()) {
                return null;
            } else {
                r[rowcolumn] = null;
            }
        }
    }
    int rowIndex = meta.getField().length;
    // Do we need to include the filename?
    if (!Utils.isEmpty(meta.getFileField())) {
        r[rowIndex] = data.filename;
        rowIndex++;
    }
    // Do we need to include the sheetname?
    if (!Utils.isEmpty(meta.getSheetField())) {
        r[rowIndex] = excelInputRow.sheetName;
        rowIndex++;
    }
    // Do we need to include the sheet rownumber?
    if (!Utils.isEmpty(meta.getSheetRowNumberField())) {
        r[rowIndex] = new Long(data.rownr);
        rowIndex++;
    }
    // Do we need to include the rownumber?
    if (!Utils.isEmpty(meta.getRowNumberField())) {
        r[rowIndex] = new Long(getLinesWritten() + 1);
        rowIndex++;
    }
    // Possibly add short filename...
    if (!Utils.isEmpty(meta.getShortFileNameField())) {
        r[rowIndex] = data.shortFilename;
        rowIndex++;
    }
    // Add Extension
    if (!Utils.isEmpty(meta.getExtensionField())) {
        r[rowIndex] = data.extension;
        rowIndex++;
    }
    // add path
    if (!Utils.isEmpty(meta.getPathField())) {
        r[rowIndex] = data.path;
        rowIndex++;
    }
    // Add Size
    if (!Utils.isEmpty(meta.getSizeField())) {
        r[rowIndex] = new Long(data.size);
        rowIndex++;
    }
    // add Hidden
    if (!Utils.isEmpty(meta.isHiddenField())) {
        r[rowIndex] = new Boolean(data.hidden);
        rowIndex++;
    }
    // Add modification date
    if (!Utils.isEmpty(meta.getLastModificationDateField())) {
        r[rowIndex] = data.lastModificationDateTime;
        rowIndex++;
    }
    // Add Uri
    if (!Utils.isEmpty(meta.getUriField())) {
        r[rowIndex] = data.uriName;
        rowIndex++;
    }
    // Add RootUri
    if (!Utils.isEmpty(meta.getRootUriField())) {
        r[rowIndex] = data.rootUriName;
        rowIndex++;
    }
    return r;
}

Example 2 with KCell

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

the class StaxPoiSheetTest method testNoUsedRangeSpecified.

// The row and column bounds of all cells in the worksheet are specified in ref attribute of Dimension tag in sheet
// xml
// But ref can be present as range: <dimension ref="A1:C2"/> or as just one start cell: <dimension ref="A1"/>.
// Below tests to validate correct work for such cases
@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());
}

Example 3 with KCell

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

the class StaxPoiSheetTest method testReadCells.

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

Example 4 with KCell

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

the class StaxPoiSheetTest method testInlineString.

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

Example 5 with KCell

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

the class StaxPoiSheetTest method testReadEmptyRow.

@Test
public void testReadEmptyRow() throws Exception {
    KSheet sheet1 = getSampleSheet();
    KCell[] row = sheet1.getRow(0);
    assertEquals("empty row expected", 0, row.length);
}