"Worksheet range names does not exist" KeyError in openpyxl

Question

Let me preface this by saying I have tried looking for, and cannot seem to find a similar situation so please don't be too upset if this seems familiar to you. I am using Python 2.7 and openpyxl version 2.2.5 (I need to use 2.7, and used an older module for other reasons.)

I am new to Python and read/write code in general, so I'm testing this on the command line before I implement it:

1. I created a file, foo.xlsx in the Python27 file directory with some values that I manually entered via Excel.

2. I then used this simple code on the Python command line to test my code

   from openpyxl import load_workbook
   wb = load_workbook('foo.xlsx')
   sheet_ranges = wb['range names']
   

It then resulted in the following error:

File "C:\Python27\lib\openpyxl\workbook.workbook.py", line 233 in getitem raise KeyError("Worksheet {0} does not exist.".format(key))
KeyError: 'Worksheet sheet range names does not exist'

So I thought it had something to do with not importing the entire openpyxl module. I proceeded to do that and run the whole process but it resulted in the same error.

Can someone please let me know what I am doing wrong/how to solve this?

Additional information:

• I had successfully written to an empty file before, and then read the values. This gave me the right values for everything EXCEPT what I had written in manually via Excel- the cells that had manual input returned None or Nonetype. The issue seems to be with cells with manual input.

• I did hit save on the file before accessing it don't worry

This was in the same directory so I know that it wasn't a matter of location.

Answer 1

The following command does not make sense:

sheet_ranges = wb['range names']

Normally you open a workbook and then access one of the worksheets, the following gives you some examples on how this can be done:

import openpyxl 

wb = openpyxl.Workbook()
wb = openpyxl.load_workbook(filename = 'input.xlsx')

# To display all of the available worksheet names
sheets = wb.sheetnames
print sheets

# To work with the first sheet (by name)
ws = wb[sheets[0]]
print ws['A1'].value

# To work with the active sheet
ws = wb.active
print ws['A1'].value

# To work with the active sheet (alternative method)
ws = wb.get_active_sheet()
print ws['A1'].value

If you want to display any named range in the workbook, you can do the following:

print wb.get_named_ranges()