Auto formatter on data injection

Posted by: davide.vago on 6 September 2021, 3:29 am EST

  • Posted 6 September 2021, 3:29 am EST

    Dear support,

    I'm reaching out as I would like to understand if within the library there is a possibility to auto format the injected data without passing any formatter key.

    For example:

    Given a JSON object which contains data as such:
    { value: "38.280" } or even { value: "12,2%"}

    Do you have any function the generates the formatter given a string to be used within a worksheet?

    e.g.
    - the first should print as value 38.280 and formatter 0.000
    - the second one should print as value 0.122 and formatter 0,0%

    Thanks in advance
  • Replied 6 September 2021, 8:40 pm EST

    Hi Davide,

    We are sorry but SpreadJS does not provide any method by which we could get the formatted string according to the value.

    Regards
    Avinash
  • Replied 6 September 2021, 9:32 pm EST

    Hi,

    Thanks for your answer. I wonder if there is any way to get that to work - when I manually type '15%' in a cell the cell is indeed formatted based on its value.
    '15%' would result in 0.15 being stored as a value and the proper formatting generated automatically.

    What we would like to achieve is the same thing with the only difference that data is not typed in from the end user but it is given programmatically within the script.

    Are you really sure this cannot be done ?
  • Replied 7 September 2021, 6:03 pm EST

    Hi,

    When we enter the 10% in any cell the spreadJS autoformatter judges the value and parse it to the percentage formatter however the spread does not expose the autoformatted to the API that is why we could not parse the string to the corresponded format.

    Further, we have escalated this issue to the dev team and asked them regarding the same. We will update you regarding this as we get any information from the team. The internal ID for this issue will be SJS-10026.

    Regards
    Avinash
  • Replied 8 September 2021, 5:02 pm EST

    HI,

    For this, you need to use the editCell Command. Please refer to the following code snippet and let us know if you face any issues.


    spread.commandManager().execute({ cmd: 'editCell', sheetName: sheet.name(), row: 0, col: 0, newValue: '10%', autoFormat: true });


    Regards
    Avinash
  • Replied 8 September 2021, 8:39 pm EST

    Dear Avinash,

    It's going to take us a couple of weeks before getting back to you, but in the meanwhile I would like to thank you for your quick answer.

    Thanks,
    Pietro
  • Marked as Answer

    Replied 9 September 2021, 6:10 pm EST

    Hi,

    That totally fine for us. Please takes your time and feels free to revert back if you face any issues.

    Regards
    Avinash
  • Replied 31 October 2021, 9:54 pm EST

    Dear Avinash,

    This snippet is not working for our specific case. as mentioned earlier we are trying to inject data which is a plain text e.g. { value: "38.280" } or even { value: "12,2%"}.

    What we were asking was something to permit us to auto format the injected data, so using the method fromJSON we were expecting this to automatically generate an output within the spreadsheet were things like value: "12.2%" becomes value: 0.122 with formatter 0.0%.

    The snippet provided executes an editCell, most likely on edit cell so it's not exactly appropriated.

    Would you please provide us a sandbox with an example of your proposed solution so we can pick it up.

    Looking forward to hearing from you
  • Replied 2 November 2021, 4:55 am EST

    Hi Davide,

    Could you please provide the following information so that we could have a better understanding

    1. Are you trying to inject data using the fromJSON method if so, how that JSON is generated?

    We have tried 2 cases:
    1.1 In the first case we have an excel sheet that has a cell with percentage format value is 10% and we imported it in SJS and it changes the 10% to the value of 0.1 which expected.

    1.2 We created an SJS sheet with cell A1 that has a value of 10%(percentage format) we export the sheet as JSON and imported it again the using fromJSON method and worked fine the value is correct.


    2. are You are using the setDataSOurce method to eject data? if that is the case could you provide dummy data so that you are using it inside the setDataSource method?

    Regards,
    Avinash
  • Replied 1 November 2021, 10:17 pm EST

    Hi Avinash,

    Thanks for getting back to me,

    I can confirm we are using fromJSON to inject the data
    The data coming from our service is defined as:

    We then bind this "contents" key into a SSJSON schema which means inside his own sheet key, we then add the sheet with the addSheet method and run the fromJSON method on top of the newer added sheet.

    As you might see the numbers are not just 10%, we are talking about examples like 1,200, (1,200).


    {"contents":{"0":{"0":{"value":""},"1":{"value":""},"2":{"value":"Three Month"},"3":{"value":"s Ended"}},"1":{"0":{"value":""},"1":{"value":""},"2":{"value":"December 27, 2019"},"3":{"value":"December 28, 2018"}},"10":{"0":{"value":"Less: Net income (loss) attributable to interest"},"1":{"value":""},"2":{"value":"122"},"3":{"value":"(6)"}},"11":{"0":{"value":"Comprehensive income attributable to stockholders"},"1":{"value":"$"},"2":{"value":"166,961$"},"3":{"value":"208,909"}},"2":{"0":{"value":"Net income"},"1":{"value":"$"},"2":{"value":"145,883$"},"3":{"value":"250,676"}},"3":{"0":{"value":"Other comprehensive income (loss), net of tax"},"1":{"value":""},"2":{"value":""},"3":{"value":""}},"4":{"0":{"value":"Pension plan adjustments"},"1":{"value":""},"2":{"value":"(285)"},"3":{"value":"753"}},"5":{"0":{"value":"Foreign currency translation adjustments"},"1":{"value":""},"2":{"value":"14,585"},"3":{"value":"(18,007)"}},"6":{"0":{"value":"Fair value of cash flow hedges"},"1":{"value":""},"2":{"value":"6,753"},"3":{"value":"(24,239)"}},"7":{"0":{"value":"Share of equity comprehensive income (loss)"},"1":{"value":""},"2":{"value":"147"},"3":{"value":"(280)"}},"8":{"0":{"value":"Other comprehensive income (loss), net of tax"},"1":{"value":""},"2":{"value":"21,200"},"3":{"value":"(41,773)"}},"9":{"0":{"value":"Comprehensive income"},"1":{"value":""},"2":{"value":"167,083"},"3":{"value":"208,903"}}}}


    Thanks for your support
  • Replied 2 November 2021, 9:39 pm EST

    Hi Davide,

    Thanks for the information, so if I understand correctly you are embedding the data (returning from the data service) in JSON directly and then you are using fromJSON method to load that data.
    If That is the case then you need to execute edit cell command with autoformatter for each cell. Please refer to the following code snippet and attached sample that demonstrates the same,

    let formatData = (sheet, range) => {
    sheet.suspendPaint();
    sheet.suspendCalcService();
    for (let row = range.row; row < range.row + range.rowCount; row++) {
    for (let col = range.col; col < range.col + range.colCount; col++) {
    spread.commandManager().execute({
    cmd: "editCell",
    sheetName: sheet.name(),
    row,
    col,
    newValue: sheet.getValue(row, col),
    autoFormat: true
    });
    }
    }
    sheet.resumeCalcService();
    sheet.resumePaint();
    };

    formatData(spread.getActiveSheet(), {
    row: 0,
    col: 0,
    rowCount: 20,
    colCount: 10
    });



    sample:https://codesandbox.io/s/green-paper-birik?file=/src/index.js:3315-3635

    Regards
    Avinash
  • Replied 8 November 2021, 11:58 pm EST

    Thanks Avinash,

    The workaround works however it's not an efficient way to solve original request so I'm requesting the feature to have a configuration key at the WorkBook/WorkSheet level which provides the developer to enable/disable the auto format functionality.

    I think it's a sane feature as having to loop through the entire WorkBook/WorkSheet to then fire an edit cell event is quite expensive and definitely not a clean solution.

    Regards,
    Davide
  • Replied 9 November 2021, 8:00 pm EST

    Hi David,

    We have made an enahcement request on your behalf. We will update you regarding this as we get any information from the team.

    Regards
    Avinash
Need extra support?

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

Learn More

Forum Channels