Export Blazor TreeList Data to XLS/XLSX
- 13 minutes to read
Call the ExportToXlsAsync/ExportToXlsxAsync method to export TreeList data to an Excel format. The output table maintains sort settings and summaries. You can save the result to a stream or download it to the client. The method parameter allows you to configure export settings and customize the sheet’s appearance.
You can also export TreeList data to PDF or CSV formats.
Limitations and Specifics
- Content of templates is not exported.
- Excel permits up to seven nesting levels in outlines. Deeper nesting levels are exported at the seventh nesting level.
- The TreeList exports custom summaries (implemented in the CustomSummary event) as plain text.
- Data columns anchored to the right edge become regular columns, while columns anchored to the component’s left edge remain frozen.
- CSS classes applied to the TreeList and its elements do not affect the exported document’s appearance. Handle the CustomizeCell event to customize the output table.
- In on demand data loading mode, an export operation forces the component to load all data.
If the TreeList is bound to a GridDevExtremeDataSource object, the following limitations apply:
- Filtering and sorting may differ in the resulting document because export depends on database collation.
- You must specify the KeyFieldName property to export only selected rows.
Refer to the following articles for information about Microsoft Excel limitations:
Prevent a Column from Being Exported
The TreeList component exports data from all data columns. Set a column’s ExportEnabled property to false to exclude it from data export:
@inject SpaceObjectDataProvider SpaceObjectDataProvider
<DxButton Text="Export to XLSX" Click="ExportXlsx_Click" />
<DxTreeList @ref="TreeList" Data="TreeListData" ChildrenFieldName="Satellites">
<Columns>
<DxTreeListDataColumn FieldName="Name" />
<DxTreeListDataColumn FieldName="TypeOfObject" Caption="Type" />
<DxTreeListDataColumn FieldName="Mass10pow21kg" Caption="Mass, kg" DisplayFormat="N2">
<HeaderCaptionTemplate>Mass, 10<sup>21</sup> × kg</HeaderCaptionTemplate>
</DxTreeListDataColumn>
<DxTreeListDataColumn FieldName="MeanRadiusInKM" ExportEnabled="false" Caption="Radius, km" DisplayFormat="N2" />
<DxTreeListDataColumn FieldName="Volume10pow9KM3" Caption="Volume, km³" DisplayFormat="N2" />
<DxTreeListDataColumn FieldName="SurfaceGravity" ExportEnabled="false" DisplayFormat="N2" />
</Columns>
</DxTreeList>
@code {
ITreeList TreeList { get; set; }
object TreeListData { get; set; }
protected override async Task OnInitializedAsync() {
TreeListData = SpaceObjectDataProvider.GenerateData();
}
async Task ExportXlsx_Click() {
await TreeList.ExportToXlsxAsync("ExportResult");
}
}
Display/Hide Exported Columns
The TreeList exports data of all data columns unless you assign false to a column’s ExportEnabled property. A column whose Visible property is set to false is exported as a hidden column (has a zero width).
Handle the CustomizeColumn event and specify the Column.IsHidden argument to display/hide columns in the output document:
async Task ExportXlsx_Click() {
await TreeList.ExportToXlsxAsync("ExportResult", new TreeListXlExportOptions() {
CustomizeColumn = CustomizeColumn,
});
}
void CustomizeColumn(TreeListExportCustomizeColumnEventArgs e) {
// Shows every TreeList column in the output document
e.Column.IsHidden = false;
}
Specify Column Width
Use the ExportWidth property to specify the column width in the exported document:
@inject SpaceObjectDataProvider SpaceObjectDataProvider
<DxButton Text="Export to XLSX" Click="ExportXlsx_Click" />
<DxTreeList @ref="TreeList" Data="TreeListData" ChildrenFieldName="Satellites">
<Columns>
<DxTreeListDataColumn FieldName="Name" ExportWidth="150" />
<DxTreeListDataColumn FieldName="TypeOfObject" Caption="Type" ExportWidth="100"/>
<DxTreeListDataColumn FieldName="Mass10pow21kg" Caption="Mass, kg" DisplayFormat="N2">
<HeaderCaptionTemplate>Mass, 10<sup>21</sup> × kg</HeaderCaptionTemplate>
</DxTreeListDataColumn>
<DxTreeListDataColumn FieldName="MeanRadiusInKM" Caption="Radius, km" DisplayFormat="N2" />
<DxTreeListDataColumn FieldName="Volume10pow9KM3" Caption="Volume, km³" DisplayFormat="N2" />
<DxTreeListDataColumn FieldName="SurfaceGravity" DisplayFormat="N2" />
</Columns>
</DxTreeList>
@code {
ITreeList TreeList { get; set; }
object TreeListData { get; set; }
protected override async Task OnInitializedAsync() {
TreeListData = SpaceObjectDataProvider.GenerateData();
}
async Task ExportXlsx_Click() {
await TreeList.ExportToXlsxAsync("ExportResult");
}
}
Note that Microsoft Excel performs width calculation in characters. When you open a document, Excel converts the column width in pixels to characters. The column width in characters differs on a machine with more than 100% DPI - the resulting width in pixels differs from the specified value. You can handle the CustomizeColumn action and specify the Column.WidthInCharacters property to set the column width in characters.
async Task ExportXlsx_Click() {
await TreeList.ExportToXlsxAsync("ExportResult", new TreeListXlExportOptions() {
CustomizeColumn = CustomizeColumn,
});
}
void CustomizeColumn(TreeListExportCustomizeColumnEventArgs e) {
if (e.FieldName == "TypeOfObject" || e.FieldName == "Name")
e.Column.WidthInCharacters = 13;
}
Hide Column Headers
Set the ExportColumnHeaders property to false to exclude the column header row from export:
async Task ExportXlsx_Click() {
await TreeList.ExportToXlsxAsync("ExportResult", new TreeListXlExportOptions() {
ExportColumnHeaders = false,
});
}
Print Column Headers on Every Page
Handle the CustomizeSheet event and use its Sheet.PrintTitles argument to repeat specific rows and columns on every printed page:
async Task ExportXlsx_Click() {
await TreeList.ExportToXlsxAsync("ExportResult", new TreeListXlExportOptions() {
CustomizeSheet = CustomizeSheet
});
}
void CustomizeSheet(TreeListExportCustomizeSheetEventArgs e) {
// Prints the first row on every page.
e.Sheet.PrintTitles.SetRows(0, 0);
}
Expand/Collapse Rows
The TreeList preserves row expanded state during export. Use the RowExpandMode property to expand/collapse all rows in the exported document:
async Task ExportXlsx_Click() {
await TreeList.ExportToXlsxAsync("ExportResult", new TreeListXlExportOptions() {
RowExpandMode = TreeListExportRowExpandMode.ExpandAll,
});
}
To export all rows as a plain list, set the RowExpandMode property to None.
Export Selected Rows
Assign true to the ExportSelectedRowsOnly property to export only selected rows:
async Task ExportXlsx_Click() {
await TreeList.ExportToXlsxAsync("ExportResult", new TreeListXlExportOptions() {
ExportSelectedRowsOnly = true,
});
}
Export Display Text
The TreeList component exports cell values. To export display text instead of values, set the ExportDisplayText property to true:
async Task ExportXlsx_Click() {
await TreeList.ExportToXlsxAsync("ExportResult", new TreeListXlExportOptions() {
ExportDisplayText = true,
});
}
Color Cells
Handle the CustomizeCell event and use the Formatting argument to format exported cells:
async Task ExportXlsx_Click() {
await TreeList.ExportToXlsxAsync("ExportResult", new TreeListXlExportOptions() {
CustomizeCell = CustomizeCell
});
}
void CustomizeCell(TreeListExportCustomizeCellEventArgs e) {
// Applies bold formatting to column headers
if (e.AreaType == DevExpress.Export.SheetAreaType.Header)
e.Formatting.Font = new XlCellFont() { Bold = true };
if (e.AreaType == DevExpress.Export.SheetAreaType.DataArea) {
var spaceObject = (SpaceObject)e.DataItem;
// Highlights planets
if (spaceObject.TypeOfObject == "Planet")
e.Formatting.BackColor = System.Drawing.Color.LightBlue;
}
// Applies the specified settings.
e.Handled = true;
}
Add Headers and Footers
Handle CustomizeSheetHeader and CustomizeSheetFooter events to add rows above and below TreeList content in the output document. For more information, see event descriptions.
Freeze Columns and Rows
Handle the CustomizeSheet event and use its Sheet.SplitPosition argument to freeze rows and columns displayed above and to the left of the specified cell:
async Task ExportXlsx_Click() {
await TreeList.ExportToXlsxAsync("ExportResult", new TreeListXlExportOptions() {
CustomizeSheet = CustomizeSheet
});
}
void CustomizeSheet(TreeListExportCustomizeSheetEventArgs e) {
// Freezes the left column and top two rows
e.Sheet.SplitPosition = new DevExpress.Export.Xl.XlCellPosition(1, 2);
}
Filter Exported Data
Handle the RowExporting event to filter exported data. Set the Cancel event argument to true to exclude the row from the exported document:
async Task ExportXlsx_Click() {
await TreeList.ExportToXlsxAsync("Solar System", new TreeListXlExportOptions() {
RowExporting = RowExporting,
});
}
void RowExporting(TreeListRowExportingEventArgs args) {
var spaceObject = (SpaceObject) args.DataItem;
if (spaceObject.TypeOfObject != "Planet" && spaceObject.TypeOfObject != "Star")
args.Cancel = true;
}
Specify the Sheet Name
Use the SheetName property to specify the sheet name for the output document:
async Task ExportXlsx_Click() {
await TreeList.ExportToXlsxAsync("ExportResult", new TreeListXlExportOptions() {
SheetName = "Space Objects"
});
}
Alternatively, you can handle the CustomizeSheet event and use its Sheet.Name argument to customize the sheet name:
async Task ExportXlsx_Click() {
await TreeList.ExportToXlsxAsync("ExportResult", new TreeListXlExportOptions() {
CustomizeSheet = CustomizeSheet
});
}
void CustomizeSheet(TreeListExportCustomizeSheetEventArgs e) {
e.Sheet.Name = "Space Objects";
}
Specify Document Print Settings
Handle the CustomizeSheet event and use the Sheet argument to access the following print settings:
- PageSetup
- Specifies page layout and printing options for a worksheet.
- PrintOptions
- Specifies options that control how a worksheet is printed.
- PrintTitles
- Specifies rows and columns to be repeated on every printed page.
- PrintArea
- Specifies the cell range to be printed.
- PageMargins
- Specifies page margins used to align the worksheet content on a printed page.
async Task ExportXlsx_Click() {
await TreeList.ExportToXlsxAsync("ExportResult", new TreeListXlExportOptions() {
CustomizeSheet = CustomizeSheet
});
}
void CustomizeSheet(TreeListExportCustomizeSheetEventArgs e) {
e.Sheet.PageSetup.PaperKind = DevExpress.Drawing.Printing.DXPaperKind.A4;
e.Sheet.PageSetup.PageOrientation = DevExpress.Export.Xl.XlPageOrientation.Landscape;
e.Sheet.PrintTitles.SetColumns(0, 0);
e.Sheet.PrintTitles.SetRows(0, 0);
e.Sheet.PageMargins = new DevExpress.Export.Xl.XlPageMargins();
e.Sheet.PageMargins.PageUnits = DevExpress.Export.Xl.XlPageUnits.Centimeters;
e.Sheet.PageMargins.Left = 3.0;
e.Sheet.PageMargins.Right = 3.0;
e.Sheet.PageMargins.Top = 3.25;
e.Sheet.PageMargins.Bottom = 3.25;
}
Display Loading Indicators
Use DevExpress Blazor Loading Panel to display a loading indicator during export operations:
@inject SpaceObjectDataProvider SpaceObjectDataProvider
<DxLoadingPanel @bind-Visible="@PanelVisible"
IsContentBlocked="true"
ApplyBackgroundShading="true"
IndicatorAreaVisible="false"
Text="Exporting Document...">
<DxButton Text="Export to XLSX" Click="ExportXlsx_Click" />
<DxTreeList @ref="TreeList" Data="TreeListData" ChildrenFieldName="Satellites">
<Columns>
<DxTreeListDataColumn FieldName="Name" />
<DxTreeListDataColumn FieldName="TypeOfObject" Caption="Type" />
<DxTreeListDataColumn FieldName="Mass10pow21kg" Caption="Mass, kg" DisplayFormat="N2">
<HeaderCaptionTemplate>Mass, 10<sup>21</sup> × kg</HeaderCaptionTemplate>
</DxTreeListDataColumn>
<DxTreeListDataColumn FieldName="MeanRadiusInKM" Caption="Radius, km" DisplayFormat="N2" />
<DxTreeListDataColumn FieldName="Volume10pow9KM3" Caption="Volume, km³" DisplayFormat="N2" />
<DxTreeListDataColumn FieldName="SurfaceGravity" DisplayFormat="N2" />
</Columns>
</DxTreeList>
</DxLoadingPanel>
@code {
ITreeList TreeList { get; set; }
object TreeListData { get; set; }
bool PanelVisible { get; set; } = false;
protected override async Task OnInitializedAsync() {
TreeListData = SpaceObjectDataProvider.GenerateData();
}
async Task ExportXlsx_Click() {
PanelVisible = true;
await Task.Yield();
await TreeList.ExportToXlsxAsync("ExportResult");
PanelVisible = false;
}
}
Insert Hyperlinks in Cells
Use the Hyperlink argument of the CustomizeCell event to insert hyperlinks into data cells:
@inject SpaceObjectDataProvider SpaceObjectDataProvider
<DxButton Text="Export to XLSX" Click="ExportXlsx_Click" />
<DxTreeList @ref="TreeList" Data="TreeListData" ChildrenFieldName="Satellites">
<Columns>
<DxTreeListDataColumn FieldName="Name" />
<DxTreeListDataColumn FieldName="TypeOfObject" Caption="Type" />
<DxTreeListDataColumn FieldName="Mass10pow21kg" Caption="Mass, kg" DisplayFormat="N2" >
<HeaderCaptionTemplate>Mass, 10<sup>21</sup> × kg</HeaderCaptionTemplate>
</DxTreeListDataColumn>
<DxTreeListDataColumn FieldName="MeanRadiusInKM" Caption="Radius, km" DisplayFormat="N2" />
<DxTreeListDataColumn FieldName="Volume10pow9KM3" Caption="Volume, km³" DisplayFormat="N2" />
<DxTreeListDataColumn FieldName="SurfaceGravity" DisplayFormat="N2" />
</Columns>
</DxTreeList>
@code {
ITreeList TreeList { get; set; }
object TreeListData { get; set; }
protected override async Task OnInitializedAsync() {
TreeListData = SpaceObjectDataProvider.GenerateData();
}
async Task ExportXlsx_Click() {
await TreeList.ExportToXlsxAsync("ExportResult", new TreeListXlExportOptions() {
CustomizeCell = CustomizeCell
});
}
void CustomizeCell(TreeListExportCustomizeCellEventArgs e) {
if (e.AreaType == DevExpress.Export.SheetAreaType.DataArea && e.ColumnFieldName == "Name") {
var spaceObject = e.DataItem as SpaceObject;
e.Hyperlink = spaceObject.WikiPage;
e.Handled = true;
}
}
}