Exporting

23 Sep 202024 minutes to read

The SfDataGrid supports exporting the data to Excel with several customization options like custom appearance, excluding specific columns, excluding headers, setting custom row height, setting custom column width, etc. It also supports Grouping, Filtering, and Sorting when exporting.

To use export to Excel and functionalities of the SfDataGrid, add the required assembly references to your application as discussed in the Assembly deployment section.

The following code explains how to create and display a SfDataGrid in view.

C#

// In MyViewController.cs

SfDataGrid SfGrid;
UIButton exportExcel;
        
public MyViewController()
{
    this.SfGrid = new SfDataGrid();
    this.SfGrid.AutoGenerateColumns = false;
    this.SfGrid.SelectionMode = SelectionMode.Single;
    this.SfGrid.ItemsSource = new OrderInfoRepository().OrderInfoCollection;
    this.SfGrid.ShowRowHeader = false;
    this.SfGrid.HeaderRowHeight = 45;
    this.SfGrid.RowHeight = 45;

    var FreightColumn = new GridTextColumn
    {
        MappingName = "Freight",
        HeaderText = "Freight"
    };
    var FirstNameColumn = new GridTextColumn
    {
        MappingName = "FirstName",
        HeaderText = "FirstName",
    };
    var OrderIdColumn = new GridTextColumn
    {
        MappingName = "OrderID",
        HeaderText = "OrderID",
    };
    var EmployeeIdColumn = new GridTextColumn
    {
        MappingName = "EmployeeID",
        HeaderText = "EmployeeID",
    };
    var IsClosedColumn = new GridTextColumn
    {
        MappingName = "IsClosed",
        HeaderText = "IsClosed",
    };
    SfGrid.Columns.Add(OrderIdColumn);
    SfGrid.Columns.Add(FirstNameColumn);
    SfGrid.Columns.Add(FreightColumn);
    SfGrid.Columns.Add(EmployeeIdColumn);
    SfGrid.Columns.Add(IsClosedColumn);

    exportExcel = new UIButton(UIButtonType.RoundedRect);
    exportExcel.SetTitle("Export To Excel", UIControlState.Normal);
    exportExcel.SetTitleColor(UIColor.Black, UIControlState.Normal);
    exportExcel.Layer.CornerRadius = 5;
    exportExcel.BackgroundColor = UIColor.FromRGB(212, 208, 200);
    exportExcel.TouchDown += ExportToExcel_Clicked;
    this.View.AddSubviews(exportExcel);
    this.View.AddSubviews(this.SfGrid);
}

public override void ViewDidLayoutSubviews()
{
    this.exportExcel.Frame = new CGRect(((this.View.Frame.Right / 2) + 15), 10, ((this.View.Frame.Right / 2) - 30), 50);
    this.SfGrid.Frame = new CGRect(0, 70, this.View.Frame.Width, this.View.Frame.Height - 70);
    base.ViewDidLayoutSubviews();
}

Export to Excel

You can export data to Excel by using the ExportToExcel method by passing the SfDataGrid as an argument.

C#

private void ExportToExcel_Clicked(object sender, EventArgs e)
{
    DataGridExcelExportingController excelExport = new DataGridExcelExportingController();
    var excelEngine = excelExport.ExportToExcel(this.SfGrid);
    var workbook = excelEngine.Excel.Workbooks[0];
    MemoryStream stream = new MemoryStream();
    workbook.SaveAs(stream);
    workbook.Close();
    excelEngine.Dispose();
    Save("DataGrid.xlsx", "application/msexcel", stream);
}

Exporting Options

You can also export data to Excel with various customizing options while exporting the SfDataGrid by passing the grid and DataGridExcelExportingOption as arguments to the ExportToExcel method. .

C#

DataGridExcelExportingController excelExport = new DataGridExcelExportingController ();
DataGridExcelExportingOption exportOption = new DataGridExcelExportingOption ();
exportOption.ExportColumnWidth = false;
exportOption.DefaultColumnWidth = 150;
var excelEngine = excelExport.ExportToExcel (this.dataGrid, exportOption);

The SfDataGrid provides you with several properties in DataGridExcelExportingOption class to customize the grid while exporting it to Excel.

AllowOutlining

