How to export samle file excel with dropdown list in Angular

import { Directive, Input, HostListener } from '@angular/core';
import * as XLSX from 'xlsx';

@Directive({
selector: '[appExcelExport]'
})
export class ExcelExportDirective {
@Input() columnHeaders: string[] = [];
@Input() rowData: any[] = [];
@Input() dropdownOptions: { [key: string]: string[] } = {};

@HostListener('click')
exportToExcel(): void {
if (!this.columnHeaders.length || !this.rowData.length) {
console.warn('Không có dữ liệu để xuất!');
return;
}

const workbook: XLSX.WorkBook = XLSX.utils.book_new();
const worksheet: XLSX.WorkSheet = XLSX.utils.aoa_to_sheet([this.columnHeaders]);

// Thêm dữ liệu vào worksheet
this.rowData.forEach(row => {
const rowData = this.columnHeaders.map(col => row[col] || '');
XLSX.utils.sheet_add_aoa(worksheet, [rowData], { origin: -1 });
});

// Thêm dropdown cho các cột cần thiết
this.addDropdownValidation(worksheet);

// Thêm worksheet vào workbook
XLSX.utils.book_append_sheet(workbook, worksheet, 'Sheet1');

// Xuất file
XLSX.writeFile(workbook, 'SampleData.xlsx');
}

private addDropdownValidation(worksheet: XLSX.WorkSheet): void {
// Thêm các giá trị dropdown vào sheet tạm
const dropdownStartRow = 102; // Bắt đầu từ dòng 102
let dropdownRange: { [key: string]: string } = {};

for (const col of this.columnHeaders) {
if (this.dropdownOptions[col]) {
dropdownRange[col] = `${this.getColLetter(this.columnHeaders.indexOf(col))}${dropdownStartRow}:${this.getColLetter(this.columnHeaders.indexOf(col))}${dropdownStartRow + this.dropdownOptions[col].length - 1}`;

// Thêm giá trị vào worksheet
this.dropdownOptions[col].forEach((option, index) => {
worksheet[`${this.getColLetter(this.columnHeaders.indexOf(col))}${dropdownStartRow + index}`] = { t: 's', v: option };
});
}
}

// Áp dụng quy tắc xác thực cho các ô trong cột
this.columnHeaders.forEach((header, index) => {
if (this.dropdownOptions[header]) {
const colLetter = this.getColLetter(index);
const range = `${colLetter}2:${colLetter}101`; // Tạo dropdown cho 100 dòng
worksheet[`!dataValidation`] = worksheet[`!dataValidation`] || [];
worksheet[`!dataValidation`].push({
ref: range,
type: 'list',
allowBlank: true,
formula1: `"${this.getColLetter(this.columnHeaders.indexOf(header))}${dropdownStartRow}:${this.getColLetter(this.columnHeaders.indexOf(header))}${dropdownStartRow + this.dropdownOptions[header].length - 1}"`,
showErrorMessage: true,
errorTitle: 'Invalid Selection',
error: 'Please select a value from the dropdown.'
});
}
});
}

private getColLetter(colIndex: number): string {
let letter = '';
let tempIndex = colIndex;
while (tempIndex >= 0) {
letter = String.fromCharCode((tempIndex % 26) + 65) + letter;
tempIndex = Math.floor(tempIndex / 26) - 1;
}
return letter;
}
}



Html:

<button appExcelExport [columnHeaders]="columnHeaders" [rowData]="rowData" [dropdownOptions]="dropdownOptions" class="px-2 buttonclick"
style="background-color: #1f5ecf">
<mat-icon class="iconButton" fontIcon="download"></mat-icon>
<span class="fs-14 text-white mb-0">Tải File Mẫu</span>
</button>




components.ts:

columnHeaders = ['eid', 'ename', 'esal', 'status'];
rowData = [
{ eid: 'e101', ename: 'ravi', esal: '', status: '' },
{ eid: 'e102', ename: 'ram', esal: '', status: '' },
{ eid: 'e103', ename: 'rajesh', esal: '', status: '' },
// Thêm 97 dòng trống
].concat(Array.from({ length: 97 }, (_, i) => ({
eid: '',
ename: '',
esal: '',
status: '',
})));

dropdownOptions = {
esal: ['val', 'val1'], // Các giá trị dropdown cho cột esal
status: ['Active', 'Inactive', 'Pending'] // Các giá trị dropdown cho cột status
};


I want to create a sample file for users to enter and import the file, but my file has fields that need to use select with "id", so when the user clicks to download the sample file, I will build a file with dropdown select with values from my database, but when downloading the file, the selects to choose from in the excel file will not be displayed.

Continue reading...