Web Components Trabajar con minigráficos

La biblioteca de Excel Infragistics Web Components admite la adición de minigráficos a una hoja de cálculo de Excel. Se pueden usar para mostrar representaciones visuales simples de las tendencias de datos en una región de celdas de datos en la hoja de cálculo. Por ejemplo, si desea ver sus datos de Excel en una región de celda en particular visualizados como una columna simple o un gráfico de minigráfico de líneas, esta función puede ayudarlo a lograrlo.

Web Components Ejemplo de trabajo con minigráficos

EXAMPLE

DATA

TS

HTML

CSS

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);
            }
        });
    }
}
tsコピー

import { ExcelUtility } from './ExcelUtility';
import { IgcExcelModule } from 'igniteui-webcomponents-excel';
import { SparklineType, WorkbookFormat } from 'igniteui-webcomponents-excel';
import { Workbook } from 'igniteui-webcomponents-excel';
import { IgcDataGridComponent } from 'igniteui-webcomponents-grids';
import { IgcDataGridModule } from 'igniteui-webcomponents-grids';
import { IgcTemplateColumnComponent } from 'igniteui-webcomponents-grids';
import { IgcTemplateCellUpdatingEventArgs } from 'igniteui-webcomponents-grids';
import { IgcTemplateCellInfo } from 'igniteui-webcomponents-grids';
import { IgcSparklineComponent } from 'igniteui-webcomponents-charts';
import { IgcSparklineModule } from 'igniteui-webcomponents-charts';
import { SparklineDisplayType } from 'igniteui-webcomponents-charts';
import { ModuleManager } from 'igniteui-webcomponents-core';

ModuleManager.register(
    IgcExcelModule,
    IgcDataGridModule,
    IgcSparklineModule
);

export class ExcelLibrarySparklines {

    public data: any[] = [];

    constructor() {

        this.initData();

        const grid = document.getElementById('grid') as IgcDataGridComponent;
        grid.dataSource = this.data;

        const exportBtn = document.getElementById('exportBtn');
        exportBtn!.addEventListener('click', this.exportGrid);

        const templateColumn = document.getElementById('templateColumn') as IgcTemplateColumnComponent;
        templateColumn.cellUpdating = this.onOrdersCellUpdating;
    }

    public onOrdersCellUpdating(s: IgcTemplateColumnComponent, e: IgcTemplateCellUpdatingEventArgs) {
        const content = e.content as HTMLDivElement;
        const info = e.cellInfo as IgcTemplateCellInfo;
        let sparkline: IgcSparklineComponent | null = null;

        if (content.childElementCount === 0) {
            sparkline = new IgcSparklineComponent();
            sparkline.height = '40px';
            sparkline.width = '200px';
            sparkline.displayType = SparklineDisplayType.Column;
            sparkline.minimum = 0;

            sparkline.valueMemberPath = "Freight";

            content.appendChild(sparkline);
        }
        else {
            sparkline = content.children[0] as IgcSparklineComponent;
        }

        if (sparkline) {
            sparkline.dataSource = info.rowItem.Orders;
        }
    }

    public exportGrid = (e: any) => {
        const headers = ['Orders', 'Company Name', 'Contact Name', 'Contact Title', 'Country'];
        const keys = ['Orders', 'CompanyName', 'ContactName', 'ContactTitle', 'Country'];
        const orderHeaders = ['Customer ID', 'Order ID', 'Freight'];

        const wb = new Workbook(WorkbookFormat.Excel2007);
        const exportSheet = wb.worksheets().add('Sheet1');
        const ordersSheet = wb.worksheets().add('Orders');

        exportSheet.defaultColumnWidth = 300 * 20;
        exportSheet.defaultRowHeight = 50 * 20;

        for (let i = 0; i < headers.length; i++) {
            exportSheet.rows(0).cells(i).value = headers[i];
        }

        for (let i = 0; i < this.data.length; i++) {
            const item = this.data[i];
            const orders = item.Orders;

            for (let j = 0; j < orders.length; j++) {
                ordersSheet.rows(i).cells(j).value = orders[j].Freight;
            }
        }

        for (let i = 0; i < this.data.length; i++) {

            const index = (i + 1).toString();
            const dataItem = this.data[i];

            for (let j = 0; j < headers.length; j++) {
                if (j === 0) {
                    exportSheet.sparklineGroups().add(SparklineType.Column, 'A' + (i + 2).toString(), 'Orders!A' + index + ':F' + index);
                }
                else {
                    exportSheet.rows(i + 1).cells(j).value = dataItem[keys[j]];
                }
            }
        }

        ExcelUtility.save(wb, 'myWorksheet');
    }

