Make worksheet numpy-style subscriptable

Issue #135 on hold
Jeffrey Tratner
created an issue

I'm interested in extending the Worksheet class to be subscriptable in a similar manner to numpy arrays. (using tuple syntax)

Examples:

  • sheet1[10,15] --> returns (the value or cell object) for cell(col=10, row=15)
  • sheet1[:,15] --> generator/iterator for row 15 (or possibly a slice)
  • sheet1[10,:] --> generator/iterator for col 10 (or possibly a slice)
  • sheet1[-1,:] --> generator/iterator for last column
  • sheet1[:,-1] --> generator/iterator for last row
  • sheet1[5:10, 15] --> returns generator/iterator for cells (5,15),(6,15),(7,15),(8,15),(9,15) [if 5:10 exists] -- same as slice notation

I don't think this would be that difficult to implement, just using the same hooks, exceptions, etc as the cell(col=, row=) function and a generator function for the iterators.

I'm volunteering to do it and debug it. Just would like to know in advance that the main devs would be interested in such an addition (and that this hasn't come up and been rejected previously)

Comments (5)

  1. Eric Gazoni

    Hey Jeffrey ! Good idea, might always be helpful to people who deal with lots of data :-) If you're still willing to do it, don't forget to include a fair share of unit tests please ;-) Thank you very much in advance !

  2. CharlieC

    I've added some convenience methods for Worbooks and Worksheets to the 1.8 branch:

    wb['Sheet'] # get a sheet by name
    for sheet in wb: pass # workbooks are now iterable
    del wb['Sheet']
    
    ws["A2"] # return ws.cell("A2")
    ws["A2"] = 4 # assign value directly
    ws["A1":"Z10"] # slicing returns a range
    

    I find the spelling here more usual than the Numpy array and more typical for imperative code where I'd expect to see it. But it might be possible have both though it would be quite a bit more leg work to have the iterators you'd like.

    I'm closing the issue but would welcome a pull request.

  3. Jeffrey Tratner reporter

    Coming back to this 1.5 years later, I don't really have the time to devote to do this, as I don't (in general) interact with openpyxl directly anymore. If someone else is interested in picking this up good for them.

    Not totally clear why it's more difficult to support slicing with numbers vs. ranges, given that ranges are (IIRC) converted to numbers under the hood.

  4. CharlieC

    Accessing by coordinate tuple is, of course, no more difficult than by cell key. Not sure if I'd want the same syntax - making sheets callable would be closer to tuple notation.
    sheet(1, 15) would return the same as sheet.cell(1, 15)
    With more work I'm referring to the iterator stuff as that doesn't exist under the hood.

    As for time, who has any? ;-)

  5. Log in to comment