Content Model
GemBox.Spreadsheet presents workbook content in a hierarchical structure with an ExcelFile as a main class in the hierarchy.
The following sections describe GemBox.Spreadsheet content model classes and properties, and some behaviors and operations common to GemBox.Spreadsheet content model classes.
GemBox.Spreadsheet Content Model
The following diagram shows GemBox.Spreadsheet content model hierarchy - a parent / child relationship between workbook elements.
Each class and property in the diagram links to its help page, so you can easily navigate to details of each workbook content member.
Common operations on GemBox.Spreadsheet Content Model
Following list contains answers to the most common questions related to GemBox.Spreadsheet content model.
How to get/set active worksheet?
Use ActiveWorksheet property.
Active worksheet is the one selected when file is opened with Microsoft Excel. By default active worksheet is the first one added with ExcelWorksheetCollection.Add(String) method.
How to get max. used columns in a worksheet?
Use CalculateMaxUsedColumns() method.
This method will iterate over all rows and find the maximum number of used columns.
How to get used cell range in a worksheet?
Use ExcelWorksheet.GetUsedCellRange(Boolean) method.
If method parameter ignoreCellsWithoutValue is set to true, then allocated cells with Value set to null are ignored. This parameter is useful because cell might be allocated even if it doesn't have a value. For example, it could have just background color, number format or some other style applied via Style property.
Warning
Method ExcelWorksheet.GetUsedCellRange(Boolean) will return null if there are no used cells in the worksheet.
How to iterate over cells in a row?
Use AllocatedCells property.
Use this property if you are reading entire workbook (you don't know exact position of cells with data). When writing values, using Cells property is recommended.
Collection returned from this property contains only allocated cells so it is faster as you avoid checking every single cell in a row. You still need to check if a specific cell contains any value (it can be empty).
How to convert cell/row/column index to name and back?
- Cell index: use CellRange.RowColumnToPosition(Int32, Int32) and CellRange.PositionToRowColumn(String, Int32, Int32) static methods.
- Row index: use ExcelRowCollection.RowIndexToName(Int32) and ExcelRowCollection.RowNameToIndex(String) static methods.
- Column index: use ExcelColumnCollection.ColumnIndexToName(Int32) and ExcelColumnCollection.ColumnNameToIndex(String) static methods.
How to get formatted cell value?
Use GetFormattedValue() method.
This method uses NumberFormat specified on the Style to format the Value.