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()# 激活并缺省創建第一個工作表：sheetws1 = wb.active# 第一個工作表命名:1_sheetws1.title = ’1_sheet’# 保存并生成文件wb.save(’simple_excel.xlsx’)

​效果如下所示：

3.2.3 創建工作表

from openpyxl import Workbook# 類實例化wb = Workbook()# 激活并缺省創建第一個工作表ws1 = wb.active# 第一個工作表命名ws1.title = ’1_sheet’# 創建工作表3ws3 = wb.create_sheet(title=’3_sheet’, index=2)# 創建工作表2ws2 = wb.create_sheet(’2_sheet’, 1)# 創建工作表4ws4 = 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 Workbookfrom 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 Workbookfrom openpyxl.styles import PatternFill, Border, Side, Font, colors,Alignmentfrom openpyxl.utils import get_column_letter# 類實例化wb = Workbook()# ...省略中間代碼...# 自動調整單元格寬度# 篩選出每一列中cell的最大長度，并作為調整列寬度的值。all_ws = wb.sheetnamesfor 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 = 10chart.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 Copy1_sheet2_sheet3_sheet3_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_namedevice_ipvendormodelsnosversionupdate_time

指定行范圍

# ... 省略代碼...# 指定行范圍for row in ws1[’2:3’]: for cell in row: print(cell.value) # ... 省略代碼...# 回顯結果如下:device_namedevice_ipvendormodelsnosversionupdate_timeswitch-01192.168.1.1ciscoWS-C3560G-24TSFOC00000000cisco_ios12.2(50)SE51 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_nameswitch-01switch-02switch-03

指定列范圍

# ... 省略代碼...# 指定列范圍for col in ws1[’A:B’]: for cell in col: print(cell.value) # ... 省略代碼... # 回顯結果如下:示例:device_nameswitch-01switch-02switch-03Nonedevice_ip192.168.1.1192.168.1.2192.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操作內容請搜索好吧啦網以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持好吧啦網！

到此這篇關于Python對excel的基本操作方法的文章就介紹到這了,更多相關Python excel操作內容請搜索好吧啦網以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持好吧啦網！