python自動(dòng)化辦公操作excel的示例詳解

更新時(shí)間：2024年03月24日 15:59:57 作者：z先生的備忘錄

這篇文章主要為大家詳細(xì)介紹了如何利用python來(lái)實(shí)現(xiàn)自動(dòng)化辦公操作excel文件進(jìn)行各種樣式展示,并自動(dòng)發(fā)送文件給"老板"的郵箱,希望對(duì)大家有所幫助

excel相關(guān)的背景知識(shí)
對(duì)單個(gè)單元格進(jìn)行操作
將二維list數(shù)組插入excel中
設(shè)置單元格樣式
插入圖表到excel文件中
插入超鏈接在單元格中
顯示迷你圖
設(shè)置單元格保護(hù)防止修改
顯示色階圖
顯示條件格式圖
利用calamine讀取excel大文件數(shù)據(jù)性能測(cè)試
將生成的excel文件發(fā)送到'老板'的郵箱

以163為例，打開(kāi)郵箱設(shè)置對(duì)應(yīng)的smtp服務(wù)
編寫(xiě)python代碼將excel文件發(fā)送指定郵箱

本文涉及的python庫(kù)版本信息如下:

!pip install pyjanitor==0.26.0
!pip install plottable==0.1.5
!pip install mpl_font==1.1.0
!pip install xlsxwriter==3.2.0
!pip install openpyxl==3.1.2

excel相關(guān)的背景知識(shí)

Excel中常見(jiàn)的文件格式(xls, xlsx, xlsm, xlsb, xla, xlam等等);在操作excel類常見(jiàn)的有三大數(shù)據(jù)對(duì)象，分別如下:

WorkBook：工作簿對(duì)象
Sheet：表單對(duì)象
Cell：表格對(duì)象

本文主要使用XlsxWriter庫(kù)和openpyxl庫(kù)來(lái)實(shí)現(xiàn)自動(dòng)化操作excel文件數(shù)據(jù)和樣式。下面進(jìn)入我們的正題~

對(duì)單個(gè)單元格進(jìn)行操作

# 導(dǎo)入xlsxwriter庫(kù)，用于創(chuàng)建Excel文件
import xlsxwriter

# 創(chuàng)建一個(gè)名為"demo.xlsx"的工作簿（即Excel文件）
workbook = xlsxwriter.Workbook("demo.xlsx")

# 在工作簿中添加一個(gè)名為'sheet1'的工作表
worksheet = workbook.add_worksheet(name='sheet1')

# 在B1單元格寫(xiě)入數(shù)值500
worksheet.write("B1", 500)

# 在第0行第1列等價(jià)于B1單元格寫(xiě)入數(shù)值500
worksheet.write(0, 1, 500)

# 在B2單元格寫(xiě)入公式，計(jì)算B1單元格數(shù)值的平方
worksheet.write_formula("B2", "{=SUM(B1*B1)}")

# 設(shè)置A列的寬度為40
worksheet.set_column("A:A", 40)

# 創(chuàng)建兩個(gè)格式對(duì)象，分別設(shè)置縮進(jìn)級(jí)別為1和2
indent1 = workbook.add_format({"indent": 1})  # 第1級(jí)縮進(jìn)格式
indent2 = workbook.add_format({"indent": 2})  # 第2級(jí)縮進(jìn)格式

# 使用不同的縮進(jìn)格式在A1和A2單元格寫(xiě)入文本，并設(shè)置相應(yīng)的縮進(jìn)
worksheet.write("A1", "This text is indented 1 level", indent1)  # A1單元格內(nèi)容縮進(jìn)1級(jí)
worksheet.write("A2", "This text is indented 2 levels", indent2)  # A2單元格內(nèi)容縮進(jìn)2級(jí)

# 關(guān)閉并保存工作簿
workbook.close()

將二維list數(shù)組插入excel中

