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

Sheet.names.add always on workbook level (Mac) #771

Closed
wkschwartz opened this Issue Nov 1, 2017 · 11 comments

Comments

Projects
None yet
2 participants
@wkschwartz

wkschwartz commented Nov 1, 2017

Overview

The names attribute of a Sheet is always empty on Mac with Excel 2011 and Python 3.6. This happens whether I make a new workbook from scratch or open an existing workbook that already contains named ranges. Adding a named range using the names.add method works from Excel's point of view—the cell takes on the name assigned to it—but not from Python's—names remains empty.

I do not have Windows. Is this reproducible in Windows?

Reproducing the error

The following code makes a workbook in which cell A1 on the first worksheet is the named range "cell". If you run this code, you can check in Excel that it works. However the last line, in which sheet.names evaluates to the empty list, should produce the list [<Name 'cell': =Sheet1!$A$1>].

>>> import xlwings
>>> workbook = xlwings.Book()
>>> sheet = workbook.sheets[0]
>>> sheet.names # Correctly empty
[]
>>> sheet.names.add("cell", "=Sheet1!$A$1")
<Name 'cell': =Sheet1!$A$1>
>>> sheet.names # Should be [<Name 'cell': =Sheet1!$A$1>] instead
[]

Versions

Python 3.6.3 (default, Oct 4 2017, 06:09:15)
xlwings 0.11.4
macOS 10.12.6
Excel for Mac 2011 version 14.2.0 (120402)

@fzumstein

This comment has been minimized.

Member

fzumstein commented Nov 1, 2017

It's mac only (also Excel 2016), thanks for reporting

@fzumstein fzumstein added this to the v0.11.5 milestone Nov 1, 2017

@wkschwartz

This comment has been minimized.

wkschwartz commented Nov 1, 2017

In case this helps with debugging, here's some more output from the same Python session I posted in the original bug report.

>>> sheet.range('cell').value = 17.2
>>> sheet.range('cell').value # Using the name of the range works
17.2
>>> sheet.names.impl
<xlwings._xlmac.Names at 0x10667fb70>
>>> sheet.names.impl.xl
app(pid=85706).workbooks['Sheet95'].worksheets[1].named_items
>>> type(sheet.names.impl.xl)
appscript.reference.Reference
>>> sheet.names.impl.xl.get()
k.missing_value

I don't know anything about appscript or COM, but let me know if there's anything else I can do to help resolve this issue—the names feature might be important for my current project.

@wkschwartz

This comment has been minimized.

wkschwartz commented Nov 1, 2017

I can't find documentation (beyond the source code) on how to run the xlwing tests. I don't have Anaconda, just the standard Python 3.6 installation on macOS (via Homebrew with XCode command line tools—nothing fancy or custom). I cloned the repository, set up a virtual environment called "venv" (python -m venv venv), installed psutils and appscript (pip install psutil appscript), and ran the unit tests for Names the default way:

python -m unittest xlwings.tests.test_names

A new instance of Excel opens at the beginning. A new workbook (or two) pops open every time a test case executes, but there's always an error message that disappears too quickly for me to see it. When the tests are done executing 3.9 seconds later, I have 16 identical errors:

Traceback (most recent call last):
  File "/path/to/xlwings/venv/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 "/path/to/xlwings/venv/lib/python3.6/site-packages/aeosa/aem/aemsend.py", line 92, in send
    raise EventError(errornum, errormsg, eventresult)
aem.aemsend.EventError: Command failed: Apple event handler failed. (-10000)

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

Traceback (most recent call last):
  File "/path/to/xlwings/xlwings/tests/common.py", line 23, in setUp
    self.wb1 = self.app1.books.add()
  File "/path/to/xlwings/xlwings/main.py", line 2717, in add
    return Book(impl=self.impl.add())
  File "/path/to/xlwings/xlwings/_xlmac.py", line 181, in add
    xl = self.app.xl.make(new=kw.workbook)
  File "/path/to/xlwings/venv/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: -10000
		MESSAGE: Apple event handler failed.
		COMMAND: app('/Applications/Microsoft Office 2011/Microsoft Excel.app', newinstance=(0, 21808331)).make(new=k.workbook)
@fzumstein

This comment has been minimized.

Member

