We have some problems with setting the cell formatting on a rather large Excel workbook with multiple worksheets. We are creating a workbook to preview and export data from a model, and the model has quite a lot of data.
What we'd like to do, is to apply some formatting on some of the cells, according to the cell type. For instance, we would like to right align all numeric columns, and show some cells with bold font. Without formatting, the workbook is created in less than a second. When formatting is applied, it takes around 4 minutes, so the performance takes a pretty dramatic hit.
We can't set the formatting for rows or columns, since there may be several small data areas on the same worksheet, and their data may differ. So we are pretty much stuck with individual cells here. Cell regions could be a solution, but we could not find any way to set a style for a region of cells, that are not merged.
When setting the cell format, we first tried to apply the format for each cell using cell.CellFormat property directly. Then we tried to create a common style for each type of formatting (five separate styles were needed in this test case), and apply the existing style by calling cell.CellFormat.SetFormatting(). However, this did not speed the process up at all.
We call workbook.SuspendCalculations() at the start of the worksheet creation.
Could you provide any ideas about how to speed up the format setting?
Thanks in advance,-Antti
Thank you for contacting Infragistics!
Where are you setting the format?Are you doing so before the export?Or during an export event?Do you have a sample you can attach?
Thanks for the quick reply. The issue seems to occur when we fill the worksheet, so the export functionality is not the problem here.
We've managed to narrow the problem down: the slowing occurs when we add both formatting and comments on the same worksheet. This can be demonstrated with a test application that can be found from this link: 1drv.ms/.../s!ApfGmLEyjZvRpRa0zOzMK97cHsqc
The application creates a workbook that has 5 worksheets. Each worksheet has 300 rows and 50 columns. There are two checkboxes, and they can be used to reproduce the problem: one for adding comments on every second worksheet, and another for adding formatting on each worksheet.
When the application is run, it can be observed that filling the first, visible worksheet is always a bit slower, as one would expect since the screen has to be updated. When only one of the checkboxes is checked, the filling process for other worksheets is very fast. However, if both of the checkboxes are checked, it affects the performance for the worksheets that have comments added.
For us, this is not an issue anymore since the comments are not absolutely necessary, and we can leave them out for now. However, we thought you might be interested to see and reproduce this issue.
Thank you for the update and the sample. We will use the sample to look further into this matter.