Angular Using Workbooks
El motor Angular Excel Infragistics le permite guardar y cargar datos desde Microsoft® Excel®. Puede crear libros y hojas de trabajo, ingresar datos y exportarlos a Excel mediante las distintas clases de la biblioteca. El motor Angular Excel Infragistics facilita la exportación de datos en su aplicación como una hoja de cálculo de Excel, así como la importación de datos desde Excel a su aplicación.
Ejemplo de uso de libros de trabajo Angular
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) {
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 ;
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 >();
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 コピー
¿Te gusta esta muestra? Obtenga acceso a nuestro kit de herramientas de Ignite UI for Angular completo y comience a crear sus propias aplicaciones en minutos. Descárgalo gratis.
Cambiar fuente predeterminada
Primero cree una nueva instancia de IWorkbookFont
. A continuación, agregue la nueva fuente a la colección styles
del Workbook
. Este estilo contiene las propiedades predeterminadas para todas las celdas del libro, a menos que se especifique lo contrario en una fila, columna o celda. Cambiar las propiedades del estilo cambiará las propiedades de formato de celda predeterminadas en el libro.
var workbook = new Workbook();
var font: IWorkbookFont;
font = workbook.styles().normalStyle.styleFormat.font;
font.name = "Times New Roman" ;
font.height = 16 * 20 ;
ts
Configuración de propiedades del libro de trabajo
Las propiedades de los documentos de Microsoft Excel® proporcionan información para ayudar a organizar y realizar un seguimiento de los documentos. Puede utilizar la biblioteca Angular Excel Infragistics para configurar estas propiedades mediante la propiedad documentProperties
del objeto Workbook
. Las propiedades disponibles son:
Author
Title
Subject
Keywords
Category
Status
Comments
Company
Manager
El siguiente código demuestra cómo crear un libro y establecer sus propiedades title
y documento status
.
var workbook = new Workbook();
workbook.documentProperties.title = "Expense Report" ;
workbook.documentProperties.status = "Complete" ;
ts
Protección del libro de trabajo
La función de protección del libro le permite proteger la estructura del libro. Es decir, la capacidad de un usuario de agregar, cambiar el nombre, eliminar, ocultar y reordenar las hojas de trabajo de ese libro.
La protección no se aplica a través del modelo de objetos de Infragistics Excel Engine. Es responsabilidad de la interfaz de usuario que visualiza este modelo de objetos respetar estas configuraciones de protección y permitir o restringir que el usuario realice las operaciones correspondientes.
La protección se aplica a un libro invocando su método protect
.
Cuando un Workbook
está protegido sin contraseña, el usuario final puede desproteger el Workbook
en Excel sin tener que proporcionar una contraseña. Para desproteger un Workbook
mediante programación, se puede utilizar el método unprotect
.
Cuando un Workbook
está protegido, los valores de las propiedades del WorkbookProtection
instancia de este Workbook
's protection
La propiedad indica las operaciones deshabilitadas.
If isProtected
is already true, the protect
method will be ignored.
var workbook = new Workbook();
workbook.protect(false , false );
ts
Compruebe si un libro tiene protección. Esta propiedad de solo lectura devuelve verdadero si el libro tiene alguna protección establecida mediante las sobrecargas del método Protect.
var workbook = new Workbook();
var protect = workbook.isProtected;
ts
Esta propiedad de solo lectura devuelve un objeto de tipo WorkbookProtection que contiene propiedades para obtener cada configuración de protección individualmente.
var workbook = new Workbook();
var protection = workbook.protection;
ts
Referencias de API