业务方霸霸

我每天需要复制粘贴各种数据到Excel，这体力活儿太累了！给我整一个自动化的吧！

    经常看到身边的同事(包括自己)在弄Excel表格的时候，各种复制粘贴非常麻烦，尤其是每天做一样的工作，重复枯燥且费时间，于是写个脚本自动更新更新数据并刷新Excel表格。

    我们以这个表格为例，给大家看下表格长啥样儿，我们要更新的列是日期-字段9里面的数据，其中最后两列是套公式自动计算的。

在这里，我们主要使用pandas和openpyxl这两个库进行操作。

思路

    首先表格中肯定会存在制作好的公式，只要填入数据就能够自动刷新，那么我们需要的是利用Python来处理我们需要自行填充的部分

    逐行更新问题：我们需要获取到当前表格的最大行，但是如果直接获取的话会存在一定的问题，如果表格这行的部分单元格有数据的话，Python会认为这一行就不是空行了，会导致数据错乱，因此我们的解决办法是用pandas去获取某列的行数，再此基础上增加一行，以达到自增的目的

    那么思路有了就开始写代码吧：

    首先需要安装相关Python库

pip install pandas
pip install openpyxl

创建一个名为 auto_excel_demo.py的Python文件

接着导包：

from openpyxl import load_workbook # 读取Excel文件from datetime import date, timedelta # 时间处理import pandas as pd # 读取文件import os # 操作本地文件import logging # 打日志

    然后新建个类，初始方法，两个方法，初始方法主要就是一些基本的配置参数、时间、文件名、文件路径等。第一个方法是获取当前表格某列的最大行数，在此基础上自增行数。第二个方法就是写入数据了。

class Writing_to_excel():def __init__(self):"""
      基本参数配置：时间参数、文件路径、源文件名、临时文件名、工作表名、字段名
      """
       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 = '日期'

    第一个方法主要是利用pandas获取日期这列最大行数，看我的表格，最后两列是有数据的，如果直接用dataframe.shape[0]去获取的话，会把这列的NaT值也算作行数，所以我们要做一下dropna处理，这样我们就能获取到一个自增的行数了。

    另外有个问题，如果源文件直接被pandas打开的话，数据会被清除格式，导致公式失效，所以我们要用os.system拷贝一个副本给我们操作，获取完行数之后删掉副本。

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_rowreturn

接下来就是填入数据的方法：

def write_data_to_excel(self):"""
      写入数据
      :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}'] = '测试一下1'
       stotal[f'C{self.next_row}'] = '测试一下2'
       stotal[f'D{self.next_row}'] = '测试一下3'
       stotal[f'E{self.next_row}'] = '测试一下4'
       stotal[f'F{self.next_row}'] = '测试一下5'
       stotal[f'G{self.next_row}'] = '测试一下6'
       stotal[f'H{self.next_row}'] = '测试一下7'
       stotal[f'I{self.next_row}'] = '测试一下8'
       stotal[f'J{self.next_row}'] = '测试一下9'
       wb.save(f'{self.filepath}{self.filename}')

    用我们动态获取到的最大行数，来填入每个单元格的数据，这个就比较体力活了，暂时没想到更好的方法。

    这里填入的是一些自定义的测试数据，后期脚本上线后，数据的获取可以多种多样，从数据库获取、爬虫数据、接口数据都可以写入到Excel内。

最后，运行脚本

if __name__ == '__main__':try:
       wte = Writing_to_excel()
       wte.get_next_row()
       wte.write_data_to_excel()
       logging.info(f"{wte.filename}   文件数据操作完成")
       print(f"{wte.filename} 文件数据操作完成")except Exception as e:
       logging.error(f"{wte.filename}操作失败！原因：{e}")
       print(e)

    细心的同学可以发现，我做了个简单的日志，方便查看报错的情况。

这里附上运行结果：

最后，完整代码如下：

from openpyxl import load_workbookfrom datetime import date, timedeltaimport pandas as pdimport osimport 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):"""
      基本参数配置：时间参数、文件路径、源文件名、临时文件名、工作表名、字段名
      """
       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_rowreturndef write_data_to_excel(self):"""
      写入数据
      :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}   文件数据操作完成")
       print(f"{wte.filename} 文件数据操作完成")except Exception as e:
       logging.error(f"{wte.filename}操作失败！原因：{e}")
       print(e)