worksheet.SaveTo* number formatting

[GerhardFinee]

Hi,

I am converting Excel worksheets to CSV using worksheet.SaveToStream method.
After upgrading Spire.XLS from 10.9.0 to 12.1.0 the method seems not to be respecting CurrentCulture for formatting numbers any more.

Code: Select all

Thread.CurrentThread.CurrentCulture = new CultureInfo("de-DE");
// generate CSV, numbers used to be formatted based on culture info
worksheet.SaveToStream(outputStream, ";", Encoding.UTF8);


With 10.9.0, comma is used for decimal separation as expected, e.g. 3,1415
With 12.1.0, point is used for decimal separation, e.g. 3.1415

Is there a new way to tell Spire how to format numbers?

KR,
Gerhard

[Marcia.Zhou]

Hello,

Thanks for your inquiry!

I simulated an Excel file, and converted it to CSV format as the code you provided with the latest Spire.Xls for net 12.1.0, but did not reproduce your issue. Here I attached my testing code, input file and generated file. To help us reproduce your issue, please provide us with the following information. You can also send them to us via email (support@e-iceblue.com), thanks in advance!

1. Your input files.
2. The target framework of your project (E.g. net5.0).
3. Your system information (E.g. Win7, 64 bit) and region setting (E.g. China, Chinese).
4. The type of your project (E.g. console application).

Sincerely,
Marcia
E-iceblue support team

[GerhardFinee]

Hi,

1. I tested with your input file and was able to reproduce the issue

2. netcoreapp3.1

3. I can reproduce the issue on 2 different machines:

a. my local machine:
CurrentCulture = de-AT
OS Name: Windows
OS Version: 10.0.19042
OS Platform: Windows
RID: win10-x64
Base Path: C:\Program Files\dotnet\sdk\6.0.100\
dotnet

b. Azure web app:
CurrentCulture = en-US
OS Name: Windows
OS Version: 10.0.14393
OS Platform: Windows
RID: win10-x86
Base Path: D:\Program Files (x86)\dotnet\sdk\6.0.100\

4. Output type is "Console Application", my code runs as part of a asp.net web service controller (Microsoft.AspNetCore.Mvc.ControllerBase)

Additionally, I was able to determine that all versions starting from 10.11.2 of Spire.XLS are causing the issue. Prior versions work as expected.

This is my code:

Code: Select all

        private byte[] SheetAsCSV(Worksheet worksheet, string cultureInfo)
        {
            using (MemoryStream outputStream = new MemoryStream())
            {
                // remember current culture
                CultureInfo currentCulture = Thread.CurrentThread.CurrentCulture;

                // set culture info if given, default to de-DE
                if (!string.IsNullOrEmpty(cultureInfo))
                    Thread.CurrentThread.CurrentCulture = new CultureInfo(cultureInfo);
                else
                    Thread.CurrentThread.CurrentCulture = new CultureInfo("de-DE");

                // generate CSV, numbers are formatted based on culture info
                worksheet.SaveToStream(outputStream, ";", Encoding.UTF8);

                // set culture info back to default
                Thread.CurrentThread.CurrentCulture = currentCulture;

                return outputStream.ToArray();
            }
        }

The result is always formatted using the machine's preset culture, regardless of what I am setting CurrentCulture to.

KR,
Gerhard

[Marcia.Zhou]

Hello,

Thanks for sharing more information!

I simulated a netcore 3.1 application and tested it on Win10 with the file I provided before. But still did not reproduce the issue, the number is formatted as "1,234". Here I attached my project. To help us reproduce your issue quickly and efficiently, please provide us with a simple project which could reproduce the issue directly. Thanks again for your cooperation!

Sincerely,
Marcia
E-iceblue support team

[GerhardFinee]

Hi,

unchanged, your sample works on my machine. However, moving assignment to CurrentCulture behind LoadFromFile() method causes the issue:

Code: Select all

            // Thread.CurrentThread.CurrentCulture = new CultureInfo("de-DE");
            MemoryStream stream = new MemoryStream();
            Workbook workbook = new Workbook();
            workbook.LoadFromFile("test.xlsx");
            Thread.CurrentThread.CurrentCulture = new CultureInfo("de-DE");
            workbook.SaveToStream(stream, ";");
            File.WriteAllBytes("a.csv", stream.GetBuffer());

Looks like you internally moved determination of current culture from "just before save method" to "right after workbook loading", starting from version 10.11.2.

KR,
Gerhard

[Marcia.Zhou]

Hello,

I did reproduce the issue according to your describe, and I have logged it in our issue tracking system with the ticket SPIREXLS-3668 for further investigation.

We will let you know if there is any update. Sorry for the inconvenience caused.

Sincerely,
Marcia
E-iceblue support team

[Marcia.Zhou]

Hello,

Thanks for your patience!

Glad to inform you that we just released Spire.XLS Pack(Hotfix) Version:12.3.2 which fixes the issue SPIREXLS-3668. Now, we use workbook.CultureInfo = new CultureInfo("de-DE"); to apply the culture to Excel directly, and this cannot affect by the culture of current thread.

Please download the fix version from the following links and the following code to test.

Website link: https://www.e-iceblue.com/Download/download-excel-for-net-now.html
Nuget link:https://www.nuget.org/packages/Spire.XLS/12.3.2

Code: Select all

            MemoryStream stream = new MemoryStream();
            Workbook workbook = new Workbook();
            workbook.LoadFromFile("test.xlsx");
            workbook.CultureInfo = new CultureInfo("de-DE");
            workbook.SaveToStream(stream, ";");
            File.WriteAllBytes("a.csv", stream.GetBuffer());


Sincerely,
Marcia
E-iceblue support team

[Marcia.Zhou]

Hello,

Hope you are doing well!

Has the issue been solved now? Could you please give us some feedback at your convenience?

Thanks in advance.

Sincerely,
Marcia
E-iceblue support team

[GerhardFinee]

Hi,

the issue has NOT been solved, you just provided a new way of setting culture for no reason. The real issue is and always was, that you lost backwards compatibility starting from version 10.11.2. As I said earlier, determination of current culture was moved from "just before save method" to "right after workbook loading", starting from version 10.11.2. Our source code relies on "just before save method" in numerous occurrences. That way we are forced to changing all source code because your lack of backwards compatibility. And I bet we are not the only customer with this problem, it's just a matter of time it arises elsewhere.

Please establish compatibility.

KR,
Gerhard

[Marcia.Zhou]

Hello Gerhard,

Kindly note that our Spire.Xls version 10.9.0 and before is based on the uncombined model, but since version 10.9.16, we combined the models, and the designed request that the culture of the Workbook is decided by the current thread culture when created it, and subsequent modifications to thread culture never affect the language culture properties of the book object. This is our design rule, and we will not change that. Even using Microsoft Excel, the data format of the value will not change as soon as you change the system region setting when you opened the Excel file, and the format is applied after reopening the file only. We followed the MS Excel rules, so we will not change the logic about culture.

If you want to change the culture after created or load the file, I am afraid that you need to change your code to mine as I provided before. Hope you can understand.

Sincerely,
Marcia
E-iceblue support team

[GerhardFinee]

Hi,

well, I am not happy to hear that. This is just poor (1) software engineering, (2) test engineering and (3) client communication.

(1) always try to maintain compatibility with old versions
(2) test extensively to be aware of changing behavior when you internally change your models
(3) if there's really no way of keeping compatibility, you NEED to inform all clients about that change. You should do so immediately in this case.

KR,
Gerhard

[Marcia.Zhou]

Hello Gerhard.

We're sorry that the new method we provided makes you feel uncomfortable.

All along, our dev team is constantly updating and improving the functions and efficiency of our products, for those parts that are not efficient and prone to failure, we will use new methods to replace, that is, the new methods will be more stable, efficient and easy to maintain than the old methods.

The same is true for your question, sorry that we will not consider going back to the previous processing logic. I just recommend that you replace it with a new method, and this method avoids the impact of thread area culture changes on multithreaded programs effectively. In the long run, the pros outweigh the cons.

I understand that modifying the code may involve a lot of work for you, but for better use later, I hope you can understand and adopt the new methods we have provided to you. Thank you again for your understanding and cooperation.

Sincerely,
Marcia
E-iceblue support team

[GerhardFinee]

Hi,

OK, I understand that you tried to improve software quality. You should know that overall we are very satisfied with the quality of your software.
Thanks for your support.

KR,
Gerhard

[Marcia.Zhou]

Hello Gerhard,

Thanks again for your understanding!

If you encounter any issues related to our product in the future, just feel free to contact us.

Have a nice day!

Sincerely,
Marcia
E-iceblue support team