Search in sources :

Example 1 with LabelSSTRecord

use of org.apache.poi.hssf.record.LabelSSTRecord in project poi by apache.

the class HSSFCell method setCellValue.

/**
     * Set a string value for the cell.
     *
     * @param value  value to set the cell to.  For formulas we'll set the formula
     * string, for String cells we'll set its value.  For other types we will
     * change the cell to a string cell and set its value.
     * If value is <code>null</code> then we will change the cell to a Blank cell.
     */
public void setCellValue(RichTextString value) {
    int row = _record.getRow();
    short col = _record.getColumn();
    short styleIndex = _record.getXFIndex();
    if (value == null) {
        notifyFormulaChanging();
        setCellType(CellType.BLANK, false, row, col, styleIndex);
        return;
    }
    if (value.length() > SpreadsheetVersion.EXCEL97.getMaxTextLength()) {
        throw new IllegalArgumentException("The maximum length of cell contents (text) is 32,767 characters");
    }
    if (_cellType == CellType.FORMULA) {
        // Set the 'pre-evaluated result' for the formula
        // note - formulas do not preserve text formatting.
        FormulaRecordAggregate fr = (FormulaRecordAggregate) _record;
        fr.setCachedStringResult(value.getString());
        // Update our local cache to the un-formatted version
        _stringValue = new HSSFRichTextString(value.getString());
        // All done
        return;
    }
    if (_cellType != CellType.STRING) {
        setCellType(CellType.STRING, false, row, col, styleIndex);
    }
    int index = 0;
    HSSFRichTextString hvalue = (HSSFRichTextString) value;
    UnicodeString str = hvalue.getUnicodeString();
    index = _book.getWorkbook().addSSTString(str);
    ((LabelSSTRecord) _record).setSSTIndex(index);
    _stringValue = hvalue;
    _stringValue.setWorkbookReferences(_book.getWorkbook(), ((LabelSSTRecord) _record));
    _stringValue.setUnicodeString(_book.getWorkbook().getSSTString(index));
}
Also used : UnicodeString(org.apache.poi.hssf.record.common.UnicodeString) FormulaRecordAggregate(org.apache.poi.hssf.record.aggregates.FormulaRecordAggregate) LabelSSTRecord(org.apache.poi.hssf.record.LabelSSTRecord)

Example 2 with LabelSSTRecord

use of org.apache.poi.hssf.record.LabelSSTRecord in project poi by apache.

the class HSSFCell method setCellType.

/**
     * sets the cell type. The setValue flag indicates whether to bother about
     *  trying to preserve the current value in the new record if one is created.
     *  <p>
     *  The @see #setCellValue method will call this method with false in setValue
     *  since it will overwrite the cell value later
     *
     */
