Infragistics｜Ultimate UI IgniteUI｜使用 Angular 讀取 Excel 文件、寫入數據和保存的範例

14 次瀏覽

#WPF

2023-02-14 更新

基於 Angular 14.1.x 版本的 Ignite UI 的信息。

此為在模板讀取Excel文件的範例，範例提及如何寫出必要數據, 並將其保存在
Excel文件中。

Excel file for template

Image after writing data

1.app.module.ts

導入 IgxExcel 模塊。

 app.module.ts
...
import { IgxExcelModule } from 'igniteui-angular-excel';

@NgModule({
  ...
  imports: [
    BrowserModule,
    BrowserAnimationsModule,
    IgxExcelModule,
  ],
  ...
})
...

2.app.component.html

用於選擇文件的對話框帶有輸入（type=“file”）。

<input type="file" #fileinput="" accept="application/vnd.ms-excel, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" (change)="changeFile($event, fileInput.files)">

3. app.component.ts

加載 Excel 文件，寫入數據，並保存在輸入更改事件（type=”file”）的事件處理程序 changeFile() 中。

// app.component.ts
import { ExcelUtility } from "./ExcelUtility";

@Component({
  selector: 'app-root',
  templateUrl: './app.component.html',
  styleUrls: ['./app.component.scss']
})

export class AppComponent {
  title = 'kb3886-app1';

  public gridData: any[];

  constructor() {
  }

  ngOnInit(): void {
    this.gridData = [];
    for(let i = 0; i < 5; i++){
      this.gridData.push({ID: i, TaskName: "Task " + i, Assignee: "負責人" + i, Progress: Math.floor(Math.random () * 11) * 10 });
    }
  }

  public async changeFile(event, files: any): Promise {
    // Workbook 讀取
    let workbook = await ExcelUtility.load(files[0]);

    // gridData 載入
    let rowOffset = 5;
    let cellOffset = 1;
    let keys = Object.keys(this.gridData[0]);
    for(let i = 0; i < this.gridData.length; i++ ){
      for(let j = 0; j < keys.length; j++){
        workbook.worksheets(0).rows(i + rowOffset).cells(j + cellOffset).value = this.gridData[i][keys[j]];
      }
    }

    // Workbook 存檔
    ExcelUtility.save(workbook, "TaskProgress");
  }
}

4. ExcelUtility.ts

收集用於處理 Excel 文件的 Method 實用程式類。可以從 Excel 實用程式複製使用。

// ExcelUtility.ts
import { saveAs } from "file-saver"; // npm package: "file-saver": "^1.3.8"
import { Workbook } from 'igniteui-angular-excel';
import { WorkbookFormat } from 'igniteui-angular-excel';
import { WorkbookSaveOptions } from 'igniteui-angular-excel';

export class ExcelUtility {
    public static getExtension(format: WorkbookFormat) {
        switch (format) {
            case WorkbookFormat.StrictOpenXml:
            case WorkbookFormat.Excel2007:
                return ".xlsx";
            case WorkbookFormat.Excel2007MacroEnabled:
                return ".xlsm";
            case WorkbookFormat.Excel2007MacroEnabledTemplate:
                return ".xltm";
            case WorkbookFormat.Excel2007Template:
                return ".xltx";
            case WorkbookFormat.Excel97To2003:
                return ".xls";
            case WorkbookFormat.Excel97To2003Template:
                return ".xlt";
        }
    }

    public static load(file: File): Promise {
        return new Promise((resolve, reject) => {
            ExcelUtility.readFileAsUint8Array(file).then((a) => {
                Workbook.load(a, (w) => {
                    resolve(w);
                }, (e) => {
                    reject(e);
                });
            }, (e) => {
                reject(e);
            });
        });
    }

    public static loadFromUrl(url: string): Promise {
        return new Promise((resolve, reject) => {
            const req = new XMLHttpRequest();
            req.open("GET", url, true);
            req.responseType = "arraybuffer";
            req.onload = (d) => {
                const data = new Uint8Array(req.response);
                Workbook.load(data, (w) => {
                    resolve(w);
                }, (e) => {
                    reject(e);
                });
            };
            req.send();
        });
    }

    public static save(workbook: Workbook, fileNameWithoutExtension: string): Promise {
        return new Promise((resolve, reject) => {
            const opt = new WorkbookSaveOptions();
            opt.type = "blob";

            workbook.save(opt, (d) => {
                const fileExt = ExcelUtility.getExtension(workbook.currentFormat);
                const fileName = fileNameWithoutExtension + fileExt;
                saveAs(d as Blob, fileName);
                resolve(fileName);
            }, (e) => {
                reject(e);
            });
        });
    }

    private static readFileAsUint8Array(file: File): Promise {
        return new Promise((resolve, reject) => {
            const fr = new FileReader();
            fr.onerror = (e) => {
                reject(fr.error);
            };

            if (fr.readAsBinaryString) {
                fr.onload = (e) => {
                    const rs = (fr as any).resultString;
                    const str: string = rs != null ? rs : fr.result;
                    const result = new Uint8Array(str.length);
                    for (let i = 0; i < str.length; i++) {
                        result[i] = str.charCodeAt(i);
                    }
                    resolve(result);
                };
                fr.readAsBinaryString(file);
            } else {
                fr.onload = (e) => {
                    resolve(new Uint8Array(fr.result as ArrayBuffer));
                };
                fr.readAsArrayBuffer(file);
            }
        });
    }
}

範例

下載 kb3886-app2

當您運行它並使用文件選擇按鈕選擇包含的 TemplateBook1.xlsx 時，gridData 的數據將保存並下載為 TaskProgress.xlsx。

參考文件