一、Python處理Excel

• Python處理Excel的好處

1.批量操作：當要處理眾多Excel文件時，例如出現重復性的手工勞動，那么使用Python就可以實現批量掃描文件、自動化進行處理，利用代碼代替手工重復勞動，實現自動化，是Python第一個比Excel強大的地方

2.大型文件，當Excel文件超過幾十兆、甚至上百兆時，打開文件很慢、處理文件更加慢，這時候若使用Python，會發(fā)現處理幾十兆、幾百兆甚至幾GB都是沒有問題的

3.當使用Excel進行復雜的計算時，會使用VBA，但是VBA本身是過時并且復雜的語言，Python是當前最簡單且容易實現的一門語言，用Python能夠處理比VBA難度更高的業(yè)務邏輯

4.Python是通用語言，不僅可以處理Excel，使用Python就可以得到很多額外的功能，例如：爬蟲、發(fā)布網頁的Web服務、與數據庫進行連接、同時結合word和PPT進行處理、加入定時任務處理、人工智能分析等，各種額外的功能，這是Excel和VBA所不具備的

• Python處理Excel主要有三大類庫

1.pandas：是Python領域非常重要的，用于數據分析和可視化的類庫，在處理Excel中，90%可以利用pandas類庫就可以搞掂，利用pandas就可以讀取Excel、處理Excel和輸出Excel，但是pandas也有缺點，就是無法做到格式類，例如Excel中合并單元、大量復雜的樣式（看起來很精美）的時候，用pandas無法搞掂，此時，依然是使用pandas結合openyxl、xlwings來搞掂需求

2.openpyxl：若電腦上未安裝office時，也可以使用openpyxl，這個類型可以運行在linux上，并且也可以實現操作大部分Excel格式和樣式的功能，使用它配合pandas，也可以完成大部分場景的需求

3.xlwings：比openyxl更加強大，只能運行在Windows或者Mac系統，并且該系統中必須安裝了office才能運行，xlwings的原理，就是基于當前系統已經安裝好的office軟件，來進行功能的拓展來操作Excel

• 使用pandas的時候，經常會結合其他類庫，來完成更加復雜的功能

  • requests, bs4：可以完成爬蟲的功能

  • flask：可以做網頁，把表格展示在網頁上

  • Matplotlib：讀取表格后，進行可視化

  • sklearn：進行復雜的數據分析時，也可以結合機器學習Sklearn把讀取的Excel數據，進行數據分析和機器學習

  • Python-docx：也可以結合Python-docx類庫，實現Excel和word的互通

  • smtplib：也可以使用smtplib，講Excel數據發(fā)送郵件出去

• 開發(fā)環(huán)境

操作系統：使用windows, mac都可以

Python版本：系統中需要安裝Python3.6以上的版本，Python2已經過期不建議使用，Python3.6以前的版本功能相對弱，最好就是采用Python3.6以上的版本

開發(fā)工具：有兩個可以選擇，jupyter notebook，是個網頁編輯器，可以運行Python，常常用于交互性、探索性的開發(fā)；pycharm，用于成熟腳本，或者web服務的一些開發(fā)；這兩個工具可以隨意選擇。

二、用Python在Excel中查找并替換數據

技術工具：

Python版本:3.9

代碼編輯器：jupyter notebook

隨著項目的進展，需要經常在Excel業(yè)務表格中查找及替換數據，已保證數據與實際項目進度一致。手動一個一個查找，然后替換，效率太低，還容易遺漏?，F在我們來試試用Python自動完成查找及替換吧。具體要求如下。

首先，我們先將左邊表格中的數據提取，并存入字典data，其鍵為“查找內容”中的數據，值為“替換內容”中的數據。 

from openpyxl import load_workbook #用于讀取Excel中的信息
#獲取Excel表格中的數據
wb = load_workbook('查找替換.xlsx')#讀取工作簿
ws = wb.active #讀取活動工作表
data={} #新建字典，用于儲存數據
 
for row in range(2,ws.max_row+1):
    chazhao = str(ws['A' + str(row)].value)  #轉換成字符串，以免后續(xù)比對時出現數據類型沖突
    tihuan = str(ws['B' + str(row)].value) #轉換成字符串，以免后續(xù)比對時出現數據類型沖突
    data[chazhao]=tihuan #鍵值對應存入字典

data

