-
Spread Windows Forms 18 Product Documentation
- Getting Started
-
Developer's Guide
- Understanding the Product
- Working with the Component
- Spreadsheet Objects
- Ribbon Control
- Sheets
-
Rows and Columns
- Customizing the Number of Rows or Columns
- Adding a Row or Column
- Removing a Row or Column
- Showing or Hiding a Row or Column
- Setting the Row Height or Column Width
- Setting Fixed (Frozen) Rows or Columns
- Moving Rows or Columns
- Creating Alternating Rows
- Setting up Preview Rows
- Input Data in Rows or Columns
- Rows or Columns That Have Data
- Adding a Tag to a Row or Column
- Headers
- Cells
- Cell Types
- Data Binding
- Customizing the Sheet Appearance
- Customizing Interaction in Cells
- Tables
- Understanding the Underlying Models
- Customizing Row or Column Interaction
- Formulas in Cells
- Sparklines
- Keyboard Interaction
- Events from User Actions
- File Operations
- Storing Excel Summary and View
- Printing
- Chart Control
- Customizing Drawing
- Touch Support with the Component
- Spread Designer Guide
- Assembly Reference
- Import and Export Reference
- Version Comparison Reference
Rows or Columns That Have Data
You can work with the rows and columns of the sheet and distinguish which ones have data by using various members of the SheetView class. You can use the following methods available on the sheet:
GetLastNonEmptyColumn method
GetLastNonEmptyRow method
You can return the number of rows and columns that have data using these properties:
NonEmptyColumnCount property
NonEmptyColumnCount property
Row/Column with Source Range
It is also possible to return the last row and column within a specified range of a sheet using the IRange.End method. This method accepts two parameters i.e. direction enum to specify the direction and includeHidden parameter to specify whether to include hidden rows and columns.
Refer to the following code to return the cell at the end of specified source range in left direction.
// IRange.End(Direction.Left)
IWorksheet TestActiveSheet = fpSpread1.AsWorkbook().ActiveSheet;
TestActiveSheet.Cells["A1"].Value = 1;
TestActiveSheet.Cells["C1:F1"].Value = 1;
TestActiveSheet.Columns[1].Hidden = true;
TestActiveSheet.Columns[5].Hidden = true;
// No data left source range
Debug.WriteLine(TestActiveSheet.Cells["B5"].End(Direction.Left, false).Address()); //or true. return A5
// Source range inside data range
Debug.WriteLine(TestActiveSheet.Cells["D1"].End(Direction.Left, false).Address()); //return A1
Debug.WriteLine(TestActiveSheet.Cells["D1"].End(Direction.Left, true).Address()); //return C1
// There is data left source range
Debug.WriteLine(TestActiveSheet.Cells["I1"].End(Direction.Left, false).Address()); //return E1
Debug.WriteLine(TestActiveSheet.Cells["I1"].End(Direction.Left, true).Address()); //return F1' IRange.End(Direction.Left)
Dim TestActiveSheet As IWorksheet = FpSpread1.AsWorkbook().ActiveSheet
TestActiveSheet.Cells("A1").Value = 1
TestActiveSheet.Cells("C1:F1").Value = 1
TestActiveSheet.Columns(1).Hidden = True
TestActiveSheet.Columns(5).Hidden = True
' No data left source range
Debug.WriteLine(TestActiveSheet.Cells("B5").End(Direction.Left, False).Address()) 'or true. return A5
' Source range inside data range
Debug.WriteLine(TestActiveSheet.Cells("D1").End(Direction.Left, False).Address()) 'return A1
Debug.WriteLine(TestActiveSheet.Cells("D1").End(Direction.Left, True).Address()) 'return C1
' There is data left source range
Debug.WriteLine(TestActiveSheet.Cells("I1").End(Direction.Left, False).Address()) 'return E1
Debug.WriteLine(TestActiveSheet.Cells("I1").End(Direction.Left, True).Address()) 'return F1Refer to the following code to return the cell at the end of specified source range in right direction.
// IRange.End(Direction.Right)
IWorksheet TestActiveSheet = fpSpread1.AsWorkbook().ActiveSheet;
TestActiveSheet.Cells["C1:F1"].Value = 1;
TestActiveSheet.Cells["H1"].Value = 1;
TestActiveSheet.Columns[2].Hidden = true;
TestActiveSheet.Columns[6].Hidden = true;
// No data right source range
Debug.WriteLine(TestActiveSheet.Cells["B5"].End(Direction.Right, false).Address()); //or true. return SF5
// Source range inside data range
Debug.WriteLine(TestActiveSheet.Cells["D1"].End(Direction.Right, false).Address()); //return H1
Debug.WriteLine(TestActiveSheet.Cells["D1"].End(Direction.Right, true).Address()); //return F1
//There is data right source range
Debug.WriteLine(TestActiveSheet.Cells["A1"].End(Direction.Right, false).Address()); //return D1
Debug.WriteLine(TestActiveSheet.Cells["A1"].End(Direction.Right, true).Address()); //return C1' IRange.End(Direction.Right)
Dim TestActiveSheet As IWorksheet = TestWorkBook.ActiveSheet
TestActiveSheet.Cells("C1:F1").Value = 1
TestActiveSheet.Cells("H1").Value = 1
TestActiveSheet.Columns(2).Hidden = True
TestActiveSheet.Columns(6).Hidden = True
' No data right source range
Debug.WriteLine(TestActiveSheet.Cells("B5").End(Direction.Right, False).Address()) 'or true. return SF5
' Source range inside data range
Debug.WriteLine(TestActiveSheet.Cells("D1").End(Direction.Right, False).Address()) 'return H1
Debug.WriteLine(TestActiveSheet.Cells("D1").End(Direction.Right, True).Address()) 'return F1
' There is data right source range
Debug.WriteLine(TestActiveSheet.Cells("A1").End(Direction.Right, False).Address()) 'return D1
Debug.WriteLine(TestActiveSheet.Cells("A1").End(Direction.Right, True).Address()) 'return C1Refer to the following code to return the cell at the end of specified source range in upward direction.
// IRange.End(Direction.Up)
IWorksheet TestActiveSheet = fpSpread1.AsWorkbook().ActiveSheet;
TestActiveSheet.Cells["C1"].Value = 1;
TestActiveSheet.Cells["C3:C6"].Value = 1;
TestActiveSheet.Rows[1].Hidden = true;
TestActiveSheet.Rows[5].Hidden = true;
// No data above source range
Debug.WriteLine(TestActiveSheet.Cells["B5"].End(Direction.Up, false).Address()); //or true. return B1
// Source range inside data range
Debug.WriteLine(TestActiveSheet.Cells["C4"].End(Direction.Up, false).Address()); //return C1
Debug.WriteLine(TestActiveSheet.Cells["C4"].End(Direction.Up, true).Address()); //return C3
// There is data above source range
Debug.WriteLine(TestActiveSheet.Cells["C10"].End(Direction.Up, false).Address()); //return C5
Debug.WriteLine(TestActiveSheet.Cells["C10"].End(Direction.Up, true).Address()); //return C6' IRange.End(Direction.Up)
Dim TestActiveSheet As IWorksheet = FpSpread1.AsWorkbook().ActiveSheet
TestActiveSheet.Cells("C1").Value = 1
TestActiveSheet.Cells("C3:C6").Value = 1
TestActiveSheet.Rows(1).Hidden = True
TestActiveSheet.Rows(5).Hidden = True
' No data above source range
Debug.WriteLine(TestActiveSheet.Cells("B5").End(Direction.Up, False).Address()) 'or true. return B1
' Source range inside data range
Debug.WriteLine(TestActiveSheet.Cells("C4").End(Direction.Up, False).Address()) 'return C1
Debug.WriteLine(TestActiveSheet.Cells("C4").End(Direction.Up, True).Address()) 'return C3
' There is data above source range
Debug.WriteLine(TestActiveSheet.Cells("C10").End(Direction.Up, False).Address()) 'return C5
Debug.WriteLine(TestActiveSheet.Cells("C10").End(Direction.Up, True).Address()) 'return C6Refer to the following code to return the cell at the end of specified source range in downward direction.
// IRange.End(Direction.Down)
IWorksheet TestActiveSheet = fpSpread1.AsWorkbook().ActiveSheet;
TestActiveSheet.Cells["C3:C6"].Value = 1;
TestActiveSheet.Cells["C8"].Value = 1;
TestActiveSheet.Rows[2].Hidden = true;
TestActiveSheet.Rows[6].Hidden = true;
// No data below source range
Debug.WriteLine(TestActiveSheet.Cells["B5"].End(Direction.Down, false).Address()); //or true. return B500
// Source range inside data range
Debug.WriteLine(TestActiveSheet.Cells["C4"].End(Direction.Down, false).Address()); //return C8
Debug.WriteLine(TestActiveSheet.Cells["C4"].End(Direction.Down, true).Address()); //return C6
// There is data below source range
Debug.WriteLine(TestActiveSheet.Cells["C1"].End(Direction.Down, false).Address()); //return C4
Debug.WriteLine(TestActiveSheet.Cells["C1"].End(Direction.Down, true).Address()); //return C3' IRange.End(Direction.Down)
Dim TestActiveSheet As IWorksheet = TestWorkBook.ActiveSheet
TestActiveSheet.Cells("C3:C6").Value = 1
TestActiveSheet.Cells("C8").Value = 1
TestActiveSheet.Rows(2).Hidden = True
TestActiveSheet.Rows(6).Hidden = True
' No data below source range
Debug.WriteLine(TestActiveSheet.Cells("B5").End(Direction.Down, False).Address()) 'or true. return B500
' Source range inside data range
Debug.WriteLine(TestActiveSheet.Cells("C4").End(Direction.Down, False).Address()) 'return C8
Debug.WriteLine(TestActiveSheet.Cells("C4").End(Direction.Down, True).Address()) 'return C6
' There is data below source range
Debug.WriteLine(TestActiveSheet.Cells("C1").End(Direction.Down, False).Address()) 'return C4
Debug.WriteLine(TestActiveSheet.Cells("C1").End(Direction.Down, True).Address()) 'return C3!type=note
Note: The IRange.End method returns the first cell inside the source range.
See Also
Customizing the Number of Rows or Columns
Showing or Hiding a Row or Column
Setting the Row Height or Column Width