Angular Excel 라이브러리 개요
Infragistics Angular Excel Library를 사용하면 workbook
, Worksheet
, Cell
, Formula
등과 같은 익숙한 Microsoft® Excel® 스프레드시트 개체를 사용하여 스프레드시트 데이터로 작업할 수 있습니다. Infragistics Angular Excel Library를 사용하면 Excel 스프레드시트에서 애플리케이션의 데이터를 쉽게 표현하고 Excel에서 애플리케이션으로 데이터를 전송할 수 있습니다.
Angular Excel Library 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 { 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 コピー
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.
Dependencies
엑셀 패키지 설치 시 코어 패키지도 함께 설치해야 합니다.
npm install --save igniteui-angular-core
npm install --save igniteui-angular-excel
cmd
Component Modules
Angular Excel 라이브러리에는 다음 모듈이 필요합니다.
import { IgxExcelModule } from 'igniteui-angular-excel' ;
@NgModule ({
imports : [
IgxExcelModule,
]
})
export class AppModule {}
ts
Modules Implementation
Excel 라이브러리에는 앱의 번들 크기를 제한하는 데 사용할 수 있는 5개의 모듈이 포함되어 있습니다.
IgxExcelCoreModule – 여기에는 개체 모델과 Excel 인프라의 대부분이 포함되어 있습니다.
IgxExcelFunctionsModule – 여기에는 Sum, Average, Min, Max 등과 같은 수식 평가를 위한 대부분의 함수가 포함되어 있습니다. 이 모듈이 없어도 수식을 계산할 경우 수식 구문 분석에 문제가 발생하지 않습니다. 예를 들어 “=SUM(A1:A5)”와 같은 수식을 적용하고 셀의 값을 요청하면 #NAME!이 표시됩니다. 오류가 반환되었습니다. 이는 예외 발생이 아닙니다. 수식으로 인해 오류가 발생할 수 있으므로 특정 오류를 나타내는 개체입니다.
IgxExcelXlsModule – 여기에는 xls(및 관련) 유형 파일, 즉 Excel97to2003 관련 WorkbookFormats에 대한 로드 및 저장 논리가 포함되어 있습니다.
IgxExcelXlsxModule – 여기에는 xlsx(및 관련) 유형 파일, 즉 Excel2007 관련 및 StrictOpenXml WorkbookFormats에 대한 로드 및 저장 논리가 포함되어 있습니다.
IgxExcelModule – 이는 다른 4개의 모듈을 참조하므로 기본적으로 모든 기능이 로드/사용 가능하도록 보장합니다.
Supported Versions of Microsoft Excel
다음은 지원되는 Excel 버전 목록입니다.**
마이크로소프트 엑셀 97
마이크로소프트 엑셀 2000
마이크로소프트 엑셀 2002
마이크로소프트 엑셀 2003
마이크로소프트 엑셀 2007
마이크로소프트 엑셀 2010
마이크로소프트 엑셀 2013
마이크로소프트 엑셀 2016
Load and Save Workbooks
이제 Excel 라이브러리 모듈을 가져왔으므로 다음 단계는 통합 문서를 로드하는 것입니다.
다음 코드 조각에서는 외부 Excel유틸리티 클래스는 저장하고 로드하는 데 사용됩니다. workbook
.
통합 workbook
개체를 로드하고 저장하려면 실제 workbook
개체의 저장 메서드와 정적 Load
메서드를 활용할 수 있습니다.
import { Workbook } from "igniteui-angular-excel" ;
import { WorkbookSaveOptions } from "igniteui-angular-excel" ;
import { WorkbookFormat } from "igniteui-angular-excel" ;
import { ExcelUtility } from "ExcelUtility" ;
var workbook = ExcelUtility.load(file);
ExcelUtility.save(workbook, "fileName" );
ts
Managing Heap
Excel 라이브러리의 크기로 인해 소스 맵 생성을 비활성화하는 것이 좋습니다.
건축가 => 빌드 => 옵션 및 봉사 => 옵션에서 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 References