Python對excel的基本操作方法
1. 前言
本文是通過Python的第三方庫openpyxl
, 該庫根據官方介紹是支持Excel 2010 的 xlsx/xlsm/xltx/xltm格式文件,哪個版本的這些格式應該都可以支持。
作為網絡攻城獅的我們,使用python對excel的基本操作技能就可以瞭,當然能夠精通更好瞭。
那我們使用openpyxl
有何作用?我是想後面跟大傢分享一篇批量備份網絡設備配置
的文章,裡面會涉及到對excel的操作,就提前給大傢分享下如何操作基本的excel,順便鞏固下自己的知識。
來來來,先看下如下圖所示,這是存放一張所有網絡設備的管理地址表,通過python的openpyxl
庫就可以讀取ip地址信息、巡檢命令等信息,就可以批量去備份網絡設備配置瞭,之前我都是用python結合txt文本的,覺得不太方便,就改成python結合excel的方式,excel編輯起來就非常方便瞭。
2. 實驗環境
- windown 10
- Python3.69
- Pycharm
- Python第三方庫
openpyxl
- excel 2013
說明:各位實驗環境請隨意組合,python版本是3.x以上。
3. 基本操作
接下來就開始一步一步教大傢如何操作…
3.1 安裝openpyxl第三方庫
C:\>pip install openpyxl
3.2 新建工作簿
3.2.1 新創建工作簿
from openpyxl import Workbook # 類實例化 wb = Workbook() # 保存並生成文件 wb.save('simple_excel.xlsx')
說明:運行該代碼後,會生成一份excel文件:simple_excel.xlsx
,暫時沒內容。
3.2.2 缺省工作表
from openpyxl import Workbook # 類實例化 wb = Workbook() # 激活並缺省創建第一個工作表:sheet ws1 = wb.active # 第一個工作表命名:1_sheet ws1.title = '1_sheet' # 保存並生成文件 wb.save('simple_excel.xlsx')
效果如下所示:
3.2.3 創建工作表
from openpyxl import Workbook # 類實例化 wb = Workbook() # 激活並缺省創建第一個工作表 ws1 = wb.active # 第一個工作表命名 ws1.title = '1_sheet' # 創建工作表3 ws3 = wb.create_sheet(title='3_sheet', index=2) # 創建工作表2 ws2 = wb.create_sheet('2_sheet', 1) # 創建工作表4 ws4 = wb.copy_worksheet(ws3) # 保存並生成文件 wb.save('simple_excel.xlsx')
參數說明:
- 屬性title:為工作表命名;
- 方法create_sheet:創建新的工作表,其中index為工作表的順序索引,如0表示第一張表…;
- 方法copy_worksheet:復制工作表;
- 方法save:保存並生成文件,每次運行都會覆蓋同名文件;
3.2.4 刪除工作表
from openpyxl import Workbook # 類實例化 wb = Workbook() # 激活並缺省創建第一個工作表 # ...省略中間代碼... ws4 = wb.copy_worksheet(ws3) # 刪除工作表 wb.remove(ws4) # 保存並生成文件 wb.save('simple_excel.xlsx')
說明:此步驟我就不運行瞭。
3.2.5 設置工作表顏色
from openpyxl import Workbook # 類實例化 wb = Workbook() # ...省略中間代碼... # 設置工作表背景色 ws1.sheet_properties.tabColor = '90EE90' ws2.sheet_properties.tabColor = '1E90FF' ws3.sheet_properties.tabColor = '90EE90' ws4.sheet_properties.tabColor = '1E90FF' # 保存並生成文件 wb.save('simple_excel.xlsx')
參數說明:
屬性tabColor:設置工作表背景色,可以使用RGB
顏色。
效果如下:
3.2.6 單元格寫入數據
#### 寫入單個數據
from openpyxl import Workbook # 類實例化 wb = Workbook() # ...省略中間代碼... # 單元格寫入數據 # 方法1: ws1['A1'] = '示例:' # 方法2: ws1.cell(row=1, column=1, value='示例:') # 保存並生成文件 wb.save('simple_excel.xlsx')
批量寫入數據
from openpyxl import Workbook # 類實例化 wb = Workbook() # ...省略中間代碼... # 單元格寫入數據 data = [ ["device_name", "device_ip", "vendor", "model", "sn", "os", "version", "update_time"], ['switch-01', "192.168.1.1", "cisco", 'WS-C3560G-24TS', "FOC00000000", "cisco_ios", "12.2(50)SE5", "1 weeks, 1 minutes" ], ['switch-02', "192.168.1.2", "cisco", 'WS-C3560G-24TS', "FOC00000000", "cisco_ios", "12.2(50)SE5", "1 weeks, 1 minutes" ], ['switch-03', "192.168.1.3", "cisco", 'WS-C3560G-24TS', "FOC00000000", "cisco_ios", "12.2(50)SE5", "1 weeks, 1 minutes" ], ] for row in data: ws1.append(row) # 保存並生成文件 wb.save('simple_excel.xlsx')
參數說明:
append:傳入可迭代對象(字符串、列表、元組…),迭代寫入單元格;
效果如下:
3.2.7 設置單元格背景色
from openpyxl import Workbook from openpyxl.styles import PatternFill, Border, Side, Font, colors # 類實例化 wb = Workbook() # ...省略中間代碼... # 單元格填充背景色 background_color = PatternFill(start_color='00BFFF', fill_type='solid') # 設置邊框 border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin')) font_type = Font(color=colors.WHITE, size=12, b=True, bold=True) # 設置字體居中 Align = Alignment(horizontal='center', vertical='center') # 循環迭代cell並設置樣式 for row in ws1.iter_rows(min_row=2,max_row=2): for cell in row: cell.fill, cell.font, cell.alignment = background_color, font_type, Align
參數說明:
- 類PatternFill:start_color、end_color為背景色、圖案顏色、圖案樣式;
- 類Border:設置邊框線條樣式,如線條寬度樣式、對角線等;
- 類Font:設置字體顏色、大小、下劃線等;
- 類Alignment:設置文本對齊方式,水平對齊、垂直對齊;
效果如下:
3.2.8 合並單元格
# ...省略代碼... # 合並單元格 ws1.merge_cells('A1:H1') ws1.unmerge_cells('A1:H1') # ...省略代碼...
參數說明:
- merge_cells:合並單元格;
- unmerge_cells:取消合並單元格;
效果如下:
3.2.9 自動調整列單元格寬度
from openpyxl import Workbook from openpyxl.styles import PatternFill, Border, Side, Font, colors,Alignment from openpyxl.utils import get_column_letter # 類實例化 wb = Workbook() # ...省略中間代碼... # 自動調整單元格寬度 # 篩選出每一列中cell的最大長度,並作為調整列寬度的值。 all_ws = wb.sheetnames for ws in all_ws: dims = {} ws = wb[ws] for row in ws.rows: for cell in row: if cell.value: dims[cell.column] = max(dims.get(cell.column, 0), len(str(cell.value))) for col, value in dims.items(): ws.column_dimensions[get_column_letter(col)].width = value + 3 dims.clear()
思路解讀:
先找出列所有數據的最大長度,然後按照這個長度自動調整單元格的寬度。
- 先定義一個空字典dims,用來存放鍵值對,column(列):value(單元格cell長度);
- 每一列的單元格value長度一一比較取得最大值,最後取得最最最大值,作為每列的寬度值width;
- 方法get_column_letter():是將cell.column整數值1、2、3…轉換為列字符串’A’、’B’、 ‘C’…;
- 方法column_dimensions:通過width設置列的寬度,建議再加大一點;
效果如下:
3.2.10 圖表
from openpyxl.chart import BarChart3D, Reference # ...省略中間代碼... # 單元格先寫入數據 data = [ ["Fruit", "2017", "2018", "2019", "2020"], ['Apples', 10000, 5000, 8000, 6000], ['Pears', 2000, 3000, 4000, 5000], ['Bananas', 6000, 6000, 6500, 6000], ['Oranges', 500, 300, 200, 700], ] for row in data: ws2.append(row) # 開始繪3D柱狀圖 chart = BarChart3D() chart.type = 'col' chart.style = 10 chart.title = '銷量柱狀圖' chart.x_axis.title = '水果' chart.y_axis.title = '銷量' # set_categories() X軸設置數據, add_data() y軸設置數據 data = Reference(ws2, min_col=2, min_row=1, max_col=5, max_row=5) series = Reference(ws2, min_col=1, min_row=2, max_row=5) chart.add_data(data=data, titles_from_data=True) chart.set_categories(series) ws2.add_chart(chart, 'A7')
參數說明:
- 屬性type:可以設置列col 和水平bar兩種方式;
- 屬性style:設置樣式,為整數值1~48之間;
- 屬性title:設置標題;
- 屬性x_axis.title:x軸的標題;
- 屬性y_axis.title:y軸的標題;
- 類Reference:引用單元格范圍的數據;
- 方法add_data:設置Y軸數據;
- 方法set_categories:設置X軸數據;
效果如下:
3.3 加載工作簿
通過load_workbook
方法加載已存在的excel文件,並以read_only
隻讀方式讀取內容,不能進行編輯。
load_workbook
方法參數:
- filename:文件路徑或文件對象;
- read_only:是否為隻讀,針對閱讀做瞭優化,不能編輯內容;
- keep_vba:是否保留vba內(並不意味可以用它),缺省保留;
- data_only:單元格是否保留公式或結果;
- keep_links:是否保留外部鏈接,缺省保留;
3.3.1 獲取工作表
from openpyxl import load_workbook as open # 類示例化 wb = open('simple_excel.xlsx', read_only=True) # 獲取所有工作表 print('所有工作表: ', wb.sheetnames) # 關閉工作簿 wb.close() # 回顯結果如下: 所有工作表: ['1_sheet', '2_sheet', '3_sheet', '3_sheet Copy']
參數說明:
- 參數read_only=True:表示以
隻讀
模式打開工作簿; - 方法sheetnames:返回的是一個列表形式的工作表名稱;
- 方法close():僅在
read-only
和write-only
模式使用即可,下同,故不做多次解釋;
3.3.2 遍歷工作表
from openpyxl import load_workbook as open # 類示例化 wb = open('simple_excel.xlsx', read_only=True) # 獲取單個工作表 print('第1個工作表:', wb.sheetnames[0]) print('第2個工作表:', wb.sheetnames[1]) print('第3個工作表:', wb.sheetnames[2]) print('第4個工作表:', wb.sheetnames[3]) # 循環遍歷工作表 for ws in wb.sheetnames: print(ws) # 關閉工作簿 wb.close() # 回顯結果如下: 第1個工作表: 1_sheet 第2個工作表: 2_sheet 第3個工作表: 3_sheet 第4個工作表: 3_sheet Copy 1_sheet 2_sheet 3_sheet 3_sheet Copy
3.3.3 獲取單元格數據
from openpyxl import load_workbook as open # 類示例化 wb = open('simple_excel.xlsx', read_only=True) # 第一個工作表對象 ws1 = wb[wb.sheetnames[0]] # 或者 # ws1 = wb['1_sheet'] # 獲取某個單元格 print(f"獲取單元格數據: {ws1['A3'].value}") # 選取范圍獲取單元格數據 for row in ws1['A3:H3']: for cell in row: print(f"按范圍獲取單元格數據: {cell.value}") # 關閉工作簿 wb.close() # 回顯結果如下: 獲取單元格數據: switch-01 按范圍獲取單元格數據: switch-01 按范圍獲取單元格數據: 192.168.1.1 按范圍獲取單元格數據: cisco 按范圍獲取單元格數據: WS-C3560G-24TS 按范圍獲取單元格數據: FOC00000000 按范圍獲取單元格數據: cisco_ios 按范圍獲取單元格數據: 12.2(50)SE5 按范圍獲取單元格數據: 1 weeks, 1 minutes
3.3.4 遍歷行
指定行
from openpyxl import load_workbook as open # 類示例化 wb = open('simple_excel.xlsx', read_only=True) # 第一個工作表對象 ws1 = wb[wb.sheetnames[0]] # 指定第二行 for cell in ws1['2']: print(cell.value) # 關閉工作簿 wb.close() # 回顯結果如下: device_name device_ip vendor model sn os version update_time
指定行范圍
# ... 省略代碼... # 指定行范圍 for row in ws1['2:3']: for cell in row: print(cell.value) # ... 省略代碼... # 回顯結果如下: device_name device_ip vendor model sn os version update_time switch-01 192.168.1.1 cisco WS-C3560G-24TS FOC00000000 cisco_ios 12.2(50)SE5 1 weeks, 1 minutes
方法iter_rows,遍歷行
from openpyxl import load_workbook as open # 類示例化 wb = open('simple_excel.xlsx', read_only=True) # 第一個工作表對象 ws1 = wb[wb.sheetnames[0]] # 循環遍歷行 for row in ws1.iter_rows(min_row=2, max_row=2, min_col=1, max_col=8): for cell in row: print(f"單元格數據:{cell.value}") # 關閉工作簿 wb.close() # 回顯結果如下: 單元格數據:device_name 單元格數據:device_ip 單元格數據:vendor 單元格數據:model 單元格數據:sn 單元格數據:os 單元格數據:version 單元格數據:update_time
參數說明:
方法iter_rows:通過該方法可以遍歷每行數據,是一個tuple
,可再次循環通過.value
獲取單元格數據;
3.3.5 遍歷列
指定列
from openpyxl import load_workbook as open # 類示例化 wb = open('simple_excel.xlsx', read_only=True) # 第一個工作表對象 ws1 = wb[wb.sheetnames[0]] # 指定第一列 for cell in ws1['A']: print(cell.value) # 關閉工作簿 wb.close() # 回顯結果如下: 示例: device_name switch-01 switch-02 switch-03
指定列范圍
# ... 省略代碼... # 指定列范圍 for col in ws1['A:B']: for cell in col: print(cell.value) # ... 省略代碼... # 回顯結果如下: 示例: device_name switch-01 switch-02 switch-03 None device_ip 192.168.1.1 192.168.1.2 192.168.1.3
方法iter_cols,遍歷列
from openpyxl import load_workbook as open # 類示例化 wb = open('simple_excel.xlsx') # 第一個工作表對象 ws1 = wb[wb.sheetnames[0]] # 循環遍歷列 for col in ws1.iter_cols(min_row=3, max_row=5, min_col=1, max_col=1): for cell in col: print(f"單元格數據:{cell.value}") # 關閉工作簿 wb.close() # 回顯結果如下: 單元格數據:switch-01 單元格數據:switch-02 單元格數據:switch-03
參數說明:
方法iter_cols:通過該方法可以遍歷每列數據,是一個tuple
,可再次循環通過.value
獲取單元格數據,另外和iter_rows
不一樣的就是load_workbook 不能使用read_only=True
;
附錄openpyxl官方文檔
openpyxl
RGB顏色參考
RGB顏色參考
到此這篇關於Python對excel的基本操作的文章就介紹到這瞭,更多相關Python excel操作內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet!
到此這篇關於Python對excel的基本操作方法的文章就介紹到這瞭,更多相關Python excel操作內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet!
推薦閱讀:
- 詳解Python操作Excel之openpyxl
- python 使用openpyxl讀取excel數據
- python技巧分享Excel創建和修改
- Python 操作 Excel 之 openpyxl 模塊
- python之openpyxl模塊的安裝和基本用法(excel管理)