Column Interface

A single column in a worksheet.

Namespace: DevExpress.Spreadsheet

Assembly: DevExpress.Spreadsheet.v23.2.Core.dll

NuGet Package: DevExpress.Spreadsheet.Core

Declaration

C#

public interface Column :
    CellRange,
    Formatting,
    IEnumerable<Cell>,
    IEnumerable

VB.NET

Public Interface Column
    Inherits CellRange,
             Formatting,
             IEnumerable(Of Cell),
             IEnumerable

Related API Members

The following members return Column objects:

• ColumnCollection.Item[Int32]
• ColumnCollection.Item[String]

Remarks

A worksheet contains 1,048,576 rows and 16,384 columns. Use the Worksheet.Columns property to access the collection of worksheet columns.

Access Columns

Use the ColumnCollection.Item property to access an individual column in a worksheet.

Obtain the Column at the Specified Index

C#

using DevExpress.Spreadsheet;
// ...

// Access the column collection.
ColumnCollection columns = workbook.Worksheets[0].Columns;

// Access the first column in the collection.
Column firstColumn = columns[0];

VB.NET

Imports DevExpress.Spreadsheet
' ...

' Access the column collection.
Dim columns As ColumnCollection = workbook.Worksheets(0).Columns

' Access the first column in the collection.
Dim firstColumn As Column = columns(0)

A column index is zero-based. It specifies the column position in the collection.

Obtain the Column with a Given Heading

C#

using DevExpress.Spreadsheet;
// ...

// Access the column collection.
ColumnCollection columns = workbook.Worksheets[0].Columns;

// Access the first column by its heading.
Column firstColumn = columns["A"];

VB.NET

Imports DevExpress.Spreadsheet
' ...

' Access the column collection.
Dim columns As ColumnCollection = workbook.Worksheets(0).Columns

'  Access the first column by its heading.
Dim firstColumn As Column = columns("A")

Column headings (“A”, “B”, “C”, etc.) identify each column in a worksheet. Specify column headings in the A1 reference style.

Insert Columns

Use the following methods to insert columns into a worksheet:

Column.Insert

Inserts a column to the left of the current column.

ColumnCollection.Insert

Inserts columns at the specified position.

Note

The number of columns in a worksheet does not change — 16,384. When you add new columns, other columns in the worksheet are shifted to the right, and an equivalent number of columns at the end of the worksheet is removed.

Insert a Single Column

C#

// Insert column "B".
worksheet.Columns["B"].Insert();

// Insert column "D".
worksheet.Columns.Insert(3);

// Insert column "F".
worksheet.Columns.Insert("F");

VB.NET

' Insert column "B".
worksheet.Columns("B").Insert()

' Insert column "D".
worksheet.Columns.Insert(3)

' Insert column "F".
worksheet.Columns.Insert("F")

Insert Multiple Columns

C#

// Insert three columns (from column "B" to column "D").
worksheet.Columns.Insert(1, 3);

// Insert two columns starting with column "F".
worksheet.Columns.Insert("F", 2);

VB.NET

' Insert three columns (from column "B" to column "B").
worksheet.Columns.Insert(1, 3)

' Insert two columns starting with column "F".
worksheet.Columns.Insert("F", 2);

Insert Columns to the Left of a Specific Cell Range

Use the Worksheet.InsertCells method to insert empty columns to the left of the specified cells. The method inserts the same number of columns as the specified cell range.

C#

// Insert three columns to the left of the "B3:D5" cell range.
worksheet.InsertCells(worksheet.Range["B3:D5"], InsertCellsMode.EntireColumn);

VB.NET

' Insert three columns to the left of the "B3:D5" cell range.
worksheet.InsertCells(worksheet.Range("B3:D5"), InsertCellsMode.EntireColumn) 

Format Inserted Columns

Pass a ColumnFormatMode enumeration member to the ColumnCollection.Insert method to specify format options for inserted columns.

The following example inserts two columns (“B” and “C”) and applies the same format as column “A” to these columns:

C#

// Insert two columns with the same formatting as the column "A".
worksheet.Columns.Insert("B", 2, ColumnFormatMode.FormatAsPrevious);

VB.NET

