智能云柜 报表 SmartReport

# coding=utf-8
import datetime
from os import path, getcwd, system
from sys import exit
from time import sleep

import pandas as pd
from plotly.subplots import make_subplots
import plotly.graph_objs as go
from plotly.io import write_image
from pandas import read_excel
import PySimpleGUI as sg


def File_choice():
    sg.theme('LightGreen3')
    layout = [sg.Frame('文件选择:', [
        [sg.FileBrowse(' 请选择文件:'), sg.Input('', size=(60, 1), key='_FILEPATH_')],
        [sg.Button(button_text='  确定  ', key='Ok', size=(6, 1), bind_return_key=True),
         sg.Button(button_text='  退出  ', key='Exit', size=(6, 1))],
    ], element_justification='center')]
    window = sg.Window('文件选择', [layout])
    while True:
        event, values = window.read()
        if event in ('Exit', sg.WIN_CLOSED):
            window.close()
            break
        if event == 'Ok':
            FILE_PATH = values['_FILEPATH_']
            if path.exists(FILE_PATH):
                return FILE_PATH
                window.close()
                break
            else:
                sg.popup_auto_close('所选文件不存在!', auto_close_duration=2)

def ReadXLS(PATH='', SheetName=''):
    data = read_excel(PATH, sheet_name=SheetName, header=0, engine='openpyxl')  # 读取excel,Sheetname默认"Table"
    END = data['操作日期'].dt.strftime('%Y/%m/%d').values[0]
    START = data['操作日期'].dt.strftime('%Y/%m/%d').values[-1]
    data['商品规格'].fillna('None', inplace=True)
    data = data.groupby(by=['商品编码', '商品名称', '工单号', '工单数量', '商品规格', '图号'], as_index=False).agg(
        {'每盒数量': 'sum', '刀具已加工产品数量': 'sum', '是否已还': lambda x: x[x == '已还'].count()}, index=False)
    data['寿命'] = (data['刀具已加工产品数量'] / data['是否已还'] / 100).round() * 100  # 100整倍数
    data['工单总数'] = '