I know we are currently capable of using exportDataAsCsv as a callback property for exporting or right-clicking the table to export as CSV or Excel with enterprise (which I have), but I am trying to add a button that allows exporting of an excel file rather than CSV file. Just feels strange that there is an implemented callback property for exportDataAsCsv but not exportDataAsExcel like other versions of AG-Grid (React, Angular, etc.)
Hi @Maxx and welcome to the Dash community 
This is available now - it’s just not documented yet.
Be sure to update to the latest version of dash-ag-grid (currently V2.3.0)
Here are a few examples to get you started:
import dash_ag_grid as dag
from dash import Dash, html, Input, Output, clientside_callback
import pandas as pd
import os
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"),
dag.AgGrid(
id="grid-excel-export",
rowData=df.to_dict("records"),
columnDefs=columnDefs,
defaultColDef={"resizable": True, "filter": True},
enableEnterpriseModules=True,
),
]
)
clientside_callback(
"""function (n) {
if (n) {
dash_ag_grid.getApi("grid-excel-export").exportDataAsExcel();
}
return dash_clientside.no_update
}""",
Output("btn-excel-export", "n_clicks"),
Input("btn-excel-export", "n_clicks"),
prevent_initial_call=True
)
if __name__ == "__main__":
app.run(debug=True)
Here’s the example from the docs with styled headers:
import dash_ag_grid as dag
from dash import Dash, html, Input, Output, clientside_callback
import pandas as pd
import os
app = Dash(__name__)
df = pd.read_csv(
"https://raw.githubusercontent.com/plotly/datasets/master/ag-grid/olympic-winners.csv"
)
excelStyles = [
{
"id": "header",
"alignment": {
"vertical": "Center",
},
"interior": {
"color": "#f8f8f8",
"pattern": "Solid",
"patternColor": "undefined",
},
"borders": {
"borderBottom": {
"color": "#ffab00",
"lineStyle": "Continuous",
"weight": 1,
},
},
},
{
"id": "headerGroup",
"font": {
"bold": "true",
},
},
{
"id": "gold-header",
"interior": {
"color": "#E4AB11",
"pattern": "Solid",
},
},
{
"id": "silver-header",
"interior": {
"color": "#bbb4bb",
"pattern": "Solid",
},
},
{
"id": "bronze-header",
"interior": {
"color": "#be9088",
"pattern": "Solid",
},
},
]
columnDefs = [
{"field": "athlete"},
{"field": "sport", "minWidth": 150},
{
"headerName": "Medals",
"children": [
{"field": "gold", "headerClass": "gold-header"},
{"field": "silver", "headerClass": "silver-header"},
{"field": "bronze", "headerClass": "bronze-header"},
],
},
]
defaultColDef = {
"sortable": True,
"filter": True,
"resizable": True,
"minWidth": 100,
"flex": 1,
}
app.layout = html.Div(
[
html.Button("Export to Excel", id="btn-excel-header-style"),
dag.AgGrid(
id="grid-excel-header-style",
rowData=df.to_dict("records"),
columnDefs=columnDefs,
defaultColDef=defaultColDef,
dashGridOptions={
"excelStyles": excelStyles,
"defaultExcelExportParams": {"headerRowHeight": 30},
},
enableEnterpriseModules=True,
),
],
className="excel-header-style",
)
clientside_callback(
"""function (n) {
if (n) {
dash_ag_grid.getApi("grid-excel-header-style").exportDataAsExcel();
}
return dash_clientside.no_update
}""",
Output("btn-excel-header-style", "n_clicks"),
Input("btn-excel-header-style", "n_clicks"),
prevent_initial_call=True,
)
if __name__ == "__main__":
app.run(debug=True)
Here’s an example with formulas exported too:
import dash_ag_grid as dag
from dash import Dash, html, Input, Output, clientside_callback
import os
app = Dash(__name__)
rowData = [
{"firstName": "Mair", "lastName": "Inworth", "age": 23, "company": "Rhyzio"},
{"firstName": "Clair", "lastName": "Cockland", "age": 38, "company": "Vitz"},
{"firstName": "Sonni", "lastName": "Jellings", "age": 24, "company": "Kimia"},
{
"firstName": "Kit",
"lastName": "Clarage",
"age": 27,
"company": "Skynoodle",
},
{
"firstName": "Tod",
"lastName": "de Mendoza",
"age": 29,
"company": "Teklist",
},
{"firstName": "Herold", "lastName": "Pelman", "age": 23, "company": "Divavu"},
{"firstName": "Paula", "lastName": "Gleave", "age": 37, "company": "Demimbu"},
{"firstName": "Seumas", "lastName": "Pouck", "age": 34, "company": "Aimbu"},
{"firstName": "Luigi", "lastName": "Rive", "age": 25, "company": "Quatz"},
{"firstName": "Carolyn", "lastName": "Blouet", "age": 29, "company": "Eamia"},
]
excelStyles = [
{
"id": "fullName",
"dataType": "Formula",
}
]
columnDefs = [
{"field": "firstName"},
{"field": "lastName"},
{
"headerName": "Full Name",
"colId": "fullName",
"cellClass": "fullName",
"valueGetter": {
"function": "`${params.data.firstName} ${params.data.lastName}`"
},
},
{"field": "age"},
{"field": "company"},
]
defaultColDef = {
"sortable": True,
"filter": True,
"resizable": True,
"minWidth": 100,
"flex": 1,
}
app.layout = html.Div(
[
html.Button("Export to Excel", id="btn-excel-formula-data-type"),
dag.AgGrid(
id="grid-excel-formula-data-type",
rowData=rowData,
columnDefs=columnDefs,
defaultColDef=defaultColDef,
dashGridOptions={
"excelStyles": excelStyles,
},
enableEnterpriseModules=True,
),
],
)
app.clientside_callback(
"""async function (id) {
grid = await dash_ag_grid.getApiAsync(id)
grid.gridOptionsService.gridOptions.defaultExcelExportParams = defaultExcelExportParams
return window.dash_clientside.no_update
}""",
Output("grid-excel-formula-data-type", "id"),
Input("grid-excel-formula-data-type", "id"),
)
clientside_callback(
"""async function (n) {
if (n) {
grid = await await dash_ag_grid.getApiAsync("grid-excel-formula-data-type")
grid.exportDataAsExcel(defaultExcelExportParams);
}
return dash_clientside.no_update
}""",
Output("btn-excel-formula-data-type", "n_clicks"),
Input("btn-excel-formula-data-type", "n_clicks"),
prevent_initial_call=True,
)
if __name__ == "__main__":
app.run(debug=True)
5 Likes
Thanks for the hasty reply this works great! However, for the first example I had to turn it into an async function to get it to work correctly
async function (n) { if (n) { grid = await dash_ag_grid.getApiAsync("grid-excel-export") grid.exportDataAsExcel(); } return dash_clientside.no_update
Yes, you need the asyn function for it to work with both the context menu and/or the button
1 Like
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
@AnnMarieW I saw in AG Grid official documentation there is feature exporting multiple grids into multiple sheets in one excel file. I am trying to implement similar features in the Dash Grid. Do you know how to implement this? Any examples will be really appreciated?
Hi @hwang and welcome to the Dash community
Yes, it’s possible to export multiple grids to multiple sheets. It works similar to the examples above. If you have AG Grid Enterprise you can access each grid’s API and call the getSheetDataForExcel method to get the data and store it in an array. Then call the getMultipleSheetsAsExcel to create a single Excel workbook.
Note that it’s necessary to write this function in JavaScript in a clientside callback. However, even if you have AG Grid Enterprise, you could just use the dcc.Download component instead – no JavaScript required The only disadvantage is that it just downloads data, and doesn’t have some of the advanced features of the AG Grid Enterprise methods such styling Excel so it looks the same as the grid in the app.
You can find more information on this page of the AG Grid docs.
I think the example in the AG Grid docs above is a little more complicated than it needs to be, since it demos downloading data to Excel in two grids where you can also also drag rows from one grid to another. If you want to do that as well, you can learn more in the dash docs Row Dragging section.
In the two examples below, I’ll demo how to take two simple grids and download them using the AG Grid Enterprise methods or the AG Grid Community way. These grids are set so you can sort, filter and edit, and it will download the state of the data at the time you click the download button.
If you have more questions, please feel free to ask
Excel Export Multiple Grids to Multiple Sheets With AG Grid Enterprise
from dash import Dash, html, Input, Output, clientside_callback
import dash_ag_grid as dag
import plotly.express as px
df1 = px.data.tips()
df2 = px.data.wind()
app = Dash(__name__)
grid1 = dag.AgGrid(
id = "grid1",
rowData=df1.to_dict("records"),
columnDefs=[{"field": c} for c in df1.columns],
defaultColDef={"filter": True, "sortable": True, "editable":True},
columnSize="sizeToFit",
enableEnterpriseModules=True,
)
grid2 = dag.AgGrid(
id = "grid2",
rowData=df2.to_dict("records"),
columnDefs=[{"field": c} for c in df2.columns],
defaultColDef={"filter": True, "sortable": True, "editable":True},
columnSize="sizeToFit",
enableEnterpriseModules=True,
)
app.layout=html.Div(
[
html.Button("Export grids to Excel", id="btn-export"),
grid1, grid2
]
)
clientside_callback(
"""async function (n) {
if (n) {
grid1Api = await dash_ag_grid.getApiAsync("grid1")
grid2Api = await dash_ag_grid.getApiAsync("grid2")
var spreadsheets = [];
spreadsheets.push(
grid1Api.getSheetDataForExcel({ sheetName: 'Grid1' }),
grid2Api.getSheetDataForExcel({
sheetName: 'Grid2',
})
);
grid1Api.exportMultipleSheetsAsExcel({
data: spreadsheets,
fileName: 'ag-grid2.xlsx',
});
}
return dash_clientside.no_update
}""",
Output("btn-export", "n_clicks"),
Input("btn-export", "n_clicks"),
prevent_initial_call=True
)
if __name__ == "__main__":
app.run(debug=True)
Excel Export Multiple Grids to Multiple Sheets With AG Grid Community
from dash import Dash, dcc, html, Input, Output, State
import dash_ag_grid as dag
import plotly.express as px
import pandas as pd
df1 = px.data.tips()
df2 = px.data.wind()
app = Dash(__name__)
grid1 = dag.AgGrid(
id="grid1",
rowData=df1.to_dict("records"),
columnDefs=[{"field": c} for c in df1.columns],
defaultColDef={"filter": True, "sortable": True, "editable": True},
columnSize="sizeToFit",
)
grid2 = dag.AgGrid(
id="grid2",
rowData=df2.to_dict("records"),
columnDefs=[{"field": c} for c in df2.columns],
defaultColDef={"filter": True, "sortable": True, "editable": True},
columnSize="sizeToFit",
)
app.layout = html.Div(
[
dcc.Download(id="download-dataframe-xlsx"),
html.Button("Export grids to Excel", id="btn-export"),
grid1, grid2
]
)
@app.callback(
Output('download-dataframe-xlsx', 'data'),
Input('btn-export', 'n_clicks'),
State('grid1', 'virtualRowData'),
State('grid2', 'virtualRowData'),
prevent_initial_call=True
)
def export_data_to_excel(n,rowData1, rowData2):
dff1 = pd.DataFrame(rowData1)
dff2 = pd.DataFrame(rowData2)
# 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.
dff1.to_excel(writer,sheet_name="grid1")
dff2.to_excel(writer, sheet_name="grid2")
# 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)
Hi @AnnMarieW Thanks for your quick reply. I guess the first method is exactly what we need. We would like to export grid directly.
Also, just out of curiosity, are there any ways to export multiple grids into one excel sheet (one excel sheet display multiple grids)?
Thanks again for your kind help.
You’re welcome @hwang - I’m happy to help.
I didn’t see any examples in the AG Grid docs or on StackOverflow for multiple grids on one sheet. Since you have AG Grid Enterprise, maybe you could ask their support team? If you get an answer using React, I could help adapt it for Dash.
I know for me, when I am trying to combine stuff into multiple sheets in a single workbook, I use Dash to do so.
Here is an example as recommended by @jinnyzor for downloading multiple grids into a single Excel sheet.
This uses AG Grid Community and the dcc.Download component.
from dash import Dash, dcc, html, Input, Output, State
import dash_ag_grid as dag
import plotly.express as px
import pandas as pd
df1 = px.data.tips()
df2 = px.data.wind()
app = Dash(__name__)
grid1 = dag.AgGrid(
id="grid1",
rowData=df1.to_dict("records"),
columnDefs=[{"field": c} for c in df1.columns],
defaultColDef={"filter": True, "sortable": True, "editable": True},
columnSize="sizeToFit",
)
grid2 = dag.AgGrid(
id="grid2",
rowData=df2.to_dict("records"),
columnDefs=[{"field": c} for c in df2.columns],
defaultColDef={"filter": True, "sortable": True, "editable": True},
columnSize="sizeToFit",
)
app.layout = html.Div(
[
dcc.Download(id="download-dataframe-xlsx"),
html.Button("Export grids to Excel", id="btn-export"),
grid1, grid2
]
)
@app.callback(
Output('download-dataframe-xlsx', 'data'),
Input('btn-export', 'n_clicks'),
State('grid1', 'virtualRowData'),
State('grid2', 'virtualRowData'),
prevent_initial_call=True
)
def export_data_to_excel(n,rowData1, rowData2):
dff1 = pd.DataFrame(rowData1)
dff2 = pd.DataFrame(rowData2)
# 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.
dff1.to_excel(writer,sheet_name="grids")
dff2.to_excel(writer, sheet_name="grids", startrow=len(dff1) + 5)
# 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)
1 Like
Hi May I ask how you are doing that?
This way in more like writing dataframe to excel instead of grids? In that way only concern I has is how can I keep the style or format when downloading as excel or csv?