Exemple #1
0
 internal void RecalcCellReferences(SheetChange sheetChange)
 {
     foreach (var w in this.Workbook.Worksheets)
     {
         w.RecalcCellReferences(sheetChange);
     }
 }
        public void RecalcCellReferences(SheetChange sheetChange)
        {
            foreach (var i in this.Cells())
            {
                var c = i.Value;
                if (c.Formula != null)
                {
                    c.Formula.Text      = ExcelFormula.TranslateForSheetChange(c.Formula.Text, sheetChange, _wsheet.Name);
                    c.Formula.R1        = ExcelRange.TranslateForSheetChange(c.Formula.R1, sheetChange, _wsheet.Name);
                    c.Formula.R2        = ExcelRange.TranslateForSheetChange(c.Formula.R2, sheetChange, _wsheet.Name);
                    c.Formula.Reference = ExcelRange.TranslateForSheetChange(c.Formula.Reference, sheetChange, _wsheet.Name);
                }
            }

            // Adjust conditional formatting
            List <ConditionalFormatting> cfToRemoveList = new List <ConditionalFormatting>();

            foreach (var cf in this.GetElements <ConditionalFormatting>())
            {
                bool removeCf          = false;
                List <StringValue> lst = new List <StringValue>();
                foreach (var sqrefItem in cf.SequenceOfReferences.Items)
                {
                    string newRef = ExcelRange.TranslateForSheetChange(sqrefItem.Value, sheetChange, _wsheet.Name);
                    if (!newRef.StartsWith("#")) // no error
                    {
                        lst.Add(new StringValue(newRef));
                    }
                    else
                    {
                        cfToRemoveList.Add(cf);
                        removeCf = true;
                        break;
                    }
                }
                if (removeCf)
                {
                    break;
                }
                cf.SequenceOfReferences = new ListValue <StringValue>(lst);
                foreach (var f in cf.Descendants <Formula>())
                {
                    f.Text = ExcelFormula.TranslateForSheetChange(f.Text, sheetChange, _wsheet.Name);
                }
            }
            foreach (ConditionalFormatting cf in cfToRemoveList)
            {
                this.RemoveElement(cf);
            }
        }
Exemple #3
0
        /// <summary>
        /// Translate a fornula due to a sheet change, e.g. insertion of rows.
        /// </summary>
        /// <param name="formula">Formula, e.g. SUM(A1,Sheet2!B2)</param>
        /// <param name="sheetChange">Details of change</param>
        /// <param name="currentSheetName">The sheet where the range is, to determine if this range is affected. If sheetChange.SheetName is null and currentSheetName is null, translation is always applied.</param>
        /// <param name="currentSheetName">The sheet where the range is, to determine if this range is affected. If sheetChange.SheetName is null and currentSheetName is null, translation is always applied.</param>
        /// <returns></returns>
        public static string TranslateForSheetChange(string formula, SheetChange sheetChange, string currentSheetName)
        {
            if (formula == null)
            {
                return(null);
            }

            ParseTree     tree    = ExcelFormula.Parse(formula);
            StringBuilder rebuilt = new StringBuilder();

            if (tree.Errors.Count > 0)
            {
                throw new ArgumentException("Error in parsing formula");
            }
            BuildTranslated(rebuilt, tree,
                            n => TranslateRangeParseNodeForSheetChange(n, sheetChange, currentSheetName));
            return(rebuilt.ToString());
        }
        public void PushRows(uint rowStart, int qty)
        {
            if (qty < 1)
            {
                throw new ArgumentException("Quantity cannot be less than 0");
            }
            if (qty == 0)
            {
                return;
            }

            _sheetCache.InsertOrDeleteRows(rowStart, qty, false);

            SheetChange sheetChange = new SheetChange()
            {
                SheetName = this.Name, RowStart = rowStart, RowDelta = qty
            };

            this.Document.RecalcCellReferences(sheetChange);
        }
        public void InsertColumns(uint colStart, int qty)
        {
            if (qty < 1)
            {
                throw new ArgumentException("Quantity cannot be less than 0");
            }
            if (qty == 0)
            {
                return;
            }

            _sheetCache.InsertOrDeleteColumns(colStart, qty, true);

            SheetChange sheetChange = new SheetChange()
            {
                SheetName = this.Name, ColumnStart = colStart, ColumnDelta = qty
            };

            this.Document.RecalcCellReferences(sheetChange);
        }
        /// <summary>
        /// Translate a range due to a sheet change, e.g. insertion of rows.
        /// </summary>
        /// <param name="range">Range, e.g. A1, Sheet1!A1</param>
        /// <param name="sheetChange">Details of change</param>
        /// <param name="currentSheetName">The sheet where the range is, to determine if this range is affected. If sheetChange.SheetName is null and currentSheetName is null, translation is always applied.</param>
        /// <returns></returns>
        public static string TranslateForSheetChange(string range, SheetChange sheetChange, string currentSheetName)
        {
            if (range == null)
            {
                return(null);
            }

            RangeComponents er = ExcelRange.Parse(range);

            if ((er.SheetName == "" && currentSheetName == sheetChange.SheetName) ||
                er.SheetName == sheetChange.SheetName)
            {
                return(TranslateInternal(er, sheetChange.RowStart, sheetChange.ColumnStart,
                                         sheetChange.RowDelta, sheetChange.ColumnDelta,
                                         false     // Don't allow absolute refs i.e. $ to affect translate
                                         ));
            }
            else
            {
                return(range);
            }
        }
Exemple #7
0
        private static string TranslateRangeParseNodeForSheetChange(ParseNode rangeNode, SheetChange sheetChange, string currentSheetName)
        {
            string range = "";

            foreach (ParseNode sub in rangeNode.Nodes)
            {
                range += sub.Token.Text;
            }
            return(ExcelRange.TranslateForSheetChange(range, sheetChange, currentSheetName));
        }
 internal void RecalcCellReferences(SheetChange sheetChange)
 {
     _sheetCache.RecalcCellReferences(sheetChange);
 }