Accessing Sheets

The primary purpose of a spreadsheet document is to act as a container for individual sheets through the XSpreadhseetDocument interface. The XSpreadsheetDocument interface defines the single method getSheets() that returns a Spreadsheets object used to manipulate the individual sheets (see Listing 2 ).

Listing 2: Obtain a com.sun.star.sheet.Spreadsheets service using a method or a property.

 ThisComponent.getSheets()  'Method defined by XSpreadsheetDocument interface. ThisComponent.Sheets       'Property of the spreadsheet document. 

 

The Spreadsheet service allows the individual sheets to be returned by index, by enumeration, and by name (see Table 2 ). The Spreadsheets service also allows sheets to be created, moved, and deleted. Many of the methods shown in Table 2 are demonstrated in Listing 3 .

Listing 3: AccessSheets is found in the Calc module in this chapter's source code files as SC14.sxc.

 Sub AccessSheets   Dim oSheets       'The sheets object that contains all of the sheets   Dim oSheet        'Individual sheet   Dim oSheetEnum    'For accessing by enumeration   Dim s As String   'String variable to hold temporary data   Dim i As Integer  'Index variable   oSheets = ThisComponent.Sheets   REM Verify that the sheet named "Sheet2" exists   If NOT oSheets.hasbyName("Sheet2") Then     REM Sheet2 does not exist so insert it as the second sheet.     insertNewByName("Sheet2", 1)   End If   REM Create a new sheet named "First" at the start   oSheets.insertNewByName("First", 0)   REM Copy "Sheet1" to the end. That is copy, not move!   oSheets.copyByName("Sheet1", "Copyl", oSheets.getCount())   REM The sheets are indexed starting at zero, but getCount() indicates   REM exactly how many sheets there are.   For i = 0 To oSheets.getCount()-1     s = s & "Sheet " & i & " = " & oSheets.getByIndex(i).Name & CHR$(10)   Next   Msgbox s, 0, "After Inserting New Sheets"   REM Now remove the new sheets that I inserted   oSheets.removeByName("First")   oSheets.removeByName("Copy1")   s = "" : i = 0   oSheetEnum = oSheets.createEnumeration()   Do While oSheetEnum.hasMoreElements()     oSheet = oSheetEnum.nextElement()     s = s & "Sheet " & i & " = " & oSheet.Name & CHR$(10)     i = i + 1   Loop   Msgbox s, 0, "After Deleting The New Sheets" End Sub 

 

Table 2: Methods implemented by the com.sun.star.sheet.Spreadsheets service.

Method	Description
copyByName( srcName, destName, index )	Copy the sheet named srcNam to the specified index and name it destName.
createEnumeration()	Create an object that enumerates the spreadsheets.
getByIndex(index)	Obtain a spreadsheet based on the sheet's index.
getByName(name)	Obtain a spreadsheet based on the sheet's name.
getCount()	Return the number of sheets as a Long Integer.
hasByName(name)	Return True if the named spreadsheet exits.
hasElements()	Return True if the document contains at least one spreadsheet.
insertNewByName(name, index)	Create a new spreadsheet and insert it at the specified location with the supplied name.
moveByName(name, index)	Move the named spreadsheet to the specified index.
removeByName(name)	Remove the named spreadsheet.