private void setCellType(CellType cellType, boolean setValue, int row, short col, short styleIndex) {
    switch(cellType) {
        case FORMULA:
            FormulaRecordAggregate frec;
            if (cellType != _cellType) {
                frec = _sheet.getSheet().getRowsAggregate().createFormula(row, col);
            } else {
                frec = (FormulaRecordAggregate) _record;
                frec.setRow(row);
                frec.setColumn(col);
            }
            if (setValue) {
                frec.getFormulaRecord().setValue(getNumericCellValue());
            }
            frec.setXFIndex(styleIndex);
            _record = frec;
            break;
        case NUMERIC:
            NumberRecord nrec = null;
            if (cellType != _cellType) {
                nrec = new NumberRecord();
            } else {
                nrec = (NumberRecord) _record;
            }
            nrec.setColumn(col);
            if (setValue) {
                nrec.setValue(getNumericCellValue());
            }
            nrec.setXFIndex(styleIndex);
            nrec.setRow(row);
            _record = nrec;
            break;
        case STRING:
            LabelSSTRecord lrec;
            if (cellType == _cellType) {
                lrec = (LabelSSTRecord) _record;
            } else {
                lrec = new LabelSSTRecord();
                lrec.setColumn(col);
                lrec.setRow(row);
                lrec.setXFIndex(styleIndex);
            }
            if (setValue) {
                String str = convertCellValueToString();
                if (str == null) {
                    // bug 55668: don't try to store null-string when formula
                    // results in empty/null value
                    setCellType(CellType.BLANK, false, row, col, styleIndex);
                    return;
                } else {
                    int sstIndex = _book.getWorkbook().addSSTString(new UnicodeString(str));
                    lrec.setSSTIndex(sstIndex);
                    UnicodeString us = _book.getWorkbook().getSSTString(sstIndex);
                    _stringValue = new HSSFRichTextString();
                    _stringValue.setUnicodeString(us);
                }
            }
            _record = lrec;
            break;
        case BLANK:
            BlankRecord brec = null;
            if (cellType != _cellType) {
                brec = new BlankRecord();
            } else {
                brec = (BlankRecord) _record;
            }
            brec.setColumn(col);
            // During construction the cellStyle may be null for a Blank cell.
            brec.setXFIndex(styleIndex);
            brec.setRow(row);
            _record = brec;
            break;
        case BOOLEAN:
            BoolErrRecord boolRec = null;
            if (cellType != _cellType) {
                boolRec = new BoolErrRecord();
            } else {
                boolRec = (BoolErrRecord) _record;
            }
            boolRec.setColumn(col);
            if (setValue) {
                boolRec.setValue(convertCellValueToBoolean());
            }
            boolRec.setXFIndex(styleIndex);
            boolRec.setRow(row);
            _record = boolRec;
            break;
        case ERROR:
            BoolErrRecord errRec = null;
            if (cellType != _cellType) {
                errRec = new BoolErrRecord();
            } else {
                errRec = (BoolErrRecord) _record;
            }
            errRec.setColumn(col);
            if (setValue) {
                errRec.setValue(FormulaError.VALUE.getCode());
            }
            errRec.setXFIndex(styleIndex);
            errRec.setRow(row);
            _record = errRec;
            break;
        default:
            throw new IllegalStateException("Invalid cell type: " + cellType);
    }
    if (cellType != _cellType && // Special Value to indicate an uninitialized Cell
    _cellType != CellType._NONE) {
        _sheet.getSheet().replaceValueRecord(_record);
    }
    _cellType = cellType;
}
Also used : BoolErrRecord(org.apache.poi.hssf.record.BoolErrRecord) BlankRecord(org.apache.poi.hssf.record.BlankRecord) UnicodeString(org.apache.poi.hssf.record.common.UnicodeString) FormulaRecordAggregate(org.apache.poi.hssf.record.aggregates.FormulaRecordAggregate) RichTextString(org.apache.poi.ss.usermodel.RichTextString) UnicodeString(org.apache.poi.hssf.record.common.UnicodeString) NumberRecord(org.apache.poi.hssf.record.NumberRecord) LabelSSTRecord(org.apache.poi.hssf.record.LabelSSTRecord)

Example 3 with LabelSSTRecord

use of org.apache.poi.hssf.record.LabelSSTRecord in project poi by apache.

the class HSSFCell method convertCellValueToString.

private String convertCellValueToString() {
    switch(_cellType) {
        case BLANK:
            return "";
        case BOOLEAN:
            return ((BoolErrRecord) _record).getBooleanValue() ? "TRUE" : "FALSE";
        case STRING:
            int sstIndex = ((LabelSSTRecord) _record).getSSTIndex();
            return _book.getWorkbook().getSSTString(sstIndex).getString();
        case NUMERIC:
            return NumberToTextConverter.toText(((NumberRecord) _record).getValue());
        case ERROR:
            return FormulaError.forInt(((BoolErrRecord) _record).getErrorValue()).getString();
        case FORMULA:
            // just use cached formula result instead
            break;
        default:
            throw new IllegalStateException("Unexpected cell type (" + _cellType + ")");
    }
    FormulaRecordAggregate fra = ((FormulaRecordAggregate) _record);
    FormulaRecord fr = fra.getFormulaRecord();
    switch(CellType.forInt(fr.getCachedResultType())) {
        case BOOLEAN:
            return fr.getCachedBooleanValue() ? "TRUE" : "FALSE";
        case STRING:
            return fra.getStringValue();
        case NUMERIC:
            return NumberToTextConverter.toText(fr.getValue());
        case ERROR:
            return FormulaError.forInt(fr.getCachedErrorValue()).getString();
        default:
            throw new IllegalStateException("Unexpected formula result type (" + _cellType + ")");
    }
}
Also used : BoolErrRecord(org.apache.poi.hssf.record.BoolErrRecord) FormulaRecord(org.apache.poi.hssf.record.FormulaRecord) FormulaRecordAggregate(org.apache.poi.hssf.record.aggregates.FormulaRecordAggregate) LabelSSTRecord(org.apache.poi.hssf.record.LabelSSTRecord)