# 定義表格的表頭信息以及相關(guān)數(shù)據(jù)
headings = ["Number", "Batch 1", "Batch 2"]  # 表頭列表
data = [                      # 數(shù)據(jù)列表
    [2, 3, 4, 5, 6, 7],       # 第一行：編號(hào)
    [40, 40, 50, 30, 25, 50], # 第二行：批次1的數(shù)據(jù)
    [30, 25, 30, 10, 5, 10],   # 第三行：批次2的數(shù)據(jù)
]
# 創(chuàng)建一個(gè)加粗格式樣式以突出顯示表頭
bold = workbook.add_format({"bold": 1})
# 將表頭信息寫(xiě)入到第一行（從A1開(kāi)始）
worksheet.write_row("A1", headings, bold)
# 將數(shù)據(jù)按列依次寫(xiě)入工作表
worksheet.write_column("A2", data[0])  # 寫(xiě)入編號(hào)數(shù)據(jù)到A列
worksheet.write_column("B2", data[1])  # 寫(xiě)入批次1的數(shù)據(jù)到B列
worksheet.write_column("C2", data[2])  # 寫(xiě)入批次2的數(shù)據(jù)到C列
# 關(guān)閉并保存工作簿
workbook.close()

設(shè)置單元格樣式

# 創(chuàng)建三種不同對(duì)角線樣式的格式：
format1 = workbook.add_format({"diag_type": 1}) # - format1: 對(duì)角線類型1
format2 = workbook.add_format({"diag_type": 2}) # - format2: 對(duì)角線類型2
format3 = workbook.add_format({"diag_type": 3}) # - format3: 對(duì)角線類型3
# 創(chuàng)建一個(gè)帶有紅色對(duì)角線、對(duì)角線類型為3且邊框線型為7的格式
format4 = workbook.add_format({
    "diag_type": 3,
    "diag_border": 7,
    "diag_color": "red",
})
# 使用不同對(duì)角線樣式寫(xiě)入單元格內(nèi)容
worksheet.write("B1", "Text", format1)  # 使用format1樣式寫(xiě)入
worksheet.write("B2", "Text", format2)  # 使用format2樣式寫(xiě)入
worksheet.write("B3", "Text", format3)  # 使用format3樣式寫(xiě)入
worksheet.write("B4", "Text", format4)  # 使用format4樣式寫(xiě)入
# 創(chuàng)建一個(gè)居中并對(duì)齊、加粗的單元格格式
centered_format = workbook.add_format({"align": 'center', 'valign': 'vcenter', "bold": True})
# 使用居中對(duì)齊格式寫(xiě)入單元格內(nèi)容
worksheet.write("A2", "Text", centered_format) 
# 最后關(guān)閉并保存工作簿
workbook.close()

插入圖表到excel文件中

# 在工作表的A1單元格嵌入圖片"demo.png"
# 注意：此操作默認(rèn)會(huì)按照?qǐng)D片原始尺寸插入
worksheet.insert_image("A1", "demo.png")
# 注：若要實(shí)現(xiàn)圖片自適應(yīng)單元格大小，可能需要在插入圖片時(shí)直接指定寬度和高度。
worksheet.insert_image("A1", "demo.png", {"x_scale": 0.5, "y_scale": 0.5})
# 最后，關(guān)閉并保存工作簿
workbook.close()

插入超鏈接在單元格中

# 設(shè)置第一列（A列）的寬度為30
worksheet.set_column("A:A", 30)
# 創(chuàng)建一個(gè)樣本格式：紅色字體、加粗、下劃線、字號(hào)為12
red_format = workbook.add_format({
    "font_color": "red",  # 文字顏色設(shè)為紅色
    "bold": 1,            # 字體加粗
    "underline": 1,       # 文字下劃線
    "font_size": 12,      # 字號(hào)為12
})
# 寫(xiě)入一些超鏈接
# A1單元格包含一個(gè)隱式格式的超鏈接至 http://www.python.org/
worksheet.write_url("A1", "http://www.python.org/")
# A3單元格包含一個(gè)顯示文本為"Python Home"的超鏈接至 http://www.python.org/
worksheet.write_url("A3", "http://www.python.org/", string="Python Home")
# A5單元格包含一個(gè)提示信息為"Click here"的超鏈接至 http://www.python.org/
worksheet.write_url("A5", "http://www.python.org/", tip="Click here")
# A7單元格包含一個(gè)應(yīng)用了紅色格式的超鏈接至 http://www.python.org/
worksheet.write_url("A7", "http://www.python.org/", red_format)
# A9單元格包含一個(gè)電子郵件鏈接至jmcnamara@cpan.org，顯示文本為"Mail me"
worksheet.write_url("A9", "mailto:jmcnamara@cpan.org", string="Mail me")
# 最后，關(guān)閉并保存工作簿
workbook.close()

