Modifying Exported Data
The exporting process can be customized at runtime using the ElementExporting
event of RadDataGrid
and the DataGridExportOptions
class. The events are raised for each element that is exported, and can be used to change the value or the corresponding cell style.
Export Options
The options are represented by the DataGridExportOptions
which contains basic information about the export settings. The DataGridExportOptions
is provided to the Export
method of RadDataGrid
. The class provides the following settings:
-
Items
—Collection that contains the items that will be exported. -
Culture
—The culture info used during the export. -
ShowColumnHeaders
—Determines if the column headers of the data grid will be exported. -
ShowColumnFooters
—Determines if the column footers of the data grid will be exported. -
ShowGroupFooters
—Determines if the group footers of the data grid will be exported. -
AutoFitColumnsWidth
—Determines if the width of the columns in the exported document will be calculated automatically based on their content. -
IgnoreCollapsedGroups
—Determines if groups that are collapsed will be exported. -
ShowGroupRows
—Determines if group rows (the group headers) will be exported.
Using the DataGridExportOptions
string projectRootFolderPath = System.IO.Path.GetFullPath("../../../../../../", AppDomain.CurrentDomain.BaseDirectory);
using (var stream = File.Open(projectRootFolderPath + "\" + "SampleDocument.xlsx", FileMode.OpenOrCreate))
{
this.dataGrid.Export(stream, ExportFormat.Xlsx, new DataGridExportOptions()
{
AutoFitColumnsWidth = true,
ShowColumnFooters = true,
ShowColumnHeaders = false,
IgnoreCollapsedGroups = true
});
}
Cancelling Element Exporting
The export process or parts of it can be cancelled using the ElementExporting
event of RadDataGrid
.
To cancel the entire export, check the Element
property of the event arguments and if it is Table
set the Cancel
property to true
.
Cancel the document export
private void RadDataGrid_ElementExporting(object sender, DataGridElementExportingEventArgs e)
{
if(e.Element == ExportElement.Table)
{
e.Cancel = true;
}
}
Element
property of the event arguments and based on its type (Row
, Cell
, HeaderCell
, etc.) and Value
, set the Cancel
property to true
.
Cancel exporting of a cell
private void RadDataGrid_ElementExporting(object sender, DataGridElementExportingEventArgs e)
{
if(e.Element == ExportElement.Cell)
{
var concreteArgs = (DataGridCellExportingEventArgs)e;
var column = (DataGridColumn)concreteArgs.Column;
if (column.Header.Equals("Name") && e.Value != null && e.Value.Equals("Item 1"))
{
e.Cancel = true;
}
}
}
Replacing the Exported Value
To replace the value of an exported element you can use the ElementExporting
event of RadDataGrid
.
Replace the exported value
private void RadDataGrid_ElementExporting(object sender, DataGridElementExportingEventArgs e)
{
if(e.Element == ExportElement.Cell)
{
var concreteArgs = (DataGridCellExportingEventArgs)e;
var column = (DataGridColumn)concreteArgs.Column;
if (column.Header.Equals("Name") && e.Value == null)
{
e.Value = "Empty";
}
}
}
Customizing the Exported Element Style
To change the default styles used in the exported document, use the ElementExporting
event of RadDataGrid
. The VisualParameters
property of the event arguments allows you to define options like foreground, background, font settings, text decorations, alignments and more.
Changing the styles of the exported rows and cells
private void RadDataGrid_ElementExporting(object sender, DataGridElementExportingEventArgs e)
{
var visualParameters = (DataGridVisualExportParameters)e.VisualParameters;
if(e.Element == ExportElement.HeaderRow)
{
visualParameters.Style = new CellSelectionStyle()
{
IsBold = true,
Fill = new PatternFill(PatternType.Solid, Telerik.Documents.Media.Colors.Green, Telerik.Documents.Media.Colors.Green),
ForeColor = new ThemableColor(Telerik.Documents.Media.Colors.White),
FontSize = 24,
IsWrapped = true,
};
}
if (e.Element == ExportElement.Cell)
{
var border = new CellBorder(CellBorderStyle.Thin, new ThemableColor(Telerik.Documents.Media.Colors.Purple));
visualParameters.Style = new CellSelectionStyle()
{
CellBorders = new CellBorders(border, border, border, border, null, null, null, null),
Fill = new PatternFill(PatternType.Solid, Telerik.Documents.Media.Colors.LightGray, Telerik.Documents.Media.Colors.LightGray)
};
}
}
Changing the Exported Value Format
To change the default cell formats provided by the DataGrid to the exported document, use the ElementExporting
event of RadDataGrid
.
A common scenario where this is useful is when the CellContentFormat
property of the column is set. This will format the numeric value as a string and this is how it will be exported to the document. To change this, use the ElementExporting
event and the VisualParameters
property of the arguments.
Setting the CellContentFormat to a column
<telerikGrid:RadDataGrid.Columns>
<telerikGrid:DataGridTextColumn PropertyName="Value" CellContentFormat="{}{0:F2}" />
</telerikGrid:RadDataGrid.Columns>
Changing the exported value format
private void RadDataGrid_ElementExporting(object sender, DataGridElementExportingEventArgs e)
{
if(e.Element == ExportElement.Cell)
{
if (e?.Value == null)
return;
var visualParameters = (DataGridVisualExportParameters)e.VisualParameters;
var tryDouble = double.TryParse(e.Value.ToString(), out var d);
if (tryDouble)
{
visualParameters.Style = new CellSelectionStyle()
{
Format = new CellValueFormat("0.00")
};
e.Value = d;
return;
}
var tryInt = int.TryParse(e.Value.ToString(), out var i);
if (tryInt)
{
visualParameters.Style = new CellSelectionStyle()
{
Format = new CellValueFormat("0")
};
e.Value = i;
return;
}
}
}
Accessing and Modifying the Document Model
The export feature uses the Telerik Document Processing Libraries internally. The export event arguments (DataGridElementExportedEventArgs
and DataGridElementExportingEventArgs
) provide access to the Workbook object that stores the document model. The Workbook
element can be accessed only when the Element
property of the ElementExporting
and ElementExported
events' arguments is set to Table
.
The ElementExported
event is raised with Element
set to Table
at the end of the export process. This is a good moment to get the Workbook
and modify it if needed.
See how to customize the workbook object in the RadStreamProcessing help documentation.
Getting the Workbook and modifying it after the export has finished
private void RadDataGrid_ElementExported(object sender, DataGridElementExportedEventArgs e)
{
if (e.Element == ExportElement.Table)
{
Workbook workbook = (Workbook)e.Workbook;
CellStyle wbStyle = workbook.Styles["Normal"];
wbStyle.ForeColor = new ThemableColor(Telerik.Documents.Media.Colors.Green);
wbStyle.FontFamily = new ThemableFontFamily(ThemeFontType.Major);
wbStyle.FontSize = 24;
wbStyle.VerticalAlignment = RadVerticalAlignment.Top;
}
}
Setting the Exporting Data Items List
By default all data from the DataGrid will be exported when you call the corresponding export method. To change this and export a specific set of data items, use the Items
property of the DataGridExportOptions
object.
Exporting half the items
string projectRootFolderPath = System.IO.Path.GetFullPath("../../../../../../", AppDomain.CurrentDomain.BaseDirectory);
using (var stream = File.Open(projectRootFolderPath + "\" + "SampleDocument.xlsx", FileMode.OpenOrCreate))
{
var items = ((IList)this.dataGrid.ItemsSource).OfType<object>();
this.dataGrid.Export(stream, ExportFormat.Xlsx, new DataGridExportOptions()
{
ShowColumnHeaders = false,
Items = items.Take(items.Count() / 2).ToList()
});
}