然后，讀取目標表格，將D列中的所有數據提取出來，以便后續(xù)比對及替換。通過`for`循環(huán)遍歷“原表”，將D列每個單元格的值提取并存入`ID_list`。然后通過切片`ID_list[:10]`查看前10個數據是否OK。結果顯示相當正常。

wb = load_workbook('原表.xlsx') #讀取目標工作簿
ws = wb.active
ID_list = [] #新建一個列表，用于儲存原表D列的信息
for row in range(2,ws.max_row+1):
    ID = ws['D' + str(row)].value #遍歷整個工作表，將D列的數據逐個存入ID變量
    ID_list.append(ID) #將讀取到的結果存入列表
ID_list[:10] #查看列表中前10個數據

type("")

為了比對數據，我們需要將`'說明碼：77601'`中的“說明碼：”字符拿掉，只保留“77601”。于是調用`split`函數來進行分割，并將分割好的數字部分存入新建的列表`code`。不好，居然報錯了，說`ID_list`列表中有"None"（空）類型的數據，而"None"類型的數據是不能使用`split`函數的。目測了一下，`ID_list`列表中除了`'說明碼：77601'`和`''`這樣的空字符串，沒看到None啊。再回來“原表”偵察一下，發(fā)現最下面還有一些單元格很有嫌疑。原來是表尾有一些“供應商”和倉位信息，這些信息所在位置對應的D列都是空單元格，其值為"None"。用`ID_list[-10:]`查看最后10個元素，果然都是"None"。

code = [i.split("：")[-1] for i in ID_list]
code

ID_list[-10:]

這樣，我們就知道`ID_list`中有三種數據，即含內容的字符串（比如'說明碼：77601'），空字符串（比如''）和空值None。因此，需要修改一下字符串分割代碼如下。加入了`if`判斷語句，如果`ID_list`中的值是None，則放入None占位，以保持列表的值的順序與原表一致；值不是None，則按"："符號分割，并放分割后的最后一個值`[-1]`進入新列表code?？兆址谶@里也要經過`split`分割，但其中沒有“：”符號，所以就分割不了，只得直接跳過，最后放入新列表的還是空字符串。 

code = []
for i in ID_list:
    if i == None:# 如果是None，則放入None占位，以保持列表的值的順序與原表一致
        code.append(None)
    else:
        code.append(i.split("：")[-1]) #不是None,則按"："符號分割，并放分割后的最后一個值進入新列表code
code[:10]

處理完數據后，即可開始查找并替換目標數據了。用`for`循環(huán)遍歷列表`code`，即原表D列中的數字部分。如果其中的值也存在于data的鍵中，即語句`if code[i] in data`，則將原表中D列(`column=4`)對應的行中的數據改寫成新的值。新的值由兩部分組成，一部分是“說明碼：”這樣的，即`ID_list[i].split("：")[0]`，另一部分則是要替換的數字，即`data[code[i]]`。這樣保證只替換了需要替換的數字部分，而保留中文和冒號部分。最后保存為新的文件，替換完成。 

for i in range(len(code)):
    if code[i] in data:
        ws.cell(row=i+2,column=4).value = ID_list[i].split("：")[0] +"："+ data[code[i]]
wb.save('原表-替換.xlsx') 

如果以上不能通過觀察原表，發(fā)現空值問題，還可以用`enumerate`函數給列表里的所有元素加上索引，以便精確定位`ID_list`中的空值。加上索引后，在轉換成列表，并存入新的列表`ID_list_idx`中。觀察其中前10個數據，可見索引已加好了。然后遍歷新列表，判斷其中的值是否為空值，若是則打印其對應的索引編號，這樣就能精準定位哪些是空值了，再回到原Excel表，就容易弄清楚發(fā)生了什么事啦。其中，新列表中的元素的結構是一個元組，像這樣`(2, '說明碼：77601')`，`i[0]`是索引`2`，`i[1]`是索引對應的值`說明碼：77601`。

ID_list_idx = list(enumerate(ID_list)) #加索引
ID_list_idx[:10]

for i in ID_list_idx: #遍歷列表
    if i[1] == None: #判斷索引對應的值是否為空值。i的結構是一個元組，像這樣(2, '說明碼：77601')，i[0]是索引，i[1]是索引對應的值
        print(i[0]) #打印索引編號

以上就是用Python在Excel中實現自動查找并替換數據的詳細內容，更多關于Python Excel查找并替換數據的資料請關注腳本之家其它相關文章！

最新評論