[Python] 自動化辦公 Excel數(shù)據(jù)調(diào)整和樣式修改

轉(zhuǎn)載請注明：陳熹 chenx6542@foxmail.com （簡書號：半為花間酒）
若公眾號內(nèi)轉(zhuǎn)載請聯(lián)系公眾號：早起Python

本例可以學(xué)到的知識點：pandas和openpyxl協(xié)同操作excel文件

數(shù)據(jù)表格：https://pan.baidu.com/s/1mw4IagrRxoCkAvD0Wh5LRA
提取碼：l9e8

讀者的基礎(chǔ)需求如下：

簡單來說就是每天都會對Excel中多個sheet進(jìn)行更新，需要操作完后可以用程序完成第一張sheet 匯總表 的更新

這里假設(shè)我手上現(xiàn)在是只有一個sheet的空表，第一步需要完成的工作是：

一、創(chuàng)建多個sheet和隨機數(shù)據(jù)（非核心步驟）

為了盡可能模擬讀者的生產(chǎn)環(huán)境多了這一步。數(shù)據(jù)已經(jīng)充分則可以跳到第二步

我已經(jīng)把這個excel文件命名好放在桌面的data文件夾中

from openpyxl import load_workbook
import os
import glob
import random

def GetDesktopPath():
    return os.path.join(os.path.expanduser("~"), 'Desktop')

# 調(diào)用glob可以利用通配符獲取指定命名格式的文件
path = glob.glob(f'{GetDesktopPath()}/data/*.xls*')[0]
workbook = load_workbook(filename=path)
sheet_init = workbook.active

接著是創(chuàng)建一些供隨機的內(nèi)容，可以隨意寫

name_lst = ['皮卡丘', '小火龍', '杰尼龜', '妙蛙種子', '風(fēng)速狗', '小拳石', '飛天螳螂']
place_lst = [chr(i).upper() for i in range(97, 123)] 
# 我忘記大寫字母的碼了哈哈哈 這樣變著法換大寫字母
activity_lst = ['橢圓機', '籃球', '足球', '羽毛球', '跳繩']
source_lst = ['朋友介紹', '微信聊天', '網(wǎng)頁彈窗', '其他']

for i in range(30):
    # 不斷去拷貝第一頁并重命名
    sheet = workbook.copy_worksheet(sheet_init)
    sheet.title = f'{i+1}日'
    for j in range(random.randint(10, 30)):
        # 從第三行開始行遍歷
        for row in sheet.iter_rows(min_row=3+j, max_row=3+j):
            info = [f'{j+1}', f'{i+1}日', f'{random.choice(name_lst)}', f'{random.choice(place_lst)}館',
                    f'{random.choice(activity_lst)}', f'{random.choice(source_lst)}', f'{random.randint(1, 10)}',
                    '無', f'{random.choice(["Y", "N"])}', f'{random.choice(["Y", "N"])}', f'{random.choice(["Y", "N"])}']
            # 嵌套循環(huán)，對當(dāng)前行的格子進(jìn)行遍歷把內(nèi)容寫入
            for index, k in enumerate(info):
                row[index].value = k
    print(f'第{i+1}日已完成')

workbook.save(filename=f'{GetDesktopPath()}/data/results.xlsx')

數(shù)據(jù)就創(chuàng)建好了，然后正式進(jìn)入讀者的問題：

二、合并多個sheet并寫入?yún)R總sheet

由于后面多個表的更新后需要按日期順序在匯總表里呈現(xiàn)，因此有一個策略是利用openpyxl按順序遍歷各表然后寫回匯總表

但注意，表格中存在邊框、居中等樣式修改

這種情況下，openpyxl會識別樣式，認(rèn)為這些行是已經(jīng)有數(shù)據(jù)的，故純粹的sheet.append()方法是無法將數(shù)據(jù)寫入這些所謂的空行，而會從沒有樣式的行開始寫入

所以需要在各表寫入的時候不斷計算所在行，并利用sheet.iter_rows()定位

是不是有點麻煩？

因此換了簡單的思路：利用pandas，其方便的地方在于無視表格樣式

path_new = glob.glob(f'{GetDesktopPath()}/data/results.xls*')[0]
workbook_new = load_workbook(filename=path_new)
# 方便獲取總表數(shù)便于遍歷
sheetnames = workbook.sheetnames
df_lst = []

for i in range(1, len(sheetnames)):
    df = pd.read_excel(path_new , encoding='utf-8', sheet_name=i, skiprows=1)
    df_lst.append(df)

# 把獲取的各表縱向合并，注意縱向合并常常需要重置索引
df_total = pd.concat(df_lst,axis=0,ignore_index=True)
# 索引是從0開始，利用索引+1重置各記錄的編號
df_total['編號'] = df_total.index + 1

將生成的表寫回匯總表即可，涉及的內(nèi)容稍微比較復(fù)雜。由于直接使用dataframe.to_excel會覆蓋原excel導(dǎo)致只有一張sheet，其他全部丟失，需要利用pd.ExcelWriter，具體見代碼

刪除原來的 匯總表 并寫入新的 匯總表。因為新寫入的sheet會置于末尾，可以用list.insert(0, list.pop())將最后一個元素置于開頭

writer = pd.ExcelWriter(path_new, engine='openpyxl')
writer.book = workbook
workbook.remove(workbook['匯總表'])
df_total.to_excel(excel_writer=writer, sheet_name=u'匯總表', index=None)
writer.close()
workbook._sheets.insert(0, workbook._sheets.pop())
workbook.save(filename=f'{GetDesktopPath()}/data/results.xlsx')