If you export the data grid with grouping applied, you can enable the group expand or collapse option in the Excel sheet by setting the DataGridExcelExportingOption.AllowOutlining to true. The default value of this property is false so, you cannot expand or collapse the group in the Excel sheet by default.

C#

DataGridExcelExportingOption option = new DataGridExcelExportingOption();
option.AllowOutlining = true;

AllowSortingAndFiltering

The SfDataGrid allows exporting the data grid to Excel with sorting and filtering options enabled on the column header in the Excel sheet by setting the DataGridExcelExportingOption.AllowSortingAndFiltering to true. The default value of this property is false.

C#

DataGridExcelExportingOption option = new DataGridExcelExportingOption();
option.AllowSortingAndFiltering = true;

ApplyGridStyle

The SfDataGrid allows exporting the data with the applied GridStyle by setting the DataGridExcelExportingOption.ApplyGridStyle to true. By default, the data will be exported without the GridStyle.

C#

DataGridExcelExportingOption option = new DataGridExcelExportingOption();
option.ApplyGridStyle = true;

DefaultColumnWidth

The SfDataGrid allows customizing the column width in Excel file using the DataGridExcelExportingOption.DefaultColumnWidth property. The DefaultColumnWidth value will be applied to all the columns in the Excel sheet.

C#

DataGridExcelExportingOption option = new DataGridExcelExportingOption();
option.DefaultColumnWidth = 100;

DefaultRowHeight

The SfDataGrid allows customizing the row height in Excel file by using the DataGridExcelExportingOption.DefaultRowHeight property. The DefaultRowHeight value will be applied to all the rows in the Excel sheet.

C#

DataGridExcelExportingOption option = new DataGridExcelExportingOption();
option.DefaultRowHeight = 50;

ExcludedColumns

By default, all the columns (including hidden columns) in the SfDataGrid will be exported to Excel. To exclude some particular columns while exporting to Excel, add those columns to the DataGridExcelExportingOption.ExcludeColumns list.

C#

DataGridExcelExportingOption option = new DataGridExcelExportingOption();
var list = new List<string>();
list.Add("OrderID");
list.Add("LastName");
option.ExcludedColumns = list;

ExportColumnWidth

By default, the data grid columns will be exported to Excel with the value of the DataGridExcelExportingOption.DefaultColumnWidth but, you can also export the data grid to Excel with the exact column widths from the SfDataGrid by setting the DataGridExcelExportingOption.ExportColumnWidth to true. The default value of the ExportColumnWidth property is false.

C#

DataGridExcelExportingOption option = new DataGridExcelExportingOption();
option.ExportColumnWidth = true;

ExportRowHeight

By default, the data grid rows will be exported to Excel with the value of the DataGridExcelExportingOption.DefaultRowHeight but, you can also export the data grid to Excel with the exact row heights from the SfDataGrid by setting the DataGridExcelExportingOption.ExportRowHeight to true. The default value of the ExportRowHeight property is false.

C#

DataGridExcelExportingOption option = new DataGridExcelExportingOption();
option.ExportRowHeight = true;

ExcelVersion

The SfDataGrid allows exporting the data to Excel in specific versions by using the DataGridExcelExportingOption.ExcelVersion property.

C#

DataGridExcelExportingOption option = new DataGridExcelExportingOption();
option.ExcelVersion = Syncfusion.XlsIO.ExcelVersion.Excel2013;

ExportGroups

By default, all the groups in the data grid will be exported to Excel sheet. To export the data grid without Groups, set the DataGridExcelExportingOption.ExportGroups property to false.

C#

DataGridExcelExportingOption option = new DataGridExcelExportingOption();
option.ExportGroups = true;

• ExportGroups is true

• ExportGroups is false

ExportHeader

By default, the column headers will be exported to Excel sheet. To export the SfDataGrid without the column headers, set the DataGridExcelExportingOption.ExportHeader to false.

C#

DataGridExcelExportingOption option = new DataGridExcelExportingOption();
option.ExportHeader = false;

ExportTableSummary

By default, table summaries in the data grid will be exported to Excel. To export the SfDataGrid without table summaries, set the DataGridExcelExportingOption.ExportTableSummary property to false.

C#

DataGridExcelExportingOption option = new DataGridExcelExportingOption();
option.ExportTableSummary = false;

