How to export the SfDataGrid to Excel with Cell styles

[Johnes]

i already read this article but this is not helpful

https://www.syncfusion.com/kb/5987/how-to-export-the-sfdatagrid-to-excel-with-styles

i am using StyleSelector base for each Cell value.

any way to get each cell style and assign to the excel?

[Vijayarasan Sivanandham]

Hi Johnes,

Thank you for contacting Syncfusion Support.

Currently, we are analyzing your requirement of “How to export the SfDataGrid to Excel with Cell styles” We will validate and update you the details on or before November 10, 2021.

We appreciate your patience until then.

Regards,
Vijayarasan S

[Vijayarasan Sivanandham]

Hi Johnes,

Thank you for your patience.

We are still working on this. We will update with further details on or before November 12, 2021. We appreciate your patience and understanding.

We appreciate your patience until then.

Regards,
Vijayarasan S

[Johnes]

right now my solution is using old method

Microsoft.Office.Interop.Excel

to loop through all value and cell style condition,

assign to excel one by one

the performance is bad but still did the job.

I also have a idea to port everything to gridcontrol

Using the Excel Engine (XlsIO)

but not test it yet.

[Vijayarasan Sivanandham]

Hi Johnes,

Thanks for the update.

Your requirement can be achieved by getting the style using FindResource method and assign the style to e.Range.CellStyle.FillBackgroundRGB in CellsExportingEventHandler.

C#: Getting the style properties values using FindResource method.

 

public static Color blueCellBackgroundColor; 

public static Color redCellBackgroundColor; 

 

private static void GetDataGridStyles(SfDataGrid dataGrid) 

{ 

             

            var blueCellStyle = dataGrid.FindResource("blueCellStyle") as Style; 

            var redCellStyle = dataGrid.FindResource("redCellStyle") as Style; 

 

            if (blueCellStyle == null || redCellStyle == null) 

                return; 

 

            //here get the blueCellStyle background color  

            foreach (Setter setter in blueCellStyle.Setters) 

            { 

                if (setter.Property == GridCell.BackgroundProperty) 

                    blueCellBackgroundColor = (Color)ColorConverter.ConvertFromString(setter.Value.ToString()); 

            } 

            //here get the redCellStyle background color  

            foreach (Setter setter in redCellStyle.Setters) 

            { 

                if (setter.Property == GridCell.BackgroundProperty) 

                    redCellBackgroundColor = (Color)ColorConverter.ConvertFromString(setter.Value.ToString()); 

            } 

 

} 

 

private void OnExportToExcelClicked(object sender, RoutedEventArgs e) 

{ 

            //here get the style  

            GetDataGridStyles(sfDataGrid); 

            var options = new ExcelExportingOptions(); 

            options.ExcelVersion = ExcelVersion.Excel2013; 

             

            options.CellsExportingEventHandler = CellExportingHandler; 

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

            var workBook = excelEngine.Excel.Workbooks[0]; 

} 

C#: Assign style property values from above code in CellExportingHandler.

 

private static void CellExportingHandler(object sender, GridCellExcelExportingEventArgs e) 

{             

            // Based on the column mapping name and the cell type, we can change the cell  

            //values while exporting to excel. 

            //here customize based on your scenario 

            if (e.CellType == ExportCellType.RecordCell && e.ColumnName == "OrderID") 

            { 

                //here apply cell style based on condition 

                if ((int)e.CellValue < 1005) 

                    e.Range.CellStyle.FillBackgroundRGB = System.Drawing.Color.FromArgb(redCellBackgroundColor.A, redCellBackgroundColor.R, redCellBackgroundColor.G, redCellBackgroundColor.B);                 

                else                

                    e.Range.CellStyle.FillBackgroundRGB = System.Drawing.Color.FromArgb(blueCellBackgroundColor.A, blueCellBackgroundColor.R, blueCellBackgroundColor.G, blueCellBackgroundColor.B);                    

                 

            } 

} 

Sample Link:  https://www.syncfusion.com/downloads/support/forum/170184/ze/SfDataGridDemo1258871440

For more information related to Cell customization in Excel while exporting, please refer the below user guide documentation link,

UG Link: https://help.syncfusion.com/wpf/datagrid/export-to-excel#cell-customization-in-excel-while-exporting

Please let us know if you have any concerns in this.

Regards,
Vijayarasan S 

[Johnes]

thanks for the support.

I have a concern about this method have to be obsolete on next release.

[Johnes]

one more thing I missed to mention 

on my StyleSelector i use other cell value to set the style,

how can I get the other cell base on the GridCellExcelExportingEventArgs ?

[Vijayarasan Sivanandham]

Hi Johnes, 

Thanks for the update.

We would like to let you know that you can get the other cells value by using NodeEntry property of GridCellExcelExportingEventArgs in CellsExportingEventHandler. Please refer to the below code snippet, 

private static void CellExportingHandler(object sender, GridCellExcelExportingEventArgs e) 

{             

            // Based on the column mapping name and the cell type, we can change the cell  

            //values while exporting to excel. 

            //here customize based on your scenario 

            if (e.CellType == ExportCellType.RecordCell && e.ColumnName == "CustomerID") 

            { 

                //here get the datarow details 

                var dataRow = e.NodeEntry as DataRowView; 

 

                //here apply cell style based on condition 

                if ((int)dataRow.Row["EmployeeID"] < 1005) 

                    e.Range.CellStyle.FillBackgroundRGB = System.Drawing.Color.FromArgb(redCellBackgroundColor.A, redCellBackgroundColor.R, redCellBackgroundColor.G, redCellBackgroundColor.B);                 

                else                

                    e.Range.CellStyle.FillBackgroundRGB = System.Drawing.Color.FromArgb(blueCellBackgroundColor.A, blueCellBackgroundColor.R, blueCellBackgroundColor.G, blueCellBackgroundColor.B);                    

                 

            } 

} 

Sample Link: https://www.syncfusion.com/downloads/support/forum/170184/ze/SfDataGridDemo1663363762

For more information related to Changing row style in excel based on data, please refer the below user guide documentation link,

UG Link: https://help.syncfusion.com/wpf/datagrid/export-to-excel#changing-row-style-in-excel-based-on-data

Please let us know if you have any concerns in this. 

Regards, 

Vijayarasan S