You can combine cells to create a span of cells, as shown in the figure below. Creating a span of cells creates one large cell where there had previously been several. For example, if you create a span of cells from cell B2 to cell D3, cell B2 then appears to occupy the space from cell B2 through cell D3.
The component is divided into four parts: sheet corner, column headers, rows headers, and data area. You can create spans within a part, but you can not create a span that goes across parts. For example, you can not span cells in the data area with cells in the row headers and you can not span cells in the column header with the sheet corner. This topic discusses spanning cells in the data area. For more information on creating a span of header cells, refer to Creating a Span in a Header.
When you create a span of cells, the data in the first cell in the span (called the anchor cell) occupies all the space in the span. When you create a span, the data that was in each of the cells in the span is still in each cell, but not displayed. The data is simply hidden by the span range. If you remove the span from a group of cells, the content of the spanned cells, which previously was hidden, is displayed as appropriate. Create a span of cells by calling the AddSpanCell method. The cell types of the cells combined in the span are not changed. The spanned cell takes the type of the left-most cell in the span.
You can return whether a specified cell is in a span of cells with the GetSpanCell method.
You can remove a span from a range of cells by calling the RemoveSpanCell method. You can remove a span range by calling this method, specifying the anchor cell of the span range to remove the range. When you remove a span range, the data that was previously in each of the cells in the span is re-displayed in the cell. The data was never removed from the cell, but simply hidden by the span range.
Note: Spans that are added to a sorted sheet are not shown, and spans will be hidden when the sheet or any part of it is sorted with any sort method other than SortRange. Cell ranges that contain spans cannot be sorted with SortRange.
Whatever properties you set on the anchor cell are applied to the cell span. This includes setting a cell note, too. If you set a cell note to one of the cells in the span that is not the anchor, the cell note is not displayed. When you create a span of cells, the data in the first cell in the span (designated by the lCol and lRow parameters) occupies all the space in the span. When you create a span, the data that was in each of the cells in the span is still in each cell, but not displayed. The data is simply hidden by the span range. Also, the cell types of the cells combined in the span are not changed. This method is called for the current sheet setting unless you first set the Sheet property to specify the sheet with which you want to work. You can return whether a specified cell is in a span of cells by calling the GetCellSpan method.
Call the GetCellSpan method to return whether a cell is in a span of cells, and if it is in a span of cells, it returns the CellRange object that contains the column and row number of the anchor cell and the number of columns and rows in the span range. This method is called for the currently selected sheet unless you first set the Sheets object to specify the sheet with which to work.
If a merged column overlaps a span, then the merged column replaces the span. It is recommended that you do not merge cells that are part of a span. For more information on automatically merging cells with identical content, refer to Allowing Cells to Merge Automatically.
For information on the underlying model for spans, refer to Understanding the Span Model.
Return to Customizing the Appearance of a Cell.
Using the Properties Window
- At design time, in the Properties window, select the Spread component.
- Select the Sheets property.
- Click the button to display the SheetView Collection Editor.
- In the Properties window on the right side of the SheetView Collection Editor, select the Cells property for the sheet.
- Click the button to display the Cell, Column, and Row Editor.
- In the editor, select either the Row Span or Column Span property and set the number to the number of cells to span starting from the selected cell. To remove a span, set the value back to 1.
- If you want to apply this change, click Apply.
- Click OK to close each editor.
The preview on the left side of the editor shows the cells spanned.
Using a Shortcut
To span cells (or remove spanning) use any of the following members:
- AddSpanCell, GetSpanCell, and RemoveSpanCell
- AddColumnHeaderSpan and AddRowHeaderSpan
For more information on these properties and methods, refer to the SheetView class.
Call the Sheets object AddSpanCell method to span the cells.
This example code defines some content then spans six adjoining cells.
// Create some content in two cells.
fpSpread1.ActiveSheet.Cells[1,1].Text = "These six cells are spanned.";
fpSpread1.ActiveSheet.Cells[2,2].Text = "This is text in 2,2.";
// Span six cells including the ones with different content.
fpSpread1.ActiveSheet.AddSpanCell(1, 1, 2, 3);
' Create some content in two cells.
fpSpread1.ActiveSheet.Cells(1,1).Text = "These six cells are spanned."
fpSpread1.ActiveSheet.Cells(2,2).Text = "This is text in 2,2."
' Span six cells including the ones with different content.
fpSpread1.ActiveSheet.AddSpanCell(1, 1, 2, 3)
Using the Spread Designer
- On the spreadsheet, select the cells to span.
- Right-click and select Span or in the property list (in the Misc category), select either the Row Span or Column Span property and set the number to a value greater than one to span cells. To remove a span, set the value back to 1.
- From the File menu choose Apply and Exit to apply your changes to the component and exit Spread Designer.
The Designer shows the cells spanned.