Formatting Performance Issue

[Bandonia]

Good Morning,

We have been noticing recently that when we attempt to do any kind of formatting in a newly created Excel worksheet, especially if the worksheet has more than 100,000 rows.

The code snippet below is an example of how we would set the right border on a collection of data... for an excel sheet of about 400,000 rows - this code takes more than 10 minutes to run. Is there some other way we can do this in a more performant way? or is this an issue that would need to be addressed with Spire?

Code: Select all

public void SetRightBorder(int nRow, int nCol, int nHeight, int nWidth)
{
            CellRange objRange = GetRange(nRow, nCol, nHeight, nWidth);
            objRange.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;
}

private CellRange GetRange(int startRow, int startColumn, int rowHeight, int columnWidth)
{
            Worksheet worksheet = _currentWorksheet;
            return worksheet.Range[startRow + 1, startColumn + 1, startRow + rowHeight < startRow + 1 ? startRow + 1 : startRow + rowHeight, startColumn + columnWidth < startColumn + 1 ? startColumn + 1 : startColumn + columnWidth];
}



I am using on a 64-bit machine running Windows 11 Business. The code is in a WinForms application using C#. And my region/language is United States/English (United States).

Hopefully this information helps you in figuring out my issue & thank you for your help!

[Triste.Dai]

Hi,

Thank you for bringing up the issue you encountered while using our XLS product.

I have conducted a simple test by creating a new blank document and applying your provided code, but I was unable to reproduce the problem you described. In order to further investigate and assist you better, could you please provide us with the following information:
1. The test document you used.
2. Complete code used for testing.
3. .NET Framework version.

Once we receive these details, we will analyze them thoroughly and work towards finding a resolution for you. Thank you for your cooperation, and we look forward to assisting you further.

Best regards,
Triste
E-iceblue support team

[Bandonia]

Hello,

I was generating an XL worksheet from data in one of our client databases and so cannot provide that data. We were not reading from an xl document in order to generate this worksheet.

We are currently using the .Net Framework 4.6.2, and I have attached a copy of the class we use to do all of our xl related writing activities that contains the methods sent in the previous post. I cannot send complete code as that logic is tightly integrated with proprietary information.

The performance issue is not evident with minimal rows, as I have run reports with over a thousand rows and the performance difference is insignificant. But with worksheets containing ~400,000 or more rows the performance is terrible. Hopefully it's an issue with how we are applying the formatting I outlined in my previous message but if not... then the utility is almost unusable.

[Triste.Dai]

Hi,

Thank you for reaching out to us with your feedback. In order to assist you better, we kindly request you to provide a detailed description of the specific scenario in which you are using our product.

It would be helpful if you could outline the steps you take when utilizing our XLS product. For example, if you are importing 100,000 rows of data from a database and then adjusting the formatting before saving it. By understanding your workflow, we can attempt to reproduce the issue and investigate further. If we are able to replicate the problem, our development team can work on optimizing the product accordingly.

Therefore, if you have any additional information that can aid us in replicating this issue, please provide us with the details. We value your cooperation in helping us improve our product and ensure a better experience for all our customers.

We look forward to your response.

Best regards,
Triste
E-iceblue support team

[Bandonia]

Good day,
The steps we follow are as outlined below...

We pull 100,000+ rows from our data source, this data is then iterated through and put into an object array.
Then we call the 'AddWorksheet()' method in the sample code (attached previously) to create the worksheet with the array
After which we then perform the necessary formatting/styling on the worksheet

The last 2 steps can be repeated multiple times for the same Excel document, as we often have need for multiple worksheets to display that data.

Once all worksheets have been added (with formatting applied) to the document, we then save the Excel document to the local file system.

Our performance hit happens though when trying to apply the aforementioned styling to the worksheet before saving, using the calls outlined in the document provided.

Thank you.

[Triste.Dai]

Hi,

Thank you for providing us with the detailed operation process as per our request.

We will proceed with simulating and testing the scenario based on the provided instructions. Once we are able to reproduce the issue, we will promptly provide you with feedback on our findings. Subsequently, we will escalate the matter to our development team for further investigation into possible optimizations.

If you have any additional information or questions, please feel free to share them with us. We will do our best to keep you informed of any progress made.

