Log in to start a topic

File created in v14.1.1 does not open correctly in v16

Posted by: Fabrice.Mainguene on 23 November 2023, 5:45 am EST

  • Fabrice.Mainguene

    • Post Options:
    • Link
      Copy

    Posted 23 November 2023, 5:45 am EST - Updated 23 November 2023, 5:52 am EST

    Hi,

    I created this file in v14.1: spreadjs.zip

    When I open it in v16, my formulas are in error:



    For open the file, I use the Italian culture:

    var myCulture = new GC.Spread.Common.CultureInfo();
myCulture.NumberFormat.currencyDecimalDigits = 2;
myCulture.NumberFormat.currencyDecimalSeparator = ',';
myCulture.NumberFormat.currencyGroupSeparator = '.';
myCulture.NumberFormat.currencyGroupSizes = [3];
myCulture.NumberFormat.currencyNegativePattern = 0;
myCulture.NumberFormat.currencyPositivePattern = 0;
myCulture.NumberFormat.currencySymbol = '€';
myCulture.NumberFormat.digitSubstitution = 1;
myCulture.NumberFormat.isReadOnly = true;
myCulture.NumberFormat.numberGroupSizes = [3];
myCulture.NumberFormat.nanSymbol = 'NaN';
myCulture.NumberFormat.nativeDigits = ['0', '1', '2', '3', '4', '5', '6', '7', '8', '9'];
myCulture.NumberFormat.numberNegativePattern = 1;
myCulture.NumberFormat.negativeInfinitySymbol = '-Infinity';
myCulture.NumberFormat.negativeSign = '-';
myCulture.NumberFormat.numberDecimalDigits = 2;
myCulture.NumberFormat.numberDecimalSeparator = ',';
myCulture.NumberFormat.numberGroupSeparator = '.';
myCulture.NumberFormat.positiveInfinitySymbol = 'Infinity';
myCulture.NumberFormat.positiveSign = '+';
myCulture.NumberFormat.percentDecimalDigits = 2;
myCulture.NumberFormat.percentDecimalSeparator = '.';
myCulture.NumberFormat.percentGroupSeparator = ',';
myCulture.NumberFormat.percentGroupSizes = [3];
myCulture.NumberFormat.percentNegativePattern = 0;
myCulture.NumberFormat.percentPositivePattern = 0;
myCulture.NumberFormat.percentSymbol = '%';
myCulture.NumberFormat.perMilleSymbol = '‰';
myCulture.NumberFormat.listSeparator = ';';
myCulture.NumberFormat.arrayListSeparator = '\\\\';
myCulture.NumberFormat.arrayGroupSeparator = ';';
myCulture.NumberFormat.dbNumber = {};
myCulture.NumberFormat.standardDictionaryNumbers.Hundreds = 'Hundreds';
myCulture.NumberFormat.standardDictionaryNumbers.Thousands = 'Thousands';
myCulture.NumberFormat.standardDictionaryNumbers.Millions = 'Millions';
myCulture.NumberFormat.standardDictionaryNumbers.Billions = 'Billions';
myCulture.NumberFormat.standardDictionaryNumbers.Trillions = 'Trillions';
myCulture.DateTimeFormat.abbreviatedDayNames = ['dom', 'lun', 'mar', 'mer', 'gio', 'ven', 'sab'];
myCulture.DateTimeFormat.abbreviatedMonthGenitiveNames = ['gen', 'feb', 'mar', 'apr', 'mag', 'giu', 'lug', 'ago', 'set', 'ott', 'nov', 'dic'];
myCulture.DateTimeFormat.abbreviatedMonthNames = ['gen', 'feb', 'mar', 'apr', 'mag', 'giu', 'lug', 'ago', 'set', 'ott', 'nov', 'dic'];
myCulture.DateTimeFormat.amDesignator = 'AM';
myCulture.DateTimeFormat.calendarIsReadOnly = true;
myCulture.DateTimeFormat.calendarWeekRule = 0;
myCulture.DateTimeFormat.Calendar = { 'MinSupportedDateTime': '@-62135568000000@', 'MaxSupportedDateTime': '@253402300799999@', 'AlgorithmType': 1, 'CalendarType': 1, 'Eras': [1], 'TwoDigitYearMax': 2029, '': true };
myCulture.DateTimeFormat.dateSeparator = '/';
myCulture.DateTimeFormat.dayNames = ['domenica', 'lunedì', 'martedì', 'mercoledì', 'giovedì', 'venerdì', 'sabato'];
myCulture.DateTimeFormat.defaultDatePattern = 'dd/MM/yyyy HH:mm:ss';
myCulture.DateTimeFormat.firstDayOfWeek = 0;
myCulture.DateTimeFormat.fullDateTimePattern = 'dddd d MMMM yyyy HH:mm:ss';
myCulture.DateTimeFormat.longDatePattern = 'dddd d MMMM yyyy';
myCulture.DateTimeFormat.longTimePattern = 'HH:mm:ss';
myCulture.DateTimeFormat.monthDayPattern = 'MMMM dd';
myCulture.DateTimeFormat.monthGenitiveNames = ['gennaio', 'febbraio', 'marzo', 'aprile', 'maggio', 'giugno', 'luglio', 'agosto', 'settembre', 'ottobre', 'novembre', 'dicembre'];
myCulture.DateTimeFormat.monthNames = ['gennaio', 'febbraio', 'marzo', 'aprile', 'maggio', 'giugno', 'luglio', 'agosto', 'settembre', 'ottobre', 'novembre', 'dicembre'];
myCulture.DateTimeFormat.nativeCalendarName = 'Gregorian Calendar';
myCulture.DateTimeFormat.pmDesignator = 'PM';
myCulture.DateTimeFormat.shortDatePattern = 'dd/MM/yyyy';
myCulture.DateTimeFormat.shortestDayNames = ['Su', 'Mo', 'Tu', 'We', 'Th', 'Fr', 'Sa'];
myCulture.DateTimeFormat.shortTimePattern = 'HH:mm';
myCulture.DateTimeFormat.timeSeparator = ':';
myCulture.DateTimeFormat.yearMonthPattern = 'MMMM yyyy';
myCulture.DateTimeFormat.filterDialogDateFormatter = 'yyyy/mmmm/dd';
myCulture.DateTimeFormat.preselectedFormatters = [];
myCulture.DateTimeFormat.eraFormatter = ['ge/M/d', 'ge-M-d', 'ge.M.d', 'gee/MM/dd'];
	
