1. Anuncie Aqui ! Entre em contato fdantas@4each.com.br

How to export samle file excel with dropdown list in Angular

Discussão em 'Angular' iniciado por Hiếu Lưu Trọng, Outubro 31, 2024 às 08:23.

  1. 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...

Compartilhe esta Página