    public initData() {
        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', 'Novak'];
        const cities = ['London', 'Paris', 'Boston', 'Berlin'];
        const countries = ['UK', 'France', 'USA', 'Germany'];
        const titles = ['Sales Rep.', 'Owner', 'Administrator', 'Manager'];
        const streets = ['Main St', 'Madison St', 'Broad Way'];
        const shippings = ['Federal Ex', 'UPS Air', 'UPS Ground'];

        const data = new Array<any>();
        // generating excel data source
        for (let i = 0; i < 10; i++) {
            const companyName = this.getItem(companies);
            const contactTitle = this.getItem(titles);
            const country = this.getItem(countries);
            const city = this.getItem(cities);
            const shipping = this.getItem(shippings);
            const contactName = this.getItem(firstNames) + ' ' + this.getItem(lastNames);
            const employeeName = this.getItem(firstNames) + ' ' + this.getItem(lastNames);
            const address = this.getRandom(10, 60) + ' ' + this.getItem(streets);
            const postalCode = this.getRandom(100, 400) + ' ' + this.getRandom(50, 90);
            const customerID = 'CID-' + this.getRandom(500, 900);
            const phone = this.getRandom(500, 900) + '-' + this.getRandom(200, 900) + '-' + this.getRandom(2000, 9000);
            const fax = this.getRandom(500, 900) + '-' + this.getRandom(200, 900) + '-' + this.getRandom(2000, 9000);

            const companyOrders = new Array<any>();
            for (let o = 0; o < 6; o++) {
                const reqDate = '2020-06-' + this.getRandom(1, 25) + 'T' + this.getRandom(10, 12) + ':00:00';
                const shipDate = '2020-06-' + this.getRandom(1, 25) + 'T' + this.getRandom(10, 12) + ':00:00';
                const orderDate = '2020-05-' + this.getRandom(1, 25) + 'T' + this.getRandom(10, 12) + ':00:00';
                const order = {
                    ContactName: contactName,
                    CustomerID: customerID,
                    EmployeeID: this.getRandom(1000, 8000),
                    EmployeeName: employeeName,
                    Freight: this.getRandom(1, 10),
                    OrderDate: orderDate,
                    OrderID: this.getRandom(3000, 5000),
                    RequiredDate: reqDate,
                    ShipAddress: address,
                    ShipCity: city,
                    ShipCountry: country,
                    ShipName: companyName,
                    ShipPostalCode: postalCode,
                    ShipRegion: '',
                    ShipVia: this.getRandom(1, 10),
                    ShippedDate: shipDate,
                    ShipperID: this.getRandom(1, 10),
                    ShipperName: shipping,
                    TotalItems: this.getRandom(10, 20),
                    TotalPrice: this.getRandom(400, 600)
                };
                companyOrders.push(order);
            }
            const dataItem = {
                Address: address,
                City: city,
                CompanyName: companyName,
                ContactName: contactName,
                ContactTitle: contactTitle,
                Country: country,
                Fax: fax,
                ID: customerID,
                Orders: companyOrders,
                Phone: phone,
                PostalCode: postalCode,
                Region: ''
            };
            data.push(dataItem);
        }
        this.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];
    }
}

new ExcelLibrarySparklines();
tsコピー

<!DOCTYPE html>
<html>
  <head>
    <title>ExcelLibrarySparklines</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">
    <div class="options horizontal">
        <button class="options-label" id="exportBtn">Export</button>
    </div>
    <igc-data-grid id="grid" auto-generate-columns="false" height="calc(100% - 3rem)" width="100%">
        <igc-template-column id="templateColumn" field="Orders"></igc-template-column>
        <igc-text-column field="CompanyName"></igc-text-column>
        <igc-text-column field="ContactName"></igc-text-column>
        <igc-text-column field="ContactTitle"></igc-text-column>
        <igc-text-column field="Country"></igc-text-column>
    </igc-data-grid>
</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コピー

Edit in: Codesandbox

¿Te gusta esta muestra? Obtenga acceso a nuestro kit de herramientas de Ignite UI for Web Components completo y comience a crear sus propias aplicaciones en minutos. Descárgalo gratis.

Minigráficos admitidos

La siguiente es una lista de los tipos de minigráficos predefinidos admitidos.

• Línea
• Columna
• Apilado (ganado/perdido)

El siguiente código demuestra cómo agregar minigráficos a una hoja de trabajo mediante programación a través de la colección sparklineGroups:

var workbook: Workbook;
var sheet1 = workbook.worksheets().add("Sparklines");
var sheet2 = workbook.worksheets().add("Data");
sheet1.sparklineGroups().add(SparklineType.Line, "Sparklines!A1:A1", "Data!A2:A11");
sheet1.sparklineGroups().add(SparklineType.Column, "Sparklines!B1:B1", "Data!A2:A11");
workbook.save(workbook, "Sparklines.xlsx");
ts

Referencias de API

• Workbook