Jump to content

Python Guide - 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