Setting Cell Style or any attribute painfully slow!

[danielgraal]

Hi,

I would like to set all my cells to individual cell styles which I hold in separate object for each cell.

My code looks like this:

Code: Select all

foreach (var row in sheet.Rows)
            {
                foreach (var cell in row.Columns)
                {
                    var sourceCellData = rows[cell.Row - 1].Cells[cell.Column - 1];
                    cell.IgnoreErrorOptions = IgnoreErrorType.NumberAsText;
                    cell.ApplyStyle((Spire.Xls.Core.IStyle)sourceCellData.Style, new CellStyleFlag()
                    {
                        All = true
                    });
                }
            }

But unfortunately any manipulation on the "cell" is painfully slow, it takes a couple of minutes for 30000 rows.
I tried using other methods like SetCellValue which is superb fast but I can't see any method for setting style.

What should I do?

[kylie.tian]

Hi,

Thank you for your inquiry.
I simulated an excel document and did a test with your code, but did not reproduce your problem. To help us have a better investigation, please provide the following information for our investigation. You can attach your document here or send it to us via email ([email protected]). Thank you in advance.
1) Your input and output documents
2) Test environment, such as Win10, 64bit
3) Application type, such as Console App, .NET Framework 4.8
Kindly note the SetCellValue method is used to set the value of the cell, we provide the CellStyle method to set the cell style, please refer to the code below.

Code: Select all

           //Get cell
            CellRange range = sheet.Range["B4"];
            //Get the style of cell
            CellStyle style = range.Style;
            foreach (var row in sheet.Rows)
            {
                foreach (var cell in row.Columns)
                {
                    cell.IgnoreErrorOptions = IgnoreErrorType.NumberAsText;
                    cell.Style = style;
                }
            }

In addition, the forum has 2MB limitation on attachment size. If your documents exceed this limit, please upload your it via Google Drive and share the download link.

Sincerely,
Kylie
E-iceblue support team

[danielgraal]

I've created a console app to reproduce this behavior.

Environment: Windows 10 64bit, .NET 6, Console App and Web App

[kylie.tian]

Hi,

Sorry for the late reply due to weekend.
Since you create a large number of cells, it mush take a long time to loop through each cell and set the style. Our Spire.XLS provides the Worksheet.ApplyStyle(style, applyRowStyle, applyColumnStyle) interface, it can achieve your requirement. Please refer to the code below.

Code: Select all

 
            //Create a cell style
            CellStyle style = book.Styles.Add("newStyle");
            style.Color = Color.LightBlue;
            style.Font.Color = Color.White;
            style.Font.IsBold = true;

            //SetCellValue
            for (int i = 0; i < rowNum; i++)
            {
                for (int j = 0; j < colNum; j++)
                {
                    sheet.SetCellValue(i + 1, j + 1, "Test Data");
                }
            };
            //Apply the style
            sheet.ApplyStyle(style, false, false);

Sincerely,
Kylie
E-iceblue support team

[danielgraal]

Hi,

I don't get your idea.
I don't want to style whole sheet with the same style. Each cell can have an individual style, so applying one to whole sheet is not an option for me.

If SetCellValue is so fast, and so other Set... methods I don't get it why applying style is slow.

[kylie.tian]

Hi,

After further investigation, I found out that the slowness was caused by this line of code(cell.IgnoreErrorOptions = IgnoreErrorType.NumberAsText). I logged this problem to our dev team for investigation, the problem ticket is SPIREXLS-3993. Once there is any update, I will inform you. Apologize for the inconvenience.

Sincerely,
Kylie
E-iceblue support team

[danielgraal]

That's right, we've managed to speed it up by removing this setting, interesting.

Thanks for your time. I'm looking forward for any news about the fix.

[kylie.tian]

Hi,

I will contact you as soon as there is any progress or update on the issue.

Sincerely,
Kylie
E-iceblue support team

[kylie.tian]

Hi,

I got feedback from our dev team about the SpireXLS-3993. If you loop through all cells and set the IgnoreErrorOptions property, a large number of ErrorIndicator objects will be created. This is what causes the program to take a long time. So please change to the code below and verify it (It only took 2.5s on my side). Please feel free to contact us if you have any questions.

Code: Select all

 
            foreach (var columns in sheet.Columns)
            {
                foreach (var cell in columns.Rows)
                {
                    cell.ApplyStyle(style, new CellStyleFlag()
                    {
                        All = true
                    });                   
                }
            }
            sheet.Range[1, 1, rowNum, colNum].IgnoreErrorOptions = IgnoreErrorType.NumberAsText;

Sincerely,
Kylie
E-iceblue support team