Spire.XLS is a professional Excel API that enables developers to create, manage, manipulate, convert and print Excel worksheets. Get free and professional technical support for Spire.XLS for .NET, Java, Android, C++, Python.

Wed Jan 19, 2022 7:01 pm

Good Afternoon,
I am still evaluating the Spire.XLS library as a potential replacement for our use of the Excel Interop. Thus far it has been going well but there are 4 extra sheets being generated in the document. I am still using the free version thru Nuget and so I do expect to see the 'Evaluation Warning' sheet & i would assume it would no longer be generated if a license was purchased. But i want to know if the other empty tabs will be removed should licensing be purchased? Or if these extra tabs will need to be removed manually regardless of licensing?

Thank you!

Bandonia
 
Posts: 34
Joined: Tue Nov 16, 2021 9:40 pm

Thu Jan 20, 2022 3:25 am

Hello,

Thank you for your inquiry.
Kindly note that our Spire.XLS will create three worksheets by default when creating a workbook, but if you use the commercial version it will create an additional "Evaluation Warning" worksheet. From your description, I guess you are using the commercial version of Spire.XLS. Please confirm whether you installed the free version through Nuget (the latest version is Free Spire.XLS for .NET(dll only) Version:11.8.6) or the commercial version (the latest version is Spire.XLS Pack(Hotfix) Version:11.12.2). In addition, our product supports specifying the number of the worksheets to be created. The sample code is as follows.
Code: Select all
Workbook workbook = new Workbook();
//Create a worksheet.
workbook.CreateEmptySheets(1);
workbook.SaveToFile("output1.xlsx", ExcelVersion.Version2010);

Sincerely,
Annika
E-iceblue support team
User avatar

Annika.Zhou
 
Posts: 1643
Joined: Wed Apr 07, 2021 2:50 am

Fri Jan 21, 2022 11:21 pm

Thank you Annika for your quick response!
In light of your answer, we will just have to remove those extra tabs manually.

I know this is an unrelated issue but I am attempting to set the number format on a Worksheet, and so far it has been successful. Then today I have attempted to do so on a worksheet I am generating with about 600k rows (each with about 6 columns) using the following line of code
Code: Select all
_currentWorksheet.AllocatedRange.NumberFormat = "#,##0";


... and after 30 minutes it still hasn't moved past that line. Is there a way to achieve a similar result in a more performant way?

Bandonia
 
Posts: 34
Joined: Tue Nov 16, 2021 9:40 pm

Mon Jan 24, 2022 5:56 am

Hello,

Thanks for your feedback.
I simulated an Excel file and did an initial test, and found that running the program took much less time than you mentioned. In fact, the time consuming to run the program is related to the content structure of the file, etc. Please provide the following information for our further investigation. Thanks in advance.
1) Your input Excel file (you could attach it here or send it to us via email (support@e-iceblue.com)).
2) Your test environment, such as OS info (E.g. Windows 7, 64-bit) and region setting (E.g. China, Chinese).
3) Your application type, such as Console app (. Net Framework 4.5).

Sincerely,
Annika
E-iceblue support team
User avatar

Annika.Zhou
 
Posts: 1643
Joined: Wed Apr 07, 2021 2:50 am

Mon Jan 24, 2022 3:57 pm

Good Morning Annika,

Thank you for your reply!
With regards to your requests below...
1) I am unfortunately working with client data and therefore I cannot provide a copy of the worksheet we are generating. But what I can tell you is that there are as many rows as I outlined previously but whereas I thought there were only 5 or 6 columns, there is in fact 20 columns. The first 4 of which contain text, while the remaining columns all contain numbers.
2) The OS is Windows 10 Pro, 64-bit and the region setting is Canada.
3) The application I am attempting to use Spire.XLS in is a C# WinForms application in .Net Framework 4.8

