Apply style to Range

Aspose.Cells Product Family
1

I use following code multiple times to apply a style to a range.
The first time the method is called, it takes less than 0,1 sec. The 20th time it is called, it takes more than 2 seconds... nrOfRows is 10000
Any ideas on how to improve the performance?

private void setColumnRangeLocked(Worksheet ws, bool date, byte col, int startRow, int nrOfRows)
{
Range range = ws.Cells.CreateRange(startRow, col, nrOfRows, 1);
Style style;
if(date)
{
style = excel.Styles[STYLE_LOCKED_DATE];
}
else
{
style = excel.Styles[STYLE_LOCKED_NO_DATE];
}
range.Style = style;
}

The styles are similar to this one and initiliazed before processing:

Style style = excel.Styles[excel.Styles.Add()];
style.Name = STYLE_LOCKED_DATE;
style.IsLocked = true;
style.Custom = AppDatePattern;

2

When you use Range.Style to set styles to this range, blank cells are created. Aspose.Excel cells are internally arranged first sorted by row, second by column. When a large amount number of cells are created, it takes many times to insert cells within the list.

You can try to change your code to:

private void setColumnRangeLocked(Worksheet ws, bool date, byte col)
{
Column col = ws.Cells.Columns[col];

Style style;
if(date)
{
style = excel.Styles[STYLE_LOCKED_DATE];
}
else
{
style = excel.Styles[STYLE_LOCKED_NO_DATE];
}
col.Style = style;
}

3

Problem is that I don't want to apply the style to the whole column but just a part of it. I could first apply the style to the whole column and then reapply the other styles to the other cells in the column, but that would be difficult to keep track of. Moreover I would have to use a range again for reapplying the styles...
If I understand right, the speed change is due to the fact that the 'cell list' is becoming bigger and bigger?

Kind regards,

TLB

4

Yes. The speed change is caused cell list growing bigger and you insert cells in the mid of the list. It's order by row first and column second. So please try to apply the style in the following way:

private void setColumnRangeLocked(Worksheet ws, bool[] dates, byte[] cols, int startRow, int nrOfRows)
{
Style style;

for(int i = startRow, i < startRow + nrOfRows; i ++)

{

for(int j = 0; j < cols.Length; j ++)

{

Cell cell = ws.Cells[i, cols[j]];

if(dates[j])
{
style = excel.Styles[STYLE_LOCKED_DATE];
}
else
{
style = excel.Styles[STYLE_LOCKED_NO_DATE];
}
cell.Style = style;

}
}

5

I implemented the style setting according to your suggestion and it works great.
Pitty the range.Style doesn't work as fast. It is more logical to apply the style to the range when creating it.

Thanx

6

Finally went to the hassle of applying the 'major' style to the entire column and then applying the other styles to the individual cells. Had to do some programming to remember which cell needed which style, but it is way faster now.

Conclusion: try not to apply a style to a large amount of cells, but apply it to the column itself Smile [:)].