Macros/Python Guide/Calc/Calc sheets

    From The Document Foundation Wiki


    ⇐ Return to Index


    Sheets

    In this Python Guide, we use the following convention:

    • sheet is a single spreadsheet within a document
    • workbook is a Calc document


    CAUTION:

    Copy or import function msgbox from Useful functions

    Names

    • Get all sheet names

    doc = XSCRIPTCONTEXT.getDocument()
        names = doc.Sheets.ElementNames
        msgbox(names)


    Count all sheets in a workbook

    doc = XSCRIPTCONTEXT.getDocument()
        count = doc.Sheets.Count
        msgbox(count)


    Iterate over all sheets

    doc = XSCRIPTCONTEXT.getDocument()
        for sheet in doc.Sheets:
            msgbox(sheet.Name)


    Get sheets

    • Get active sheet from current document.

    doc = XSCRIPTCONTEXT.getDocument()
        sheet = doc.getCurrentController().getActiveSheet()
        msgbox(sheet.Name)


    • Get active sheet by index from current document

    sheet = doc.Sheets[0]
        msgbox(sheet.Name)


    • Get active sheet by name from current document

    sheet = doc.Sheets['data']
        msgbox(sheet.Name)


    Activate

    Send focus to any sheet

    doc = XSCRIPTCONTEXT.getDocument()
        sheet = doc.Sheets[0]
        doc.getCurrentController().setActiveSheet(sheet)


    Change name

    • Change name visible to user

    sheet = doc.Sheets[0]
        msgbox(sheet.Name)
        sheet.Name = 'OtherName'
        msgbox(sheet.Name)


    • Change name visible only to code

    sheet = doc.Sheets[0]
        msgbox(sheet.CodeName)
        sheet.CodeName = 'Data'
        msgbox(sheet.CodeName)


    Set sheet visibility

    • Set property IsVisible to False to hide a sheet; and set it to True to show it.

    sheet = doc.Sheets[0]
        msgbox(sheet.IsVisible)
        sheet.IsVisible = False
        msgbox(sheet.IsVisible)
        sheet.IsVisible = True


    Password management

    • Set password

    sheet = doc.Sheets[0]
        contra = 'letmein'
        sheet.protect(contra)


    • Remove password

    sheet = doc.Sheets[0]
        contra = 'letmein'
        sheet.unprotect(contra)


    Check if sheet exists in workbook

    doc = XSCRIPTCONTEXT.getDocument()
        exists = 'data' in doc.Sheets
        msgbox(exists)


    Insert a blank new sheet

    insertNewByName(NAME, POSITION)

    doc = XSCRIPTCONTEXT.getDocument()
        doc.Sheets.insertNewByName('new', 0)


    Insert an existing sheet into workbook

    insertByName(SHEET, POSITION)

    doc = XSCRIPTCONTEXT.getDocument()
        sheet = doc.createInstance('com.sun.star.sheet.Spreadsheet')
        doc.Sheets.insertByName(sheet, 0)


    Move position of a sheet within workbook

    moveByName(NAME, NEW_POSITION)

    doc = XSCRIPTCONTEXT.getDocument()
        doc.Sheets.moveByName('new', 3)


    Remove sheet from workbook

    removeByName(NAME)

    doc = XSCRIPTCONTEXT.getDocument()
        doc.Sheets.removeByName('new')


    Replace with a blank sheet

    replaceByName(NAME, SHEET)

    CAUTION: Use wisely. This function replaces a sheet NAME with a clean sheet.

    doc = XSCRIPTCONTEXT.getDocument()
        sheet = doc.createInstance('com.sun.star.sheet.Spreadsheet')
        doc.Sheets.replaceByName('data', sheet)


    Copy sheet

    • Rename a sheet

    copyByName(SOURCE_NAME, NEW_NAME, POSITION)

    CAUTION: If new name exists, it will copy the sheet with name SOURCE_NAME_2, and it will not get any error.

    • Copy into the same document

    doc = XSCRIPTCONTEXT.getDocument()
        doc.Sheets.copyByName('data', 'NewData', 10)


    • Copy (or import) from another document

    importSheet(SOURCE, SOURCE_NAME, POSITION)

    source = XSCRIPTCONTEXT.getDocument()
    
        desktop = app.create_instance('com.sun.star.frame.Desktop', True)
        path = 'private:factory/scalc'
        doc = desktop.loadComponentFromURL(path, '_default', 0, ())
    
        doc.Sheets.importSheet(source, 'Services', 0)


    Colorize a tab sheet

    CAUTION

    Copy or import the function get_color from Useful functions

    sheet = doc.Sheets['Sheet1']
    sheet.TabColor = get_color(255, 0, 0)


    ⇐ Return to Index