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-onlywrite-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

附錄


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

公眾號:點滴技術

相關文章