Dash AG-Grid: Export Data As Excel

Dash Python
5

Here are some more examples!

Excel Export to Multiple Sheets With AG Grid Enterprise

Note - this is a AG Grid Enterprise features. If you are using AG Grid Community, see the examples below.

This example is from the AG Grid docs

  • The exported Excel file will contain one sheet for each sport result.
  • Each sheet is exported using the sport name as the name of the sheet.
import dash_ag_grid as dag
from dash import Dash, html, Input, State, Output, clientside_callback, ClientsideFunction
import pandas as pd


app = Dash(__name__)

df = pd.read_csv(
    "https://raw.githubusercontent.com/plotly/datasets/master/ag-grid/olympic-winners.csv"
)

columnDefs = [
    {
        "headerName": "Athlete Details",
        "children": [
            {"field": "athlete", "width": 180},
            {"field": "age", "width": 90},
            {"field": "country", "width": 140},
        ],
    },
    {
        "headerName": "Sports Results",
        "children": [
            {"field": "sport", "width": 140},
            {"field": "total", "width": 100, "filter": "agNumberColumnFilter", "columnGroupShow": "closed"},
            {"field": "gold", "width": 100, "filter": "agNumberColumnFilter", "columnGroupShow": "open"},
            {"field": "silver", "width": 100, "filter": "agNumberColumnFilter", "columnGroupShow": "open"},
            {"field": "bronze", "width": 100, "filter": "agNumberColumnFilter", "columnGroupShow": "open"},
        ],
    },
]

app.layout = html.Div(
    [
        html.Button("Export to Excel", id="btn-excel-export-multiple-sheets"),
        dag.AgGrid(
            id="grid-excel-export-multiple-sheets",
            rowData=df.to_dict("records"),
            columnDefs=columnDefs,
            defaultColDef={"resizable": True, "filter": True},
            enableEnterpriseModules=True,

        ),
    ]
)

clientside_callback(
    ClientsideFunction(
        namespace='clientside',
        function_name='onBtnExport'
    ),
    Output("btn-excel-export-multiple-sheets", "n_clicks"),
    Input("btn-excel-export-multiple-sheets", "n_clicks"),
    State("grid-excel-export-multiple-sheets", "id"),
    prevent_initial_call=True
)


if __name__ == "__main__":
    app.run(debug=True)

Put this in a .js file in the /assets folder:


onBtnExport = (n, id) => {
    var gridApi = dash_ag_grid.getApi(id)
    var sports = {};
    gridApi.forEachNode(function (node) {
      if (!sports[node.data.sport]) {
        sports[node.data.sport] = true;
      }
    });
    var spreadsheets = [];
    var sportFilterInstance = gridApi.getFilterInstance('sport');
    for (var sport in sports) {
      sportFilterInstance.setModel({ values: [sport] });
      gridApi.onFilterChanged();
      if (sportFilterInstance.getModel() == null) {
        throw new Error('Example error: Filter not applied');
      }
      const sheet = gridApi.getSheetDataForExcel({
        sheetName: sport,
      });
      if (sheet) {
        spreadsheets.push(sheet);
      }
    }
    sportFilterInstance.setModel(null);
    gridApi.onFilterChanged();
    gridApi.exportMultipleSheetsAsExcel({
      data: spreadsheets,
      fileName: 'ag-grid.xlsx',
    });
    spreadsheets = [];
    return dash_clientside.no_update
  };


window.dash_clientside = Object.assign({}, window.dash_clientside, {
    clientside: {
        onBtnExport
    }
});

Excel Export to Multiple Sheets With AG Grid Community

This is the “free” version using AG Grid Community. It uses the dcc.Download component, which means you don’t need to write clientside callbacks and JavaScript functions.

For more info see:

Like the Enterprise version above:

  • The exported Excel file will contain one sheet for each sport result.
  • Each sheet is exported using the sport name as the name of the sheet.

import dash_ag_grid as dag
from dash import Dash, html, dcc, Input, Output
import pandas as pd


app = Dash(__name__)

df = pd.read_csv(
    "https://raw.githubusercontent.com/plotly/datasets/master/ag-grid/olympic-winners.csv"
)

columnDefs = [
    {
        "headerName": "Athlete Details",
        "children": [
            {"field": "athlete", "width": 180},
            {"field": "age", "width": 90},
            {"field": "country", "width": 140},
        ],
    },
    {
        "headerName": "Sports Results",
        "children": [
            {"field": "sport", "width": 140},
            {"field": "total", "width": 100, "filter": "agNumberColumnFilter", "columnGroupShow": "closed"},
            {"field": "gold", "width": 100, "filter": "agNumberColumnFilter", "columnGroupShow": "open"},
            {"field": "silver", "width": 100, "filter": "agNumberColumnFilter", "columnGroupShow": "open"},
            {"field": "bronze", "width": 100, "filter": "agNumberColumnFilter", "columnGroupShow": "open"},
        ],
    },
]

app.layout = html.Div(
    [
        dcc.Download(id="download-dataframe-xlsx"),
        html.Button("Export to Excel", id="btn-excel-export-multiple-sheets"),
        dag.AgGrid(
            id="grid-excel-export-multiple-sheets",
            rowData=df.to_dict("records"),
            columnDefs=columnDefs,
            defaultColDef={"resizable": True, "filter": True},
        ),
    ]
)

@app.callback(
    Output('download-dataframe-xlsx', 'data'),
    Input('btn-excel-export-multiple-sheets', 'n_clicks'),
    prevent_initial_call=True
)
def export_data_to_excel(n):

    # Create a Pandas Excel writer using XlsxWriter as the engine.
    writer = pd.ExcelWriter('new_excel_file.xlsx', engine ='xlsxwriter')

    # Write each dataframe to a different worksheet.
    for sport in df.sport.unique():
        dff =  df[df.sport==sport]
        dff.to_excel(writer,sheet_name=sport)

    # Close the Pandas Excel writer and output the Excel file.
    writer.save()

    return dcc.send_file('new_excel_file.xlsx')


if __name__ == "__main__":
    app.run(debug=True)

Excel Export Filtered and Sorted Data With AG Grid Community

Here’s an example where the user can export data to Excel. Note that it will export data after sorting and filtering by using the virtualRowData prop in the callback.



import dash_ag_grid as dag
from dash import Dash, html, dcc, Input, Output, State
import pandas as pd


app = Dash(__name__)

df = pd.read_csv(
    "https://raw.githubusercontent.com/plotly/datasets/master/ag-grid/olympic-winners.csv"
)

app.layout = html.Div(
    [
        dcc.Download(id="download-dataframe-xlsx"),
        html.Div("Excel File Name"),
        dcc.Input(id="excel-file-name", value="ag_grid_download.xlsx"),
        html.Button("Export to Excel", id="btn-excel-export"),
        dag.AgGrid(
            id="grid-excel-export",
            rowData=df.to_dict("records"),
            columnDefs=[{"field": c} for c in df.columns],
            defaultColDef={"resizable": True, "filter": True, "sortable": True},
        ),
    ]
)

@app.callback(
    Output('download-dataframe-xlsx', 'data'),
    Input('btn-excel-export', 'n_clicks'),
    State('excel-file-name', 'value'),
    State('grid-excel-export', "virtualRowData"),
    prevent_initial_call=True
)
def export_data_to_excel(n, filename, data):
    dff = pd.DataFrame(data)    
    return dcc.send_data_frame(dff.to_excel, filename, sheet_name="Sheet1")


if __name__ == "__main__":
    app.run(debug=True)

dag-docs

1 Like