顯示迷你圖

# 示例數(shù)據(jù)集，每個(gè)列表代表一行數(shù)據(jù)
data = [
    [-2, 2, 3, -1, 0],  # 第一行數(shù)據(jù)
    [30, 20, 33, 20, 15],  # 第二行數(shù)據(jù)
    [1, -1, -1, 1, -1],  # 第三行數(shù)據(jù)
]
# 將示例數(shù)據(jù)寫(xiě)入到工作表中
worksheet.write_row("A1", data[0])  # 寫(xiě)入第一行數(shù)據(jù)
worksheet.write_row("A2", data[1])  # 寫(xiě)入第二行數(shù)據(jù)
worksheet.write_row("A3", data[2])  # 寫(xiě)入第三行數(shù)據(jù)
# 在F1單元格添加一條帶標(biāo)記點(diǎn)的默認(rèn)折線型sparkline圖表（基于A1到E1區(qū)域的數(shù)據(jù)）
worksheet.add_sparkline("F1", {"range": "Sheet1!A1:E1", "markers": True})
# 在F2單元格添加一條柱狀sparkline圖表，采用非默認(rèn)風(fēng)格（基于A2到E2區(qū)域的數(shù)據(jù)）
# 同時(shí)顯示第一個(gè)點(diǎn)和最后一個(gè)點(diǎn)
worksheet.add_sparkline("F2", {
    "range": "Sheet1!A2:E2", 
    "type": "column", 
    "style": 12, 
    "first_point": True, 
    "last_point": True
})
# 在F3單元格添加一條勝敗（Win/Loss）類型的sparkline圖表（基于A3到E3區(qū)域的數(shù)據(jù)）
# 高亮顯示負(fù)值區(qū)域，并顯示最高點(diǎn)和最低點(diǎn)
worksheet.add_sparkline("F3", {
    "range": "Sheet1!A3:E3", 
    "type": "win_loss", 
    "negative_points": True,
    "high_point": True, 
    "low_point": True
})
# 最后，關(guān)閉并保存工作簿
workbook.close()

設(shè)置單元格保護(hù)防止修改

# 創(chuàng)建兩種具有保護(hù)屬性的單元格格式：
# - unlocked: 不鎖定的格式，可以編輯
# - hidden: 隱藏格式，公式不可見(jiàn)
unlocked = workbook.add_format({"locked": False})  # 創(chuàng)建不鎖定的格式
hidden = workbook.add_format({"hidden": True})     # 創(chuàng)建隱藏公式的格式
# 設(shè)置A列的寬度為40，以便更好地展示文本
worksheet.set_column("A:A", 40)
# 開(kāi)啟工作表保護(hù)模式
worksheet.protect()  # 默認(rèn)情況下所有單元格都會(huì)被鎖定
# 分別寫(xiě)入鎖定、解鎖及隱藏公式的單元格內(nèi)容
worksheet.write("A1", "Cell B1 is locked. It cannot be edited.")  # B1默認(rèn)鎖定
worksheet.write("A2", "Cell B2 is unlocked. It can be edited.")
worksheet.write("A3", "Cell B3 is hidden. The formula isn't visible.")
# 寫(xiě)入具有不同保護(hù)屬性的公式
worksheet.write_formula("B1", "=1+2")  # 默認(rèn)鎖定，無(wú)法編輯公式
worksheet.write_formula("B2", "=1+2", unlocked)  # 公式所在單元格已解鎖，可以編輯
worksheet.write_formula("B3", "=1+2", hidden)  # 公式所在單元格被隱藏，公式不可見(jiàn)
# 關(guān)閉并保存工作簿
workbook.close()

