A read out of range bug when using the fast "read_only" feature

Issue #583 invalid
Paul Flint created an issue

Greetings Eric Gazoni, Charlie Clark,

I am using openpyxl version 2.3.2, and I encountered an interesting issue while running openpyxl under ipython.

Here is a snippet of my code:

#!/usr/bin/python
# -*- coding: utf-8 -*-
#
# SOURCE: http://openpyxl.readthedocs.org/en/latest/optimized.html
# reads in xls file line-by-line using regular and fast read only...
import openpyxl
import os.path
import string
from openpyxl import load_workbook
#
# spreadsheet = "example.xlsx"
# use whatever spreadsheet you have
#
def rsheet(cell):
    '''Reads in a sheet'''
    wb = openpyxl.load_workbook(spreadsheet)
    ws =  "".join(wb.get_sheet_names()[0])
    sheet_ranges = wb[ws]
    return (sheet_ranges[cell].value)
#
def rosheet(cell):
    '''Reads in a sheet'''
    # wb = openpyxl.load_workbook(spreadsheet)
    wb = load_workbook(filename=spreadsheet, read_only=True)
    ws =  "".join(wb.get_sheet_names()[0])
    sheet_ranges = wb[ws]
    return (sheet_ranges[cell].value)
##
#
print "This is with regular open: "rsheet("A1")
print "This is with fast open: "rosheet("A1")
print "Now go to outside the range of your sheet and do it again, I picked 1000"
print rsheet("A1000")
print "...as you can see above this gave a 'None' answer"
print rosheet("A1000")
Print "This last thing is the bug..."

Thank you both profusely for your excellent code. If this is actually fixed in 2.4.0 I will get to loading it.

Comments (6)

  1. CharlieC

    Standard mode creates missing cells on demand but this isn't possible in read-only mode so that an exception is the correct behaviour.

  2. Paul Flint reporter

    Dear Charlie,

    I see the sense of this...

    My goal was to try to find the last record row in the sheet.

    Methinks that somehow the fast read_only mode must therefore already know where the last record/row is. Could you point me in a good direction to find this?

    Thanks for your kindness in pointing out the logic here!

    BTW Kevin Cole is on carbon here as he is a mentor to me, and I want to show him I am rubbing elbows with some real geniuses...

    Kindest Regards,

    Paul Flint (802) 479-2360 (802) 595-9365 Cell

    /****** Based upon email reliability concerns, please send an acknowledgment in response to this note.

    Paul Flint Barre Open Systems Institute 17 Averill Street Barre, VT 05641

    http://www.bosivt.org http://family.flint.com/flint skype: flintinfotech Work: (202) 537-0480

    Consilium _ gratuitum .~. ASCII ribbon campaign ( ) valet /V\ against HTML e-mail X quanti /( )\ www.asciiribbon.org / \ numerantur ^^-^^

  3. CharlieC

    Well, you can use ws.max_row and ws.max_col if the information is available. However, it often isn't in which case you will have to loop over all the cells in the worksheet. You can use ws.calculate_dimensions() for this.

    BTW please use the mailing for questions.

  4. Log in to comment