js-xlsx-validation.js

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'});

but the file gets corrupt if the dropdown options are more than 14.

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'

@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.