Articles in this section
Category / Section

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

2 mins read

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

 

 

Did you find this information helpful?
Yes
No
Help us improve this page
Please provide feedback or comments
Comments (0)
Please sign in to leave a comment
Access denied
Access denied