Worksheet

The worksheet represents a rectangular grid of cells. Each cell can contain data, such as numbers, text, and formulas. The worksheet can contain other elements, such as images, annotations and charts.

The worksheets are represented by the Worksheet class. The Worksheet class cannot be instantiated directly. New worksheets can be created through the Worksheets collection of an existing Workbook object. The worksheets are part of the model and do not provide visualization. To display a worksheet on a form, use the built-in WorkbookView control. For more information about this, see Workbook View.

Cells and Cell Ranges

The cells in a worksheet are accessed through the Cells property, by providing the cell name or the zero-based indices of the cell's column and row. The cell name is specified in the A1 or R1C1 formats. The following example demonstrates how to set the data of a worksheet cell:

C# Copy Code
worksheet.Cells["C4"].Data = 100; // Accessing the cell by its name in the A1 format
worksheet.Cells["R4C3"].Data = 100; // Accessing the cell by its name in the R1C1 format
worksheet.Cells[2, 3].Data = 100; // Accessing the cell by its zero-based indices

C# Copy Code
worksheet.Cells["C4"].Data = 100; // Accessing the cell by its name in the A1 format worksheet.Cells["R4C3"].Data = 100; // Accessing the cell by its name in the R1C1 format worksheet.Cells[2, 3].Data = 100; // Accessing the cell by its zero-based indices

Visual Basic Copy Code
worksheet.Cells("C4").Data = 100 ' Accessing the cell by its name in the A1 format
worksheet.Cells("R4C3").Data = 100 ' Accessing the cell by its name in the R1C1 format
worksheet.Cells(2, 3).Data = 100 ' Accessing the cell by its zero-based indices

Visual Basic Copy Code
worksheet.Cells("C4").Data = 100 ' Accessing the cell by its name in the A1 format worksheet.Cells("R4C3").Data = 100 ' Accessing the cell by its name in the R1C1 format worksheet.Cells(2, 3).Data = 100 ' Accessing the cell by its zero-based indices

The individual cells in the worksheet are represented by the Cell class. This class can be used to manage the cell's data, style, and so on. The worksheets can also provide access to a rectangular subset of cells through the CellRange class. Instances of this class can be obtained through the CellRanges collection by specifying the top, left, bottom, and right cells of the cell area of interest. The CellRange objects can be used to style the entire range of cells, to merge and unmerge the cells in the range, and so on.

Rows and Columns

The rows and columns in a worksheet are represented by the Row and Column classes respectively. The individual rows and columns are obtained by indexing the Rows and Columns collections. The Row and Column objects can be used to adjust the size of the respective row and column and to style all cells in the row or column. The Rows and Columns collections can also provide access to a range of rows and columns. These ranges are represented by the RowRange and ColumnRange classes respectively and can be accessed by using the collection's indexer that accepts two arguments - the start and end index of the range. The range objects can be used to style, resize or hide a range of rows or columns. The following example demonstrates how to hide all rows in a worksheet beyond row 30:

C# Copy Code
worksheet.Rows[30, worksheet.Rows.Count - 1].IsHidden = true;

C# Copy Code
worksheet.Rows[30, worksheet.Rows.Count - 1].IsHidden = true;

Visual Basic Copy Code
worksheet.Rows(30, worksheet.Rows.Count - 1).IsHidden = True

Visual Basic Copy Code
worksheet.Rows(30, worksheet.Rows.Count - 1).IsHidden = True

Pictures and Charts

In addition to the regular data, the worksheet can also contain pictures and charts (collectively known as interactive objects). The interactive objects in the worksheet are managed through the Drawing object. For example, to add new pictures or charts to a worksheet, use one of the AddPicture overloads of the Drawing class.

For additional information about charts, see Charts.