C1Excel provides functionality to apply styles and merge cells, rows, and columns. This blog shows the implementation of merging and styling in C1Excel.

Excel Styles

The appearance of each cell is defined by one or more XLStyle objects. When displaying a cell, Excel combines the row, column, and cell styles and merges the style elements defined in each one in order to determine how the cell should be displayed. The precedence of the styles is: (1) cell, (2) row, (3) column, (4) default style. For example, if a cell style defines the font and background color, those will be applied regardless of the settings in the row and column styles. If the row style defines an alignment, that will be applied regardless of the column style, and so on. The cell style may be null, in which case the cell is displayed using the other styles available or the default book style if no others are available. For instance, to apply BackColor to Row and Column in C1Excel, we first create a style _Colstyle , _FullColstyle and then set its BackColor and Rotation property. After this we set the style property of Row and Column to apply the corresponding style to the row /column

//Defining style Objects  
XLStyle \_FullColstyle, \_FullRowstyle;  

_FullColstyle = new C1.C1Excel.XLStyle(c1XLBook1);  
_FullColstyle.BackColor = Color.LightSlateGray;  
_FullColstyle.Rotation = 255;  

_FullRowstyle = new XLStyle(c1XLBook1);  
_FullRowstyle.BackColor = Color.Green;  

//Set Row and column Styles  
c1XLBook1.Sheets[0].Rows[2].Style = _FullRowstyle;  
c1XLBook1.Sheets[0].Columns[6].Style = _FullColstyle;

Merging

In order to merge cells we need to use MergeCells property and provide the range of cells that need to be merged together. MergeCells can be used for inspecting, adding, or clearing merged ranges in a sheet. Each merged range is represented by C1.C1Excel.XLCellRange object. For instance, in the code given below, we have created two XLCellRanges (_ColRange,_RowRange), and added these range to the MergeCells cell collection. It also applies styles to the merged rows :

//Select range to Merge  
XLCellRange _ColRange = new C1.C1Excel.XLCellRange(4, 6, 0, 8);  
XLCellRange _RowRange = new C1.C1Excel.XLCellRange(9, 21, 3, 4);  

// Set Text for Merged Cells  
c1XLBook1.Sheets[0][4, 0].Value = "Merged Cells";  
c1XLBook1.Sheets[0][9, 3].Value = "Merged Cells";  

//Merge Cells  
c1XLBook1.Sheets[0].MergedCells.Add(_ColRange);  
c1XLBook1.Sheets[0].MergedCells.Add(_RowRange);  

//Define and Apply Styles for Merged Cellss  
XLStyle _Colstyle = new C1.C1Excel.XLStyle(c1XLBook1);  
_Colstyle.BackColor = Color.Yellow;  
XLStyle _Rowstyle = new XLStyle(c1XLBook1);  
_Rowstyle.BackColor = Color.AliceBlue;  

c1XLBook1.Sheets[0][4, 0].Style = _Colstyle;  
c1XLBook1.Sheets[0][9, 3].Style = _Rowstyle;

Download Sample