In the Microsoft office the sheet can be referenced by index, by name or by codename.
And in libreoffice would it be possible to reference a sheet by its codename?
CodeName = ‘Sheet1’, something like a:
doc.Sheets.getByCodeName(‘Sheet1’)
or
doc.Sheet1
you use ‘single-quotes’ so we are in python!
doc = XSCRIPTCONTEXT.getDocument()
sheets = doc.Sheets
maybe_code_name = 'Tabelle1'
if maybe_code_name in sheets.ElementNames:
sheet = sheets[maybe_code_name]
if sheet.CodeName == maybe_code_name:
print(f"found Sheet: '{maybe_code_name}'")
else:
for sheet in sheets:
if sheet.CodeName == maybe_code_name:
print(f"""sheet: "{sheet.Name}" has CodeName: '{maybe_code_name}' """)
Unfortunately LibreOffice doesn’t have a native solution for this.
But simplifying the @karolus solution, that’s what I need. Thanks
def main():
doc = XSCRIPTCONTEXT.getDocument()
sheets = doc.Sheets
first_code_name = 'Sheet1'
second_code_name = 'Sheet2'
for sheet in sheets:
if sheet.CodeName == first_code_name:
my_first_sheet = sheet
if sheet.CodeName == second_code_name:
my_second_sheet = sheet
my_first_sheet['A1'].String = 'Test CodeName'
my_second_sheet['B5'].Value = 100
return
But I just realized that if I change the Libreoffice interface language, it will break the code. Because the CodeName changes according to the language
Not only that, any user can change this “default” at any time via: →→Tools→→Options→→LO-Calc→Defaults
After some sophisticated things I come back to simple “dictionary on the fly”
def test():
doc = XSCRIPTCONTEXT.getDocument()
sheets = doc.Sheets
get_by_code = dict((sheet.CodeName, sheet) for sheet in sheets)
sheet = get_by_code['Tabelle1']
print(sheet.Name)
print(sheets.getByName(sheet.Name).AbsoluteName)
test()
CodeName is an Invisible Man property: it is not described in the documentation and even the omnipotent MRI does not see its value. 
However, this property can be changed to be independent of Options:
ThisComponent.Sheets(0).CodeName="MyCodeName"
This approach can make sense if the user is allowed to rename the sheets and change their order.
Sub SetSheetCodeNamesAsYouLike()
''' Run this procedure.
Dim oSheet As Object
Dim s$
For Each oSheet In ThisComponent.Sheets
s = InputBox("Enter a codename for the sheet """& oSheet.Name & """:" _
, "Changing sheet codenames", oSheet.CodeName)
If Len(s) > 0 Then
If oSheet.CodeName <> s Then oSheet.CodeName = s
Else: Exit For
End If
Next
End Sub
Xray sees everything the same way.
Half-truth.
Codename
doc = ThisComponent
sheet = doc.CurrentController.ActiveSheet
MsgBox sheet.CodeNameUnfortunately this code only displays the CodeName of the active sheet. Does not reference the sheet by CodeName.
With power Python
class Sheets():
def __init__(self, doc):
self._doc = doc
def __getitem__(self, index):
for sheet in self._doc.Sheets:
if sheet.CodeName == index:
return sheet
def main():
doc = XSCRIPTCONTEXT.getDocument()
sheets = Sheets(doc)
sheet = sheets['CodeName']
if not sheet is None:
print(sheet.CodeName)
returnIf we design Claases they should proper designed:
from com.sun.star.sheet import XSpreadsheets
from unohelper import Base
class Sheets( XSpreadsheets, Base):
def __init__(self, sheets):
XSpreadsheets.__init__(sheets)
self.sheets = sheets
## ↓↓↓ copy NameSpace from 'sheets' into class.Namespace
## !ugly … is there a better way ??
for entry in dir(sheets):
setattr(self, entry, getattr(sheets, entry))
def getByCodeName(self, code_name):
for sheet in self.sheets:
if sheet.CodeName == code_name:
return sheet
raise NameError(f"Sheet with CodeName: {code_name} dont exist")
def test():
doc = XSCRIPTCONTEXT.getDocument()
sheets = Sheets(doc.Sheets)
sheet = sheets.getByCodeName('Tabelle1')
print(sheet.Name)
print(sheets.getByName(sheet.Name).AbsoluteName)
Much Ado About Nothing
Function GetSheetByCodeName(sCodeName$, Optional Document) As Object
If IsMissing(Document) Then Document = ThisComponent
Dim oSheet As Object
For Each oSheet In Document.Sheets
If oSheet.CodeName = sCodeName Then
GetSheetByCodeName = oSheet
Exit For
End If
Next
End Function
oSheet = ThisComponent.Sheets.getByName("Sheet 1")
oSheet = GetSheetByCodeName("Sheet1")