I create the object array in memory and populate it, and then I use the InsertArray() to populate the new worksheet. It takes a little time (~3 minutes) but does get past this logic (that I've used elsewhere). It is at this point that I do the following...

Code: Select all
            _currentWorksheet.AllocatedRange.ConvertToNumber();
            _currentWorksheet.AllocatedRange.IgnoreErrorOptions = IgnoreErrorType.NumberAsText;
            _currentWorksheet.AllocatedRange.NumberFormat = Format;


I have tried to skip this code but I then find we have a performance issue when I try to set the font size & name on the worksheet...

Code: Select all
            _currentWorksheet.AllocatedRange.Style.Font.Size = nFontSize;
            _currentWorksheet.AllocatedRange.Style.Font.FontName = fontName;


This process also creates other worksheets for the workbook before this sheet is attempted.

I have created the same document using the Excel interop in under 3 minutes...why is the performance so poor using this library?
The memory usage when using Excel interop never goes above 5GB but when using Spire.XLS I have seen it reach over 40GB with my computer at 99% memory usage.
Do I need to dispose of anything?

Are there best practices when creating worksheets?

Bandonia
 
Posts: 34
Joined: Tue Nov 16, 2021 9:40 pm

Tue Jan 25, 2022 7:41 am

Hello,

Thanks for your sharing.
According to your description, I did an initial test in the same environment with the same settings as you, and reproduced the issue you mentioned. I have logged the issue into our bug tracking system with the ticket number SPIREXLS-3665. Our development team will investigate and fix it. Once it is resolved, I will inform you in time. Sorry for the inconvenience caused.

Sincerely,
Annika
E-iceblue support team
User avatar

Annika.Zhou
 
Posts: 1643
Joined: Wed Apr 07, 2021 2:50 am

Wed Jan 26, 2022 12:00 am

Hi Annika,

I know tickets can be difficult to estimate but I am wondering do you have any idea when the issue might be addressed?

Thank you!

Bandonia
 
Posts: 34
Joined: Tue Nov 16, 2021 9:40 pm

Wed Jan 26, 2022 8:54 am

Hello,

Thanks for following up.
Kindly note that we deal with the issues on a customer-priority basis, which means we prioritize the issues from customers who have already purchased. Based on the complexity of your issue, it may take our developers a little more time to investigate and solve it. I'll tell you as soon as there is any update. Hope you can understand.

Sincerely,
Annika
E-iceblue support team
User avatar

Annika.Zhou
 
Posts: 1643
Joined: Wed Apr 07, 2021 2:50 am

Mon Jan 31, 2022 9:34 pm

Hi Annika,

I appreciate that clients take priority and totally understand your explanation.

That being said we are currently evaluating your library for use in our Software, and this issue is proving to be something that will prevent us from moving forward with purchasing any licensing.

We regularly generate reconciliation reporting for clients that can often result in the need for excel documents with > 500k rows, and if Spire.Xls cannot provide that in a timely & efficient manner then we will need to move on to investigate alternative providers.

Has there been any update on the ticket created?

Thank you for all of your help on this!

Bandonia
 
Posts: 34
Joined: Tue Nov 16, 2021 9:40 pm

Tue Feb 01, 2022 1:32 am

Hello,

Thanks for following up.
I'm sorry that the issue SPIREXLS-3665 has not been resolved completely due to its complexity. We have a 7-Day Spring Festival Holiday during 01/31/2022-02/06/2022. Once we get back to office, we will deal with them as soon as possible.

Thanks for your understanding and support. We apologize for any inconvenience we may have caused.


Sincerely,
Annika
E-iceblue support team
User avatar

Annika.Zhou
 
Posts: 1643
Joined: Wed Apr 07, 2021 2:50 am

Fri Feb 11, 2022 8:08 pm

Hello Annika,

I hope your Spring Festival was enjoyable.

My apologies for another post but I was curious to know if there had been any progress in the resolution of this ticket?

Thank you!

Bandonia
 
Posts: 34
Joined: Tue Nov 16, 2021 9:40 pm

Mon Feb 14, 2022 1:48 am

Hello,

Thanks for following up.
I just checked the issue status and found that the issue is only partially resolved. In any case, I have asked our developers to fix it ASAP. Please spare us more time. If there is any update, I will inform you in time. We apologize for the inconvenience caused.

Sincerely,
Annika
E-iceblue support team
User avatar

Annika.Zhou
 
Posts: 1643
Joined: Wed Apr 07, 2021 2:50 am

Mon Feb 28, 2022 4:57 pm

Good morning Annika,

I am aware that you said that you would reach out once the issue was resolved, but I am being asked about this regularly. Has there been any progress in the resolution of the ticket?

Thank you for your time

Bandonia
 
Posts: 34
Joined: Tue Nov 16, 2021 9:40 pm

Tue Mar 01, 2022 1:39 am

Hello,

The issue SPIREXLS-3665 has been fully resolved. Considering your situation, we compiled a temporary version for you. I have confirmed your issue has been resolved in this version. Please download and refer to the sample code for testing. Besides, I will inform you ASAP when the official version is released.
Code: Select all
Workbook ReportOutputFile = new Workbook();
ReportOutputFile.LoadFromFile(inputFile);
Worksheet worksheet = ReportOutputFile.Worksheets[0];

CellStyle style = worksheet.Range["A1"].Style;
style.Font.Size = 14.0;
style.Font.FontName = "Arial";
worksheet.ApplyStyle(style);

ReportOutputFile.SaveToFile(outputFile, ExcelVersion.Version2013);

Sincerely,
Annika
E-iceblue support team
User avatar

Annika.Zhou
 
Posts: 1643
Joined: Wed Apr 07, 2021 2:50 am

Wed Mar 02, 2022 6:04 pm

Hi Annika,

From where can I download the libraries with the updated fix? Directly from the site here? And thank you for all of your help to this point it is very much appreciated.

Thank you!

Bandonia
 
Posts: 34
Joined: Tue Nov 16, 2021 9:40 pm

Return to Spire.XLS

cron