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:
- Move your mouse pointer over the border between the column headers.
The pointer will change to a double arrow.
- Click and drag the border to resize the column.
A preview line will appear when dragging.
- 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
|