openpyxl: a better way to read a range of numbers to an array
<p>I am looking for a better (more readable / less hacked together) way of reading a range of cells using <code>openpyxl</code>. What I have at the moment works, but involves composing the excel cell range (e.g. <code>A1:C3</code>) by assembling bits of the string, which feels a bit rough.</p>

<p>At the moment this is how I read <code>nCols</code> columns and <code>nRows</code> rows starting from a particular cell (minimum working example, assumes that <code>worksheet.xlsx</code> is in working directory, and has the cell references written in cells <code>A1</code> to <code>C3</code> in <code>Sheet1</code>:</p>

<pre><code>from openpyxl import load_workbook
import numpy as np

firstCol = "B"
firstRow = 2

nCols = 2
nRows = 2

lastCol = chr(ord(firstCol) + nCols - 1) 

cellRange = firstCol + str(firstRow) + ":" + lastCol + str(firstRow + nRows - 1)

wsName = "Sheet1"
wb = load_workbook(filename="worksheet.xlsx", data_only=True)
data = np.array([[i.value for i in j] for j in wb[wsName][cellRange]])
print(data)
</code></pre>

<p>Returns:</p>

<pre><code>[[u'B2' u'C2']
 [u'B3' u'C3']]
</code></pre>

<p>As well as being a bit ugly there are functional limitations with this approach. For example in sheets with more than 26 columns it will fail for columns like <code>AA</code>.</p>

<p>Is there a better/correct way to read <code>nRows</code> and <code>nCols</code> from a given top-left corner using openpyxl?</p>

<p>openpyxl provides functions for converting between numerical column indices (1-based index) and Excel's 'AA' style. See the <code>utils</code> module for details.</p>

<p>However, you'll have little need for them in general. You can use the <code>get_squared_range()</code> method of worksheets for programmatic access. And, starting with openpyxl 2.4, you can do the same with the <code>iter_rows()</code> and <code>iter_cols()</code> methods. NB. <code>iter_cols()</code> is not available in read-only mode.</p>

<p>The equivalent MWE using <code>iter_rows()</code> would be:</p>

<pre><code>from openpyxl import load_workbook
import numpy as np
wsName = "Sheet1"
wb = load_workbook(filename="worksheet.xlsx", data_only=True)
ws = wb[wsName]

firstRow = 2
firstCol = 2
nCols = 2
nRows = 2

allCells = np.array([[cell.value for cell in row] for row in ws.iter_rows()])

# allCells is zero-indexed
data = allCells[(firstRow-1):(firstRow-1+nRows),(firstCol-1):(firstCol-1+nCols)]
print(data)
</code></pre>

<p>The equivalent MWE using <code>get_squared_range()</code> would be:</p>

<pre><code>from openpyxl import load_workbook
import numpy as np

wsName = "Sheet1"
wb = load_workbook(filename="worksheet.xlsx", data_only=True)

firstCol = 2
firstRow = 2
nCols = 2
nRows = 2

data = np.array([[i.value for i in j] for j in wb[wsName].get_squared_range(
            firstCol, firstRow, firstCol+nCols-1, firstRow+nRows-1)])
print(data)
</code></pre>

<p>Both of which return:</p>

<pre><code>[[u'B2' u'C2']
 [u'B3' u'C3']]
</code></pre>

<p>See also <a href="https://openpyxl.readthedocs.io/en/default/pandas.html" rel="nofollow">https://openpyxl.readthedocs.io/en/default/pandas.html</a> for more information on using Pandas and openpyxl together.</p>

python numpy openpyxl