Thank you for your understanding.

Best regards,
Triste
E-iceblue support team

[Triste.Dai]

Hi,

I have conducted testing using an existing document that contains over 110,000 rows, including a significant amount of text and numerous formulas. For the purpose of the test, I specifically focused on processing 100,000 rows. it only took approximately 80 seconds to complete the operation. This time cost is significantly lower than the duration you mentioned.

debug.png (104.14 KiB) Viewed 2815 times

Since I was unable to reproduce the performance problem you encountered, it would be helpful if you could provide us with more specific details about the scenario. Specifically, if you could simulate and provide a single row of data (excluding any sensitive information) by specifying the number of columns and the types of data contained within them, it would greatly assist our investigation. We will then replicate this row and conduct testing to uncover any underlying issues.

Thank you for your understanding and assistance.

Best regards,
Triste
E-iceblue support team

[Bandonia]

Hi,

Unfortunately the data contained in the excel I referenced is all confidential, and I have not received permission to share that. What I can tell you is that the worksheets in question are typically more than 400,000 rows with about 15 columns. The data contained therein are a mixture of strings (typically 5 - 50 characters long), dates, integers and doubles.
I do not think we have any formulae. Is there anything in the code snippet I provided that could be changed to help the performance? 80 seconds for 100,000 is still too slow for us considering the Excel Interop used to typically perform the same task in a fraction of the time.

We have also noted that the "AllocatedRange.AutoFitColumns()" & "AllocatedRange.AutoFitRows()" are extremely slow in our environment. Is there an alternative to their use? Or will we need to individually resize the columns/rows?

[Triste.Dai]

Hi,

Thanks for your feedback.
Based on your description, I simulated a 120000-line Excel document that contains strings, dates, integers, and decimals. Then adjust the line style of the border, which takes about 44 seconds for 120000 rows of data, as shown in the screenshot below. The test document is attached(https://www.e-iceblue.com/downloads/attachment/test34121.zip).

test1.png (75.48 KiB) Viewed 2777 times

For your another issue, AutoFitColumns() and AutoFitRows() are slow. I consulted our developers, and they said that the number of rows and the amount of text within cells will affect the performance of these two methods. If your data length is similar, you can calculate the width yourself and then proceed uniformly.

Code: Select all

// for example
sheet.Columns[0].ColumnWidth = 100;


Best regards,
Triste
E-iceblue support team

[Bandonia]

Good Day Triste,

I would like to send you a console application that I have put together to highlight our issues mentioned above. Unfortunately, the email to the support email address was rejected because of the attached zip file.

Is there a way to get this zip file to you without attaching it here?

Thank you!

[Triste.Dai]

Hi,

Thanks for your effort.
You can upload your project to Google Drive or OneDrive, then share the download link with us via email ([email protected]). We really appreciate your kind behavior and assistance.

Bet regards,
Triste
E-iceblue support team

[Bandonia]

Good Day,

Were you able to access the file I provided? I sent a share notification and an email to support right afterwards. I have not heard anything since I sent the email and so wanted to know.

Thank you!

[Triste.Dai]

Hi,

Thanks for your feedback. We apologize for any inconvenience caused and appreciate your effort in sending us the application via email.

However, after checking our mailbox including junk/spam folder, we did not receive your email. Could you please resend it to us again? We would be happy to investigate the issue further and provide a solution as soon as possible.

Thank you for your patience and understanding.

Best regards,
Triste
E-iceblue support team

[Bandonia]

Good Morning!

I have just now resent the email with the link to the shared item on Google Drive to both the support email address and to your personal address in case, for some reason, there is a specific reason they are not getting through. Please let me know if you should receive this email as it is important, we get a resolution to the performance issues.

Thank you!

[Triste.Dai]

Hi,

Thank you for sharing your project with us via Google Drive. We appreciate your cooperation in providing the necessary information to investigate the performance issue with our XLS product.

However, we encountered a slight hurdle while accessing the shared link you provided. It seems that access and download permissions require approval. I have already submitted a request using my personal Gmail account. Kindly ask you to approve the request when you see it, so that we can proceed with our analysis.

Thank you for your assistance.

Best regards,
Triste
E-iceblue support team