一、Python處理Excel

• Python處理Excel的好處

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

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

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

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

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

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

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

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

• 使用pandas的時候，經(jīng)常會結(jié)合其他類庫，來完成更加復(fù)雜的功能

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

  • flask：可以做網(wǎng)頁，把表格展示在網(wǎng)頁上

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

  • sklearn：進行復(fù)雜的數(shù)據(jù)分析時，也可以結(jié)合機器學(xué)習(xí)Sklearn把讀取的Excel數(shù)據(jù)，進行數(shù)據(jù)分析和機器學(xué)習(xí)

  • Python-docx：也可以結(jié)合Python-docx類庫，實現(xiàn)Excel和word的互通

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

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

操作系統(tǒng)：使用windows, mac都可以

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

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

二、用Python在Excel中查找并替換數(shù)據(jù)

技術(shù)工具：

Python版本:3.9

代碼編輯器：jupyter notebook

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

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

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

data

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

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

type("")

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

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

ID_list[-10:]

這樣，我們就知道`ID_list`中有三種數(shù)據(jù)，即含內(nèi)容的字符串（比如'說明碼：77601'），空字符串（比如''）和空值None。因此，需要修改一下字符串分割代碼如下。加入了`if`判斷語句，如果`ID_list`中的值是None，則放入None占位，以保持列表的值的順序與原表一致；值不是None，則按"："符號分割，并放分割后的最后一個值`[-1]`進入新列表code?？兆址谶@里也要經(jīng)過`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]

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

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ā)現(xiàn)空值問題，還可以用`enumerate`函數(shù)給列表里的所有元素加上索引，以便精確定位`ID_list`中的空值。加上索引后，在轉(zhuǎn)換成列表，并存入新的列表`ID_list_idx`中。觀察其中前10個數(shù)據(jù)，可見索引已加好了。然后遍歷新列表，判斷其中的值是否為空值，若是則打印其對應(yīng)的索引編號，這樣就能精準(zhǔn)定位哪些是空值了，再回到原Excel表，就容易弄清楚發(fā)生了什么事啦。其中，新列表中的元素的結(jié)構(gòu)是一個元組，像這樣`(2, '說明碼：77601')`，`i[0]`是索引`2`，`i[1]`是索引對應(yīng)的值`說明碼：77601`。

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

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

以上就是用Python在Excel中實現(xiàn)自動查找并替換數(shù)據(jù)的詳細內(nèi)容，更多關(guān)于Python Excel查找并替換數(shù)據(jù)的資料請關(guān)注腳本之家其它相關(guān)文章！

最新評論