/// <summary> /// Recalculate this <paramref name="range"/> with the specified <paramref name="options"/>. /// </summary> /// <param name="range">The range to be calculated.</param> /// <param name="options">Settings for this calculation.</param> /// <param name="setResultStyle">Indicates whether or not to set the cell's style based on the calculation result.</param> public static void Calculate(this ExcelRangeBase range, ExcelCalculationOption options, bool setResultStyle = false) { Init(range.myWorkbook); var parser = range.myWorkbook.FormulaParser; parser.InitNewCalc(); if (range.IsName) { range = AddressUtility.GetFormulaAsCellRange(range.Worksheet.Workbook, range.Worksheet, range.Address); } var dc = DependencyChainFactory.Create(range, options); CalcChain(range.myWorkbook, parser, dc, setResultStyle); }
/// <summary> /// Update the records in <see cref="ExcelPivotCacheRecords"/> and any referencing <see cref="ExcelPivotTable"/>s. /// </summary> /// <param name="resourceManager">The <see cref="ResourceManager"/> to retrieve translations from (optional).</param> public void UpdateData(ResourceManager resourceManager = null) { var sourceRange = this.GetSourceRangeAddress(); // If the source range is an Excel pivot table or named range, resolve the address. if (sourceRange.IsName) { sourceRange = AddressUtility.GetFormulaAsCellRange(this.Workbook, sourceRange.Worksheet, sourceRange.Address); } // Update all cacheField names assuming the shape of the pivot cache definition source range remains unchanged. for (int col = sourceRange.Start.Column; col < sourceRange.Columns + sourceRange.Start.Column; col++) { int fieldIndex = col - sourceRange.Start.Column; this.CacheFields[fieldIndex].Name = sourceRange.Worksheet.Cells[sourceRange.Start.Row, col].Value.ToString(); } // Update all cache record values. var worksheet = sourceRange.Worksheet; var range = new ExcelRange(worksheet, worksheet.Cells[sourceRange.Start.Row + 1, sourceRange.Start.Column, sourceRange.End.Row, sourceRange.End.Column]); this.CacheRecords.UpdateRecords(range); this.StringResources.LoadResourceManager(resourceManager); // Refresh pivot tables. foreach (var pivotTable in this.GetRelatedPivotTables()) { pivotTable.RefreshFromCache(this.StringResources); } // Remove the 'u' xml attribute from each cache item to prevent corrupting the workbook, since Excel automatically adds them. foreach (var cacheField in this.CacheFields) { if (cacheField.HasSharedItems) { cacheField.RemoveXmlUAttribute(); } } }
/// <summary> /// Attempts to parse the <see cref="NameFormula"/> as an address, evaluating reference functions and /// nested named ranges as necessary. /// </summary> /// <returns>The formula as an <see cref="ExcelRangeBase"/> if it is an address, null otherwise.</returns> public ExcelRangeBase GetFormulaAsCellRange() { return(AddressUtility.GetFormulaAsCellRange(this.Workbook, this.LocalSheet, this.NameFormula)); }
/// <summary> /// Update the records in <see cref="ExcelPivotCacheRecords"/> and any referencing <see cref="ExcelPivotTable"/>s. /// </summary> /// <param name="resourceManager">The <see cref="ResourceManager"/> to retrieve translations from (optional).</param> public void UpdateData(ResourceManager resourceManager = null) { this.Workbook.FormulaParser.Logger?.LogFunction(nameof(this.UpdateData)); var sourceRange = this.GetSourceRangeAddress(); // If the source range is an Excel pivot table or named range, resolve the address. if (sourceRange.IsName) { sourceRange = AddressUtility.GetFormulaAsCellRange(this.Workbook, sourceRange.Worksheet, sourceRange.Address); } // Update all cacheField names assuming the shape of the pivot cache definition source range remains unchanged. for (int col = sourceRange.Start.Column; col < sourceRange.Columns + sourceRange.Start.Column; col++) { int fieldIndex = col - sourceRange.Start.Column; this.CacheFields[fieldIndex].Name = sourceRange.Worksheet.Cells[sourceRange.Start.Row, col].Value.ToString(); } // Get all of the related slicer selected values before refreshing. var relatedSlicerCaches = this.GetRelatedSlicerCaches(); var selectedSlicerValues = this.GetSelectedSlicerCacheItems(relatedSlicerCaches); // Update all cache record values. var worksheet = sourceRange.Worksheet; var range = new ExcelRange(worksheet, worksheet.Cells[sourceRange.Start.Row + 1, sourceRange.Start.Column, sourceRange.End.Row, sourceRange.End.Column]); // Clear out all of the shared items in order to update them from the source data. foreach (var cacheField in this.CacheFields) { if (cacheField.HasSharedItems) { cacheField.SharedItems.Clear(); cacheField.ClearedSharedItems = true; } } this.CacheRecords.UpdateRecords(range, this.Workbook.FormulaParser.Logger); this.UpdateCacheFieldSharedItemMetadata(); this.StringResources.LoadResourceManager(resourceManager); this.UpdateCacheFieldFieldGroups(); this.RefreshRelatedSlicerCaches(relatedSlicerCaches, selectedSlicerValues); var relatedPivotTables = this.GetRelatedPivotTables(); // Refresh pivot tables. foreach (var pivotTable in relatedPivotTables) { pivotTable.RefreshFromCache(this.StringResources, relatedSlicerCaches); } // Apply the slicer sort and hide settings after pivot tables have been updated. relatedSlicerCaches.ForEach(s => s.ApplySettings(this)); // Remove the 'u' xml attribute from each cache item to prevent corrupting the workbook, since Excel automatically adds them. foreach (var cacheField in this.CacheFields) { if (cacheField.FieldGroup != null || cacheField.HasSharedItems) { cacheField.RemoveXmlUAttribute(); } } }