Auto formatter on data injection

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

Please login to follow topic

davide.vago
- Post Options:
- Link
  Copy
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
avinash.pathak
- Post Options:
- Link
  Copy
Posted 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
pietro.paolini
- Post Options:
- Link
  Copy
Posted 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 ?
avinash.pathak
- Post Options:
- Link
  Copy
Posted 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
avinash.pathak
- Post Options:
- Link
  Copy
Posted 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
pietro.paolini
- Post Options:
- Link
  Copy
Posted 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
avinash.pathak
- Post Options:
- Link
  Copy
Posted 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
davide.vago
- Post Options:
- Link
  Copy
Posted 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

davide.vago

Post Options:
Link
Copy

Posted 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, b[/b].


{"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

avinash.pathak
- Post Options:
- Link
  Copy
Posted 2 November 2021, 4:55 am EST
Hi Davide,

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

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.

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

avinash.pathak

Post Options:
Link
Copy

Posted 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

davide.vago
- Post Options:
- Link
  Copy
Posted 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
avinash.pathak
- Post Options:
- Link
  Copy
Posted 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
avinash.pathak
- Post Options:
- Link
  Copy
Posted 19 December 2021, 4:54 pm EST
Hi Davide,

The Devs informed us that we have an internal API called getPreferredDisplayForamtter on GeneralFormatter, it is not minified, Please use this code and let us know if you face any issues.

var formatter = new GC.Spread.Formatter.GeneralFormatter(); var preferredFormatter = formatter.getPreferredDisplayFormatter('100%') var formatStr = preferredFormatter.formatString();

Regards,

Avinash

Please login to reply to thread

Need extra support?

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

Learn More

Forum Channels

ComponentOne

Forums for all current editions of the ComponentOne .NET UI control product line, including ComponentOne Studio and ComponentOne Studio for Xamarin.
ActiveReports

Forums for all versions of ActiveReports and ActiveReports Server
Spread

Forums for all current versions of Spread .NET spreadsheets, SpreadJS JavaScript spreadsheets, and SpreadCOM spreadsheets.
Wijmo

Forums for all Wijmo products, including Wijmo Core, FinancialChart, FlexSheet, MultiRow, OLAP, and ReportViewer
- General Discussion
Document Solutions

Forums for all Document Solutions products, including Document Solutions for PDF, Word, Excel (.NET and Java), and Imaging.