Spread WPF 18
Features / Cells
In This Topic
    Cells
    In This Topic

    A cell is the basic unit of a worksheet, which is formed at the intersection of a row and a column. Spread for WPF lets you work with cells and perform different operations on them, as explained in the following sections.

    Set Active Cell

    Active cell refers to the currently selected or highlighted cell into which data is being entered when you start typing. Only one cell is active at a time. By default, A1 is the active cell and it is displayed with a thick border. You can also move the active cell by clicking on any other cell.

    To set your desired cell as an active cell, use the Activate() method of the IRange interface. This method is called to set particular cell as active cell.

    Refer to the following example code to set the B2 cell as the active cell.

    Copy Code
    // Set active cell.
    spreadSheet1.Workbook.ActiveSheet.Cells["B2"].Activate();
    spreadSheet1.Workbook.ActiveSheet.ActiveCell.Value = "Active cell";
    
    Copy Code
    ' Set active cell.
    spreadSheet1.Workbook.ActiveSheet.Cells("B2").Activate()
    spreadSheet1.Workbook.ActiveSheet.ActiveCell.Value = "Active cell"
    

    Merge Cells

    You can merge the cells in a worksheet to span multiple rows or columns. Merging cells allows you to combine multiple cells in a worksheet into a single large cell. This is useful when you want to organize or format your worksheet content.

    When you merge cells, the data of the first cell (the "anchor cell") in the range will expand to fill all the merged space. The data in the other cells in the range will be hidden, but not lost. Once you unmerge the cells, the hidden data will reappear as before. For example, if the cell range A1 to C3 has the same value, you can merge them and then the cell A1 will occupy the space from A1 to C3 as shown in the following image.

    To merge and unmerge the cells in a worksheet, use the Merge and UnMerge methods of the IRange interface.

    Refer to the following example code that merges the cell A1:C3 and creates a single merged cell and unmerge it later.

    Copy Code
    // Merge cells.
    spreadSheet1.Workbook.ActiveSheet.Cells["A1"].Interior.Color = GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.Thistle);
    spreadSheet1.Workbook.ActiveSheet.Cells["A1:C3"].Merge();
    spreadSheet1.Workbook.ActiveSheet.Cells["A1"].Text = "Merged Cell Range";
    // Unmerge cells.
    spreadSheet1.Workbook.ActiveSheet.Cells["A1:C3"].UnMerge();
    
    Copy Code
    ' Merge cells.
    spreadSheet1.Workbook.ActiveSheet.Cells("A1").Interior.Color = GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.Thistle)
    spreadSheet1.Workbook.ActiveSheet.Cells("A1:C3").Merge()
    spreadSheet1.Workbook.ActiveSheet.Cells("A1").Text = "Merged Cell Range"
    ' Unmerge cells.
    spreadSheet1.Workbook.ActiveSheet.Cells("A1:C3").UnMerge()
    

    Merge Priority Rules

    When merging cells, if the range overlaps with another merged range, the merge range shown in the control will be determined by the merge range priority. The priority of the merging range is decided based on the rules listed below.

    Auto-Merge Cells

    The Auto-Merge feature simplifies the process of merging duplicate content in adjacent cells. It automatically merges rows or columns with the same value. This helps you remove redundancies (repeated text) and reduce the complexity of your worksheet. It also saves you time and effort by eliminating the need to manually check for duplicate cells and merge them one by one.

    To enable automatic merging of cells in a worksheet, you can use the MergePolicy enumeration of the GrapeCity.Spreadsheet namespace. It helps to set the merge policies for the columns to control how cells can be merged. The MergePolicy has the following members:

    For example, set the value of cells A1:A8 as {c; c; d; d; d; d; e; e} and the value of cells B1:B8 as {7; 7; 7; 7; 7; 6; 6; 6}. If column B has a merge policy of "Always", the cells in column B will be merged into two blocks: B1:B5 and B6:B8. On the other hand, if column B has a merge policy of "Restricted", the cells in column B will be merged into four blocks: B1:B2, B3:B5, B6, B7:B8.

    Note:

    • The data in the merged cells are not lost, they are just hidden.
      You can edit the original content by double-clicking the cell. When you exit the edit mode, if the cell's content differs from the other cells it was merged with, the cell will no longer look as merged.
    • Cells of different cell types that contain same data can also be merged.
      For example, a cell with a string cell type contains content "45" and the adjacent cell with a number cell type contains the same content; then both the cells will automatically be merged.
    • Merged cells inherit the properties of the top-left cell in the merged range.
      For example, if the top-left merged cell has the red background color, all merged cells will have the same background color.

    Refer to the following example code that shows how to auto-merge cells with the same values in the second column in a worksheet in both XAML view and in the code view.

    Copy Code
    <gss:GcSpreadSheet Name="spreadSheet1" HorizontalAlignment="Left" Margin="10,24,0,0" VerticalAlignment="Top">
                <gss:GcSpreadSheet.Sheets>
                    <gss:SheetInfo Name="Sheet1" RowCount="10">
                        <gss:SheetInfo.Columns>
                            <gss:ColumnInfo />
                            <gss:ColumnInfo MergePolicy ="Always"/>
                            <gss:ColumnInfo/>
                            <gss:ColumnInfo />
                            <gss:ColumnInfo/>
                            <gss:ColumnInfo/>
                        </gss:SheetInfo.Columns>
                    </gss:SheetInfo>
                </gss:GcSpreadSheet.Sheets>
    </gss:GcSpreadSheet>
    
    Copy Code
    // Set MergePolicy to "Always".
    spreadSheet1.Workbook.ActiveSheet.Cells["B1"].Text = "AutoMerge";
    spreadSheet1.Workbook.ActiveSheet.Cells["B2"].Text = "AutoMerge";
    spreadSheet1.Workbook.ActiveSheet.Cells["B3"].Text = "AutoMerge";
    spreadSheet1.Workbook.ActiveSheet.Cells["B4"].Text = "AutoMerge";
    spreadSheet1.Workbook.ActiveSheet.Columns[1].MergePolicy = MergePolicy.Always;
    
    Copy Code
    ' Set the MergePolicy of the second column to "Always".
    spreadSheet1.Workbook.ActiveSheet.Cells("B1").Text = "AutoMerge"
    spreadSheet1.Workbook.ActiveSheet.Cells("B2").Text = "AutoMerge"
    spreadSheet1.Workbook.ActiveSheet.Cells("B3").Text = "AutoMerge"
    spreadSheet1.Workbook.ActiveSheet.Cells("B4").Text = "AutoMerge"
    spreadSheet1.Workbook.ActiveSheet.Columns(1).MergePolicy = MergePolicy.Always
    

    Unlock Cells

    Protecting a worksheet prevents its cells from being edited by default. To enable cell editing, unlock the cells by setting the Locked property of the IRange interface to false. Note that the data can be copied from locked cells.

    Refer to the following example code to unlock cells to edit.

    Copy Code
    // Protect worksheet.
    spreadSheet1.Workbook.ActiveSheet.Protect(GrapeCity.Spreadsheet.WorksheetLocks.All, "test");
    // Unlock cells.
    spreadSheet1.Workbook.ActiveSheet.Cells["C3:D4"].Text = "Unlocked";
    spreadSheet1.Workbook.ActiveSheet.Cells["C3:D4"].Locked = false;
    
    Copy Code
    ' Protect worksheet.
    spreadSheet1.Workbook.ActiveSheet.Protect(GrapeCity.Spreadsheet.WorksheetLocks.All, "test")
    ' Unlock cells.
    spreadSheet1.Workbook.ActiveSheet.Cells("C3:D4").Text = "Unlocked"
    spreadSheet1.Workbook.ActiveSheet.Cells("C3:D4").Locked = False
    

    Format Cell Text

    You can change font settings such as font, size, style, color, etc. of the cell text using properties like Name, Size, Italic, Color, and several methods of the IFont interface. Additionally, Spread for WPF allows you to change the horizontal and vertical alignment of the cell using the HorizontalAlignment and VerticalAlignment properties of the IRange interface.

    Refer to the following example code to format cell text.

    Copy Code
    // Cell font.
    spreadSheet1.Workbook.ActiveSheet.Cells[2, 2].Text = "Hello";
    spreadSheet1.Workbook.ActiveSheet.Cells[2, 2].Font.Name = "Algerian";
    spreadSheet1.Workbook.ActiveSheet.Cells[2, 2].Font.Size = 14;
    spreadSheet1.Workbook.ActiveSheet.Cells[2, 2].Font.Italic = true;
    spreadSheet1.Workbook.ActiveSheet.Cells[2, 2].Font.Color = GrapeCity.Spreadsheet.Color.FromThemeColor(GrapeCity.Core.ThemeColors.Accent3);
    // Cell alignment.
    spreadSheet1.Workbook.ActiveSheet.Cells[2, 2].HorizontalAlignment = GrapeCity.Spreadsheet.HorizontalAlignment.Center;
    spreadSheet1.Workbook.ActiveSheet.Cells[2, 2].VerticalAlignment = GrapeCity.Spreadsheet.VerticalAlignment.Center;
    
    Copy Code
    ' Cell font.
    spreadSheet1.Workbook.ActiveSheet.Cells(2, 2).Text = "Hello"
    spreadSheet1.Workbook.ActiveSheet.Cells(2, 2).Font.Name = "Algerian"
    spreadSheet1.Workbook.ActiveSheet.Cells(2, 2).Font.Size = 14
    spreadSheet1.Workbook.ActiveSheet.Cells(2, 2).Font.Italic = True
    spreadSheet1.Workbook.ActiveSheet.Cells[2, 2].Font.Color = GrapeCity.Spreadsheet.Color.FromThemeColor(GrapeCity.Core.ThemeColors.Accent3)
    ' Cell alignment.
    spreadSheet1.Workbook.ActiveSheet.Cells(2, 2).HorizontalAlignment = GrapeCity.Spreadsheet.HorizontalAlignment.Center
    spreadSheet1.Workbook.ActiveSheet.Cells(2, 2).VerticalAlignment = GrapeCity.Spreadsheet.VerticalAlignment.Center
    

    Change Cell Size

    You can modify the column width and row height of a worksheet to fit the data by changing the cell size. To do this, use the ColumnWidth and RowHeight properties of the IRange interface.

    Refer to the following example code that changes the cell size.

    Copy Code
    // Set cell size.
    spreadSheet1.Workbook.ActiveSheet.Cells[2, 2].ColumnWidth = 100;
    spreadSheet1.Workbook.ActiveSheet.Cells[2, 2].RowHeight = 60;
    
    Copy Code
    ' Set cell size.
    spreadSheet1.Workbook.ActiveSheet.Cells(2, 2).ColumnWidth = 100
    spreadSheet1.Workbook.ActiveSheet.Cells(2, 2).RowHeight = 60
    

    Set Cell Background Color

    Spread for WPF allows you to set the background colors of worksheet cells, which can help you highlight relevant data. To change the background color of a cell, use the Color property of the IInterior interface for the specified cell index.

    Additionally, the mouse hover color, the background color of a cell when hovered over, can also be set using the HoverCellBackground property of the GcSpreadSheet class.

    Copy Code
    // Set the cell background color.
    GrapeCity.Spreadsheet.IWorksheet worksheet = spreadSheet1.Workbook.Worksheets[0];
    worksheet.Cells[0, 0].Value = 123;
    worksheet.Cells[0, 0].Interior.Color = GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.Red);
    // Set the mouse hover color.
    spreadSheet1.HoverCellBackground = new SolidColorBrush(System.Windows.Media.Color.FromArgb(128, 0, 255, 255));
    
    Copy Code
    ' Set the cell background color.
    Dim worksheet As GrapeCity.Spreadsheet.IWorksheet = spreadSheet1.Workbook.Worksheets(0)
    worksheet.Cells(0, 0).Value = 123
    worksheet.Cells(0, 0).Interior.Color = GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.Red)
    ' Set the mouse hover color.
    spreadSheet1.HoverCellBackground = New SolidColorBrush(Windows.Media.Color.FromArgb(128, 0, 255, 255))
    

    You can also set the background color of active or selected cell by using the SelectionBackground or ActiveCellBackground property of GcSpreadSheet class. To do this, make sure that you have set the SelectionStyle property that indicates how the selection is painted. This property has the following values:

    Value  Description
    None  Does not change the style of selected cells.
    Renderer  Uses default color for selected cells (transparent grey for selection, and white for active cell).
    Color  Uses the selected background color for selected cells.
    Both  Uses both the selected background color and renderer for selected cells.

    The following example code shows how to set the background color of the active or selected cell in both XAML view and in the code view.

    Copy Code
    < gss:GcSpreadSheet  x:Name ="spreadSheet1"  SelectionStyle ="Both"  SelectionBackground ="LightPink"   ActiveCellBackground ="LightYellow" />
    
    Copy Code
    // Set SelectionStyle to "Both".
    spreadSheet1.SelectionStyle = SelectionStyle.Both;
    // Set the ActiveCellBackground.
    spreadSheet1.ActiveCellBackground = System.Windows.Media.Brushes.LightYellow;
    // Set SelectionBackground.
    spreadSheet1.SelectionBackground = System.Windows.Media.Brushes.LightPink;
    
    Copy Code
    ' Set SelectionStyle to "Both".
    spreadSheet1.SelectionStyle = SelectionStyle.Both
    ' Set the ActiveCellBackground.
    spreadSheet1.ActiveCellBackground = Windows.Media.Brushes.LightYellow
    ' Set SelectionBackground.
    spreadSheet1.SelectionBackground = Windows.Media.Brushes.LightPink
    

    Customize Cell Borders

    You can set worksheet cell borders using the ApplyBorder method of the IBorders interface. It method allows you to change the color and line style of cell borders. However, you can use the BordersIndex enumeration if you want to explicitly set the style for one side of a border.

    Refer to the following example code to change cell borders.

    Copy Code
    // Set cell borders.
    spreadSheet1.Workbook.ActiveSheet.Cells[4, 2].Borders.ApplyBorder(new GrapeCity.Spreadsheet.Border
    (new GrapeCity.Spreadsheet.BorderLine(GrapeCity.Spreadsheet.BorderLineStyle.Double,
    GrapeCity.Spreadsheet.Color.FromArgb(255, 0, 0))));
    spreadSheet1.Workbook.ActiveSheet.Cells[4, 2].Value = "Border";
    // Explicitly set the style for one side of the border.
    spreadSheet1.Workbook.ActiveSheet.Cells["A1:F10"].Borders[GrapeCity.Spreadsheet.BordersIndex.InsideHorizontal].LineStyle = GrapeCity.Spreadsheet.BorderLineStyle.Double;
    
    Copy Code
    ' Set cell borders.
    spreadSheet1.Workbook.ActiveSheet.Cells(4, 2).Borders.ApplyBorder(New GrapeCity.Spreadsheet.Border(New GrapeCity.Spreadsheet.BorderLine(GrapeCity.Spreadsheet.BorderLineStyle.[Double], GrapeCity.Spreadsheet.Color.FromArgb(255, 0, 0))))
    spreadSheet1.Workbook.ActiveSheet.Cells(4, 2).Value = "Border"
    ' Explicitly set the style for one side of the border.
    spreadSheet1.Workbook.ActiveSheet.Cells("A1:F10").Borders(GrapeCity.Spreadsheet.BordersIndex.InsideHorizontal).LineStyle = GrapeCity.Spreadsheet.BorderLineStyle.[Double]
    

    Apply Fill Effects to Cells

    Spread for WPF lets you apply fill effects to worksheet cells to enhance the visualization of data in a worksheet. These effects, such as solid colors, gradients, and patterns, help make your data easier to understand.

    To apply fill effects to the cells, you can set the Pattern property, which accepts values from the PatternType enumeration. This enumeration denotes the type of fill effect to be applied on a cell.

    You can use three different types of fill effects to paint a cell.

    Fill Effects Sample Image Description
    Solid Fill Solid fill effect uses a single color to paint the cells of a worksheet. This can be done by setting the value of the PatternType enumeration to Solid. You can specify the fill color by using the FromArgb method of Color structure.
    Gradient Fill Gradient fill effect uses two colors and a direction to paint the cells of a worksheet. This can be done by setting the value of the PatternType enumeration to LinearGradient. You can specify two different colors for gradient fill by using the ThemeColor property of the ColorStop class.
    Pattern Fill

    Pattern fill effect uses a set of predefined patterns to paint the cell. These patterns can be set by using the PatternType enumeration. This enumeration provides Automatic, Gray125, LightHorizontal, LightTrellis, LightUp, LightVertical, LightGrid, LightDown, LightGray, MediumGray, Gray0625, DarkVertical, DarkUp, DarkTrellis, DarkHorizontal, DarkGrid, DarkGray, and DarkDown values.

    You can set the fill color of the pattern by using the FromArgb method of Color structure.

    Refer to the following example code to apply fill effects to the specified cells.

    Copy Code
    // Solid Fill.
    spreadSheet1.Workbook.ActiveSheet.Cells[6, 0].Interior.Pattern = GrapeCity.Spreadsheet.PatternType.Solid;
    spreadSheet1.Workbook.ActiveSheet.Cells[6, 0].Interior.Color = GrapeCity.Spreadsheet.Color.FromArgb(255, 255, 255, 0);
    // Gradient Fill.
    spreadSheet1.Workbook.ActiveSheet.Cells[4, 0].Interior.Pattern = GrapeCity.Spreadsheet.PatternType.LinearGradient;
    spreadSheet1.Workbook.ActiveSheet.Cells[4, 0].Interior.Gradient.ColorStops.Add(0).ThemeColor = GrapeCity.Core.ThemeColors.Accent1;
    spreadSheet1.Workbook.ActiveSheet.Cells[4, 0].Interior.Gradient.ColorStops.Add(1).ThemeColor = GrapeCity.Core.ThemeColors.Accent5;
    // Pattern Fill.
    spreadSheet1.Workbook.ActiveSheet.Cells[2, 0].Interior.Pattern = GrapeCity.Spreadsheet.PatternType.Gray125;
    spreadSheet1.Workbook.ActiveSheet.Cells[2, 0].Interior.PatternColor = GrapeCity.Spreadsheet.Color.FromArgb(255, 0, 0);
    
    Copy Code
    ' Solid Fill.
    spreadSheet1.Workbook.ActiveSheet.Cells(6, 0).Interior.Pattern = GrapeCity.Spreadsheet.PatternType.Solid
    spreadSheet1.Workbook.ActiveSheet.Cells(6, 0).Interior.Color = GrapeCity.Spreadsheet.Color.FromArgb(255, 255, 255, 0)
    ' Gradient Fill.
    spreadSheet1.Workbook.ActiveSheet.Cells(4, 0).Interior.Pattern = GrapeCity.Spreadsheet.PatternType.LinearGradient
    spreadSheet1.Workbook.ActiveSheet.Cells(4, 0).Interior.Gradient.ColorStops.Add(0).ThemeColor = GrapeCity.Core.ThemeColors.Accent1
    spreadSheet1.Workbook.ActiveSheet.Cells(4, 0).Interior.Gradient.ColorStops.Add(1).ThemeColor = GrapeCity.Core.ThemeColors.Accent5
    ' Pattern Fill.
    spreadSheet1.Workbook.ActiveSheet.Cells(2, 0).Interior.Pattern = GrapeCity.Spreadsheet.PatternType.Gray125
    spreadSheet1.Workbook.ActiveSheet.Cells(2, 0).Interior.PatternColor = GrapeCity.Spreadsheet.Color.FromArgb(255, 0, 0)
    

    Apply Cell Styles

    In addition to the fill effects, Spread for WPF supports applying built-in styles and also creating custom named styles.

    Built-in styles can be applied to cells, rows, and columns and a worksheet using the ApplyStyle method of the IRange interface and by specifying a BuiltInStyle enumeration as an argument.

    On the other hand, A named style is a collection of different settings such as borders, colors, fonts, etc. that can be applied to cells, rows, and columns. This feature is useful when you want to apply the same style of formatting to multiple cells, rows, or columns at once. To create a named style (a unique name), use the Add method of the IStyles interface.

    The Styles collection of the GcSpreadSheet class stores both built-in and custom named styles that you can access later. You can apply the custom named style that you have created to cells, rows, and columns of the worksheet using the ApplyStyle method of the IRange interface. Additionally, you can use the properties of the IStyles interface to configure various settings of the custom named style in the worksheet. 

    Copy Code
    // Apply built-in style.
    spreadSheet1.Workbook.ActiveSheet.Cells["A1:H10"].ApplyStyle(GrapeCity.Spreadsheet.BuiltInStyle.LinkedCell);
    // Apply named style.
    GrapeCity.Spreadsheet.IStyle style1 = spreadSheet1.Workbook.Styles.Add("Style1");
    style1.Borders.LineStyle = GrapeCity.Spreadsheet.BorderLineStyle.Double;
    style1.Borders.Color = GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.Red);
    style1.Interior.ColorIndex = 3;
    // Apply the style to the cells.
    spreadSheet1.Workbook.ActiveSheet.Cells["C3"].ApplyStyle("Style1");
    // Apply the style to the column.
    spreadSheet1.Workbook.ActiveSheet.Columns[0].ApplyStyle("Style1");
    // Apply the style to the rows.
    spreadSheet1.Workbook.ActiveSheet.Rows[0].ApplyStyle("Style1");
    
    Copy Code
    ' Apply built-in style.
    spreadSheet1.Workbook.ActiveSheet.Cells("A1:H10").ApplyStyle(GrapeCity.Spreadsheet.BuiltInStyle.LinkedCell)
    ' Apply named style.
    Dim style1 As GrapeCity.Spreadsheet.IStyle = spreadSheet1.Workbook.Styles.Add("Style1")
    style1.Borders.LineStyle = GrapeCity.Spreadsheet.BorderLineStyle.Double
    style1.Borders.Color = GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.Red)
    style1.Interior.ColorIndex = 3
    ' Apply the style to the cells.
    spreadSheet1.Workbook.ActiveSheet.Cells("C3").ApplyStyle("Style1")
    ' Apply the style to the column.
    spreadSheet1.Workbook.ActiveSheet.Columns(0).ApplyStyle("Style1")
    ' Apply the style to the rows.
    spreadSheet1.Workbook.ActiveSheet.Rows(0).ApplyStyle("Style1")