protected RefPtgBase(CellReference c)
{
Row = (c.Row);
Column = (c.Col);
IsColRelative = (!c.IsColAbsolute);
IsRowRelative = (!c.IsRowAbsolute);
}
/**
* Takes in a String representation of a cell reference and Fills out the
* numeric fields.
*/
protected RefPtgBase(String cellref)
{
CellReference c = new CellReference(cellref);
Row = c.Row;
Column = c.Col;
IsColRelative = !c.IsColAbsolute;
IsRowRelative = !c.IsRowAbsolute;
}
public Ref3DPtg(String cellref, int externIdx)
{
CellReference c = new CellReference(cellref);
Row=c.Row;
Column=c.Col;
IsColRelative=!c.IsColAbsolute;
IsRowRelative=!c.IsRowAbsolute;
ExternSheetIndex=externIdx;
}
/**
* Create an area ref from a string representation. Sheet names containing special Chars should be
* delimited and escaped as per normal syntax rules for formulas.<br/>
* The area reference must be contiguous (i.e. represent a single rectangle, not a Union of rectangles)
*/
public AreaReference(String reference)
{
if (!IsContiguous(reference))
{
throw new ArgumentException(
"References passed to the AreaReference must be contiguous, " +
"use generateContiguous(ref) if you have non-contiguous references");
}
String[] parts = SeparateAreaRefs(reference);
String part0 = parts[0];
if (parts.Length == 1)
{
// TODO - probably shouldn't initialize area ref when text is really a cell ref
// Need to fix some named range stuff to get rid of this
_firstCell = new CellReference(part0);
_lastCell = _firstCell;
_isSingleCell = true;
return;
}
if (parts.Length != 2)
{
throw new ArgumentException("Bad area ref '" + reference + "'");
}
String part1 = parts[1];
if (IsPlainColumn(part0))
{
if (!IsPlainColumn(part1))
{
throw new Exception("Bad area ref '" + reference + "'");
}
// Special handling for whole-column references
// Represented internally as x$1 to x$65536
// which is the maximum range of rows
bool firstIsAbs = CellReference.IsPartAbsolute(part0);
bool lastIsAbs = CellReference.IsPartAbsolute(part1);
int col0 = CellReference.ConvertColStringToIndex(part0);
int col1 = CellReference.ConvertColStringToIndex(part1);
_firstCell = new CellReference(0, col0, true, firstIsAbs);
_lastCell = new CellReference(0xFFFF, col1, true, lastIsAbs);
_isSingleCell = false;
// TODO - whole row refs
}
else
{
_firstCell = new CellReference(part0);
_lastCell = new CellReference(part1);
_isSingleCell = part0.Equals(part1);
}
}
public override String ToString()
{
CellReference cr = new CellReference(Row, Column);
StringBuilder sb = new StringBuilder();
sb.Append(GetType().Name).Append("[");
sb.Append(_evaluator.SheetName);
sb.Append('!');
sb.Append(cr.FormatAsString());
sb.Append("]");
return sb.ToString();
}
public override String ToString()
{
CellReference cr = new CellReference(Row, Column, !IsRowRelative, !IsColRelative);
StringBuilder sb = new StringBuilder();
sb.Append(GetType().Name);
sb.Append(" [");
sb.Append("sheetIx=").Append(ExternSheetIndex);
sb.Append(" ! ");
sb.Append(cr.FormatAsString());
sb.Append("]");
return sb.ToString();
}
public ValueEval Evaluate(ValueEval[] args, int srcRowIndex,
int srcColumnIndex)
{
if (args.Length < 2 || args.Length > 5)
{
return ErrorEval.VALUE_INVALID;
}
try
{
bool pAbsRow, pAbsCol;
int row = (int)NumericFunction.SingleOperandEvaluate(args[0], srcRowIndex, srcColumnIndex);
int col = (int)NumericFunction.SingleOperandEvaluate(args[1], srcRowIndex, srcColumnIndex);
int refType;
if (args.Length > 2)
{
refType = (int)NumericFunction.SingleOperandEvaluate(args[2], srcRowIndex, srcColumnIndex);
}
else
{
refType = REF_ABSOLUTE;
}
switch (refType)
{
case REF_ABSOLUTE:
pAbsRow = true;
pAbsCol = true;
break;
case REF_ROW_ABSOLUTE_COLUMN_RELATIVE:
pAbsRow = true;
pAbsCol = false;
break;
case REF_ROW_RELATIVE_RELATIVE_ABSOLUTE:
pAbsRow = false;
pAbsCol = true;
break;
case REF_RELATIVE:
pAbsRow = false;
pAbsCol = false;
break;
default:
throw new EvaluationException(ErrorEval.VALUE_INVALID);
}
bool a1;
if (args.Length > 3)
{
ValueEval ve = OperandResolver.GetSingleValue(args[3], srcRowIndex, srcColumnIndex);
// TODO R1C1 style is not yet supported
a1 = ve == MissingArgEval.instance ? true : OperandResolver.CoerceValueToBoolean(ve, false).Value;
}
else
{
a1 = true;
}
String sheetName;
if (args.Length == 5)
{
ValueEval ve = OperandResolver.GetSingleValue(args[4], srcRowIndex, srcColumnIndex);
sheetName = ve == MissingArgEval.instance ? null : OperandResolver.CoerceValueToString(ve);
}
else
{
sheetName = null;
}
CellReference ref1 = new CellReference(row - 1, col - 1, pAbsRow, pAbsCol);
StringBuilder sb = new StringBuilder(32);
if (sheetName != null)
{
SheetNameFormatter.AppendFormat(sb, sheetName);
sb.Append('!');
}
sb.Append(ref1.FormatAsString());
return new StringEval(sb.ToString());
}
catch (EvaluationException e)
{
return e.GetErrorEval();
}
}
/**
* Also collects any loose MergeCellRecords and puts them in the supplied
* mergedCellsTable
*/
public RowBlocksReader(RecordStream rs)
{
ArrayList plainRecords = new ArrayList();
ArrayList shFrmRecords = new ArrayList();
ArrayList arrayRecords = new ArrayList();
ArrayList tableRecords = new ArrayList();
ArrayList mergeCellRecords = new ArrayList();
List<CellReference> firstCellRefs = new List<CellReference>();
Record prevRec = null;
while (!RecordOrderer.IsEndOfRowBlock(rs.PeekNextSid()))
{
// End of row/cell records for the current sheet
// Note - It is important that this code does not inadvertently add any sheet
// records from a subsequent sheet. For example, if SharedFormulaRecords
// are taken from the wrong sheet, this could cause bug 44449.
if (!rs.HasNext())
{
throw new InvalidOperationException("Failed to find end of row/cell records");
}
Record rec = rs.GetNext();
ArrayList dest;
switch (rec.Sid)
{
case MergeCellsRecord.sid:
dest = mergeCellRecords;
break;
case SharedFormulaRecord.sid:
dest = shFrmRecords;
if (!(prevRec is FormulaRecord))
{
throw new Exception("Shared formula record should follow a FormulaRecord");
}
FormulaRecord fr = (FormulaRecord)prevRec;
firstCellRefs.Add(new CellReference(fr.Row, fr.Column));
break;
case ArrayRecord.sid:
dest = arrayRecords;
break;
case TableRecord.sid:
dest = tableRecords;
break;
default: dest = plainRecords;
break;
}
dest.Add(rec);
prevRec = rec;
}
SharedFormulaRecord[] sharedFormulaRecs = new SharedFormulaRecord[shFrmRecords.Count];
List<ArrayRecord> arrayRecs = new List<ArrayRecord>(arrayRecords.Count);
List<TableRecord> tableRecs = new List<TableRecord>(tableRecords.Count);
sharedFormulaRecs = (SharedFormulaRecord[])shFrmRecords.ToArray(typeof(SharedFormulaRecord));
CellReference[] firstCells = new CellReference[firstCellRefs.Count];
firstCells=firstCellRefs.ToArray();
arrayRecs = new List<ArrayRecord>((ArrayRecord[])arrayRecords.ToArray(typeof(ArrayRecord)));
tableRecs = new List<TableRecord>((TableRecord[])tableRecords.ToArray(typeof(TableRecord)));
_plainRecords = plainRecords;
_sfm = SharedValueManager.Create(sharedFormulaRecs,firstCells, arrayRecs, tableRecs);
_mergedCellsRecords = new MergeCellsRecord[mergeCellRecords.Count];
_mergedCellsRecords = (MergeCellsRecord[])mergeCellRecords.ToArray(typeof(MergeCellsRecord));
}
/**
* is the reference for a whole-column reference,
* such as C:C or D:G ?
*/
public static bool IsWholeColumnReference(CellReference topLeft, CellReference botRight)
{
// These are represented as something like
// C$1:C$65535 or D$1:F$0
// i.e. absolute from 1st row to 0th one
if (topLeft.Row == 0 && topLeft.IsRowAbsolute &&
(botRight.Row == -1 || botRight.Row == 65535) && botRight.IsRowAbsolute)
{
return true;
}
return false;
}
/**
* Creates an area ref from a pair of Cell References.
*/
public AreaReference(CellReference topLeft, CellReference botRight)
{
//_firstCell = topLeft;
//_lastCell = botRight;
//_isSingleCell = false;
bool swapRows = topLeft.Row > botRight.Row;
bool swapCols = topLeft.Col > botRight.Col;
if (swapRows || swapCols)
{
int firstRow;
int lastRow;
int firstColumn;
int lastColumn;
bool firstRowAbs;
bool lastRowAbs;
bool firstColAbs;
bool lastColAbs;
if (swapRows)
{
firstRow = botRight.Row;
firstRowAbs = botRight.IsRowAbsolute;
lastRow = topLeft.Row;
lastRowAbs = topLeft.IsRowAbsolute;
}
else
{
firstRow = topLeft.Row;
firstRowAbs = topLeft.IsRowAbsolute;
lastRow = botRight.Row;
lastRowAbs = botRight.IsRowAbsolute;
}
if (swapCols)
{
firstColumn = botRight.Col;
firstColAbs = botRight.IsColAbsolute;
lastColumn = topLeft.Col;
lastColAbs = topLeft.IsColAbsolute;
}
else
{
firstColumn = topLeft.Col;
firstColAbs = topLeft.IsColAbsolute;
lastColumn = botRight.Col;
lastColAbs = botRight.IsColAbsolute;
}
_firstCell = new CellReference(firstRow, firstColumn, firstRowAbs, firstColAbs);
_lastCell = new CellReference(lastRow, lastColumn, lastRowAbs, lastColAbs);
}
else
{
_firstCell = topLeft;
_lastCell = botRight;
}
_isSingleCell = false;
}
protected String FormatReferenceAsString()
{
CellReference topLeft = new CellReference(FirstRow, FirstColumn, !IsFirstRowRelative, !IsFirstColRelative);
CellReference botRight = new CellReference(LastRow, LastColumn, !IsLastRowRelative, !IsLastColRelative);
if (AreaReference.IsWholeColumnReference(topLeft, botRight))
{
return (new AreaReference(topLeft, botRight)).FormatAsString();
}
return topLeft.FormatAsString() + ":" + botRight.FormatAsString();
}
public String FormatReferenceAsString()
{
// Only make cell references as needed. Memory is an issue
CellReference cr = new CellReference(Row, Column, !IsRowRelative, !IsColRelative);
return cr.FormatAsString();
}
/**
* Resolves a cell or area reference dynamically.
* @param workbookName the name of the workbook Containing the reference. If <code>null</code>
* the current workbook is assumed. Note - to Evaluate formulas which use multiple workbooks,
* a {@link CollaboratingWorkbooksEnvironment} must be set up.
* @param sheetName the name of the sheet Containing the reference. May be <code>null</code>
* (when <c>workbookName</c> is also null) in which case the current workbook and sheet is
* assumed.
* @param refStrPart1 the single cell reference or first part of the area reference. Must not
* be <code>null</code>.
* @param refStrPart2 the second part of the area reference. For single cell references this
* parameter must be <code>null</code>
* @param isA1Style specifies the format for <c>refStrPart1</c> and <c>refStrPart2</c>.
* Pass <c>true</c> for 'A1' style and <c>false</c> for 'R1C1' style.
* TODO - currently POI only supports 'A1' reference style
* @return a {@link RefEval} or {@link AreaEval}
*/
public ValueEval GetDynamicReference(String workbookName, String sheetName, String refStrPart1,
String refStrPart2, bool isA1Style)
{
if (!isA1Style)
{
throw new Exception("R1C1 style not supported yet");
}
SheetRefEvaluator sre = CreateExternSheetRefEvaluator(workbookName, sheetName);
if (sre == null)
{
return ErrorEval.REF_INVALID;
}
// ugly typecast - TODO - make spReadsheet version more easily accessible
SpreadsheetVersion ssVersion = ((IFormulaParsingWorkbook)_workbook).GetSpreadsheetVersion();
NameType part1refType = ClassifyCellReference(refStrPart1, ssVersion);
switch (part1refType)
{
case NameType.BAD_CELL_OR_NAMED_RANGE:
return ErrorEval.REF_INVALID;
case NameType.NAMED_RANGE:
IEvaluationName nm = ((IFormulaParsingWorkbook)_workbook).GetName(refStrPart1, _sheetIndex);
if (!nm.IsRange)
{
throw new Exception("Specified name '" + refStrPart1 + "' is not a range as expected.");
}
return _bookEvaluator.EvaluateNameFormula(nm.NameDefinition, this);
}
if (refStrPart2 == null)
{
// no ':'
switch (part1refType)
{
case NameType.COLUMN:
case NameType.ROW:
return ErrorEval.REF_INVALID;
case NameType.CELL:
CellReference cr = new CellReference(refStrPart1);
return new LazyRefEval(cr.Row, cr.Col, sre);
}
throw new InvalidOperationException("Unexpected reference classification of '" + refStrPart1 + "'.");
}
NameType part2refType = ClassifyCellReference(refStrPart1, ssVersion);
switch (part2refType)
{
case NameType.BAD_CELL_OR_NAMED_RANGE:
return ErrorEval.REF_INVALID;
case NameType.NAMED_RANGE:
throw new Exception("Cannot Evaluate '" + refStrPart1
+ "'. Indirect Evaluation of defined names not supported yet");
}
if (part2refType != part1refType)
{
// LHS and RHS of ':' must be compatible
return ErrorEval.REF_INVALID;
}
int firstRow, firstCol, lastRow, lastCol;
switch (part1refType)
{
case NameType.COLUMN:
firstRow = 0;
lastRow = ssVersion.LastRowIndex;
firstCol = ParseColRef(refStrPart1);
lastCol = ParseColRef(refStrPart2);
break;
case NameType.ROW:
firstCol = 0;
lastCol = ssVersion.LastColumnIndex;
firstRow = ParseRowRef(refStrPart1);
lastRow = ParseRowRef(refStrPart2);
break;
case NameType.CELL:
CellReference cr;
cr = new CellReference(refStrPart1);
firstRow = cr.Row;
firstCol = cr.Col;
cr = new CellReference(refStrPart2);
lastRow = cr.Row;
lastCol = cr.Col;
break;
default:
throw new InvalidOperationException("Unexpected reference classification of '" + refStrPart1 + "'.");
}
return new LazyAreaEval(firstRow, firstCol, lastRow, lastCol, sre);
}
/// <summary>
/// Sets the print area.
/// </summary>
/// <param name="sheetIndex">Zero-based sheet index (0 = First Sheet)</param>
/// <param name="startColumn">Column to begin printarea</param>
/// <param name="endColumn">Column to end the printarea</param>
/// <param name="startRow">Row to begin the printarea</param>
/// <param name="endRow">Row to end the printarea</param>
public void SetPrintArea(int sheetIndex, int startColumn, int endColumn,
int startRow, int endRow)
{
//using absolute references because they don't Get copied and pasted anyway
CellReference cell = new CellReference(startRow, startColumn, true, true);
String reference = cell.FormatAsString();
cell = new CellReference(endRow, endColumn, true, true);
reference = reference + ":" + cell.FormatAsString();
SetPrintArea(sheetIndex, reference);
}
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();
}
public override String ToString()
{
CellReference crA = new CellReference(FirstRow, FirstColumn);
CellReference crB = new CellReference(LastRow, LastColumn);
StringBuilder sb = new StringBuilder();
sb.Append(GetType().Name).Append("[");
sb.Append(_evaluator.SheetName);
sb.Append('!');
sb.Append(crA.FormatAsString());
sb.Append(':');
sb.Append(crB.FormatAsString());
sb.Append("]");
return sb.ToString();
}
public override String ToString()
{
StringBuilder sb = new StringBuilder(64);
CellReference crA = new CellReference(_firstRowIndex, _firstColumnIndex, false, false);
CellReference crB = new CellReference(_lastRowIndex, _lastColumnIndex, false, false);
sb.Append(GetType().Name);
sb.Append(" [").Append(crA.FormatAsString()).Append(':').Append(crB.FormatAsString()).Append("]");
return sb.ToString();
}
public RefPtg(CellReference cr):base(cr)
{
}
/**
* Returns a reference to every cell covered by this area
*/
public CellReference[] GetAllReferencedCells()
{
// Special case for single cell reference
if (_isSingleCell)
{
return new CellReference[] { _firstCell, };
}
// Interpolate between the two
int minRow = Math.Min(_firstCell.Row, _lastCell.Row);
int maxRow = Math.Max(_firstCell.Row, _lastCell.Row);
int minCol = Math.Min(_firstCell.Col, _lastCell.Col);
int maxCol = Math.Max(_firstCell.Col, _lastCell.Col);
String sheetName = _firstCell.SheetName;
ArrayList refs = new ArrayList();
for (int row = minRow; row <= maxRow; row++)
{
for (int col = minCol; col <= maxCol; col++)
{
CellReference ref1 = new CellReference(sheetName, row, col, _firstCell.IsRowAbsolute, _firstCell.IsColAbsolute);
refs.Add(ref1);
}
}
return (CellReference[])refs.ToArray(typeof(CellReference));
}
/// <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 Ref3DPtg(CellReference cr, int externIdx):base(cr)
{
ExternSheetIndex = externIdx;
}
protected Ref2DPtgBase(CellReference cr):base(cr)
{
}
/// <summary>
/// Remove a Array Formula from this sheet. All cells contained in the Array Formula range are removed as well
/// </summary>
/// <param name="cell">any cell within Array Formula range</param>
/// <returns>the <see cref="ICellRange{ICell}"/> of cells affected by this change</returns>
public ICellRange<ICell> RemoveArrayFormula(ICell cell)
{
if (cell.Sheet != this)
{
throw new ArgumentException("Specified cell does not belong to this sheet.");
}
CellValueRecordInterface rec = ((HSSFCell)cell).CellValueRecord;
if (!(rec is FormulaRecordAggregate))
{
String ref1 = new CellReference(cell).FormatAsString();
throw new ArgumentException("Cell " + ref1 + " is not part of an array formula.");
}
FormulaRecordAggregate fra = (FormulaRecordAggregate)rec;
CellRangeAddress range = fra.RemoveArrayFormula(cell.RowIndex, cell.ColumnIndex);
ICellRange<ICell> result = GetCellRange(range);
// clear all cells in the range
foreach (ICell c in result)
{
c.SetCellType(CellType.BLANK);
}
return result;
}