// Set up data validation for the first dropdown
const categoryCell = worksheet.getCell('A1');
categoryCell.dataValidation = {
type: 'list',
formula1: categories.join(','),
allowBlank: true,
showErrorMessage: true,
errorTitle: 'Invalid category',
error: 'Please select a valid category from the list.'
};
// For the second dropdown, set up data validation with dynamic range
worksheet.getCell('B1').dataValidation = {
type: 'list',
allowBlank: true,
showErrorMessage: true,
errorTitle: 'Invalid selection',
error: 'Please select a valid item from the list.',
formula1: =INDIRECT(IF($A$1="Fruit", "C2:C4", "D2:D4"))
};
// Populate options for fruits and vegetables
worksheet.getCell('C1').value = "Fruits";
fruits.forEach((fruit, index) => {
worksheet.getCell(C${index + 2}).value = fruit;
});
const ExcelJS = require('exceljs');
const workbook = new ExcelJS.Workbook(); const worksheet = workbook.addWorksheet('Sheet1');
const categories = ['Fruit', 'Vegetables']; const fruits = ['Apple', 'Banana', 'Orange']; const vegetables = ['Carrot', 'Broccoli', 'Lettuce'];
// Set up data validation for the first dropdown const categoryCell = worksheet.getCell('A1'); categoryCell.dataValidation = { type: 'list', formula1: categories.join(','), allowBlank: true, showErrorMessage: true, errorTitle: 'Invalid category', error: 'Please select a valid category from the list.' };
// For the second dropdown, set up data validation with dynamic range worksheet.getCell('B1').dataValidation = { type: 'list', allowBlank: true, showErrorMessage: true, errorTitle: 'Invalid selection', error: 'Please select a valid item from the list.', formula1:
=INDIRECT(IF($A$1="Fruit", "C2:C4", "D2:D4"))
};// Populate options for fruits and vegetables worksheet.getCell('C1').value = "Fruits"; fruits.forEach((fruit, index) => { worksheet.getCell(
C${index + 2}
).value = fruit; });worksheet.getCell('D1').value = "Vegetables"; vegetables.forEach((vegetable, index) => { worksheet.getCell(
D${index + 2}
).value = vegetable; });workbook.xlsx.writeFile('conditional_dropdown.xlsx') .then(() => { console.log('Workbook created successfully!'); }) .catch((error) => { console.error('Error:', error); });
please help me thanks