BottomTableSummaryStyle

The SfDataGrid supports exporting the bottom TableSummary with custom style by using the DataGridExcelExportingOption.BottomTableSummaryStyle property.

C#

DataGridExcelExportingOption option = new DataGridExcelExportingOption();
option.BottomTableSummaryStyle = new ExportCellStyle()
{
    BackgroundColor = UIColor.Purple,
    BorderColor = UIColor.White,
    ForegroundColor = UIColor.Green,
};

GroupCaptionStyle

The SfDataGrid supports exporting the GroupCaptionSummaries with custom style by using the DataGridExcelExportingOption.GroupCaptionStyle property.

C#

DataGridExcelExportingOption option = new DataGridExcelExportingOption();
option.GroupCaptionStyle = new ExportCellStyle()
{
    BackgroundColor = UIColor.Yellow,
    BorderColor = UIColor.Green,
    ForegroundColor = UIColor.Green,
};

HeaderStyle

The SfDataGrid allows exporting the column headers with custom style by using the DataGridExcelExportingOption.HeaderStyle property.

C#

DataGridExcelExportingOption option = new DataGridExcelExportingOption();
option.HeaderStyle = new ExportCellStyle()
{
    BackgroundColor = UIColor.White,
    BorderColor = UIColor.Black,
    ForegroundColor = UIColor.Black,
};

RecordStyle

The SfDataGrid allows exporting the records with custom style by using the DataGridExcelExportingOption.RecordStyle property.

C#

DataGridExcelExportingOption option = new DataGridExcelExportingOption();
option.RecordStyle = new ExportCellStyle()
{
    BackgroundColor = UIColor.Yellow,
    BorderColor = UIColor.Black,
    ForegroundColor = UIColor.Black,
};

TopTableSummaryStyle

The SfDataGrid supports exporting the top TableSummary with custom style by using the DataGridExcelExportingOption.TopTableSummaryStyle property.

C#

DataGridExcelExportingOption option = new DataGridExcelExportingOption();
option.TopTableSummaryStyle = new ExportCellStyle()
{
    BackgroundColor = UIColor.Purple,
    BorderColor = UIColor.White,
    ForegroundColor = UIColor.White,
};

ExportGroupSummary

By default, the GroupSummary rows in the data grid will be exported to Excel. To export the SfDataGrid without group summaries, set the DataGridExcelExportingOption.ExportGroupSummary property to false.

C#

DataGridExcelExportingOption option = new DataGridExcelExportingOption();
// Set false here to export the DataGrid without GroupSummary rows. The default value is true.
//option.ExportGroupSummary = false;

GroupSummaryStyle

SfDataGrid supports exporting the GroupSummary rows with custom style by using the DataGridExcelExportingOption.GroupSummaryStyle property.

C#

DataGridExcelExportingOption option = new DataGridExcelExportingOption();
option.GroupSummaryStyle = new ExportCellStyle()
{
    BackgroundColor = UIColor.Red,
    BorderColor = UIColor.Yellow,
    ForegroundColor = UIColor.White,
};

StartColumnIndex

By default, the exported SfDataGrid will start from the 0th column in the Excel sheet but, you can specify the start column in the Excel sheet using the DataGridExcelExportingOption.StartColumnIndex property.

C#

DataGridExcelExportingOption option = new DataGridExcelExportingOption();
option.StartColumnIndex = 2;

StartRowIndex

By default, the exported SfDataGrid will start from the 0th row in the Excel sheet but, you can specify the start row in the Excel sheet using the DataGridExcelExportingOption.StartRowIndex property.

C#

DataGridExcelExportingOption option = new DataGridExcelExportingOption();
option.StartRowIndex = 5;

ExportAllPages

While exporting to Excel using the SfDataPager inside the SfDataGrid, by default it will export only the current page. However, you can export all the pages by setting the DataGridExcelExportingOption.ExportAllPages to true. The default value for this property is false.

C#

DataGridExcelExportingOption option = new DataGridExcelExportingOption();
option.ExportAllPages = true;

• ExportAllPages is true

Events

The SfDataGrid provides you the following events for exporting to excel:

• RowExporting: Raised while exporting a row at the execution time.
• CellExporting: Raised while exporting a cell at the execution time.

