目錄
- 1. 前言
- 2. 實驗環境
- 3. 基本操作
- 3.1 安裝openpyxl第三方庫
- 3.2 新建工作簿
- 3.3 加載工作簿
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操作內容請搜索腳本之家以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持腳本之家！
到此這篇關于Python對excel的基本操作方法的文章就介紹到這了,更多相關Python excel操作內容請搜索腳本之家以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持腳本之家！
您可能感興趣的文章:- 利用python對excel中一列的時間數據更改格式操作
- Python matplotlib讀取excel數據并用for循環畫多個子圖subplot操作
- python讀取excel進行遍歷/xlrd模塊操作
- 通過實例學習Python Excel操作
- Python操作excel的方法總結（xlrd、xlwt、openpyxl）
- Python3操作Excel文件（讀寫）的簡單實例
- python實現讀取excel文件中所有sheet操作示例
