Python對excel的基本操作

Singvis發表於2021-02-17

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

首先，我們得先安裝一下第三方庫`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進行那些操作
2024-10-27
PythonExcel
openpyxl 操作 Excel表的格基本用法
2021-11-29
Excel
python操作excel
2024-11-04
PythonExcel
python 操作 Excel 表格
2020-04-09
PythonExcel
python 3 操作 excel
2016-08-13
PythonExcel
python基本操作
2020-11-19
Python
Python對於Excel的一些基本繪製圖示操作，讓你日常工作更加輕鬆
2018-12-15
PythonExcel
python用win32com對Excel的一些操作
2017-04-19
PythonWin32Excel
[python]使用xlrd對Excel表格進行讀寫操作
2017-06-20
PythonExcel
C#對EXCEL的讀寫操作
2009-01-05
C#Excel
用Python完成Excel的常用操作
2018-09-08
PythonExcel
python 使用csv的基本操作
2021-06-24
Python
python對Excel的讀取
2019-07-18
PythonExcel
Oracle--02對錶的基本操作
2016-05-23
Oracle
SQLAIchemy對資料基本操作
2018-06-22
SQLAI
Python操作Excel的Xlwings教程（六）
2020-10-20
PythonExcel
Python操作Excel的Xlwings教程（七）
2020-10-28
PythonExcel
【Python自動化Excel】pandas處理Excel資料的基本流程
2022-01-09
PythonExcel
如何用python pandas操作excel?
2021-09-11
PythonExcel
python讀寫excel表操作
2020-11-03
PythonExcel
python+資料庫（三）用python對資料庫基本操作
2018-11-08
Python資料庫
python3之os的基本操作
2020-04-09
Python
python 操作 excel 之資料清洗
2021-03-02
PythonExcel
python操作Excel讀寫--使用xlrd
2012-03-18
PythonExcel
SQL—對資料表內容的基本操作
2018-05-14
SQL
python對檔案的操作
2018-04-08
Python
Python操作Excel的36個必備函式
2021-09-11
PythonExcel函式
excel基本操作：基礎、資料條件格式、快捷鍵
2024-07-03
Excel
關於python操作excel，xlwt，xlwd，最簡單的操作介紹
2020-10-07
PythonExcel
MySQL對錶和庫的一些基本操作
2018-08-09
MySql
python字典基本認識和操作
2020-10-29
Python
【Python自動化Excel】Python與pandas字串操作
2022-03-31
PythonExcel字串
Python操作excel(將多張excel表融合到一張表)
2020-10-20
PythonExcel
python對excel檔案中指定表格的指定列資料進行去重複操作。
2024-06-03
PythonExcel
Python中集合的概念及基本操作詳解！
2023-02-03
Python
Go 操作 Redis 的基本操作
2019-09-19
GoRedis
Python實現簡單的excel對比工具
2021-04-22
PythonExcel
.net core下對於Excel的一些操作及使用
2019-02-16
Excel

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，遍歷列

附錄

相關文章