How to export the SfDataGrid to Excel with Cell styles

Question

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 · Accepted Answer

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/SfDataGridDemo1663363762For 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-dataPlease let us know if you have any concerns in this. 

Regards, 
Vijayarasan S

Vijayarasan Sivanandham · Answer

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 · Answer

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 · Answer

right now

my solution is using old methodMicrosoft.Office.Interop.Excelto loop through all value and cell style condition,

assign to excel one by onethe 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 · Answer

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/SfDataGridDemo1258871440For 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-exportingPlease let us know if you have any concerns in this.Regards,Vijayarasan S

Johnes · Answer

thanks for the support.I have a concern about this method have to be obsolete on next release.

Johnes · Answer

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 ?