Example 4 with LabelSSTRecord

use of org.apache.poi.hssf.record.LabelSSTRecord in project jeesuite-libs by vakinge.

the class XLS2CSV method processRecord.

/**
 * Main HSSFListener method, processes events, and outputs the
 *  CSV as the file is processed.
 */
public void processRecord(Record record) {
    // 超过10行空白就不处理了
    if (blankRowNum == 10)
        return;
    int thisRow = -1;
    int thisColumn = -1;
    String thisStr = null;
    switch(record.getSid()) {
        case BoundSheetRecord.sid:
            boundSheetRecords.add((BoundSheetRecord) record);
            break;
        case BOFRecord.sid:
            BOFRecord br = (BOFRecord) record;
            if (br.getType() == BOFRecord.TYPE_WORKSHEET) {
                // Create sub workbook if required
                if (workbookBuildingListener != null && stubWorkbook == null) {
                    stubWorkbook = workbookBuildingListener.getStubHSSFWorkbook();
                }
                // Output the worksheet name
                // Works by ordering the BSRs by the location of
                // their BOFRecords, and then knowing that we
                // process BOFRecords in byte offset order
                sheetIndex++;
                if (orderedBSRs == null) {
                    orderedBSRs = BoundSheetRecord.orderByBofPosition(boundSheetRecords);
                }
                // sheetName
                // System.out.println(orderedBSRs[sheetIndex].getSheetname() + " [" + (sheetIndex+1) + "]:" );
                String sheetname = orderedBSRs[sheetIndex].getSheetname();
                results.add(ExcelValidator.SHEET_NAME_PREFIX + sheetname);
            }
            break;
        case SSTRecord.sid:
            sstRecord = (SSTRecord) record;
            break;
        case BlankRecord.sid:
            BlankRecord brec = (BlankRecord) record;
            thisRow = brec.getRow();
            thisColumn = brec.getColumn();
            thisStr = "";
            break;
        case BoolErrRecord.sid:
            BoolErrRecord berec = (BoolErrRecord) record;
            thisRow = berec.getRow();
            thisColumn = berec.getColumn();
            thisStr = "";
            break;
        case FormulaRecord.sid:
            FormulaRecord frec = (FormulaRecord) record;
            thisRow = frec.getRow();
            thisColumn = frec.getColumn();
            if (outputFormulaValues) {
                if (Double.isNaN(frec.getValue())) {
                    // Formula result is a string
                    // This is stored in the next record
                    outputNextStringRecord = true;
                    nextRow = frec.getRow();
                    nextColumn = frec.getColumn();
                } else {
                    thisStr = formatListener.formatNumberDateCell(frec);
                }
            } else {
                thisStr = HSSFFormulaParser.toFormulaString(stubWorkbook, frec.getParsedExpression());
            }
            break;
        case StringRecord.sid:
            if (outputNextStringRecord) {
                // String for formula
                StringRecord srec = (StringRecord) record;
                thisStr = srec.getString();
                thisRow = nextRow;
                thisColumn = nextColumn;
                outputNextStringRecord = false;
            }
            break;
        case LabelRecord.sid:
            LabelRecord lrec = (LabelRecord) record;
            thisRow = lrec.getRow();
            thisColumn = lrec.getColumn();
            thisStr = lrec.getValue();
            break;
        case LabelSSTRecord.sid:
            LabelSSTRecord lsrec = (LabelSSTRecord) record;
            thisRow = lsrec.getRow();
            thisColumn = lsrec.getColumn();
            if (sstRecord == null) {
                thisStr = "(No SST Record, can't identify string)";
            } else {
                thisStr = sstRecord.getString(lsrec.getSSTIndex()).toString();
            }
            break;
        case NoteRecord.sid:
            NoteRecord nrec = (NoteRecord) record;
            thisRow = nrec.getRow();
            thisColumn = nrec.getColumn();
            // TODO: Find object to match nrec.getShapeId()
            thisStr = "(TODO)";
            break;
        case NumberRecord.sid:
            NumberRecord numrec = (NumberRecord) record;
            thisRow = numrec.getRow();
            thisColumn = numrec.getColumn();
            // Format
            thisStr = formatListener.formatNumberDateCell(numrec);
            break;
        case RKRecord.sid:
            RKRecord rkrec = (RKRecord) record;
            thisRow = rkrec.getRow();
            thisColumn = rkrec.getColumn();
            thisStr = "(TODO)";
            break;
        default:
            break;
    }
    // Handle new row
    if (thisRow != -1 && thisRow != lastRowNumber) {
        lastColumnNumber = -1;
    }
    // Handle missing column
    if (record instanceof MissingCellDummyRecord) {
        MissingCellDummyRecord mc = (MissingCellDummyRecord) record;
        thisRow = mc.getRow();
        thisColumn = mc.getColumn();
        thisStr = "";
    }
    if (thisStr != null) {
        if (thisColumn > 0) {
            _resultRowTmp.append(ExcelValidator.FIELD_SPLIT);
        }
        _resultRowTmp.append(thisStr);
    }
    // Update column and row count
    if (thisRow > -1)
        lastRowNumber = thisRow;
    if (thisColumn > -1)
        lastColumnNumber = thisColumn;
    // Handle end of row
    if (record instanceof LastCellOfRowDummyRecord) {
        // Print out any missing commas if needed
        if (minColumns > 0) {
            // Columns are 0 based
            if (lastColumnNumber == -1) {
                lastColumnNumber = 0;
            }
            for (int i = lastColumnNumber; i < (minColumns); i++) {
                _resultRowTmp.append(',');
            }
        }
        // We're onto a new row
        lastColumnNumber = -1;
        // End the row
        if (!ExcelValidator.isBlankCSVRow(_resultRowTmp.toString())) {
            results.add(_resultRowTmp.toString());
        } else {
            blankRowNum = blankRowNum + 1;
        }
        _resultRowTmp.setLength(0);
    }
}
Also used : BlankRecord(org.apache.poi.hssf.record.BlankRecord) BOFRecord(org.apache.poi.hssf.record.BOFRecord) LabelRecord(org.apache.poi.hssf.record.LabelRecord) LabelSSTRecord(org.apache.poi.hssf.record.LabelSSTRecord) StringRecord(org.apache.poi.hssf.record.StringRecord) BoolErrRecord(org.apache.poi.hssf.record.BoolErrRecord) FormulaRecord(org.apache.poi.hssf.record.FormulaRecord) RKRecord(org.apache.poi.hssf.record.RKRecord) LastCellOfRowDummyRecord(org.apache.poi.hssf.eventusermodel.dummyrecord.LastCellOfRowDummyRecord) NoteRecord(org.apache.poi.hssf.record.NoteRecord) NumberRecord(org.apache.poi.hssf.record.NumberRecord) MissingCellDummyRecord(org.apache.poi.hssf.eventusermodel.dummyrecord.MissingCellDummyRecord)

