Fastest way to apply a style to a bunch of cells

Aspose.Cells Product Family
1

Hello Aspose support. I have some code that is populating a large excel workbook. It is running extremely slow. Every time I break into the code it's setting the style on cells, so that is definitely the bottleneck.

My code is doing a bunch of stuff in a loop and for each row it's getting a range of cells on that row and setting a specific style. I'm wondering what the fastest way to do this is?

Currently the code is looping through all the cells in the range, getting each cell with worksheet.Cells[Row, Column], and then setting the style property on each cell. In this version of the code, the bottleneck is actually looking up the cell in the loop (e.g. each time I break it's on the Cells[] call, not on the application of the style property).

I thought maybe it would be faster to do this with ranges so instead of looping through individual cells I tried worksheet.Cells.CreateRange() and passed in the range of cells. I then set the style on the range. This seemed significantly slower than my first approach. Strangely, the slowness was on the application of the style, not on the CreateRange() call. I didn't try the applystyle method but I assume it will be as slow.

Can you recommend a way to speed up this extremely slow operation? Currently we are using the first approach I describe, and just the call to Cells[] in the loop is very slow (obviously for a large number of cells). Thank you.

2

Hi,

Thanks for sharing some details.

We appreciate if you could create a simple project to show the issue, we will check it soon.

BTW, there are some optimization techniques if you could have a look at it to follow:

Applying Styles on Cells
Format Cells using GetStyle and SetStyle Methods

Thank you.

3

Hi. Thanks for your responses. None of your responses address the issue. They are all about reusing the style object. We are reusing the same style object, I am trying to set one style and I'm pretty sure my code is not creating multiple styles behind the scenes. Additionally, my first message specifically said that the bottleneck was getting the cell, not setting the style. Additionally, none of your responses said anything about setting the style on a range of cells at once, which was my question.

I will try to write up a code snippet to clarify, but I think what I am asking is relatively straightforward. Thank you.

4

Sorry for the confusion.

In your first post,

Every time I break into the code it's setting the style on cells, so that is definitely the bottleneck.

So we concentrate on style setting.

How many rows and columns did you set in your program? And how did you loop them? The way of looping does affect the performance. So your sample code will greatly help us to understand your scenario.

And since you want to apply a same style setting to a large range of cells, have you ever tried to apply the style on a row or column? That will be much faster.

5

I apologize – my message was definitely not clear. The style is not the same for an entire row or column, it’s for a set of cells within each row. So setting it on a row or column is not an option. I will get you the code snippet soon. Thanks again for your help.

6

Hi. This is a code snippet of what our current code is doing (I don't have internet access from my machine so I can't easily write and send actual working code). The numbers I'm picking below are sort of arbitrary -- it's just demonstrating that there's a lot of data. Also, in my example below, I am setting the style to be the same in the same columns on each row but the columns I set are actually varying row by row, I just used the example below for simplicity.

Worksheet worksheet = someworksheet;

Style style = somestylethatwechooseaheadoftime;

for(int row = 2; row <= 50000; row++)

{

for(int column = 5; column <= 50; column++)

{

Cell cell = worksheet.Cells[row, column];

cell.Style = style;
}
}

In the above code snippet, the actual getting of the style is not taking a long time. what's taking a long time is the retrieving of each cell. Every time I hit break in the code, it is on the .Cells[] call.

I was wondering if there was some way to set the style of the range of cells faster? I changed the code to instead of looping through the columns, to do something like:

Range range = worksheet.Cells.CreateRange(row, 5, row, 50);

range.style = style;

In this case, whenever I broke into the code, it was on the line that applied the style. This wasn't faster (it possibly was much slower, I had to kill it before it finished). I didn't try the ApplyStyle method instead of just .style, though.

Do you have any ideas as to how I can apply the style for a range of cells (specifically a block of cells in a specific row, assuming I am processing many rows), more quickly? Thanks again for your help.

7

Since you want to apply style settings to a lot of cells, there is not a simple approach to make it faster. I think you should change your way of setting styles.

Following are my suggestions:

1. Setting the style before populating any data.

2. Since you will set a same formatting to more than 50000 rows, I think you'd better to use ApplyStyle to set a whole column first. Then you can set the other formattings in this column. The number of cells with different formattting should be much smaller, so the performance will be greatly improved.

3. Setting style to a range is similiar to your code, so the performance won't be improved.