Angular Using Workbooks

Infragistics Angular Excel Engine을 사용하면 Microsoft® Excel®에 데이터를 저장하고 로드할 수 있습니다. 라이브러리의 다양한 클래스를 사용하여 통합 문서와 워크시트를 만들고, 데이터를 입력하고, Excel로 데이터를 내보낼 수 있습니다. Infragistics Angular Excel Engine을 사용하면 애플리케이션의 데이터를 Excel 스프레드시트로 쉽게 내보내고 Excel에서 애플리케이션으로 데이터를 가져올 수 있습니다.

Angular Using Workbooks Example

EXAMPLE

DATA

MODULES

TS

HTML

SCSS

import { saveAs } from "file-saver";
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<Workbook> {
        return new Promise<Workbook>((resolve, reject) => {
            ExcelUtility.readFileAsUint8Array(file).then((a) => {
                Workbook.load(a, null, (w) => {
                    resolve(w);
                }, (e) => {
                    reject(e);
                });
            }, (e) => {
                reject(e);
            });
        });
    }

    public static loadFromUrl(url: string): Promise<Workbook> {
        return new Promise<Workbook>((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, null, (w) => {
                    resolve(w);
                }, (e) => {
                    reject(e);
                });
            };
            req.send();
        });
    }

    public static save(workbook: Workbook, fileNameWithoutExtension: string): Promise<string> {
        return new Promise<string>((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<Uint8Array> {
        return new Promise<Uint8Array>((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);
            }
        });
    }
}
tsコピー

import { NgModule } from "@angular/core";
import { FormsModule } from "@angular/forms";
import { CommonModule } from "@angular/common";
import { BrowserModule } from "@angular/platform-browser";
import { BrowserAnimationsModule } from "@angular/platform-browser/animations";
import { AppComponent } from "./app.component";
import { IgxButtonModule, IgxGridModule } from "igniteui-angular";
import { IgxExcelModule } from "igniteui-angular-excel";

@NgModule({
  bootstrap: [AppComponent],
  declarations: [
    AppComponent,

],
  imports: [
    BrowserModule,
    BrowserAnimationsModule,
    CommonModule,
    FormsModule,
    IgxButtonModule,
    IgxGridModule,
    IgxExcelModule
],
  providers: [],
  schemas: []
})
export class AppModule {}
tsコピー

import { Component, ComponentFactoryResolver, Injector, OnInit, ViewChild, ViewContainerRef } from "@angular/core";
import { IgxGridComponent } from "igniteui-angular";
import { TextFormatMode } from "igniteui-angular-excel";
import { Workbook } from "igniteui-angular-excel";
import { WorkbookFormat } from "igniteui-angular-excel";
import { WorksheetTable } from "igniteui-angular-excel";
import { ExcelUtility } from "./ExcelUtility";

@Component({
    standalone: false,
    selector: "app-root",
    styleUrls: ["./app.component.scss"],
    templateUrl: "./app.component.html"
})
export class AppComponent implements OnInit {

    @ViewChild("gridContainer", { read: ViewContainerRef, static: true })
    public gridContainerRef: ViewContainerRef;
    public canSave = false;
    public wb: Workbook;
    public worksheetTables: string[];
    public selectedTable: string;

    constructor(private resolver: ComponentFactoryResolver, private injector: Injector) {
    }

    public ngOnInit() {
        this.workbookCreate();
    }

    public workbookSave(): void {
        if (this.canSave) {
            // setting document properties to organize Excel files
            this.wb.documentProperties.author = "My Name";
            this.wb.documentProperties.company = "My Company";
            this.wb.documentProperties.title = "Employees and income";
            this.wb.documentProperties.status = "Completed";
            this.wb.documentProperties.category = "Financial";
            this.wb.documentProperties.keywords = "Financial;Company;Employees;income";

            // setting protection on workbook of Excel file
            this.wb.protection.allowEditStructure = true;
            this.wb.protection.allowEditWindows = true;

            this.wb.windowOptions.tabBarVisible = true;

            ExcelUtility.save(this.wb, "ExcelWorkbook").then((f) => {
                console.log("Saved:" + f);
            }, (e) => {
                console.error("ExcelUtility.Save Error:" + e);
            });
        }
    }

