public void UpdateSheetName(int sheetIndex, string name)
{
IFormulaRenderingWorkbook frwb = (IFormulaRenderingWorkbook) new XSSFFormulaRenderingWorkbook(this._fpwb, sheetIndex, name);
for (int nameIndex = 0; nameIndex < this._wb.NumberOfNames; ++nameIndex)
{
IName nameAt = this._wb.GetNameAt(nameIndex);
if (nameAt.SheetIndex == -1 || nameAt.SheetIndex == sheetIndex)
{
this.UpdateName(nameAt, frwb);
}
}
foreach (ISheet sheet in this._wb)
{
foreach (IRow row in sheet)
{
foreach (ICell cell in row)
{
if (cell.CellType == CellType.FORMULA)
{
this.UpdateFormula((XSSFCell)cell, frwb);
}
}
}
}
}
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.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();
}
private void UpdateFormula(XSSFCell cell, IFormulaRenderingWorkbook frwb)
{
CT_CellFormula f = cell.GetCTCell().f;
if (f == null)
{
return;
}
string formula = f.Value;
if (formula == null || formula.Length <= 0)
{
return;
}
int sheetIndex = this._wb.GetSheetIndex(cell.Sheet);
Ptg[] ptgs = FormulaParser.Parse(formula, (IFormulaParsingWorkbook)this._fpwb, FormulaType.CELL, sheetIndex);
string formulaString = FormulaRenderer.ToFormulaString(frwb, ptgs);
if (formula.Equals(formulaString))
{
return;
}
f.Value = formulaString;
}
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());
}
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();
}
/**
* Parse formula in the named range and re-assemble it back using the specified FormulaRenderingWorkbook.
*
* @param name the name to update
* @param frwb the formula rendering workbbok that returns new sheet name
*/
private void UpdateName(IName name, IFormulaRenderingWorkbook frwb)
{
String formula = name.RefersToFormula;
if (formula != null)
{
int sheetIndex = name.SheetIndex;
Ptg[] ptgs = FormulaParser.Parse(formula, _fpwb, FormulaType.NamedRange, sheetIndex);
String updatedFormula = FormulaRenderer.ToFormulaString(frwb, ptgs);
if (!formula.Equals(updatedFormula))
{
name.RefersToFormula = (updatedFormula);
}
}
}
internal static DVConstraint CreateDVConstraint(DVRecord dvRecord, IFormulaRenderingWorkbook book)
{
switch (dvRecord.DataType)
{
case ValidationType.ANY:
return(new DVConstraint(ValidationType.ANY, dvRecord.ConditionOperator, null, null, double.NaN, double.NaN, null));
case ValidationType.INTEGER:
case ValidationType.DECIMAL:
case ValidationType.DATE:
case ValidationType.TIME:
case ValidationType.TEXT_LENGTH:
FormulaValuePair pair1 = toFormulaString(dvRecord.Formula1, book);
FormulaValuePair pair2 = toFormulaString(dvRecord.Formula2, book);
return(new DVConstraint(dvRecord.DataType, dvRecord.ConditionOperator, pair1.formula(),
pair2.formula(), pair1.Value, pair2.Value, null));
case ValidationType.LIST:
if (dvRecord.ListExplicitFormula)
{
String values = toFormulaString(dvRecord.Formula1, book).AsString();
if (values.StartsWith("\""))
{
values = values.Substring(1);
}
if (values.EndsWith("\""))
{
values = values.Substring(0, values.Length - 1);
}
String[] explicitListValues = values.Split("\0".ToCharArray());
return(CreateExplicitListConstraint(explicitListValues));
}
else
{
String listFormula = toFormulaString(dvRecord.Formula1, book).AsString();
return(CreateFormulaListConstraint(listFormula));
}
case ValidationType.FORMULA:
return(CreateCustomFormulaConstraint(toFormulaString(dvRecord.Formula1, book).AsString()));
default:
throw new InvalidOperationException(string.Format("validationType={0}", dvRecord.DataType));
}
}
private static FormulaValuePair toFormulaString(Ptg[] ptgs, IFormulaRenderingWorkbook book)
{
FormulaValuePair pair = new FormulaValuePair();
if (ptgs != null && ptgs.Length > 0)
{
String aString = FormulaRenderer.ToFormulaString(book, ptgs);
if (ptgs.Length == 1 && ptgs[0].GetType() == typeof(NumberPtg))
{
pair._value = aString;
}
else
{
pair._formula = aString;
}
}
return(pair);
}
private void UpdateName(IName name, IFormulaRenderingWorkbook frwb)
{
string refersToFormula = name.RefersToFormula;
if (refersToFormula == null)
{
return;
}
int sheetIndex = name.SheetIndex;
Ptg[] ptgs = FormulaParser.Parse(refersToFormula, (IFormulaParsingWorkbook)this._fpwb, FormulaType.NAMEDRANGE, sheetIndex);
string formulaString = FormulaRenderer.ToFormulaString(frwb, ptgs);
if (refersToFormula.Equals(formulaString))
{
return;
}
name.RefersToFormula = formulaString;
}
/**
* Parse cell formula and re-assemble it back using the specified FormulaRenderingWorkbook.
*
* @param cell the cell to update
* @param frwb the formula rendering workbbok that returns new sheet name
*/
private void UpdateFormula(XSSFCell cell, IFormulaRenderingWorkbook frwb)
{
CT_CellFormula f = cell.GetCTCell().f;
if (f != null)
{
String formula = f.Value;
if (formula != null && formula.Length > 0)
{
int sheetIndex = _wb.GetSheetIndex(cell.Sheet);
Ptg[] ptgs = FormulaParser.Parse(formula, _fpwb, FormulaType.Cell, sheetIndex);
String updatedFormula = FormulaRenderer.ToFormulaString(frwb, ptgs);
if (!formula.Equals(updatedFormula))
{
f.Value = (updatedFormula);
}
}
}
}
/**
* 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(IFormulaRenderingWorkbook 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(IFormulaRenderingWorkbook book)
{
// -1 to convert definedNameIndex from 1-based to zero-based
return(book.ResolveNameXText(this));
}
/**
* @return text representation of this cell reference that can be used in text
* formulas. The sheet name will Get properly delimited if required.
*/
public String ToFormulaString(IFormulaRenderingWorkbook book)
{
return(ExternSheetNameResolver.PrependSheetName(book, field_1_index_extern_sheet, FormatReferenceAsString()));
}
public String ToFormulaString(IFormulaRenderingWorkbook book)
{
return(ExternSheetNameResolver.PrependSheetName(book, field_1_index_extern_sheet,
HSSFErrorConstants.GetText(HSSFErrorConstants.ERROR_REF)));
}
public String ToFormulaString(IFormulaRenderingWorkbook book)
{
return book.GetNameText(this);
}
/**
* Parse formula in the named range and re-assemble it back using the specified FormulaRenderingWorkbook.
*
* @param name the name to update
* @param frwb the formula rendering workbbok that returns new sheet name
*/
private void UpdateName(IName name, IFormulaRenderingWorkbook frwb)
{
String formula = name.RefersToFormula;
if (formula != null)
{
int sheetIndex = name.SheetIndex;
Ptg[] ptgs = FormulaParser.Parse(formula, _fpwb, FormulaType.NAMEDRANGE, sheetIndex);
String updatedFormula = FormulaRenderer.ToFormulaString(frwb, ptgs);
if (!formula.Equals(updatedFormula)) name.RefersToFormula = (updatedFormula);
}
}
/**
* 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(IFormulaRenderingWorkbook 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(IFormulaRenderingWorkbook book)
{
return(ExternSheetNameResolver.PrependSheetName(book, field_1_index_extern_sheet,
FormulaError.REF.String));
}
/**
* @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(IFormulaRenderingWorkbook book)
{
return ExternSheetNameResolver.PrependSheetName(book, field_1_index_extern_sheet, FormatReferenceAsString());
}
public String ToFormulaString(IFormulaRenderingWorkbook book)
{
return(book.GetNameText(this));
}
internal static DVConstraint CreateDVConstraint(DVRecord dvRecord, IFormulaRenderingWorkbook book)
{
switch (dvRecord.DataType)
{
case ValidationType.ANY:
return new DVConstraint(ValidationType.ANY, dvRecord.ConditionOperator, null, null, double.NaN, double.NaN, null);
case ValidationType.INTEGER:
case ValidationType.DECIMAL:
case ValidationType.DATE:
case ValidationType.TIME:
case ValidationType.TEXT_LENGTH:
FormulaValuePair pair1 = toFormulaString(dvRecord.Formula1, book);
FormulaValuePair pair2 = toFormulaString(dvRecord.Formula2, book);
return new DVConstraint(dvRecord.DataType, dvRecord.ConditionOperator, pair1.formula(),
pair2.formula(), pair1.Value, pair2.Value, null);
case ValidationType.LIST:
if (dvRecord.ListExplicitFormula)
{
String values = toFormulaString(dvRecord.Formula1, book).AsString();
if (values.StartsWith("\""))
{
values = values.Substring(1);
}
if (values.EndsWith("\""))
{
values = values.Substring(0, values.Length - 1);
}
String[] explicitListValues = values.Split("\0".ToCharArray());
return CreateExplicitListConstraint(explicitListValues);
}
else
{
String listFormula = toFormulaString(dvRecord.Formula1, book).AsString();
return CreateFormulaListConstraint(listFormula);
}
case ValidationType.FORMULA:
return CreateCustomFormulaConstraint(toFormulaString(dvRecord.Formula1, book).AsString());
default:
throw new InvalidOperationException(string.Format("validationType={0}", dvRecord.DataType));
}
}
public String ToFormulaString(IFormulaRenderingWorkbook book)
{
// -1 to convert definedNameIndex from 1-based to zero-based
return book.ResolveNameXText(this);
}
public String ToFormulaString(IFormulaRenderingWorkbook book)
{
return ExternSheetNameResolver.PrependSheetName(book, field_1_index_extern_sheet,
HSSFErrorConstants.GetText(HSSFErrorConstants.ERROR_REF));
}
private static FormulaValuePair toFormulaString(Ptg[] ptgs, IFormulaRenderingWorkbook book)
{
FormulaValuePair pair = new FormulaValuePair();
if (ptgs != null && ptgs.Length > 0)
{
String aString = FormulaRenderer.ToFormulaString(book, ptgs);
if (ptgs.Length == 1 && ptgs[0].GetType() == typeof(NumberPtg))
{
pair._value = aString;
}
else
{
pair._formula = aString;
}
}
return pair;
}
/**
* Parse cell formula and re-assemble it back using the specified FormulaRenderingWorkbook.
*
* @param cell the cell to update
* @param frwb the formula rendering workbbok that returns new sheet name
*/
private void UpdateFormula(XSSFCell cell, IFormulaRenderingWorkbook frwb)
{
CT_CellFormula f = cell.GetCTCell().f;
if (f != null)
{
String formula = f.Value;
if (formula != null && formula.Length > 0)
{
int sheetIndex = _wb.GetSheetIndex(cell.Sheet);
Ptg[] ptgs = FormulaParser.Parse(formula, _fpwb, FormulaType.CELL, sheetIndex);
String updatedFormula = FormulaRenderer.ToFormulaString(frwb, ptgs);
if (!formula.Equals(updatedFormula)) f.Value = (updatedFormula);
}
}
}
