如何使用python讀取Excel指定範圍並轉為陣列

2022-11-05 14:00:56

前言

最近需要讀取Excel中的內容，然後進行後續操作，對於這塊知識，博主以前以為自己不會涉及到，但是現在一涉及到，第一步就錯了，搞了好久。真的心累。因此寫了這篇部落格。

目的：

excel中存放著資料，如果要進行計算及其它操作，首先就要進行讀取。

我們先來看一下python中能操作Excel的庫對比（一共九個庫）：

可以發現，還是挺多的

這裡使用的是xlrd庫。

安裝

這裡首先就是匯入這個包，

pip install xlrd==1.2.0

xlrd包版本最好是1.2.0，因為筆者使用2.多版本的xlrd時，程式碼出現了類似下面的報錯，也就是說xlrd版本太高會導致無法支援讀取xlsx字尾的excel。

xlrd.biffh.XLRDError: Excel xlsx file; not supported

大家可以去試試將excel另存，看看儲存型別。

目前筆者使用的是Excel2019版本的，預設儲存型別為xlsx。

如果你之前已經安裝xlrd高版本或更低版本了，建議先解除安裝一下，重新安裝。

pip uninstall xlrd
pip install xlrd==1.2.0

範例

初階

一個excel中有如上資料，我們需要將其提取出來，方便python進行後續操作。

程式碼如下：

def extract1(file,index=0):
    workbook = xlrd.open_workbook(file)
    worksheet = workbook.sheet_by_index(index)
    rows = worksheet.nrows
    all = []
    for i in range(rows):
        a = worksheet.row_values(i)[:]
        all.append(a)
    print(all)
    cc = np.array(all)
    print(cc)
    return cc

file是檔案的路徑及名稱，index就是當前sheet表的索引。下圖就是具體的索引。

當然也可以根據sheet表的名稱。

如下面程式碼第一行按照的是索引方式，第二行按照的是sheet名稱。大家可自行選擇

    worksheet = workbook.sheet_by_index(0)
    worksheet = workbook.sheet_by_name("sheet1")

rows = worksheet.nrows

返回的是sheet表的行數，ncols則是列數

    all = []
    for i in range(rows):
        a = worksheet.row_values(i)[:]
        all.append(a)

首先定義一個空列表，然後遍歷每行，將裡面的資料寫入列表中，

row_values()

是用來返回給定行中單元格值的切片。

最後將其轉換成陣列型別即可。（按要求來，大家也可以不換）

下圖是結果：

換個高階的寫法，用推導式寫（濃縮才是精華）

def extract(file,index=0):
    workbook = xlrd.open_workbook(file)
    worksheet = workbook.sheet_by_index(index)
    rows = worksheet.nrows
    c = tuple(worksheet.row_values(i)[:] for i in range(rows))
    a = np.array(c)
    print(a)
    return a

程式碼行數瞬間縮短了。

最好呼叫一下函數即可

file = r'C:UsersknighthoodOneDrive桌面11.xlsx'
extract1(file)

進階1

要求：假如我excel只要圖中框出來的區域。

以下為了看的較為簡便，我使用推導式的程式碼

def confine_array(file,index=0):
    workbook = xlrd.open_workbook(file)
    worksheet = workbook.sheet_by_index(index)
    rows = worksheet.nrows
    c = tuple(worksheet.row_values(i)[1:] for i in range(1, rows))
    a = np.array(c)
    print(a)
    return a

如上，可以發現，程式碼變化之處就下面這一行。

    c = tuple(worksheet.row_values(i)[1:] for i in range(1, rows))

一步步講解：

①for i in range(1, rows)

首先對於後面的for迴圈，i控制的就是獲取的行，更改其範圍就會更改獲取到的行、行數。

如果是上面說的（1，rows），則對應著獲取第二行到最後一行，（0表示第一行）

②worksheet.row_values(i)[1:]

最後的[1:]（本來的程式碼中是沒有或者是[:]）表示的是i行的元素從第2列（個）獲取到最後一行（個）。

因此我們只需要更改這兩處就可以獲得不同的內容矩陣（如下）。

def flexible_array(file,index=0,row=1,col=1):
    workbook = xlrd.open_workbook(file)
    worksheet = workbook.sheet_by_index(index)
    rows = worksheet.nrows
    c = tuple(worksheet.row_values(i)[col:] for i in range(row,rows))
    a = np.array(c)
    print(a)
    return a

結果如下圖

進階2

要求：能不能更簡化一點，根據我從哪個位置要資料，如第二行第二列開始，將這後面的資料進行讀取。每次這樣對來對去，容易出錯，還是根據行列開始計算比較方便。

這裡為了防止行列一樣，我就多加了一列。

def flexible1_array(file,index=0,row=1,col=1):
    workbook = xlrd.open_workbook(file)
    worksheet = workbook.sheet_by_index(index)
    rows = worksheet.nrows
    c = tuple(worksheet.row_values(i)[col-1:] for i in range(row-1,rows))
    a = np.array(c)
    print(a)
    return a

程式碼也主要變化了這一行

    c = tuple(worksheet.row_values(i)[col-1:] for i in range(row-1,rows))

這裡筆者就不多解釋了。

現在就可以根據需要的起始單元格所在的行列進行選取所要的內容。

進階3

要求：不需要最後一列

這裡的話，筆者就設定了最後需要的行和列作為結束的讀取。

def flexible2_array(file,index=0,row=1,col=1,end_row=None,end_col=None):
    workbook = xlrd.open_workbook(file)
    worksheet = workbook.sheet_by_index(index)
    rows = worksheet.nrows
    if end_row is None:
        c = tuple(worksheet.row_values(i)[col-1:end_col] for i in range(row-1, rows))
    else:
        c = tuple(worksheet.row_values(i)[col - 1:end_col] for i in range(row - 1, end_row))
    a = np.array(c)
    print(a)
    return a

上述程式碼意思是，如果不輸入結束的行和列，讀取到的是包含資料的行列，如果輸入了行和列（或者其中一個），就讀取相應的內容。由於end_row放在range()函數中，因此需要加個if判斷。

結果如下：

此外，我還發現，end_col由於放在[]中，可輸入負數（不懂的可以去看看python列表負索引）。

不過這裡的-1，其實際是排除了最後一列，從你輸入的行列到，你輸入的結束行和倒數第二列。

有些人可能會覺得彆扭（比如我，更喜歡-1表示從起始列到最後一列，-2表示從起始列到倒數第二列）

def flexible3_array(file,index=0,row=1,col=1,end_row=None,end_col=None):
    workbook = xlrd.open_workbook(file)
    worksheet = workbook.sheet_by_index(index)
    rows = worksheet.nrows
    if end_row is None:
        c = tuple(worksheet.row_values(i)[col-1:end_col if end_col > 0 else end_col+1] for i in range(row-1, rows))
    else:
        c = tuple(worksheet.row_values(i)[col-1:end_col if end_col > 0 else end_col+1] for i in range(row - 1, end_row))
    a = np.array(c)
    print(a)
    return a

這裡，程式碼中將判斷end_col是否為負，使用了if-else寫在一行。減少了很多程式碼判斷量，使看起來更簡潔。