    public workbookLoad(input: HTMLInputElement): void {
        if (input.files == null || input.files.length === 0) {
            return;
        }
        console.log("Loaded:" + input.files[0].name);
        ExcelUtility.load(input.files[0]).then((w) => { this.workbookParse(w); },
            (e) => {
            console.error("ExcelUtility.Load Error:" + e);
        });
    }

    public workbookParse(wb: Workbook): void {
        if (wb === undefined) {
            this.worksheetTables = null;
            this.selectedTable = null;
        } else {
            const names = new Array<string>();
            for (const ws of wb.worksheets()) {
                for (const tbl of ws.tables()) {
                    names.push(ws.name + " - " + tbl.name);
                }
            }
            this.worksheetTables = names;
            this.selectedTable = names.length > 0 ? names[0] : null;
        }
        this.wb = wb;
        this.canSave = wb != null;
        this.onTableChange(this.selectedTable);
    }

    public workbookCreate(): void {
        const wb = new Workbook(WorkbookFormat.Excel2007);
        const employeeSheet = wb.worksheets().add("Employees");
        const employeeHeader = employeeSheet.rows(0);
        const companies = ["Amazon", "Ford", "Jaguar", "Tesla", "IBM", "Microsoft" ];
        const firstNames = ["Andrew", "Mike", "Martin", "Ann", "Victoria", "John", "Brian", "Jason", "David" ];
        const lastNames = ["Smith", "Jordan", "Johnson", "Anderson", "Louis", "Phillips", "Williams" ];
        const countries = ["UK", "France", "USA", "Germany", "Poland", "Brazil" ];
        const titles = ["Sales Rep.", "Engineer", "Administrator", "Manager" ];
        const employeeColumns = ["Name", "Company", "Title", "Age", "Country", "Salary" ];
        for (let col = 0; col < employeeColumns.length; col++) {
            employeeSheet.columns(col).width = 5000;
            employeeHeader.setCellValue(col, employeeColumns[col]);
        }

        for (let i = 1; i < 20; i++) {
            const company = this.getItem(companies);
            const title = this.getItem(titles);
            const country = this.getItem(countries);
            const name = this.getItem(firstNames) + " " + this.getItem(lastNames);
            const salary = this.getAmount(75000, 95000);
            const age = this.getRandom(20, 65);
            const wr = employeeSheet.rows(i);
            wr.setCellValue(0, name);
            wr.setCellValue(1, company);
            wr.setCellValue(2, title);
            wr.setCellValue(3, age);
            wr.setCellValue(4, country);
            wr.setCellValue(5, salary);
        }
        employeeSheet.tables().add("A1:F20", true);

        const expenseSheet = wb.worksheets().add("Expenses");
        const expanseHeader = expenseSheet.rows(0);
        const expanseNames = ["Year", "Computers", "Research", "Travel", "Salary", "Software" ];
        let expanseCol = 0;
        for (const key of expanseNames) {
            expenseSheet.columns(expanseCol).width = 5000;
            expanseHeader.setCellValue(expanseCol, key);
            for (let i = 1; i < 20; i++) {
                const wr = expenseSheet.rows(i);
                if (key === "Year") {
                    wr.setCellValue(expanseCol, 2010 + i);
                } else if (key === "Computers") {
                    wr.setCellValue(expanseCol, this.getAmount(50000, 65000));
                } else if (key === "Research") {
                    wr.setCellValue(expanseCol, this.getAmount(150000, 165000));
                } else if (key === "Travel") {
                    wr.setCellValue(expanseCol, this.getAmount(20000, 25000));
                } else if (key === "Salary") {
                    wr.setCellValue(expanseCol, this.getAmount(4000000, 450000));
                } else if (key === "Software") {
                    wr.setCellValue(expanseCol, this.getAmount(100000, 150000));
                }
            }
            expanseCol++;
        }
        expenseSheet.tables().add("A1:F20", true);

        const incomeSheet = wb.worksheets().add("Income");
        const incomeHeader = incomeSheet.rows(0);
        const incomeNames = ["Year", "Phones", "Computers", "Software", "Services", "Royalties" ];
        let incomeCol = 0;
        for (const key of incomeNames) {
            incomeSheet.columns(incomeCol).width = 5000;
            incomeHeader.setCellValue(incomeCol, key);
            for (let i = 1; i < 20; i++) {
                const wr = incomeSheet.rows(i);
                if (key === "Year") {
                    wr.setCellValue(incomeCol, 2010 + i);
                } else if (key === "Software") {
                    wr.setCellValue(incomeCol, this.getAmount(700000, 850000));
                } else if (key === "Computers") {
                    wr.setCellValue(incomeCol, this.getAmount(250000, 265000));
                } else if (key === "Royalties") {
                    wr.setCellValue(incomeCol, this.getAmount(400000, 450000));
                } else if (key === "Phones") {
                    wr.setCellValue(incomeCol, this.getAmount(6000000, 650000));
                } else if (key === "Services") {
                    wr.setCellValue(incomeCol, this.getAmount(700000, 750000));
                }
            }
            incomeCol++;
        }
        incomeSheet.tables().add("A1:F20", true);
        this.workbookParse(wb);
    }

