Excel export format of cell borders

[Dan Garvin]

Export to Excel from a databoundgrid works well, but wondering if I can control the visual appearance of the exported worksheet once it's opened in Excel. The data cells have no borders (cell grids). Please see the attached screen print. Here is the code I use to create and save the xls file:

Syncfusion.GridExcelConverter.GridExcelConverterControl gecc = new Syncfusion.GridExcelConverter.GridExcelConverterControl();
string path = "";
if (dlgSaveFile.ShowDialog() == DialogResult.OK)
{
path = dlgSaveFile.FileName;
}

// Create Excel automation objects
IWorkbook myWorkBook = ExcelUtils.CreateWorkbook(1);
IWorksheet sheet = myWorkBook.Worksheets[0];

gecc.GridToExcel(gridSearchResults.Model, sheet, Syncfusion.GridExcelConverter.ConverterOptions.RowHeaders
| Syncfusion.GridExcelConverter.ConverterOptions.RowHeaders
| Syncfusion.GridExcelConverter.ConverterOptions.Default);

// Save the workbook to disk so we can launch it via the file system
myWorkBook.SaveAs(path);
myWorkBook.Close();
ExcelUtils.ThrowNotSavedOnDestroy = false;

Thanks in advance.
-Dan

ExcelPrint1.zip

[Administrator]

Hi Dan

We were not able to reproduce the issue here. Is it possible for you to upload us a sample or modify the ExcelExport_DataBoundGrid browser ( \Syncfusion\Essential Studio\4.4.0.49\windows\Grid.Windows\Samples\Serialization\ExcelExport_DataBoundGrid\cs ) sample to reproduce the issue here? This will help us to analyse the issue further.

Best regards,
Haneef

[Dan Garvin]

Hi Haneef:
Here is a sample that recreate the problem. It appears to have something to do with the underlying datatable, but I can't figure it out. Thanks for your help.
-Dan

>Hi Dan

We were not able to reproduce the issue here. Is it possible for you to upload us a sample or modify the ExcelExport_DataBoundGrid browser ( \Syncfusion\Essential Studio\4.4.0.49\windows\Grid.Windows\Samples\Serialization\ExcelExport_DataBoundGrid\cs ) sample to reproduce the issue here? This will help us to analyse the issue further.

Best regards,
Haneef

Test_ExcelExport.zip

[Administrator]

Hi Dan,

If you want to control appearance of the WorkSheet border then set the ActiveSheet.IsGridLinesVisible property to false. Here is a code snippet

Syncfusion.GridExcelConverter.GridExcelConverterControl gecc = new Syncfusion.GridExcelConverter.GridExcelConverterControl();
string path = "";
path = "C:\\test5.xls";

IWorkbook myWorkBook = ExcelUtils.CreateWorkbook(1);
IWorksheet sheet = myWorkBook.Worksheets[0];

gecc.GridToExcel(gridSearchResults.Model, sheet, Syncfusion.GridExcelConverter.ConverterOptions.RowHeaders
| Syncfusion.GridExcelConverter.ConverterOptions.RowHeaders);

myWorkBook.ActiveSheet.IsGridLinesVisible = false;

myWorkBook.SaveAs(path);
myWorkBook.Close();

ExcelUtils.ThrowNotSavedOnDestroy = false;

Best regards,
Haneef

[Dan Garvin]

Yes, isGridLinesVisible = false turns off gridlines for the entire worksheet. But, that is not the problem. Notice in the example application that I've attached above that the cells in which the data resides do not show the grid lines, but blank cells do. I want the grid lines to show for all data. This condition only seems to occur when attaching a datatable as the source, as in the example.

Upon further searching I found that if you look at the resulting exported spreadsheet, that you can restore the gridlines to the data area by selecting Format, Cells, Patterns, and then clicking on No color.

What appears to be happening is that the export is setting the pattern (border?) to white, but it should be set to No color. Any ideas? Thanks.

[Administrator]

Hi Dan,

The reason is that you are not setting the TableStyle.Borders in a IntializeComponent method. Please add the below line in your IntilalizeComponent method( line no:121). Let me know if this helps.

//>>>>>>
gridStyleInfo1.Borders.All = new Syncfusion.Windows.Forms.Grid.GridBorder(Syncfusion.Windows.Forms.Grid.GridBorderStyle.Standard);
//>>>>>>
gridStyleInfo1.ReadOnly = true;
this.gridSearchResults.TableStyle = gridStyleInfo1;


Best regards,
Haneef

[Dan Garvin]

Well, the suggestion you gave me above set borders around the cells, but this is not the desired behavior. If you look at the example, the cells exported have white borders. If you select the cells and then select Format Cells Borders and select No color, the cells then look like normal excel cells. So, how do we set the borders to no color programatically from the method?

myWorkBook.?
Thanks.
-dan

[haneefm]

Hi Dan,

Here is a code snippet that shows you "How to set a border with no color in a grid cell?".

this.gridControl1.TableStyle.Borders.All = new GridBorder(GridBorderStyle.Dotted,Color.Empty );
//For Empty Border.
this.gridControl1.TableStyle.Borders.All = GridBorder.Empty;

Best regards,
Haneef