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
Hi Caroline, which CurrencyFormat are you using? Does it happen with US too?
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
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.
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.
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
(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
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>
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>
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).
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?
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>
Tested with LibreOffice and MS Excel. Confirmed the problem and the patch fixes it.
(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.
Thanks!
Pushed to master for 22.05, thanks to everybody involved [U+1F984]
Pushed to 21.11.x for 21.11.05