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
Sample Link:
C#: Export formula cell with format_CS
VB: Export formula cell with format_VB