1.找出用戶行為偏好.xlsx中 Sheet3 表中空著的格子，并輸出這些格子的坐標。

# 獲取當前工作目錄
import os
print(f'當前工作目錄為：{os.getcwd()} \n')

import warnings
warnings.filterwarnings('ignore')
path = './OpenPyXL_test/'

from openpyxl import load_workbook
exl = load_workbook(path+'用戶行為偏好.xlsx')

# 查看excel 中表的名稱
print(f'excel文件中的表包括：{exl.sheetnames}\n')

# sheet1 = exl.get_sheet_by_name('sheet3')
sheet3 = exl['Sheet3']

# 輸出Sheet3 內容占據的大小
print(f'Sheet3內容大小：{sheet3.dimensions}\n')

# 直接通過sheet索引，sheet3.dimensions獲取sheet數據區域
cells = sheet3[sheet3.dimensions]

# 遍歷元組 判斷每一個cell值是否為空
for rows in cells:
    for cell in rows:
        if not cell.value:
            # coordinate：單元格坐標
            print(f'{cell.coordinate} is None \n')

2.Excel 在原有工作簿中修改數據并保存。

（1）修改并保存數據

# 1、導入 openpyxl 中的 load_workbook 函數
from openpyxl import load_workbook
# 2、獲取指定 excel 文件對象 Workbook
exl = load_workbook(filename=path+'用戶行為偏好.xlsx')
# 3、通過指定 sheetname 從 Workbook 中獲取 sheet 對象 Worksheet
sheet = exl.get_sheet_by_name("Sheet3")
# 4、通過索引方式獲取指定 cell 值，將 A1 重新賦值
print(f"修改前 sheet['A1']：{sheet['A1'].value}")
sheet['A1'].value = 'hello world'
print(f"修改后 sheet['A1']：{sheet['A1'].value}")
# 5、保存修改后的內容
# 如果filename和原文件同名，則直接在源文件中修改；
# 否則會新建一個excel文件，并保存內容
exl.save(filename=path+'用戶行為偏好_changed.xlsx')
# 我們選擇保存在一個新文件 用戶行為偏好_changed.xlsx 中

（2）驗證是否修改成功

# 驗證保存修改內容是否成功
exl_ = load_workbook(filename=path+'用戶行為偏好_changed.xlsx')
a1 = exl_['Sheet3']['A1'].value
if a1 == 'hello world':
    print(f"修改成功！\n exl_['Sheet3']['A1'].value={a1}")
else:
    print(f"修改失敗！\n exl_['Sheet3']['A1'].value={a1}")

3.創建新的表格寫入數據并保存

# 1、導入 openpyxl 中的 Workbook 類
from openpyxl import Workbook

# 2、初始化一個 Workbook 對象
wb = Workbook()
print(f'默認sheet：{wb.sheetnames}')

# 3、通過 Workbook 對象的 create_sheet 函數創建一個 sheet
# title sheet 名稱
# index sheet 位置，默認從0開始
sheet = wb.create_sheet(title='mysheet',index=0)
print(f'添加后sheet：{wb.sheetnames}')

# 4、在新建的 sheet 中寫入數據
# 比如在 A1 單元格中寫入‘test’
sheet['A1'].value='test'
print(f"sheet['A1'].value = {sheet['A1'].value}")

# 5、保存
wb.save(path+'create_sheet_test.xlsx')

4.將公式寫入單元格保存

# 1、導入 openpyxl 中的 load_workbook 函數
from openpyxl import load_workbook

# 2、獲取指定 excel 文件對象 Workbook
exl_1 = load_workbook(filename=path+'用戶行為偏好_changed.xlsx')

# 3、通過指定 sheetname 從 Workbook 中獲取 sheet 對象 Worksheet
sheet = exl_1['訂單時長分布']

# 先查看原有表格的單元格范圍，防止替代原有數據
print(f'訂單時長分布值范圍：{sheet.dimensions}\n')

# 單元格 A15 中寫入 合計
sheet['A15'].value = '合計'

# 單元格 D15 中寫入求和公式：SUM(D2:D14)
sheet['D15'] = '=SUM(D2:D14)'

# 保存
exl_1.save(filename='用戶行為偏好_changed.xlsx')

# 使用 xlwing 打開 excel 文件然后保存，使寫入的公式生效
import xlwings as xw

