Skip to content

Performance for Getting/Setting styling for individual cells should be improved #2084

@OssianEPPlus

Description

@OssianEPPlus

Consider the following test:

        [TestMethod]
        public void cellStyles()
		{
			using(var package = OpenPackage("s912_cellStyles.xlsx", true))
			{
                var sw = new Stopwatch();
                sw.Start();

                var ws = package.Workbook.Worksheets.Add("ws");

                var range = ws.Cells["C1:FC1000"];

                var nbCols = range.Columns;
                var nbLines = range.Rows;

                ws.Cells["C3"].Style.Fill.SetBackground(Color.Red);
                ws.Cells["D4"].Style.Fill.SetBackground(Color.Blue);
                ws.Cells["E5"].Style.Fill.SetBackground(Color.Yellow);
                ws.Cells["F6"].Style.Fill.SetBackground(Color.Green);

                ////If this is uncommented performance is exponentionally faster as loop skips checking for previous cells
                //range.Style.Locked = true;
                //range.Style.Locked = false;

                for (int i = range.Start.Column; i <= nbLines; i++)
                {
                    for (int j = range.Start.Column; j <= nbCols; j++)
                    {
                        if (ws.Cells[i,j].Style.Fill.BackgroundColor.Rgb == null)
						{
                            ws.Cells[i, j].Style.Fill.SetBackground(Color.Purple);
						}
                    }
                }

				sw.Stop();
                var seconds = sw.Elapsed.TotalSeconds;

                Assert.IsTrue(seconds < 2.5);

                SaveAndCleanup(package);
            }
		}

Despite it attempting to set cell styles individually and directly. We still spend 50% of CPU time in the ColumnIndex file ColumnIndex.GetPrevRow(int row)
This as ExcelStyles.SetStyleCells on line 561 executes if (ws._values.PrevCell(ref r, ref c)) As a way to find the styling of the closest ExcelColumn.

It seems strange why we even need to even check previous cells/columns when the user input is to put a styling directly onto a cell.
Finding the given column range could be more efficent. It should likely be improved and also centralized as we perform similar checks in Worksheet.GetColumn(). Potentially using ColumnLookup or as part of a larger refactor to improve handling of ExcelColumns that span several columns.

This is the same underlying issue as #2063

Metadata

Metadata

Assignees

No one assigned

    Labels

    enhancementNew feature or request

    Type

    No type

    Projects

    Status

    PR Backlog

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions