React Excel ライブラリの概要
Infragistics React Excel ライブラリは、workbook
、Worksheet
、Cell
、Formula
などの人気の Microsoft® Excel® スプレッドシート オブジェクトを使用してスプレッドシート データで作業をすることができます。Infragistics React Excel ライブラリによって Excel スプレッドシートでアプリケーションのデータを表示するだけでなく、Excel からアプリケーションへのデータの転送も簡単になります。
React Excel ライブラリの例
export class ExcelSharedData {
}
tsimport { saveAs } from "file-saver";
import { Workbook } from "@infragistics/igniteui-react-excel";
import { WorkbookFormat } from "@infragistics/igniteui-react-excel";
import { WorkbookSaveOptions } from "@infragistics/igniteui-react-excel";
import { WorkbookLoadOptions } from "@infragistics/igniteui-react-excel";
import { IgrExcelXlsxModule } from "@infragistics/igniteui-react-excel";
import { IgrExcelCoreModule } from "@infragistics/igniteui-react-excel";
import { IgrExcelModule } from "@infragistics/igniteui-react-excel";
IgrExcelCoreModule.register();
IgrExcelModule.register();
IgrExcelXlsxModule.register();
export class ExcelUtility {
public static getExtension(format: WorkbookFormat): string {
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): void => {
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): void => {
reject(fr.error);
};
if (fr.readAsBinaryString) {
fr.onload = (e): void => {
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): void => {
resolve(new Uint8Array(fr.result as ArrayBuffer));
};
fr.readAsArrayBuffer(file);
}
});
}
}
tsimport React from 'react';
import ReactDOM from 'react-dom/client';
import './index.css';
import { ExcelUtility } from './ExcelUtility';
// import { ExcelSharedData } from './ExcelSharedData';
import { IgrExcelModule } from "@infragistics/igniteui-react-excel";
import { Workbook } from "@infragistics/igniteui-react-excel";
import { Worksheet } from "@infragistics/igniteui-react-excel";
import { WorkbookFormat } from "@infragistics/igniteui-react-excel";
IgrExcelModule.register();
export default class ExcelLibraryOverview extends React.Component<any, any> {
public canSave = false;
public wb: Workbook;
public ws: Worksheet;
public worksheetRegion: string[] | null;
public selectedRegion: string | null;
constructor(props: any) {
super(props);
this.init();
}
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 = () => {
this.workbookSave();
}
public render(): JSX.Element {
return (
<div className="container sample">
<div className="options horizontal">
<button onClick={this.onClick}>Save Workbook</button>
</div>
</div>
);
}
public init() {
this.workbookCreate();
}
}
// rendering above class to the React DOM
const root = ReactDOM.createRoot(document.getElementById('root'));
root.render(<ExcelLibraryOverview/>);
tsx
このサンプルが気に入りましたか? 完全な Ignite UI for Reactツールキットにアクセスして、すばやく独自のアプリの作成を開始します。無料でダウンロードできます。
依存関係
excel パッケージをインストールするときに core パッケージもインストールする必要があります。
npm install --save igniteui-react-core
npm install --save igniteui-react-excel
cmd
モジュールの要件
React Excel ライブラリを作成するには、以下のモジュールが必要です。
import { IgrExcelModule } from 'igniteui-react-excel';
IgrExcelModule.register();
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