' Insert two columns with the same formatting as the column "A".
worksheet.Columns.Insert("B", 2, ColumnFormatMode.FormatAsPrevious)

Remove Columns

Use the following methods to delete columns from a worksheet:

Column.Delete

Deletes the current column.

ColumnCollection.Remove

Deletes columns at the specified position or deletes multiple columns at once.

Note

The number of columns in a worksheet does not change — 16,384. When you delete columns, other columns in the worksheet are shifted to the left and the equivalent number of new columns is automatically added to the end of the worksheet’s column collection.

Remove a Single Column

C#

// Delete column "C".
worksheet.Columns.Remove("C");

// Delete the third column.
worksheet.Columns[2].Delete();

// Delete the fifth column.
worksheet.Columns.Remove(4);

VB.NET

' Delete column "C".
worksheet.Columns.Remove("C")

' Delete the third column.
worksheet.Columns(2).Delete()

' Delete the fifth column.
worksheet.Columns.Remove(4)

Remove Multiple Columns

C#

// Delete three columns (from column "C" to column "E").
worksheet.Columns.Remove("C", "E");

VB.NET

' Delete three columns (from column "C" to column "E").
worksheet.Columns.Remove("C", "E")

Remove Columns That Meet a Specific Condition

C#

// Specify the condition to remove worksheet columns.
// If text in the first row is "Change in Price", remove the corresponding column. 
Func<int, bool> columnRemovalCondition = x => worksheet.Cells[0, x].Value.TextValue == "Change in Price";

// Delete columns that meet the specified condition.
// Check columns "A" through "H".
worksheet.Columns.Remove(0, 7, columnRemovalCondition);

VB.NET

' Specify the condition to remove worksheet columns.
' If text in the first row is "Change in Price", remove the corresponding column. 
Dim columnRemovalCondition As Func(Of Integer, Boolean) = Function(x) worksheet.Cells(0, x).Value.TextValue = "Change in Price"

' Delete columns that meet the specified condition.
' Check columns "A" through "H".
worksheet.Columns.Remove(0, 7, columnRemovalCondition)

Remove Columns That Contain Specific Cells

Use the Worksheet.DeleteCells method to delete columns that contain the specified cell range.

C#

// Delete a column that contains the "F3" cell.
worksheet.DeleteCells(worksheet.Cells["F3"], DeleteMode.EntireColumn);

VB.NET

' Delete a column that contains the "F3" cell.
worksheet.DeleteCells(worksheet.Cells("F3"), DeleteMode.EntireColumn)

Group and Ungroup Columns

Use the ColumnCollection.Group method to group columns.

C#

// Group columns "C" through "F" and expand the group.
worksheet.Columns.Group(2, 5, false);

// Group columns "C" and "D" and collapse the group.
worksheet.Columns.Group("C", "D", true);

VB.NET

' Group columns "C" through "F" and expand the group.
worksheet.Columns.Group(2, 5, False)

' Group columns "C" and "D" and collapse the group.
worksheet.Columns.Group("C", "D", True)

Use the ColumnCollection.UnGroup method to ungroup columns.

C#

// Ungroup columns "C" through "F".
worksheet.Columns.UnGroup(2, 5, false);

// Ungroup columns "C" and "D" and display collapsed data.
worksheet.Columns.UnGroup("C", "D", true);

VB.NET

' Ungroup columns "C" through "F".
worksheet.Columns.UnGroup(2, 5, False)

' Ungroup columns "C" and "D" and display collapsed data.
worksheet.Columns.UnGroup("C", "D", True)

Expand or Collapse Groups

Use the following methods to collapse or expand column groups in a worksheet:

ColumnCollection.CollapseAllGroups

Collapses all column groups.

ColumnCollection.CollapseGroups

Collapses column groups starting with the specified group level.

ColumnCollection.ExpandAllGroups

Expands all column groups.

C#

// Collapse all column groups except the first level group.
worksheet.Columns.CollapseGroups(2);

VB.NET

' Collapse all column groups except the first level group.
worksheet.Columns.CollapseGroups(2)

Hide Columns

Use the Column.Visible property or the ColumnCollection.Hide method to hide columns in a worksheet.

C#

// Hide column "A".
worksheet.Columns[0].Visible = false;

