Skip to main content

DHTMLX Pivot. Export to XLSX, CSV example

Demo of exporting DHTMLX Pivot data to XLSX and CSV formats. Use the table API's exec method with custom file names, cell styles, separators, and header/footer options for flexible data export.

Live example​

const widget = new pivot.Pivot("#pivot", {
    fields,
    data: dataset,
    tableShape: {
        sizes: {
            rowHeight: 24,
            headerHeight: 24,
            footerHeight: 24,
            columnWidth: 220,
        },
        totalRow: true
    },
    config: {
        rows: [
            "studio",
        ],
        columns: [
            "type"
        ],
        values: [
            {
                field: "title",
                method: "count"
            },
            {
                field: "score",
                method: "max"
            },
        ]
    }
});

const table = widget.api.getTable();

function runExport(type) {
    if (type === "xlsx") {
        table.exec("export", {
            options: {
                format: "xlsx",
                fileName: "results", // a name of file, "data" by default
                header: false, // export header or not, true by default
                footer: false, // export footer or not, true by default
                styles: {
                    cell: { // names of style properties should be written in camelCase
                        fontWeight: "bold",
                        color: "#000000",
                        background: "#fafafb",
                    },
                    header: { /* style propeties header cells */ },
                    footer: { /* style propeties for footer cells */ },
                    lastHeaderCell: { /* style propeties for the last row of header cells */ },
                    firstFooterCell: { /* style propeties the first row of footer cells */ },
                }
            },
        });
    }
    if (type === "csv") {
        table.exec("export", {
            options: {
                format: "csv",
                fileName: "results", // a name of file, "data" by default
                rows: "|", // a separator between rows, newline "\n" by default
                cols: ";", // a separator between columns, tab "\t" by default
                header: false, // export header or not, true by default
                footer: false, // export footer or not, true by default
                download: true // if set to false, file will not be downloaded, CSV data (Blob) will be available as ev.result, true by default
            },
        });
    }
};
<!-- auxiliary controls for interacting with the sample -->
<link rel="stylesheet" href="https://snippet.dhtmlx.com/codebase/assets/css/auxiliary_controls.css">
<section class="dhx_sample-controls">
  <button class="dhx_sample-btn dhx_sample-btn--flat" onclick="runExport('xlsx')">
    Export xlsx
  </button>
  <button class="dhx_sample-btn dhx_sample-btn--flat" onclick="runExport('csv')">
    Export csv
  </button>
</section>

<!-- component container -->
<div id="pivot" style="width: 100%; height: calc(100% - 60px);"></div>

<!-- dataset -->

<script>
const dataset = [
      {
        "rank": 1,
        "title": "Shingeki no Kyojin: The Final Season - Kanketsu-hen",
        "popularity": 609,
        "genre": "Action",
        "studio": "MAPPA",
        "type": "Special",
        "episodes": 2,
        "duration": 61,
        "members": 347875,
        "score": 9.17
      },
      {
        "rank": 2,
        "title": "Fullmetal Alchemist: Brotherhood",
        "popularity": 3,
        "genre": "Action",
        "studio": "Bones",
        "type": "TV",
        "episodes": 64,
        "duration": 24,
        "members": 3109951,
        "score": 9.11
      },
      // ... 246 more items (see Live Editor for full data)
    ]; 
    
    const fields = [
      {
        "id": "rank",
        "label": "Rank",
        "type": "number"
      },
      {
        "id": "title",
        "label": "Title",
        "type": "text"
      },
      {
        "id": "popularity",
        "label": "Popularity",
        "type": "number"
      },
      {
        "id": "genre",
        "label": "Genre",
        "type": "text"
      },
      {
        "id": "studio",
        "label": "Studio",
        "type": "text"
      },
      {
        "id": "type",
        "label": "Type",
        "type": "text"
      },
      {
        "id": "episodes",
        "label": "Episodes",
        "type": "number"
      },
      {
        "id": "duration",
        "label": "Duration",
        "type": "number"
      },
      {
        "id": "members",
        "label": "Members",
        "type": "number"
      },
      {
        "id": "score",
        "label": "Score",
        "type": "number"
      }
    ];
</script>

Pivot table data often needs to be shared with stakeholders who prefer working in spreadsheet applications or need the data in a portable format for further analysis. Manually copying data from a web-based pivot table is error-prone and tedious, especially with large datasets that include totals and multiple column groups.

DHTMLX Pivot provides built-in export functionality through the table API's exec("export") command. It supports two formats: XLSX for full-featured spreadsheets with styling, and CSV for lightweight text-based exports. Both formats offer extensive customization - you can control the file name, include or exclude headers and footers, apply cell styles for XLSX, and customize row/column separators for CSV.

This example demonstrates both export options on an anime dataset with a total row enabled. The XLSX export applies custom cell styles (bold font, specific colors and background) and excludes headers and footers. The CSV export uses pipe (|) as the row separator and semicolon (;) as the column separator instead of the defaults. Two buttons trigger the respective exports, downloading the file immediately.

Solution overview​

To export pivot data, get the table API via widget.api.getTable(), then call table.exec("export") with an options object specifying the format and customization settings. The XLSX format supports cell styling, while CSV supports custom separators.

Key points​

  • Two export formats: Use format: "xlsx" for styled spreadsheets or format: "csv" for lightweight text exports - both triggered through the same table.exec("export") API
  • XLSX cell styling: The styles object supports cell, header, footer, lastHeaderCell, and firstFooterCell targets with CSS-like properties in camelCase (fontWeight, color, background)
  • CSV separator control: Customize row separators (rows: "|") and column separators (cols: ";") for CSV exports to match the requirements of downstream systems
  • Header/footer toggle: Set header: false and footer: false to export only the data rows, excluding column headers and total rows from the output file

API reference​

Additional resources​