Formatting Performance and styles

Aspose.Cells Product Family
1

Hello,

We are trying to generate large excel files and it appears that we are spending the majority of our time in file generation on formatting. In my code I have a calls to InsertColumn and InsertRows which inserts about 500 rows and 30 columns. When I ran the code through a profiler it appears the majority of the time is spent copying the foreground color and doing other formatting tasks such as setting the borders. What is the recommended way for handling the formatting and is there a function that would allow us to insert a row without copying over any of the formatting?

Also, I tried creating a named style in excel and then searching for that style by name in the styles collection of the worksheet. But when I try to set the Style attribute of a cell to the retrieved style it doesn’t appear that any of the settings are working.

Finally, the files that are getting generated seem to be quite large (sometimes over 5MB). Are there any guidelines that we should follow to reduce file size?

Thanks,
Eric

2

Hi Eric,

How do you call InsertRows and InsertColumns method? Please call them as few as possible. I think you can just call them once in your program.

I can add a new Insert method without copying formattings but I think you can try my suggestion first.

For named style issue, could you please post a simple sample to show your problem? And which version of Aspose.Cells are you using?

The file size depends on data size so I don't have clues to reduce it now. You can zip and post your output file here and I will check if there are some workarounds.

3

We are calling the InsertRows function once to generate all of the rows (about 500) and then InsertColumn once for each column (the number of columns varies and needs to be set programatically). This is then done for multiple worksheets. Is there any better way for us to implement this?

Some sample code is proivded below for the styles issue:

Style style = null;
Style pidStyle = null;
for (int i = 0; i < wrkbk.Styles.Count; i++)
{
if (wrkbk.StylesIdea [I].Name.Equals(“PreExistDataCell”))
{
style = wrkbk.StylesIdea [I];
}
}


for (int i = 0; i < _data.Length; i++)
if (!_protected && !_allowValueEdit)
{
retVal.Cells[ColumnGroup.Worksheet.Workbook.DataTableStartRow + START_DATA_ROW_OFFSET + i, columnIndex].Style = style;
}
}

When we call this code the output doesn’t seem to set the foreground color equal to the one in the defined style.

Thanks for your prompt reply and help!

Thanks,
Eric

4

1. Please call InsertColumn method first then call InsertRows method. After that, please populate data into your worksheets.

2. How do you et foreground color for this defined style?

5

I am back working on this issue again. Our code now does call insert columns first and then insert rows. I can’t seem to be able to get away from some type of cost related to styles when inserting rows regardless of what I try to do. If I set the foreground color to “No Color” then it does improve performance but we we are still spending close to 50% of our time in the Insert function now in the Style.IsEqual method (based on a profiler). Are there any other ways to be able to improve the performance? We would prefer to be able to have a foreground color for the cells that we are copying (it is the color as that of the Normal style). Any pointers would be greatly appretiated.

Thanks,
Eric

6

Hi Eric,

Thanks for considering Aspose.

Well, Laurence is sick, hopefully he will be back tomorrow.

Regards

Amjad Sahi

Aspose Nanjing Team

7

Is it possible to post a sample project to show your problem? Then I can check how to optimize it.

Maybe a new method to push down rows but don't copying style for the first row may help in your case.