Sorting thru a cell collection in openpyxl

Question

I am reading in an xlsx file, like so,

import openpyxl
book = openpyxl.load_workbook('test.xlsx')
sheet = book.get_sheet_by_name(sheets['DATA'])
allCells = sheet.get_cell_collection()
print allCells

allCells is an unordered list of openpyxl.cell.Cell objects. allCells looks like,

[<Cell Data.B4>, <Cell Data.A6>, <Cell Data.B6>, <Cell Data.A1>, <Cell Data.B5>, <Cell Data.A3>, <Cell Data.A2>, <Cell Data.A5>, <Cell Data.B1>, <Cell Data.B2>]

I want to sort this list by column and then by row indices. Any ideas on how to do this?

Use COM and forget openpyxl, that's what I've done. http://pythonexcels.com/python-excel-mini-cookbook/ — Aleksander Lidtke, Sep 04 '13 at 20:28
What is an unordered list in Python? Are you referring to a `set` object? Or just that `allCells` *is* actually `list`, it just isn't guaranteed to be in any particular order? — Phillip Cloud, Sep 04 '13 at 20:31
It is not guaranteed to be of particular order as returned by openpyxl. — Aleksander Lidtke, Sep 04 '13 at 20:32

score 1 · Accepted Answer · edited May 23 '17 at 10:25

You can get row and column information for every cell in the list like so:

import openpyxl
wb = openpyxl.workbook.Workbook()
ws = wb.worksheets[0]
a=ws.cell('D6')
a.row
6
a.column
'D'

Now it's just a matter to sort the lot according to any criteria that you desire. Here's a list of different algorithms to be used for sorting. And this post describes how to sort a list according to objects' attributes. What is the problem? I'll edit the answer accordingly.

Sorting thru a cell collection in openpyxl

1 Answers1