Bug 22038 - When exporting account table to excel, decimal is lost
Summary: When exporting account table to excel, decimal is lost
Status: Pushed to stable
Alias: None
Product: Koha
Classification: Unclassified
Component: I18N/L10N (show other bugs)
Version: master
Hardware: All All
: P5 - low enhancement (vote)
Assignee: Didier Gautheron
QA Contact: Tomás Cohen Arazi
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2018-12-20 23:04 UTC by Caroline Cyr La Rose
Modified: 2022-04-08 14:39 UTC (History)
8 users (show)

See Also:
Change sponsored?: ---
Patch complexity: ---
Text to go in the release notes:
Version(s) released in:
22.05.00,21.11.05


Attachments
Bug 22038: Disable excel export if CurrencyFormat is FR (2.17 KB, patch)
2020-12-11 11:17 UTC, Didier Gautheron
Details | Diff | Splinter Review
Bug 22038: Fix excel export if CurrencyFormat is FR (2.99 KB, patch)
2020-12-14 11:24 UTC, Didier Gautheron
Details | Diff | Splinter Review
Bug 22038: Fix excel export if CurrencyFormat is FR (2.99 KB, patch)
2021-06-23 18:41 UTC, Hakam Almotlak
Details | Diff | Splinter Review
Bug 22038: Fix excel export if CurrencyFormat is FR (3.05 KB, patch)
2021-12-17 14:39 UTC, Florian
Details | Diff | Splinter Review
Bug 22038: Fix excel export if CurrencyFormat is FR (3.11 KB, patch)
2022-03-28 09:41 UTC, Tomás Cohen Arazi
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description Caroline Cyr La Rose 2018-12-20 23:04:59 UTC
When you want to export a patron's account lines in Excel, the decimals are lost. So instead of having a fine of 3,75, the Excel file says the fine is 375.

Note that I opened the Excel file in LibreOffice Calc. I'm not sure if that affects anything. Although I also opened the file in vim and got the same result: <c r="D2" s="63"><v>375</v></c><c r="E2" s="63"><v>375</v></c>

To reproduce:
1) Find a patron with small fines (my patron had 3,75) or create some manual charges
2) In the patrons file, go to the Fines tab and then the Account tab
3) At the top of the account table, click on Excel
4) Open the file
5) Notice the fines are now multiplied by 100
Comment 1 Katrin Fischer 2018-12-20 23:33:38 UTC
Hi Caroline, which CurrencyFormat are you using? Does it happen with US too?
Comment 2 Didier Gautheron 2020-12-10 13:51:20 UTC
Hi

It's a known issue with datatables.js library.
cf all regular expression in koha-tmpl/intranet-tmpl/lib/datatables/datatables.js

The easiest workaround would be: 
disable excel export if not using US CurrencyFormat.

Regards
Didier
Comment 3 Didier Gautheron 2020-12-11 11:17:36 UTC
Created attachment 114335 [details] [review]
Bug 22038: Disable excel export if CurrencyFormat is FR

DataTables excel export is broken if number decimal separator is a comma.

Test plan:
1 - Set syspref CurrencyFormat to US
2 - Export as excel a table with decimal numbers, patrons list with fines
for example.
3 - Open in libreoffice or excel, numbers are ok.
4 - Set syspref CurrencyFormat to FR
5 - Export and open again, number are wrong 25,10 is imported as 2510
6 - Apply patch
7 - Redo 1 to 4
8 - Excel export is not available with FR.
Comment 4 Katrin Fischer 2020-12-12 10:32:05 UTC
I don't feel like taking away the whole export functionality is a good solution. Especially if done for all tables and even those not containing monetary values. We just promoted the functionality to our libraries after the last update.
Comment 5 Didier Gautheron 2020-12-14 11:24:09 UTC
Created attachment 114376 [details] [review]
Bug 22038: Fix excel export if CurrencyFormat is FR

DataTables excel export is broken if number decimal separator is a comma.

