I am trying to create an Excel Workbook using excel4node in Node JS
const workbook = new excel.Workbook();
const sheet = workbook.addWorksheet();
sheet.cell(1, 1).string("title");
sheet.cell(1, 2).string("description");
sheet.cell(1, 5).string("option1");
sheet.cell(1, 6).string("option2");
["E", "F"].forEach((col) => {
sheet.addDataValidation({
type: "list",
allowBlank: true,
prompt: "Choose from dropdown",
error: "Invalid choice was chosen",
showDropDown: true,
sqref: `${col}2:${col}2000`,
formulas: [
"P20 - Attend workshops ABCs, and Lessons learned sessions",
"P40 - Facilitate workshops, ABC, and lessons learned sessions",
],
});
});
return workbook
The problem is when I download and open the Excel file instead of 2 options in Row E3 I see multiple options like
P20 - Attend workshops ABCs
and Lessons learned sessions
P40 - Facilitate workshops
ABC
and lessons learned sessions
I read online and it seems one solution is to use Named Range. However, I could not figure out how to use Named Range in excel4Node or if there is any other solution that I can try