Unable to show cell backcolor of the GridControl while exporting to excel

[ramnadh]

We are using syncfusion Essential Studio 7.1.0.30 Grid control for showing the data in the tabular form.

We have a screen where we will have some cells in the grid shown with backcolor and fontcolor dynamically based on the conditions.

When we are trying to export the grid data to excel, we are unable to export the cell backcolors and fontcolors to the excel sheet.

We have tried using the Excel ConditionalFormats, but not successful.

IConditionalFormats cond = e.ExcelCell.ConditionalFormats;
IConditionalFormat cond1 = cond.AddCondition();
cond1.FirstFormula = "0";
cond1.SecondFormula = "0";
cond1.Operator = ExcelComparisonOperator.Equal;
cond1.FormatType = ExcelCFType.CellValue;
cond1.BackColorRGB = Color.FromArgb(e.GridCell.BackColor.R, e.GridCell.BackColor.G, e.GridCell.BackColor.B);
e.ExcelCell.Value = text;

With the above code we see one of the color (yellow) is getting shown, but not the other RGB colors like Red, Green are not showing up.

Could anyone help us in defining the correct API for exporting the colors into the excel from the syncfusion GridControl

[Jisha Joy]

Hi Ramanadh,

Thank you for using Syncfusion products.

You could use GridExcelConverterControl class to export the grid contents along with styles to Excel sheet. See the code:

Syncfusion.GridExcelConverter.GridExcelConverterControl gecc = new Syncfusion.GridExcelConverter.GridExcelConverterControl();
gecc.GridToExcel(this.gridControl1.Model, FileName);

Sample can be accessed from the browser sample.


..\\Windows\Grid.Windows\Samples\2.0\Export\GC XLS Export Demo\cs

Regards,
Jisha

[ramnadh]

Hi Joy,
Appreciate your help in resolving the issue.
We are using the same GridExcelConverterControl.GridToExcel for exporting the grid to excel, but the problem we had is that we have customized the exporting the excel by registering the event QueryImportExportCellInfo and overriding the values for the cell types DataTypeCustomProperty.CellDataType.String, DataTypeCustomProperty.CellDataType.Date and DataTypeCustomProperty.CellDataType.DateTime

Example:
private void gridConverter_QueryImportExportCellInfo(object sender, GridImportExportCellInfoEventArgs e)
{
if (e.Action == Syncfusion.GridExcelConverter.GridConverterAction.Export)
{
GridDrawCellDisplayTextEventArgs e_ = new GridDrawCellDisplayTextEventArgs (null, e.GridCell.Text, Rectangle.Empty, e.GridCell);

OnDrawCellDisplayText (e_);

string text = (e_.DisplayText != null)? e_.DisplayText:e.GridCell.Text;

DataTypeCustomProperty dtcp = new DataTypeCustomProperty (e.GridCell);

switch ((dtcp.HasDataType)? dtcp.DataType:DataTypeCustomProperty.CellDataType.Undefined)
{
case DataTypeCustomProperty.CellDataType.String: e.ExcelCell.Value = text;
DataTypeCustomProperty.CellDataType.Date: e.ExcelCell.Value = /*Modifying format based on configuration*/;
default:
e.ExcelCell.Value = text;
}
e.Handled = true;

The problem is we when we register the event and overriding, we are formatting the display text, but not the formats.

To achieve formatting to the ExcelCell object, could you provide us an example or a reference, which will be helpful to us.

Thanks

[ramnadh]

Hi joy,

I think we are able to solve the issue by setting the e.ExcelCell.CellStyle properties.

e.ExcelCell.CellStyle.Color = e.GridCell.BackColor;
e.ExcelCell.CellStyle.Font.RGBColor = e.GridCell.TextColor;
e.ExcelCell.BorderAround(ExcelLineStyle.Thin, Color.Black);

We have set the background color, font color and border color.

Do you think this paradigm is correct? or do you suggest any other way to export the colors.

Thanks

[Jisha Joy]

Hi Ramanadh,

Glad to know that issue got resolved. Thank you for your update.

Regards,
Jisha