Export Data in Blazor TreeList
- 9 minutes to read
The TreeList allows you to export data to XLS, XLSX, and CSV files.
If you export to Excel formats, the resulting file can calculate totals and shape data (group, sort, and filter). If you export to a CSV file, the TreeList exports plain data and data shaping settings are ignored.
Export API
Call the following methods to export data:
- ExportToXlsAsync - Exports data to an XLS file.
- ExportToXlsxAsync - Exports data to an XLSX file.
- ExportToCsvAsync - Exports data to a CSV file.
These methods allow you to write the result to a stream or to a file downloaded to a client machine.
<DxTreeList Data="TreeListData"
KeyFieldName="Id"
ParentKeyFieldName="ParentId"
@ref="MyTreeList">
@* ... *@
</DxTreeList>
<DxButton Text="Export XLSX" Click="ExportXlsx_Click" />
<DxButton Text="Export XLS" Click="ExportXls_Click" />
<DxButton Text="Export CSV" Click="ExportCsv_Click" />
@code {
ITreeList MyTreeList { get; set; }
async Task ExportXlsx_Click() {
await MyTreeList.ExportToXlsxAsync("ExportResult");
}
async Task ExportXls_Click() {
await MyTreeList.ExportToXlsAsync("ExportResult");
}
async Task ExportCsv_Click() {
await MyTreeList.ExportToCsvAsync("ExportResult");
}
// ...
}
All export methods accept the options parameter that allows you to specify export settings. See examples in the following section: Customize Exported Document.
Important
CSV Export Security Considerations
Exported data can contain executable content. To prevent possible security vulnerabilities, set the EncodeExecutableContent property to true to enclose potentially dangerous content in quotation marks prior to exporting it to the CSV format.
Limitations
The following limitations apply:
- Content of templates is not exported.
- Custom summaries implemented in the CustomSummary event are exported as plain text.
- Data columns anchored to the right edge become regular columns, while columns anchored to the components’s left edge remain frozen.
- The TreeList component filters and sorts selected rows on the client side. Filtering and sorting may differ in the resulting document because export depends on database collation.
- Appearance settings applied by style settings or in the CustomizeElement event handler are not exported. You can handle the CustomizeCell event to customize element appearance in the the exported document.
- Excel permits up to seven nesting levels in outlines. Deeper nesting levels are exported on the seventh nesting level.
Refer to the following articles for information about Microsoft Excel limitations:
Customize Exported Document
This section contains code samples that demonstrate common export scenarios.
Manage Column Visibility in the Document
The TreeList exports data of all data columns. A column whose Visible property is set to false is exported as a column with zero width (hidden). You can use the Column.IsHidden property in the CustomizeColumn action to specify whether to show or hide the column in the document regardless of its visibility in the component.
<DxTreeList Data="TreeListData"
KeyFieldName="Id"
ParentKeyFieldName="ParentId"
@ref="MyTreeList">
<Columns>
<DxTreeListDataColumn FieldName="Name" Caption="Task" />
<DxTreeListDataColumn FieldName="EmployeeName" />
<DxTreeListDataColumn FieldName="StartDate" Visible="false" />
<DxTreeListDataColumn FieldName="DueDate" />
</Columns>
</DxTreeList>
@code {
ITreeList MyTreeList { get; set; }
async Task ExportXlsx_Click() {
await MyTreeList.ExportToXlsxAsync("ExportResult", new TreeListXlExportOptions() {
CustomizeColumn = CustomizeColumn
});
}
void CustomizeColumn(GridExportCustomizeColumnEventArgs e) {
// Shows all columns in the resulting document
e.Column.IsHidden = false;
}
// ...
}
If you do not want to export a specific column, set the column’s ExportEnabled property to false:
<DxTreeList Data="TreeListData" KeyFieldName="Id" ParentKeyFieldName="ParentId">
<Columns>
<DxTreeListDataColumn FieldName="Name" Caption="Task" ExportEnabled="false" />
<DxTreeListDataColumn FieldName="EmployeeName" />
<DxTreeListDataColumn FieldName="StartDate" />
<DxTreeListDataColumn FieldName="DueDate" />
</Columns>
</DxTreeList>
Specify Column Width
Use the ExportWidth property to specify the column width in pixels in the exported document.
<DxTreeList Data="TreeListData" KeyFieldName="Id" ParentKeyFieldName="ParentId">
<Columns>
<DxTreeListDataColumn FieldName="Name" Caption="Task" ExportWidth="300" />
<DxTreeListDataColumn FieldName="EmployeeName" />
<DxTreeListDataColumn FieldName="StartDate" />
<DxTreeListDataColumn FieldName="DueDate" />
</Columns>
</DxTreeList>
You can handle the CustomizeColumn action and use the Column.WidthInPixels or Column.WidthInCharacters property to specify an exported column’s width.
async Task ExportXlsx_Click() {
await MyTreeList.ExportToXlsxAsync("ExportResult", new TreeListXlExportOptions() {
CustomizeColumn = CustomizeColumn
});
}
void CustomizeColumn(TreeListExportCustomizeColumnEventArgs e) {
if (e.FieldName == "Name")
e.Column.WidthInPixels = 300;
}
Hide Column Headers
Set the ExportColumnHeaders property to false to prevent the export of column headers.
async Task ExportXlsx_Click() {
await MyTreeList.ExportToXlsxAsync("ExportResult", new TreeListXlExportOptions() {
ExportColumnHeaders = false
});
}
Print Column Headers on Every Page
Handle the CustomizeSheet action and use its Sheet.PrintTitles argument property to repeat selected rows and columns on every printed page.
async Task ExportXlsx_Click() {
await MyTreeList.ExportToXlsxAsync("ExportResult", new TreeListXlExportOptions() {
CustomizeSheet = CustomizeSheet
});
}
void CustomizeSheet(TreeListExportCustomizeSheetEventArgs e) {
e.Sheet.PrintTitles.SetRows(0, 0);
}
Specify Row Expand States
The TreeList preserves row expansion state during export. Use the RowExpandMode property to expand or collapse all rows in the exported document, or to export all rows as a plain list.
async Task ExportXlsx_Click() {
await MyTreeList.ExportToXlsxAsync("ExportResult", new TreeListXlExportOptions() {
RowExpandMode = TreeListExportRowExpandMode.None
});
}
Export Selected Rows
Set the ExportSelectedRowsOnly property to true to export selected rows only.
async Task ExportXlsx_Click() {
await MyTreeList.ExportToXlsxAsync("ExportResult", new TreeListXlExportOptions() {
ExportSelectedRowsOnly = true
});
}
Export Display Text
The TreeList component exports cell values. If you need to export display text instead of values, set the ExportDisplayText property to true.
async Task ExportXlsx_Click() {
await MyTreeList.ExportToXlsxAsync("ExportResult", new TreeListXlExportOptions() {
ExportDisplayText = true
});
}
Format Cells
Handle the CustomizeCell action and use its Formatting argument property to format exported cells.
async Task ExportXlsx_Click() {
await MyTreeList.ExportToXlsxAsync("ExportResult", new TreeListXlExportOptions() {
CustomizeCell = CustomizeCell
});
}
void CustomizeCell(TreeListExportCustomizeCellEventArgs e) {
if (e.AreaType == DevExpress.Export.SheetAreaType.DataArea) {
e.Formatting.BackColor = System.Drawing.Color.LightYellow;
}
// Set the Handled property to true to apply the specified settings
e.Handled = true;
}
Insert a Hyperlink into a Cell
Use the Hyperlink property in the CustomizeCell action delegate to add a hyperlink to a cell.
async Task ExportXlsx_Click() {
await MyTreeList.ExportToXlsxAsync("ExportResult", new TreeListXlExportOptions() {
CustomizeCell = CustomizeCell
});
}
void CustomizeCell(TreeListExportCustomizeCellEventArgs e) {
if (e.AreaType == DevExpress.Export.SheetAreaType.DataArea && e.ColumnFieldName == "Name") {
var product = e.DataItem as TestModel;
e.Hyperlink = product.Url;
e.Handled = true;
}
};
Add Sheet Header and Footer
The TreeListXlExportOptions object contains the CustomizeSheetHeader and CustomizeSheetFooter actions that allow you to add rows above and below TreeList content in the output document. For more information, see action descriptions.
Freeze Columns and Rows
Handle the CustomizeSheet action and use its Sheet.SplitPosition argument property to freeze a specific number of rows and columns above and to the left of the specified cell.
async Task ExportXlsx_Click() {
await MyTreeList.ExportToXlsxAsync("ExportResult", new TreeListXlExportOptions() {
CustomizeSheet = CustomizeSheet
});
}
void CustomizeSheet(TreeListExportCustomizeSheetEventArgs e) {
// Freeze the left column and top two rows
e.Sheet.SplitPosition = new DevExpress.Export.Xl.XlCellPosition(1, 2);
}
Filter Exported Data
Handle the RowExporting delegate action to filter exported data. The Grid component calls the RowExporting action for data and group rows. Set the Cancel property to true to exclude the row from the exported document.
async Task ExportXlsx_Click() {
await MyTreeList.ExportToXlsxAsync("ExportResult", new TreeListXlExportOptions() {
RowExporting = RowExporting
});
}
void RowExporting(TreeListRowExportingEventArgs e) {
// Exports rows whose due date is earlier than January 1, 2018
if ((DateTime)e.GetRowValue("DueDate") > new DateTime(2018, 1, 1)) {
e.Cancel = true;
}
}
Specify the Sheet Name
Use the SheetName property to specify the name of the sheet in the output document.
async Task ExportXlsx_Click() {
await MyTreeList.ExportToXlsxAsync("ExportResult", new TreeListXlExportOptions() {
SheetName = "Tasks"
});
}
You can also handle the CustomizeSheet delegate action and use its Sheet.Name argument property to customize the sheet name.
async Task ExportXlsx_Click() {
await MyTreeList.ExportToXlsxAsync("ExportResult", new TreeListXlExportOptions() {
CustomizeSheet = CustomizeSheet;
});
}
void CustomizeSheet(TreeListExportCustomizeSheetEventArgs e) {
e.Sheet.Name = "Contacts";
}
Specify Document Print Settings
Handle the CustomizeSheet action and use its Sheet argument property to access the following print settings:
- PageSetup
- Specifies the 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 worksheet content on a printed page.
async Task ExportXlsx_Click() {
await Grid.ExportToXlsxAsync("ExportResult", new GridXlExportOptions() {
CustomizeSheet = CustomizeSheet
});
}
void CustomizeSheet(GridExportCustomizeSheetEventArgs 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;
}