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

    Columns are vertical groups of cells in a spreadsheet. They are listed from top to bottom in the worksheet and labelled with alphabetical characters, such as A, B, C, etc. Note that a specific column can be accessed by its index or its name.

    The following sections explain various operations on columns that you can perform in the Spread for WPF.

    Insert Column

    You can insert one or more columns at any position in a worksheet using the InsertColumns method the IWorksheet interface.

    The following example code inserts three columns starting from the second position in the worksheet:

    Copy Code 
    // Insert three columns into the second position.
spreadSheet1.Workbook.ActiveSheet.InsertColumns(1, 3);
    Copy Code 
    ' Insert three columns into the second position.
spreadSheet1.Workbook.ActiveSheet.InsertColumns(1, 3)

    Delete Column

    You can remove one or more columns from a worksheet using the RemoveColumns method of the IWorksheet interface.

    The following example code removes three columns starting with the column at the third position.

    Copy Code 
    // Remove columns.
spreadSheet1.Workbook.ActiveSheet.RemoveColumns(3, 3);
    Copy Code 
    ' Remove columns.
spreadSheet1.Workbook.ActiveSheet.RemoveColumns(3, 3)

    Clear Column

    You can clear the unwanted contents of a single column or multiple columns from the control. To accomplish this, use the IWorksheet's Columns property to reference a collection of columns, and then apply the Clear method to clear the contents of a specific column or to clear the contents of all columns.

    The following example code clears the content of the columns.

    Copy Code 
    // Clear the content of all columns.
spreadSheet1.Workbook.ActiveSheet.Columns.Clear();
// Clear the content of column B.
spreadSheet1.Workbook.ActiveSheet.Columns[1].Clear();
    Copy Code 
    ' Clear the content of all columns.
spreadSheet1.Workbook.ActiveSheet.Columns.Clear()
' Clear the content of column B.
spreadSheet1.Workbook.ActiveSheet.Columns(1).Clear()

    Move Column

    You can move columns by dragging when the value of the DragDropMode enumeration is set to Column. By default, the value is none.

    To move a column, click on the header of the column you want to move and then drag and drop the header to the desired position as shown in the gif below.

    The following example code moves a column as shown in the above image.

    Copy Code 
    // Set DragDropMode enum to Column.
spreadSheet1.DragDropMode = DragDropMode.Column;
    Copy Code 
    ' Set DragDropMode enum to Column.
spreadSheet1.DragDropMode = DragDropMode.Column

    Set Column Count

    You can set the number of columns to be displayed in a worksheet using the ColumnCount property of the IWorksheet interface.

     

    Note: If the control is bound to a data source with auto-generation of columns, the number of columns is automatically determined by the number of fields in the data source. In this case, the ColumnCount property has no effect.

    The following example code sets the number of columns to three in a worksheet as shown in the above image.

    Copy Code 
    // Set column count.
spreadSheet1.Workbook.ActiveSheet.ColumnCount = 3;
    Copy Code 
    ' Set column count.
spreadSheet1.Workbook.ActiveSheet.ColumnCount = 3

    Set Column Width

    All columns in a worksheet have the same size by default. To set the width of a specific column in a worksheet, use the ColumnWidth property of the IRange interface. The column width is defined in pixels.

    The following example code sets the width of the column B to 190 pixels.

    Copy Code 
    // Set column width.
spreadSheet1.Workbook.ActiveSheet.Columns[1].ColumnWidth = 190;
    Copy Code 
    ' Set column width.
spreadSheet1.Workbook.ActiveSheet.Columns(1).ColumnWidth = 190

    Resize Column Width

    You can adjust the size of a column in the worksheet by using the Resizable property of the IRange interface. This property allows users to change column width by clicking and dragging columns' border based on the content size.

    To resize a column width:

    1. Move your mouse pointer over the border between the column headers.
      The pointer will change to a double arrow.
    2. Click and drag the border to resize the column.
      A preview line will appear when dragging.
    3. Release the mouse to apply the new size for the column.

    By default, resizing column widths is enabled. However, you can prevent resizing by setting the Resizable property to false. This property can be set in both XAML view and in the code view.

    The following example code disables resizing columns.

    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 Resizable ="False"/>
                        <gss:ColumnInfo/>
                        <gss:ColumnInfo/>
                    </gss:SheetInfo.Columns>
                </gss:SheetInfo>
       </gss:GcSpreadSheet.Sheets>
</gss:GcSpreadSheet>
    Copy Code 
    // Disable resizing for a single column.
spreadSheet1.Workbook.ActiveSheet.Columns[1].Resizable = false;
// Disable resizing for all columns.
spreadSheet1.Workbook.ActiveSheet.Columns.Resizable = false;
    Copy Code 
    ' Disable resizing for a single column.