Example 5 with LabelSSTRecord

use of org.apache.poi.hssf.record.LabelSSTRecord in project hutool by looly.

the class Excel03SaxReader method processCellValue.

/**
 * 处理单元格值
 *
 * @param record 单元格
 */
private void processCellValue(Record record) {
    Object value = null;
    switch(record.getSid()) {
        case BlankRecord.sid:
            // 空白记录
            addToRowCellList(((BlankRecord) record), StrUtil.EMPTY);
            break;
        case BoolErrRecord.sid:
            // 布尔类型
            final BoolErrRecord berec = (BoolErrRecord) record;
            addToRowCellList(berec, berec.getBooleanValue());
            break;
        case FormulaRecord.sid:
            // 公式类型
            final FormulaRecord formulaRec = (FormulaRecord) record;
            if (isOutputFormulaValues) {
                if (Double.isNaN(formulaRec.getValue())) {
                    // Formula result is a string
                    // This is stored in the next record
                    isOutputNextStringRecord = true;
                } else {
                    value = ExcelSaxUtil.getNumberOrDateValue(formulaRec, formulaRec.getValue(), this.formatListener);
                }
            } else {
                value = HSSFFormulaParser.toFormulaString(stubWorkbook, formulaRec.getParsedExpression());
            }
            addToRowCellList(formulaRec, value);
            break;
        case StringRecord.sid:
            // 单元格中公式的字符串
            if (isOutputNextStringRecord) {
                // String for formula
                // value = ((StringRecord) record).getString();
                isOutputNextStringRecord = false;
            }
            break;
        case LabelRecord.sid:
            final LabelRecord lrec = (LabelRecord) record;
            value = lrec.getValue();
            addToRowCellList(lrec, value);
            break;
        case LabelSSTRecord.sid:
            // 字符串类型
            LabelSSTRecord lsrec = (LabelSSTRecord) record;
            if (null != sstRecord) {
                value = sstRecord.getString(lsrec.getSSTIndex()).toString();
            }
            addToRowCellList(lsrec, ObjectUtil.defaultIfNull(value, StrUtil.EMPTY));
            break;
        case // 数字类型
        NumberRecord.sid:
            final NumberRecord numrec = (NumberRecord) record;
            value = ExcelSaxUtil.getNumberOrDateValue(numrec, numrec.getValue(), this.formatListener);
            // 向容器加入列值
            addToRowCellList(numrec, value);
            break;
        default:
            break;
    }
}
Also used : BoolErrRecord(org.apache.poi.hssf.record.BoolErrRecord) BlankRecord(org.apache.poi.hssf.record.BlankRecord) FormulaRecord(org.apache.poi.hssf.record.FormulaRecord) LabelRecord(org.apache.poi.hssf.record.LabelRecord) LabelSSTRecord(org.apache.poi.hssf.record.LabelSSTRecord) NumberRecord(org.apache.poi.hssf.record.NumberRecord)

Aggregations

LabelSSTRecord (org.apache.poi.hssf.record.LabelSSTRecord)10 BoolErrRecord (org.apache.poi.hssf.record.BoolErrRecord)6 FormulaRecord (org.apache.poi.hssf.record.FormulaRecord)6 BlankRecord (org.apache.poi.hssf.record.BlankRecord)5 NumberRecord (org.apache.poi.hssf.record.NumberRecord)5 MissingCellDummyRecord (org.apache.poi.hssf.eventusermodel.dummyrecord.MissingCellDummyRecord)4 LabelRecord (org.apache.poi.hssf.record.LabelRecord)4 FormulaRecordAggregate (org.apache.poi.hssf.record.aggregates.FormulaRecordAggregate)4 UnicodeString (org.apache.poi.hssf.record.common.UnicodeString)4 LastCellOfRowDummyRecord (org.apache.poi.hssf.eventusermodel.dummyrecord.LastCellOfRowDummyRecord)3 BOFRecord (org.apache.poi.hssf.record.BOFRecord)3 StringRecord (org.apache.poi.hssf.record.StringRecord)3 NoteRecord (org.apache.poi.hssf.record.NoteRecord)2 RKRecord (org.apache.poi.hssf.record.RKRecord)2 Record (org.apache.poi.hssf.record.Record)2 RichTextString (org.apache.poi.ss.usermodel.RichTextString)2 EscherBSERecord (org.apache.poi.ddf.EscherBSERecord)1 EscherBlipRecord (org.apache.poi.ddf.EscherBlipRecord)1 EscherRecord (org.apache.poi.ddf.EscherRecord)1 MissingRowDummyRecord (org.apache.poi.hssf.eventusermodel.dummyrecord.MissingRowDummyRecord)1