Bulk cell formatting not performant/faulty implementation or missing documentation?

Aspose.Cells Product Family
1

Hi


The following issues occur.

1) For a certain functionality it’s necessary to change the default cell formatting for a complete column (all rows).

Until now I didn’t find any sollution present in the documentation which would allow me to multiple select multiple cells and edit their settings in one stroke.

My first idea was to merge the cells and then edit that merged cell’s properties and do an unmerge. This doesn’t work out. The cells get merged allright, but the documentation states that I need to select the first cell in the index (upper left?) and change the properties of that one to select the merged cell. Editing the value for the cell isn’t a problem, but other to that nothing works. Can’t change formatting or style so that it would reflect on the complete merged cell.

For example : editing borders will only change the border for the selected “index” cell, chich you use to select the merged cell, and not the complete merged cell.

So unmerging will just reflect changes in the first (index) cell and leave the others as there were before the merge.

If you do this manualy in excel, merge/change style/unmerge … it works just perfect!


2) My second try-out was to iterate manualy over all the rows… Don’t do that. HUGE performance issue. I do find it strange that the Range-object doesnt allow the cells to inherit any of the style/changes. No API implemetation for this kind of functionality.
Allthough you can manually select multiple cells (“range”) in excel and edit their properties in one go. Shouldn’t be that hard do implement I think. Although I’m no Excel expert like you guys :smiley:

kind regards
Frederick
FinArch.com

2

Dear Frederick,

Which version are you using?

To set style for a row/column, you can use Row.Style and Column.Style property. To set style for a range, you can use Range.Style.

Style styleObject = excel.Styles[excel.Styles.Add()];
//Set style here
…

Row row = sheet.Rows[0];
row.Style = styleObject;

Column column = sheet.Columns[0];
column.Style = styleObject;

Range range = cells.CreateRange(…);
range.Style = styleObject;

3


My mistake. I didn’t read the manual properly. [8-)]

I’m gonna try the suggested usage of range right away.


Thanks
Frederick

4

Hi




I have to say that I'm dissapointed about the performance of the functinality where you create a range and set a style on it.

Before using the API like yu suggested I just iterated over de rows to set the style for a certain column which did take a lifetime if you do that for all 65k+ rows.

And it's not different at all with the usage of the Range.[:^)] Its not only not faster, but way too much slower.

And this is a huge performance issue. Our dual processor servers goes full throttle (100%) and all other processing for the webserver (IIS) almost comes to a halt.

My conclusion is to kick out the requested functionality as long as the performance issue stays. A shame realy. Like I stated earlier, when you perform the same thing manualy in Excel, being selecting multiple cells and editing their format by right clicking and changing properties, then it does take no time at all. It's like an instant cast.


5

Iterating all cells is very very slow indeed. But you don’t need to do that.



Could you please post your code here? I will check how to optimize it. And please post your desired file with format setting. I will provide sample code to set those range formattings.