From e720825703ef467532ab6e0ed9eb86f31b0a2a60 Mon Sep 17 00:00:00 2001 From: David Cook Date: Mon, 28 Jul 2025 03:45:24 +0000 Subject: [PATCH] [22.11] Bug 40525: Update spreadsheet format to escape formulas This change updates the spreadsheet format to escape formulas using the built-in function if using Buttons 3.2.0+ or using a workaround if not. Test plan: 0. Apply the patch 1. Go to http://localhost:8081/cgi-bin/koha/cataloguing/addbiblio.pl?biblionumber=29 2. Click on tab 1 3. For 100$a type in the following and save: =SUM(1+1) 4. Go to http://localhost:8081/cgi-bin/koha/circ/circulation.pl?findborrower=42 5. Checkout barcode 39999000001310 to the patron 6. Go to http://localhost:8080/cgi-bin/koha/opac-user.pl and login as that patron 7. Download the CSV for the summary table and open in Excel 8. Note that the "Author" is "'=SUM(1+1)" and not "=SUM(1+1)" or "2" Signed-off-by: Brendan Lawlor Signed-off-by: Marcel de Rooy --- .../opac-tmpl/bootstrap/js/datatables.js | 29 ++++++++++++++++++- 1 file changed, 28 insertions(+), 1 deletion(-) diff --git a/koha-tmpl/opac-tmpl/bootstrap/js/datatables.js b/koha-tmpl/opac-tmpl/bootstrap/js/datatables.js index 566ee9869bc..9f0c79708f6 100644 --- a/koha-tmpl/opac-tmpl/bootstrap/js/datatables.js +++ b/koha-tmpl/opac-tmpl/bootstrap/js/datatables.js @@ -237,6 +237,33 @@ $.fn.dataTable.ext.buttons.clearFilter = { } }; + var export_format_spreadsheet = { + body: function ( data, row, column, node ) { + var newnode = node.cloneNode(true); + var no_export_nodes = newnode.querySelectorAll(".no-export"); + no_export_nodes.forEach(child => { + child.parentNode.removeChild(child); + }); + //Note: innerHTML is the same thing as the data variable, + //minus the ".no-export" nodes that we've removed + //Note: See dataTables.buttons.js for original function usage + var str = DataTable.Buttons.stripData(newnode.innerHTML, { + decodeEntities: false, + stripHtml: true, + stripNewlines: true, + trim: true, + escapeExcelFormula: true, + }); + //Note: escapeExcelFormula only works from Buttons 3.2.0+, so + //we add a workaround for now + var unsafeCharacters = /^[=+\-@\t\r]/; + if (unsafeCharacters.test(str)) { + str = "'" + str; + } + return str; + }, + }; + var export_buttons = [ { extend: 'excelHtml5', @@ -251,7 +278,7 @@ $.fn.dataTable.ext.buttons.clearFilter = { text: __("CSV"), exportOptions: { columns: exportColumns, - format: export_format + format: export_format_spreadsheet }, }, { -- 2.39.5