From c341d03f065d1f5816773f4227abbd99d9c23821 Mon Sep 17 00:00:00 2001 From: David Cook Date: Mon, 28 Jul 2025 03:45:24 +0000 Subject: [PATCH] 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" --- .../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 114dfcd22a..c391c8e1b3 100644 --- a/koha-tmpl/opac-tmpl/bootstrap/js/datatables.js +++ b/koha-tmpl/opac-tmpl/bootstrap/js/datatables.js @@ -428,6 +428,33 @@ function _dt_buttons(params) { }, }; + const export_format_spreadsheet = { + body: function (data, row, column, node) { + const newnode = node.cloneNode(true); + const 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 + let 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 + const unsafeCharacters = /^[=+\-@\t\r]/; + if (unsafeCharacters.test(str)) { + str = "'" + str; + } + return str; + }, + }; + var export_buttons = [ { extend: "excelHtml5", @@ -479,7 +506,7 @@ function _dt_buttons(params) { text: __("CSV"), exportOptions: { columns: exportColumns, - format: export_format, + format: export_format_spreadsheet, }, }, { -- 2.39.5