python file does not exist_解决python - ＂Worksheet range names does not exist＂ KeyError in openpyxl...

1. 例子如下： from math import sqrtif __name__ == '__main__': N = 100 a = range(0,N) for i in range(2,int(sqrt(N))): for j in ran

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:

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

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 worrypython xml处理中文时出现的错误，记录一下，以免忘记   "UnicodeDecodeError: 'ascii' codec can't decode byte 0xe9 in position 0: ordinal not in range(128

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

python

python-2.7

openpyxl

|

this question

edited Feb 29 '16 at 7:55

Martin Evans 13.5k 9 17 34 asked Feb 29 '16 at 6:19

R. Joseph 37 7

|

2 Answers

2

解决方法

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()

|

this answer

edited Feb 29 '16 at 7:56 answered Feb 29 '16 at 7:09

Martin Evans 13.5k 9 17 34      Hi Martin, ws = wb.get_sheet_by_name(sheets[0]) is returning the same error. the code I wrote does the same thing, except I know the name of the sheet, hence it assigns the sheet called range names. It said that the worksheet Sheet1 does not exist –

R. Joseph Feb 29 '16 at 7:22

ws = wb.active is the usual way to work with an existing worksheet, have you tried that? –

Martin Evans Feb 29 '16 at 7:23      Okay that worked! Thank you very much Mr. Evans! –

R. Joseph Feb 29 '16 at 7:27      To anyone reading this answer: ' ws = wb.active' used manually entered data in the sheet, the other method(recommended by

link) returned Nonetypes for manual inputs. –

R. Joseph Feb 29 '16 at 7:30      I advise against using

wb.get_named_ranges() as it is essentially an internal function. –

Charlie Clark Feb 29 '16 at 7:43

|

show more comments

Did you find this question interesting? Try our newsletter

Sign up for our newsletter and get our top new questions delivered to your inbox (see an example).

Subscribed!

Success! Please click the link in the confirmation email to activate your subscription.

I'm not exactly sure what it is you need to do, but to read Excel spreadsheets into python, I usually use xlrd (which to me was easier to get use to). See example:

import xlrd

workbook = xlrd.open_workbook(in_fname)

worksheet = workbook.sheet_by_index(0)

To write to Excel spreadsheets, I use xlsxwriter:

import xlsxwriter

workbook = xlsxwriter.Workbook(out_fname)

worksheet = workbook.add_worksheet('spreadsheet_name')

Hope this helps.

|

this answer answered Feb 29 '16 at 6:29

ljc 163 11      Should I just scrap openpyxl? I've been reading that openpyxl was the best module to use to manipulate xlsx files. Will this read data manually entered into the spreadsheet? –

R. Joseph Feb 29 '16 at 6:32      xlrd and xlsxwriter are both fine libraries but openpyxl is currently the only Python library that lets you edit XLSX files. –

Charlie Clark Feb 29 '16 at 7:37

|