這就完成了嗎？沒有

pandas的優(yōu)勢“無視樣式”也成為了它的缺陷：寫入文件時沒有樣式信息
因此最后再用openpyxl對第一頁的樣式調(diào)整

三、設(shè)置樣式

# 設(shè)置對齊、線性、邊框、字體
from openpyxl.styles import Alignment
from openpyxl.styles import Side, Border
from openpyxl.styles import Font

sheet = workbook[sheetnames[0]]
sheet.insert_rows(idx=0) # 插入第一行
font = Font(name='宋體', size=18, bold=True)
sheet['A1'] = '皮卡丘體育2020年06月新學(xué)員信息登記表'
sheet['A1'].font = font # 設(shè)置字體大小和加粗

req = ':(\w)'
weight = re.findall(req, sheet.dimensions)[0]
sheet.merge_cells(f'A1:{weight}1')

# 樣式先準(zhǔn)備好
alignment = Alignment(horizontal='center', vertical='center')
side = Side(style='thin', color='000000')
border = Border(left=side, right=side, top=side, bottom=side)

# 遍歷cell設(shè)置樣式
rows = sheet[f'{sheet.dimensions}']
for row in rows:
    for cell in row:
        cell.alignment = alignment
        cell.border = border

# 設(shè)置前兩行的行高
sheet.row_dimensions[1].height = 38
sheet.row_dimensions[2].height = 38

# 設(shè)置列寬
letter_lst = [chr(i+64).upper() for i in range(2, ord(weight)-ord('A')+1+1)]
sheet.column_dimensions['A'].width = 8
for i in letter_lst:
     sheet.column_dimensions[f'{i}'].width = 14

workbook.save(filename=f'{GetDesktopPath()}/data/results.xlsx')

樣式就修改完成了！

完整代碼

from openpyxl import load_workbook
import os
import glob
import random
import pandas as pd
import re
from openpyxl.styles import Alignment
from openpyxl.styles import Side, Border
from openpyxl.styles import Font

def GetDesktopPath():
    return os.path.join(os.path.expanduser("~"), 'Desktop')

path = glob.glob(f'{GetDesktopPath()}/data/*.xls*')[0]
workbook = load_workbook(filename=path)
sheet_init = workbook.active
name_lst = ['皮卡丘', '小火龍', '杰尼龜', '妙蛙種子', '風(fēng)速狗', '小拳石', '飛天螳螂']
place_lst = [chr(i).upper() for i in range(97, 123)]
activity_lst = ['橢圓機', '籃球', '足球', '羽毛球', '跳繩']
source_lst = ['朋友介紹', '微信聊天', '網(wǎng)頁彈窗', '其他']
for i in range(30):
    sheet = workbook.copy_worksheet(sheet_init)
    sheet.title = f'{i+1}日'
    for j in range(random.randint(10, 30)):
        for row in sheet.iter_rows(min_row=3+j, max_row=3+j):
            info = [f'{j+1}', f'{i+1}日', f'{random.choice(name_lst)}', f'{random.choice(place_lst)}館',
                    f'{random.choice(activity_lst)}', f'{random.choice(source_lst)}', f'{random.randint(1, 10)}',
                    '無', f'{random.choice(["Y", "N"])}', f'{random.choice(["Y", "N"])}', f'{random.choice(["Y", "N"])}']
            for index, k in enumerate(info):
                row[index].value = k

workbook.save(filename=f'{GetDesktopPath()}/data/results.xlsx')

path_new = glob.glob(f'{GetDesktopPath()}/data/results.xls*')[0]
# 方便獲取總表數(shù)便于遍歷
workbook = load_workbook(path_new)
sheetnames = workbook.sheetnames
df_lst = []
for i in range(1, len(sheetnames)):
    df = pd.read_excel(path_new, encoding='utf-8', sheet_name=i, skiprows=1)
    df_lst.append(df)

df_total = pd.concat(df_lst, axis=0, ignore_index=True)
df_total['編號'] = df_total.index + 1

writer = pd.ExcelWriter(path_new, engine='openpyxl')
writer.book = workbook
workbook.remove(workbook['匯總表'])
df_total.to_excel(excel_writer=writer, sheet_name=u'匯總表', index=None)
writer.close()
workbook._sheets.insert(0, workbook._sheets.pop())

sheet = workbook[sheetnames[0]]
sheet.insert_rows(idx=0)
font = Font(name='宋體', size=18, bold=True)
sheet['A1'] = '皮卡丘體育2020年06月新學(xué)員信息登記表'
sheet['A1'].font = font

req = ':(\w)'
weight = re.findall(req, sheet.dimensions)[0]
sheet.merge_cells(f'A1:{weight}1')

alignment = Alignment(horizontal='center', vertical='center')
side = Side(style='thin', color='000000')
border = Border(left=side, right=side, top=side, bottom=side)

rows = sheet[f'{sheet.dimensions}']
for row in rows:
    for cell in row:
        cell.alignment = alignment
        cell.border = border

sheet.row_dimensions[1].height = 38
sheet.row_dimensions[2].height = 38

letter_lst = [chr(i+64).upper() for i in range(2, ord(weight)-ord('A')+1+1)]
sheet.column_dimensions['A'].width = 8
for i in letter_lst:
     sheet.column_dimensions[f'{i}'].width = 14

workbook.save(filename=f'{GetDesktopPath()}/data/results.xlsx')
最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容