RowExporting

The DataGridRowExcelExportingEventHandler delegate allows customizing the styles for the record rows, group caption rows, and group summary rows. The RowExporting event is triggered with DataGridRowExcelExportingEventArgs that contains the following properties:

• Range: Specifies the Excel range to be exported. It provides full access to the exporting cell in Excel.
• Record: Gets the collection of the exported underlying data objects.
• RowType: Specifies the row type by using ExportRowType Enum. You can use this property to check the row type and apply different styles based on the row type.
• Worksheet: Sets the Worksheet properties such as sheet protection, gridlines, and so on.

You can use this event to customize the properties of the grid rows exported to Excel. The following code example illustrates how to change the background color of the record rows, caption summary rows, and group summary rows when exporting.

C#

//HandlingRowExportingEvent for exporting to excel
DataGridExcelExportingController excelExport = new DataGridExcelExportingController ();
excelExport.RowExporting += excelExport_RowExporting; 

void excelExport_RowExporting (object sender, DataGridRowExcelExportingEventArgs e)
{
    if (e.RowType == ExportRowType.Record) {
        if ((e.Record.Data as OrderInfo).IsClosed)
            e.Range.CellStyle.ColorIndex = Syncfusion.XlsIO.ExcelKnownColors.Yellow;
        else
            e.Range.CellStyle.ColorIndex = Syncfusion.XlsIO.ExcelKnownColors.LightGreen;
    }
    
    // You can also set the desired background colors for the CaptionSummary row and GroupSummary row as shown below
    //if (e.RowType == ExportRowType.CaptionSummary) {
        //e.Range.CellStyle.ColorIndex = Syncfusion.XlsIO.ExcelKnownColors.Grey_25_percent;
    //}

    //if (e.RowType == ExportRowType.GroupSummary) {
        //e.Range.CellStyle.ColorIndex = Syncfusion.XlsIO.ExcelKnownColors.Red;
    //}
}

CellExporting

The DataGridCellExcelExportingEventHandler delegate allows customizing the styles for the header cells, record cells, group caption cells, and group summary cells. The CellExporting event is triggered with DataGridCellExcelExportingEventArgs that contains the following properties:

• CellType: Specifies the cell type by using ExportCellType Enum. You can use this property to check the cell type and apply different cell styles based on the cell type.
• CellValue: Contains the actual value exported to the Excel. You can use this value to apply formatting in Excel using the Range property.
• ColumnName: Specifies the column name (MappingName) of the exporting cell. You can apply formatting for a particular column by checking the ColumnName.
• Handled: Determines whether the cell is exported to Excel or not.
• Range: Specifies the Excel range to be exported. It provides full access to the exporting cell in Excel.
• Record: Gets the collection of the exported underlying data objects.

You can use this event to customize the properties of the grid cells exported to Excel. The following code example illustrates how to customize the background color, foreground color, and cell value of the header cells, record cells, caption summary cells, and group summary cells when exporting.

C#

//HandlingCellExportingEvent for exporting to excel
DataGridExcelExportingController excelExport = new DataGridExcelExportingController ();
excelExport.CellExporting += excelExport_CellExporting;  

