ASP.NET Core MVC Controls | ComponentOne
Working with Controls / OLAP / Work with OLAP / Excel Export
In This Topic
    Excel Export
    In This Topic

    The PivotGrid control extends the FlexGrid control, so you can export it to any of the formats supported by the extension modules provided with the FlexGrid. The list of the supported formats includes XLSLX, CSV, and PDF. In the example below, it exports the excel file with two sheets: the current view and a transposed version of the current view.

    The image below shows how the OLAP control appears after you implement the code below.

    OLAP Export

    OLAP Export

    The following code examples demonstrate how export OLAP data to excel file. This example uses the sample created in the Quick Start topic.

    (Index.cshtml)

    Razor
    Copy Code
    <c1-pivot-engine id="dataSourceEngine" source-key="dataset10">
    <c1-view-field-collection c1-property="RowFields" items="Country"></c1-view-field-collection>
    <c1-view-field-collection c1-property="ColumnFields" items="Product"></c1-view-field-collection>
    <c1-view-field-collection c1-property="ValueFields" items="Sales"></c1-view-field-collection>
    </c1-pivot-engine>
    <c1-pivot-panel items-source-id="dataSourceEngine"></c1-pivot-panel>
    <c1-pivot-chart items-source-id="dataSourceEngine"></c1-pivot-chart>
    <c1-pivot-grid items-source-id="dataSourceEngine"></c1-pivot-grid>
    
    <button type="button" class="btn btn-default" onclick="excelExport()">Export to XLSX</button>
    @section Scripts{
        <script src="http://cdnjs.cloudflare.com/ajax/libs/jszip/2.5.0/jszip.min.js"></script>
        <script type="text/javascript">
            function excelExport() {
                var pivotGrid = wijmo.Control.getControl('#indexGrid');
                // create book with current view
                var book = wijmo.grid.xlsx.FlexGridXlsxConverter.save(pivotGrid, {
                    includeColumnHeaders: true,
                    includeRowHeaders: true
                });
                book.sheets[0].name = 'Main View';
                addTitleCell(book.sheets[0], getViewTitle(pivotGrid.engine));
                // add sheet with transposed view
                transposeView(pivotGrid.engine);
                var transposed = wijmo.grid.xlsx.FlexGridXlsxConverter.save(pivotGrid, {
                    includeColumnHeaders: true,
                    includeRowHeaders: true
                });
                transposed.sheets[0].name = 'Transposed View';
                addTitleCell(transposed.sheets[0], getViewTitle(pivotGrid.engine));
                book.sheets.push(transposed.sheets[0]);
                transposeView(pivotGrid.engine);
                // save the book
                book.save('wijmo.olap.xlsx');
            }
            // build a title for the current view
            function getViewTitle(ng) {
                var title = '';
                for (var i = 0; i < ng.valueFields.length; i++) {
                    if (i > 0) title += ', ';
                    title += ng.valueFields[i].header;
                }
                title += ' by ';
                if (ng.rowFields.length) {
                    for (var i = 0; i < ng.rowFields.length; i++) {
                        if (i > 0) title += ', ';
                        title += ng.rowFields[i].header;
                    }
                }
                if (ng.rowFields.length && ng.columnFields.length) {
                    title += ' and by ';
                }
                if (ng.columnFields.length) {
                    for (var i = 0; i < ng.columnFields.length; i++) {
                        if (i > 0) title += ', ';
                        title += ng.columnFields[i].header;
                    }
                }
                return title;
            }
            function transposeView(ng) {
                ng.deferUpdate(function () {
                    // save row/col fields
                    var rows = [],
                        cols = [];
                    for (var r = 0; r < ng.rowFields.length; r++) {
                        rows.push(ng.rowFields[r].header);
                    }
                    for (var c = 0; c < ng.columnFields.length; c++) {
                        cols.push(ng.columnFields[c].header);
                    }
                    // clear row/col fields
                    ng.rowFields.clear();
                    ng.columnFields.clear();
                    // restore row/col fields in transposed order
                    for (var r = 0; r < rows.length; r++) {
                        ng.columnFields.push(rows[r]);
                    }
                    for (var c = 0; c < cols.length; c++) {
                        ng.rowFields.push(cols[c]);
                    }
                });
            }
            //adds a title cell into an xlxs sheet
            function addTitleCell(sheet, title) {
                // create cell
                var cell = new wijmo.xlsx.WorkbookCell();
                cell.value = title;
                cell.style = new wijmo.xlsx.WorkbookStyle();
                cell.style.font = new wijmo.xlsx.WorkbookFont();
                cell.style.font.bold = true;
                // create row to hold the cell
                var row = new wijmo.xlsx.WorkbookRow();
                row.cells[0] = cell;
                // and add the new row to the sheet
                sheet.rows.splice(0, 0, row);
            }
        </script>
    }