spreadSheet1.Workbook.ActiveSheet.Columns(1).Resizable = False
' Disable resizing for all columns.
spreadSheet1.Workbook.ActiveSheet.Columns.Resizable = False

    Auto-Fit Column Width

    By default, automatic column width adjustment is enabled. When you move the mouse over the border of a column header, the mouse pointer changes to a resizing shape. Double-clicking on the column edge resizes the column to fit the large content of that column to preferred width. To autofit multiple columns, select them, and double click any boundary between two column headers in the selection.

    Note: The AutoFit feature can not be applied to merged cells.

     

    Additionally, you can also specify the automatic fit behavior using the AutoFit method for the columns.

    The following example code autofits data in the columns.

    Copy Code 
    // Autofit column B.
spreadSheet1.Workbook.ActiveSheet.Cells["B2"].Text = "This is a very long text";
spreadSheet1.Workbook.ActiveSheet.Columns[1].AutoFit();
// Autofit all columns.
spreadSheet1.Workbook.ActiveSheet.Columns.AutoFit();
    Copy Code 
    ' Autofit column B.
spreadSheet1.Workbook.ActiveSheet.Cells("B2").Text = "This is a very long text"
spreadSheet1.Workbook.ActiveSheet.Columns(1).AutoFit()
' Autofit all columns.
spreadSheet1.Workbook.ActiveSheet.Columns.AutoFit()

    Auto-Merge Columns

    You can automatically merge adjacent cells in the same column when their values are equal. To enable this feature, set the MergePolicy enumeration for a specific column, which has the following values.

    Value Description
    None Do not merge automatically.
    Always Merge adjacent cells whenever their values are equal.
    Restricted  Adjacent cells are merged if their values are equal and the corresponding ranges in the previous column are merged.

    The following code sample sets auto-merging for columns 1 and 2 as shown in the above image.

    Copy Code 
    // Auto-merge columns.
spreadSheet1.Workbook.ActiveSheet.Columns[1].MergePolicy = MergePolicy.Always;
spreadSheet1.Workbook.ActiveSheet.Columns[2].MergePolicy = MergePolicy.Restricted;
    Copy Code 
    ' Auto-merge columns.
spreadSheet1.Workbook.ActiveSheet.Columns(1).MergePolicy = MergePolicy.Always
spreadSheet1.Workbook.ActiveSheet.Columns(2).MergePolicy = MergePolicy.Restricted

    Hide/Show Columns

    You can hide or show columns in a worksheet by using the Hidden property of the IRange interface. If the property value is set to true, it hides the column. To show the column again, change the property value to false.

    The following example code hides the second column in the worksheet as shown in the above image.

    Copy Code 
    // Hide the second column.
spreadSheet1.Workbook.ActiveSheet.Columns[1].Hidden = true;
    Copy Code 
    ' Hide the second column.
spreadSheet1.Workbook.ActiveSheet.Columns(1).Hidden = True

    Apply Alternate Column Color

    You can easily apply alternate colors to the columns of your worksheet by using the AlternatingColumns property of the IWorksheetStyles interface. Setting the alternate column colors can improve the readability of your worksheet.

    To use this functionality, ensure that the Enabled property of the IAlternatingStyles interface is set to true, which allows the alternate column colors to be applied. You can choose a color from the color list by setting the ThemeColor property of the IInterior interface. The ThemeColor property accepts values from the ThemeColors enumeration.

     

    The following example code sets different colors to the alternate columns as shown in the above image.

    Copy Code 
    // Color the alternate columns. 
spreadSheet1.Workbook.ActiveSheet.Styles.AlternatingColumns.Enabled = true;
spreadSheet1.Workbook.ActiveSheet.Styles.AlternatingColumns.Count = 2;
spreadSheet1.Workbook.ActiveSheet.Styles.AlternatingColumns[0].Interior.ThemeColor = GrapeCity.Core.ThemeColors.Accent1;
spreadSheet1.Workbook.ActiveSheet.Styles.AlternatingColumns[1].Interior.ThemeColor = GrapeCity.Core.ThemeColors.Accent2;
    Copy Code 
    ' Color the alternate columns. 
spreadSheet1.Workbook.ActiveSheet.Styles.AlternatingColumns.Enabled = True
spreadSheet1.Workbook.ActiveSheet.Styles.AlternatingColumns.Count = 2
spreadSheet1.Workbook.ActiveSheet.Styles.AlternatingColumns(0).Interior.ThemeColor = GrapeCity.Core.ThemeColors.Accent1
spreadSheet1.Workbook.ActiveSheet.Styles.AlternatingColumns(1).Interior.ThemeColor = GrapeCity.Core.ThemeColors.Accent2