Web Components スパークラインを使用した作業

Infragistics Web Components Excel Library は、Excel ワークシートにスパークラインを追加する機能があります。これらを使用して、ワークシートのデータ セルの領域全体のデータの傾向を簡単に視覚的に表現することができます。たとえば、特定のセル領域の Excel データを単純な縦棒チャートまたは折れ線チャートとして視覚化したい場合は、この機能を使用すると役立ちます。

Web Components スパークラインを使用した作業の例

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

このサンプルが気に入りましたか? 完全な Ignite UI for Web Componentsツールキットにアクセスして、すばやく独自のアプリの作成を開始します。無料でダウンロードできます。

サポートされるスパークライン

以下はサポートされる定義済スパークラインのタイプです。

• 折れ線チャート
• 列
• 積層 (Win/Loss)

以下のコードは、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

API リファレンス

• Workbook