Macros/Basic/Calc/Sheets

    From The Document Foundation Wiki
    Other languages:

    Return to Index

    Sheets

    All sheets

    doc = ThisComponent
    
    For Each sheet In doc.Sheets
        MsgBox sheet.Name
    Next

    All names

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

    Get by name

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

    Get by index

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

    Verify if exists

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

    Count

    sheets = ThisComponent.Sheets
    Msgbox sheets.Count

    Get active sheet

    doc = ThisComponent
    sheet = doc.CurrentController.ActiveSheet

    Set active sheet

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

    Insert by name

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

    Insert like object

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

    Delete

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

    Move

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

    Copy in the same file

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

    Copy from other file

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

    Replace

    CAUTION: Replace sheet NAME with a clean sheet.

    doc = ThisComponent
    sheets = doc.Sheets
    
    sheet = doc.createInstance("com.sun.star.sheet.Spreadsheet")
    sheets.replaceByName("Sheet1", sheet)

    Rename

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

    Visibility

    sheet = sheets.getByName("Sheet1")
    sheet.IsVisible = False
    
    'Or
    
    sheet.IsVisible = True

    Set password

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

    Remove password

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

    Tab color

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