    public onTableChange(newValue: string) {
        if (!newValue) {
            this.onTableSelected(null);
        } else {
            const parts = newValue.split(" - ");
            const worksheetName = parts[0];
            const tableName = parts[1];
            for (const ws of this.wb.worksheets()) {
                if (ws.name === worksheetName) {
                    for (const tbl of ws.tables()) {
                        if (tbl.name === tableName) {
                          this.onTableSelected(tbl);
                          return;
                        }
                    }
                }
            }
        }
    }

    public onTableSelected(table: WorksheetTable) {
        this.gridContainerRef.clear();

        if (table) {
            const headers = new Array<string>();
            // expanseCollect the keys for the data
            for (const expanseCol of table.columns()) {
                headers.push(expanseCol.name);
            }

            const ws = table.worksheet;
            const region = table.dataAreaRegion;
            const data = new Array<any>();

            for (let r = region.firstRow; r <= region.lastRow; r++) {
                const row = {};
                const excelRow = ws.rows(r);
                for (let c = 0; c < headers.length; c++) {
                    row[headers[c]] = excelRow.getCellText(c + region.firstColumn, TextFormatMode.IgnoreCellWidth);
                }
                data.push(row);
            }

            const gridFactory = this.resolver.resolveComponentFactory(IgxGridComponent);
            const gridRef = this.gridContainerRef.createComponent(gridFactory);
            gridRef.instance.autoGenerate = true;
            gridRef.instance.data = data;
        }
    }

    public getRandom(min: number, max: number): number {
        return Math.floor(Math.random() * (max - min + 1) + min);
    }

    public getItem(array: string[]): string {
        const i = this.getRandom(0, array.length - 1);
        return array[i];
    }

    public getAmount(min: number, max: number) {
        const n = this.getRandom(min, max);
        const s = n.toFixed(2).replace(/\d(?=(\d{3})+\.)/g, "$&,");
        return "$" + s.replace(".00", "");
    }
}
tsコピー

<div >
    <div class="workbookOptions">

        <div class="workbookOperations">
            <button class="workbookButton" (click)="workbookCreate()">
                <label>Create Workbook</label>
            </button>
            <button class="workbookButton" (click)="workbookSave()" [disabled]="!canSave">
                <label>Save Workbook</label>
            </button>
            <div class="workbookSelector">
                <button class="workbookButton">
                    <label for="workbookSelector">Load Workbook</label>
                </button>
                <input class="workbookButton" id="workbookSelector" type="file" style="visibility:hidden;"
                    accept=".xls, .xlt, .xlsx, .xlsm, .xltm, .xltx"
                    (change)='workbookLoad($event.target)' >
            </div>
        </div>
        <div class="workbookDetails">
            <label class="label">Select Table:</label>
            <select size="3" [(ngModel)]="selectedTable" (ngModelChange)="onTableChange($event)">
                <option *ngFor="let tblName of worksheetTables">
                    {{tblName}}
                </option>
            </select>
        </div>
    </div>

    <div class="workbookPreview">
        <label class="label"> Data Preview: </label>
        <template #gridContainer></template>
    </div>
</div>
htmlコピー

.workbookOptions {
    display: "block";
    width: 100%;
}

.workbookOperations {
    width: 160px;
    padding-left: 5px;
    padding-right: 5px;
    float:left;
}

