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