Python将excel模板复制到新的excel中，然后插入新数据导出

import copy
import datetime
import numpy
import time
import openpyxl
import pymssql
import requests
import json
from requests_toolbelt import MultipartEncoder
from urllib import parse
import os  # 要想使用路径相关功能需要导入 os 模块
from apscheduler.schedulers.blocking import BlockingScheduler
import pytz


timezone = pytz.timezone('Asia/Shanghai')
scheduler = BlockingScheduler()
def read_db_config():
    BASE_DIR = os.path.abspath(__file__)
    a = BASE_DIR.split("\")[:-2]
    fat = '/'.join(a)
    with open(fat+'/config.json') as file:
        config = json.load(file)
    return config


class downLoadPOutAluminumTasks2():
    def __init__(self) -> object:
        self.sheet_name = '任务表' + time.strftime("%Y-%m-%d")
        self.filename = '任务表' + time.strftime("%Y-%m-%d") + '.xlsx'
        self.out_path = 'd:/data/任务表'+ '.xlsx'
        self.file_path1 = 'D:/data/模板.xlsx'
        # 下面有说明
        self.url = "https://qyapi.weixin.qq.com/cgi-bin/webhook/send?key=xxxxxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxx"
        self.file_path = self.out_path
        pass

    def selectData(self):
        config = read_db_config()
        with pymssql.connect(**config) as connect:
            with connect.cursor() as cursor:
                sql = """
                       SELECT
                        row_number() over(order by t_id asc,ABS(MAX ( plan_out_aluminum_weight ) -SUM ( real_out_aluminum_weight )) desc) as id,
                        t_id,
                        p_apartment,
                            no,
                            MAX ( plan_weight ) AS plan_weight,
                            SUM ( real_weight ) AS real_weight,
                            MAX ( plan_weight ) -SUM ( real_weight ) AS diff_weight,
                            case WHEN ABS(MAX ( plan_weight ) -SUM ( real_weight )) >=5 then convert(nvarchar(20), '一级')
                             WHEN ABS(MAX ( plan_weight ) -SUM ( real_weight )) >=3 then convert(nvarchar(20), '二级')
                            WHEN ABS(MAX ( plan_weight ) -SUM ( real_weight )) >=1 then convert(nvarchar(20), '三级')
                            else '' end as dj,
                            ''
                        FROM
                            tableName1 t1
                            LEFT JOIN (SELECT t_id,p_apartment,p_max,p_min FROM tableName12WHERE p_apartment like '%123%') t2 on t1.slot_no BETWEEN t2.p_min and t2.p_max 
                        WHERE
                         operation_time =cast( DATEADD(day, -1, GETDATE()) as date)
                        GROUP BY
                        t_id,
                        p_apartment,
                            no
                    """
                # 处理数据
                cursor.execute(sql)
                # 搜取所有结果
                results = cursor.fetchall()
                if len(results) == 0:
                    return
                results1 = numpy.delete(results,1, axis=1)
                # 打开模板excel
                wb1 = openpyxl.load_workbook(self.file_path1)
                sheets1 = wb1.get_sheet_names()  # 获取sheet页
                sheets1.remove(sheets1[2])
                for i in sheets1:
                    wb1.remove(wb1[i])
                wb1.save(self.out_path)  # 保存数据
                wb1.close()
                wb2 = openpyxl.load_workbook(self.out_path)
                sheets2 = wb2.get_sheet_names()
                sheet2 = wb2.get_sheet_by_name(sheets2[0])
                sheet2['C3']=datetime.datetime.now()
                sheet2['G3'] = self.getYesterday()
                # 获取并写入数据段信息
                for row in range(5, len(results1) + 5):
                    for col in range(0, len(results1[0])):
                        sheet2[row][col].value= results1[row - 5][col]
                        sheet2[row][col].font = copy.copy(sheet2[5][col].font)
                        sheet2[row][col].border = copy.copy(sheet2[5][col].border)
                        sheet2[row][col].alignment = copy.copy(sheet2[5][col].alignment)
                wb2.save(self.out_path)  # 保存数据
                wb2.close()
                print(f"{datetime.datetime.now()}====>下载成功！")
                self.QYWXSendGroupFile(self.file_path, self.url)

    # 获取每列所占用的最大列宽
    def get_max_col(self, max_list):
        line_list = []
        # i表示行，j代表列
        for j in range(len(max_list[0])):
            line_num = []
            for i in range(len(max_list)):
                line_num.append(max_list[i][j])  # 将每列的宽度存入line_num
            line_list.append(max(line_num))  # 将每列最大宽度存入line_list
        return line_list
    def QYWXSendGroupFile(self, filepath, url):
        """
        发送微信群组机器人文件
        :param filepath: 文件路径
        :param url: 群组机器人WebHook
        :return:
        """
        # url为群组机器人WebHook，配置项
        headers = {
            "content-type": "application/json"
        }
        # 发送文件需要先上传文件获取media_id
        media_id = self.UploadFile(filepath, url)
        msg = {"msgtype": "file", "file": {"media_id": media_id}}
        # 发送请求
        try:
            result = requests.post(url, headers=headers, json=msg)
            return True
        except Exception as e:
            print("企业微信机器人发送文件失败,详细信息:" + str(e))
            return False

    def UploadFile(self, filepath, webHookUrl):
        """
        企业微信机器人上传文件，发送文件前需要先上传--要求文件大小在5B~20M之间
        :param filepath: 文件路径
        :param webHookUrl: 群组机器人WebHook
        :return: media_id
        """
        # url为群组机器人WebHook，配置项
        url = webHookUrl
        params = parse.parse_qs(parse.urlparse(webHookUrl).query)
        webHookKey = params['key'][0]
        upload_url = f'https://qyapi.weixin.qq.com/cgi-bin/webhook/upload_media?key={webHookKey}&type=file'
        headers = {"Accept": "application/json, text/plain, */*", "Accept-Encoding": "gzip, deflate",
                   "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/80.0.3987.100 Safari/537.36"}
        filename = os.path.basename(filepath)
        try:
            multipart = MultipartEncoder(
                fields={'filename': filename, 'filelength': '', 'name': 'media',
                        'media': (filename, open(filepath, 'rb'), 'application/octet-stream')},
                boundary='-------------------------acebdf13572468')
            headers['Content-Type'] = multipart.content_type
            resp = requests.post(upload_url, headers=headers, data=multipart)
            json_res = resp.json()
            if json_res.get('media_id'):
                # print(f"企业微信机器人上传文件成功，file:{filepath}")
                return json_res.get('media_id')
        except Exception as e:
            # print(f"企业微信机器人上传文件失败，file: {filepath}, 详情：{e}")
            print("企业微信机器人上传文件失败,详细信息:" + str(e))
            return ""
    def getYesterday(self):
        today = datetime.date.today()
        oneday = datetime.timedelta(days=1)
        yesterday = today - oneday
        return yesterday
    def run(self):
        # 判断文件是否存在
        if (os.path.exists(self.out_path)):
            # 存在，则删除文件
            os.remove(self.out_path)
        self.selectData()


if __name__ == "__main__":
    env = downLoadPOutAluminumTasks2()
    env.run()

@scheduler.scheduled_job('cron', year="*", month="*", day="*", hour="*", minute="*", second="10")
def request_update_status():
    env = downLoadPOutAluminumTasks2()
    env.run()


scheduler.start()

模板

结果

企业微信机器人api获取方式和config.json配置文件具体见

代码007普通

打赏 收藏 海报 链接