New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

mac: cannot handle sheet names with spaces inside #889

Closed
zogzog opened this Issue Jun 4, 2018 · 6 comments

Comments

Projects
None yet
2 participants
@zogzog

zogzog commented Jun 4, 2018

More precisely, given two sheets:

<Name 'rc_past': =through_time!$E$5:$F$11>
<Name 'rw_digit': ='space bar'!$E$8:$E$17>

when asking .refers_to_range.sheet.name on the second, one gets a nasty

E                   appscript.reference.CommandError: Command failed:
E                   		OSERROR: -1728
E                   		MESSAGE: The object you are trying to access does not exist
E                   		COMMAND: app(pid=75015).workbooks['proto_validator.xlsx'].worksheets["'space bar'"].name.get()
@zogzog

This comment has been minimized.

zogzog commented Jun 4, 2018

The followin diff seems to "fix" it:

@@ -1033,6 +1035,9 @@ class Name(object):
     @property
     def refers_to_range(self):
         ref = self.refers_to[1:].split('!')
+        sheetname = ref[0]
+        if sheetname.startswith("'") and sheetname.endswith("'"):
+            ref[0] = sheetname[1:-1]
         book = self.parent if isinstance(self.parent, Book) else self.parent.book
         return Range(Sheet(book, ref[0]), ref[1])

@fzumstein fzumstein added the bug label Jun 4, 2018

@fzumstein

This comment has been minimized.

Member

fzumstein commented Jun 4, 2018

Can you please include a self contained example? I can't replicated with the following code:

import xlwings as xw

wb = xw.Book()
wb.sheets.add('space bar')
wb.sheets['space bar'].names.add('test1', '=Sheet1!$A$1:$B$3')
print(wb.sheets['space bar'].names['test1'].refers_to_range.sheet.name)

neither with

import xlwings as xw

wb = xw.Book()
wb.sheets.add('space bar')
wb.names.add('test1', '=Sheet1!$A$1:$B$3')
print(wb.names['test1'].refers_to_range.sheet.name)

maybe you're mixing workbook and sheet scope?

@zogzog

This comment has been minimized.

zogzog commented Jun 6, 2018

That was done with a real xl file. I don't understand the "mixing workbook and sheet scope" part.

What's happening is an iteration on the names of a workbook, like that:

for xlname in wb.names:
    sheet_name = xlname.refers_to_range.sheet.name
@fzumstein

This comment has been minimized.

Member

fzumstein commented Jun 16, 2018

Can you please upload a sample spreadsheet with code to replicate the issue. xw.Book() produces a pretty real xl file.

@zogzog

This comment has been minimized.

zogzog commented Sep 11, 2018

book.xlsx

Within this book we have a sheet named "ba bar" and a named cell within.

While iterating on the names, we get to the issue:

b = xlwings.Book('book.xlsx')
for n in b.names: 
    print(n.refers_to_range)

=>

Traceback (most recent call last):
File "/Users/vq5355/miniconda3/lib/python3.6/site-packages/aeosa/appscript/reference.py", line 460, in call
return self.AS_appdata.target().event(self._code, params, atts, codecs=self.AS_appdata).send(timeout, sendflags)
File "/Users/vq5355/miniconda3/lib/python3.6/site-packages/aeosa/aem/aemsend.py", line 92, in send
raise EventError(errornum, errormsg, eventresult)
aem.aemsend.EventError: Command failed: The object you are trying to access does not exist (-1728)

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
File "", line 1, in
File "/Users/vq5355/miniconda3/lib/python3.6/site-packages/xlwings/main.py", line 1590, in repr
return "<Range [{1}]{0}!{2}>".format(self.sheet.name, self.sheet.book.name, self.address)
File "/Users/vq5355/miniconda3/lib/python3.6/site-packages/xlwings/main.py", line 779, in name
return self.impl.name
File "/Users/vq5355/miniconda3/lib/python3.6/site-packages/xlwings/_xlmac.py", line 300, in name
return self.xl.name.get()
File "/Users/vq5355/miniconda3/lib/python3.6/site-packages/aeosa/appscript/reference.py", line 496, in call
raise CommandError(self, (args, kargs), e, self.AS_appdata) from e
appscript.reference.CommandError: Command failed:
OSERROR: -1728
MESSAGE: The object you are trying to access does not exist
COMMAND: app('/Applications/Microsoft Excel.app', newinstance=(0, 2888385)).workbooks['book.xlsx'].worksheets["'ba bar'"].name.get()

@fzumstein

This comment has been minimized.

Member

fzumstein commented Sep 11, 2018

thanks! will look into it

@fzumstein fzumstein added this to the v0.11.9 milestone Sep 17, 2018

@fzumstein fzumstein closed this in c3cb4f5 Sep 17, 2018

fzumstein added a commit that referenced this issue Sep 17, 2018

Merge pull request #936 from ZoomerAnalytics/xlwings-889-fix
fix sheetnames with spaces for refers_to_range, closes #889

fzumstein added a commit that referenced this issue Oct 19, 2018

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment