/* ==================================================================== Licensed to the Apache Software Foundation (ASF) Under one or more contributor license agreements. See the NOTICE file distributed with this work for Additional information regarding copyright ownership. The ASF licenses this file to You Under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the License. You may obtain a copy of the License at http://www.apache.org/licenses/LICENSE-2.0 Unless required by applicable law or agreed to in writing, software distributed Under the License is distributed on an "AS Is" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations Under the License. ==================================================================== */ namespace HH.WMS.Utils.NPOI.HSSF.UserModel { using System; using System.Collections; using System.Collections.Generic; using System.IO; using HH.WMS.Utils.NPOI.HSSF.Model; using HH.WMS.Utils.NPOI.HSSF.Record; using HH.WMS.Utils.NPOI.HSSF.Record.Aggregates; using HH.WMS.Utils.NPOI.SS; using HH.WMS.Utils.NPOI.SS.Formula.PTG; using HH.WMS.Utils.NPOI.SS.UserModel; using HH.WMS.Utils.NPOI.SS.Util; using HH.WMS.Utils.NPOI.SS.Formula; using System.Globalization; /// /// High level representation of a cell in a row of a spReadsheet. /// Cells can be numeric, formula-based or string-based (text). The cell type /// specifies this. String cells cannot conatin numbers and numeric cells cannot /// contain strings (at least according to our model). Client apps should do the /// conversions themselves. Formula cells have the formula string, as well as /// the formula result, which can be numeric or string. /// Cells should have their number (0 based) before being Added to a row. Only /// cells that have values should be Added. /// /// /// @author Andrew C. Oliver (acoliver at apache dot org) /// @author Dan Sherman (dsherman at Isisph.com) /// @author Brian Sanders (kestrel at burdell dot org) Active Cell support /// @author Yegor Kozlov cell comments support /// [Serializable] public class HSSFCell : ICell { public const short ENCODING_UNCHANGED = -1; public const short ENCODING_COMPRESSED_UNICODE = 0; public const short ENCODING_UTF_16 = 1; private CellType cellType; private HSSFRichTextString stringValue; // fix warning CS0414 "never used": private short encoding = ENCODING_UNCHANGED; private HSSFWorkbook book; private HSSFSheet sheet; private CellValueRecordInterface record; private IComment comment; private static String FILE_FORMAT_NAME = "BIFF8"; public static int LAST_COLUMN_NUMBER = SpreadsheetVersion.EXCEL97.LastColumnIndex; // 2^8 - 1 private static String LAST_COLUMN_NAME = SpreadsheetVersion.EXCEL97.LastColumnName; /// /// Creates new Cell - Should only be called by HSSFRow. This Creates a cell /// from scratch. /// When the cell is initially Created it is Set to CellType.BLANK. Cell types /// can be Changed/overwritten by calling SetCellValue with the appropriate /// type as a parameter although conversions from one type to another may be /// prohibited. /// /// Workbook record of the workbook containing this cell /// Sheet record of the sheet containing this cell /// the row of this cell /// the column for this cell public HSSFCell(HSSFWorkbook book, HSSFSheet sheet, int row, short col) : this(book, sheet, row, col, CellType.BLANK) { } /// /// Creates new Cell - Should only be called by HSSFRow. This Creates a cell /// from scratch. /// /// Workbook record of the workbook containing this cell /// Sheet record of the sheet containing this cell /// the row of this cell /// the column for this cell /// CellType.NUMERIC, CellType.STRING, CellType.FORMULA, CellType.BLANK, /// CellType.BOOLEAN, CellType.ERROR public HSSFCell(HSSFWorkbook book, HSSFSheet sheet, int row, short col, CellType type) { CheckBounds(col); cellType = CellType.Unknown; // Force 'SetCellType' to Create a first Record stringValue = null; this.book = book; this.sheet = sheet; short xfindex = sheet.Sheet.GetXFIndexForColAt(col); SetCellType(type, false, row, col, xfindex); } /// /// Creates an Cell from a CellValueRecordInterface. HSSFSheet uses this when /// reading in cells from an existing sheet. /// /// Workbook record of the workbook containing this cell /// Sheet record of the sheet containing this cell /// the Cell Value Record we wish to represent public HSSFCell(HSSFWorkbook book, HSSFSheet sheet, CellValueRecordInterface cval) { record = cval; cellType = DetermineType(cval); stringValue = null; this.book = book; this.sheet = sheet; switch (cellType) { case CellType.STRING: stringValue = new HSSFRichTextString(book.Workbook, (LabelSSTRecord)cval); break; case CellType.BLANK: break; case CellType.FORMULA: stringValue = new HSSFRichTextString(((FormulaRecordAggregate)cval).StringValue); break; } ExtendedFormatRecord xf = book.Workbook.GetExFormatAt(cval.XFIndex); CellStyle = new HSSFCellStyle((short)cval.XFIndex, xf, book); } /** * private constructor to prevent blank construction */ private HSSFCell() { } /** * used internally -- given a cell value record, figure out its type */ private CellType DetermineType(CellValueRecordInterface cval) { if (cval is FormulaRecordAggregate) { return CellType.FORMULA; } Record record = (Record)cval; int sid = record.Sid; switch (sid) { case NumberRecord.sid: return CellType.NUMERIC; case BlankRecord.sid: return CellType.BLANK; case LabelSSTRecord.sid: return CellType.STRING; case FormulaRecordAggregate.sid: return CellType.FORMULA; case BoolErrRecord.sid: BoolErrRecord boolErrRecord = (BoolErrRecord)record; return (boolErrRecord.IsBoolean) ? CellType.BOOLEAN : CellType.ERROR; } throw new Exception("Bad cell value rec (" + cval.GetType().Name + ")"); } /** * Returns the Workbook that this Cell is bound to * @return */ public InternalWorkbook BoundWorkbook { get { return book.Workbook; } } public ISheet Sheet { get { return this.sheet; } } /** * Returns the HSSFRow this cell belongs to * * @return the HSSFRow that owns this cell */ public IRow Row { get { int rowIndex = this.RowIndex; return sheet.GetRow(rowIndex); } } /// /// Set the cells type (numeric, formula or string) /// /// Type of the cell. public void SetCellType(CellType cellType) { NotifyFormulaChanging(); if (IsPartOfArrayFormulaGroup) { NotifyArrayFormulaChanging(); } int row = record.Row; int col = record.Column; short styleIndex = record.XFIndex; SetCellType(cellType, true, row, col, styleIndex); } /// /// 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. /// The SetCellValue method will call this method with false in SetValue /// since it will overWrite the cell value later /// /// Type of the cell. /// if set to true [set value]. /// The row. /// The col. /// Index of the style. private void SetCellType(CellType cellType, bool setValue, int row, int col, short styleIndex) { if (cellType > CellType.ERROR) { throw new Exception("I have no idea what type that Is!"); } switch (cellType) { case CellType.FORMULA: FormulaRecordAggregate frec = null; if (cellType != this.cellType) { frec = sheet.Sheet.RowsAggregate.CreateFormula(row, col); } else { frec = (FormulaRecordAggregate)record; } frec.Column = col; if (setValue) { frec.FormulaRecord.Value = NumericCellValue; } frec.XFIndex = styleIndex; frec.Row = row; record = frec; break; case CellType.NUMERIC: NumberRecord nrec = null; if (cellType != this.cellType) { nrec = new NumberRecord(); } else { nrec = (NumberRecord)record; } nrec.Column = col; if (setValue) { nrec.Value = NumericCellValue; } nrec.XFIndex = styleIndex; nrec.Row = row; record = nrec; break; case CellType.STRING: LabelSSTRecord lrec = null; if (cellType != this.cellType) { lrec = new LabelSSTRecord(); } else { lrec = (LabelSSTRecord)record; } lrec.Column = col; lrec.Row = row; lrec.XFIndex = styleIndex; if (setValue) { String str = ConvertCellValueToString(); int sstIndex = book.Workbook.AddSSTString(new UnicodeString(str)); lrec.SSTIndex = (sstIndex); UnicodeString us = book.Workbook.GetSSTString(sstIndex); stringValue = new HSSFRichTextString(); stringValue.UnicodeString = us; } record = lrec; break; case CellType.BLANK: BlankRecord brec = null; if (cellType != this.cellType) { brec = new BlankRecord(); } else { brec = (BlankRecord)record; } brec.Column = col; // During construction the cellStyle may be null for a Blank cell. brec.XFIndex = styleIndex; brec.Row = row; record = brec; break; case CellType.BOOLEAN: BoolErrRecord boolRec = null; if (cellType != this.cellType) { boolRec = new BoolErrRecord(); } else { boolRec = (BoolErrRecord)record; } boolRec.Column = col; if (setValue) { boolRec.SetValue(ConvertCellValueToBoolean()); } boolRec.XFIndex = styleIndex; boolRec.Row = row; record = boolRec; break; case CellType.ERROR: BoolErrRecord errRec = null; if (cellType != this.cellType) { errRec = new BoolErrRecord(); } else { errRec = (BoolErrRecord)record; } errRec.Column = col; if (setValue) { errRec.SetValue((byte)HSSFErrorConstants.ERROR_VALUE); } errRec.XFIndex = styleIndex; errRec.Row = row; record = errRec; break; } if (cellType != this.cellType && this.cellType != CellType.Unknown) // Special Value to indicate an Uninitialized Cell { sheet.Sheet.ReplaceValueRecord(record); } this.cellType = cellType; } /// /// Get the cells type (numeric, formula or string) /// /// The type of the cell. public CellType CellType { get { return cellType; } } private String ConvertCellValueToString() { switch (cellType) { case CellType.BLANK: return ""; case CellType.BOOLEAN: return ((BoolErrRecord)record).BooleanValue ? "TRUE" : "FALSE"; case CellType.STRING: int sstIndex = ((LabelSSTRecord)record).SSTIndex; return book.Workbook.GetSSTString(sstIndex).String; case CellType.NUMERIC: return NumberToTextConverter.ToText(((NumberRecord)record).Value); case CellType.ERROR: return HSSFErrorConstants.GetText(((BoolErrRecord)record).ErrorValue); case CellType.FORMULA: // should really evaluate, but Cell can't call HSSFFormulaEvaluator // just use cached formula result instead break; default: throw new InvalidDataException("Unexpected cell type (" + cellType + ")"); } FormulaRecordAggregate fra = ((FormulaRecordAggregate)record); FormulaRecord fr = fra.FormulaRecord; switch (fr.CachedResultType) { case CellType.BOOLEAN: return fr.CachedBooleanValue ? "TRUE" : "FALSE"; case CellType.STRING: return fra.StringValue; case CellType.NUMERIC: return NumberToTextConverter.ToText(fr.Value); case CellType.ERROR: return HSSFErrorConstants.GetText(fr.CachedErrorValue); } throw new InvalidDataException("Unexpected formula result type (" + cellType + ")"); } /// /// Set a numeric value for the cell /// /// the numeric value to Set this cell to. For formulas we'll Set the /// precalculated value, for numerics we'll Set its value. For other types we /// will Change the cell to a numeric cell and Set its value. public void SetCellValue(double value) { if(double.IsInfinity(value)) { // Excel does not support positive/negative infinities, // rather, it gives a #DIV/0! error in these cases. SetCellErrorValue(FormulaError.DIV0.Code); } else if (double.IsNaN(value)) { // Excel does not support Not-a-Number (NaN), // instead it immediately generates a #NUM! error. SetCellErrorValue(FormulaError.NUM.Code); } else { int row = record.Row; int col = record.Column; short styleIndex = record.XFIndex; switch (cellType) { case CellType.NUMERIC: ((NumberRecord)record).Value = value; break; case CellType.FORMULA: ((FormulaRecordAggregate)record).SetCachedDoubleResult(value); break; default: SetCellType(CellType.NUMERIC, false, row, col, styleIndex); ((NumberRecord)record).Value = value; break; } } } /// /// Set a date value for the cell. Excel treats dates as numeric so you will need to format the cell as /// a date. /// /// the date value to Set this cell to. For formulas we'll Set the /// precalculated value, for numerics we'll Set its value. For other types we /// will Change the cell to a numeric cell and Set its value. public void SetCellValue(DateTime value) { SetCellValue(DateUtil.GetExcelDate(value, this.book.Workbook.IsUsing1904DateWindowing)); } /// /// Set a string value for the cell. Please note that if you are using /// full 16 bit Unicode you should call SetEncoding() first. /// /// 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 null then we will Change the cell to a Blank cell. public void SetCellValue(String value) { HSSFRichTextString str = new HSSFRichTextString(value); SetCellValue(str); } /** * set a error value for the cell * * @param errorCode the error value to set this cell to. For formulas we'll set the * precalculated value , for errors we'll set * its value. For other types we will change the cell to an error * cell and set its value. */ public void SetCellErrorValue(byte errorCode) { int row = record.Row; int col = record.Column; short styleIndex = record.XFIndex; switch (cellType) { case CellType.ERROR: ((BoolErrRecord)record).SetValue(errorCode); break; case CellType.FORMULA: ((FormulaRecordAggregate)record).SetCachedErrorResult(errorCode); break; default: SetCellType(CellType.ERROR, false, row, col, styleIndex); ((BoolErrRecord)record).SetValue(errorCode); break; } } /// /// Set a string value for the cell. Please note that if you are using /// full 16 bit Unicode you should call SetEncoding() first. /// /// 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 null then we will Change the cell to a Blank cell. public void SetCellValue(IRichTextString value) { HSSFRichTextString hvalue = (HSSFRichTextString)value; int row = record.Row; int col = record.Column; short styleIndex = record.XFIndex; if (hvalue == null) { NotifyFormulaChanging(); SetCellType(CellType.BLANK, false, row, col, styleIndex); return; } if (hvalue.Length > HH.WMS.Utils.NPOI.SS.SpreadsheetVersion.EXCEL97.MaxTextLength) { throw new ArgumentException("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.String); // Update our local cache to the un-formatted version stringValue = new HSSFRichTextString(value.String); return; } if (cellType != CellType.STRING) { SetCellType(CellType.STRING, false, row, col, styleIndex); } int index = 0; UnicodeString str = hvalue.UnicodeString; index = book.Workbook.AddSSTString(str); ((LabelSSTRecord)record).SSTIndex = index; stringValue = hvalue; stringValue.SetWorkbookReferences(book.Workbook, ((LabelSSTRecord)record)); stringValue.UnicodeString = book.Workbook.GetSSTString(index); } /** * Should be called any time that a formula could potentially be deleted. * Does nothing if this cell currently does not hold a formula */ private void NotifyFormulaChanging() { if (record is FormulaRecordAggregate) { ((FormulaRecordAggregate)record).NotifyFormulaChanging(); } } /// /// Gets or sets the cell formula. /// /// The cell formula. public String CellFormula { get { if (!(record is FormulaRecordAggregate)) throw TypeMismatch(CellType.FORMULA, cellType, true); return HSSFFormulaParser.ToFormulaString(book, ((FormulaRecordAggregate)record).FormulaTokens); } set { SetCellFormula(value); } } public void SetCellFormula(String formula) { if (IsPartOfArrayFormulaGroup) { NotifyArrayFormulaChanging(); } int row = record.Row; int col = record.Column; short styleIndex = record.XFIndex; if (string.IsNullOrEmpty(formula)) { NotifyFormulaChanging(); SetCellType(CellType.BLANK, false, row, col, styleIndex); return; } int sheetIndex = book.GetSheetIndex(sheet); Ptg[] ptgs = HSSFFormulaParser.Parse(formula, book, FormulaType.CELL, sheetIndex); SetCellType(CellType.FORMULA, false, row, col, styleIndex); FormulaRecordAggregate agg = (FormulaRecordAggregate)record; FormulaRecord frec = agg.FormulaRecord; frec.Options = ((short)2); frec.Value = (0); //only set to default if there is no extended format index already set if (agg.XFIndex == (short)0) { agg.XFIndex = ((short)0x0f); } agg.SetParsedExpression(ptgs); } /// /// Get the value of the cell as a number. For strings we throw an exception. /// For blank cells we return a 0. /// /// The numeric cell value. public double NumericCellValue { get { switch (cellType) { case CellType.BLANK: return 0.0; case CellType.NUMERIC: return ((NumberRecord)record).Value; case CellType.FORMULA: break; default: throw TypeMismatch(CellType.NUMERIC, cellType, false); } FormulaRecord fr = ((FormulaRecordAggregate)record).FormulaRecord; CheckFormulaCachedValueType(CellType.NUMERIC, fr); return fr.Value; } } /// /// Used to help format error messages /// /// The cell type code. /// private String GetCellTypeName(CellType cellTypeCode) { switch (cellTypeCode) { case CellType.BLANK: return "blank"; case CellType.STRING: return "text"; case CellType.BOOLEAN: return "boolean"; case CellType.ERROR: return "error"; case CellType.NUMERIC: return "numeric"; case CellType.FORMULA: return "formula"; } return "#unknown cell type (" + cellTypeCode + ")#"; } /// /// Types the mismatch. /// /// The expected type code. /// The actual type code. /// if set to true [is formula cell]. /// private Exception TypeMismatch(CellType expectedTypeCode, CellType actualTypeCode, bool isFormulaCell) { String msg = "Cannot get a " + GetCellTypeName(expectedTypeCode) + " value from a " + GetCellTypeName(actualTypeCode) + " " + (isFormulaCell ? "formula " : "") + "cell"; return new InvalidOperationException(msg); } /// /// Checks the type of the formula cached value. /// /// The expected type code. /// The fr. private void CheckFormulaCachedValueType(CellType expectedTypeCode, FormulaRecord fr) { CellType cachedValueType = fr.CachedResultType; if (cachedValueType != expectedTypeCode) { throw TypeMismatch(expectedTypeCode, cachedValueType, true); } } /// /// Get the value of the cell as a date. For strings we throw an exception. /// For blank cells we return a null. /// /// The date cell value. public DateTime DateCellValue { get { if (cellType == CellType.BLANK) { return DateTime.MaxValue; } if (cellType == CellType.STRING) { throw new InvalidDataException( "You cannot get a date value from a String based cell"); } if (cellType == CellType.BOOLEAN) { throw new InvalidDataException( "You cannot get a date value from a bool cell"); } if (cellType == CellType.ERROR) { throw new InvalidDataException( "You cannot get a date value from an error cell"); } double value = this.NumericCellValue; if (book.Workbook.IsUsing1904DateWindowing) { return DateUtil.GetJavaDate(value, true); } else { return DateUtil.GetJavaDate(value, false); } } } /// /// Get the value of the cell as a string - for numeric cells we throw an exception. /// For blank cells we return an empty string. /// For formulaCells that are not string Formulas, we return empty String /// /// The string cell value. public String StringCellValue { get { IRichTextString str = RichStringCellValue; return str.String; } } /// /// Get the value of the cell as a string - for numeric cells we throw an exception. /// For blank cells we return an empty string. /// For formulaCells that are not string Formulas, we return empty String /// /// The rich string cell value. public IRichTextString RichStringCellValue { get { switch (cellType) { case CellType.BLANK: return new HSSFRichTextString(""); case CellType.STRING: return stringValue; case CellType.FORMULA: break; default: throw TypeMismatch(CellType.STRING, cellType, false); } FormulaRecordAggregate fra = ((FormulaRecordAggregate)record); CheckFormulaCachedValueType(CellType.STRING, fra.FormulaRecord); String strVal = fra.StringValue; return new HSSFRichTextString(strVal == null ? "" : strVal); } } /// /// Set a bool value for the cell /// /// the bool value to Set this cell to. For formulas we'll Set the /// precalculated value, for bools we'll Set its value. For other types we /// will Change the cell to a bool cell and Set its value. public void SetCellValue(bool value) { int row = record.Row; int col = record.Column; short styleIndex = record.XFIndex; switch (cellType) { case CellType.BOOLEAN: ((BoolErrRecord)record).SetValue(value); break; case CellType.FORMULA: ((FormulaRecordAggregate)record).SetCachedBooleanResult(value); break; default: SetCellType(CellType.BOOLEAN, false, row, col, styleIndex); ((BoolErrRecord)record).SetValue(value); break; } } /// /// Chooses a new bool value for the cell when its type is changing. /// Usually the caller is calling SetCellType() with the intention of calling /// SetCellValue(bool) straight afterwards. This method only exists to give /// the cell a somewhat reasonable value until the SetCellValue() call (if at all). /// TODO - perhaps a method like SetCellTypeAndValue(int, Object) should be introduced to avoid this /// /// private bool ConvertCellValueToBoolean() { switch (cellType) { case CellType.BOOLEAN: return ((BoolErrRecord)record).BooleanValue; case CellType.STRING: int sstIndex = ((LabelSSTRecord)record).SSTIndex; String text = book.Workbook.GetSSTString(sstIndex).String; return Convert.ToBoolean(text, CultureInfo.CurrentCulture); case CellType.NUMERIC: return ((NumberRecord)record).Value != 0; // All other cases Convert to false // These choices are not well justified. case CellType.FORMULA: // use cached formula result if it's the right type: FormulaRecord fr = ((FormulaRecordAggregate)record).FormulaRecord; CheckFormulaCachedValueType(CellType.BOOLEAN, fr); return fr.CachedBooleanValue; // Other cases convert to false // These choices are not well justified. case CellType.ERROR: case CellType.BLANK: return false; } throw new Exception("Unexpected cell type (" + cellType + ")"); } /// /// Get the value of the cell as a bool. For strings, numbers, and errors, we throw an exception. /// For blank cells we return a false. /// /// true if [boolean cell value]; otherwise, false. public bool BooleanCellValue { get { switch (cellType) { case CellType.BLANK: return false; case CellType.BOOLEAN: return ((BoolErrRecord)record).BooleanValue; case CellType.FORMULA: break; default: throw TypeMismatch(CellType.BOOLEAN, cellType, false); } FormulaRecord fr = ((FormulaRecordAggregate)record).FormulaRecord; CheckFormulaCachedValueType(CellType.BOOLEAN, fr); return fr.CachedBooleanValue; } } /// /// Get the value of the cell as an error code. For strings, numbers, and bools, we throw an exception. /// For blank cells we return a 0. /// /// The error cell value. public byte ErrorCellValue { get { switch (cellType) { case CellType.ERROR: return ((BoolErrRecord)record).ErrorValue; case CellType.FORMULA: break; default: throw TypeMismatch(CellType.ERROR, cellType, false); } FormulaRecord fr = ((FormulaRecordAggregate)record).FormulaRecord; CheckFormulaCachedValueType(CellType.ERROR, fr); return (byte)fr.CachedErrorValue; } } /// /// Get the style for the cell. This is a reference to a cell style contained in the workbook /// object. /// /// The cell style. public ICellStyle CellStyle { get { short styleIndex = record.XFIndex; ExtendedFormatRecord xf = book.Workbook.GetExFormatAt(styleIndex); return new HSSFCellStyle(styleIndex, xf, book); } set { // Verify it really does belong to our workbook ((HSSFCellStyle)value).VerifyBelongsToWorkbook(book); short styleIndex; if (((HSSFCellStyle)value).UserStyleName != null) { styleIndex = ApplyUserCellStyle((HSSFCellStyle)value); } else { styleIndex = value.Index; } // Change our cell record to use this style record.XFIndex = value.Index; } } /** * Applying a user-defined style (UDS) is special. Excel does not directly reference user-defined styles, but * instead create a 'proxy' ExtendedFormatRecord referencing the UDS as parent. * * The proceudre to apply a UDS is as follows: * * 1. search for a ExtendedFormatRecord with parentIndex == style.getIndex() * and xfType == ExtendedFormatRecord.XF_CELL. * 2. if not found then create a new ExtendedFormatRecord and copy all attributes from the user-defined style * and set the parentIndex to be style.getIndex() * 3. return the index of the ExtendedFormatRecord, this will be assigned to the parent cell record * * @param style the user style to apply * * @return the index of a ExtendedFormatRecord record that will be referenced by the cell */ private short ApplyUserCellStyle(HSSFCellStyle style) { if (style.UserStyleName == null) { throw new ArgumentException("Expected user-defined style"); } InternalWorkbook iwb = book.Workbook; short userXf = -1; int numfmt = iwb.NumExFormats; for (short i = 0; i < numfmt; i++) { ExtendedFormatRecord xf = iwb.GetExFormatAt(i); if (xf.XFType == ExtendedFormatRecord.XF_CELL && xf.ParentIndex == style.Index) { userXf = i; break; } } short styleIndex; if (userXf == -1) { ExtendedFormatRecord xfr = iwb.CreateCellXF(); xfr.CloneStyleFrom(iwb.GetExFormatAt(style.Index)); xfr.IndentionOptions = (short)0; xfr.XFType = (ExtendedFormatRecord.XF_CELL); xfr.ParentIndex = (style.Index); styleIndex = (short)numfmt; } else { styleIndex = userXf; } return styleIndex; } /// /// Should only be used by HSSFSheet and friends. Returns the low level CellValueRecordInterface record /// /// the cell via the low level api. public CellValueRecordInterface CellValueRecord { get { return record; } } /// /// Checks the bounds. /// /// The cell num. /// if the bounds are exceeded. private void CheckBounds(int cellIndex) { if (cellIndex < 0 || cellIndex > LAST_COLUMN_NUMBER) { throw new ArgumentException("Invalid column index (" + cellIndex + "). Allowable column range for " + FILE_FORMAT_NAME + " is (0.." + LAST_COLUMN_NUMBER + ") or ('A'..'" + LAST_COLUMN_NAME + "')"); } } /// /// Sets this cell as the active cell for the worksheet /// public void SetAsActiveCell() { int row = record.Row; int col = record.Column; this.sheet.Sheet.SetActiveCell(row, col); } /// /// Returns a string representation of the cell /// This method returns a simple representation, /// anthing more complex should be in user code, with /// knowledge of the semantics of the sheet being Processed. /// Formula cells return the formula string, /// rather than the formula result. /// Dates are Displayed in dd-MMM-yyyy format /// Errors are Displayed as #ERR<errIdx> /// public override String ToString() { switch (CellType) { case CellType.BLANK: return ""; case CellType.BOOLEAN: return BooleanCellValue ? "TRUE" : "FALSE"; case CellType.ERROR: return HH.WMS.Utils.NPOI.SS.Formula.Eval.ErrorEval.GetText(((BoolErrRecord)record).ErrorValue); case CellType.FORMULA: return CellFormula; case CellType.NUMERIC: string format = this.CellStyle.GetDataFormatString(); DataFormatter formatter = new DataFormatter(); return formatter.FormatCellValue(this); case CellType.STRING: return StringCellValue; default: return "Unknown Cell Type: " + CellType; } } /// /// Returns comment associated with this cell /// /// The cell comment associated with this cell. public IComment CellComment { get { if (comment == null) { comment = FindCellComment(sheet.Sheet, record.Row, record.Column); } return comment; } set { if (value == null) { RemoveCellComment(); return; } value.Row = record.Row; value.Column = record.Column; this.comment = value; } } /// /// Removes the comment for this cell, if /// there is one. /// /// WARNING - some versions of excel will loose /// all comments after performing this action! public void RemoveCellComment() { HSSFComment comment = FindCellComment(sheet.Sheet, record.Row, record.Column); this.comment = null; if (comment == null) { // Nothing to do return; } // Zap the underlying NoteRecord IList sheetRecords = sheet.Sheet.Records; sheetRecords.Remove(comment.NoteRecord); // If we have a TextObjectRecord, is should // be proceeed by: // MSODRAWING with container // OBJ // MSODRAWING with EscherTextboxRecord if (comment.TextObjectRecord != null) { TextObjectRecord txo = comment.TextObjectRecord; int txoAt = sheetRecords.IndexOf(txo); if (sheetRecords[txoAt - 3] is DrawingRecord && sheetRecords[txoAt - 2] is ObjRecord && sheetRecords[txoAt - 1] is DrawingRecord) { // Zap these, in reverse order sheetRecords.RemoveAt(txoAt - 1); sheetRecords.RemoveAt(txoAt - 2); sheetRecords.RemoveAt(txoAt - 3); } else { throw new InvalidOperationException("Found the wrong records before the TextObjectRecord, can't Remove comment"); } // Now Remove the text record sheetRecords.Remove(txo); } } /// /// Cell comment Finder. /// Returns cell comment for the specified sheet, row and column. /// /// The sheet. /// The row. /// The column. /// cell comment or /// null /// if not found public static HSSFComment FindCellComment(InternalSheet sheet, int row, int column) { HSSFComment comment = null; Dictionary noteTxo = new Dictionary(); //map shapeId and TextObjectRecord int i = 0; for (IEnumerator it = sheet.Records.GetEnumerator(); it.MoveNext(); ) { RecordBase rec = (RecordBase)it.Current; if (rec is NoteRecord) { NoteRecord note = (NoteRecord)rec; if (note.Row == row && note.Column == column) { if (i < noteTxo.Count) { TextObjectRecord txo = (TextObjectRecord)noteTxo[note.ShapeId]; comment = new HSSFComment(note, txo); comment.Row = note.Row; comment.Column = note.Column; comment.Author = note.Author; comment.Visible = (note.Flags == NoteRecord.NOTE_VISIBLE); comment.String = txo.Str; break; } } } else if (rec is ObjRecord) { ObjRecord obj = (ObjRecord)rec; SubRecord sub = obj.SubRecords[0]; if (sub is CommonObjectDataSubRecord) { CommonObjectDataSubRecord cmo = (CommonObjectDataSubRecord)sub; if (cmo.ObjectType == CommonObjectType.COMMENT) { //Find the nearest TextObjectRecord which holds comment's text and map it to its shapeId while (it.MoveNext()) { rec = (Record)it.Current; if (rec is TextObjectRecord) { noteTxo.Add(cmo.ObjectId, (TextObjectRecord)rec); break; } } } } } } return comment; } /// /// Gets the index of the column. /// /// The index of the column. public int ColumnIndex { get { return record.Column & 0xFFFF; } } /** * Updates the cell record's idea of what * column it belongs in (0 based) * @param num the new cell number */ internal void UpdateCellNum(int num) { record.Column = num; } /// /// Gets the (zero based) index of the row containing this cell /// /// The index of the row. public int RowIndex { get { return record.Row; } } /// /// Returns hyperlink associated with this cell /// /// The hyperlink associated with this cell or null if not found public IHyperlink Hyperlink { get { for (IEnumerator it = sheet.Sheet.Records.GetEnumerator(); it.MoveNext(); ) { RecordBase rec = (RecordBase)it.Current; if (rec is HyperlinkRecord) { HyperlinkRecord link = (HyperlinkRecord)rec; if (link.FirstColumn == record.Column && link.FirstRow == record.Row) { return new HSSFHyperlink(link); } } } return null; } set { value.FirstRow = record.Row; value.LastRow = record.Row; value.FirstColumn = record.Column; value.LastColumn = record.Column; switch (value.Type) { case HyperlinkType.EMAIL: case HyperlinkType.URL: value.Label = ("url"); break; case HyperlinkType.FILE: value.Label = ("file"); break; case HyperlinkType.DOCUMENT: value.Label = ("place"); break; } int eofLoc = sheet.Sheet.FindFirstRecordLocBySid(EOFRecord.sid); sheet.Sheet.Records.Insert(eofLoc, ((HSSFHyperlink)value).record); } } /// /// Only valid for formula cells /// /// one of (CellType.NUMERIC,CellType.STRING, CellType.BOOLEAN, CellType.ERROR) depending /// on the cached value of the formula public CellType CachedFormulaResultType { get { if (this.cellType != CellType.FORMULA) { throw new InvalidOperationException("Only formula cells have cached results"); } return ((FormulaRecordAggregate)record).FormulaRecord.CachedResultType; } } public bool IsPartOfArrayFormulaGroup { get { if (cellType != CellType.FORMULA) { return false; } return ((FormulaRecordAggregate)record).IsPartOfArrayFormula; } } internal void SetCellArrayFormula(CellRangeAddress range) { int row = record.Row; int col = record.Column; short styleIndex = record.XFIndex; SetCellType(CellType.FORMULA, false, row, col, styleIndex); // Billet for formula in rec Ptg[] ptgsForCell = { new ExpPtg(range.FirstRow, range.FirstColumn) }; FormulaRecordAggregate agg = (FormulaRecordAggregate)record; agg.SetParsedExpression(ptgsForCell); } public CellRangeAddress GetArrayFormulaRange() { if (cellType != CellType.FORMULA) { String ref1 = new CellReference(this).FormatAsString(); throw new InvalidOperationException("Cell " + ref1 + " is not part of an array formula."); } return ((FormulaRecordAggregate)record).GetArrayFormulaRange(); } /// /// The purpose of this method is to validate the cell state prior to modification /// /// internal void NotifyArrayFormulaChanging(String msg) { CellRangeAddress cra = GetArrayFormulaRange(); if (cra.NumberOfCells > 1) { throw new InvalidOperationException(msg); } //un-register the single-cell array formula from the parent XSSFSheet this.Row.Sheet.RemoveArrayFormula(this); } /// /// Called when this cell is modified. /// The purpose of this method is to validate the cell state prior to modification. /// internal void NotifyArrayFormulaChanging() { CellReference ref1 = new CellReference(this); String msg = "Cell " + ref1.FormatAsString() + " is part of a multi-cell array formula. " + "You cannot change part of an array."; NotifyArrayFormulaChanging(msg); } public bool IsMergedCell { get { foreach (CellRangeAddress range in sheet.Sheet.MergedRecords.MergedRegions) { if (range.FirstColumn <= this.ColumnIndex && range.LastColumn >= this.ColumnIndex && range.FirstRow <= this.RowIndex && range.LastRow >= this.RowIndex) { return true; } } return false; } } } }