Failing to import "xlsx' file...

[Renato Armelin]

Hello, this is my first time with openpyxl. I have an old version, 1.8.5 enabled in a Spider app with a 2.7 python version. I created a 'test.xlsx' excel file with a 3x3 number matrix starting at cell A1, but when I follow the tutorial and try to load it, the Python console apparently cannot find it. The command "load_workbook" is enabled and the excel file, saved in /Desktop, is opened.

The command I type: wb=load_workbook('test.xlsx')

I get the following message: InvalidFileException: [Errno 2] No such file or directory: 'test.xlsx'

Could anyone tell me what is wrong? Do I need to specify where the file is? How can I do it?

Thank you very much

[Charlie Clark]

Am .07.2015, 21:17 Uhr, schrieb Renato Armelin <renato....@gmail.com>:

> Could anyone tell me what is wrong? Do I need to specify where the file
> is?

Yes.

> How can I do it?

Unless the file is in the same folder as you are when you start Python,
sorry I have no idea what Spyder is, you will need to pass the full path
name load_workbook.

The best way to do this is to use the os.path.join function so that you
don't need to worry about the path separator, which is particularly a
problem on Windows.

For example, assuming the file is in your user's documents directory.

import os.path

pth = os.path.join("users", "renato", "temp", "test.xlsx")
wb = load_workbook(pth)

NB 1.8.5 really is very much out of date and you are strongly encouraged
to upgrade to 2.2 or 2.3b

Charlie
--
Charlie Clark
Managing Director
Clark Consulting & Research
German Office
Kronenstr. 27a
Düsseldorf
D- 40217
Tel: +49-211-600-3657
Mobile: +49-178-782-6226

[Renato Armelin]

Thank you very much, Charlie!

Unfortunately, something else went wrong. I am on a macbook, but I believe this should not be the problem. Maybe because I am just starting with python, I still have difficulty in understanding how it works.

After following your tips, I got:

In: wb

Out: <openpyxl.workbook.Workbook at 0x1110deb90>

I don`t know what it means. I tried:

wb['A1']

And I got:

raise KeyError("Worksheet {0} does not exist.".format(key))

KeyError: 'Worksheet A1 does not exist.'

Do you have any idea about what went wrong?

Thanks for your patience! Anyway, the excel test file I am trying to load is attached, just in case you feel like looking at it.

Spider is a Scientific Python Development Environment (a shell, I would say) from Anaconda, a Python package. I will study it deeper to learn how to update it as you suggest.

Thank you very much again!

[Charlie Clark]

Am .07.2015, 23:59 Uhr, schrieb Renato Armelin <renato....@gmail.com>:

> I don`t know what it means. I tried:
> wb['A1']

> And I got:
> raise KeyError("Worksheet {0} does not exist.".format(key))
> KeyError: 'Worksheet A1 does not exist.'

"wb" is a workbook that contains worksheets. "A1" is the address of a cell
of a worksheet. (It's possible to have a worksheet called A1 but I don't
think that's what you mean!).

So, first of all you need to get a worksheet

ws = wb['Sheet1'], for example
wb.sheetnames will give you the names of the worksheets in your workbook.

[Renato Armelin]

Thank you very much Charlie! It worked!

Just a final question: do you know a way, better than the one I used and show below, to transform the loaded data (a simple numeric matrix 3x3) in a matrix ipython can print and therefore show the values in it?

The way I got to do it:

matrix[0]=ws['A1'].value

...

matrix[8]=ws['C3'].value

Thank you very much again!

[Charlie Clark]

Am .07.2015, 21:02 Uhr, schrieb Renato Armelin <renato....@gmail.com>:

> Thank you very much Charlie! It worked!

> Just a final question: do you know a way, better than the one I used and
> show below, to transform the loaded data (a simple numeric matrix 3x3)
> in a
> matrix ipython can print and therefore show the values in it?

I think you want to look at using Pandas to do this.

[Renato Armelin]

Thank you Charlie, I will!

[Renato Armelin]

Hello again Charlie,

Sorry to come back to this point, but I got stuck again while trying to repeat the loading workbook routine you taught me before. The problem seems to be the path, although I used the same path as before and the file is in the same place! Indeed, the path is the same as shown up in the IDE as working directory and is the same where I save my codes.

Could you take a look at the code below to see wether you spot a command or syntax mistake?

import openpyxl

from openpyxl import load_workbook

import os.path

pth=os.path.join("Users","MBPRSA13","Desktop","test.xlsx")

wb=load_workbook(pth)

Message I got: [Errno 2] No such file or directory 'Users/MBPRSA13/Desktop/test.xlsx'

Thanks once more!

Em quinta-feira, 16 de julho de 2015 16:27:34 UTC-3, Charlie Clark escreveu:

[Charlie Clark]

Am .07.2015, 14:32 Uhr, schrieb Renato Armelin <renato....@gmail.com>:

> import openpyxl
> from openpyxl import load_workbook
> import os.path
> pth=os.path.join("Users","MBPRSA13","Desktop","test.xlsx")
> wb=load_workbook(pth)
> Message I got: [Errno 2] No such file or directory
> 'Users/MBPRSA13/Desktop/test.xlsx'

That looks like a relative path.

https://docs.python.org/2.7/library/os.path.html#os.path.join

Looks like you should use ("/Users", …)

[Renato Armelin]

Thank you again! It worked! I will not forget that...