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

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