Skip to content

Instantly share code, notes, and snippets.

@magnifi
Last active July 25, 2024 07:46
Show Gist options
  • Save magnifi/e020425cf2711ea24d3bec2b782b1e68 to your computer and use it in GitHub Desktop.
Save magnifi/e020425cf2711ea24d3bec2b782b1e68 to your computer and use it in GitHub Desktop.
function write_ws_xml_datavalidation(validations) {
var o = '<dataValidations>';
for(var i=0; i < validations.length; i++) {
var validation = validations[i];
o += '<dataValidation type="list" allowBlank="1" sqref="' + validation.sqref + '">';
o += '<formula1>&quot;' + validation.values + '&quot;</formula1>';
o += '</dataValidation>';
}
o += '</dataValidations>';
return o;
}
// modify the function in xlsx.js
function write_ws_xml(idx, opts, wb, rels) {
// ...
if(ws['!merges'] != null && ws['!merges'].length > 0) o[o.length] = (write_ws_xml_merges(ws['!merges']));
if(ws['!dataValidation']) o[o.length] = write_ws_xml_datavalidation(ws['!dataValidation']);
// ...
}
/*
* ==================== example ====================
*/
var wb = {
Sheets: {
Sheet1: {
'$ref': 'A1:Z99',
'!dataValidation': [
{sqref: 'A1:A99', values: ['foo', 'bar', 'baz']},
{sqref: 'B1:B99', values: ['Africa', 'Asia', 'Europe', 'America', 'Australia']},
]
}
},
SheetNames: ['Sheet1']
}
var buff = xlsx.write(wb, {type: 'buffer'});
@ToxicTree
Copy link

In case someone else stumble upon this modification, keep in mind that if you import xlsx as a module, you need to update xlsx.mjs instead of xlsx.js.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment