how to set validation on a column

[swatantra1]

i want to generate an excel for students where i have a section gender and i want the column to have validation as list = ["male", "female", "other"]
how can i do so

[Siemienik]

https://github.com/exceljs/exceljs#data-validations

ws.getColumn("A").eachCell({ includeEmpty: true }, function(cell, rowNumber) {
  cell.dataValidation = {
    type: 'list',
    allowBlank: true,
    formulae: ['"male,female,other"']
  };
});

[bhavan0]

https://github.com/exceljs/exceljs#data-validations

ws.getColumn("A").eachCell({ includeEmpty: true }, function(cell, rowNumber) {
  cell.dataValidation = {
    type: 'list',
    allowBlank: true,
    formulae: ['"male,female,other"']
  };
});

Hi @Siemienik ,

using the above code the validations are only getting added to the column cells which have data during download.

How do we add the validation to the entire column when there is data entered or not?

[Siemienik]

#1184 may be connected with

[Siemienik]

@bhavan0 may you confirm is it working properly?

ws.dataValidations.add("X2:X9999",{
    type: 'list',
    allowBlank: true,
    formulae: ['"male,female,other"']
  });

[bhavan0]

@bhavan0 may you confirm is it working properly?

ws.dataValidations.add("X2:X9999",{
    type: 'list',
    allowBlank: true,
    formulae: ['"male,female,other"']
  });

This works.
Thank You :)

[Siemienik]

👍

[maikknebel]

Is this still working for someone?
ws.dataValidations is shown as an unresolved variable to me. Calling ws.dataValidations.add(...) leads to TypeError: Cannot set property 'marked' of undefined

[jrbarnard]

Is this still working for someone?
ws.dataValidations is shown as an unresolved variable to me. Calling ws.dataValidations.add(...) leads to TypeError: Cannot set property 'marked' of undefined

Seeing the same error

[GowthamGottimukkala]

Is this still working for someone?
ws.dataValidations is shown as an unresolved variable to me. Calling ws.dataValidations.add(...) leads to TypeError: Cannot set property 'marked' of undefined

Same error. Someone solved this?

[GowthamGottimukkala]

#1027

[adnaanbheda]

@GowthamGottimukkala did you find a solution to the TypeError ?

[GowthamGottimukkala]

@adnaanbheda

Downgrading the version to 3.3.1 helped and didn't raise any error for the method told by @Siemienik.

If I'm not wrong, I checked the time taken by this method and it is almost equal to the following one. So I updated my version and currently using the following as a workaround.

let values = ["SerialNo"];
values.push(...Array.from(Array(9999).keys()));
worksheet.getColumn("O").values = values;
worksheet.getColumn("O").hidden = true;

worksheet.getColumn("A").eachCell(function(cell) {
      worksheet.getCell(cell.address).dataValidation = {
        type: 'list',
        allowBlank: true,
        formulae: ['"male,female,other"']
      };
}

Hope this helps

[adnaanbheda]

@GowthamGottimukkala

Yeah, this is what I ended up doing, still it would've been nice to avoid the for loop in code. Anyways thanks for the update!

[mariiaandreeva]

This works in version 4.2.0:

sheet.dataValidations.model['X2:X9999'] = {
  allowBlank: true,
  error: 'Please use the drop down to select a valid value',
  errorTitle: 'Invalid Selection',
  formulae: ['"Apples,Bananas,Oranges"'],
  showErrorMessage: true,
  type: 'list',
};

[Pushkraj-Space]

Thank you

[Siemienik]

I don't know why the dataValidations is commented in typings:

https://github.com/exceljs/exceljs/blob/e1de626/index.d.ts#L984

TS will throw an error if this field is used. Please try to use // @ts-ignore as a quick workaround.

Unfortunately, I cannot explore this that I would like, because I don't have enough time for volunteer working as earlier.
If you would like to allow me to spend more time on open source activities or order me some issue being done, write me hi@siemienik.pl.

#1500

EDIT: This line has been commented since types definitions were created 2b6a2e5#diff-7aa4473ede4abd9ec099e87fec67fd57afafaf39e05d493ab4533acc38547eb8R825

[garvitjoshi]

Hey, Can we pass the Dynamic dataset in formulae:

I am trying to call it but it is throwing an error while opening the excel:

let adb = Company_Code_Data.map((item) => {
var fullname = [item.Company_Code].join(" ");
return fullname;
});

            let Company_Code_list = '\'\"'+adb.toString()+'\"\'';

            console.log(Company_Code_list);

            newSheet.getColumn('Company_Code').eachCell((cell) => {
                cell.protection = {
                    locked: false
                }
                cell.dataValidation = {
                    type: 'list',
                    allowBlank: false,
                    operator: 'equal',
                    formulae: [adb.toString()],
                    showErrorMessage: true,
                    errorStyle: 'error',
                    errorTitle: 'Invalid value',
                    error: 'The value must be C&W, Pro Unlimited, Concur, AWS, Azure, Legal, Other'
                }
                cell.fill = {
                    type: "pattern",
                    pattern: "solid",
                    fgColor: {
                        argb: "FF85c8ff"
                    }
                }
            }); 

[DavidPx]

Another workaround would be to extend the Worksheet interface... put this into a .d.ts file:

import { DataValidation } from "exceljs";

declare module 'exceljs' {
    export interface DataValidations {
        add(range: string, validation: DataValidation): void;
        readonly model: Record<string, DataValidation>;
    }
    
    export interface Worksheet {
        dataValidations: DataValidations;
    }
}

I would really like to see #1682 pulled in!

I tried the workaround of adding a bunch of empty rows and then setting cell-level validation but it bloats the size of the worksheet (as downloaded). The range-style validation has very little effect upon the resultant file size.

[djolf]

I am using this answer, however, how do I remove the data validations? How does the add() function work? How can I implement a remove() function the same way?

[het4199]

I want to add a dropdown to a column, but the list array is coming from an API. The array has 185 items, I converted them into the format accepted by the library, but it is showing data till a particular limit. If the item length goes beyond a particular limit it does not show any data. Does anyone have a workaround for this issue? Can we increase the limit of the dropdown list?

[leonardonogueira07]

dynamic validation, it took a lot of work to solve it dynamically. Sharing in case anyone has the same problem.

worksheet.dataValidations.add("C2:C9999", {
type: "list",
allowBlank: true,
formulae: ['"' + arrayOptions + '"']
});

[Kiran-Oleti]

Brother i am having the same issue could you help me out
i am fetching the data from api dynamically and mapping those data into excel but i am not able to get dropdown options