1. Home
  2. Forum
  3. WinForms
  4. Format cell based on another cell value in row on export to excel

Format cell based on another cell value in row on export to excel

I am trying to format the exported excel file the same as the sfDatagrid backcolor, forcolor and font.

I can see format rows and columns but not individual cells in a row where a value of another cell is equal to a certain value.

Is this possible.

Kind regards,

Neil



8 Replies 1 reply marked as answer
MA Mohanram Anbukkarasu Syncfusion Team February 22, 2021 07:37 AM UTC

Hi Neil, 

Thanks for contacting Syncfusion support. 

You can set background, foreground and font style for the cells in the exported excel document based on the cell value as shown in the following code example. 

Code example :  

var options = new ExcelExportingOptions(); 
options.CellExporting += Options_CellExporting; 
sfDataGrid1.ExportToExcel(sfDataGrid1.View, options); 

private void Options_CellExporting(object sender, Syncfusion.WinForms.DataGridConverter.Events.DataGridCellExcelExportingEventArgs e) 
{ 
    if (e.CellValue.ToString() == "FRANS") 
    { 
        e.Range.CellStyle.Color = Color.SkyBlue; 
        e.Range.CellStyle.Font.Color = ExcelKnownColors.Red; 
        e.Range.CellStyle.Font.FontName = "Arial"; 
        e.Range.CellStyle.Font.Bold = true; 
    } 
} 

 


Please let us know if you require further assistance from us.  

Regards, 
Mohanram A. 


NE Neil February 23, 2021 07:09 AM UTC

Thank you for replying.

The e.cellvalue needs to be a value from another cell in the row. If this other cellvalue is x then the current cell would need to be format depending on other cell value. Not sure how to access a another cell value than the one that i need to format.

Kind regards, Neil

MA Mohanram Anbukkarasu Syncfusion Team February 24, 2021 09:18 AM UTC

Hi Neil, 

Thanks for the update.  

You can achieve your requirement to set background, foreground and font style for a cell in the exported excel document based on the another cell value as shown in the following code example.  

Code example :  

var options = new ExcelExportingOptions();  
options.CellExporting += Options_CellExporting;  
sfDataGrid1.ExportToExcel(sfDataGrid1.View, options);  
 
private void Options_CellExporting(object sender, Syncfusion.WinForms.DataGridConverter.Events.DataGridCellExcelExportingEventArgs e) 
{ 
     var record = e.NodeEntry as OrderInfo; 
 
     // Style for OrderID column is changed based on the values in CustomerID column 
     if(e.ColumnName == "OrderID") 
     { 
         if(record != null && record.CustomerID == "FRANS") 
         { 
             e.Range.CellStyle.Color = Color.SkyBlue; 
             e.Range.CellStyle.Font.Color = ExcelKnownColors.Red; 
             e.Range.CellStyle.Font.FontName = "Arial"; 
             e.Range.CellStyle.Font.Bold = true; 
         } 
     } 
} 



Please let us know if you require further assistance from us.  

Regards, 
Mohanram A. 


NE Neil February 24, 2021 11:03 AM UTC

Thank you, I have seen this but not using any class like orderinfo. The data source is a dataset table and will look again.

NE Neil February 24, 2021 07:35 PM UTC

I have created a class that is the same as the dataset table and zipped. In the Options_CellExporting reference the class, while debuging the record variable is always null so no format  is applying. Have I interpreted the example incorrect.

        private void radButtonExportToExcel_Click(object sender, EventArgs e)
        {

            var options = new ExcelExportingOptions();
            options.CellExporting += Options_CellExporting;


            var excelEngine = sfDataGridSectionalTimes.ExportToExcel(sfDataGridSectionalTimes.View, options);
            var workBook = excelEngine.Excel.Workbooks[0];

            SaveFileDialog saveFilterDialog = new SaveFileDialog
            {
                FilterIndex = 2,
                Filter = "Excel 97 to 2003 Files(*.xls)|*.xls|Excel 2007 to 2010 Files(*.xlsx)|*.xlsx|Excel 2013 File(*.xlsx)|*.xlsx"
            };

            if (saveFilterDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
                using (Stream stream = saveFilterDialog.OpenFile())
                {
                    if (saveFilterDialog.FilterIndex == 1)
                        workBook.Version = ExcelVersion.Excel97to2003;
                    else if (saveFilterDialog.FilterIndex == 2)
                        workBook.Version = ExcelVersion.Excel2010;
                    else
                        workBook.Version = ExcelVersion.Excel2013;
                    workBook.SaveAs(stream);
                }

                //Message box confirmation to view the created workbook.
                if (MessageBox.Show(this.sfDataGridSectionalTimes, "Do you want to view the workbook?", "Workbook has been created",
                                    MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
                {

                    //Launching the Excel file using the default Application.[MS Excel Or Free ExcelViewer]
                    System.Diagnostics.Process.Start(saveFilterDialog.FileName);
                }
            }
        }

        private void Options_CellExporting(object sender, Syncfusion.WinForms.DataGridConverter.Events.DataGridCellExcelExportingEventArgs e)
        {
            var record = e.NodeEntry as SectionalInfo;

            // Style for S1 column is changed based on the values in SPC1 column
            if (e.ColumnName == "S1")
            {
                if (record != null && record.SPC1 == "fast")
                {
                    e.Range.CellStyle.Color = Color.SkyBlue;
                    e.Range.CellStyle.Font.Color = ExcelKnownColors.Red;
                    e.Range.CellStyle.Font.FontName = "Arial";
                    e.Range.CellStyle.Font.Bold = true;
                }
            }
        }

Attachment: SectionalInfo_789f5fa2.7z

MA Mohanram Anbukkarasu Syncfusion Team February 25, 2021 11:53 AM UTC

Hi Neil, 

Thanks for the update.  

You achieve the same when using DataTable as shown in the following code example.  

Code example :  

private void Options_CellExporting(object sender, Syncfusion.WinForms.DataGridConverter.Events.DataGridCellExcelExportingEventArgs e) 
{ 
    if(e.NodeEntry != null && e.ColumnName == "Order ID") 
    { 
        if((e.NodeEntry as DataRowView).Row["Customer ID"] == "FRANS") 
        { 
            e.Range.CellStyle.Color = Color.SkyBlue; 
            e.Range.CellStyle.Font.Color = ExcelKnownColors.Red; 
            e.Range.CellStyle.Font.FontName = "Arial"; 
            e.Range.CellStyle.Font.Bold = true; 
        } 
    } 
} 


Please let us know if you require further assistance from us.  

Regards, 
Mohanram A. 


Marked as answer
NE Neil February 27, 2021 02:23 PM UTC

Much appreciated thank you, works exactly the way I expected.

MA Mohanram Anbukkarasu Syncfusion Team March 1, 2021 05:04 AM UTC

Hi Neil, 

Thanks for the update. 

We are glad to know that the provided solution worked at your end. Please let us know if you have any further queries on this. We are happy to help you. 

Regards, 
Mohanram A. 


Loader.
Up arrow icon