// Hide columns "C" through "D".
worksheet.Columns.Hide(2, 3);

// Hide columns "F" through "H".
worksheet.Columns.Hide("F", "H");

VB.NET

' Hide column "A".
worksheet.Columns(0).Visible = False

' Hide columns "C" through "D".
worksheet.Columns.Hide(2, 3)

' Hide columns "F" through "H".
worksheet.Columns.Hide("F", "H")

Set the Column.Visible property to true to display a column. Use the ColumnCollection.Unhide method to show multiple hidden columns.

C#

// Show column "A".
worksheet.Columns[0].Visible = true;

// Show columns "C" through "D".
worksheet.Columns.Unhide(2, 3);

// Show columns "F" through "H".
worksheet.Columns.Unhide("F", "H");

VB.NET

' Show column "A".
worksheet.Columns(0).Visible = True

' Show columns "C" through "D".
worksheet.Columns.Unhide(2, 3)

' Show columns "F" through "H".
worksheet.Columns.Unhide("F", "H")

Specify Column Width

AutoFit Column Width

Use the Column.AutoFit or ColumnCollection.AutoFit method to automatically change the column width to fit the contents.

C#

// Adjust the width of column "B".
worksheet.Columns[1].AutoFit();

// Adjust the width of columns "C" through "E".
worksheet.Columns.AutoFit(2, 4);

VB.NET

' Adjust the width of column "B".
worksheet.Columns(1).AutoFit()

' Adjust the width of columns "C" through "E".
worksheet.Columns.AutoFit(2, 4)

Set the Width Value

Use the following properties to set the width for worksheet columns:

Column.Width

Specifies the column width in measurement units defined by the Workbook.Unit property.

Column.WidthInCharacters

Specifies the column width in characters of the default font specified by the built-in Normal style.

Column.WidthInPixels

Specifies the column width in pixels.

C#

// Set the width of column "B" to 170 points.
workbook.Unit = DevExpress.Office.DocumentUnit.Point;
worksheet.Columns["B"].Width = 170;

// Set the width of column "C" to the width of column "B".
worksheet.Columns["C"].Width = worksheet.Columns["B"].Width;

// Set the width of column "E" to 15 characters
// of the default font specified by the built-in Normal style.
worksheet.Columns["E"].WidthInCharacters = 15;

// Set the width of column "F" to 150 pixels.
worksheet.Columns["F"].WidthInPixels = 150;

VB.NET

' Set the width of column "B" to 170 points.
workbook.Unit = DevExpress.Office.DocumentUnit.Point
worksheet.Columns("B").Width = 170

' Set the width of column "C" to the width of column "B".
worksheet.Columns("C").Width = worksheet.Columns("B").Width

' Set the width of column "E" to 15 characters 
' of the default font specified by the built-in Normal style.
worksheet.Columns("E").WidthInCharacters = 15

' Set the width of column "F" to 150 pixels.
worksheet.Columns("F").WidthInPixels = 150

Note

You can set the column width to 0 to hide the column. Alternatively, use the Column.Visible property to hide a column or display the hidden column again.

Set the Default Column Width

Use the following properties to set the default width for worksheet columns:

Worksheet.DefaultColumnWidth

Specifies the default column width in measurement units used in the workbook.

Worksheet.DefaultColumnWidthInCharacters

Specifies the default column width in characters of the default font specified by the built-in Normal style.

Worksheet.DefaultColumnWidthInPixels

Specifies the default column width in pixels.

Note

These properties do not affect columns with an explicitly specified width.

C#

// Set the default column width to 100 points.
workbook.Unit = DevExpress.Office.DocumentUnit.Point;
worksheet.DefaultColumnWidth = 100;

// Set the default column width to 30 characters 
// of the default font specified by the built-in Normal style.
worksheet.DefaultColumnWidthInCharacters = 30;

// Set the default column width to 50 pixels.
worksheet.DefaultColumnWidthInPixels = 50;

VB.NET

' Set the default column width to 100 points.
workbook.Unit = DevExpress.Office.DocumentUnit.Point
worksheet.DefaultColumnWidth = 100

