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

Can you be so kind to post a working (browser only write) example??

I tried your function as well as manually a setting XML to ws['!dataValidation'] ,
ws['!dataValidation'] = "<dataValidation prompt=...>...etc.</dataValidation>"
Ie:

var o = '<dataValidations>';
    o += '<dataValidation type="list" allowBlank="1" sqref="H1">';
    o += '<formula1>=$A$1:$A$4</formula1>';
    o += '</dataValidation>';
o += '</dataValidations>';

...and it doesn't seeem to work. I can't find the dropdowns in Excel 2013/16 nor any tag reference in the .XML files in the xlsx zip package. I even tried pasting back the working dataValidation XML created with Excel.exe without success.

tried also different combos:

<formula1>&quot;=$A$1:$A$4&quot;</formula1>
<formula1>&quot;$A$1:$A$4&quot;</formula1>

@dbgtx the gist only works with a list of values, but I think you can try examining an XLS XML and modify the code.

this is an example:

 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']
}

buff = xlsx.write(wb, {type: 'buffer'});

Thanks! Tried it and it works. Some notes that may not seem so obvious to all who stumble on this solution

1. function write_ws_xml() is a pre-existing in xlsx.js. Mohamed's if(ws['!dataValidation'])... condition must be pasted in it towards the end
2. I initially got an error and had to replace " with ". the single quote marks at the end should do the trick
3. The only difference between Excel.exe's generated XML and this function's is the initial count variable:
   <dataValidations count="2">
   Apparently the drop-downs work without it, but you can easily add validations.length to it in the function

So why is the below commented?
// if(ws['!merges'] != null && ws['!merges'].length > 0)

@ dbgtx

1. Yes, this is a modification to the function.
2. I am not sure, but I think it is safer to encode the double quotes, plus this was the format I have got from examining an XLS file generated by Libre Office.
3. I am not very familiar with the different specifications of the XLSX format, so I tried to use the most generic format I can find which is XLSX 2003.

My bad, the line should not be commented I was trying to indicate where the new function call should be inserted.

@magnifi
Thank you for this data validation, this help me a lot. Do you also know how to add date validation (e.g. valid date)? I tried some solution but I cannot get it work. Also I found out that if the values array has so many elements like 500 element, the generated xlsx has no data or maybe some special characters like & is not allowed?

@magnifi, Is there any limit on how many dropdown options can be added? I am using this to add dynamic dropdown options in generated excel but the file gets corrupt if the dropdown options are more than 14.

thank you very much

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.