Hello,
In our application we use a custom calculation engine to retrieve and distribute values for custom formulas. Since it’s expensive to get values from our datasource we use a cache so that subsequent calls workbook.Calculate can perform fast. The challenge is that part of our processing may cause rows or columns to be inserted or deleted, so tying our cached value back to a cell address is difficult. I had previously logged a request to store some identifier information in a cell (e.g. a tag property) Enhancements for tracking cell information - #5 by weissa. Currently, we create a named range for each cell that we distribute a value to. Then when we perform a recalc we can use the “name” of that cell as a lookup key into our cache. This works very well for use, except that workbook.Worksheets.Names.Remove(names) scales poorly at large quantities.
Using the test case below and cycling the rows variable from between 1000, 2000 & 4000 will show these results:
Removed 250000 named ranges in 35 seconds ------- 1x
Removed 500000 named ranges in 138 seconds ------ 2x size / 4x timing
Removed 1000000 named ranges in 679 seconds ---- 4x size / 20x timing
Can anything be done to optimize the removal of a list of named ranges?
or
Can we find a better way of uniquely identifying a cell which may move? (e.g. a tag or metadata bucket to place an identifier in)
Thank You,
-Andy
[Test]
public void foo30()
{
var workbook = new Workbook();
var sheet = workbook.Worksheets["Sheet1"];
var rows = 4000;
var cols = 250;
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
for (var r = 0; r < rows; r++)
{
for (var c = 0; c < cols; c++)
{
var cell = sheet.Cells[r, c];
sheet.Cells.CreateRange(cell.Name).Name = "RWNR_" + Guid.NewGuid().ToString().Replace("-", "");
}
}
stopwatch.Stop();
Console.Out.WriteLine($"Created {rows * cols} named ranges in {stopwatch.Elapsed.TotalSeconds} seconds");
stopwatch.Reset();
stopwatch.Start();
var names = workbook.Worksheets.Names.Select(n => n.Text).ToArray();
workbook.Worksheets.Names.Remove(names);
stopwatch.Stop();
Console.Out.WriteLine($"Removed {rows * cols} named ranges in {stopwatch.Elapsed.TotalSeconds} seconds");
}