.workbookDetails {
    float:left;
    width: 70%;
    height: 100px;
    padding-left: 5px;
    padding-right: 5px;
}

.workbookDetails > select {
    display: inline-block;
    width: 100%;
    height: 90px;
}

.label {
    margin-top: 8px;
    font-weight: bold;
}

.workbookPreview {
    display: inline-block;
    padding-left: 5px;
    padding-right: 5px;
    width: 100%;
    height: 200px;
}

.workbookButton {
    display: block;
    width: 150px;
    margin-top: 5px;
}

.workbookSelector {
    display: inline-block;
    width: 150px;
    margin-top: 5px;
}
scssコピー

Edit in: Codesandbox

Like this sample? Get access to our complete Ignite UI for Angular toolkit and start building your own apps in minutes. Download it for free.

60 以上のコンポーネント、柔軟な API、強力なテーマ設定およびブランディング機能、必要な速度と機能を備えた Angular アプリケーションを構築するための豊富な機能セット。

無料で試してみる

Change Default Font

먼저 IWorkbookFont의 새 인스턴스를 만듭니다. 다음으로 Workbook의 styles 컬렉션에 새 글꼴을 추가합니다. 이 스타일에는 행, 열 또는 셀에 별도로 지정되지 않는 한 통합 문서의 모든 셀에 대한 기본 속성이 포함됩니다. 스타일 속성을 변경하면 통합 문서의 기본 셀 형식 속성이 변경됩니다.

var workbook = new Workbook();
var font: IWorkbookFont;
font = workbook.styles().normalStyle.styleFormat.font;
font.name = "Times New Roman";
font.height = 16 * 20;
ts

Setting Workbook Properties

Microsoft Excel® 문서 속성은 문서를 구성하고 추적하는 데 도움이 되는 정보를 제공합니다. Infragistics Angular Excel Library를 사용하여 Workbook 개체의 documentProperties 속성을 사용하여 이러한 속성을 설정할 수 있습니다. 사용 가능한 속성은 다음과 같습니다.

• Author

• Title

• Subject

• Keywords

• Category

• Status

• Comments

• Company

• Manager

다음 코드는 통합 문서를 만들고 title 및 status 문서 속성을 설정하는 방법을 보여줍니다.

var workbook = new Workbook();
workbook.documentProperties.title = "Expense Report";
workbook.documentProperties.status = "Complete";
ts

Workbook Protection

통합 문서 보호 기능을 사용하면 통합 문서의 구조를 보호할 수 있습니다. 즉, 사용자가 해당 통합 문서의 워크시트를 추가하고, 이름을 바꾸고, 삭제하고, 숨기고, 순서를 바꾸는 기능입니다.

보호는 Infragistics Excel Engine의 개체 모델을 통해 적용되지 않습니다. 이러한 보호 설정을 존중하고 사용자가 해당 작업을 수행하는 것을 허용하거나 제한하는 것은 이 개체 모델을 시각화하는 UI의 책임입니다.

​protect 메서드를 호출하여 통합 문서에 보호가 적용됩니다.

​Workbook 암호 없이 보호되면 최종 사용자는 암호를 제공하지 않고도 Excel에서 Workbook 보호를 해제할 수 있습니다. Workbook 프로그래밍 방식으로 보호 해제하려면 unprotect 메서드를 사용할 수 있습니다.

언제 Workbook 보호되어 있으면 해당 속성의 값은 WorkbookProtection 이것의 예 Workbook '에스 protection 속성은 비활성화된 작업을 나타냅니다.

If isProtected is already true, the protect method will be ignored.

var workbook = new Workbook();
workbook.protect(false, false);
ts

통합 문서에 보호 기능이 있는지 확인하세요. 이 읽기 전용 속성은 통합 문서에 Protect 메서드의 오버로드를 사용하여 설정된 보호가 있는 경우 true를 반환합니다.

var workbook = new Workbook();
var protect = workbook.isProtected;
ts

이 읽기 전용 속성은 각 보호 설정을 개별적으로 가져오기 위한 속성이 포함된 WorkbookProtection 유형의 개체를 반환합니다.

var workbook = new Workbook();
var protection = workbook.protection;
ts

API References

• documentProperties
• WorkbookProtection
• Workbook
• Workbook