fzumstein commented Nov 2, 2017

Needs to be fixed with this:

from appscript import k as kw

workbook.api.named_items.get()
workbook.api.named_items['...'].properties().get(kw.name)
workbook.api.named_items['...'].properties().get(kw.references)
@wkschwartz

This comment has been minimized.

wkschwartz commented Nov 2, 2017

Where do those lines go, or what do they replace?

@fzumstein

This comment has been minimized.

Member

fzumstein commented Nov 3, 2017

it was more a memo on how to implement this here.

@wkschwartz

This comment has been minimized.

wkschwartz commented Nov 3, 2017

Ok, got it. Don't forget here as well.

When you have a minute, would you please explain how to run the tests (re my earlier comment)? If I can get the tests to work, I can try to use your memo to put together a pull request (because then my team can use the names feature while we wait for v0.11.5 comes out).

@wkschwartz

This comment has been minimized.

wkschwartz commented Nov 3, 2017

Also, thank you for being so responsive about this! We switched to XLWings from Data Nitro because XLWings's API is much better. I'm excited it's also actively maintained and hope I can be helpful.

@fzumstein

This comment has been minimized.

Member

fzumstein commented Nov 3, 2017

I use this script to run the tests: https://github.com/ZoomerAnalytics/xlwings/blob/master/runtests.py

It uses nose, however, it should be compatible with plain vanilla unittest.

@wkschwartz

This comment has been minimized.

wkschwartz commented Nov 3, 2017

I finally got the test_names tests to run. I think I need to start the tests with Excel closed rather than already open. Is this a known limitation of the test suite?

Occasionally Excel crashes during the tests and I get the following error log from Excel:

Microsoft Error Reporting log version: 2.0

Error Signature:
Exception: EXC_BAD_ACCESS
Date/Time: 2017-11-03 19:15:53 +0000
Application Name: Microsoft Excel
Application Bundle ID: com.microsoft.Excel
Application Signature: XCEL
Application Version: 14.2.0.120402
Crashed Module Name: libdispatch.dylib
Crashed Module Version: unknown
Crashed Module Offset: 0x00009e45
Blame Module Name: Microsoft Excel
Blame Module Version: 14.2.0.120402
Blame Module Offset: 0x0003ac36
Application LCID: 1033
Extra app info: Reg=en Loc=0x0409
Crashed thread: 0

This does not seem to produce a failed test. Strangely, however, there are only 16 tests in the test_names module, but it says it runs 17 tests:

(venv) ~/Documents/xlwings $ python -m unittest unittest xlwings.tests.test_names
E................
======================================================================
ERROR: unittest (unittest.loader._FailedTest)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/usr/local/Cellar/python3/3.6.3/Frameworks/Python.framework/Versions/3.6/lib/python3.6/fnmatch.py", line 35, in fnmatch
    pat = os.path.normcase(pat)
  File "/usr/local/Cellar/python3/3.6.3/Frameworks/Python.framework/Versions/3.6/lib/python3.6/posixpath.py", line 52, in normcase
    s = os.fspath(s)
TypeError: expected str, bytes or os.PathLike object, not NoneType

----------------------------------------------------------------------
Ran 17 tests in 26.746s

FAILED (errors=1)

For what it's worth, runtests.py requires conda, which I don't have. However, reading the code, it looks like runtests.py just launches notestest for a few combinations of python versions (I only have Python 3.6 anyway). If all nosetest does is run the vanilla unittests under different Python configurations, then I should be able to run the tests under my installed python with python -m unittest. Trying this produced a lot of errors, so I had to abort (Ctrl+C). However, running just the test_names tests worked as above.

@fzumstein fzumstein changed the title from Sheet.names always empty (Mac) to Add Sheet.names always on workbook level (Mac) Jan 7, 2018

@fzumstein fzumstein changed the title from Add Sheet.names always on workbook level (Mac) to Sheet.names.add always on workbook level (Mac) Jan 7, 2018

@fzumstein fzumstein closed this in 2e70963 Jan 7, 2018

fzumstein added a commit that referenced this issue Jan 7, 2018

@fzumstein

This comment has been minimized.

Member

fzumstein commented Jan 7, 2018

in the end it was only that sheet.names.add() was adding the name on workbook level instead of sheet level.

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