' Set the default column width to 30 characters 
' of the default font specified by the built-in Normal style.
worksheet.DefaultColumnWidthInCharacters = 30

' Set the default column width to 50 pixels.
worksheet.DefaultColumnWidthInPixels = 50

Set Column Width for a Cell Range

Use the following properties to set the width of columns that contain the specified cell range:

CellRange.ColumnWidth

Specifies the width of columns in measurement units used in the workbook.

CellRange.ColumnWidthInCharacters

Specifies the width of columns in characters of the default font specified by the built-in Normal style.

C#

// Set the width of the column that contains the "B2" cell to 2.5 inches.
workbook.Unit = DevExpress.Office.DocumentUnit.Inch;
worksheet.Cells["B2"].ColumnWidth = 2.5;

// Set the width of all columns that contain the "D2:F7" cell range 
// (the "D", "E", and "F" columns) to 15 characters of the default font.
worksheet.Range["D2:F7"].ColumnWidthInCharacters = 15;

VB.NET

' Set the width of the column that contains the "B2" cell to 2.5 inches.
workbook.Unit = DevExpress.Office.DocumentUnit.Inch
worksheet.Cells("B2").ColumnWidth = 2.5

' Set the width of all columns that contain the "D2:F7" cell range 
' (the "D", "E", and "F" columns) to 15 characters of the default font.
worksheet.Range("D2:F7").ColumnWidthInCharacters = 15

Set the Width of All Columns

Use the Row.ColumnWidth property to specify the width of all columns in a worksheet.

C#

// Set the width of all columns to 90 points.
workbook.Unit = DevExpress.Office.DocumentUnit.Point;
worksheet.Rows[0].ColumnWidth = 90;

VB.NET

' Set the width of all columns to 90 points.
workbook.Unit = DevExpress.Office.DocumentUnit.Point
worksheet.Rows(0).ColumnWidth = 90

Copy Columns

Use the Column.CopyFrom method to copy a column in a worksheet.

C#

// Copy data from column "A" to column "B".
worksheet.Columns[1].CopyFrom(worksheet.Columns[0]);

VB.NET

' Copy data from column "A" to column "B".
worksheet.Columns(1).CopyFrom(worksheet.Columns(0))

Pass a PasteSpecial enumeration member to the Column.CopyFrom method to specify which part of the copied data should be pasted into destination cells.

C#

// Copy number formats from column "D" to column "E".
worksheet.Columns["E"].CopyFrom(worksheet.Columns["D"], PasteSpecial.NumberFormats);

VB.NET

' Copy number formats from column "D" to column "E".
worksheet.Columns["E"].CopyFrom(worksheet.Columns["D"], PasteSpecial.NumberFormats)

Move Columns

Use the Column.MoveTo method to move a column to a different position in a worksheet.

Note

The Column.MoveTo method replaces existing destination cell content.

C#

// Move column "A" one column to the right.
worksheet.Columns["A"].MoveTo(worksheet.Columns["B"]);

VB.NET

' Move column "A" one column to the right.
worksheet.Columns("A").MoveTo(worksheet.Columns("B"))

Extension Methods

Show 35 items

ExportToImage()

ExportToImage(RangeImageOptions)

ExportToImage(String, ImageFileFormat)

ExportToImage(String, ImageFileFormat, RangeImageOptions)

ExportToImage(Stream, ImageFileFormat)

ExportToImage(Stream, ImageFileFormat, RangeImageOptions)

Clear()

ClearContents()

ClearFormats()

ClearHyperlinks()

ClearComments()

GroupRows(Boolean)

GroupColumns(Boolean)

UnGroupColumns(Boolean)

UnGroupRows(Boolean)

ClearOutline(Boolean, Boolean)

Subtotal(Int32, List<Int32>, Int32, String)

RemoveSubtotal()

Merge()

Merge(MergeCellsMode)

UnMerge()

Insert()

Delete()

Insert(InsertCellsMode)

Delete(DeleteMode)

Select()

Select(Boolean)

AutoFitColumns()

AutoFitRows()

Sum(Boolean, Boolean)

Count(Boolean, Boolean)

NumericalCount(Boolean, Boolean)

Average(Boolean, Boolean)

Max(Boolean, Boolean)

Min(Boolean, Boolean)