void excelExport_CellExporting(object sender, DataGridCellExcelExportingEventArgs e)
{
    if (e.CellType == ExportCellType.HeaderCell) {
        e.Range.CellStyle.ColorIndex = Syncfusion.XlsIO.ExcelKnownColors.Blue;
        e.Range.CellStyle.PatternColorIndex = Syncfusion.XlsIO.ExcelKnownColors.White;
        e.Range.CellStyle.BeginUpdate();
       e.Range.CellStyle.Borders.LineStyle = Syncfusion.XlsIO.ExcelLineStyle.Dash_dot_dot;
        e.Range.CellStyle.Borders[ExcelBordersIndex.DiagonalDown].LineStyle = ExcelLineStyle.Dash_dot_dot;
        e.Range.CellStyle.Borders[ExcelBordersIndex.DiagonalUp].LineStyle = ExcelLineStyle.Dash_dot_dot;
        e.Range.CellStyle.Borders.Color = ExcelKnownColors.Black;
        e.Range.CellStyle.EndUpdate();
    }

    if (e.CellType == ExportCellType.RecordCell) {
        e.Range.CellStyle.ColorIndex = Syncfusion.XlsIO.ExcelKnownColors.Yellow;
        e.Range.CellStyle.PatternColorIndex = Syncfusion.XlsIO.ExcelKnownColors.Black;
        e.Range.CellStyle.BeginUpdate();
       e.Range.CellStyle.Borders.LineStyle = Syncfusion.XlsIO.ExcelLineStyle.Dash_dot_dot;
        e.Range.CellStyle.Borders[ExcelBordersIndex.DiagonalDown].LineStyle = ExcelLineStyle.Dash_dot_dot;
        e.Range.CellStyle.Borders[ExcelBordersIndex.DiagonalUp].LineStyle = ExcelLineStyle.Dash_dot_dot;
        e.Range.CellStyle.Borders.Color = ExcelKnownColors.Black;
        e.Range.CellStyle.EndUpdate();
    }
    
    // You can also set the desired values for the CaptionSummary rows and GroupSummary rows as shown below
    //if (e.CellType == ExportCellType.GroupCaptionCell) {
        //e.Range.CellStyle.ColorIndex = Syncfusion.XlsIO.ExcelKnownColors.Grey_25_percent;
        //e.Range.CellStyle.PatternColorIndex = Syncfusion.XlsIO.ExcelKnownColors.Blue;
        //e.Range.CellStyle.BeginUpdate();
        //e.Range.CellStyle.Borders.LineStyle = Syncfusion.XlsIO.ExcelLineStyle.Dash_dot_dot;
        //e.Range.CellStyle.Borders[ExcelBordersIndex.DiagonalDown].LineStyle = ExcelLineStyle.Dash_dot_dot;
        //e.Range.CellStyle.Borders[ExcelBordersIndex.DiagonalUp].LineStyle = ExcelLineStyle.Dash_dot_dot;
        //e.Range.CellStyle.Borders.Color = ExcelKnownColors.Black;
        //e.Range.CellStyle.EndUpdate();
    //}

    //if (e.CellType == ExportCellType.GroupSummaryCell){
        //e.Range.CellStyle.ColorIndex = Syncfusion.XlsIO.ExcelKnownColors.Light_yellow;
        //e.Range.CellStyle.PatternColorIndex = Syncfusion.XlsIO.ExcelKnownColors.Black;
        //e.Range.CellStyle.BeginUpdate();
        //e.Range.CellStyle.Borders.LineStyle = Syncfusion.XlsIO.ExcelLineStyle.Dashed;
        //e.Range.CellStyle.Borders[ExcelBordersIndex.DiagonalDown].LineStyle = ExcelLineStyle.Dashed;
        //e.Range.CellStyle.Borders[ExcelBordersIndex.DiagonalUp].LineStyle = ExcelLineStyle.Dashed;
        //e.Range.CellStyle.Borders.Color = ExcelKnownColors.Red;
        //e.Range.CellStyle.EndUpdate();
    //}
}

Custom Borders

The SfDataGrid allows exporting the data to Excel with custom borders style in the Excel sheet by handling the CellExporting event.

C#

private void ExcelExport_CellExporting(object sender, DataGridCellExcelExportingEventArgs e)
{
    if (e.CellType == ExportCellType.RecordCell)
    {
        e.Range.CellStyle.BeginUpdate();
        e.Range.CellStyle.Borders.LineStyle = Syncfusion.XlsIO.ExcelLineStyle.Dash_dot_dot;
        e.Range.CellStyle.Borders[ExcelBordersIndex.DiagonalDown].LineStyle = ExcelLineStyle.Dash_dot_dot;
        e.Range.CellStyle.Borders[ExcelBordersIndex.DiagonalUp].LineStyle = ExcelLineStyle.Dash_dot_dot;
        e.Range.CellStyle.Borders.Color = ExcelKnownColors.Black;
        e.Range.CellStyle.EndUpdate();
    }
}

Save a File

The following code snippet explains how to save the converted Excel sheet in our local device.

C#

