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