Exporting Pivot

The content of dhtmlxPivot can be easily exported to the Excel or CSV format.

Export to Excel

To export Pivot to an Excel file make use of the xlsx() method of the Export module. As a parameter, you should pass an object with export settings:

  • name - (string) the name of the exported Excel file.
  • url - (string) the URL that will execute export.
pivot.export.xlsx({
    name:"pivot_data",
    url:"//export.dhtmlx.com/excel"
});

Returned object

The xlsx() method returns an object with data and export parameters:

var data = {
    name:"pivot_data",
    columns:[
        { width: 100 },
        { width: 100 }
        // more objects
    ],
    header:[
        [" ", " ", "Q1", " ", "Q2", " "],
        [" ", " ", "Oil (Max)", "Oil (Min)", "Oil (Max)", "Oil (Min)"]
    ],
    data:[
        ["Constitutional monarchy", " ", "78.276", "2.946", "80.259", "5.564"],
        [" ", 2012, "23.650", "23.650", "80.259", "51.127"],
        // more arrays with cells values
    ],
    styles:[
        css:{
            major:{ color: "#ff00ff", background: "#0000AA", fontSize: 14 }
        },
        cells:[
            [1,2,"major"]
        ]
    ]
}

Below you will find the details on each of the data parameters:

  • the name of the exported Excel file
var data = {
    name:"pivot_data"
}
  • columns configuration set as an array of objects. Each object stores the width of a column:
var data = {
    columns:[
        { width: 100 },
        { width: 100 }
        // more objects
    ]
}
  • column headers set as a matrix of header cells, null values are provided for cells in colspans or rowspans:
var data = {
    header:[
        [" ", " ", "Q1", " ", "Q2", " "],
        [" ", " ", "Oil (Max)", "Oil (Min)", "Oil (Max)", "Oil (Min)"]
    ]
}
  • data defined as a matrix, where each cell stores a single value - the value of the related cell:
var data = {
    data:[
        ["Constitutional monarchy", " ", "78.276", "2.946", "80.259", "5.564"],
        [" ", 2012, "23.650", "23.650", "80.259", "51.127"],
        [" ", 2013, "71.218", "2.946", "71.663", "47.657"],
        // more arrays with cells values
    ]
}
  • the styles array that includes:
    • a set of styles. It is specified as a css object. Each style is defined as a hash of key:value pairs, where the key is the name of the style and the value is an object with the style attributes
    • a set of cells. It is specified as a cells matrix. Each cell is defined as an array that contains three elements: the number of a column, the number of a row, and the name of a CSS class applied to this cell (from the css object)
var data = {
    styles:[
        css:{
            major:{ color: "#ff00ff", background: "#0000AA", fontSize: 14 }
        },
        cells:[
            [1,2,"major"]
        ]
    ]
}

Related sample:  Export - DHTMLX Pivot

Export to CSV

You can export data from Pivot to the CSV format with the csv() method of the Export module. The method takes an object with export settings as a parameter:

  • asFile - (boolean) defines whether Pivot should be exported to a file, true by default. To export Pivot only as a CSV string, you need to set asFile:false.
  • name - (string) the name of the exported CSV file (if asFile is not set to false).
  • flat - (boolean) defines whether Pivot data should be exported as a flat structure. false by default.
  • rowDelimiter - (string) a newline ("\n") by default. A separator between rows, can be a tab - "\t", or any other value.
  • columnDelimiter - (string) a comma (",") by default. A separator between columns, can be a semicolon - ";", or any other value.
pivot.export.csv({
    name:"pivot_data", // pivot data will be exported to a CSV file named "pivot_data"
    flat:true, // pivot data will be presented as a flat structure
    rowDelimiter: "\t", // the tab delimiter will be used to separate rows
    columnDelimiter: ";" // the semicolon delimiter will be used to separate columns
});

The csv() method returns a CSV string with Pivot data.

Related sample:  Export - DHTMLX Pivot

Back to top