Easily Export WinUI DataGrid to Excel

TL;DR: Explore how to export data from the Syncfusion WinUI DataGrid to an Excel document. We covered topics, such as custom export options, styling exported rows, and exporting only selected rows.

The Syncfusion WinUI DataGrid is designed to display and manipulate tabular data efficiently. Its comprehensive feature set includes data binding, editing, sorting, filtering, and grouping. It is also optimized for handling millions of records and can easily manage high-frequency, real-time updates.

In this blog, we’ll explore how to export WinUI DataGrid data to an Excel file with code examples.

Note: Before proceeding, refer to the getting started with WinUI DataGrid documentation.

Exporting WinUI DataGrid to Excel

To export the WinUI DataGrid’s data to an Excel document, we should add the following NuGet packages in our app:

1. Syncfusion.Grid.WinUI: To add the WinUI DataGrid control.

2. Syncfusion.GridExport.WinUI: To export the DataGrid to Excel files.

Now, initialize the WinUI DataGrid control on your XAML page.

<dataGrid:SfDataGrid x:Name="SfDataGrid" 
                     DataContext="{StaticResource orderInfoViewModel}"
                     ItemsSource="{Binding OrdersDetails}"
                     GridLinesVisibility="Both"
                     AutoGenerateColumns="False"
                     ColumnWidthMode="Auto">
    <dataGrid:SfDataGrid.Columns>
        <dataGrid:GridNumericColumn HeaderText="Order ID" MappingName="OrderID" TextAlignment="Right"/>
        <dataGrid:GridDateColumn MappingName="OrderDate" HeaderText="Order Date" TextAlignment="Right" />
        <dataGrid:GridTextColumn HeaderText="Shipping City" MappingName="ShipCity" />
        <dataGrid:GridTextColumn HeaderText="Shipping Country" MappingName="ShipAddress" />
        <dataGrid:GridTextColumn HeaderText="Quantity" MappingName="Quantity" TextAlignment="Right"/>
        <dataGrid:GridNumericColumn HeaderText="Unit Price" MappingName="UnitPrice" DisplayNumberFormat="C2" Width="165" />
    </dataGrid:SfDataGrid.Columns>
</dataGrid:SfDataGrid>

All the methods for Excel exporting are available in the DataGridToExcelConverter class. By using the ExportToExcel method, you can export the DataGrid content to an Excel workbook and save it as an Excel file. The DataGridExcelExportExtensions class provides the extension methods for exporting the data from the DataGrid.

Refer to the following code example to export the data to an Excel file.

private void OnExportToExcelClick(object sender, RoutedEventArgs e)
{
    var options = new DataGridExcelExportOptions();
    options.ExcelVersion = ExcelVersion.Excel2013;
    var excelEngine = sfDataGrid.ExportToExcel(sfDataGrid.View, options);

    var workBook = excelEngine.Excel.Workbooks[0];
    MemoryStream outputStream = new MemoryStream();
    workBook.SaveAs(outputStream);
    SaveExcelWorkbook(outputStream, "OrderDetails");
}
async void SaveExcelWorkbook(MemoryStream stream, string filename)
{
    StorageFile stFile;
    if (!(Windows.Foundation.Metadata.ApiInformation.IsTypePresent("Windows.Phone.UI.Input.HardwareButtons")))
    {
        FileSavePicker savePicker = new FileSavePicker();
        savePicker.DefaultFileExtension = ".xlsx";
        savePicker.SuggestedFileName = filename;
        savePicker.FileTypeChoices.Add("Excel Documents", new List<string>() { ".xlsx" });
        var hwnd = System.Diagnostics.Process.GetCurrentProcess().MainWindowHandle;
        WinRT.Interop.InitializeWithWindow.Initialize(savePicker, hwnd);
        stFile = await savePicker.PickSaveFileAsync();
    }
    else
    {
        StorageFolder local = Windows.Storage.ApplicationData.Current.LocalFolder;
        stFile = await local.CreateFileAsync(filename, CreationCollisionOption.ReplaceExisting);
    }
    if (stFile != null)
    {
        using (IRandomAccessStream zipStream = await stFile.OpenAsync(FileAccessMode.ReadWrite))
        {
            //Write compressed data from memory to file.
            using (Stream outstream = zipStream.AsStreamForWrite())
            {
                byte[] buffer = stream.ToArray();
                outstream.Write(buffer, 0, buffer.Length);
                outstream.Flush();
            }
        }
        //Launch the saved Excel file.
        await Windows.System.Launcher.LaunchFileAsync(stFile);
    }
}

Refer to the following image.

Customizing the Excel exporting

We can also customize the export operation using the DataGridExcelExportOptions. Let’s see how to do so!

Export the DataGrid’s stacked headers to Excel

By default, only the column headers of the WinUI DataGrid will be exported. However, if you want to include the stacked headers in the exported Excel file, you can enable the CanExportStackedHeaders property while exporting.