private void Save(string filename, string contentType, MemoryStream stream)
{
    string exception = string.Empty;
    string path = Environment.GetFolderPath(Environment.SpecialFolder.Personal);
    string filePath = Path.Combine(path, filename);
    try
    {
        FileStream fileStream = File.Open(filePath, FileMode.Create);
        stream.Position = 0;
        stream.CopyTo(fileStream);
        fileStream.Flush();
        fileStream.Close();
    }
    catch (Exception e)
    {
        exception = e.ToString();
    }
    
    if (contentType == "application/html" || exception != string.Empty)
        return;
    
    UIViewController currentController = UIApplication.SharedApplication.KeyWindow.RootViewController;
    while (currentController.PresentedViewController != null)
        currentController = currentController.PresentedViewController;
    UIView currentView = currentController.View;

    QLPreviewController qlPreview = new QLPreviewController();
    QLPreviewItem item = new QLPreviewItemBundle(filename, filePath);
    qlPreview.DataSource = new PreviewControllerDS(item);

    currentController.PresentViewController((UIViewController)qlPreview, true, (Action)null);
}

public class QLPreviewItemFileSystem : QLPreviewItem
{
    string _fileName, _filePath;

    public QLPreviewItemFileSystem(string fileName, string filePath)
    {
        _fileName = fileName;
        _filePath = filePath;
    }

    public override string ItemTitle
    {
        get
        {
            return _fileName;
        }
    }
    
    public override NSUrl ItemUrl
    {
        get
        {
            return NSUrl.FromFilename(_filePath);
        }
    }
}

public class QLPreviewItemBundle : QLPreviewItem
{
    string _fileName, _filePath;
	
    public QLPreviewItemBundle(string fileName, string filePath)
    {
        _fileName = fileName;
        _filePath = filePath;
    }

    public override string ItemTitle
    {
        get
        {
            return _fileName;
        }
    }
    
    public override NSUrl ItemUrl
    {
        get
        {
            var documents = NSBundle.MainBundle.BundlePath;
	     var lib = Path.Combine(documents, _filePath);
            var url = NSUrl.FromFilename(lib);
            return url;
	 }
    }
}

public class PreviewControllerDS : QLPreviewControllerDataSource
{
    private QLPreviewItem _item;

    public PreviewControllerDS(QLPreviewItem item)
    {
        _item = item;
    }

    public override nint PreviewItemCount (QLPreviewController controller)
    {
        return (nint)1;
    }

    public override IQLPreviewItem GetPreviewItem (QLPreviewController controller, nint index)
    {
        return _item;
    }
}

Exporting Unbound rows

By default, the Unbound rows will not be exported to the excel document. However, you can export the unbound rows to excel by setting the DataGridExcelExportingOption.ExportUnboundRows property as true.

C#

DataGridExcelExportingOption option = new DataGridExcelExportingOption();
option.ExportUnboundRows = true;

Exporting Unbound Columns

The SfDataGrid.GridUnboundColumns will be exported as SfDataGrid.GridTextColumns without any specific codes. You can customize the SfDataGrid.GridUnboundColumns as SfDataGrid.GridTextColumns using the CellExporting and RowExporting events.

The following code illustrates how to create and export unbound columns.

C#

var unboundColumn = new GridUnboundColumn()
{
    MappingName = "Unbound",
    Expression = "OrderID",
};

sfGrid.Columns.Add(unboundColumn);

The following screenshot shows that the unbound column is exported to excel sheet along with text columns.

Exporting the selected rows of SfDataGrid

SfDataGrid allows you to export only the currently selected rows in the grid to the worksheet using the DataGridExcelExportingController.ExportToExcel method by passing the instance of the SfDataGrid and SfDataGrid.SelectedItems collection as an argument.

Refer the below code to export the selected rows alone to Excel.

C#

private void ExPortToExcel(object sender, EventArgs e)
        {
            DataGridExcelExportingController excelExport = new DataGridExcelExportingController();
            ObservableCollection<object> selectedItems = dataGrid.SelectedItems;
            var excelEngine = excelExport.ExportToExcel(this.dataGrid, selectedItems);
            var workbook = excelEngine.Excel.Workbooks[0];
            MemoryStream stream = new MemoryStream();
            workbook.SaveAs(stream);
            workbook.Close();
            excelEngine.Dispose();
            Save("DataGrid.xlsx", "application/msexcel", stream);
        }

The following screenshot shows that the selected rows are exported to excel sheet.