Web Components Excel ライブラリの概要
Infragistics Web Components Excel ライブラリは、workbook
、Worksheet
、Cell
、Formula
などの人気の Microsoft® Excel® スプレッドシート オブジェクトを使用してスプレッドシート データで作業をすることができます。Infragistics Web Components Excel ライブラリによって Excel スプレッドシートでアプリケーションのデータを表示するだけでなく、Excel からアプリケーションへのデータの転送も簡単になります。
Web Components Excel ライブラリの例
import { saveAs } from "file-saver";
import { Workbook } from 'igniteui-webcomponents-excel';
import { WorkbookFormat } from 'igniteui-webcomponents-excel';
import { WorkbookSaveOptions } from 'igniteui-webcomponents-excel';
import { WorkbookLoadOptions } from 'igniteui-webcomponents-excel';
import { IgcExcelXlsxModule } from 'igniteui-webcomponents-excel';
import { IgcExcelCoreModule } from 'igniteui-webcomponents-excel';
import { IgcExcelModule } from 'igniteui-webcomponents-excel';
IgcExcelCoreModule.register();
IgcExcelModule.register();
IgcExcelXlsxModule.register();
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, new WorkbookLoadOptions(), (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, new WorkbookLoadOptions(), (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);
}
});
}
}
tsimport { ExcelUtility } from './ExcelUtility';
// import { IgcExcelXlsxModule } from 'igniteui-webcomponents-excel';
// import { IgcExcelCoreModule } from 'igniteui-webcomponents-excel';
import { IgcExcelModule } from 'igniteui-webcomponents-excel';
import { Workbook } from 'igniteui-webcomponents-excel';
import { Worksheet } from 'igniteui-webcomponents-excel';
import { WorkbookFormat } from 'igniteui-webcomponents-excel';
import { ModuleManager } from 'igniteui-webcomponents-core';
ModuleManager.register(
// IgcExcelXlsxModule,
// IgcExcelCoreModule,
IgcExcelModule
);
export class ExcelLibraryOverview {
public canSave = false;
public wb: Workbook;
public ws: Worksheet;
public worksheetRegion: string[] | null;
public selectedRegion: string | null;
constructor() {
this.init();
const saveWorkbook = document.getElementById('saveWorkbook');
saveWorkbook!.addEventListener('click', this.onClick);
}
public workbookSave(): void {
if (this.canSave) {
ExcelUtility.save(this.wb, 'ExcelWorkbook').then((f: any) => {
console.log('Saved:' + f);
}, (e: any) => {
console.error('ExcelUtility.Save Error:' + e);
});
}
}
public workbookParse(wb: Workbook): void {
if (wb === undefined) {
this.worksheetRegion = null;
this.selectedRegion = null;
} else {
const names = new Array<string>();
const worksheets = wb.worksheets();
const wsCount = worksheets.count;
for (let i = 0; i < wsCount; i ++) {
const tables = worksheets.item(i).tables();
const tCount = tables.count;
for (let j = 0; j < tCount; j++) {
names.push(worksheets.item(i).name + ' - ' + tables.item(j).name);
}
}
this.worksheetRegion = names;
this.selectedRegion = 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'];
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.getRandom(45000, 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);
}
const expanseSheet = wb.worksheets().add('Expanses');
const expanseHeader = expanseSheet.rows(0);
const expanseNames = ['Year', 'Computers', 'Research', 'Travel', 'Salary', 'Software' ];
let expanseCol = 0;
for (const key of expanseNames) {
expanseSheet.columns(expanseCol).width = 5000;
expanseHeader.setCellValue(expanseCol, key);
for (let i = 1; i < 20; i++) {
const wr = expanseSheet.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++;
}
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++;
}
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;
}
public onClick = (e: any) => {
this.workbookSave();
}
public init() {
this.workbookCreate();
}
}
new ExcelLibraryOverview();
ts<!DOCTYPE html>
<html>
<head>
<title>ExcelLibraryOverview</title>
<meta charset="UTF-8" />
<link rel="shortcut icon" href="https://static.infragistics.com/xplatform/images/browsers/wc.png" >
<link rel="stylesheet" href="https://fonts.googleapis.com/icon?family=Material+Icons" />
<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Kanit&display=swap" />
<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Titillium Web" />
<link rel="stylesheet" href="https://static.infragistics.com/xplatform/css/samples/shared.v8.css" type="text/css" />
</head>
<body>
<div id="root">
<div class="container sample">
<button id="saveWorkbook">Save Workbook</button>
</div>
</div>
<!-- This script is needed only for parcel and it will be excluded for webpack -->
<% if (false) { %><script src="src/index.ts"></script><% } %>
</body>
</html>
html/* shared styles are loaded from: */
/* https://static.infragistics.com/xplatform/css/samples */
css
このサンプルが気に入りましたか? 完全な Ignite UI for Web Componentsツールキットにアクセスして、すばやく独自のアプリの作成を開始します。無料でダウンロードできます。
依存関係
excel パッケージをインストールするときに core パッケージもインストールする必要があります。
npm install --save igniteui-webcomponents-core
npm install --save igniteui-webcomponents-excel
cmd
モジュールの要件
Web Components Excel ライブラリを作成するには、以下のモジュールが必要です。
// Module Manager for registering the modules of the chart
import { ModuleManager } from 'igniteui-webcomponents-core';
import { IgcExcelModule } from 'igniteui-webcomponents-excel';
// register the modules
ModuleManager.register(
IgcExcelModule
);
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