Web Components Spreadsheet Chart Adapter

The Web Components Spreadsheet component allows displaying charts in your IgcSpreadsheetComponent.

Web Components Spreadsheet Chart Adapter Example

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);
            }
        });
    }
}
tsCOPY CODE

import { ExcelUtility } from './ExcelUtility';
import { IgcSpreadsheetChartAdapterModule } from 'igniteui-webcomponents-spreadsheet-chart-adapter';
import { IgcSpreadsheetModule } from 'igniteui-webcomponents-spreadsheet';
import { IgcSpreadsheetComponent } from 'igniteui-webcomponents-spreadsheet';
import { SpreadsheetChartAdapter } from 'igniteui-webcomponents-spreadsheet-chart-adapter';
import { ChartType, ChartTitle, FormattedString } from 'igniteui-webcomponents-excel';
import { WorksheetCell } from 'igniteui-webcomponents-excel';
import { Worksheet } from 'igniteui-webcomponents-excel';
import { ModuleManager } from 'igniteui-webcomponents-core';

ModuleManager.register(
    IgcSpreadsheetModule,
    IgcSpreadsheetChartAdapterModule
);

export class SpreadsheetAdapter {

    private spreadsheet: IgcSpreadsheetComponent;

    constructor() {

        this.spreadsheet = document.getElementById('spreadsheet') as IgcSpreadsheetComponent;
        this.spreadsheet.chartAdapter = new SpreadsheetChartAdapter();

        let path = 'https://static.infragistics.com/xplatform/excel/ChartData.xlsx';
        ExcelUtility.loadFromUrl(path).then((w) => {
            this.spreadsheet.workbook = w;

            const sheet: Worksheet = this.spreadsheet.workbook.worksheets(0);

            sheet.defaultColumnWidth = 450 * 20;
            sheet.rows(0).height = 150 * 20;

            const cell1: WorksheetCell = sheet.getCell('A1');
            const cell2: WorksheetCell = sheet.getCell('B1');
            const cell3: WorksheetCell = sheet.getCell('C1');
            const cell4: WorksheetCell = sheet.getCell('D1');

            const dataCellAddress = 'A3:D6';

            const title: ChartTitle = new ChartTitle();
            title.text = new FormattedString('Line Chart');
            const chart1 = sheet.shapes().addChart(ChartType.Line, cell1, { x: 5, y: 5 }, cell1, { x: 90, y: 90 });
            chart1.chartTitle = title;
            chart1.setSourceData(dataCellAddress, true);

            const title2: ChartTitle = new ChartTitle();
            title2.text = new FormattedString('Column Chart');
            const chart2 = sheet.shapes().addChart(ChartType.ColumnClustered, cell2, { x: 5, y: 5 }, cell2, { x: 90, y: 90 });
            chart2.chartTitle = title2;
            chart2.setSourceData(dataCellAddress, true);

            const title3: ChartTitle = new ChartTitle();
            title3.text = new FormattedString('Area Chart');
            const chart3 = sheet.shapes().addChart(ChartType.Area, cell3, { x: 5, y: 5 }, cell3, { x: 90, y: 90 });
            chart3.chartTitle = title3;
            chart3.setSourceData(dataCellAddress, true);

            const title4: ChartTitle = new ChartTitle();
            title4.text = new FormattedString('Pie Chart');
            const chart4 = sheet.shapes().addChart(ChartType.Pie, cell4, { x: 5, y: 5 }, cell4, { x: 90, y: 90 });
            chart4.chartTitle = title4;
            chart4.setSourceData(dataCellAddress, true);
        });
    }
}

new SpreadsheetAdapter();
tsCOPY CODE

<!DOCTYPE html>
<html>
  <head>
    <title>SpreadsheetAdapter</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">
    <igc-spreadsheet id="spreadsheet" width="100%" height="100%">

    </igc-spreadsheet>

</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>htmlCOPY CODE

/* shared styles are loaded from: */
/* https://static.infragistics.com/xplatform/css/samples */cssCOPY CODE

Edit in: Codesandbox

Like this sample? Get access to our complete Ignite UI for Web Components toolkit and start building your own apps in minutes. Download it for free.

Chart Adapter Overview

Using chartAdapter you can display the charts in the spreadsheet. The spreadsheet chart adapters creates and initializes chart elements for the spreadsheet based on a Infragistics.Documents.Excel.WorksheetChart instance.

In order to add a WorksheetChart to a worksheet, you must use the addChart method of the worksheet’s Shapes collection.You can find more detail of adding charts in Excel below.

Here are the steps by step description :

1. Add the SpreadsheetChartAdapterModule reference to your project
2. Create an instance of a SpreadsheetChartAdapter class assigning it to the Spreadsheet
3. Run your app and load a worksheet containing a chart.

Supported Charts Types

There are over 35 chart types supported by the Spreadsheet ChartAdapters including, Line, Area, Column, and Doughnut. See the full list here:

• Column Charts
  • Clustered column
  • Stacked column
  • 100% stacked column
• Line Charts
  • Line
  • Line with Markers
  • Stacked line
  • Stacked line with markers
  • 100% stacked line
  • 100% stacked line with markers
• Pie Charts
• Donut Charts
• Bar Charts
  • Clustered bar
  • Stacked bar
  • 100% stacked bar
  • Area Charts
  • Area
  • Stacked area
  • 100% stacked area
• XY (Scatter) and Bubble Charts
  • Scatter (with Marker only)
  • Scatter with smooth lines
  • Scatter with smooth lines and markers
  • Scatter with straight lines
  • Scatter with straight lines and markers
  • Bubble (without effects)
  • Bubble3DEffect
• Stock Charts
  • High-low-close
  • Open-high-low-close
  • Volume-high-low-close
  • Volume-open-high-low-close
• Radar Charts
  • Radar without markers
  • Radar with markers
  • Filled Radar
• Combo Charts
  • Column and line chart sharing xAxis
  • Column and line chart and 2nd xAxis
  • Stacked Area and Column
  • Custom Combination

Dependencies

In the following code snippet, an external ExcelUtility class is used to save and load a workbook.

When setting up your Web Components spreadsheet control to add charts, you will need to import the SpreadsheetChartAdapter class like so:

// Module Manager for registering the modules of the chart
import { ModuleManager } from 'igniteui-webcomponents-core';

import { IgcExcelXlsxModule } from 'igniteui-webcomponents-excel';
import { IgcExcelCoreModule } from 'igniteui-webcomponents-excel';
import { IgcExcelModule } from 'igniteui-webcomponents-excel';

import { IgcSpreadsheetChartAdapterModule } from 'igniteui-webcomponents-spreadsheet-chart-adapter';
import { SpreadsheetChartAdapter } from 'igniteui-webcomponents-spreadsheet-chart-adapter';

import { ExcelUtility } from "ExcelUtility";

import { Worksheet } from 'igniteui-webcomponents-excel';
import { WorksheetCell } from 'igniteui-webcomponents-excel';
import { ChartType, ChartTitle, FormattedString, Workbook } from 'igniteui-webcomponents-excel';

// register the modules
ModuleManager.register(
    IgcExcelCoreModule,
    IgcExcelModule,
    IgcExcelXlsxModule,
    IgcSpreadsheetModule,
    IgcSpreadsheetChartAdapterModule
);
ts

Code Snippet

The following code snippet demonstrates how to add charts to the currently viewed worksheet in the IgcSpreadsheetComponent control:

this.spreadsheet.chartAdapter = new SpreadsheetChartAdapter();

ExcelUtility.loadFromUrl(process.env.PUBLIC_URL + "/ExcelFiles/ChartData.xlsx").then((w) => {
    this.spreadsheet.workbook = w;

    const sheet: Worksheet = this.spreadsheet.workbook.worksheets(0);

    sheet.defaultColumnWidth = 500 * 20;
    sheet.rows(0).height = 150 * 20;

    const cell1: WorksheetCell = sheet.getCell("A1");
    const cell2: WorksheetCell = sheet.getCell("B1");
    const cell3: WorksheetCell = sheet.getCell("C1");
    const cell4: WorksheetCell = sheet.getCell("D1");

    const dataCellAddress = "A4:D6";

    const chart1 = sheet.shapes().addChart(ChartType.Line, cell1, { x: 0, y: 0 }, cell1, { x: 100, y: 100 });

    const title: Web Components ChartTitle = new ChartTitle();
    title.text = new FormattedString("Line Chart");
    chart1.chartTitle = title;

    chart1.setSourceData(dataCellAddress, true);

    const chart2 = sheet.shapes().addChart(ChartType.ColumnClustered, cell2, { x: 0, y: 0 }, cell2, { x: 100, y: 100 });

    const title2: ChartTitle = new ChartTitle();
    title2.text = new FormattedString("Column Chart");
    chart2.chartTitle = title2;

    chart2.setSourceData(dataCellAddress, true);

    const chart3 = sheet.shapes().addChart(ChartType.Area, cell3, { x: 0, y: 0 }, cell3, { x: 100, y: 100 });

    const title3: ChartTitle = new ChartTitle();
    title3.text = new FormattedString("Area Chart");
    chart3.chartTitle = title3;

    chart3.setSourceData(dataCellAddress, true);

    const chart4 = sheet.shapes().addChart(ChartType.Pie, cell4, { x: 0, y: 0 }, cell4, { x: 100, y: 100 });

    const title4: ChartTitle = new ChartTitle();
    title4.text = new FormattedString("Pie Chart");
    chart4.chartTitle = title4;

    chart4.setSourceData(dataCellAddress, true);
});
typescript

API References

• addChart
• chartAdapter
• SpreadsheetChartAdapter
• IgcSpreadsheetComponent
• workbook