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

Example 1 with KCellType

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

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

the class StaxPoiSheet method parseRow.

private KCell[] parseRow() throws XMLStreamException {
    KCell[] cells = new StaxPoiCell[numCols];
    for (int i = 0; i < numCols; i++) {
        // go to the "c" cell tag
        while (sheetReader.hasNext()) {
            int event = sheetReader.next();
            if (event == XMLStreamConstants.START_ELEMENT && sheetReader.getLocalName().equals("c")) {
                break;
            }
            if (event == XMLStreamConstants.END_ELEMENT && sheetReader.getLocalName().equals("row")) {
                // premature end of row, returning what we have
                return cells;
            }
        }
        String cellLocation = sheetReader.getAttributeValue(null, "r");
        int columnIndex = StaxUtil.extractColumnNumber(cellLocation) - 1;
        String cellType = sheetReader.getAttributeValue(null, "t");
        String cellStyle = sheetReader.getAttributeValue(null, "s");
        boolean isFormula = false;
        String content = null;
        // get value tag
        while (sheetReader.hasNext()) {
            int event = sheetReader.next();
            if (event == XMLStreamConstants.START_ELEMENT && sheetReader.getLocalName().equals("v")) {
                // read content as string
                if (cellType != null && cellType.equals("s")) {
                    int idx = Integer.parseInt(sheetReader.getElementText());
                    content = new XSSFRichTextString(sst.getEntryAt(idx)).toString();
                } else {
                    content = sheetReader.getElementText();
                }
            }
            if (event == XMLStreamConstants.START_ELEMENT && sheetReader.getLocalName().equals("is")) {
                while (sheetReader.hasNext()) {
                    event = sheetReader.next();
                    if (event == XMLStreamConstants.CHARACTERS) {
                        content = new XSSFRichTextString(sheetReader.getText()).toString();
                        break;
                    }
                }
            }
            if (event == XMLStreamConstants.START_ELEMENT && sheetReader.getLocalName().equals("f")) {
                isFormula = true;
            }
            if (event == XMLStreamConstants.END_ELEMENT && sheetReader.getLocalName().equals("c")) {
                break;
            }
        }
        if (content != null) {
            KCellType kcType = getCellType(cellType, cellStyle, isFormula);
            cells[columnIndex] = new StaxPoiCell(parseValue(kcType, content), kcType, currentRow);
        }
    // else let cell be null
    }
    return cells;
}