This project has moved and is read-only. For the latest updates, please go here.
1
Vote

ConditionalFormatting causing HIGH overhead

description

I have a process that builds a rather large spreadsheet. About 30,000 rows, roughly 300 columns. (don't knock me, I'm the developer, I tried to convince the user it was ludicrous, but what do I know).

This spreadsheet takes about 45 minutes to generate. That's acceptable given the size.

Recently, my user asked me to put conditional formatting in on a number of columns. My formula is below. When I did this, the run time went from 45 minutes, to 36 hours (yes, day and 1/2). I commented out the 4 lines below, and it's back to 45 minutes. I would have expected a little overhead, maybe 5 to 10 minutes. But an increase of roughly 3600% seems ridiculous for this.

IExcelConditionalFormattingExpression _cond = worksheet.ConditionalFormatting.AddExpression(new ExcelAddress(row, col, row, col));
_cond.Style.NumberFormat.Format = "#,##0.###";
_cond.Formula = ExcelMisc.GetColumnName(col) + row + " - TRUNC(" + ExcelMisc.GetColumnName(col) + row + ") <> 0";
worksheet.Cells[row, col].Style.Numberformat.Format = "#,##0";

comments