Class WorksheetCollection
Namespace: Aspose.Cells
Assembly: Aspose.Cells.dll (25.2.0)
Encapsulates a collection of Aspose.Cells.Worksheet objects.
public class WorksheetCollection : CollectionBase<worksheet>, IList<worksheet>, ICollection<worksheet>, IEnumerable<worksheet>, ICollection, IEnumerableInheritance
object ← CollectionBase<worksheet> ← WorksheetCollection
Implements
IList<worksheet>, ICollection<worksheet>, IEnumerable<worksheet>, ICollection, IEnumerable
Inherited Members
CollectionBase<worksheet>.BinarySearch(Worksheet), CollectionBase<worksheet>.BinarySearch(Worksheet, IComparer<worksheet>), CollectionBase<worksheet>.BinarySearch(int, int, Worksheet, IComparer<worksheet>), CollectionBase<worksheet>.Contains(Worksheet), CollectionBase<worksheet>.CopyTo(Worksheet[]), CollectionBase<worksheet>.CopyTo(Worksheet[], int), CollectionBase<worksheet>.CopyTo(int, Worksheet[], int, int), CollectionBase<worksheet>.Exists(Predicate<worksheet>), CollectionBase<worksheet>.Find(Predicate<worksheet>), CollectionBase<worksheet>.FindAll(Predicate<worksheet>), CollectionBase<worksheet>.FindIndex(Predicate<worksheet>), CollectionBase<worksheet>.FindIndex(int, Predicate<worksheet>), CollectionBase<worksheet>.FindIndex(int, int, Predicate<worksheet>), CollectionBase<worksheet>.FindLast(Predicate<worksheet>), CollectionBase<worksheet>.FindLastIndex(Predicate<worksheet>), CollectionBase<worksheet>.FindLastIndex(int, Predicate<worksheet>), CollectionBase<worksheet>.FindLastIndex(int, int, Predicate<worksheet>), CollectionBase<worksheet>.IndexOf(Worksheet), CollectionBase<worksheet>.IndexOf(Worksheet, int), CollectionBase<worksheet>.IndexOf(Worksheet, int, int), CollectionBase<worksheet>.LastIndexOf(Worksheet), CollectionBase<worksheet>.LastIndexOf(Worksheet, int), CollectionBase<worksheet>.LastIndexOf(Worksheet, int, int), CollectionBase<worksheet>.GetEnumerator(), CollectionBase<worksheet>.Clear(), CollectionBase<worksheet>.RemoveAt(int), CollectionBase<worksheet>.OnClearComplete(), CollectionBase<worksheet>.OnClear(), CollectionBase<worksheet>.Capacity, CollectionBase<worksheet>.Count, CollectionBase<worksheet>.InnerList, CollectionBase<worksheet>.this[int], object.GetType(), object.MemberwiseClone(), object.ToString(), object.Equals(object?), object.Equals(object?, object?), object.ReferenceEquals(object?, object?), object.GetHashCode()
Examples
Workbook workbook = new Workbook();
WorksheetCollection sheets = workbook.Worksheets;
//Add a worksheet
sheets.Add();
//Change the name of a worksheet
sheets[0].Name = "First Sheet";
//Set the active sheet to the second worksheet
sheets.ActiveSheetIndex = 1;Dim excel as Workbook = new Workbook()
Dim sheets as WorksheetCollection = excel.Worksheets
'Add a worksheet
sheets.Add()
'Change the name of a worksheet
sheets(0).Name = "First Sheet"
'Set the active sheet to the second worksheet
sheets.ActiveSheetIndex = 1Properties
ActiveSheetIndex
Represents the index of active worksheet when the spreadsheet is opened.
public int ActiveSheetIndex { get; set; }Property Value
Remarks
Sheet index is zero based.
ActiveSheetName
Represents the name of active worksheet when the spreadsheet is opened.
public string ActiveSheetName { get; set; }Property Value
BuiltInDocumentProperties
Returns a Aspose.Cells.Properties.DocumentProperty collection that represents all the built-in document properties of the spreadsheet.
public BuiltInDocumentPropertyCollection BuiltInDocumentProperties { get; }Property Value
BuiltInDocumentPropertyCollection
Examples
Workbook workbook = new Workbook();
DocumentProperty doc = workbook.Worksheets.BuiltInDocumentProperties["Author"];
doc.Value = "John Smith";Dim workbook as Workbook = New Workbook()
Dim doc as DocumentProperty = workbook.Worksheets.BuiltInDocumentProperties("Author")
doc.Value = "John Smith"Remarks
A new property cannot be added to built-in document properties list. You can only get a built-in property and change its value. The following is the built-in properties name list:
Title
Subject
Author
Keywords
Comments
Template
Last Author
Revision Number
Application Name
Last Print Date
Creation Date
Last Save Time
Total Editing Time
Number of Pages
Number of Words
Number of Characters
Security
Category
Format
Manager
Company
Number of Bytes
Number of Lines
Number of Paragraphs
Number of Slides
Number of Notes
Number of Hidden Slides
Number of Multimedia Clips
CustomDocumentProperties
Returns a Aspose.Cells.Properties.DocumentProperty collection that represents all the custom document properties of the spreadsheet.
public CustomDocumentPropertyCollection CustomDocumentProperties { get; }Property Value
CustomDocumentPropertyCollection
Examples
Workbook workbook = new Workbook();
workbook.Worksheets.CustomDocumentProperties.Add("Checked by", "Jane");Dim workbook as Workbook = New Workbook()
workbook.Worksheets.CustomDocumentProperties.Add("Checked by", "Jane")Dxfs
Gets the master differential formatting records.
public DxfCollection Dxfs { get; }Property Value
ExternalLinks
Represents external links in a workbook.
public ExternalLinkCollection ExternalLinks { get; }Property Value
IsRefreshAllConnections
Indicates whether refresh all connections on opening file in MS Excel.
public bool IsRefreshAllConnections { get; set; }Property Value
Names
Gets the collection of all the Name objects in the spreadsheet.
public NameCollection Names { get; }Property Value
OleSize
Gets and Sets displayed size when Workbook file is used as an Ole object.
public object OleSize { get; set; }Property Value
Remarks
Null means no ole size setting.
RevisionLogs
Represents revision logs.
public RevisionLogCollection RevisionLogs { get; }Property Value
TableStyles
Gets Aspose.Cells.WorksheetCollection.TableStyles object.
public TableStyleCollection TableStyles { get; }Property Value
ThreadedCommentAuthors
Gets the list of threaded comment authors.
public ThreadedCommentAuthorCollection ThreadedCommentAuthors { get; }Property Value
ThreadedCommentAuthorCollection
WebExtensionTaskPanes
Gets the list of task panes.
public WebExtensionTaskPaneCollection WebExtensionTaskPanes { get; }Property Value
WebExtensionTaskPaneCollection
WebExtensions
Gets the list of task panes.
public WebExtensionCollection WebExtensions { get; }Property Value
XmlMaps
Gets and sets the XML maps in the workbook.
public XmlMapCollection XmlMaps { get; set; }Property Value
this[int]
Gets the Aspose.Cells.Worksheet element at the specified index.
public Worksheet this[int index] { get; }Property Value
this[string]
Gets the Aspose.Cells.Worksheet element with the specified name.
public Worksheet this[string sheetName] { get; }Property Value
Methods
Add(SheetType)
Adds a worksheet to the collection.
public int Add(SheetType type)Parameters
type SheetType
Worksheet type.
Returns
Aspose.Cells.Worksheet object index.
Examples
Workbook workbook = new Workbook();
workbook.Worksheets.Add(SheetType.Chart);
Cells cells = workbook.Worksheets[0].Cells;
cells["c2"].PutValue(5000);
cells["c3"].PutValue(3000);
cells["c4"].PutValue(4000);
cells["c5"].PutValue(5000);
cells["c6"].PutValue(6000);
ChartCollection charts = workbook.Worksheets[1].Charts;
int chartIndex = charts.Add(ChartType.Column, 10,10,20,20);
Chart chart = charts[chartIndex];
chart.NSeries.Add("Sheet1!C2:C6", true);Dim workbook As Workbook = New Workbook()
workbook.Worksheets.Add(SheetType.Chart)
Dim cells As Cells = workbook.Worksheets(0).Cells
cells("c2").PutValue(5000)
cells("c3").PutValue(3000)
cells("c4").PutValue(4000)
cells("c5").PutValue(5000)
cells("c6").PutValue(6000)
Dim charts As ChartCollection = workbook.Worksheets(1).Charts
Dim chartIndex As Integer = charts.Add(ChartType.Column,10,10,20,20)
Dim chart As Chart = charts(chartIndex)
chart.NSeries.Add("Sheet1!C2:C6", True)Add()
Adds a worksheet to the collection.
public int Add()Returns
Aspose.Cells.Worksheet object index.
Add(string)
Adds a worksheet to the collection.
public Worksheet Add(string sheetName)Parameters
sheetName string
Worksheet name
Returns
Aspose.Cells.Worksheet object.
AddCopy(string)
Adds a worksheet to the collection and copies data from an existed worksheet.
public int AddCopy(string sheetName)Parameters
sheetName string
Name of source worksheet.
Returns
Aspose.Cells.Worksheet object index.
Exceptions
Specifies an invalid worksheet name.
AddCopy(int)
Adds a worksheet to the collection and copies data from an existed worksheet.
public int AddCopy(int sheetIndex)Parameters
sheetIndex int
Index of source worksheet.
Returns
Aspose.Cells.Worksheet object index.
AddCopy(Worksheet[], string[])
Copy a group of worksheets.
public void AddCopy(Worksheet[] source, string[] destSheetNames)Parameters
source Worksheet[]
The source worksheets.
destSheetNames string[]
The names of the copied sheets.
Clear()
Clear all worksheets.
public void Clear()Remarks
A workbook must contains a worksheet.
ClearPivottables()
Clears pivot tables from the spreadsheet.
public void ClearPivottables()CreateRange(string, int)
Creates a Aspose.Cells.Range object from an address of the range.
public Range CreateRange(string address, int sheetIndex)Parameters
address string
The address of the range.
sheetIndex int
The sheet index.
Returns
A Aspose.Cells.Range object
CreateUnionRange(string, int)
Creates a Aspose.Cells.Range object from an address of the range.
public UnionRange CreateUnionRange(string address, int sheetIndex)Parameters
address string
The address of the range.
sheetIndex int
The sheet index.
Returns
A Aspose.Cells.Range object
GetNamedRanges()
Gets all pre-defined named ranges in the spreadsheet.
public Range[] GetNamedRanges()Returns
Range[]
An array of Range objects. If the defined Name’s reference is external or has multiple ranges, no Range object will be returned for this Name.
Returns null if the named range does not exist.GetNamedRangesAndTables()
Gets all pre-defined named ranges in the spreadsheet.
public Range[] GetNamedRangesAndTables()Returns
Range[]
An array of Range objects.
Returns null if the named range does not exist.GetRangeByName(string)
Gets Range object by pre-defined name.
public Range GetRangeByName(string rangeName)Parameters
rangeName string
Name of range.
Returns
Range object.
Returns null if the named range does not exist.GetRangeByName(string, int, bool)
Gets Aspose.Cells.Range by pre-defined name or table’s name
public Range GetRangeByName(string rangeName, int currentSheetIndex, bool includeTable)Parameters
rangeName string
Name of range or table’s name.
currentSheetIndex int
The sheet index. -1 represents global .
includeTable bool
Indicates whether checking all tables.
Returns
GetSheetByCodeName(string)
Gets the worksheet by the code name.
public Worksheet GetSheetByCodeName(string codeName)Parameters
codeName string
Worksheet code name.
Returns
The element with the specified code name.
Insert(int, SheetType)
Insert a worksheet.
public Worksheet Insert(int index, SheetType sheetType)Parameters
index int
The sheet index
sheetType SheetType
The sheet type.
Returns
Returns an inserted worksheet.
Insert(int, SheetType, string)
Insert a worksheet.
public Worksheet Insert(int index, SheetType sheetType, string sheetName)Parameters
index int
The sheet index
sheetType SheetType
The sheet type.
sheetName string
The sheet name.
Returns
Returns an inserted worksheet.
RefreshAll()
Refresh all pivot tables and charts with pivot source.
public void RefreshAll()RefreshPivotTables()
Refreshes all the PivotTables in the Excel file.
public void RefreshPivotTables()RefreshPivotTables(PivotTableRefreshOption)
Refreshes all the PivotTables in the Excel file.
public bool RefreshPivotTables(PivotTableRefreshOption option)Parameters
option PivotTableRefreshOption
The option for refreshing data source of the pivot tables.
Returns
RegisterAddInFunction(string, string, bool)
Adds addin function into the workbook
public int RegisterAddInFunction(string addInFile, string functionName, bool lib)Parameters
addInFile string
the file contains the addin functions
functionName string
the addin function name
lib bool
whether the given addin file is in the directory or sub-directory of Workbook Add-In library. This flag takes effect and makes difference when given addInFile is of relative path: true denotes the path is relative to Add-In library and false denotes the path is relative to this Workbook.
Returns
ID of the data which contains given addin function
RegisterAddInFunction(int, string)
Adds addin function into the workbook
public string RegisterAddInFunction(int id, string functionName)Parameters
id int
ID of the data which contains addin functions, can be got by the first call of Aspose.Cells.WorksheetCollection.RegisterAddInFunction(System.String,System.String,System.Boolean) for the same addin file.
functionName string
the addin function name
Returns
URL of the addin file which contains addin functions
RemoveAt(string)
Removes the element at a specified name.
public void RemoveAt(string name)Parameters
name string
The name of the element to remove.
RemoveAt(int)
Removes the element at a specified index.
public void RemoveAt(int index)Parameters
index int
The index value of the element to remove.
SetOleSize(int, int, int, int)
Sets displayed size when Workbook file is used as an Ole object.
public void SetOleSize(int startRow, int endRow, int startColumn, int endColumn)Parameters
startRow int
Start row index.
endRow int
End row index.
startColumn int
Start column index.
endColumn int
End column index.
Remarks
This method is generally used to adjust display size in ppt file or doc file.
SortNames()
Sorts the defined names.
public void SortNames()Remarks
If you create a large amount of named ranges in the Excel file, please call this method after all named ranges are created and before saving
SwapSheet(int, int)
Swaps the two sheets.
public void SwapSheet(int sheetIndex1, int sheetIndex2)Parameters
sheetIndex1 int
The first worksheet.
sheetIndex2 int
The second worksheet. </worksheet></worksheet></worksheet></worksheet></worksheet></worksheet></worksheet></worksheet></worksheet></worksheet></worksheet></worksheet></worksheet></worksheet></worksheet></worksheet></worksheet></worksheet></worksheet></worksheet></worksheet></worksheet></worksheet></worksheet></worksheet></worksheet></worksheet></worksheet></worksheet></worksheet></worksheet></worksheet></worksheet></worksheet></worksheet></worksheet></worksheet></worksheet></worksheet></worksheet></worksheet></worksheet></worksheet></worksheet></worksheet></worksheet></worksheet></worksheet>