# 打開工作簿
app = xw.App(visible=False,add_book=False)
wb = app.books.open('用戶行為偏好_changed.xlsx')
wb.save()

# 關閉工作簿
wb.close()
app.quit()

# 驗證寫入是否成功
# 1、獲取指定 excel 文件對象 Workbook,并設置 data_only=True,
# 表示讀取的時候如果單元格內是公式的話，以公式計算后的值的形式顯示
exl_2 = load_workbook(filename='用戶行為偏好_changed.xlsx',data_only=True)

# 2、打印相關信息
sheet = exl_2['訂單時長分布']
print(f"sheet['A15']={sheet['A15'].value},sheet['D15']={sheet['D15'].value}")
print(f"{sheet['D1'].value}求和值為SUM(D2:D14)={sheet['D15'].value}")

注：即使設置了 data_only=True，也不能立即獲取到剛剛添加的公式計算后的結果，需要自己 手動/添加代碼 打開下 對應excel表格，然后 ctrl s保存下，再運行上面代碼才能獲取到對應公式計算后的值。

你可以使用下面代碼自動打開指定 excel 文件然后保存使寫入的公式生效，使用前你需要安裝 xlwings，輸入pip3 install xlwings即可，再后面我們也會學習這個模塊。

5.插入空列/行

# 獲取指定 sheet
sheet = exl_1['Sheet3']

# 插入列數據 insert_cols(idx,amount=1)
# idx是插入位置，amount是插入列數，默認是1
# idx=2表示第二列，即第二列前插入一列
sheet.insert_cols(idx=2)

# 第二列前插入5列
# sheet.insert_cols(idx=2,amount=5)

# 插入行數據 insert_rows(idx,amount=1)
# idx是插入位置，amount是插入行數，默認是1

# 在第二行前插入一行
sheet.insert_rows(idx=2)

# 在第2行前插入5行
# sheet.insert_rows(idx=2,amount=5)

exl_1.save(filename=path+'用戶行為偏好_changed.xlsx')

6.刪除和移動列和行

（1）刪除

# 刪除多列
sheet.delete_cols(idx=5,amount=2)

# 刪除多行
sheet.delete_rows(idx=2,amount=5)

exl_1.save(filename=path+'用戶行為偏好_changed.xlsx')

(2)移動

# 移動
# 當數字為正即向下或向右，為負即為向上或向左
sheet.move_range('B3:E16',rows=1,cols=-1)
exl_1.save(filename=path+'用戶行為偏好_changed.xlsx')

7.Excel樣式

（1）設置單個cell（單元格）字體樣式

Font(name字體名稱,size大小,bold粗體,italic斜體,color顏色)。

# 1) 導入 openpyxl 中的 load_workbook 函數
#    導入 openpyxl 中的  styles 模塊中的 Font 類
from openpyxl import load_workbook
from openpyxl.styles import Font

# 2) 獲取指定 excel文件對象 Workbook
exl_1 = load_workbook(filename=root_path+'用戶行為偏好_1.xlsx')
# 3) 通過指定 sheetname 從 Workbook 中獲取 sheet 對象 Worksheet
sheet = exl_1['訂單時長分布']
# 4) 獲取到指定 cell 后，查看cell字體屬性
cell = sheet['A1']
cell.font

# 5) 實例化一個 Font 對象，設置字體樣式
#    字體改為：黑體  大小改為：20  設置為：加粗 斜體 紅色
font = Font(name='黑體', size=20, bold=True, italic=True, color='FF0000')
cell.font = font
# 6) 保存修改 
exl_1.save(filename=root_path+'用戶行為偏好_1.xlsx')

（2）設置多個cell（單元格）字體樣式

# 上面我們已經獲取到了 '用戶行為偏好_1.xlsx' 中的 訂單時長分布 工作表
# 我們處理了 單元格 A1 的字體樣式，我們也可以通過遍歷的形式，批量設置單元格字體樣式

# 1) 獲取要處理的單元格 
#    通過 sheet 索引獲取第二行 cell
#    獲取列可以用 字母索引，如 sheet['A'] 獲取第一列 cell
cells = sheet[2]
# 2) 實例化一個 Font 對象，設置字體樣式
#    字體改為：黑體  大小改為：10  設置為：加粗 斜體 紅色
font = Font(name='黑體', size=10, bold=True, italic=True, color='FF0000')
# 3) 遍歷給每一個 cell 都設置上對應字體樣式
for cell in cells:
    cell.font = font
