Python對excel的基本操作

1. 前言
2. 實驗環境
3. 基本操作
- 3.1 安裝openpyxl第三方庫
- 3.2 新建作業簿
  - 3.2.1 新創建作業簿
  - 3.2.2 預設作業表
  - 3.2.3 創建作業表
  - 3.2.4 洗掉作業表
  - 3.2.5 設定作業表顏色
  - 3.2.6 單元格寫入資料
    - 批量寫入資料
  - 3.2.7 設定單元格背景色
  - 3.2.8 合并單元格
  - 3.2.9 自動調整列單元格寬度
  - 3.2.10 圖表
- 3.3 加載作業簿
  - 3.3.1 獲取作業表
  - 3.3.2 遍歷作業表
  - 3.3.3 獲取單元格資料
  - 3.3.4 遍歷行
    - 指定行
    - 指定行范圍
    - 方法iter_rows，遍歷行
  - 3.3.5 遍歷列
    - 指定列
    - 指定列范圍
    - 方法iter_cols，遍歷列
附錄

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第三方庫

首先，我們得先安裝一下第三方庫`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='https://www.cnblogs.com/singvis/p/示例:')

# 保存并生成檔案
wb.save('simple_excel.xlsx')

批量寫入資料

from openpyxl import Workbook

# 類實體化
wb = Workbook()

# ...省略中間代碼...

# 單元格寫入資料
data = https://www.cnblogs.com/singvis/p/[
    ["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 = https://www.cnblogs.com/singvis/p/[
    ["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 = https://www.cnblogs.com/singvis/p/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顏色參考

如果喜歡的我的文章，歡迎關注我的公眾號：點滴技術，掃碼關注，不定期分享

公眾號：點滴技術

轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/260475.html

標籤：Python

上一篇：云原生系列4 批量定時更新本地代碼庫

下一篇：胡編亂造論文生成器

Python對excel的基本操作

1. 前言

2. 實驗環境

3. 基本操作

3.1 安裝openpyxl第三方庫

3.2 新建作業簿

3.2.1 新創建作業簿

3.2.2 預設作業表

3.2.3 創建作業表

3.2.4 洗掉作業表

3.2.5 設定作業表顏色

3.2.6 單元格寫入資料

批量寫入資料

3.2.7 設定單元格背景色

3.2.8 合并單元格

3.2.9 自動調整列單元格寬度

3.2.10 圖表

3.3 加載作業簿

3.3.1 獲取作業表

3.3.2 遍歷作業表

3.3.3 獲取單元格資料

3.3.4 遍歷行

指定行

指定行范圍

方法iter_rows，遍歷行

3.3.5 遍歷列

指定列

指定列范圍

方法iter_cols，遍歷列

附錄