顯示色階圖

這里使用openpyxl庫(kù)來(lái)實(shí)現(xiàn)色階圖輸出到excel文件中。

from openpyxl import Workbook
from openpyxl.formatting.rule import ColorScaleRule
# 創(chuàng)建一個(gè)新的工作簿
workbook = Workbook()
# 選擇或創(chuàng)建一個(gè)工作表
worksheet = workbook.active
worksheet.title = 'Color Scale Test'
# 假設(shè)我們有以下一列測(cè)試數(shù)據(jù)
data = [30, 60, 90, 120, 150, 180, 210, 240, 270, 300]
# 將數(shù)據(jù)寫(xiě)入單元格
for row, value in enumerate(data, 1):
    ws.cell(row=row, column=1, value=value)
# 定義色階規(guī)則
rule = ColorScaleRule(start_type='min', start_color='FF0000',
                      mid_type='percentile', mid_value=50, mid_color='FFFF00',
                      end_type='max', end_color='00FF00')
# 應(yīng)用色階到數(shù)據(jù)所在列 (這里是第一列A)
worksheet.conditional_formatting.add('A1:A{}'.format(len(data)), rule)

# 保存Excel文件
wb.save('demo.xlsx')

顯示條件格式圖

這里使用openpyxl庫(kù)來(lái)實(shí)現(xiàn)條件格式圖輸出到excel文件中。

# 導(dǎo)入所需庫(kù)
from openpyxl import Workbook
from openpyxl.formatting.rule import ColorScaleRule, DataBarRule
from openpyxl.styles.colors import Color
# 1. 創(chuàng)建一個(gè)新的Excel工作簿
workbook = Workbook()
# 2. 選擇或獲取工作簿的第一個(gè)工作表，并設(shè)置其標(biāo)題
worksheet = workbook.active
worksheet.title = 'Color Scale Test'
# 3. 假設(shè)有如下一列測(cè)試數(shù)據(jù)
data = [30, 60, 90, 120, 150, 180, 210, 240, 270, 300]
# 4. 將測(cè)試數(shù)據(jù)寫(xiě)入Excel工作表的A列，從第二行開(kāi)始（Excel表格的第一行默認(rèn)為表頭）
for row_index, value in enumerate(data, 1):  # 使用enumerate()函數(shù)，索引從1開(kāi)始
    worksheet.cell(row=row_index, column=1, value=value)  # 設(shè)置單元格內(nèi)容
# 5. 定義數(shù)據(jù)所在的區(qū)域，即A2到A10這一列（由于enumerate()從1開(kāi)始，所以此處是A1到A10）
data_range = worksheet['A1:A10']
# 6. 創(chuàng)建一個(gè)DataBarRule實(shí)例，用于在指定范圍內(nèi)應(yīng)用數(shù)據(jù)條（顏色條）條件格式
rule = DataBarRule(
    start_type='min',  # 數(shù)據(jù)條的起點(diǎn)基于該列的最小值
    end_type='max',   # 數(shù)據(jù)條的終點(diǎn)基于該列的最大值
    color=Color(rgb='00FF00'),  # 數(shù)據(jù)條的顏色為綠色
    showValue=True,           # 顯示數(shù)據(jù)條旁邊的實(shí)際數(shù)值
    minLength=None,            # 數(shù)據(jù)條的最小長(zhǎng)度將由程序自動(dòng)計(jì)算，保持默認(rèn)值
    maxLength=None             # 數(shù)據(jù)條的最大長(zhǎng)度將由程序自動(dòng)計(jì)算，保持默認(rèn)值
)
# 7. 將數(shù)據(jù)條條件格式規(guī)則應(yīng)用到指定的單元格區(qū)域
worksheet.conditional_formatting.add('A1:A10', rule)  # 將規(guī)則應(yīng)用到A1到A10這一列
# 8. 保存已應(yīng)用條件格式的工作簿至Excel文件
workbook.save('demo.xlsx')  # 文件名：demo.xlsx

