Export Data in Blazor Grid
- 10 minutes to read
The Grid allows you to export data to XLS, XLSX, and CSV files.
When exporting to XLS and XLSX, the resulting files can maintain the following grid data shaping settings:
- Data grouping
- Data sorting
- Totals and group summaries
If you export to a CSV file, the grid exports plain data. Grid 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.
<DxGrid @ref="Grid" Data="@Data" >
@* ... *@
</DxGrid>
<DxButton Text="Export XLSX" Click="ExportXlsx_Click" />
<DxButton Text="Export XLS" Click="ExportXls_Click" />
<DxButton Text="Export CSV" Click="ExportCsv_Click" />
@code {
// ...
async Task ExportXlsx_Click() {
await Grid.ExportToXlsxAsync("ExportResult");
}
async Task ExportXls_Click() {
await Grid.ExportToXlsAsync("ExportResult");
}
async Task ExportCsv_Click() {
await Grid.ExportToCsvAsync("ExportResult");
}
}
Every export method accepts the options parameter that allows you to customize export settings. You can find use-case examples in the following section: Customize Exported Document.
async Task ExportXlsx_Click() {
var options = new GridXlExportOptions();
// customize the export options
await Grid.ExportToXlsxAsync("ExportResult", options);
}
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
- Content of templates is not exported, including detail grids.
- Custom summaries implemented in the CustomSummary grid event, are exported as plain text.
- Appearance settings applied by style settings or in the CustomizeElement event handler are not exported. You can handle the CustomizeCell event to customize grid element appearance in the exported document.
- If the grid is bound to a GridDevExtremeDataSource object, the expand state of groups is not exported and corresponds to the GroupExportMode property value.
- If you want to export selected rows only and the Grid component is bound to a GridDevExtremeDataSource object, specify the KeyFieldName property.
If the grid is bound to a GridDevExtremeDataSource object, the following limitations apply:
- If you want to export selected rows only, specify the KeyFieldName property.
- Grouped data, and filtered and sorted selected rows may differ in the resulting document. This happens because export depends on database collation. For example, the same string with different capitalization may form multiple groups in the exported document. The Grid component puts such values into a single group.
See the following article for more information about Microsoft Excel limitations (for example, row count and column count): Excel specifications and limits.
Customize Exported Document
This section contains code samples that demonstrate common export use cases.
Prevent a Column from Being Exported
Set a column’s ExportEnabled property to false to exclude that column from data export.
<DxGrid @ref="Grid" Data="@Data" >
<Columns>
<DxGridDataColumn FieldName="CompanyName" />
<DxGridDataColumn FieldName="City" />
<DxGridDataColumn FieldName="Country" ExportEnabled="false" />
<DxGridDataColumn FieldName="UnitPrice" DisplayFormat="c" ExportEnabled="false" />
<DxGridDataColumn FieldName="Quantity" ExportEnabled="false" />
</Columns>
</DxGrid>
Hide and Show Exported Columns
The grid exports data of every data column unless its ExportEnabled property is set to false. A column whose Visible property is set to false is exported as a hidden column (one that has a zero width).
You can use the Column.IsHidden property in the CustomizeColumn action to show and hide columns in the resulting document.
<DxGrid @ref="Grid" Data="@Data" >
<Columns>
<DxGridDataColumn FieldName="CompanyName" />
<DxGridDataColumn FieldName="City" />
<DxGridDataColumn FieldName="Country" Visible="false" />
<DxGridDataColumn FieldName="UnitPrice" DisplayFormat="c" Visible="false" />
<DxGridDataColumn FieldName="Quantity" Visible="false" />
</Columns>
</DxGrid>
@code {
// ...
await Grid.ExportToXlsxAsync("ExportResult", new GridXlExportOptions() {
CustomizeColumn = CustomizeColumn,
});
void CustomizeColumn(GridExportCustomizeColumnEventArgs e) {
// Shows every grid column in the output document
e.Column.IsHidden = false;
}
}
Specify Column Export Width
Use the ExportWidth property to specify the width of the column in the exported document.
<DxGrid @ref="Grid" Data="@Data" >
<Columns>
<DxGridDataColumn FieldName="CompanyName" ExportWidth="280" />
<DxGridDataColumn FieldName="City" />
<DxGridDataColumn FieldName="Country" ExportWidth="100" />
<DxGridDataColumn FieldName="UnitPrice" DisplayFormat="c" />
<DxGridDataColumn FieldName="Quantity" />
</Columns>
</DxGrid>
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 Grid.ExportToXlsxAsync("ExportResult", new GridXlExportOptions() {
CustomizeColumn = CustomizeColumn,
});
}
void CustomizeColumn(GridExportCustomizeColumnEventArgs e) {
if (e.FieldName == "Country") {
e.Column.WidthInPixels = 100;
} else if (e.FieldName == "CompanyName") {
e.Column.WidthInPixels = 280;
}
}
Hide Column Headers
Set the ExportColumnHeaders property to false to prevent the export of column headers.
async Task ExportXlsx_Click() {
await Grid.ExportToXlsxAsync("ExportResult", new GridXlExportOptions() {
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 Grid.ExportToXlsxAsync("ExportResult", new GridXlExportOptions() {
CustomizeSheet = CustomizeSheet
});
}
void CustomizeSheet(GridExportCustomizeSheetEventArgs e) {
e.Sheet.PrintTitles.SetRows(0, 0);
}
Expand/Collapse Group Rows
The grid exports group rows and preserves their expansion state. Use the GroupExportMode property to expand or collapse all rows in the exported document or to prevent group row export.
async Task ExportXlsx_Click() {
await Grid.ExportToXlsxAsync("ExportResult", new GridXlExportOptions() {
GroupExportMode = GridGroupExportMode.ExpandAll,
});
}
To prevent group row export, set the GroupExportMode property to None.
Export Selected Rows
Set the ExportSelectedRowsOnly property to true to export selected rows only.
async Task ExportXlsx_Click() {
await Grid.ExportToXlsxAsync("ExportResult", new GridXlExportOptions() {
ExportSelectedRowsOnly = true,
});
}
Export Display Text
The Grid 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 Grid.ExportToXlsxAsync("ExportResult", new GridXlExportOptions() {
ExportDisplayText = true,
});
}
Export Unbound Expressions as Formulas
When exporting an unbound column whose values are evaluated based on the UnboundExpression property, the Grid component exports cell values. Set the ExportUnboundExpressionAsFunction property to true to export these column values as formulas.
<DxGrid @ref="Grid" Data="@Data">
<Columns>
@* ... *@
<DxGridDataColumn FieldName="UnitPrice" DisplayFormat="c" ExportWidth="70"/>
<DxGridDataColumn FieldName="Quantity" ExportWidth="70" />
<DxGridDataColumn FieldName="Total" UnboundType="GridUnboundColumnType.Decimal" DisplayFormat="c"
UnboundExpression="[UnitPrice] * [Quantity]" />
</Columns>
</DxGrid>
<DxButton Text="Export to XLSX" Click="ExportXlsx_Click" />
@code {
// ...
async Task ExportXlsx_Click() {
await Grid.ExportToXlsxAsync("ExportResult", new GridXlExportOptions() {
ExportUnboundExpressionAsFunction = true,
});
}
}
Color Cells
Handle the CustomizeCell action and use its Formatting argument property to format an exported cell.
async Task ExportXlsx_Click() {
await Grid.ExportToXlsxAsync("ExportResult", new GridXlExportOptions() {
CustomizeCell = CustomizeCell
});
}
void CustomizeCell(GridExportCustomizeCellEventArgs e) {
if (e.AreaType == DevExpress.Export.SheetAreaType.DataArea) {
decimal unitPrice = (decimal)e.DataItem.GetType().GetProperty("UnitPrice").GetValue(e.DataItem, null);
short quantity = (short)e.DataItem.GetType().GetProperty("Quantity").GetValue(e.DataItem, null);
decimal total = unitPrice * quantity;
// Highlight each column's data cell when Total in the current row > 1000
if (total > 1000) {
e.Formatting.BackColor = System.Drawing.Color.LightGreen;
e.Formatting.Font = new XlCellFont() { Bold = true };
}
// Set the Handled property to true to apply the specified settings.
e.Handled = true;
}
}
Insert a Hyperlink in the Cell
Use the Hyperlink property in the CustomizeCell action delegate to insert a hyperlink in the cell.
async Task ExportXlsx_Click() {
await Grid.ExportToXlsxAsync("ExportResult", new GridXlExportOptions() {
CustomizeCell = CustomizeCell
});
}
void CustomizeCell(GridExportCustomizeCellEventArgs 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 GridXlExportOptions object contains the CustomizeSheetHeader and CustomizeSheetFooter actions that allow you to add rows above and below grid 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 Grid.ExportToXlsxAsync("ExportResult", new GridXlExportOptions() {
CustomizeSheet = CustomizeSheet;
});
}
void CustomizeSheet(GridExportCustomizeSheetEventArgs e) {
// Freeze the left column and top two rows
e.Sheet.SplitPosition = new DevExpress.Export.Xl.XlCellPosition(1, 2);
}
Filter the 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 Grid.ExportToXlsxAsync("Big Deals", new GridXlExportOptions() {
RowExporting = RowExporting;
});
}
void RowExporting(GridRowExportingEventArgs e) {
// Exports rows with Total value larger than 1000
if (!e.IsGroupRow) {
if ((int)e.GetRowValue("Total") < 1000) {
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 Grid.ExportToXlsxAsync("ExportResult", new GridXlExportOptions() {
SheetName = "Contacts"
});
}
You can handle the CustomizeSheet delegate action and use its Sheet.Name argument property to customize the sheet name.
async Task ExportXlsx_Click() {
await Grid.ExportToXlsxAsync("ExportResult", new GridXlExportOptions() {
CustomizeSheet = CustomizeSheet;
});
}
void CustomizeSheet(GridExportCustomizeSheetEventArgs e) {
e.Sheet.Name = "Contacts";
}
Specify Document Print Settings
Handle the CustomizeSheet action and use its Sheet argument property to access the following document’s 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 the 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;
}
Export Data to PDF and DOCX Formats
You can use DevExpress Reporting tools to implement export to other formats (PDF and DOCX). Note that Reports ship within the same DevExpress subscriptions as our Blazor UI Controls. Refer to the following examples for more information and code: