Sparklines를 사용한 React 작업

Infragistics React Excel Library는 Excel 워크시트에 스파크라인을 추가하는 기능을 지원합니다. 이를 사용하여 워크시트의 데이터 셀 영역에 걸친 데이터 추세를 간단하게 시각적으로 표현할 수 있습니다. 예를 들어, 특정 셀 영역에 걸친 Excel 데이터를 간단한 열 또는 선 스파크라인 차트로 시각화하려는 경우 이 기능을 사용하면 이를 달성하는 데 도움이 될 수 있습니다.

Sparklines 예제를 사용한 React 작업

EXAMPLE

DATA

TSX

// NOTE this file contains multiple data sources:


// Data Source #1


export class ExcelSharedData {

}



// Data Source #2
import { 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);
            }
        });
    }
}

tsコピー

import React from 'react';
import ReactDOM from 'react-dom/client';
import './index.css';
import { ExcelUtility } from './ExcelUtility';
import { Workbook } from "@infragistics/igniteui-react-excel";
import { WorkbookFormat } from "@infragistics/igniteui-react-excel";
import { IgrDataGridModule } from "@infragistics/igniteui-react-data-grids";
import { IgrDataGrid } from "@infragistics/igniteui-react-data-grids";
import { IgrTextColumn } from "@infragistics/igniteui-react-data-grids";
import { IgrTemplateColumn, IIgrCellTemplateProps } from "@infragistics/igniteui-react-data-grids";
import { IgrTemplateCellInfo } from "@infragistics/igniteui-react-data-grids";
import { IgrSparkline } from "@infragistics/igniteui-react-charts";
import { IgrSparklineModule } from "@infragistics/igniteui-react-charts";
import { IgrExcelXlsxModule } from "@infragistics/igniteui-react-excel";
import { IgrExcelCoreModule } from "@infragistics/igniteui-react-excel";
import { IgrExcelModule } from "@infragistics/igniteui-react-excel";
import { SparklineType } from "@infragistics/igniteui-react-excel";

IgrDataGridModule.register();

IgrSparklineModule.register();

IgrExcelCoreModule.register();
IgrExcelModule.register();
IgrExcelXlsxModule.register();

export default class ExcelLibraryWorkingWithSparklines extends React.Component<any, any> {

    public data: any[];

    constructor(props: any) {
        super(props);

        this.exportGrid = this.exportGrid.bind(this);

        this.initData();
    }

    public render(): JSX.Element {
        return (
            <div className="container sample">
                <div className="options horizontal">
                    <button className="options-button" onClick={this.exportGrid}>Export</button>
                </div>
                <IgrDataGrid autoGenerateColumns={false} dataSource={this.data} height="calc(100% - 30px)" width="100%">
                    <IgrTemplateColumn width="*>90" field="Orders" template={this.templateColTestMethod} />
                    <IgrTextColumn width="*>130" field="CompanyName"  headerText="Company" />
                    <IgrTextColumn width="*>90"  field="ContactName" headerText="Name" />
                    <IgrTextColumn width="*>70"  field="ContactTitle" headerText="Title" />
                    <IgrTextColumn width="*>90"  field="Country" headerText="Country" />
                </IgrDataGrid>
            </div>
        );
    }

    public templateColTestMethod(props: IIgrCellTemplateProps) {
        const tmpl = props.dataContext as IgrTemplateCellInfo;

        return (
            <IgrSparkline dataSource={tmpl.rowItem.Orders} height="40px" width="200px"
                          displayType="Column" valueMemberPath="Freight" minimum={0} />
        );
    }

    public exportGrid() {
        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 < 20; 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(3, 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];
    }

}

// rendering above class to the React DOM
const root = ReactDOM.createRoot(document.getElementById('root'));
root.render(<ExcelLibraryWorkingWithSparklines/>);
tsxコピー

Edit in: Codesandbox

지원되는 스파크라인

다음은 지원되는 사전 정의된 스파크라인 유형 목록입니다.

• 선
• 열
• 스택(승/패)

다음 코드는 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