GC.Spread.Common.CultureManager.addCultureInfo('it-IT',myCulture);
GC.Spread.Common.CultureManager.culture('it-IT');

    I tried to use the recalcAll() function to recalculate my formulas but It seems to work only after the user did a selection in the document …

    So why the document is now opened with errors?

    Why the recalcAll() command doesn’t work properly at the opening of the document?

  • chandan.rawat

    Posted 23 November 2023, 7:44 am EST - Updated 23 November 2023, 7:49 am EST

    Hi,

    Thank you for providing us with the SSJSON file and code snippet.

    We are not able to replicate the issue at our end. We tried to replicate the issue with SpreadJS v16.2.6 by setting Italian culture to SpreadJS. However, it was working correctly and the formulas were not throwing errors.

    Please refer to the attached sample and GIF for more understanding.

    sample: https://jscodemine.grapecity.com/share/vzJmWVnWyEGkKLXVZZ-dXw/?IsEmbed=false&Theme=Unset&PreviewDirection=0&IsEditorShow=true&IsExplorerShow=true&IsPreviewShow=true&IsConsoleShow=true&IsRunBTNShow=false&IsResetBTNShow=false&IsOpenInCodemineBTNShow=false&PanelWidth=20&PanelWidth=50&PanelWidth=30&defaultOpen={"OpenedFileName"%3A["%2Findex.html"%2C"%2Fsrc%2Fapp.js"%2C"%2Fsrc%2Fculture.js"]%2C"ActiveFile"%3A"%2Fsrc%2Fculture.js"}

    It is crucial to note that the Italian culture should be set in SpreadJS where you are importing the SSJSON file because the parsing and calculation of formulas also depend upon the culture applied.

    Regards

  • Fabrice.Mainguene

    Posted 23 November 2023, 9:16 am EST

    Thanks for your quick answer.

    I never tested it without designer. Actually it seems that the bug only occurs when the designer is present.

  • Fabrice.Mainguene

    Posted 27 November 2023, 5:14 am EST

    Hi,

    Have you test with the designer?

  • chandan.rawat

    Posted 28 November 2023, 1:55 am EST

    Hi,

    I have tested it in the SpreadJS Designer at https://developer.mescius.com/spreadjs/designer/index.html. The issue occurs here because the hosted SpreadJS Designer has an en-US culture by default and the SSJSON file includes formats of Italian culture. That is why the issue occurs.

    As I mentioned in my previous reply, the parsing of the values/formulas also depends on the culture applied. A valid formatted value in Italian culture can be invalid in en-US culture.

    Regards,

    Chandan

  • Fabrice.Mainguene

    Posted 28 November 2023, 5:12 am EST

    And do what?

    I know the bug thanks… I need a solution!

    I don’t see what the UI designer has to do with opening of my document with the right culture! I inject the correct culture and I open my document. It must work correctly. The designer has nothing to do with it. It is just a bug!

    By the way, how do you update your designer according to the culture?

  • chandan.rawat

    Posted 29 November 2023, 2:26 am EST - Updated 29 November 2023, 2:31 am EST

    Hi,

    We are sorry for the misunderstanding caused. The observed behavior is not a bug. Further, we would like to explain how culture affects the calculation of formulas/functions.

    In the SSJSON file that you have shared with us, the cell range D1:E2 has string values. For instance, cell D1 has the value “17,1”. So, if the Italian(it-IT) culture is applied to SpreadJS and since “,” acts as a decimal separator, the value “17,1” is parsed as 17.1 for calculation in the formulas. That is why, the formulas are calculated without any errors in the SpreadJS sample that I previously shared with you.

    1273×637 34.4 KB

    Kindly refer to the below GIF.

    But when the SSJSON file is imported in the SpreadJS Designer hosted at https://developer.mescius.com/spreadjs/designer/index.html, the formulas are not calculated and #VALUE! errors are thrown because the default culture applied is en-US. In en-US culture, the symbol comma(“,”) is treated as a thousand group separator and not as a decimal number separator. That is why, the string values in cell range D1:E2 can not be parsed to number(data type), and the error is thrown.

    There are two ways to solve the issue. One way is to apply the Italian culture. But since it is already hosted, that is not recommended. However, if you are using SpreadJS Designer in your application, you can apply the Italian culture. Another way is to set number values in cell range D1:E2 instead of string values. So that they could be used without parsing the string values to numbers in the absence of Italian culture.

    It is important to note that the actual values should be set with SpreadJS APIs irrespective of the culture applied. For instance, even if Italian culture is applied to SpreadJS, with SpreadJS APIs the actual value should be set.

    sheet.setValue(0, 0, 11.11); instead of sheet.setValue(0, 0, ‘11,11’);

    Kindly refer to the below GIF.

    In the attached gif, period(“.”) is used as a number decimal separator.

    Please note that when the SSJSON is exported to xlsx from SpreadJS and xlsx file is opened in MS Excel, it also shows #VALUE! because by default, MS Excel uses the culture set in the system. At my end, it is en-US.

    Kindly refer to the below GIF.

    If you need further assistance, kindly let us know.

    Regards

  • Fabrice.Mainguene

    Posted 29 November 2023, 4:56 am EST

    Hi,

    i don’t understand why you’re talking about US culture.

    These are the tasks I do:

    1- Load the spreadjs area with the designer

    2- when the loading is terminated, I change the culture for the Italian culture

    3- I load my document

    4- I have the error in my cells

    when I open my document It is the Italian culture that is loaded, so why it is the US that is used. If I do a recalculate() on it, the Italian culture is correctly used…

    Regarding the data, I know the document isn’t perfect, but it was created and saved with SpreadJS by my customers. It should therefore be reloaded without error.

  • chandan.rawat

    Posted 30 November 2023, 3:15 am EST - Updated 30 November 2023, 3:20 am EST

    Hi,

    We are able to replicate the issue at our end. The issue was replicable when Italian culture was applied to SpreadJS and SSJSON file was imported with SpreadJS Designer. Hence, we have escalated this issue to the dev team for further investigation. The internal tracking ID for the issue is SJS-21596. Please be rest assured that we will keep you updated on the progress of the issue.

    As a workaround, you can add an event listener to the FileLoaded event of the designer and inside the handler change the selection on the sheet and use spread.calculate() method to re-calculate the formulas.

    Please refer to the attached sample and GIF for more understanding.

    sample: https://jscodemine.grapecity.com/share/B_7Ii-FZjE274cR8LQwueQ/?IsEmbed=false&Theme=Unset&PreviewDirection=0&IsEditorShow=true&IsExplorerShow=true&IsPreviewShow=true&IsConsoleShow=true&IsRunBTNShow=false&IsResetBTNShow=false&IsOpenInCodemineBTNShow=false&PanelWidth=20&PanelWidth=50&PanelWidth=30&defaultOpen={"OpenedFileName"%3A["%2Findex.html"%2C"%2Fsrc%2Fapp.js"]%2C"ActiveFile"%3A"%2Fsrc%2Fapp.js"}

    Regards

  • chandan.rawat

    Posted 9 January 2024, 9:22 am EST

    Hi,

    The mentioned issue has been fixed in the latest version of SpreadJS i.e. v17.0.1. Please refer to the below link to download the latest version of SpreadJS.

    Download link: http://cdn.mescius.com/spreadjs/17.0.1/Files/SpreadJS.Release.17.0.1.zip

    If you face any issues, kindly let us know.

    Regards

Need extra support?

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

Learn More

Forum Channels

  • © 2023 MESCIUS USA, Inc. All Rights Reserved.  ·  1.800.858.2739

    All product and company names herein may be trademarks of their respective owners.