We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. (Last updated on: November 16, 2018).
Unfortunately, activation email could not send to your email. Please try again.
Syncfusion Feedback

How to export the formula cells with formatted text only and cell style to excel from grid?

Platform: WinForms |
Control: GridControl |
Published Date: May 11, 2018 |
Last Revised Date: May 27, 2019

By default, in the GridControl, when a formula cell is exported to Excel, both the result of formula and formula text will be exported. To export the result with format and cell styles applied to the formula cell of the grid, create a custom GridExcelConverterControl class; override the ExportCellToExcel() method; set the result to the Excel cell. The result of the formula cell will be displayed by the GridStyleInfo.FormattedText property of that cell.

Code Snippet

C#

//Creating Custom Class
private class GridExcelConverterControlAdv : GridExcelConverterControl
{
    public GridExcelConverterControlAdv()
    {
    }
    protected override void ExportCellToExcel(GridStyleInfo gridCell, IRange range)
    {
        base.ExportCellToExcel(gridCell, range);
        switch (gridCell.CellType)
        {
            case "FormulaCell":
                if (gridCell.Text.Length != 0 && gridCell.Text[0] == '=')
                {
                    //To set the text alone in the formula cell.
                    range.Value = gridCell.FormattedText;
                }
                break;
        }
    }
}
 
private void exportBtn_Click(object sender, EventArgs e)
{
    GridExcelConverterControlAdv excelConverter = new GridExcelConverterControlAdv();
    if (saveFileDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
    {
        strFileLocation = saveFileDialog.FileName;
        //To export the grid to excel with style and format.
        excelConverter.GridToExcel(gridControl1, sheet, ConverterOptions.Default);
    }
}

 

VB

'Custom class
Private Class GridExcelConverterControlAdv
 Inherits GridExcelConverterControl 
 Public Sub New()
 End Sub
 Protected Overrides Sub ExportCellToExcel(ByVal gridCell As GridStyleInfo, ByVal range As IRange)
  MyBase.ExportCellToExcel(gridCell, range)
  Select Case gridCell.CellType
   Case "FormulaCell"
    If gridCell.Text.Length <> 0 AndAlso gridCell.Text(0) = "="c Then
     'To set the text alone in the formula cell.
     range.Value = gridCell.FormattedText
    End If
  End Select
 End Sub
End Class
 
 
Private Sub exportBtn_Click(ByVal sender As Object, ByVal e As EventArgs) Handles button1.Click 
  Dim excelConverter As New GridExcelConverterControlAdv()
 If saveFileDialog.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
  strFileLocation = saveFileDialog.FileName
  'To export the grid to excel with style and format.
  excelConverter.GridToExcel(gridControl1, sheet, ConverterOptions.Default)
 End If
End Sub

 

Screenshot

Showing formula cell exported with style

 

Showing formula cell is exported with formatted text

Sample Link:

C#: Export formula cell with format_CS

VB: Export formula cell with format_VB

 

 

2X faster development

The ultimate WinForms UI toolkit to boost your development speed.
ADD COMMENT
You must log in to leave a comment

Please sign in to access our KB

This page will automatically be redirected to the sign-in page in 10 seconds.

Warning Icon You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.Close Icon