Slow creation of Aspose.Excel with a lot of different styles

Aspose.Cells Product Family
1

Hi Laurence !

We are testing ASPOSE to generate our XL files, but we are confronted with the following problem:
We have a lot of cells ( like 1 or 2 millions of cells ) and each cell has its own style. The generation is very slow and the time of generation is not linear ( if 1 million = 1 hour, 2 millions = 3 hours or more ).

May I have some advice ?

Many thanks.Smile

2

Do you use the following method to set style?

cells[0,0].Style.Font.Name = "Tahoma";

cells[0,1].Style.Font.Name = "Tahoma";


.......


In such method, each cell will create a new Style object. That wil consume huge amount of memory. And MS Excel doesn't support different styles more than 512. So Aspose.Excel has to merge these style objects.

So please create style object first and assign it to all cells with same style.

Style style1 = excel.Styles[excel.Styles.Add()];
style1.Font.Name = "Tahoma";

cells[0, 0].Style = style1;
cells[0, 1].Style = style1;

........


3

Hi Laurence,

Thanks for your quick reply …
We are already using a link of styles with the cells.

I think I have found why the generation is so long: we are filling the cells by columns then by lines … I made a test and it seems to be the problem.

Do you know something that can help simply or do we have to reform completely our code ?

Many thanks …

4

If you have a large amount of data, please fill them by rows first then by columns. Cells object internally contains all Cell object in a collection, order by row first and column second.


If you fill by rows first then by columns, the new added cell is appended at the last of the collection. If you fill by columns first then by rows, previous cells may have to be reordered and pushed down. Then it will slow the program.

For example, if you have 6 cells in a worksheet: A1, A2, A3, B1, B2, B3.

Aspose.Excel will collection them as A1, B1, A2, B2, A3, B3.

5

Hi Laurence,

I have understood the internal treatment.
Do you know if it is simple to develop a version of Aspose which can work vertically ( or which can work vertically / horizontally in a transparent way ).

Thanks, Yannick.

6

No. It’s a huge job to let it do as your wish. Please use the workaround to insert by rows first then by columns.