ig.excel.Worksheet
Remarks
An Excel worksheet is essentially a table with a limited number of rows and columns. To create new worksheet, use WorksheetCollection.add method of the Workbook.worksheets collection on a Worksheet.workbook instance.
Dependencies
-
clearConditionalFormattingData
- .clearConditionalFormattingData( );
Clears all conditional formatting rules from the Worksheet so they do not get saved.
Remarks
Note: this method is obsolete. Use the Worksheet.ConditionalFormats.Clear() method instead. -
columns
- .columns( );
- Return Type:
- ig.excel.WorksheetColumnCollection
- Return Type Description:
- The collection of columns in the worksheet.
Gets the collection of columns in the worksheet.
Remarks
The collection of columns is a fixed length collection, with the maximum number of columns in the collection being Workbook.maxExcelColumnCount or Workbook.maxExcel2007ColumnCount, depending on the Workbook.CurrentFormat. Internally, the columns are only created and added to the collection when they are requested.
Note: Iterating the collection will not create all columns. It will only iterate the columns which have already been used. To create and iterate all columns in the worksheet use a For loop, iterating from 0 to one less than the maximum column count, and pass in each index to the collection's indexer.
-
columns
- .columns( index:number );
- Return Type:
- ig.excel.WorksheetColumn
- Return Type Description:
- The column at the specified index.
Gets the column at the specified index.
- index
- Type:number
- The zero-based index of the column to get.
Exceptions
Exception Description ig.ArgumentOutOfRangeException index is less than zero. ig.InvalidOperationException index is greater than or equal to Workbook.maxExcelColumnCount or Workbook.maxExcel2007ColumnCount, depending on the Workbook.currentFormat. Remarks
Note: Iterating the collection will not create all columns. It will only iterate the columns which have already been used. To create and iterate all columns in the worksheet use a For loop, iterating from 0 to one less than the maximum column count, and pass in each index to the collection's indexer.
See Also
-
conditionalFormats
- .conditionalFormats( );
- Return Type:
- ig.excel.ConditionalFormatCollection
- Return Type Description:
- Returns a ConditionalFormatCollection.
Returns a collection to which conditions can be added to control the visual attributes of a WorksheetCell based on user-defined criteria.
See Also
- ig.excel.ConditionalFormatCollection
- ig.excel.ConditionalFormatCollection.addAverageCondition
- ig.excel.ConditionalFormatCollection.addBlanksCondition
- ig.excel.ConditionalFormatCollection.addColorScaleCondition
- ig.excel.ConditionalFormatCollection.addDataBarCondition
- ig.excel.ConditionalFormatCollection.addDateTimeCondition
- ig.excel.ConditionalFormatCollection.addDuplicateCondition
- ig.excel.ConditionalFormatCollection.addErrorsCondition
- ig.excel.ConditionalFormatCollection.addFormulaCondition
- ig.excel.ConditionalFormatCollection.addIconSetCondition
- ig.excel.ConditionalFormatCollection.addNoBlanksCondition
- ig.excel.ConditionalFormatCollection.addNoErrorsCondition
- ig.excel.ConditionalFormatCollection.addOperatorCondition
- ig.excel.ConditionalFormatCollection.addRankCondition
- ig.excel.ConditionalFormatCollection.addTextCondition
- ig.excel.ConditionalFormatCollection.addUniqueCondition
- ig.excel.ConditionBase
-
conditionalFormats
- .conditionalFormats( index:number );
- Return Type:
- ig.excel.ConditionBase
- Return Type Description:
- The item at the specified index
Indexer
- index
- Type:number
- The zer-based index of an item
Exceptions
Exception Description ig.IndexOutOfRangeException If the index is negative or greater than or equal to ConditionalFormatCollection.count See Also
-
dataTables
- .dataTables( );
- Return Type:
- ig.excel.WorksheetDataTableCollection
- Return Type Description:
- The collection of data tables in the worksheet.
Gets the collection of data tables in the worksheet.
-
dataTables
- .dataTables( index:number );
- Return Type:
- ig.excel.WorksheetDataTable
- Return Type Description:
- The data table at the specified index.
Gets the data table at the specified index in the collection.
- index
- Type:number
- The zero-based index of the data table to get.
See Also
-
dataValidationRules
- .dataValidationRules( );
- Return Type:
- ig.excel.DataValidationRuleCollection
- Return Type Description:
- Returns a DataValidationRuleCollection.
Gets the collection of data validation rules applied to cells in the Worksheet.
See Also
-
dataValidationRules
- .dataValidationRules( rule:ig.excel.DataValidationRule );
- Return Type:
- ig.excel.WorksheetReferenceCollection
- Return Type Description:
- Returns a WorksheetReferenceCollection.
Gets the references which have the data validation rule applied to it.
- rule
- Type:ig.excel.DataValidationRule
- A data validation rule.
Exceptions
Exception Description ig.ArgumentNullException Occurs when the rule is null. ig.excel.KeyNotFoundException Occurs when the property is retrieved and the rule is not applied to the DataValidationRuleCollection.worksheet. ig.ArgumentException Occurs when the property is set and the value is from a Worksheet other than this collection. ig.ArgumentException Occurs when the property is set and the rule is invalid based on its rule type's requirements. -
dataValidationRules
- .dataValidationRules( rule:ig.excel.DataValidationRule, value:ig.excel.WorksheetReferenceCollection );
- Return Type:
- ig.excel.WorksheetReferenceCollection
- Return Type Description:
- Returns a WorksheetReferenceCollection.
Sets the references which have the data validation rule applied to it.
- rule
- Type:ig.excel.DataValidationRule
- A data validation rule.
- value
- Type:ig.excel.WorksheetReferenceCollection
Exceptions
Exception Description ig.ArgumentNullException Occurs when the rule is null. ig.excel.KeyNotFoundException Occurs when the property is retrieved and the rule is not applied to the DataValidationRuleCollection.worksheet. ig.ArgumentException Occurs when the property is set and the value is from a Worksheet other than this collection. ig.ArgumentException Occurs when the property is set and the rule is invalid based on its rule type's requirements. -
defaultColumnWidth
- .defaultColumnWidth( );
- Return Type:
- number
- Return Type Description:
- The default column width including padding, in 256ths of the '0' digit character width in the workbook's default font.
Gets the default column width including padding, in 256ths of the '0' digit character width in the workbook's default font.
Remarks
The default column width is the width of all columns which do not have their width set.
The value assigned must be between 0 and 65535. Invalid values will be automatically adjusted to valid values.
Setting or getting this property is equivalent to calling Worksheet.setDefaultColumnWidth or Worksheet.getDefaultColumnWidth using the WorksheetColumnWidthUnit value of Character256th.
See Also
-
defaultColumnWidth
- .defaultColumnWidth( value:number );
- Return Type:
- number
- Return Type Description:
- The default column width including padding, in 256ths of the '0' digit character width in the workbook's default font.
Sets the default column width including padding, in 256ths of the '0' digit character width in the workbook's default font.
- value
- Type:number
Remarks
The default column width is the width of all columns which do not have their width set.
The value assigned must be between 0 and 65535. Invalid values will be automatically adjusted to valid values.
Setting or getting this property is equivalent to calling Worksheet.setDefaultColumnWidth or Worksheet.getDefaultColumnWidth using the WorksheetColumnWidthUnit value of Character256th.
See Also
-
defaultRowHeight
- .defaultRowHeight( );
- Return Type:
- number
- Return Type Description:
- The default row height in twips (1/20th of a point).
Gets the default row height in twips (1/20th of a point).
Exceptions
Exception Description ig.ArgumentOutOfRangeException The value assigned is outside the value range of -1 and 8192. Remarks
The default row height is the height of all rows which do not have their height explicitly set to a positive number.
If the assigned value is -1, the default row height will then be calculated based on the default font for the workbook, and subsequently getting this property will return the font-based default row height.
-
defaultRowHeight
- .defaultRowHeight( value:number );
- Return Type:
- number
- Return Type Description:
- The default row height in twips (1/20th of a point).
Sets the default row height in twips (1/20th of a point).
- value
- Type:number
Exceptions
Exception Description ig.ArgumentOutOfRangeException The value assigned is outside the value range of -1 and 8192. Remarks
The default row height is the height of all rows which do not have their height explicitly set to a positive number.
If the assigned value is -1, the default row height will then be calculated based on the default font for the workbook, and subsequently getting this property will return the font-based default row height.
-
deleteCells
- .deleteCells( region:ig.excel.WorksheetRegion, shiftCellsLeft:boolean );
Deletes a region of cells.
- region
- Type:ig.excel.WorksheetRegion
- The region to delete
- shiftCellsLeft
- Type:boolean
- If true will shift cells left from the right of the region to occupy the deleted region, otherwise will shift cells up from below the region.
Exceptions
Exception Description ig.InvalidOperationException If the operation is not allowed, e.g. if it the range intersects with a protected region. ig.ArgumentNullException If region is null. See Also
-
deleteCells
- .deleteCells( address:string, shiftCellsLeft:boolean );
Deletes a region of cells.
- address
- Type:string
- The address of the region to delete
- shiftCellsLeft
- Type:boolean
- If true will shift cells left from the right of the region to occupy the deleted region, otherwise will shift cells up from below the region.
Exceptions
Exception Description ig.InvalidOperationException If the operation is not allowed, e.g. if it the range intersects with a protected region. ig.ArgumentException If address is not valid. See Also
-
displayOptions
- .displayOptions( );
- Return Type:
- ig.excel.WorksheetDisplayOptions
- Return Type Description:
- The object which controls the display of the worksheet.
Gets the object which controls the display of the worksheet.
Remarks
The display options include any settings which affect the display of the worksheet when viewed in Microsoft Excel. These settings will not affect the printed worksheet or the data stored in the worksheet.
-
filterSettings
- .filterSettings( );
- Return Type:
- ig.excel.WorksheetFilterSettings
- Return Type Description:
- Returns a WorksheetFilterSettings.
Exposes methods to identify a region in the worksheet and to apply filters and optionally sort criteria to that region.
See Also
-
getCell
- .getCell( address:string );
- Return Type:
- ig.excel.WorksheetCell
- Return Type Description:
- A cell represented by the specified address or name.
Gets the cell at the specified address or name.
- address
- Type:string
- The address or name of the cell.
Exceptions
Exception Description ig.ArgumentNullException address is null. ig.ArgumentException address is not a valid name or a valid cell or region address in the workbook's cell reference mode. ig.InvalidOperationException address is a relative R1C1 address. The overload taking an origin cell must be used to resolve relative R1C1 references. Remarks
The Workbook.cellReferenceMode of the workbook will be used to parse the cell address.
If one or more region references are specified instead of a cell reference, the top-left cell of the first region will be returned.
If a name is specified, it must refer to a cell or region in the Worksheet and it must be scoped to the Workbook or the Worksheet or null will be returned.
See Also
-
getCell
- .getCell( address:string, cellReferenceMode:ig.excel.CellReferenceMode );
- Return Type:
- ig.excel.WorksheetCell
- Return Type Description:
- A cell represented by the specified address or name.
Gets the cell at the specified address or name.
- address
- Type:string
- The address or name of the cell.
- cellReferenceMode
- Type:ig.excel.CellReferenceMode
- The cell reference mode to use to parse the cell address.
Exceptions
Exception Description ig.ArgumentNullException address is null. ig.excel.InvalidEnumArgumentException cellReferenceMode is not defined in the CellReferenceMode enumeration. ig.ArgumentException address is not a valid name or a valid cell or region address in the specified cell reference mode. ig.InvalidOperationException address is a relative R1C1 address. The overload taking an origin cell must be used to resolve relative R1C1 references. Remarks
If one or more region references are specified instead of a cell reference, the top-left cell of the first region will be returned.
If a name is specified, it must refer to a cell or region in the Worksheet and it must be scoped to the Workbook or the Worksheet or null will be returned.
See Also
-
getCell
- .getCell( address:string, cellReferenceMode:ig.excel.CellReferenceMode, originCell:ig.excel.WorksheetCell );
- Return Type:
- ig.excel.WorksheetCell
- Return Type Description:
- A cell represented by the specified address or name.
Gets the cell at the specified address or name.
- address
- Type:string
- The address or name of the cell.
- cellReferenceMode
- Type:ig.excel.CellReferenceMode
- The cell reference mode to use to parse the cell address.
- originCell
- Type:ig.excel.WorksheetCell
- The origin for resolving relative R1C1 references.
Exceptions
Exception Description ig.ArgumentNullException address is null. ig.excel.InvalidEnumArgumentException cellReferenceMode is not defined in the CellReferenceMode enumeration. ig.ArgumentException address is not a valid name or a valid cell or region address in the specified cell reference mode. ig.InvalidOperationException address is a relative R1C1 address and originCell is null. An origin cell must be specified to resolve relative R1C1 references. Remarks
If one or more region references are specified instead of a cell reference, the top-left cell of the first region will be returned.
The origin cell specified will not be used if a name is specified, if the workbook has an A1 cell reference mode, or if an absolute R1C1 address is specified.
If a name is specified, it must refer to a cell or region in the Worksheet and it must be scoped to the Workbook or the Worksheet or null will be returned.
See Also
-
getCell
- .getCell( address:string, originCell:ig.excel.WorksheetCell );
- Return Type:
- ig.excel.WorksheetCell
- Return Type Description:
- A cell represented by the specified address or name.
Gets the cell at the specified address or name.
- address
- Type:string
- The address or name of the cell.
- originCell
- Type:ig.excel.WorksheetCell
- The origin for resolving relative R1C1 references.
Exceptions
Exception Description ig.ArgumentNullException address is null. ig.ArgumentException address is not a valid name or a valid cell or region address in the workbook's cell reference mode. ig.InvalidOperationException address is a relative R1C1 address and originCell is null. An origin cell must be specified to resolve relative R1C1 references. Remarks
The Workbook.cellReferenceMode of the workbook will be used to parse the cell address.
If one or more region references are specified instead of a cell reference, the top-left cell of the first region will be returned.
The origin cell specified will not be used if a name is specified, if the workbook has an A1 cell reference mode, or if an absolute R1C1 address is specified.
If a name is specified, it must refer to a cell or region in the Worksheet and it must be scoped to the Workbook or the Worksheet or null will be returned.
See Also
-
getCellConditionalFormat
- .getCellConditionalFormat( rowIndex:number, columnIndex:number );
- Return Type:
- ig.excel.CellConditionalFormat
- Return Type Description:
- The aggregated condition format for the cell at the specified column index or null if the cell is not contained in any conditional format region.
Gets the cell conditional format for the cell at the specified row and column index.
- rowIndex
- Type:number
- The 0-based index of the WorksheetRow within the Worksheet.rows collection.
- columnIndex
- Type:number
- The 0-based index of the cell within the WorksheetRow.
Exceptions
Exception Description ig.ArgumentOutOfRangeException rowIndex is less than zero or greater than or equal to the number of rows in the worksheet. columnIndex is less than zero or greater than or equal to the number of columns in the worksheet. Remarks
Note: if the cell is contained in one or more conditional format regions but doesn't match any of thse criteria an instance of CellConditionalFormat will still be returned but its CellConditionalFormat.hasConditionFormatting property will return null. However, if the sell is not contained in any conditional format region then this method will return null. See Also
-
getDefaultColumnWidth
- .getDefaultColumnWidth( units:ig.excel.WorksheetColumnWidthUnit );
- Return Type:
- number
- Return Type Description:
- The default column width in the specified units.
Gets the default column width in the specified units.
- units
- Type:ig.excel.WorksheetColumnWidthUnit
- The units in which the width should be returned.
Exceptions
Exception Description ig.excel.InvalidEnumArgumentException units is not defined in the WorksheetColumnWidthUnit enumeration. Remarks
If units is Character256th, the value returned will be the same as the value of the Worksheet.defaultColumnWidth property.
See Also
-
getRegion
- .getRegion( address:string );
- Return Type:
- ig.excel.WorksheetRegion
- Return Type Description:
- A region represented by the specified address or name.
Gets the region at the specified address or name.
- address
- Type:string
- The address or name of the region.
Exceptions
Exception Description ig.ArgumentNullException address is null. ig.ArgumentException address is not a valid name or a valid cell or region address in the workbook's cell reference mode. ig.InvalidOperationException address is a relative R1C1 address. The overload taking an origin cell must be used to resolve relative R1C1 references. Remarks
The Workbook.cellReferenceMode of the workbook will be used to parse the region address.
If a cell reference is specified instead of a region reference, a 1x1 region containing the cell at the address will be returned.
If a list of references is specified, the region specified by the first reference will be returned.
If a name is specified, it must refer to a cell or region in the Worksheet and it must be scoped to the Workbook or the Worksheet or null will be returned.
See Also
-
getRegion
- .getRegion( address:string, cellReferenceMode:ig.excel.CellReferenceMode );
- Return Type:
- ig.excel.WorksheetRegion
- Return Type Description:
- A region represented by the specified address or name.
Gets the region at the specified address or name.
- address
- Type:string
- The address or name of the region.
- cellReferenceMode
- Type:ig.excel.CellReferenceMode
- The cell reference mode to use to parse the region address.
Exceptions
Exception Description ig.ArgumentNullException address is null. ig.excel.InvalidEnumArgumentException cellReferenceMode is not defined in the CellReferenceMode enumeration. ig.ArgumentException address is not a valid name or a valid cell or region address in the specified cell reference mode. ig.InvalidOperationException address is a relative R1C1 address. The overload taking an origin cell must be used to resolve relative R1C1 references. Remarks
If a cell reference is specified instead of a region reference, a 1x1 region containing the cell at the address will be returned.
If a list of references is specified, the region specified by the first reference will be returned.
If a name is specified, it must refer to a cell or region in the Worksheet and it must be scoped to the Workbook or the Worksheet or null will be returned.
See Also
-
getRegion
- .getRegion( address:string, cellReferenceMode:ig.excel.CellReferenceMode, originCell:ig.excel.WorksheetCell );
- Return Type:
- ig.excel.WorksheetRegion
- Return Type Description:
- A region represented by the specified address or name.
Gets the region at the specified address or name.
- address
- Type:string
- The address or name of the region.
- cellReferenceMode
- Type:ig.excel.CellReferenceMode
- The cell reference mode to use to parse the region address.
- originCell
- Type:ig.excel.WorksheetCell
- The origin for resolving relative R1C1 references.
Exceptions
Exception Description ig.ArgumentNullException address is null. ig.excel.InvalidEnumArgumentException cellReferenceMode is not defined in the CellReferenceMode enumeration. ig.ArgumentException address is not a valid name or a valid cell or region address in the specified cell reference mode. ig.InvalidOperationException address is a relative R1C1 address and originCell is null. An origin cell must be specified to resolve relative R1C1 references. Remarks
If a cell reference is specified instead of a region reference, a 1x1 region containing the cell at the address will be returned.
If a list of references is specified, the region specified by the first reference will be returned.
The origin cell specified will not be used if a name is specified, if the workbook has an A1 cell reference mode, or if an absolute R1C1 address is specified.
If a name is specified, it must refer to a cell or region in the Worksheet and it must be scoped to the Workbook or the Worksheet or null will be returned.
See Also
-
getRegion
- .getRegion( address:string, originCell:ig.excel.WorksheetCell );
- Return Type:
- ig.excel.WorksheetRegion
- Return Type Description:
- A region represented by the specified address or name.
Gets the region at the specified address or name.
- address
- Type:string
- The address or name of the region.
- originCell
- Type:ig.excel.WorksheetCell
- The origin for resolving relative R1C1 references.
Exceptions
Exception Description ig.ArgumentNullException address is null. ig.ArgumentException address is not a valid name or a valid cell or region address in the workbook's cell reference mode. ig.InvalidOperationException address is a relative R1C1 address and originCell is null. An origin cell must be specified to resolve relative R1C1 references. Remarks
The Workbook.cellReferenceMode of the workbook will be used to parse the region address.
If a cell reference is specified instead of a region reference, a 1x1 region containing the cell at the address will be returned.
If a list of references is specified, the region specified by the first reference will be returned.
The origin cell specified will not be used if a name is specified, if the workbook has an A1 cell reference mode, or if an absolute R1C1 address is specified.
If a name is specified, it must refer to a cell or region in the Worksheet and it must be scoped to the Workbook or the Worksheet or null will be returned.
See Also
-
getRegions
- .getRegions( address:string );
- Return Type:
- ig.excel.WorksheetRegion[]
- Return Type Description:
- An array of regions represented by the specified address or name.
Gets the regions at the specified address or name.
- address
- Type:string
- The address or name of the region.
Exceptions
Exception Description ig.ArgumentNullException address is null. ig.ArgumentException address is not a valid name or a valid cell or region address in the workbook's cell reference mode. ig.InvalidOperationException address is a relative R1C1 address. The overload taking an origin cell must be used to resolve relative R1C1 references. Remarks
The Workbook.cellReferenceMode of the workbook will be used to parse the region address.
The address can be a list of references, each one referring to a separate region on the Worksheet.
If a cell or single region reference is specified instead, an array of one region at the address will be returned.
If a name is specified, it must refer to cells or regions in the Worksheet and it must be scoped to the Workbook or the Worksheet or null will be returned.
See Also
-
getRegions
- .getRegions( address:string, cellReferenceMode:ig.excel.CellReferenceMode );
- Return Type:
- ig.excel.WorksheetRegion[]
- Return Type Description:
- An array of regions represented by the specified address or name.
Gets the regions at the specified address or name.
- address
- Type:string
- The address or name of the region.
- cellReferenceMode
- Type:ig.excel.CellReferenceMode
- The cell reference mode to use to parse the region address.
Exceptions
Exception Description ig.ArgumentNullException address is null. ig.excel.InvalidEnumArgumentException cellReferenceMode is not defined in the CellReferenceMode enumeration. ig.ArgumentException address is not a valid name or a valid cell or region address in the specified cell reference mode. ig.InvalidOperationException address is a relative R1C1 address. The overload taking an origin cell must be used to resolve relative R1C1 references. Remarks
The address can be a list of references, each one referring to a separate region on the Worksheet.
If a cell or single region reference is specified instead, an array of one region at the address will be returned.
If a name is specified, it must refer to cells or regions in the Worksheet and it must be scoped to the Workbook or the Worksheet or null will be returned.
See Also
-
getRegions
- .getRegions( address:string, cellReferenceMode:ig.excel.CellReferenceMode, originCell:ig.excel.WorksheetCell );
- Return Type:
- ig.excel.WorksheetRegion[]
- Return Type Description:
- An array of regions represented by the specified address or name.
Gets the regions at the specified address or name.
- address
- Type:string
- The address or name of the region.
- cellReferenceMode
- Type:ig.excel.CellReferenceMode
- The cell reference mode to use to parse the region address.
- originCell
- Type:ig.excel.WorksheetCell
- The origin for resolving relative R1C1 references.
Exceptions
Exception Description ig.ArgumentNullException address is null. ig.excel.InvalidEnumArgumentException cellReferenceMode is not defined in the CellReferenceMode enumeration. ig.ArgumentException address is not a valid name or a valid cell or region address in the specified cell reference mode. ig.InvalidOperationException address is a relative R1C1 address and originCell is null. An origin cell must be specified to resolve relative R1C1 references. Remarks
The address can be a list of references, each one referring to a separate region on the Worksheet.
If a cell or single region reference is specified instead, an array of one region at the address will be returned.
The origin cell specified will not be used if a name is specified, if the workbook has an A1 cell reference mode, or if an absolute R1C1 address is specified.
If a name is specified, it must refer to cells or regions in the Worksheet and it must be scoped to the Workbook or the Worksheet or null will be returned.
See Also
-
getRegions
- .getRegions( address:string, originCell:ig.excel.WorksheetCell );
- Return Type:
- ig.excel.WorksheetRegion[]
- Return Type Description:
- An array of regions represented by the specified address or name.
Gets the regions at the specified address or name.
- address
- Type:string
- The address or name of the region.
- originCell
- Type:ig.excel.WorksheetCell
- The origin for resolving relative R1C1 references.
Exceptions
Exception Description ig.ArgumentNullException address is null. ig.ArgumentException address is not a valid name or a valid cell or region address in the workbook's cell reference mode. ig.InvalidOperationException address is a relative R1C1 address and originCell is null. An origin cell must be specified to resolve relative R1C1 references. Remarks
The Workbook.cellReferenceMode of the workbook will be used to parse the region address.
The address can be a list of references, each one referring to a separate region on the Worksheet.
If a cell or single region reference is specified instead, an array of one region at the address will be returned.
The origin cell specified will not be used if a name is specified, if the workbook has an A1 cell reference mode, or if an absolute R1C1 address is specified.
If a name is specified, it must refer to cells or regions in the Worksheet and it must be scoped to the Workbook or the Worksheet or null will be returned.
See Also
-
hasProtectionPassword
Inherited- .hasProtectionPassword( );
Returns a boolean indicating if the Sheet has been protected with a password.
Remarks
When protecting a Sheet, a password is optional. The HasProtectionPassword will return true if the Sheet is currently protected (see Sheet.isProtected) and a password was specified when it was protected.
-
hideColumns
- .hideColumns( [startIndex:number], [count:number] );
Hides the columns in the specified range.
- startIndex
- Type:number
- Optional
- The index of the first column to hide, or null to start hiding at the first column.
- count
- Type:number
- Optional
- The number of columns to hide. If null, a single column will be hidden when startIndex is specified and otherwise, all columns will be hidden.
Exceptions
Exception Description ig.ArgumentException Occurs when the startIndex is less than 0. ig.ArgumentException Occurs when the count is less than 0. ig.ArgumentException Occurs when the startIndex plus count is greater than the number of columns in the worksheet. -
hideRows
- .hideRows( [startIndex:number], [count:number] );
Hides the rows in the specified range.
- startIndex
- Type:number
- Optional
- The index of the first row to hide, or null to start hiding at the first row.
- count
- Type:number
- Optional
- The number of rows to hide. If null, a single row will be hidden when startIndex is specified and otherwise, all rows will be hidden.
Exceptions
Exception Description ig.ArgumentException Occurs when the startIndex is less than 0. ig.ArgumentException Occurs when the count is less than 0. ig.ArgumentException Occurs when the startIndex plus count is greater than the number of rows in the worksheet. -
hyperlinks
- .hyperlinks( );
- Return Type:
- ig.excel.WorksheetHyperlinkCollection
- Return Type Description:
- Returns a WorksheetHyperlinkCollection.
Gets the collection of hyperlinks applied to cells and regions in the Worksheet.
See Also
-
hyperlinks
- .hyperlinks( index:number );
- Return Type:
- ig.excel.WorksheetHyperlink
- Return Type Description:
- The hyperlink at the specified index.
Gets the hyperlink at the specified index in the collection.
- index
- Type:number
- The zero-based index of the hyperlink to get.
Exceptions
Exception Description ig.ArgumentOutOfRangeException index is less than zero or index is greater than or equal to WorksheetHyperlinkCollection.count. See Also
-
index
- .index( );
- Return Type:
- number
- Return Type Description:
- The zero-based index of this worksheet in its parent Worksheets collection.
Gets the zero-based index of this worksheet in its parent Workbook.worksheets collection.
Remarks
Negative one indicates the worksheet has been removed from its parent collection.
-
insertCells
- .insertCells( region:ig.excel.WorksheetRegion, shiftCellsRight:boolean );
Inserts a region of cells.
- region
- Type:ig.excel.WorksheetRegion
- The region to insert
- shiftCellsRight
- Type:boolean
- If true will shift existing cells right to make room for the newly inserted cells, otherwise will shift existing cells down below the region.
Exceptions
Exception Description ig.InvalidOperationException If the operation is not allowed, e.g. if it would cause data to be shifted off the worksheet. ig.ArgumentNullException If region is null. See Also
-
insertCells
- .insertCells( address:string, shiftCellsRight:boolean );
Inserts a region of cells.
- address
- Type:string
- The region to insert
- shiftCellsRight
- Type:boolean
- If true will shift existing cells right to make room for the newly inserted cells, otherwise will shift existing cells down below the region.
Exceptions
Exception Description ig.InvalidOperationException If the operation is not allowed, e.g. if it would cause data to be shifted off the worksheet. ig.ArgumentException If address is not valid. See Also
-
isProtected
Inherited- .isProtected( );
Returns a boolean indicating if the Worksheet has been protected.
Remarks
The IsProtected property will return true if the Worksheet is currently protected. When a Worksheet is protected only unprotected cells may be edited. In addition, other operations may be limited based on the properties of the WorksheetProtection which may be accessed using the Protection property.
See Also
-
mergedCellsRegions
- .mergedCellsRegions( );
- Return Type:
- ig.excel.WorksheetMergedCellsRegionCollection
- Return Type Description:
- The collection of merged cell ranges in this worksheet.
Gets the collection of merged cell ranges in this worksheet.
Remarks
Use WorksheetMergedCellsRegionCollection.add method to add new merged cell ranges to the worksheet.
-
mergedCellsRegions
- .mergedCellsRegions( index:number );
- Return Type:
- ig.excel.WorksheetMergedCellsRegion
- Return Type Description:
- The merged cell region at the specified index.
Gets the merged cell region at the specified index.
- index
- Type:number
- The zero-based index of the merged cell region to get.
Exceptions
Exception Description ig.ArgumentOutOfRangeException index is less than zero or index is greater than or equal to WorksheetMergedCellsRegionCollection.count. See Also
-
moveToIndex
- .moveToIndex( index:number );
Moves the worksheet to a new position in the owning workbook's collections of worksheets.
- index
- Type:number
- The new 0-based index to where the worksheet should be moved.
Exceptions
Exception Description ig.InvalidOperationException The worksheet has previously been removed from its workbook. ig.ArgumentOutOfRangeException index is less than 0 or greater than or equal to the number of worksheets in the owning workbook. -
moveToSheetIndex
Inherited- .moveToSheetIndex( index:number );
Moves the sheet to a new position in the owning workbook's collections of sheets.
- index
- Type:number
- The new 0-based index to where the sheet should be moved.
Exceptions
Exception Description ig.InvalidOperationException The sheet has previously been removed from its workbook. ig.ArgumentOutOfRangeException index is less than 0 or greater than or equal to the number of sheets in the owning workbook. -
name
Inherited- .name( );
- Return Type:
- string
- Return Type Description:
- The sheet name.
Gets the sheet name.
Exceptions
Exception Description ig.ArgumentNullException The value assigned is null or empty. ig.ArgumentException The value assigned contains the invalid characters: ':', '\', '/', '?', '*', '[', or ']'. ig.ArgumentException The value assigned exceeds 31 characters in length. ig.ArgumentException The value assigned is being used as the name of another sheet (sheet names are case-insensitively compared). Remarks
The sheet name is case-insensitively unique in the workbook.
The sheet name is shown in the tab for the worksheet. In addition, the worksheet name can be used by formulas from other worksheets to refer to cells in this worksheet.
-
name
Inherited- .name( value:string );
- Return Type:
- string
- Return Type Description:
- The sheet name.
Sets the sheet name.
- value
- Type:string
Exceptions
Exception Description ig.ArgumentNullException The value assigned is null or empty. ig.ArgumentException The value assigned contains the invalid characters: ':', '\', '/', '?', '*', '[', or ']'. ig.ArgumentException The value assigned exceeds 31 characters in length. ig.ArgumentException The value assigned is being used as the name of another sheet (sheet names are case-insensitively compared). Remarks
The sheet name is case-insensitively unique in the workbook.
The sheet name is shown in the tab for the worksheet. In addition, the worksheet name can be used by formulas from other worksheets to refer to cells in this worksheet.
-
printOptions
- .printOptions( );
- Return Type:
- ig.excel.PrintOptions
- Return Type Description:
- The object which controls how the worksheet prints.
Gets the object which controls how the worksheet prints.
Remarks
The print options include any settings which affect the printed appearance of the worksheet. These settings will not affect the data in the worksheet. Although these are not display settings, some worksheet display styles will display all or some of the print options, so these settings may affect the display of the worksheet when viewed in Microsoft Excel.
-
protect
- .protect( [allowDeletingColumns:boolean], [allowDeletingRows:boolean], [allowEditObjects:boolean], [allowEditScenarios:boolean], [allowFiltering:boolean], [allowFormattingCells:boolean], [allowFormattingColumns:boolean], [allowFormattingRows:boolean], [allowInsertingColumns:boolean], [allowInsertingHyperlinks:boolean], [allowInsertingRows:boolean], [allowSorting:boolean], [allowUsingPivotTables:boolean] );
Protects the worksheet without a password.
- allowDeletingColumns
- Type:boolean
- Optional
- Optional boolean indicating the new value for the WorksheetProtection.allowDeletingColumns.
- allowDeletingRows
- Type:boolean
- Optional
- Optional boolean indicating the new value for the WorksheetProtection.allowDeletingRows.
- allowEditObjects
- Type:boolean
- Optional
- Optional boolean indicating the new value for the WorksheetProtection.allowEditObjects.
- allowEditScenarios
- Type:boolean
- Optional
- Optional boolean indicating the new value for the WorksheetProtection.allowEditScenarios.
- allowFiltering
- Type:boolean
- Optional
- Optional boolean indicating the new value for the WorksheetProtection.allowFiltering.
- allowFormattingCells
- Type:boolean
- Optional
- Optional boolean indicating the new value for the WorksheetProtection.allowFormattingCells.
- allowFormattingColumns
- Type:boolean
- Optional
- Optional boolean indicating the new value for the WorksheetProtection.allowFormattingColumns.
- allowFormattingRows
- Type:boolean
- Optional
- Optional boolean indicating the new value for the WorksheetProtection.allowFormattingRows.
- allowInsertingColumns
- Type:boolean
- Optional
- Optional boolean indicating the new value for the WorksheetProtection.allowInsertingColumns.
- allowInsertingHyperlinks
- Type:boolean
- Optional
- Optional boolean indicating the new value for the WorksheetProtection.allowInsertingHyperlinks.
- allowInsertingRows
- Type:boolean
- Optional
- Optional boolean indicating the new value for the WorksheetProtection.allowInsertingRows.
- allowSorting
- Type:boolean
- Optional
- Optional boolean indicating the new value for the WorksheetProtection.allowSorting.
- allowUsingPivotTables
- Type:boolean
- Optional
- Optional boolean indicating the new value for the WorksheetProtection.allowUsingPivotTables.
Remarks
When a Worksheet is protected without a password, the end user may unprotect the Worksheet in Excel without having to supply a password. To programatically unprotect a Worksheet, one may use the Unprotect() method.
The optional parameters are used to update the property values of the WorksheetProtection instance from this Worksheet's Worksheet.protection property. If the parameter is null, the current value of the associated property will be retained; otherwise the property will be updated with the specified value. For example, if the WorksheetProtection.allowDeletingColumns is currently true and the allowDeletingColumns is null, then the property will continue to be true but if the allowDeletingColumns is false the WorksheetProtection.allowDeletingColumns will be changed to false.
When one protects a Worksheet in Excel, the UI allows one to choose which cells may be selected. To affect this state one would change the WorksheetProtection.selectionMode property before or after protecting the Worksheet.
Note: If IsProtected is already true, the method will be ignored.
-
protection
- .protection( );
- Return Type:
- ig.excel.WorksheetProtection
- Return Type Description:
- Returns a WorksheetProtection.
Returns an object that provides information used when the Worksheet has been protected.
-
rows
- .rows( );
- Return Type:
- ig.excel.WorksheetRowCollection
- Return Type Description:
- The collection of rows in the worksheet.
Gets the collection of rows in the worksheet.
Remarks
The collection of rows is a fixed length collection, with the maximum number of rows in the collection being Workbook.maxExcelRowCount or Workbook.maxExcel2007RowCount, depending on the Workbook.CurrentFormat. Internally, the rows are only created and added to the collection when they are requested.
Note: Iterating the collection will not create all rows. It will only iterate the rows which have already been used. To create and iterate all rows in the worksheet use a For loop, iterating from 0 to one less than the maximum row count, and pass in each index to the collection's indexer.
-
rows
- .rows( index:number );
- Return Type:
- ig.excel.WorksheetRow
- Return Type Description:
- The row at the specified index.
Gets the row at the specified index.
- index
- Type:number
- The zero-based index of the row to get.
Exceptions
Exception Description ig.ArgumentOutOfRangeException index is less than zero. ig.InvalidOperationException index is greater than or equal to Workbook.maxExcelRowCount or Workbook.maxExcel2007RowCount, depending on the Workbook.currentFormat. Remarks
Note: Iterating the collection will not create all rows. It will only iterate the rows which have already been used. To create and iterate all rows in the worksheet use a For loop, iterating from 0 to one less than the maximum row count, and pass in each index to the collection's indexer.
See Also
-
selected
Inherited- .selected( );
- Return Type:
- boolean
- Return Type Description:
- The value which indicates whether this worksheet is selected.
Gets the value which indicates whether this worksheet is selected.
Remarks
If the worksheet has been removed from its parent Workbook.worksheets collection, this will always return False.
See Also
-
setDefaultColumnWidth
- .setDefaultColumnWidth( value:number, units:ig.excel.WorksheetColumnWidthUnit );
Sets the default column width in the specified units.
- value
- Type:number
- The default column width to set on the worksheet, expressed in the specified units.
- units
- Type:ig.excel.WorksheetColumnWidthUnit
- The units in which the value is expressed.
Exceptions
Exception Description ig.ArgumentException value is infinity or NaN. ig.excel.InvalidEnumArgumentException units is not defined in the WorksheetColumnWidthUnit enumeration. See Also
-
shapes
- .shapes( );
- Return Type:
- ig.excel.WorksheetShapeCollection
- Return Type Description:
- The collection of shapes on the worksheet.
Gets the collection of shapes on the worksheet.
-
shapes
- .shapes( index:number );
- Return Type:
- ig.excel.WorksheetShape
- Return Type Description:
- The shape at the specified index.
Gets the shape at the specified index in the collection.
- index
- Type:number
- The zero-based index of the shape to get.
Exceptions
Exception Description ig.ArgumentOutOfRangeException index is less than zero or index is greater than or equal to WorksheetShapeCollection.count. See Also
-
sheetIndex
Inherited- .sheetIndex( );
- Return Type:
- number
- Return Type Description:
- The zero-based index of this sheet in its parent Sheets collection.
Gets the zero-based index of this sheet in its parent Workbook.sheets collection.
Remarks
Negative one indicates the sheet has been removed from its parent collection.
-
sortSettings
- .sortSettings( );
- Return Type:
- ig.excel.WorksheetSortSettings
- Return Type Description:
- Returns a WorksheetSortSettings.
Exposes methods to identify a region in the worksheet and to apply sort criteria to that region.
See Also
-
sparklineGroups
- .sparklineGroups( );
- Return Type:
- ig.excel.SparklineGroupCollection
- Return Type Description:
- Returns a SparklineGroupCollection.
Returns a collection of SparklineGroups (read-only)
See Also
-
sparklineGroups
- .sparklineGroups( index:number );
- Return Type:
- ig.excel.SparklineGroup
- Return Type Description:
- The item at the specified index
Indexer
- index
- Type:number
- The zer-based index of an item
Exceptions
Exception Description ig.IndexOutOfRangeException If the index is negative or greater than or equal to SparklineGroupCollection.count See Also
-
tabColorInfo
Inherited- .tabColorInfo( );
- Return Type:
- ig.excel.WorkbookColorInfo
- Return Type Description:
- The WorkbookColorInfo to use for the associated sheet's tab in the tab bar of Microsoft Excel.
Gets the WorkbookColorInfo to use for the associated sheet's tab in the tab bar of Microsoft Excel.
Remarks
If the tab bar is not visible, this color will not be seen.
-
tabColorInfo
Inherited- .tabColorInfo( value:ig.excel.WorkbookColorInfo );
- Return Type:
- ig.excel.WorkbookColorInfo
- Return Type Description:
- The WorkbookColorInfo to use for the associated sheet's tab in the tab bar of Microsoft Excel.
Sets the WorkbookColorInfo to use for the associated sheet's tab in the tab bar of Microsoft Excel.
- value
- Type:ig.excel.WorkbookColorInfo
Remarks
If the tab bar is not visible, this color will not be seen.
-
tables
- .tables( );
- Return Type:
- ig.excel.WorksheetTableCollection
- Return Type Description:
- Returns a WorksheetTableCollection.
Gets the collection of WorksheetTable instances, or regions formatted as tables, in the worksheet.
-
tables
- .tables( index:number );
- Return Type:
- ig.excel.WorksheetTable
- Return Type Description:
- The WorksheetTable at the specified index.
Gets the WorksheetTable at the specified index.
- index
- Type:number
- The zero-based index of the WorksheetTable to get.
Exceptions
Exception Description ig.ArgumentOutOfRangeException index is less than zero or index is greater than or equal to WorksheetTableCollection.count. See Also
-
tables
- .tables( name:string );
- Return Type:
- ig.excel.WorksheetTable
- Return Type Description:
- The WorksheetTable with the specified name.
Gets the WorksheetTable with the specified name.
- name
- Type:string
- The name of the WorksheetTable to get.
Exceptions
Exception Description ig.InvalidOperationException A WorksheetTable with the specified name does not exist in the collection. Remarks
Worksheet names are compared case-insensitively.
-
type
- .type( );
- Return Type:
- ig.excel.SheetType
- Return Type Description:
- Returns a SheetType.
Returns a value indicating the type of sheet
See Also
-
unhideColumns
- .unhideColumns( [startIndex:number], [count:number] );
Unhides the columns in the specified range.
- startIndex
- Type:number
- Optional
- The index of the first column to unhide, or null to start unhiding at the first column.
- count
- Type:number
- Optional
- The number of columns to unhide. If null, a single column will be unhidden when startIndex is specified and otherwise, all columns will be unhidden.
Exceptions
Exception Description ig.ArgumentException Occurs when the startIndex is less than 0. ig.ArgumentException Occurs when the count is less than 0. ig.ArgumentException Occurs when the startIndex plus count is greater than the number of columns in the worksheet. -
unhideRows
- .unhideRows( [startIndex:number], [count:number] );
Unhides the rows in the specified range.
- startIndex
- Type:number
- Optional
- The index of the first row to unhide, or null to start unhiding at the first row.
- count
- Type:number
- Optional
- The number of rows to unhide. If null, a single row will be unhidden when startIndex is specified and otherwise, all rows will be unhidden.
Exceptions
Exception Description ig.ArgumentException Occurs when the startIndex is less than 0. ig.ArgumentException Occurs when the count is less than 0. ig.ArgumentException Occurs when the startIndex plus count is greater than the number of rows in the worksheet. -
unprotect
Inherited- .unprotect( );
Removes the Sheet protection.
-
workbook
Inherited- .workbook( );
- Return Type:
- ig.excel.Workbook
- Return Type Description:
- The Workbook that owns the worksheet.
Gets the Sheet.workbook that owns the worksheet.
See Also