private static void ConfirmCellRange(string text, int numberOfPrefixLetters, bool expected)
{
String prefix = text.Substring(0, numberOfPrefixLetters);
String suffix = text.Substring(numberOfPrefixLetters);
Assert.AreEqual(expected, SheetNameFormatter.CellReferenceIsWithinRange(prefix, suffix));
}
/**
* @return the text format of this range using specified sheet name.
*/
public String FormatAsString(String sheetName, bool useAbsoluteAddress)
{
StringBuilder sb = new StringBuilder();
if (sheetName != null)
{
sb.Append(SheetNameFormatter.Format(sheetName));
sb.Append("!");
}
CellReference cellRefFrom = new CellReference(FirstRow, FirstColumn,
useAbsoluteAddress, useAbsoluteAddress);
CellReference cellRefTo = new CellReference(LastRow, LastColumn,
useAbsoluteAddress, useAbsoluteAddress);
sb.Append(cellRefFrom.FormatAsString());
//for a single-cell reference return A1 instead of A1:A1
//for full-column ranges or full-row ranges return A:A instead of A,
//and 1:1 instead of 1
if (!cellRefFrom.Equals(cellRefTo) ||
IsFullColumnRange || IsFullRowRange)
{
sb.Append(':');
sb.Append(cellRefTo.FormatAsString());
}
return(sb.ToString());
}
public static String PrependSheetName(IFormulaRenderingWorkbook book, int field_1_index_extern_sheet, String cellRefText)
{
ExternalSheet externalSheet = book.GetExternalSheet(field_1_index_extern_sheet);
StringBuilder sb;
if (externalSheet != null)
{
String wbName = externalSheet.GetWorkbookName();
String sheetName = externalSheet.GetSheetName();
sb = new StringBuilder(wbName.Length + sheetName.Length + cellRefText.Length + 4);
SheetNameFormatter.AppendFormat(sb, wbName, sheetName);
}
else
{
String sheetName = book.GetSheetNameByExternSheet(field_1_index_extern_sheet);
sb = new StringBuilder(sheetName.Length + cellRefText.Length + 4);
if (sheetName.Length < 1)
{
// What excel does if sheet has been deleted
sb.Append("#REF"); // note - '!' added just once below
}
else
{
SheetNameFormatter.AppendFormat(sb, sheetName);
}
}
sb.Append('!');
sb.Append(cellRefText);
return(sb.ToString());
}
public static String PrependSheetName(IFormulaRenderingWorkbook book, int field_1_index_extern_sheet, String cellRefText)
{
ExternalSheet externalSheet = book.GetExternalSheet(field_1_index_extern_sheet);
StringBuilder sb;
if (externalSheet != null)
{
String wbName = externalSheet.WorkbookName;
String sheetName = externalSheet.SheetName;
if (wbName != null)
{
sb = new StringBuilder(wbName.Length + sheetName.Length + cellRefText.Length + 4);
SheetNameFormatter.AppendFormat(sb, wbName, sheetName);
}
else
{
sb = new StringBuilder(sheetName.Length + cellRefText.Length + 4);
SheetNameFormatter.AppendFormat(sb, sheetName);
}
if (externalSheet is ExternalSheetRange)
{
ExternalSheetRange r = (ExternalSheetRange)externalSheet;
if (!r.FirstSheetName.Equals(r.LastSheetName))
{
sb.Append(':');
SheetNameFormatter.AppendFormat(sb, r.LastSheetName);
}
}
}
else
{
String firstSheetName = book.GetSheetFirstNameByExternSheet(field_1_index_extern_sheet);
String lastSheetName = book.GetSheetLastNameByExternSheet(field_1_index_extern_sheet);
sb = new StringBuilder(firstSheetName.Length + cellRefText.Length + 4);
if (firstSheetName.Length < 1)
{
// What excel does if sheet has been deleted
sb.Append("#REF"); // note - '!' Added just once below
}
else
{
SheetNameFormatter.AppendFormat(sb, firstSheetName);
if (!firstSheetName.Equals(lastSheetName))
{
sb.Append(':');
sb.Append(lastSheetName);
}
}
}
sb.Append('!');
sb.Append(cellRefText);
return(sb.ToString());
}
/**
* Example return values:
* <table border="0" cellpAdding="1" cellspacing="0" summary="Example return values">
* <tr><th align='left'>Result</th><th align='left'>Comment</th></tr>
* <tr><td>A1</td><td>Cell reference without sheet</td></tr>
* <tr><td>Sheet1!A1</td><td>Standard sheet name</td></tr>
* <tr><td>'O''Brien''s Sales'!A1'</td><td>Sheet name with special characters</td></tr>
* </table>
* @return the text representation of this cell reference as it would appear in a formula.
*/
public String FormatAsString()
{
StringBuilder sb = new StringBuilder(32);
if (_sheetName != null)
{
SheetNameFormatter.AppendFormat(sb, _sheetName);
sb.Append(SHEET_NAME_DELIMITER);
}
AppendCellReference(sb);
return(sb.ToString());
}
public void SetPrintArea(int sheetIndex, string reference)
{
XSSFName xssfName = this.GetBuiltInName(XSSFName.BUILTIN_PRINT_AREA, sheetIndex) ?? this.CreateBuiltInName(XSSFName.BUILTIN_PRINT_AREA, sheetIndex);
string[] strArray = XSSFWorkbook.COMMA_PATTERN.Split(reference);
StringBuilder out1 = new StringBuilder(32);
for (int index = 0; index < strArray.Length; ++index)
{
if (index > 0)
{
out1.Append(",");
}
SheetNameFormatter.AppendFormat(out1, this.GetSheetName(sheetIndex));
out1.Append("!");
out1.Append(strArray[index]);
}
xssfName.RefersToFormula = out1.ToString();
}
public String ResolveNameXText(int refIndex, int definedNameIndex, InternalWorkbook workbook)
{
int extBookIndex = _externSheetRecord.GetExtbookIndexFromRefIndex(refIndex);
int firstTabIndex = _externSheetRecord.GetFirstSheetIndexFromRefIndex(refIndex);
if (firstTabIndex == -1)
{
// The referenced sheet could not be found
throw new RuntimeException("Referenced sheet could not be found");
}
// Does it exist via the external book block?
ExternalBookBlock externalBook = _externalBookBlocks[extBookIndex];
if (externalBook._externalNameRecords.Length > definedNameIndex)
{
return(_externalBookBlocks[extBookIndex].GetNameText(definedNameIndex));
}
else if (firstTabIndex == -2)
{
// Workbook scoped name, not actually external after all
NameRecord nr = GetNameRecord(definedNameIndex);
int sheetNumber = nr.SheetNumber;
StringBuilder text = new StringBuilder();
if (sheetNumber > 0)
{
String sheetName = workbook.GetSheetName(sheetNumber - 1);
SheetNameFormatter.AppendFormat(text, sheetName);
text.Append("!");
}
text.Append(nr.NameText);
return(text.ToString());
}
else
{
throw new IndexOutOfRangeException(
"Ext Book Index relative but beyond the supported length, was " +
extBookIndex + " but maximum is " + _externalBookBlocks.Length
);
}
}
public override String ToFormulaString()
{
StringBuilder sb = new StringBuilder();
if (externalWorkbookNumber >= 0)
{
sb.Append('[');
sb.Append(externalWorkbookNumber);
sb.Append(']');
}
SheetNameFormatter.AppendFormat(sb, firstSheetName);
if (lastSheetName != null)
{
sb.Append(':');
SheetNameFormatter.AppendFormat(sb, lastSheetName);
}
sb.Append('!');
sb.Append(FormatReferenceAsString());
return(sb.ToString());
}
public override String ToString()
{
StringBuilder sb = new StringBuilder();
sb.Append(GetType().Name);
sb.Append(" [");
if (externalWorkbookNumber >= 0)
{
sb.Append(" [");
sb.Append("workbook=").Append(ExternalWorkbookNumber);
sb.Append("] ");
}
if (sheetName != null)
{
SheetNameFormatter.AppendFormat(sb, sheetName);
}
sb.Append(" ! ");
sb.Append(FormulaError.REF.String);
sb.Append("]");
return(sb.ToString());
}
public override string ToString()
{
StringBuilder sb = new StringBuilder();
sb.Append(GetType().Name);
sb.Append(" [");
if (externalWorkbookNumber >= 0)
{
sb.Append(" [");
sb.Append("workbook=").Append(ExternalWorkbookNumber);
sb.Append("] ");
}
if (sheetName != null)
{
SheetNameFormatter.AppendFormat(sb, sheetName);
}
sb.Append(" ! ");
sb.Append(ErrorConstants.GetText(ErrorConstants.ERROR_REF));
sb.Append("]");
return(sb.ToString());
}
private static string GetReferenceBuiltInRecord(string sheetName, int startC, int endC, int startR, int endR)
{
CellReference cellReference1 = new CellReference(sheetName, 0, startC, true, true);
CellReference cellReference2 = new CellReference(sheetName, 0, endC, true, true);
string str1 = SheetNameFormatter.Format(sheetName);
string str2;
if (startC == -1 && endC == -1)
{
str2 = "";
}
else
{
str2 = str1 + "!$" + cellReference1.CellRefParts[2] + ":$" + cellReference2.CellRefParts[2];
}
CellReference cellReference3 = new CellReference(sheetName, startR, 0, true, true);
CellReference cellReference4 = new CellReference(sheetName, endR, 0, true, true);
string str3 = "";
if (startR == -1 && endR == -1)
{
str3 = "";
}
else if (!cellReference3.CellRefParts[1].Equals("0") && !cellReference4.CellRefParts[1].Equals("0"))
{
str3 = str1 + "!$" + cellReference3.CellRefParts[1] + ":$" + cellReference4.CellRefParts[1];
}
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append(str2);
if (stringBuilder.Length > 0 && str3.Length > 0)
{
stringBuilder.Append(',');
}
stringBuilder.Append(str3);
return(stringBuilder.ToString());
}
public override String ToFormulaString()
{
StringBuilder sb = new StringBuilder();
bool needsExclamation = false;
if (externalWorkbookNumber >= 0)
{
sb.Append('[');
sb.Append(externalWorkbookNumber);
sb.Append(']');
needsExclamation = true;
}
if (sheetName != null)
{
SheetNameFormatter.AppendFormat(sb, sheetName);
needsExclamation = true;
}
if (needsExclamation)
{
sb.Append('!');
}
sb.Append(nameName);
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 && args[2] != MissingArgEval.instance)
{
refType = (int)NumericFunction.SingleOperandEvaluate(args[2], srcRowIndex, srcColumnIndex);
}
else
{
refType = REF_ABSOLUTE; // this is also the default if parameter is not given
}
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());
}
}
private static void ConfirmCellNameMatch(string rawSheetName, bool expected)
{
Assert.AreEqual(expected, SheetNameFormatter.NameLooksLikePlainCellReference(rawSheetName));
}
private static void ConfirmFormat(string rawSheetName, String expectedSheetNameEncoding)
{
Assert.AreEqual(expectedSheetNameEncoding, SheetNameFormatter.Format(rawSheetName));
}
