Custom culture not applying properly?

Posted by: adrien.corbin on 16 October 2018, 3:56 am EST

    • Post Options:
    • Link

    Posted 16 October 2018, 3:56 am EST

    In our project (and in the inspector) if I execute the following code, the short date is not formatted properly.

             var fr_CA = new GC.Spread.Common.CultureInfo();
    
            fr_CA.NumberFormat.currencySymbol = "$"
            fr_CA.NumberFormat.numberDecimalSeparator = ",";
            fr_CA.NumberFormat.numberGroupSeparator = " ";
            fr_CA.NumberFormat.arrayGroupSeparator = ";";
            fr_CA.NumberFormat.arrayListSeparator = "\\";
            fr_CA.NumberFormat.listSeparator = ";";
            fr_CA.DateTimeFormat.amDesignator = "AM";
            fr_CA.DateTimeFormat.pmDesignator = "PM";
            fr_CA.DateTimeFormat.abbreviatedMonthNames = ["Jan", "Fev", "Mars", "Avr", "Mai", "Jun", "Jul", "Aout", "Sep", "Oct", "Nov", "Dec", ""];
            fr_CA.DateTimeFormat.abbreviatedMonthGenitiveNames = ["Jan", "Fev", "Mars", "Avr", "Mai", "Jun", "Jul", "Aout", "Sep", "Oct", "Nov", "Dec", ""];
            fr_CA.DateTimeFormat.abbreviatedDayNames = ["Di", "Lu", "Ma", "Me", "Je", "Ve", "Sa"];
            fr_CA.DateTimeFormat.dayNames = ["Dimanche", "Lundi", "Mardi", "Mercredi", "Jeudi", "Vendredi", "Samedi"];
            fr_CA.DateTimeFormat.fullDateTimePattern = "EEEE d MMMM yyyy";
            fr_CA.DateTimeFormat.longDatePattern = "d MMMM yyyy";
            fr_CA.DateTimeFormat.longTimePattern = "HH:mm:ss";
            fr_CA.DateTimeFormat.monthDayPattern = "dd MMMM";
            fr_CA.DateTimeFormat.monthNames = ["Janvier", "Février", "Mars", "Avril", "Mai", "Juin", "Juillet", "Août", "Septembre", "Octobre", "Novembre", "Décembre", ""];
            fr_CA.DateTimeFormat.monthGenitiveNames = ["Janvier", "Février", "Mars", "Avril", "Mai", "Juin", "Juillet", "Août", "Septembre", "Octobre", "Novembre", "Décembre", ""];
            fr_CA.DateTimeFormat.shortDatePattern = "yyyy-MM-dd";
            fr_CA.DateTimeFormat.shortTimePattern = "HH:mm";
            fr_CA.DateTimeFormat.yearMonthPattern = "MMMM yyyy";
    
            GC.Spread.Common.CultureManager.addCultureInfo("fr-CA", fr_CA);
            GC.Spread.Common.CultureManager.culture("fr-CA");
    
    

    When we add amDesignator and pmDesignator, the format seems to add “/” to the short date, when left empty, the date is written without any separator.

    In all cases, it does not seem to take account for the shortDatePattern.

    Is there known issues or are we doing somthing wrong?

    Thanks

  • Posted 18 October 2018, 12:44 am EST - Updated 3 October 2022, 1:56 am EST

    Hello,

    This seems to be working fine at my end. Please refer to the attached image and sample application.

    Thanks,

    Deepak Sharma

    SpreadJSCultureFr.zip

  • Posted 9 November 2018, 12:22 am EST

    Thanks for the help after further investication it does work when you add some more values. But when I open a file with a cell already formatted the value does not change.

    I attached a document where the shortDate should be yyyy-MM-dd when in french.

    The step I took to reproduce is:

    1. Open the document in the Inspector
    2. Run the previous code sample

      Expected dates to change to requested format. Also editing already existing dates does not make it change to the requested format.
  • Posted 9 November 2018, 12:22 am EST

  • Posted 12 November 2018, 2:23 am EST

    Hi Adrien,

    You would need to set the culture for spread after importing the Excel. Also you can set the format for desired cells. For example:

    
     $("#button1").click(function () {
                   var excelFile = document.getElementById("fileDemo").files[0];
                excelIO.open(excelFile, function (json) {
                    var workbookObj = json;
                    spread.fromJSON(workbookObj);
                }, function (e) {
                    console.log(e);
                });
                var fr_CA = new GC.Spread.Common.CultureInfo();
    
                fr_CA.NumberFormat.currencySymbol = "$"
                fr_CA.NumberFormat.numberDecimalSeparator = ",";
                fr_CA.NumberFormat.numberGroupSeparator = " ";
                fr_CA.NumberFormat.arrayGroupSeparator = ";";
                fr_CA.NumberFormat.arrayListSeparator = "\\";
                fr_CA.NumberFormat.listSeparator = ";";
                fr_CA.DateTimeFormat.amDesignator = "AM";
                fr_CA.DateTimeFormat.pmDesignator = "PM";
                fr_CA.DateTimeFormat.abbreviatedMonthNames = ["Jan", "Fev", "Mars", "Avr", "Mai", "Jun", "Jul", "Aout", "Sep", "Oct", "Nov", "Dec", ""];
                fr_CA.DateTimeFormat.abbreviatedMonthGenitiveNames = ["Jan", "Fev", "Mars", "Avr", "Mai", "Jun", "Jul", "Aout", "Sep", "Oct", "Nov", "Dec", ""];
                fr_CA.DateTimeFormat.abbreviatedDayNames = ["Di", "Lu", "Ma", "Me", "Je", "Ve", "Sa"];
                fr_CA.DateTimeFormat.dayNames = ["Dimanche", "Lundi", "Mardi", "Mercredi", "Jeudi", "Vendredi", "Samedi"];
                fr_CA.DateTimeFormat.fullDateTimePattern = "EEEE d MMMM yyyy";
                fr_CA.DateTimeFormat.longDatePattern = "d MMMM yyyy";
                fr_CA.DateTimeFormat.longTimePattern = "HH:mm:ss";
                fr_CA.DateTimeFormat.monthDayPattern = "dd MMMM";
                fr_CA.DateTimeFormat.monthNames = ["Janvier", "Février", "Mars", "Avril", "Mai", "Juin", "Juillet", "Août", "Septembre", "Octobre", "Novembre", "Décembre", ""];
                fr_CA.DateTimeFormat.monthGenitiveNames = ["Janvier", "Février", "Mars", "Avril", "Mai", "Juin", "Juillet", "Août", "Septembre", "Octobre", "Novembre", "Décembre", ""];
                fr_CA.DateTimeFormat.shortDatePattern = "yyyy-MM-dd";
                fr_CA.DateTimeFormat.shortTimePattern = "HH:mm";
                fr_CA.DateTimeFormat.yearMonthPattern = "MMMM yyyy";
    
                GC.Spread.Common.CultureManager.addCultureInfo("fr-CA", fr_CA);
                GC.Spread.Common.CultureManager.culture("fr-CA");
              
    
            });
                $("#btngetChart").click(function () {
                  var sheet1 = spread.getSheet(1);                     
                  var val = sheet1.getValue(3, 2);   
                  var dt = new Date(val);
                  sheet1.setValue(3, 2, dt, GC.Spread.Sheets.SheetArea.viewport);     
                  //sheet1.getCell(3, 2).formatter("mmm");
                });
            });
    
    

    Please test the same and let me know if it does not work.

    Thanks,

    Deepak Sharma

  • Posted 12 November 2018, 9:36 am EST

    As mentionned above, I do set the culture after loading the workbook. Since its done manually in the Inspector after importing the Date.xlsx provided.

    Could you provided me the full sample, there is no index.html

    However, tested in the inspector the same way I did before.

    1. Open https://www.grapecity.com/en/demos/spread/JS/InspectorSample/
    2. Import Date.xlsx
    3. Run
             var fr_CA = new GC.Spread.Common.CultureInfo();
    
            fr_CA.NumberFormat.currencySymbol = "$"
            fr_CA.NumberFormat.numberDecimalSeparator = ",";
            fr_CA.NumberFormat.numberGroupSeparator = " ";
            fr_CA.NumberFormat.arrayGroupSeparator = ";";
            fr_CA.NumberFormat.arrayListSeparator = "\\";
            fr_CA.NumberFormat.listSeparator = ";";
            fr_CA.DateTimeFormat.amDesignator = "AM";
            fr_CA.DateTimeFormat.pmDesignator = "PM";
            fr_CA.DateTimeFormat.abbreviatedMonthNames = ["Jan", "Fev", "Mars", "Avr", "Mai", "Jun", "Jul", "Aout", "Sep", "Oct", "Nov", "Dec", ""];
            fr_CA.DateTimeFormat.abbreviatedMonthGenitiveNames = ["Jan", "Fev", "Mars", "Avr", "Mai", "Jun", "Jul", "Aout", "Sep", "Oct", "Nov", "Dec", ""];
            fr_CA.DateTimeFormat.abbreviatedDayNames = ["Di", "Lu", "Ma", "Me", "Je", "Ve", "Sa"];
            fr_CA.DateTimeFormat.dayNames = ["Dimanche", "Lundi", "Mardi", "Mercredi", "Jeudi", "Vendredi", "Samedi"];
            fr_CA.DateTimeFormat.fullDateTimePattern = "EEEE d MMMM yyyy";
            fr_CA.DateTimeFormat.longDatePattern = "d MMMM yyyy";
            fr_CA.DateTimeFormat.longTimePattern = "HH:mm:ss";
            fr_CA.DateTimeFormat.monthDayPattern = "dd MMMM";
            fr_CA.DateTimeFormat.monthNames = ["Janvier", "Février", "Mars", "Avril", "Mai", "Juin", "Juillet", "Août", "Septembre", "Octobre", "Novembre", "Décembre", ""];
            fr_CA.DateTimeFormat.monthGenitiveNames = ["Janvier", "Février", "Mars", "Avril", "Mai", "Juin", "Juillet", "Août", "Septembre", "Octobre", "Novembre", "Décembre", ""];
            fr_CA.DateTimeFormat.shortDatePattern = "yyyy-MM-dd";
            fr_CA.DateTimeFormat.shortTimePattern = "HH:mm";
            fr_CA.DateTimeFormat.yearMonthPattern = "MMMM yyyy";
    
            GC.Spread.Common.CultureManager.addCultureInfo("fr-CA", fr_CA);
            GC.Spread.Common.CultureManager.culture("fr-CA");
    
    
    1. Run
    
    let sheet = spread.getSheet(1);
    let newValue = new Date(sheet.getValue(3, 2));
    sheet.setValue(3, 2, new Date(newValue), GC.Spread.Sheets.SheetArea.viewport);
    

    Expected to see 2018-01-01 00:00:00 (or the date only) but no change are seen and still have 1/1/2018

  • Posted 12 November 2018, 8:04 pm EST

    Hello,

    It requires to set the format for the cells as desired. You can use the code below:

    
       $(document).ready(function () {
                var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"));
                
                var excelIO= new GC.Spread.Excel.IO();
    
                $("#button1").click(function () {
                    spread.suspendPaint();
                   var excelFile = document.getElementById("fileDemo").files[0];
                excelIO.open(excelFile, function (json) {
                    var workbookObj = json;
                    spread.fromJSON(workbookObj);
                }, function (e) {
                    console.log(e);
                });
                spread.resumePaint();
    
    
                var fr_CA = new GC.Spread.Common.CultureInfo();
                fr_CA.NumberFormat.currencySymbol = "$"
                fr_CA.NumberFormat.numberDecimalSeparator = ",";
                fr_CA.NumberFormat.numberGroupSeparator = " ";
                fr_CA.NumberFormat.arrayGroupSeparator = ";";
                fr_CA.NumberFormat.arrayListSeparator = "\\";
                fr_CA.NumberFormat.listSeparator = ";";
                fr_CA.DateTimeFormat.amDesignator = "AM";
                fr_CA.DateTimeFormat.pmDesignator = "PM";
                fr_CA.DateTimeFormat.abbreviatedMonthNames = ["Jan", "Fev", "Mars", "Avr", "Mai", "Jun", "Jul", "Aout", "Sep", "Oct", "Nov", "Dec", ""];
                fr_CA.DateTimeFormat.abbreviatedMonthGenitiveNames = ["Jan", "Fev", "Mars", "Avr", "Mai", "Jun", "Jul", "Aout", "Sep", "Oct", "Nov", "Dec", ""];
                fr_CA.DateTimeFormat.abbreviatedDayNames = ["Di", "Lu", "Ma", "Me", "Je", "Ve", "Sa"];
                fr_CA.DateTimeFormat.dayNames = ["Dimanche", "Lundi", "Mardi", "Mercredi", "Jeudi", "Vendredi", "Samedi"];
                fr_CA.DateTimeFormat.fullDateTimePattern = "EEEE d MMMM yyyy";
                fr_CA.DateTimeFormat.longDatePattern = "d MMMM yyyy";
                fr_CA.DateTimeFormat.longTimePattern = "HH:mm:ss";
                fr_CA.DateTimeFormat.monthDayPattern = "dd MMMM";
                fr_CA.DateTimeFormat.monthNames = ["Janvier", "Février", "Mars", "Avril", "Mai", "Juin", "Juillet", "Août", "Septembre", "Octobre", "Novembre", "Décembre", ""];
                fr_CA.DateTimeFormat.monthGenitiveNames = ["Janvier", "Février", "Mars", "Avril", "Mai", "Juin", "Juillet", "Août", "Septembre", "Octobre", "Novembre", "Décembre", ""];
                fr_CA.DateTimeFormat.shortDatePattern = "yyyy-MM-dd";
                fr_CA.DateTimeFormat.shortTimePattern = "HH:mm";
                fr_CA.DateTimeFormat.yearMonthPattern = "MMMM yyyy";
                GC.Spread.Common.CultureManager.addCultureInfo("fr-CA", fr_CA);
                GC.Spread.Common.CultureManager.culture("fr-CA");
    
            });
                $("#btnsetFormat").click(function () {              
                   
                    var sheet1 = spread.getSheet(1);
                    sheet1.getRange(0, 2, sheet1.getRowCount(), 1, GC.Spread.Sheets.SheetArea.viewport).formatter("yyyy-MM-dd");
    
                });
            });
    
    

    It works fine with the Excel file you provided.

    Thanks,

    Deepak Sharma

  • Posted 13 November 2018, 12:43 am EST

    This does not work either. If I do set the format to ‘yyyy-MM-dd’. It will be shown properly. But this break the purpose.

    If I change the language back to en-US, it stays with the format I just set and does not go back to MM/dd/yyyy format.

    I believe we shouldn’t have anything to do to make cell which already have a dynamic culture format set from excel to change when the culture is changed.

    As stated before, it does work for new cell which we are defining values, it just does not work for cell which have been set with fromJson for some reason.

    As usual, the workbook provided is only an example and it would be tedious to have to keep track of all cell containing dates and reformat them on culture change. It does not seem an option to us.

    Thanks

  • Posted 14 November 2018, 1:02 am EST

    Hello,

    When Excel file is imported, the text of the cell is set without format. However the underlying value is shown as per the format used. This can be checked while editing the cell.

    The format would need to be applied again. I will further check if is another way apply the format in existing cell value and let you know.

    Thanks,

    Deepak Sharma

  • Posted 16 November 2018, 12:31 am EST

    Hello,

    The tracking id for this is 267386. I will update you soon.

    Thanks,

    Deepak Sharma

  • Posted 13 February 2019, 1:47 am EST

    Hi, is there any news on this?

  • Posted 13 February 2019, 4:40 pm EST

    Hi Andrien,

    As per the development team, this is not supported as of now.

    In your excel file, the cell set to a built-in date format which mapped to current built-in format list.

    Which means if you open the excel file in France version, it will show as "yyyy-mm-dd), but if you open the excel file in English version, it will show as “mm/dd/yyyy” .

    Hence, when SpreadJS import that excel, we found that the format is just a built-in format index, hence, we will get the format list from current built-in culture but could not find “France” culture built-in list, hence use default “en” culture, and the format string is “mm/dd/yyyy”.

    I have asked for further investigation on this. I will let you know as soon as I get an update on it.

    Thanks,

    Deepak Sharma

  • Posted 15 February 2019, 11:22 pm EST

    Hi Team,

    We have a requirement of using Arabic language in spreadjs. The requirement is to use culture for Arabic for number and date format.

    As per our understanding, spreadjs supports three language cultures (English, Chinese Japanese) through the use of resource library.

    Can spreadjs share similar resource library/file for Arabic culture or pseudo angular code to create custom culture for Arabic language?

    Thanks,

    Manoj

  • Posted 17 February 2019, 7:35 pm EST

    HI Manoj,

    You can set the number /date format as per Arabic culture settings creating custom culture as shown above.

    To provide Arabic resource library, I have submitted an enhancement request to the development team. I will let you know as soon as I get an update on it.

    Request id for this is 270077.

    Thanks,

    Deepak Sharma

  • Posted 6 March 2019, 8:35 pm EST

    Hi Adrien,

    Developers have come up with a work around for this issue(for bug id #267386). You can set the format for dates using the code as follows:

    
    <script type="text/javascript">
            var enToFranchFormatMap = {};
            enToFranchFormatMap['mm/dd/yyyy'] = new GC.Spread.Formatter.GeneralFormatter('yyyy-mm-dd');
            enToFranchFormatMap['m/d/yyyy'] = new GC.Spread.Formatter.GeneralFormatter('yyyy-m-d');
            // add more mapping here as needed.
     
            var g_getFormatter = GC.Spread.Formatter.GeneralFormatter.prototype.getFormatter;
            GC.Spread.Formatter.GeneralFormatter.prototype.getFormatter = function (index) {       
                var f = g_getFormatter.call(this, index);
                var formatString = f && f.formatString();
                if (formatString && formatString.length > 0 && enToFranchFormatMap[formatString]) {
                    return enToFranchFormatMap[formatString];
                }
                return f;
            }
        </script>
    
    

    I have tested this and it seems to be working fine.

    Please refer to the attached sample.

    Thanks,

    Deepak SharmaSpreadJSCultureJPFR.zip

Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels