Angular Excel ライブラリの概要
Infragistics Angular Excel ライブラリは、workbook
、Worksheet
、Cell
、Formula
などの人気の Microsoft® Excel® スプレッドシート オブジェクトを使用してスプレッドシート データで作業をすることができます。Infragistics Angular Excel ライブラリによって Excel スプレッドシートでアプリケーションのデータを表示するだけでなく、Excel からアプリケーションへのデータの転送も簡単になります。
Angular Excel ライブラリの例
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 { BrowserModule } from "@angular/platform-browser";
import { BrowserAnimationsModule } from "@angular/platform-browser/animations";
import { AppComponent } from "./app.component";
import { IgxExcelModule } from "igniteui-angular-excel";
@NgModule({
bootstrap: [AppComponent],
declarations: [
AppComponent,
],
imports: [
BrowserModule,
BrowserAnimationsModule,
IgxExcelModule
],
providers: [],
schemas: []
})
export class AppModule {}
ts
import { Component, ComponentFactoryResolver, Injector, OnInit } from "@angular/core";
import { Workbook } from "igniteui-angular-excel";
import { WorkbookFormat } 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 {
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) {
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";
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;
}
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 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">
<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>
<button class="workbookButton" (click)="workbookSave()" [disabled]="!canSave">
<label>Save Workbook</label>
</button>
</div>
</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
このサンプルが気に入りましたか? 完全な Ignite UI for Angularツールキットにアクセスして、すばやく独自のアプリの作成を開始します。無料でダウンロードできます。
依存関係
excel パッケージをインストールするときに core パッケージもインストールする必要があります。
npm install --save igniteui-angular-core
npm install --save igniteui-angular-excel
cmd
モジュールの要件
Angular Excel ライブラリを作成するには、以下のモジュールが必要です。
import { IgxExcelModule } from 'igniteui-angular-excel';
@NgModule({
imports: [
IgxExcelModule,
]
})
export class AppModule {}
ts
モジュールの実装
Excel ライブラリには、アプリのバンドル サイズを制限するために使用できる 5 つのモジュールが含まれています。
- IgxExcelCoreModule – オブジェクトモデルを含み、Excel の基盤となります。
- IgxExcelFunctionsModule – Sum、Average、Min、Max、SumIfs、Ifs など、数式評価のほとんどのカスタム関数を含み、このモジュールがなくても数式が計算 ( “=SUM(A1:A5 などの数式を適用するなど) されてセルの Value を要求する場合は数式の解析で問題を発生しません。(注: 例外のスローではありません。数式の結果がエラーとなるため特定のエラーを表すオブジェクト)。
- IgxExcelXlsModule – xls (および関連する) タイプ ファイルのロジックの読み込みと保存を含みます。これは Excel97to2003 関連の WorkbookFormats です。
- IgxExcelXlsxModule – xlsx (および関連する) タイプ ファイルのロジックの読み込みと保存を含みます。これは Excel2007 関連および StrictOpenXml ANDWorkbookFormats です。
- IgxExcelModule – 他の 4 つのモジュールの参照ですべての機能の読み込み/使用を可能にします。
サポートされるバージョンの Microsoft Excel
以下は Excel のサポートされるバージョンのリストです。
Microsoft Excel 97
Microsoft Excel 2000
Microsoft Excel 2002
Microsoft Excel 2003
Microsoft Excel 2007
Microsoft Excel 2010
Microsoft Excel 2013
Microsoft Excel 2016
ヒープの管理
Excel Library のサイズに因り、ソースマップの生成を無効にすることを推奨します。
architect => build => options から serve の options で vendorSourceMap
オプションを設定して angular.json
を変更します。
"architect": {
"build": {
"builder": "...",
"options": {
"vendorSourceMap": false,
"outputPath": "dist",
"index": "src/index.html",
"main": "src/main.ts",
"tsConfig": "src/tsconfig.app.json",
},
},
"serve": {
"builder": "...",
"options": {
"vendorSourceMap": false,
"browserTarget": "my-app:build"
},
},
}
ts
API リファレンス