利用calamine讀取excel大文件數(shù)據(jù)性能測(cè)試

# 生成一個(gè)50000行、5列的隨機(jī)數(shù)二維數(shù)組
data = np.random.random(size=(50000, 5))
# 將生成的隨機(jī)數(shù)組轉(zhuǎn)換為Pandas DataFrame，這是一個(gè)表格型數(shù)據(jù)結(jié)構(gòu)，便于進(jìn)行數(shù)據(jù)分析和操作
pf = pd.DataFrame(data)
# 利用openpyxl引擎寫(xiě)入excel文件
%timeit pf.to_excel("demo.xlsx",index=False, engine= 'openpyxl')
# 4.33 s ± 16.1 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)
# 利用calamine引擎讀excel文件
%timeit pd.read_excel("demo.xlsx", engine= 'calamine')
# 344 ms ± 1.76 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)
# 利用openpyxl引擎讀excel文件
%timeit pd.read_excel("demo.xlsx", engine= 'openpyxl')
# 3.33 s ± 26.7 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

通過(guò)實(shí)驗(yàn)看出：在5萬(wàn)行*5數(shù)據(jù)中，通過(guò)calamine引擎讀excel文件比傳統(tǒng)openpyxl快9倍多。

將生成的excel文件發(fā)送到'老板'的郵箱

以163為例，打開(kāi)郵箱設(shè)置對(duì)應(yīng)的smtp服務(wù)

在開(kāi)啟的同時(shí)會(huì)彈出‘授權(quán)密碼’，下面會(huì)用到。

編寫(xiě)python代碼將excel文件發(fā)送指定郵箱

import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.base import MIMEBase
from email.mime.text import MIMEText
from email.utils import COMMASPACE
from email import encoders
import os

# 配置SMTP服務(wù)器和登錄信息
smtp_server = 'smtp.163.com' #使用163郵箱作為發(fā)送服務(wù)器，也可以使用其他郵箱
smtp_port = 25 #端口號(hào)
username = 'your_email@163.com' #你的163郵箱，也可以使用其他郵箱
password = 'xxxxx' #你郵箱對(duì)應(yīng)smtp的授權(quán)密碼
sender_email = 'your_email@163.com'
receiver_emails = ['recipient1@qq.com',]  # 收件人列表

# 創(chuàng)建MIMEMultipart對(duì)象以便容納多部分郵件內(nèi)容
msg = MIMEMultipart()
msg['From'] = sender_email
msg['To'] = COMMASPACE.join(receiver_emails)
msg['Subject'] = '測(cè)試文件'

# 創(chuàng)建郵件正文
body = '這是今天的測(cè)試文檔.'
msg.attach(MIMEText(body))

# 打開(kāi)并讀取Excel文件
filename = 'demo.xlsx'  # 要發(fā)送的Excel文件路徑
with open(filename, 'rb') as f:
    attachment = MIMEBase('application', 'vnd.openxmlformats-officedocument.spreadsheetml.sheet')
    attachment.set_payload(f.read())
    encoders.encode_base64(attachment)  # 對(duì)附件內(nèi)容進(jìn)行base64編碼
    attachment.add_header('Content-Disposition', f'attachment; filename="{os.path.basename(filename)}"')
    msg.attach(attachment)

# 連接SMTP服務(wù)器并發(fā)送郵件
try:
    server = smtplib.SMTP(smtp_server, smtp_port)
    server.starttls()  # 如果使用的是非安全端口，需要啟用TLS加密
    server.login(username, password)
    server.sendmail(sender_email, receiver_emails, msg.as_string())
    server.quit()
    print("Email sent successfully.")
except Exception as e:
    print("Error occurred while sending email:", e)

效果展示