# 4) 保存修改
exl_1.save(filename=root_path+'用戶行為偏好_1.xlsx')

8.設置邊框樣式

（1）設置單元格邊框樣式

Side：邊線樣式設置類，邊線顏色等

Side(style=None, color=None, border_style=None)

• style：邊線的樣式，有以下值可選：double, mediumDashDotDot, slantDashDot, dashDotDot, dotted, hair, mediumDashed, dashed, dashDot, thin, mediumDashDot, medium, thick
• color：邊線顏色
• border_style：style 的別名，必須設置，一般直接設置 border_style 就行，不用設置 style

Border：邊框定位類，左右上下邊線

Border常用參數解釋：

• top bottom left right diagonal：上下左右和對角線的邊線樣式，為 Side 對象
• diagonalDown：對角線從左上角向右下角方向，默認為 False
• diagonalUp：對角線從右上角向左下角方向，默認為 False

# 上面我們已經獲取到了 '用戶行為偏好_1.xlsx' 中的 訂單時長分布 工作表 sheet
# 1) 導入 openpyxl 中的  styles 模塊中的 Side, Border 類
from openpyxl.styles import Side, Border
# 2) 首先初始化一個邊線對象（也可以設置多個）
side = Side(border_style='double', color='FF000000')
# 3) 通過 Border 去設置 整個單元格邊框樣式
border = Border(left=side, right=side, top=side, bottom=side, diagonal=side, diagonalDown=True, diagonalUp=True)

# 4) 查看目前單元格邊框樣式
# 獲取第一行 cells
cells = sheet[1]
# 取出一個 cell 看邊框樣式
cells[0].border

# 5) 修改邊框樣式，并保存修改
for cell in cells:
    cell.border = border
exl_1.save(filename=root_path+'用戶行為偏好_1.xlsx')

9.設置單元格其他樣式

（1） 設置單元格背景色

# 上面我們已經獲取到了 '用戶行為偏好_1.xlsx' 中的 訂單時長分布 工作表 sheet
# 1) 從 openpyxl.styles 中導入 背景顏色設置類 PatternFill, GradientFill
from openpyxl.styles import PatternFill, GradientFill

# 2) 實例化 PatternFill 對象，fill_type 參數必須指定
pattern_fill = PatternFill(fill_type='solid',fgColor="DDDDDD")
# 3) 實例化 GradientFill 對象，填充類型 type 默認為 linear
gradient_fill = GradientFill(stop=('FFFFFF', '99ccff','000000'))

# 4) 獲取指定 cells 遍歷填充
# 對第三行 PatternFill 模式設置背景色
cells = sheet[3]
for cell in cells:
    cell.fill = pattern_fill

# 對第四行 GradientFill 模式設置背景色
cells = sheet[4]
for cell in cells:
    cell.fill = gradient_fill

# 5) 保存修改
exl_1.save(filename=root_path+'用戶行為偏好_1.xlsx')

（2）設置水平居中

openpyxl.styles 中的 Alignment 類常用參數介紹：

• horizontal：水平對齊，常見值 distributed, justify, center, left, fill, centerContinuous, right, general
• vertical：垂直對齊，常見值 bottom, distributed, justify, center, top
• textRotation：文字旋轉角度，數值：0-180
• wrapText：是否自動換行，bool值，默認 False

# 上面我們已經獲取到了 '用戶行為偏好_1.xlsx' 中的 訂單時長分布 工作表 sheet
# 1) 從 openpyxl.styles 中導入 對齊方式設置類 Alignment
from openpyxl.styles import Alignment

# 2) 實例化一個 Alignment 對象，設置水平、垂直居中
alignment = Alignment(horizontal='center', vertical='center')

# 3) 獲取指定 cells 遍歷填充
# 對第五行數據設置上面的對齊方式
cells = sheet[5]
for cell in cells:
    cell.alignment = alignment
# 4) 保存修改
exl_1.save(filename=root_path+'用戶行為偏好_1.xlsx')

(3)設置行高與列高

# 1) 設置行高，通過 row_dimensions 和 column_dimensions 來獲取行和列對象
# 2) 設置第1行行高為 30
sheet.row_dimensions[1].height = 30
# 3) 設置第3列列款為 24
sheet.column_dimensions['C'].width = 24
# 4) 保存修改
exl_1.save(filename=root_path+'用戶行為偏好_1.xlsx')

10.合并、取消合并單元格