public static String PrependSheetName(FormulaRenderingWorkbook 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(FormulaRenderingWorkbook 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();
}
/**
* @return text representation of this area reference that can be used in text
* formulas. The sheet name will get properly delimited if required.
*/
public String ToFormulaString(FormulaRenderingWorkbook book)
{
return ExternSheetNameResolver.PrependSheetName(book, field_1_index_extern_sheet, FormatReferenceAsString());
}
public String ToFormulaString(FormulaRenderingWorkbook book)
{
return ExternSheetNameResolver.PrependSheetName(book, field_1_index_extern_sheet,
HSSFErrorConstants.GetText(HSSFErrorConstants.ERROR_REF));
}
public String ToFormulaString(FormulaRenderingWorkbook book)
{
return book.GetNameText(this);
}
/**
* Static method To convert an array of {@link Ptg}s in RPN order
* To a human readable string format in infix mode.
* @param book used for defined names and 3D references
* @param ptgs must not be <c>null</c>
* @return a human readable String
*/
public static String ToFormulaString(FormulaRenderingWorkbook book, Ptg[] ptgs)
{
if (ptgs == null || ptgs.Length == 0)
{
throw new ArgumentException("ptgs must not be null");
}
Stack stack = new Stack();
for (int i = 0; i < ptgs.Length; i++)
{
Ptg ptg = ptgs[i];
// TODO - what about MemNoMemPtg?
if (ptg is MemAreaPtg || ptg is MemFuncPtg || ptg is MemErrPtg)
{
// marks the start of a list of area expressions which will be naturally combined
// by their trailing operators (e.g. UnionPtg)
// TODO - Put comment and throw exception in ToFormulaString() of these classes
continue;
}
if (ptg is ParenthesisPtg)
{
String contents = (String)stack.Pop();
stack.Push("(" + contents + ")");
continue;
}
if (ptg is AttrPtg)
{
AttrPtg attrPtg = ((AttrPtg)ptg);
if (attrPtg.IsOptimizedIf || attrPtg.IsOptimizedChoose || attrPtg.IsSkip)
{
continue;
}
if (attrPtg.IsSpace)
{
// POI currently doesn't render spaces in formulas
continue;
// but if it ever did, care must be taken:
// tAttrSpace comes *before* the operand it applies To, which may be consistent
// with how the formula text appears but is against the RPN ordering assumed here
}
if (attrPtg.IsSemiVolatile)
{
// similar To tAttrSpace - RPN is violated
continue;
}
if (attrPtg.IsSum)
{
String[] operands = GetOperands(stack, attrPtg.NumberOfOperands);
stack.Push(attrPtg.ToFormulaString(operands));
continue;
}
throw new Exception("Unexpected tAttr: " + attrPtg.ToString());
}
if (ptg is WorkbookDependentFormula)
{
WorkbookDependentFormula optg = (WorkbookDependentFormula)ptg;
stack.Push(optg.ToFormulaString(book));
continue;
}
if (!(ptg is OperationPtg))
{
stack.Push(ptg.ToFormulaString());
continue;
}
OperationPtg o = (OperationPtg)ptg;
String[] operands1 = GetOperands(stack, o.NumberOfOperands);
stack.Push(o.ToFormulaString(operands1));
}
if (stack.Count == 0)
{
// inspection of the code above reveals that every stack.pop() is followed by a
// stack.push(). So this is either an internal error or impossible.
throw new InvalidOperationException("Stack underflow");
}
String result = (String)stack.Pop();
if (stack.Count != 0)
{
// Might be caused by some Tokens like AttrPtg and Mem*Ptg, which really shouldn't
// Put anything on the stack
throw new InvalidOperationException("too much stuff left on the stack");
}
return(result);
}
/**
* Static method To convert an array of {@link Ptg}s in RPN order
* To a human readable string format in infix mode.
* @param book used for defined names and 3D references
* @param ptgs must not be <c>null</c>
* @return a human readable String
*/
public static String ToFormulaString(FormulaRenderingWorkbook book, Ptg[] ptgs)
{
if (ptgs == null || ptgs.Length == 0)
{
throw new ArgumentException("ptgs must not be null");
}
Stack stack = new Stack();
for (int i = 0; i < ptgs.Length; i++)
{
Ptg ptg = ptgs[i];
// TODO - what about MemNoMemPtg?
if (ptg is MemAreaPtg || ptg is MemFuncPtg || ptg is MemErrPtg)
{
// marks the start of a list of area expressions which will be naturally combined
// by their trailing operators (e.g. UnionPtg)
// TODO - Put comment and throw exception in ToFormulaString() of these classes
continue;
}
if (ptg is ParenthesisPtg)
{
String contents = (String)stack.Pop();
stack.Push("(" + contents + ")");
continue;
}
if (ptg is AttrPtg)
{
AttrPtg attrPtg = ((AttrPtg)ptg);
if (attrPtg.IsOptimizedIf || attrPtg.IsOptimizedChoose || attrPtg.IsSkip)
{
continue;
}
if (attrPtg.IsSpace)
{
// POI currently doesn't render spaces in formulas
continue;
// but if it ever did, care must be taken:
// tAttrSpace comes *before* the operand it applies To, which may be consistent
// with how the formula text appears but is against the RPN ordering assumed here
}
if (attrPtg.IsSemiVolatile)
{
// similar To tAttrSpace - RPN is violated
continue;
}
if (attrPtg.IsSum)
{
String[] operands = GetOperands(stack, attrPtg.NumberOfOperands);
stack.Push(attrPtg.ToFormulaString(operands));
continue;
}
throw new Exception("Unexpected tAttr: " + attrPtg.ToString());
}
if (ptg is WorkbookDependentFormula)
{
WorkbookDependentFormula optg = (WorkbookDependentFormula)ptg;
stack.Push(optg.ToFormulaString(book));
continue;
}
if (!(ptg is OperationPtg))
{
stack.Push(ptg.ToFormulaString());
continue;
}
OperationPtg o = (OperationPtg)ptg;
String[] operands1 = GetOperands(stack, o.NumberOfOperands);
stack.Push(o.ToFormulaString(operands1));
}
if (stack.Count == 0)
{
// inspection of the code above reveals that every stack.pop() is followed by a
// stack.push(). So this is either an internal error or impossible.
throw new InvalidOperationException("Stack underflow");
}
String result = (String)stack.Pop();
if (stack.Count != 0)
{
// Might be caused by some Tokens like AttrPtg and Mem*Ptg, which really shouldn't
// Put anything on the stack
throw new InvalidOperationException("too much stuff left on the stack");
}
return result;
}
public String ToFormulaString(FormulaRenderingWorkbook book)
{
return(ExternSheetNameResolver.PrependSheetName(book, field_1_index_extern_sheet,
HSSFErrorConstants.GetText(HSSFErrorConstants.ERROR_REF)));
}
public String ToFormulaString(FormulaRenderingWorkbook book)
{
// -1 to convert definedNameIndex from 1-based to zero-based
return(book.ResolveNameXText(this));
}
/**
* @return text representation of this area reference that can be used in text
* formulas. The sheet name will get properly delimited if required.
*/
public String ToFormulaString(FormulaRenderingWorkbook book)
{
return(ExternSheetNameResolver.PrependSheetName(book, field_1_index_extern_sheet, FormatReferenceAsString()));
}
public String ToFormulaString(FormulaRenderingWorkbook book)
{
// -1 to convert definedNameIndex from 1-based to zero-based
return book.ResolveNameXText(this);
}
public String ToFormulaString(FormulaRenderingWorkbook book)
{
return(book.GetNameText(this));
}
