generating excel file with drop down menu menu while using of `xlsx-js-style` library

Question

Im using of xlsx-js-style library for generating excel file, I can export my excel while type of columns are string or number.

but I wanna to use of dropdown option as a one cell in excel. same as below screenshot. is there any way for doing?

Answer 1

Here's the code that works, but you have to use a different library; namely, exceljs:

import * as excelJs from 'exceljs';

And then you can use the code below to generate dropdown categories for three different columns A, B, and C, for all cells between the indices 2 and 99,999 (plus I've included some other things like cell width, background color etc):

const generateTemplate = async () => {
        const workbook = new excelJs.Workbook();

        const ws = workbook.addWorksheet('Test Worksheet');

        const options1 = [myStore1.options.map((opt) => opt.name)];
        const options2 = [myStore2.options.map((opt) => opt.name)];
        const options3 = [myStore3.options.map((opt) => opt.name)];

        // Add data to the worksheet
        ws.addRow(['Name', 'Latitude', 'Parents', 'Address', 'Dog name']);

        ws.columns.map((col, index) => (col.width = 18));

        // @ts-ignore
        ws.dataValidations.add('A2:A99999', {
            type: 'list',
            allowBlank: false,
            formulae: [`"${options1.join(',')}"`],
        });

        // @ts-ignore
        ws.dataValidations.add('B2:B99999', {
            type: 'list',
            allowBlank: false,
            formulae: [`"${options2.join(',')}"`],
        });

        // @ts-ignore
        ws.dataValidations.add('C2:C99999', {
            type: 'list',
            allowBlank: false,
            formulae: [`"${options3.join(',')}"`],
        });

        ws.getRow(1).fill = {
            type: 'pattern',
            pattern: 'solid',
            fgColor: { argb: 'FFADD8E6' },
        };

        ws.eachRow((row) => {
            row.eachCell((cell) => {
                cell.font = {
                    name: 'Inter',
                    size: 8,
                };
                cell.alignment = {
                    horizontal: 'center',
                };
            });
        });

        const excelBlob = await workbook.xlsx.writeBuffer();
        const excelUrl = URL.createObjectURL(
            new Blob([excelBlob], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' })
        );

        const link = document.createElement('a');
        link.href = excelUrl;
        link.download = 'template.xlsx';
        document.body.appendChild(link);
        link.click();

        URL.revokeObjectURL(excelUrl);
        document.body.removeChild(link);
    };
    ```