How to export formula cells with formatted text only and cell style to excel from the grid?
By default, the WinForms GridControl, when a formula cell is exported to Excel, both the result of the formula and the 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.
//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);
}
}'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 The
Screenshot below shows the exporting of formula cells with style.
Sample Links:
