Partager via


Row Class

Row.When the object is serialized out as xml, its qualified name is x:row.

Inheritance Hierarchy

System.Object
  DocumentFormat.OpenXml.OpenXmlElement
    DocumentFormat.OpenXml.OpenXmlCompositeElement
      DocumentFormat.OpenXml.Spreadsheet.Row

Namespace:  DocumentFormat.OpenXml.Spreadsheet
Assembly:  DocumentFormat.OpenXml (in DocumentFormat.OpenXml.dll)

Syntax

'Declaration
<ChildElementInfoAttribute(GetType(Cell))> _
<ChildElementInfoAttribute(GetType(ExtensionList))> _
Public Class Row _
    Inherits OpenXmlCompositeElement
'Usage
Dim instance As Row
[ChildElementInfoAttribute(typeof(Cell))]
[ChildElementInfoAttribute(typeof(ExtensionList))]
public class Row : OpenXmlCompositeElement

Remarks

The following table lists the possible child types:

  • Cell <x:c>

  • ExtensionList <x:extLst>

[ISO/IEC 29500-1 1st Edition]

18.3.1.73 row (Row)

The element expresses information about an entire row of a worksheet, and contains all cell definitions for a particular row in the worksheet.

[Example:

This row expresses information about row 2 in the worksheet, and contains 3 cell definitions.

<row r="2" spans="2:12">
<c r="C2" s="1">
<f>PMT(B3/12,B4,-B5)</f>
<v>672.68336574300008</v>
</c>
  <c r="D2">
<v>180</v>
</c>
  <c r="E2">
<v>360</v>
</c>
</row>

end example]

Parent Elements

sheetData (§18.3.1.80)

Child Elements

Subclause

c (Cell)

§18.3.1.4

extLst (Future Feature Data Storage Area)

§18.2.10

Attributes

Description

collapsed (Collapsed)

'1' if the rows 1 level of outlining deeper than the current row are in the collapsed outline state. It means that the rows which are 1 outline level deeper (numerically higher value) than the current row are currently hidden due to a collapsed outline state.

It is possible for collapsed to be false and yet still have the rows in question hidden. This can be achieved by having a lower outline level collapsed, thus hiding all the child rows.

[Example:

This example shows 3 levels of outlining:

DocumentFormat.OpenXml.Spreadsheet.Row-image001

In the XML must be:

<sheetData>
<row r="6" outlineLevel="3"/>
<row r="7" outlineLevel="3"/>
<row r="8" outlineLevel="2"/>
<row r="9" outlineLevel="1"/>
</sheetData>

end example]

[Example:

This example shows the same outline feature, with the middle level collapsed:

DocumentFormat.OpenXml.Spreadsheet.Row-image002

In the XML must be:

<sheetData>
<row r="6" hidden="1" outlineLevel="3"/>
<row r="7" hidden="1" outlineLevel="3"/>
<row r="8" hidden="1" outlineLevel="2"/>
<row r="9" outlineLevel="1" collapsed="1"/>
</sheetData>

end example]

[Example:

This example shows the same outline feature as above, where both the middle and lowest level are collapsed:

DocumentFormat.OpenXml.Spreadsheet.Row-image003

In the XML must be:

<sheetData>
<row r="6" hidden="1" outlineLevel="3"/>
<row r="7" hidden="1" outlineLevel="3"/>
<row r="8" hidden="1" outlineLevel="2"/>
<row r="9" hidden="1" outlineLevel="1" collapsed="1"/>
<row r="10" collapsed="1"/>
</sheetData>

Note that in this case, if the lowest level were expanded, the middle level would remain collapsed due to collapsed being true on row 9.

end example]

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 boolean datatype.

customFormat (Custom Format)

'1' if the row style should be applied.

The possible values for this attribute are defined by the W3C XML Schema boolean datatype.

customHeight (Custom Height)

'1' if the row height has been manually set.

The possible values for this attribute are defined by the W3C XML Schema boolean datatype.

hidden (Hidden)

'1' if the row is hidden, e.g., due to a collapsed outline or by manually selecting and hiding a row.

The possible values for this attribute are defined by the W3C XML Schema boolean datatype.

ht (Row Height)

Row height measured in point size. There is no margin padding on row height.

The possible values for this attribute are defined by the W3C XML Schema double datatype.

outlineLevel (Outline Level)

Outlining level of the row, when outlining is on. 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.

ph (Show Phonetic)

'1' if the row should show phonetic.

The possible values for this attribute are defined by the W3C XML Schema boolean datatype.

r (Row Index)

Row index. Indicates to which row in the sheet this <row> definition corresponds.

The possible values for this attribute are defined by the W3C XML Schema unsignedInt datatype.

s (Style Index)

Index to style record for the row (only applied if customFormat attribute is '1')

The possible values for this attribute are defined by the W3C XML Schema unsignedInt datatype.

spans (Spans)

Optimization only, and not required. Specifies the range of non-empty columns (in the format X:Y) for the block of rows to which the current row belongs. To achieve the optimization, span attribute values in a single block should be the same.

There are 16 rows per block, beginning with the first row.

[Note: this is an optimization, and is purely optional. Different span values within the same row block is allowed. Not writing the span value at all is also allowed. end note]

Blank rows are not required to write out span values.

[Example: If cells F8, E9, and D10 have data in them and the rest of the sheet is empty, then for those three rows (8,9, and 10), the spans value should each be "4:6":

<sheetData>
<row r="8" spans="4:6">
<c r="F8">
<v>1</v>
</c>
</row>
  <row r="9" spans="4:6">
<c r="E9">
<v>2</v>
</c>
</row>
  <row r="10" spans="4:6">
<c r="D10">
<v>3</v>
</c>
</row>
</sheetData>

If cells A1 and J10 have data in them and the rest of the sheet is empty, then the rows should be written like this:

<sheetData>
<row r="1" spans="1:10">
<c r="A1">
<v>1</v>
</c>
</row>
  <row r="10" spans="1:10">
<c r="J10">
<v>2</v>
</c>
</row>
</sheetData>

end example]

The possible values for this attribute are defined by the ST_CellSpans simple type (§18.18.9).

thickBot (Thick Bottom)

'1' if any cell in the row has a medium or thick bottom border, or if any cell in the row directly below the current row has a thick top border. When true and customHeight is false, this flag means that the row height has been adjusted higher by .75 points of the normal style font height. This also means that if the row no longer contains these borders, then the height is automatically re-adjusted down.

This adjustment is in addition to any adjustment of height due to thickTop.

Medium borders are these enumeration values from the Styles Part:

  • mediumDashDotDot

  • slantDashDot

  • mediumDashDot

  • mediumDashed

  • medium

  • Thick borders are these enumeration values from the Styles Part:

  • thick

  • double

The possible values for this attribute are defined by the W3C XML Schema boolean datatype.

thickTop (Thick Top Border)

True if the row has a medium or thick top border, or if any cell in the row directly above the current row has a thick bottom border. When true and customHeight is false, this flag means that the row height has been adjusted higher by .75 points of the normal style font height. This also means that if the row no longer contains these borders, then the height is automatically re-adjusted down.

This adjustment is in addition to any adjustment of height due to thickBot.

Medium borders are these enumeration values from the Styles Part:

  • mediumDashDotDot

  • slantDashDot

  • mediumDashDot

  • mediumDashed

  • medium

Thick borders are these enumeration values from the Styles Part:

  • thick

  • double

The possible values for this attribute are defined by the W3C XML Schema boolean datatype.

[Note: The W3C XML Schema definition of this element’s content model (CT_Row) is located in §A.2. end note]

© ISO/IEC29500: 2008.

Examples

The following code example creates a spreadsheet documents, adds a row to the cell table, and writes the number "123" in the cell A1.

using System;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

namespace RowEx
{
    class Program
    {
        static void Main(string[] args)
        {
            string fileName = @"C:\Users\Public\Documents\RowEx.xlsx";

            // Create a spreadsheet document by using the file name.
            SpreadsheetDocument spreadsheetDocument =
                 SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook);

            // Add a WorkbookPart and Workbook objects.
            WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
            workbookpart.Workbook = new Workbook();

            // Add a WorksheetPart.
            WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();

            // Create Worksheet and SheetData objects.
            worksheetPart.Worksheet = new Worksheet(new SheetData());

            // Add a Sheets object.
            Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook
                .AppendChild<Sheets>(new Sheets());

            // Append the new worksheet named "mySheet" and associate it 
            // with the workbook.
            Sheet sheet = new Sheet()
            {
                Id = spreadsheetDocument.WorkbookPart
                    .GetIdOfPart(worksheetPart),
                SheetId = 1,
                Name = "mySheet"
            };
            sheets.Append(sheet);

            // Get the sheetData cell table.
            SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();

            // Add a row to the cell table.
            Row row;
            row = new Row() { RowIndex = 1 };
            sheetData.Append(row);

            // Add the cell to the cell table at A1.
            Cell refCell = null;
            Cell newCell = new Cell() { CellReference = "A1" };
            row.InsertBefore(newCell, refCell);

            // Set the cell value to be a numeric value of 123.
            newCell.CellValue = new CellValue("123");
            newCell.DataType = new EnumValue<CellValues>(CellValues.Number);

            // Close the document.
            spreadsheetDocument.Close();

            Console.WriteLine("All done. Press a key");
            Console.ReadKey();
        }
    }
}
Imports System
Imports DocumentFormat.OpenXml
Imports DocumentFormat.OpenXml.Packaging
Imports DocumentFormat.OpenXml.Spreadsheet

Module Module1

    Sub Main(ByVal args As String())
        Dim fileName As String = "C:\Users\Public\Documents\RowEx.xlsx"

        ' Create a spreadsheet document by using the file name.
        Dim spreadsheetDocument As SpreadsheetDocument = _
            spreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook)

        ' Add a WorkbookPart and Workbook objects.
        Dim workbookpart As WorkbookPart = spreadsheetDocument.AddWorkbookPart()
        workbookpart.Workbook = New Workbook()

        ' Add a WorksheetPart.
        Dim worksheetPart As WorksheetPart = _
            workbookpart.AddNewPart(Of WorksheetPart)()

        ' Create Worksheet and SheetData objects.
        worksheetPart.Worksheet = New Worksheet(New SheetData())

        ' Add a Sheets object.
        Dim sheets As Sheets = _
            spreadsheetDocument.WorkbookPart.Workbook.AppendChild(Of Sheets)(New Sheets())

        ' Append the new worksheet named "mySheet" and associate it 
        ' with the workbook.
        Dim sheet As New Sheet() With { _
         .Id = SpreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart), _
         .SheetId = 1, _
         .Name = "mySheet" _
        }
        sheets.Append(sheet)

        ' Get the sheetData cell table.
        Dim sheetData As SheetData = _
            worksheetPart.Worksheet.GetFirstChild(Of SheetData)()

        ' Add a row to the cell table.
        Dim row As Row
        row = New Row() With { _
         .RowIndex = 1 _
        }
        sheetData.Append(row)

        ' Add the cell to the cell table at A1.
        Dim refCell As Cell = Nothing
        Dim newCell As New Cell() With { _
         .CellReference = "A1" _
        }
        row.InsertBefore(newCell, refCell)

        ' Set the cell value to be a numeric value of 123.
        newCell.CellValue = New CellValue("123")
        newCell.DataType = New EnumValue(Of CellValues)(CellValues.Number)

        ' Close the document.
        spreadsheetDocument.Close()

        Console.WriteLine("All done. Press a key")
        Console.ReadKey()
    End Sub
End Module

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.

See Also

Reference

Row Members

DocumentFormat.OpenXml.Spreadsheet Namespace