Format numbers when export as CSV file

Tags: #<Tag:0x00007f8b1dafaa28>
#1

Hi, in my app I have different number formats,I am able to display in my table the good format but when I export to CSV the format is always 1111.50 and in Europe we need 1111,50 so that when we open excel could be recognized as a number, because in Europe Excel number format is with a comma not a dot.Can we do that with handsontable?I tried to modify exportPlugin but with no succes

#2

Hey @bogdan.ceu

CSV is a file format that support RAW data. It does not allow to use

  • formatting
  • styling
  • all other metadata
#3

Ok but if just before exporting I want to change all numbers data in my exportPlugin to set them as a string with comma instead of a dot ,but not affecting my hotTable instance I can?

#4

Yes. That sounds doable. Do you export data on a button click?

#5

yes

#6

When I click the button I do this public exportAsFile(filename: string) {
let csvSeparator: string = ‘,’;
this.userService.getUser().pipe(
take(1)
).subscribe((user: User) => {
csvSeparator = user.decimalMask === ‘,’ ? ‘;’ : ‘,’;
this.exportPlugin.downloadFile(‘csv’, {
filename: filename,
columnHeaders: true,
rowHeaders: true,
columnDelimiter: csvSeparator
});
});
}

#7

So there is a methode to change exportPlugin data?

#8

So there is a methode to change exportPlugin data?

No. You’d need to change the real data. However, as exportPlugin works in a split of second user wouldn’t even notice.

  1. Clicking export button
  2. Replace data (with loadData or setDataAtCell)
  3. Export to file
  4. Get back the old data

I suggest creating a deep clone of the data (JSON.stringify()) (before step 2). This will allow you to use
instance.loadData(JSON.parse(your_stringified_data)). Which is much easier than change the data cell after cell.

#9

Ok i will try it like this.Thanks a lot!

#10

You’re welcome. Keep me updated :slight_smile:

#11

It’s working like this …deep copy data… change numbers to string…load copy of data in exportPlugin and then after export reload the good data :wink: Thanks

#12

great! :slight_smile: I keep my fingers crossed for further progress on the project

#13

Hi,
can you by any chance generate some jsfiddle?
I think it can be usefull for many European users :wink:
Thanks in advance

#14

@adrian.wawrzyn

Step 1
let myDeepClone = JSON.stringify(hot.getData())
where hot is your instance.

Steps 2
hot.updateSettings({type: 'text'})

Step 3
Run the export plugin like you did before.

Steps 4
hot.loadData(JSON.parse(myDeepClone))

1 Like
#15