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)); }