Column Class
Column Width and Formatting.When the object is serialized out as xml, its qualified name is x:col.
Inheritance Hierarchy
System.Object
DocumentFormat.OpenXml.OpenXmlElement
DocumentFormat.OpenXml.OpenXmlLeafElement
DocumentFormat.OpenXml.Spreadsheet.Column
Namespace: DocumentFormat.OpenXml.Spreadsheet
Assembly: DocumentFormat.OpenXml (in DocumentFormat.OpenXml.dll)
Syntax
'Declaration
Public Class Column _
Inherits OpenXmlLeafElement
'Usage
Dim instance As Column
public class Column : OpenXmlLeafElement
Remarks
[ISO/IEC 29500-1 1st Edition]
18.3.1.13 col (Column Width & Formatting)
Defines column width and column formatting for one or more columns of the worksheet.
[Example:This example shows that column 5 (E) has width and style information applied.
<col min="5" max="5" width="9.140625" style="3"/>
end example]
Parent Elements |
|---|
cols (§18.3.1.17) |
Attributes |
Description |
|---|---|
bestFit (Best Fit Column Width) |
Flag indicating if the specified column(s) is set to 'best fit'. 'Best fit' is set to true under these conditions:
The possible values for this attribute are defined by the W3C XML Schema boolean datatype. |
collapsed (Collapsed) |
Flag indicating if the outlining of the affected column(s) is in the collapsed state. See description of row collapsed and outlinePr element's summaryBelow and summaryRight attributes for detailed information. The possible values for this attribute are defined by the W3C XML Schema boolean datatype. |
customWidth (Custom Width) |
Flag indicating that the column width for the affected column(s) is different from the default or has been manually set. The possible values for this attribute are defined by the W3C XML Schema boolean datatype. |
hidden (Hidden Columns) |
Flag indicating if the affected column(s) are hidden on this worksheet. The possible values for this attribute are defined by the W3C XML Schema boolean datatype. |
max (Maximum Column) |
Last column affected by this 'column info' record. The possible values for this attribute are defined by the W3C XML Schema unsignedInt datatype. |
min (Minimum Column) |
First column affected by this 'column info' record. The possible values for this attribute are defined by the W3C XML Schema unsignedInt datatype. |
outlineLevel (Outline Level) |
Outline level of affected column(s). Range is 0 to 7. See description of outlinePr element's summaryBelow and summaryRight attributes for detailed information. The possible values for this attribute are defined by the W3C XML Schema unsignedByte datatype. |
phonetic (Show Phonetic Information) |
Flag indicating if the phonetic information should be displayed by default for the affected column(s) of the worksheet. The possible values for this attribute are defined by the W3C XML Schema boolean datatype. |
style (Style) |
Default style for the affected column(s). Affects cells not yet allocated in the column(s). In other words, this style applies to new columns. The possible values for this attribute are defined by the W3C XML Schema unsignedInt datatype. |
width (Column Width) |
Column width measured as the number of characters of the maximum digit width of the numbers 0, 1, 2, …, 9 as rendered in the normal style's font. There are 4 pixels of margin padding (two on each side), plus 1 pixel padding for the gridlines. width = Truncate([{Number of Characters} * {Maximum Digit Width} + {5 pixel padding}]/{Maximum Digit Width}*256)/256 [Example: Using the Calibri font as an example, the maximum digit width of 11 point font size is 7 pixels (at 96 dpi). In fact, each digit is the same width for this font. Therefore, if the cell width is 8 characters wide, the value of this attribute must be Truncate([8*7+5]/7*256)/256 = 8.7109375. end example] To translate the value of width in the file into the column width value at runtime (expressed in terms of pixels), use this calculation: =Truncate(((256 * {width} + Truncate(128/{Maximum Digit Width}))/256)*{Maximum Digit Width}) [Example: Using the same example as above, the calculation would be Truncate(((256*8.7109375+Truncate(128/7))/256)*7) = 61 pixels. end example] To translate from pixels to character width, use this calculation: =Truncate(({pixels}-5)/{Maximum Digit Width} * 100+0.5)/100 [Example: Using the example above, the calculation would be Truncate((61-5)/7*100+0.5)/100 = 8 characters. end example] [Note: when wide borders are applied, part of the left/right border must overlap with the 2 pixel padding on each side. Wide borders do not affect the width calculation of the column. end note] [Note: When the sheet is in the mode to view formulas instead of values, the pixel width of the column is doubled. end note] The possible values for this attribute are defined by the W3C XML Schema double datatype. |
[Note: The W3C XML Schema definition of this element’s content model (CT_Col) is located in §A.2. end note]
© ISO/IEC29500: 2008.
Examples
The following example opens a spreadsheet document for read-only access. By using the worksheet name and the cell name, it gets the column name and displays it.
using System;
using System.Collections.Generic;
using System.Linq;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System.Text.RegularExpressions;
namespace ColumnEx
{
class Program
{
// Given a document name, a worksheet name, and a cell name, gets the column of the cell.
static void Main(string[] args)
{
string docName = @"C:\Users\Public\Documents\ColumnEx.xlsx";
string worksheetName = "Sheet1";
string cellName = "B2";
// Open the document as read-only.
using (SpreadsheetDocument document = SpreadsheetDocument.Open(docName, false))
{
IEnumerable<Sheet> sheets =
document.WorkbookPart.Workbook.Descendants<Sheet>()
.Where(s => s.Name == worksheetName);
WorksheetPart worksheetPart = (WorksheetPart)document
.WorkbookPart.GetPartById(sheets.First().Id);
// Get the column name for the specified cell.
// Create a regular expression to match the column name portion of the cell name.
Regex regex = new Regex("[A-Za-z]+");
Match match = regex.Match(cellName);
Console.WriteLine("{0} {1}","The column is:", match.Value);
Console.ReadKey();
}
}
}
}
// Output:
// The column is: B
Imports System.Collections.Generic
Imports System.Linq
Imports DocumentFormat.OpenXml.Packaging
Imports DocumentFormat.OpenXml.Spreadsheet
Imports System.Text.RegularExpressions
Module Module1
' Given a document name, a worksheet name, and a cell name, gets the column of the cell.
Sub Main(ByVal args As String())
Dim docName As String = "C:\Users\Public\Documents\ColumnEx.xlsx"
Dim worksheetName As String = "Sheet1"
Dim cellName As String = "B2"
' Open the document as read-only.
Using document As SpreadsheetDocument = SpreadsheetDocument.Open(docName, False)
Dim sheets As IEnumerable(Of Sheet) = document.WorkbookPart.Workbook.Descendants(Of Sheet)().Where(Function(s) s.Name = worksheetName)
Dim worksheetPart As WorksheetPart = DirectCast(document.WorkbookPart.GetPartById(sheets.First().Id), WorksheetPart)
' Get the column name for the specified cell.
' Create a regular expression to match the column name portion of the cell name.
Dim regex As New Regex("[A-Za-z]+")
Dim match As Match = regex.Match(cellName)
Console.WriteLine("{0} {1}", "The column is:", match.Value)
Console.ReadKey()
End Using
End Sub
End Module
' Output:
' The column is: B
Thread Safety
Any public static (Shared in Visual Basic) members of this type are thread safe. Any instance members are not guaranteed to be thread safe.