經(jīng)常看到身邊的同事（包括自己）在弄Excel表格的時(shí)候，各種復(fù)制粘貼非常麻煩团驱，尤其是每天做一樣的工作，重復(fù)枯燥費(fèi)時(shí)間空凸，于是打算寫個(gè)小腳本自動(dòng)更新Excel表格嚎花。

image.png

我們以這個(gè)表格為例，給大家看下表格長啥樣兒呀洲，我們要更新的列是日期-字段9里面的數(shù)據(jù)紊选，其中最后兩列是套公式自動(dòng)計(jì)算的。

我們這里主要使用pandas和openpyxl這兩個(gè)庫進(jìn)行操作道逗。

思路

• 首先表格中肯定會存在制作好的公式兵罢，只要填入數(shù)據(jù)就能夠自動(dòng)刷新，那么我們需要的是利用Python來處理我們需要自行填充的部分
• 逐行更新問題：我們需要獲取到當(dāng)前表格的最大行滓窍，但是如果直接獲取的話會存在一定的問題卖词，如果表格這行的部分單元格有數(shù)據(jù)的話，Python會認(rèn)為這一行就不是空行了吏夯，會導(dǎo)致數(shù)據(jù)錯(cuò)亂此蜈，因此我們的解決辦法是用pandas去獲取某列的行數(shù)，再此基礎(chǔ)上增加一行噪生，以達(dá)到自增的目的

那么思路有了就開始寫代碼吧
首先導(dǎo)包：

from openpyxl import load_workbook # 讀取Excel文件
from datetime import date, timedelta # 時(shí)間處理
import pandas as pd # 讀取文件
import os # 操作本地文件
import logging # 打日志

然后新建個(gè)類裆赵，初始方法，兩個(gè)方法跺嗽，初始方法主要就是一些基本的配置參數(shù)战授、時(shí)間啊、文件名桨嫁、文件路徑之類的陈醒。第一個(gè)方法是獲取當(dāng)前表格某列的最大行數(shù)，在此基礎(chǔ)上自增行數(shù)瞧甩。第二個(gè)方法就是寫入數(shù)據(jù)了钉跷。

class Writing_to_excel():
    def __init__(self):
        """
        基本參數(shù)配置：時(shí)間參數(shù)、文件路徑肚逸、源文件名爷辙、臨時(shí)文件名、工作表名朦促、字段名
        """
        self.data_ytd = (date.today() + timedelta(days=-1)).strftime("%Y/%m/%d")
        self.filepath = '/Users/jason/Desktop/'
        self.filename = 'test_data.xlsx'
        self.filename_replace = 'tmp_test_data.xlsx'
        self.sheet_name = '匯總'
        self.column_name = '日期'

• 第一個(gè)方法主要是利用pandas獲取日期這列最大行數(shù)膝晾，看我的表格，最后兩列是有數(shù)據(jù)的务冕，如果直接用dataframe.shape[0]去獲取的話血当，會把這列的NaT值也算作行數(shù)，所以我們要做一下dropna處理，這樣我們就能獲取到一個(gè)自增的行數(shù)了臊旭。
• 另外有個(gè)問題落恼，如果源文件直接被pandas打開的話，數(shù)據(jù)會被清除格式离熏，導(dǎo)致公式失效佳谦，所以我們要用os.system拷貝一個(gè)副本給我們操作，獲取完行數(shù)之后刪掉副本滋戳。

    def get_next_row(self):
        """
        拷貝出新文件钻蔑，獲取行號
        防止破壞源文件
        :return: next_row
        """
        os.system(f'cp {self.filepath}{self.filename} {self.filepath}{self.filename_replace}')
        pd_data = pd.read_excel(f'{self.filepath}{self.filename_replace}', sheet_name=self.sheet_name)
        col1 = pd_data[[self.column_name]].dropna()
        _maxrow = col1.shape[0]
        next_row = _maxrow + 2
        pd_data.to_excel(f'{self.filepath}{self.filename_replace}', sheet_name=self.sheet_name)
        os.system(f'rm {self.filepath}{self.filename_replace}')
        self.next_row = next_row
        return

