-
-
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>"' + validation.values + '"</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'}); |
@magnifi thank you so much bro! You saved my life! Just in case someone still having this issue, I currently use xlsx and xlsx-style libraries together. So for your modification work, I had to modify the xlsx.js file on xlsx-style and on the dist folder too.
Tks!
Hi guys! I'm unable to implement this solution in "SheetJS" aka "xlsx". @magnifi I also try to implement the solution in one of your repositories but no success. I'm using React 18 with Typescript. I don't need any complex list, I'm allowing my user to download a template file to use in import and in that file I just want to show enum values in dropdown. Also, I want to validate numeric values, etc.
I cannot use exceljs as it is not maintained hence, React 18 does not support it. If anyone can help me figuring this out I would be greatful!
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.
It limitation for formulas(max length 255 chars). if you have huge list, you can pass values with definedName
wb.Workbook.Names = [ { Name: 'HUGE_LIST_VALUES', Comment: 'This is a sheet-scope reference', Ref: "SHEET_WITH_VALUES!$A$2:$A$27", } ]in write_ws_xml_datavalidation function need remove """
instead of
values: ['foo', 'bar', 'baz']use
values: 'HUGE_LIST_VALUES'