French formated dates aren't calculated as wished in Spire

[eurotitrisation]

Hello everyone;

We are meeting a problem while trying to calculate french dates in formulas.

French format is "AAAAMMJJ" and it should return for exemple 20240702 but instead we get chinese values 星期一07JJ

This code replicates the issue ( The attached file is sotred in Resource folder at the root of the project ) :

Code: Select all

using System;
using System.IO;
using Spire.Xls;

namespace ExempleSpireExcel
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                string cheminBase = AppDomain.CurrentDomain.BaseDirectory;

                string cheminProjet = Path.GetFullPath(Path.Combine(cheminBase, @"..\..\.."));

                string cheminFichier = Path.Combine(cheminProjet, "resources", "test.xlsx");

                Console.WriteLine($"Chargement du fichier : {cheminFichier}");

                Workbook wb = new Workbook();
                wb.LoadFromFile(cheminFichier);
                wb.CalculateAllValue();

                string cheminSortie = Path.Combine(cheminProjet, "resources", "test_Calcule.xlsx");
                wb.SaveToFile(cheminSortie, ExcelVersion.Version2016);

                Console.WriteLine($"Terminé ! Fichier sauvegardé : {cheminSortie}");
            }
            catch (Exception ex)
            {
                Console.WriteLine("Erreur : " + ex.Message);
            }
        }
    }
}


Then, to avoid Excel recalcuating the file, we convert it to a zip and look to the computed value :

Code: Select all

<row r="1" ht="15">
<c r="A1" t="str">
<f ca="1">TEXT(TODAY(),"aaaammjj")</f>
<v>星期一12jj</v>
</c>
</row>

Please let us know if you got a workaround for this.

Thanks

[talia.liu]

Hello,

Thank you for your inquiry.
When I opened your test.xlsx file using Microsoft Excel Pro 2019, the cells containing date formulas continued to display as "星期二12jj." Through research, I found that "aaaammjj" is a specific date format used in the French version of Excel. Unfortunately, Spire.XLS does not currently support this particular format. To resolve this issue, we recommend adopting the internationally recognized Excel date format "yyyymmdd" in your date format settings. Thank you for your understanding.

[eurotitrisation]

Thanks for your reply.

Unfortunately, it’s impossible for us to switch to international date formats. For now, we parse the Excel file before reading from it, but this approach has its limitations.

Can we expect a fix from your side?

Thanks in advance.

[talia.liu]

Hello,

Thank you for your feedback.
I have logged your requirement into our product enhancement system with the ticket SPIREXLS-6043. Our Dev team will further investigate implementation possibilities, once it is resolved or there is any update, I will notify you immediately. Apologize for the inconvenience caused.

[talia.liu]

Hello,

Thank you for your patience.
We have just released the official version(Hotfix:Spire.XLS Version:15.12) which fixed the issue where date formulas were calculated incorrectly in French locale(SPIREXLS-6043). Welcome to test. For testing, please add the following setting to your code and set your computer's regional format to French (France):

Code: Select all

book.CultureInfo = new System.Globalization.CultureInfo("fr-FR");

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