EPPlus
EPPlus copied to clipboard
Copying cells with conditional formatting is really slow
I have spreadsheet that has over 300 conditional formattings for row. When i insert new row, conditional formattings do not come along - so i have to do it manually (cell by cell). Why not use range copy? It still has some bugs even after release 5.8.3. Excel says that spreadsheet is invalid and gives me some sort of restored version.
So I did a workaround.
Random bencmark: I had to insert 16 new rows.
Copying cell by cell took ~80sec. Not acceptable.
The workaround. After inserting rows i do this:
foreach (var f in ws.ConditionalFormatting)
{
var addresses = f.Address.Address.Split(",")
.Select(x =>
{
var range = x.Split(':');
var rangeStart = range[0];
var rangeEnd = range[0];
if (range.Length > 1)
{
rangeEnd = range[1];
}
var rangeEndRow = int.Parse(Regex.Match(rangeEnd, @"^([A-Z]*)(?<row>\d*)$").Groups["row"].Value);
if (rangeEndRow > lastRow)
{
return x;
}
rangeEnd = Regex.Replace(rangeEnd, @"^(?<col>[A-Z]*)\d*$", (x) => $"{x.Groups["col"].Value}{lastRow}");
return $"{rangeStart}:{rangeEnd}";
})
.ToArray();
f.Address = new ExcelAddress(string.Join(",", addresses));
}
And I get result within ~1.5sec
Probably i'm missing some cases, because im not too familiar with this library yet - but it works in my case (and really well). Baybe this sort of solution could be part of inserting new rows?
Anyway... appreciate your work! Keep it going!
Do you think that you can attach a reproducable sample, so we can fix this bug?
Closed due to inactivity/lack of testability.