Macros/Basic/Calc/Sheets

From The Document Foundation Wiki
Jump to navigation Jump to search
Other languages:
English • ‎español • ‎italiano

Return to Index

Sheets

All sheets

1doc = ThisComponent
2
3For Each sheet In doc.Sheets
4    MsgBox sheet.Name
5Next

All names

1sheets = ThisComponent.Sheets
2MsgBox Join(sheets.ElementNames, Chr(13))

Get by name

1sheets = ThisComponent.Sheets
2sheet = sheets.getByName("Sheet1")

Get by index

1sheets = ThisComponent.Sheets
2sheet = sheets.getByIndex(1)

Verify if exists

1sheets = ThisComponent.Sheets
2exists = sheets.hasByName("Sheet1")

Count

1sheets = ThisComponent.Sheets
2Msgbox sheets.Count

Get active sheet

1doc = ThisComponent
2sheet = doc.CurrentController.ActiveSheet

Set active sheet

1doc = ThisComponent
2sheet = doc.Sheets.getByIndex(1)
3doc.CurrentController.setActiveSheet(sheet)

Insert by name

1sheets = ThisComponent.Sheets
2position = 0
3sheets.insertNewByName("New_Sheet", position)

Insert like object

1doc = ThisComponent
2sheet = doc.createInstance("com.sun.star.sheet.Spreadsheet")
3sheets.insertByName("New_Sheet_Obj", sheet)

Delete

1sheets = ThisComponent.Sheets
2sheets.removeByName("Sheet1")

Move

1sheets = ThisComponent.Sheets
2position = 0
3sheets.moveByName("Sheet1", position)

Copy in the same file

1sheets = ThisComponent.Sheets
2position = 0
3sheets.copyByName("Sheet1", "Copy_Sheet", position)

Copy from other file

1sheets = ThisComponent.Sheets
2path = ConvertToUrl("/home/USER/Source_File.ods")
3doc_source = StarDesktop.loadComponentFromURL(path, "default", 0, Array())
4position = 0
5
6sheets.importSheet(doc_source, "Sheet1", position)

Replace

CAUTION: Replace sheet NAME with a clean sheet.

1doc = ThisComponent
2sheets = doc.Sheets
3
4sheet = doc.createInstance("com.sun.star.sheet.Spreadsheet")
5sheets.replaceByName("Sheet1", sheet)

Rename

1sheet = sheets.getByName("Sheet1")
2sheet.Name = "New_Name"

Visibility

1sheet = sheets.getByName("Sheet1")
2sheet.IsVisible = False
3
4'Or
5
6sheet.IsVisible = True

Set password

1sheet = sheets.getByName("Sheet1")
2sheet.Protect("letmein")

Remove password

1sheet = sheets.getByName("Sheet1")
2sheet.unProtect("letmein")

Tab color

1sheet = sheets.getByName("New_Name")
2sheet.TabColor = RGB(255, 0, 0)