Test plan:
1 - Set syspref CurrencyFormat to US
2 - Export as excel a table with decimal numbers, patrons list with fines
for example.
3 - Open in libreoffice or excel, numbers are ok.
4 - Set syspref CurrencyFormat to FR
5 - Export and open again, number are wrong 25,10 is imported as 2510
6 - Apply patch
7 - Redo 1 to 4
8 - Excel export, number is 25,10
Comment 6 Didier Gautheron 2020-12-14 11:29:06 UTC
(In reply to Katrin Fischer from comment #4)
> I don't feel like taking away the whole export functionality is a good
> solution. Especially if done for all tables and even those not containing
> monetary values. We just promoted the functionality to our libraries after
> the last update.

Ok, replace , with . in excel export if syspref CurrencyFormat is FR
Comment 7 Hakam Almotlak 2021-06-23 18:41:35 UTC
Created attachment 122350 [details] [review]
Bug 22038: Fix excel export if CurrencyFormat is FR

DataTables excel export is broken if number decimal separator is a comma.

Test plan:
1 - Set syspref CurrencyFormat to US
2 - Export as excel a table with decimal numbers, patrons list with fines
for example.
3 - Open in libreoffice or excel, numbers are ok.
4 - Set syspref CurrencyFormat to FR
5 - Export and open again, number are wrong 25,10 is imported as 2510
6 - Apply patch
7 - Redo 1 to 4
8 - Excel export, number is 25,10

Signed-off-by: hakam <hakam@inlibro.com>
Comment 8 Florian 2021-12-17 14:39:37 UTC
Created attachment 128674 [details] [review]
Bug 22038: Fix excel export if CurrencyFormat is FR

DataTables excel export is broken if number decimal separator is a comma.

Test plan:
1 - Set syspref CurrencyFormat to US
2 - Export as excel a table with decimal numbers, patrons list with fines
for example.
3 - Open in libreoffice or excel, numbers are ok.
4 - Set syspref CurrencyFormat to FR
5 - Export and open again, number are wrong 25,10 is imported as 2510
6 - Apply patch
7 - Redo 1 to 4
8 - Excel export, number is 25,10

Signed-off-by: hakam <hakam@inlibro.com>
Signed-off-by: Florian Bontemps <florian.bontemps@biblibre.com>
Comment 9 Florian 2021-12-17 14:51:25 UTC
The patch fixed the problem (and also worked for CH currency btw), but it did introduce a weird but harmless glitch : for FR and CH currency, any sum higher than 999.99 is written from the left side of their cell rather than the usual right.

For reference, I used LibreOffice calculator on Linux(ubuntu).
Comment 10 Katrin Fischer 2022-02-13 13:15:16 UTC
I am testing this with LibreOffice, but all amounts export with 00.00 for FR for me :( Not sure if this is because I am running my laptop with English default language or because LibreOffice is not Excel... could someone else give this a go?
Comment 11 Tomás Cohen Arazi 2022-03-28 09:41:08 UTC
Created attachment 132301 [details] [review]
Bug 22038: Fix excel export if CurrencyFormat is FR

DataTables excel export is broken if number decimal separator is a comma.

Test plan:
1 - Set syspref CurrencyFormat to US
2 - Export as excel a table with decimal numbers, patrons list with fines
for example.
3 - Open in libreoffice or excel, numbers are ok.
4 - Set syspref CurrencyFormat to FR
5 - Export and open again, number are wrong 25,10 is imported as 2510
6 - Apply patch
7 - Redo 1 to 4
8 - Excel export, number is 25,10

Signed-off-by: hakam <hakam@inlibro.com>
Signed-off-by: Florian Bontemps <florian.bontemps@biblibre.com>
Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>
Comment 12 Tomás Cohen Arazi 2022-03-28 09:41:46 UTC
Tested with LibreOffice and MS Excel. Confirmed the problem and the patch fixes it.
Comment 13 Tomás Cohen Arazi 2022-03-28 09:44:17 UTC
(In reply to Katrin Fischer from comment #10)
> I am testing this with LibreOffice, but all amounts export with 00.00 for FR
> for me :( Not sure if this is because I am running my laptop with English
> default language or because LibreOffice is not Excel... could someone else
> give this a go?

I tried on LibreOffice, Excel and Numbers (the macOS counterpart). They all put zeroes when they detect is a numeric column. With and without this patch.
Comment 14 Katrin Fischer 2022-03-28 09:45:59 UTC
Thanks!
Comment 15 Fridolin Somers 2022-04-05 15:14:23 UTC
Pushed to master for 22.05, thanks to everybody involved 🦄
Comment 16 Kyle M Hall 2022-04-08 14:39:44 UTC
Pushed to 21.11.x for 21.11.05