Bug 22038

Summary:	When exporting account table to excel, decimal is lost
Product:	Koha	Reporter:	Caroline Cyr La Rose <caroline.cyr-la-rose>
Component:	I18N/L10N	Assignee:	Didier Gautheron <didier.gautheron>
Status:	CLOSED FIXED	QA Contact:	Tomás Cohen Arazi (tcohen) <tomascohen>
Severity:	enhancement
Priority:	P5 - low	CC:	didier.gautheron, f.demians, florian.bontemps, fridolin.somers, kyle, oleonard, tomascohen
Version:	Main
Hardware:	All
OS:	All
Change sponsored?:	---	Patch complexity:	---
Documentation contact:		Documentation submission:
Text to go in the release notes:		Version(s) released in:	22.05.00,21.11.05
Circulation function:
Attachments:	Bug 22038: Disable excel export if CurrencyFormat is FR Bug 22038: Fix excel export if CurrencyFormat is FR Bug 22038: Fix excel export if CurrencyFormat is FR Bug 22038: Fix excel export if CurrencyFormat is FR Bug 22038: Fix excel export if CurrencyFormat is FR

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 (tcohen) 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 (tcohen) 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 (tcohen) 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 ﷐[U+1F984]﷑

Comment 16 Kyle M Hall (khall) 2022-04-08 14:39:44 UTC

Pushed to 21.11.x for 21.11.05