/* ====================================================================
|
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;
|
|
/// <summary>
|
/// 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.
|
/// </summary>
|
/// <remarks>
|
/// @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
|
/// </remarks>
|
[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;
|
|
/// <summary>
|
/// 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.
|
/// </summary>
|
/// <param name="book">Workbook record of the workbook containing this cell</param>
|
/// <param name="sheet">Sheet record of the sheet containing this cell</param>
|
/// <param name="row">the row of this cell</param>
|
/// <param name="col">the column for this cell</param>
|
public HSSFCell(HSSFWorkbook book, HSSFSheet sheet, int row, short col)
|
: this(book, sheet, row, col, CellType.BLANK)
|
{
|
}
|
|
/// <summary>
|
/// Creates new Cell - Should only be called by HSSFRow. This Creates a cell
|
/// from scratch.
|
/// </summary>
|
/// <param name="book">Workbook record of the workbook containing this cell</param>
|
/// <param name="sheet">Sheet record of the sheet containing this cell</param>
|
/// <param name="row">the row of this cell</param>
|
/// <param name="col">the column for this cell</param>
|
/// <param name="type">CellType.NUMERIC, CellType.STRING, CellType.FORMULA, CellType.BLANK,
|
/// CellType.BOOLEAN, CellType.ERROR</param>
|
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);
|
}
|
|
/// <summary>
|
/// Creates an Cell from a CellValueRecordInterface. HSSFSheet uses this when
|
/// reading in cells from an existing sheet.
|
/// </summary>
|
/// <param name="book">Workbook record of the workbook containing this cell</param>
|
/// <param name="sheet">Sheet record of the sheet containing this cell</param>
|
/// <param name="cval">the Cell Value Record we wish to represent</param>
|
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);
|
}
|
}
|
|
/// <summary>
|
/// Set the cells type (numeric, formula or string)
|
/// </summary>
|
/// <param name="cellType">Type of the cell.</param>
|
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);
|
}
|
|
/// <summary>
|
/// 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
|
/// </summary>
|
/// <param name="cellType">Type of the cell.</param>
|
/// <param name="setValue">if set to <c>true</c> [set value].</param>
|
/// <param name="row">The row.</param>
|
/// <param name="col">The col.</param>
|
/// <param name="styleIndex">Index of the style.</param>
|
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;
|
}
|
|
/// <summary>
|
/// Get the cells type (numeric, formula or string)
|
/// </summary>
|
/// <value>The type of the cell.</value>
|
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 + ")");
|
|
}
|
/// <summary>
|
/// Set a numeric value for the cell
|
/// </summary>
|
/// <param name="value">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.</param>
|
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;
|
}
|
}
|
|
}
|
|
/// <summary>
|
/// Set a date value for the cell. Excel treats dates as numeric so you will need to format the cell as
|
/// a date.
|
/// </summary>
|
/// <param name="value">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.</param>
|
public void SetCellValue(DateTime value)
|
{
|
SetCellValue(DateUtil.GetExcelDate(value, this.book.Workbook.IsUsing1904DateWindowing));
|
}
|
|
|
/// <summary>
|
/// Set a string value for the cell. Please note that if you are using
|
/// full 16 bit Unicode you should call SetEncoding() first.
|
/// </summary>
|
/// <param name="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 null then we will Change the cell to a Blank cell.</param>
|
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;
|
}
|
}
|
/// <summary>
|
/// Set a string value for the cell. Please note that if you are using
|
/// full 16 bit Unicode you should call SetEncoding() first.
|
/// </summary>
|
/// <param name="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 null then we will Change the cell to a Blank cell.</param>
|
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();
|
}
|
}
|
|
/// <summary>
|
/// Gets or sets the cell formula.
|
/// </summary>
|
/// <value>The cell formula.</value>
|
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);
|
}
|
|
/// <summary>
|
/// Get the value of the cell as a number. For strings we throw an exception.
|
/// For blank cells we return a 0.
|
/// </summary>
|
/// <value>The numeric cell value.</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;
|
}
|
}
|
|
/// <summary>
|
/// Used to help format error messages
|
/// </summary>
|
/// <param name="cellTypeCode">The cell type code.</param>
|
/// <returns></returns>
|
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 + ")#";
|
}
|
|
|
/// <summary>
|
/// Types the mismatch.
|
/// </summary>
|
/// <param name="expectedTypeCode">The expected type code.</param>
|
/// <param name="actualTypeCode">The actual type code.</param>
|
/// <param name="isFormulaCell">if set to <c>true</c> [is formula cell].</param>
|
/// <returns></returns>
|
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);
|
}
|
|
/// <summary>
|
/// Checks the type of the formula cached value.
|
/// </summary>
|
/// <param name="expectedTypeCode">The expected type code.</param>
|
/// <param name="fr">The fr.</param>
|
private void CheckFormulaCachedValueType(CellType expectedTypeCode, FormulaRecord fr)
|
{
|
CellType cachedValueType = fr.CachedResultType;
|
if (cachedValueType != expectedTypeCode)
|
{
|
throw TypeMismatch(expectedTypeCode, cachedValueType, true);
|
}
|
}
|
|
|
/// <summary>
|
/// Get the value of the cell as a date. For strings we throw an exception.
|
/// For blank cells we return a null.
|
/// </summary>
|
/// <value>The date cell value.</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);
|
}
|
}
|
}
|
|
/// <summary>
|
/// 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
|
/// </summary>
|
/// <value>The string cell value.</value>
|
public String StringCellValue
|
{
|
get
|
{
|
IRichTextString str = RichStringCellValue;
|
return str.String;
|
}
|
}
|
|
/// <summary>
|
/// 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
|
/// </summary>
|
/// <value>The rich string cell value.</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);
|
}
|
}
|
|
/// <summary>
|
/// Set a bool value for the cell
|
/// </summary>
|
/// <param name="value">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.</param>
|
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;
|
}
|
}
|
/// <summary>
|
/// 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
|
/// </summary>
|
/// <returns></returns>
|
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 + ")");
|
}
|
|
/// <summary>
|
/// 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.
|
/// </summary>
|
/// <value><c>true</c> if [boolean cell value]; otherwise, <c>false</c>.</value>
|
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;
|
}
|
}
|
|
/// <summary>
|
/// 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.
|
/// </summary>
|
/// <value>The error cell value.</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;
|
}
|
}
|
|
/// <summary>
|
/// Get the style for the cell. This is a reference to a cell style contained in the workbook
|
/// object.
|
/// </summary>
|
/// <value>The cell style.</value>
|
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;
|
}
|
|
/// <summary>
|
/// Should only be used by HSSFSheet and friends. Returns the low level CellValueRecordInterface record
|
/// </summary>
|
/// <value>the cell via the low level api.</value>
|
public CellValueRecordInterface CellValueRecord
|
{
|
get { return record; }
|
}
|
|
/// <summary>
|
/// Checks the bounds.
|
/// </summary>
|
/// <param name="cellIndex">The cell num.</param>
|
/// <exception cref="Exception">if the bounds are exceeded.</exception>
|
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 + "')");
|
}
|
}
|
|
/// <summary>
|
/// Sets this cell as the active cell for the worksheet
|
/// </summary>
|
public void SetAsActiveCell()
|
{
|
int row = record.Row;
|
int col = record.Column;
|
|
this.sheet.Sheet.SetActiveCell(row, col);
|
}
|
|
/// <summary>
|
/// 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>
|
/// </summary>
|
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;
|
}
|
|
}
|
|
|
/// <summary>
|
/// Returns comment associated with this cell
|
/// </summary>
|
/// <value>The cell comment associated with this cell.</value>
|
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;
|
}
|
}
|
|
/// <summary>
|
/// Removes the comment for this cell, if
|
/// there is one.
|
/// </summary>
|
/// <remarks>WARNING - some versions of excel will loose
|
/// all comments after performing this action!</remarks>
|
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);
|
}
|
}
|
|
/// <summary>
|
/// Cell comment Finder.
|
/// Returns cell comment for the specified sheet, row and column.
|
/// </summary>
|
/// <param name="sheet">The sheet.</param>
|
/// <param name="row">The row.</param>
|
/// <param name="column">The column.</param>
|
/// <returns>cell comment or
|
/// <c>null</c>
|
/// if not found</returns>
|
public static HSSFComment FindCellComment(InternalSheet sheet, int row, int column)
|
{
|
HSSFComment comment = null;
|
Dictionary<int, TextObjectRecord> noteTxo = new Dictionary<int, TextObjectRecord>(); //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;
|
}
|
/// <summary>
|
/// Gets the index of the column.
|
/// </summary>
|
/// <value>The index of the column.</value>
|
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;
|
}
|
/// <summary>
|
/// Gets the (zero based) index of the row containing this cell
|
/// </summary>
|
/// <value>The index of the row.</value>
|
public int RowIndex
|
{
|
get
|
{
|
return record.Row;
|
}
|
}
|
/// <summary>
|
/// Returns hyperlink associated with this cell
|
/// </summary>
|
/// <value>The hyperlink associated with this cell or null if not found</value>
|
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);
|
}
|
}
|
/// <summary>
|
/// Only valid for formula cells
|
/// </summary>
|
/// <value>one of (CellType.NUMERIC,CellType.STRING, CellType.BOOLEAN, CellType.ERROR) depending
|
/// on the cached value of the formula</value>
|
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();
|
}
|
/// <summary>
|
/// The purpose of this method is to validate the cell state prior to modification
|
/// </summary>
|
/// <param name="msg"></param>
|
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);
|
}
|
|
/// <summary>
|
/// Called when this cell is modified.
|
/// The purpose of this method is to validate the cell state prior to modification.
|
/// </summary>
|
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;
|
}
|
}
|
}
|
}
|