private void OnExportDataGridClick(object sender, RoutedEventArgs e)
{
    var options = new DataGridExcelExportOptions();
    options.ExcelVersion = ExcelVersion.Excel2013;

    options.CanExportStackedHeaders = true;

    var excelEngine = sfDataGrid.ExportToExcel(sfDataGrid.View, options);
    var workBook = excelEngine.Excel.Workbooks[0];
    MemoryStream outputStream = new MemoryStream();
    workBook.SaveAs(outputStream);
    SaveExcelWorkbook(outputStream, "OrderDetails");
}

Refer to the following image.

Excluding specific DataGrid columns during export

By default, all columns are exported, including those that are hidden. You can exclude specific data columns in a grid from being exported to an Excel sheet.

Refer to the following code example. Here, the OrderDate and ShipAddress columns are excluded while exporting to Excel.

private void OnExportToExcelClick(object sender, RoutedEventArgs e)
{
    var options = new DataGridExcelExportOptions();
    options.ExcelVersion = ExcelVersion.Excel2013;

    options.ExcludedColumns.Add("OrderDate");
    options.ExcludedColumns.Add("ShipAddress");

    var excelEngine = sfDataGrid.ExportToExcel(sfDataGrid.View, options);

    var workBook = excelEngine.Excel.Workbooks[0];
    MemoryStream outputStream = new MemoryStream();
    workBook.SaveAs(outputStream);
    SaveExcelWorkbook(outputStream, "OrderDetails");
}

Refer to the following image.

Exporting the selected rows

Let’s see how to export only the selected rows in a DataGrid using the ExportToExcel method and passing instances of the SelectedItems collection.

Refer to the following code example.

private void OnExportSelectedRowsClick(object sender, RoutedEventArgs e)
{
    var options = new DataGridExcelExportOptions();
    options.ExcelVersion = ExcelVersion.Excel2013;
    var excelEngine = sfDataGrid.ExportToExcel(sfDataGrid.SelectedItems, options);

    var workBook = excelEngine.Excel.Workbooks[0];
    MemoryStream outputStream = new MemoryStream();
    workBook.SaveAs(outputStream);
    SaveExcelWorkbook(outputStream, "SelectedOrders");
}

Refer to the following image.

Customizing the starting row and column position in Excel

You can also customize the starting position of the row and column in the exported Excel sheet using the StartRowIndex and StartColumnIndex properties, respectively.

Refer to the code example.

private void OnExportToExcelClick(object sender, RoutedEventArgs e)
{
    var options = new DataGridExcelExportOptions();
    options.ExcelVersion = ExcelVersion.Excel2013;

    options.StartRowIndex = 4;
    options.StartColumnIndex = 2;

    var excelEngine = sfDataGrid.ExportToExcel(sfDataGrid.View, options);
    var workBook = excelEngine.Excel.Workbooks[0];
    MemoryStream outputStream = new MemoryStream();
    workBook.SaveAs(outputStream);
    SaveExcelWorkbook(outputStream, "OrderDetails");
}

Refer to the following image.

Applying row style while exporting

Let’s apply styles for cells or rows in the WinUI DataGrid while exporting using the CellsExportHandler. Here, the exported rows can be styled based on the Grid data.

Refer to the following code example.

private void OnExportToExcelClick(object sender, RoutedEventArgs e)
{
    var options = new DataGridExcelExportOptions();
    options.ExcelVersion = ExcelVersion.Excel2013;

    options.CellsExportHandler = CellsExportHandler;    

    var excelEngine = sfDataGrid.ExportToExcel(sfDataGrid.View, options);
    var workBook = excelEngine.Excel.Workbooks[0];
    MemoryStream outputStream = new MemoryStream();
    workBook.SaveAs(outputStream);
    SaveExcelWorkbook(outputStream, "OrderDetails");
}
private void CellsExportHandler(object sender, DataGridCellExcelExportOptions e)
{
    var record = e.NodeEntry;
    if (record != null && (record as OrderInfo).ShipAddress == "Brazil")
    {
        e.Range.CellStyle.ColorIndex = ExcelKnownColors.Blue_grey;
        e.Range.CellStyle.Font.Color = ExcelKnownColors.Light_yellow;
    }
}

Refer to the following image.

References

For more details, refer to Export WinUI DataGrid to Excel documentation and GitHub demo.

Conclusion

Thanks for reading! In this blog, we’ve explored how to export the Syncfusion WinUI DataGrid data to an Excel document. We encourage you to try these steps and share your feedback in the comments below.

Our WinUI demo app is available for download on the Microsoft Store. We would love to hear your feedback after you’ve tried it, so please leave your thoughts in the comment section below.

For our existing customers, the latest version of Essential Studio for WinUI is accessible from the License and Downloads page. If you are not a Syncfusion customer, feel free to download our free evaluation to discover all our controls.

You can also contact us through our support forum, support portal, or feedback portal. We are always happy to assist you!

Related blogs

• Easily Load Appointments in WinUI Scheduler with SQLite and Perform CRUD Actions

• From Stars to Hearts: Explore Creative Rating Designs in WinUI

• Chart of the Week: Creating a WinUI Stacked Area Chart to Visualize US Primary Energy Consumption

• Introducing the New WinUI Shimmer Control