python之excel文件(.xls文件)處理方式
python excel文件(.xls文件)如何處理
xlrd
用于讀取文件,xlwt
用于寫入文件,xlutils
是兩個工具包的橋梁,也就是通過xlrd 讀取.xls
文件,然后通過xlutils 將文件內(nèi)容交給xlwt處理并且保存。
xlrd
常用API:
#導(dǎo)入 import xlrd # 打開文件 必須是存在的文件路徑 wb = xlrd.open_workbook('路徑') # 獲取文件中所有的sheet對象 objects = wb.sheets() #獲取文件中所有的sheet名稱 names = wb.sheet_names() # 按照索引獲得sheet對象 ws = wb.sheet_by_index(索引值) #按照名稱獲得sheet對象 ws = wb.sheet_by_name(文件名) #獲得當(dāng)前sheet對象的名稱 name = ws.name #獲得當(dāng)前excel文件的sheet個數(shù) n = wb.nsheets #獲得當(dāng)前sheet已使用的行和列 nrows = ws.nrows ncols = ws.ncols # 獲得當(dāng)前sheet某一行或者某一列的所有元素 元素格式是:數(shù)據(jù)類型:數(shù)據(jù)值 # 數(shù)據(jù)類型:0.空,1.字符串,2.數(shù)字,3.日期,4.布爾,5.error lst = ws.row(索引值) lst = ws.col(索引值) # 是ws.row(索引值)和s.col(索引值)得到允許切片版本 lst = ws.row_slice(索引值, start_colx=0, end_colx=None) lst = ws.col_slice(索引值, start_rowx=0, end_rowx=None) # 獲得當(dāng)前sheet某一行或者某一列的所有元素的值 lst = ws.row_values(索引值) lst = ws.col_values(索引值) # 獲得當(dāng)前sheet某一行或者某一列的所有元素的數(shù)據(jù)類型 lst = ws.row_types(索引值,start_colx=0, end_colx=None) lst = ws.col_types(索引值, start_rowx=0, end_rowx=None) # 返回當(dāng)前sheet某一行已使用的長度 i = ws.row_len(索引值) # 獲取當(dāng)前sheet中某個單元格的元素 元素格式是:數(shù)據(jù)類型:數(shù)據(jù)值 # 數(shù)據(jù)類型:0.空,1.字符串,2.數(shù)字,3.日期,4.布爾,5.error n = ws.cell(行索引, 列索引) # 獲取當(dāng)前sheet中某個單元格的元素的值 n = ws.cell_value(行索引, 列索引) n = ws.cell(行索引,列索引).value n = ws.row(行索引)[列索引].value # 獲取當(dāng)前sheet中某個單元格的元素的數(shù)據(jù)類型 n = ws.cell_type(行索引, 列索引) n = ws.cell(行索引,列索引).ctype n = ws.row(行索引)[列索引].ctype
xlwt
# 導(dǎo)入 import xlwt # 新建.xls的文件 nwb = xlwt.Workbook('utf-8') # 添加工作表 nws = nwb.add_sheet('名稱', cell_overwrite_ok=True) # 在對應(yīng)單元格上寫入內(nèi)容 nws.write(行索引,列索引,寫入內(nèi)容) # 保存文件 注意.xls的后綴不能少 nwb.save('文件名.xls')
xlutils
# 導(dǎo)入 from xlutils.copy import copy # 用xlrd導(dǎo)入待處理的文件 wb = xlrd.open_workbook('文件名') #使用copy函數(shù)處理 此時的 nwb 不僅具有 xlwt 的功能, 還具有 xlrd 的功能 #也就是既可讀又可寫. nwb = copy(wb)
python處理excel文件問題
最近需要頻繁讀寫 excel 文件,想通過程序?qū)?excel 文件進(jìn)行自動化處理,發(fā)現(xiàn)使用 python 的 openpyxl 庫進(jìn)行 excel 文件讀寫實在太方便了,結(jié)構(gòu)清晰,操作簡單。
本文對 openpyxl 的使用進(jìn)行總結(jié),主要包含以下內(nèi)容:
- openpyxl 的介紹及 excel 文件結(jié)構(gòu)說明
- 工作表的讀寫處理
- 行列的讀寫處理
- 單元格的讀寫處理
openpyxl 及 excel 文件結(jié)構(gòu)
openpyxl 是一個對 xlsx/xlsm/xltx/xltm 格式的 2010 excel 文檔進(jìn)行讀寫的 python 庫。它官網(wǎng)有詳細(xì)的文檔介紹。在進(jìn)行使用前,需先安裝并引入
# 安裝 pip install openpyxl # 引入openpyxl 模塊 import openpyxl
在進(jìn)行 excel 操作之前,先對 excel 的文件結(jié)構(gòu)做一個簡單了解,以便于熟悉后續(xù)的操作。如下圖:
一個 excel 文件,其內(nèi)容按層次分為工作簿(文件) -> 工作表(sheet) -> 行列 -> 單元格 ,對應(yīng)上圖,整個 excel 文件即是一個工作簿;工作簿下可以有多個工作表(如圖中的 Sheet1/test1 等等);工作表中就是對應(yīng)的表格數(shù)據(jù),分為行和列,行是用序號表示,列用大寫字母表示(也可用序號);行與列的交點就是每一個存儲數(shù)據(jù)的單元格。
因此,我們對 excel 表格進(jìn)行讀寫,基本按這個層次思路來操作:讀入文件,找到工作表,遍歷行列,定位單元格,對單元格進(jìn)行讀寫。因此,會涉及到工作表、行列、單元格的讀寫操作。
這些操作之前,需要先把文件加載進(jìn)來,一個 excel 文件就是一個工作簿 (workbook),加載操作如下(示例中的 excel 文件為 text.xlsx):
# 加載excel文件 file_path = "E:/pythontest/test.xlsx" workbook = openpyxl.load_workbook(file_path)
工作表處理
- 工作表讀取
工作表( sheet )會有多個,可以讀取全部的工作表,讀取單個時,可以按 sheet 名稱讀取,也可以按下標(biāo)(下標(biāo)從0開始)。
- 全部工作表對象:workbook.worksheets
- 全部工作表名稱:workbook.sheetnames
- 按名稱(sheet_name)獲取工作表:workbook[sheet_name]
- 按下標(biāo)(i從0開始)獲取工作表:workbook.worksheets[i]
- 獲取正在使用的工作表:workbook.active
- 獲取工作表的屬性(如工作表名稱、最大行數(shù)和列數(shù)等):sheet.title、sheet.max_row、sheet.max_column
如下:
# 全部sheet對象 >>> workbook.worksheets [<Worksheet "Sheet1">, <Worksheet "test1">, <Worksheet "test2">] # 全部sheet名稱 >>> workbook.sheetnames ['Sheet1', 'test1', 'test2'] # 按名稱讀取sheet >>> workbook["Sheet1"] <Worksheet "Sheet1"> # 按下標(biāo)讀取 >>> workbook.worksheets[0] <Worksheet "Sheet1"> # 獲取當(dāng)前正在使用的sheet >>> workbook.active <Worksheet "Sheet1"> # 獲取sheet的屬性 >>> sheet_active.title Sheet1 >>> sheet_active.max_row 6 >>> sheet_active.max_column 3
- 工作表添加
若需要新增工作表,按操作流程,先添加工作表,再保存文件。創(chuàng)建通過create_sheet完成,創(chuàng)建后保存(save)文件,添加才能生效。
- 創(chuàng)建工作表,若名稱相同,則自動進(jìn)行重命名:workbook.create_sheet(“test3”)
- 在指定的下標(biāo)創(chuàng)建工作表:workbook.create_sheet(“test4”,1)
- 保存文件,若文件路徑與打開的文件路徑相同,則覆蓋;不同,則會復(fù)制原文件并保存(相當(dāng)于另存為):workbook.save(file_path)
- 工作表修改
要修改工作表名稱,直接通過設(shè)置工作表的 title 即可,修改后同樣需要保存文件。
# 修改工作表名稱 >>> sheet1 = workbook['test1'] >>> sheet1.title = 'test11' # 保存文件 >>> workbook.save(file_path)
- 工作表刪除
刪除工作表,需要先獲取 sheet 對象,然后刪除。刪除有兩種方式,一是使用 workbook 提供的 remove 方法,也可以直接使用 python 的del進(jìn)行刪除。刪除操作后,同樣需要保存文件:
# remove刪除工作表 sheet = workbook["test-1"] workbook.remove(sheet) # del操作刪除 del workbook["test2"] # 保存文件 workbook.save(file_path)
行列處理
獲取 sheet 對象后,后續(xù)即可進(jìn)行行列操作,包括行列讀取,添加,刪除等。
- 讀行列
獲取全部行和列,然后可以進(jìn)行遍歷:sheet.rows ,sheet.columns
讀取部分行列:讀一行sheet[1],讀多行sheet[2:3],讀一列sheet[‘A’],讀多列sheet[‘B:C’]
# 遍歷全部行 >>> for row in sheet.rows: ... print(row) ... (<Cell 'Sheet1'.A1>, <Cell 'Sheet1'.B1>, <Cell 'Sheet1'.C1>) (<Cell 'Sheet1'.A2>, <Cell 'Sheet1'.B2>, <Cell 'Sheet1'.C2>) .... # 讀取部分行列 >>> sheet[1] (<Cell 'Sheet1'.A1>, <Cell 'Sheet1'.B1>, <Cell 'Sheet1'.C1>) >>> sheet["A:B"] ((<Cell 'Sheet1'.A1>, <Cell 'Sheet1'.A2>, <Cell 'Sheet1'.A3>, <Cell 'Sheet1'.A4>, <Cell 'Sheet1'.A5>, <Cell 'Sheet1'.A6>), (<Cell 'Sheet1'.B1>, <Cell 'Sheet1'.B2>, <Cell 'Sheet1'.B3>, <Cell 'Sheet1'.B4>, <Cell 'Sheet1'.B5>, <Cell 'Sheet1'.B6>))
- 添加行列
添加行列,可以指定位置添加單個行列或多個行列。
- 直接在工作表中追加行數(shù)據(jù):sheet.append(rowdata)
- 在指定 index(從1開始計算) 位置添加行列:sheet.insert_rows,sheet.insert_cols
# 在第4行插入1行空行 >>> sheet.insert_rows(4) # 在第2行插入2行空行 >>> sheet.insert_rows(idx=2,amount=2) # 添加一行數(shù)據(jù)到表 >>> row_data = ["tom", 15, "tom@test.com"] >>> sheet.append(row_data) # 保存修改內(nèi)容 >>> workbook.save(file_path)
- 刪除行列
刪除操作與插入行列操作方式一致,使用delete_rows及delete_cols方法。
# 刪除行 >>> sheet.delete_rows(2,2) >>> workbook.save(file_path)
單元格處理
我們的數(shù)據(jù)最終是保存在每一個單元格(Cell)中,因此,最終我們操作數(shù)據(jù)其實就是單元格中的數(shù)據(jù),單元格中,openpyxl 使用是 Cell 對象。前面在遍歷行列數(shù)據(jù)時,可以看到輸出<Cell ‘Sheet1’.A1>的內(nèi)容,這對應(yīng)的單元格對象。下面對單元格的操作進(jìn)行說明。
- 獲取單元格數(shù)據(jù)值及屬性值
定位獲取單元格有兩種方式:
- 直接指定行列名:sheet[A1]
- 使用 cell 函數(shù)(行列下標(biāo)從1開始):sheet.cell(row=2,column=1)
# 指定行列坐標(biāo)獲取單元格 >>> sheet["A1"] <Cell 'Sheet1'.A1> # cell函數(shù)獲取單元格 >>> sheet.cell(row=1, column=1) <Cell 'Sheet1'.A1>
獲取單元格對象后,可以獲取數(shù)據(jù)值及其屬性,包括它所在的行列數(shù),坐標(biāo),值等。
>>> cell = sheet["A2"] >>> cell.value '張三' >>> cell.coordinate 'A2' >>> cell.column 1 >>> cell.row 2
- 移動單元格
通過對單元格區(qū)域,可以向上、下、左、右進(jìn)行移動,使用的是move_range(range,rows,cols),其中 rows 和 cols 為整數(shù),正整數(shù)表示向下或向右,負(fù)整數(shù)為向上或向左。
# 單元格合并,使用范圍坐標(biāo) sheet.merge_cells("A2:B3") # 單元格合并,指定行列下標(biāo)(下標(biāo)從1開始) sheet.merge_cells(start_row=5, start_column=3, end_row=7, end_column=4) wb.save(file_path) # 拆分單元格 sheet.unmerge_cells("A2:B3") sheet.unmerge_cells(start_row=5, start_column=3, end_row=7, end_column=4) # 保存文件 wb.save(file_path)
- 合并拆分單元格
對于跨行和跨列,需要對單元格進(jìn)行合并,使用的是merge_cells(range_string, start_row, start_column, end_row, end_column)。如果要合并的單元格都有數(shù)據(jù),只會保留左上角的數(shù)據(jù),其他則丟棄。合并及拆分都可以通過行列坐標(biāo)(如A1)或者行列下標(biāo)(如1,2)進(jìn)行。
# 單元格合并,使用范圍坐標(biāo) sheet.merge_cells("A2:B3") # 單元格合并,指定行列下標(biāo)(下標(biāo)從1開始) sheet.merge_cells(start_row=5, start_column=3, end_row=7, end_column=4) wb.save(file_path) # 拆分單元格 sheet.unmerge_cells("A2:B3") sheet.unmerge_cells(start_row=5, start_column=3, end_row=7, end_column=4) # 保存文件 wb.save(file_path)
- 寫入單元格
對單元格值進(jìn)行修改和寫入,直接對cell.value進(jìn)行賦值即可。
這里需要注意的是,可以寫入 excel 公式,具體公式與 excel 中用到公式一致,另外,若是寫入公式,讀取時獲取到的 value 值也是公式,而非公式值。
# 寫入值 cell.value = "張三" # 寫入公式(求平均值) cell.value = "=AVERAGE(B2:B6)"
- 設(shè)置單元格格式
單元格的格式包括行高,列寬,字體、邊框、對齊方式、填充顏色等。這些都在 openpyxl 的 styles 模塊中。
- 行高/列寬:row_dimensions[row_num].height = xx,sheet.column_dimensions[col_name].width = xx
- 字體( Font 對象):包括字段名稱,大小、加粗、斜體、顏色等,F(xiàn)ont(name=“微軟雅黑”, size=20, bold=True, italic=True, color=“000000”)
- 邊框( Border 對象和 Side 對象):邊框每一條邊的格式大小/顏色Side(style=“thin”, color=“000000”),通過邊構(gòu)建邊框?qū)ο螅築order(left=side, right=side, top=side, bottom=side)
- 對齊( Alignment 對象):垂直和水平對齊方向,是否自動換行。Alignment(horizontal=“center”, vertical=“center”, wrap_text=True)
- 填充顏色,分為普通顏色填充和漸變顏色填充:PatternFill(fill_type=“solid”, fgColor=“FF0000”)和 GradientFill(stop=(“FF0000”, “FD1111”, “000000”))
# 設(shè)置行高和列寬 sheet.row_dimensions[1].height = 50 sheet.column_dimensions["A"].width = 20 # 設(shè)置單元格字體 cell = sheet["A1"] current_font = cell.font font = Font(name="微軟雅黑", size=20, bold=True, italic=True, color="000000") cell.font = font # 設(shè)置邊框(細(xì)邊,黑色) side_style = Side(style="thin", color="000000") border = Border(left=side_style, right=side_style, top=side_style, bottom=side_style) cell.border = border # 居中對齊,自動換行 cell_alignment = Alignment(horizontal="center", vertical="center", wrap_text=True) cell.alignment = cell_alignment # 填充顏色(紅色填充,和紅色到黑色漸變填充) p_fill = PatternFill(fill_type="solid", fgColor="FF0000") g_fill = GradientFill(stop=("FF0000", "FD1111", "000000")) cell.fill = p_fill sheet["B1"].fill = g_fill
最后注意的是,這些修改操作最后都需要通過保存操作**(wb.save(file_path))**才能生效。
注意:
通過上面的講解,了解如何使用 python 的 openpyxl 庫對 excel 文檔的處理操作,可以發(fā)現(xiàn)它的操作邏輯相當(dāng)是清晰簡單的,符合的我們使用 excel 的習(xí)慣。
處理流程基本是加載文件、定位需要處理的工作表、行、列及單元格。對它們進(jìn)行讀、寫、修改格式等操作。
因此,如果有自動化處理 excel 文件的需求,用 openpyxl 吧,但它限制只能處理 2010 格式的 excel 文檔,對于舊格式( xls )的建議都統(tǒng)一換為新的格式再操作,或者也可以使用 xlrd 和 xlwt 模塊操作。
總結(jié)
以上為個人經(jīng)驗,希望能給大家一個參考,也希望大家多多支持腳本之家。
相關(guān)文章
python使用multiprocessing模塊實現(xiàn)帶回調(diào)函數(shù)的異步調(diào)用方法
這篇文章主要介紹了python使用multiprocessing模塊實現(xiàn)帶回調(diào)函數(shù)的異步調(diào)用方法,實例分析了multiprocessing模塊異步調(diào)用的相關(guān)使用技巧,需要的朋友可以參考下2015-04-04基于python全局設(shè)置id 自動化測試元素定位過程解析
這篇文章主要介紹了基于python全局設(shè)置id 自動化測試元素定位過程解析,文中通過示例代碼介紹的非常詳細(xì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友可以參考下2019-09-09攻擊者是如何將PHP Phar包偽裝成圖像以繞過文件類型檢測的(推薦)
這篇文章主要介紹了攻擊者是如何將PHP Phar包偽裝成圖像以繞過文件類型檢測的,需要的朋友可以參考下2018-10-10Python定時發(fā)送消息的腳本:每天跟你女朋友說晚安
今天小編就為大家分享一篇關(guān)于Python定時發(fā)送消息的腳本:每天跟你女朋友說晚安的文章,小編覺得內(nèi)容挺不錯的,現(xiàn)在分享給大家,具有很好的參考價值,需要的朋友一起跟隨小編來看看吧2018-10-10使用 Django Highcharts 實現(xiàn)數(shù)據(jù)可視化過程解析
這篇文章主要介紹了使用 Django Highcharts 實現(xiàn)數(shù)據(jù)可視化過程解析,文中通過示例代碼介紹的非常詳細(xì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友可以參考下2019-07-07Python實現(xiàn)RGB與HSI顏色空間的互換方式
今天小編就為大家分享一篇Python實現(xiàn)RGB與HSI顏色空間的互換方式,具有很好的參考價值,希望對大家有所幫助。一起跟隨小編過來看看吧2019-11-11Python實現(xiàn)爬蟲抓取與讀寫、追加到excel文件操作示例
這篇文章主要介紹了Python實現(xiàn)爬蟲抓取與讀寫、追加到excel文件操作,結(jié)合具體實例形式分析了Python針對糗事百科的抓取與Excel文件讀寫相關(guān)操作技巧,需要的朋友可以參考下2018-06-06Python實現(xiàn)word2Vec model過程解析
這篇文章主要介紹了Python實現(xiàn)word2Vec model過程解析,文中通過示例代碼介紹的非常詳細(xì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友可以參考下2019-12-12