接下來就是填入數(shù)據(jù)的方法：

    def write_data_to_excel(self):
        """
        寫入數(shù)據(jù)
        :return:
        """
        wb = load_workbook(f'{self.filepath}{self.filename}')
        stotal = wb['匯總']
        stotal[f'A{self.next_row}'] = self.data_ytd
        stotal[f'B{self.next_row}'] = 77
        stotal[f'C{self.next_row}'] = 77
        stotal[f'D{self.next_row}'] = 77
        stotal[f'E{self.next_row}'] = 77
        stotal[f'F{self.next_row}'] = 77
        stotal[f'G{self.next_row}'] = 77
        stotal[f'H{self.next_row}'] = 77
        stotal[f'I{self.next_row}'] = 77
        stotal[f'J{self.next_row}'] = 77
        wb.save(f'{self.filepath}{self.filename}')

用我們動(dòng)態(tài)獲取到的最大行數(shù)，來填入每個(gè)單元格的數(shù)據(jù)奸鸯，這個(gè)就比較體力活了咪笑，暫時(shí)沒想到更好的方法。最后save文件

最后娄涩，運(yùn)行腳本

if __name__ == '__main__':
    try:
        wte = Writing_to_excel()
        wte.get_next_row()
        wte.write_data_to_excel()
        logging.info(f"{wte.filename}   文件數(shù)據(jù)操作完成")
        print(f"{wte.filename}  文件數(shù)據(jù)操作完成")
    except Exception as e:
        logging.error(f"{wte.filename}操作失敶芭！原因：{e}")
        print(e)

細(xì)心的同學(xué)可以發(fā)現(xiàn)钝满，我做了個(gè)簡單的日志兜粘，方便查看報(bào)錯(cuò)的情況申窘。
上運(yùn)行結(jié)果：

image.png

最后弯蚜，完整代碼如下：

from openpyxl import load_workbook
from datetime import date, timedelta
import pandas as pd
import os
import logging

LOG_FORMAT = "%(asctime)s %(name)s %(levelname)s %(pathname)s %(message)s "
DATE_FORMAT = '%Y-%m-%d %H:%M:%S'
logging.basicConfig(level=logging.DEBUG, format=LOG_FORMAT, datefmt=DATE_FORMAT, filename="excel_operate.log")


class Writing_to_excel():
    def __init__(self):
        """
        基本參數(shù)配置：時(shí)間參數(shù)、文件路徑剃法、源文件名碎捺、臨時(shí)文件名、工作表名贷洲、字段名
        """
        self.data_ytd = (date.today() + timedelta(days=-1)).strftime("%Y/%m/%d")
        self.filepath = '/Users/jason/Desktop/'
        self.filename = 'test_data.xlsx'
        self.filename_replace = 'tmp_test_data.xlsx'
        self.sheet_name = '匯總'
        self.column_name = '日期'

    def get_next_row(self):
        """
        拷貝出新文件收厨，獲取行號
        防止破壞源文件
        :return: next_row
        """
        os.system(f'cp {self.filepath}{self.filename} {self.filepath}{self.filename_replace}')
        pd_data = pd.read_excel(f'{self.filepath}{self.filename_replace}', sheet_name=self.sheet_name)
        col1 = pd_data[[self.column_name]].dropna()
        _maxrow = col1.shape[0]
        next_row = _maxrow + 2
        pd_data.to_excel(f'{self.filepath}{self.filename_replace}', sheet_name=self.sheet_name)
        os.system(f'rm {self.filepath}{self.filename_replace}')
        self.next_row = next_row
        return

    def write_data_to_excel(self):
        """
        寫入數(shù)據(jù)
        :return:
        """
        wb = load_workbook(f'{self.filepath}{self.filename}')
        stotal = wb[self.sheet_name]
        stotal[f'A{self.next_row}'] = self.data_ytd
        stotal[f'B{self.next_row}'] = 77
        stotal[f'C{self.next_row}'] = 77
        stotal[f'D{self.next_row}'] = 77
        stotal[f'E{self.next_row}'] = 77
        stotal[f'F{self.next_row}'] = 77
        stotal[f'G{self.next_row}'] = 77
        stotal[f'H{self.next_row}'] = 77
        stotal[f'I{self.next_row}'] = 77
        stotal[f'J{self.next_row}'] = 77
        wb.save(f'{self.filepath}{self.filename}')


if __name__ == '__main__':
    try:
        wte = Writing_to_excel()
        wte.get_next_row()
        wte.write_data_to_excel()
        logging.info(f"{wte.filename}   文件數(shù)據(jù)操作完成")
        print(f"{wte.filename}  文件數(shù)據(jù)操作完成")
    except Exception as e:
        logging.error(f"{wte.filename}操作失敗优构！原因：{e}")
        print(e)