1.Excel Code

import os
import time
import re
import win32com.client

def dealpath(pathname='') -> str:
"""
deal with windows file path

:param pathname: path name of excel  
:return: path  
"""  
if pathname:  
    pathname = pathname.strip()  
if pathname:  
    pathname = r'%s' % pathname  
    pathname = pathname.replace('/', '\\\\')  
    pathname = os.path.abspath(pathname)  
    if pathname.find(":\\\\") == -1:  
        pathname = os.path.join(os.getcwd(), pathname)  
return pathname

def tuple_to_list(data: tuple) -> list:
"""
convert tuple to list

:param data: One/two-dimensional tuple  
:return: list  
"""  
ret = list(data)  
for index in range(len(ret)):  
    if isinstance(ret\[index\], tuple):  
        ret\[index\] = list(ret\[index\])  
return ret

class EasyExcel(object):

def \_\_init\_\_(self, visible=False):  
    """initial excel application"""  
    self.m\_filename = ''  
    self.m\_book = None  
    self.m\_exists = False  
    self.m\_excel = win32com.client.DispatchEx('Excel.Application')  
    # self.m\_excel.Visible = visible  
    self.m\_excel.DisplayAlerts = False

@staticmethod  
def getColDict():  
    """

    :return:  
    """  
    dict = {'A': 1, 'B': 2, 'C': 3,  
            'D': 4, 'E': 5, 'F': 6,  
            'G': 7, 'H': 8, 'I': 9,  
            'J': 10, 'K': 11, 'L': 12,  
            'M': 13, 'N': 14, 'O': 15,  
            'P': 16, 'Q': 17, 'R': 18,  
            'S': 19, 'T': 20, 'U': 21,  
            'V': 22, 'W': 23, 'X': 24,  
            'Y': 25, 'Z': 26, 'AA': 27,  
            'AB': 28, 'AC': 29, 'AD': 30,  
            'AE': 31, 'AF': 32, 'AG': 33,  
            'AH': 34, 'AI': 35, 'AJ': 36,  
            'AK': 37, 'AL': 38, 'AM': 39,  
            'AN': 40, 'AO': 41, 'AP': 42,  
            'AQ': 43, 'AR': 44, 'AS': 45,  
            'AT': 46, 'AU': 47, 'AV': 48,  
            'AW': 49, 'AX': 50, 'AY': 51,  
            'AZ': 52, 'BA': 53, 'BB': 54,  
            'BC': 55, 'BD': 56, 'BE': 57,  
            'BF': 58, 'BG': 59, }  
    return dict

def open(self, filename=''):  
    """open excel file

    :param filename:  
    :return:  
    """  
    if getattr(self, 'm\_book', False):  
        self.m\_book.Close()  
    self.m\_filename = dealpath(filename) or ''  
    self.m\_exists = os.path.isfile(self.m\_filename)  
    if not self.m\_filename or not self.m\_exists:  
        self.m\_book = self.m\_excel.Workbooks.Add()  
    else:  
        self.m\_book = self.m\_excel.Workbooks.Open(self.m\_filename)

    return self

def reset(self):  
    """  
    reset class  
    """  
    self.m\_book = None  
    self.m\_excel = None  
    self.m\_filename = ''

def save(self, newfile=''):  
    """  
    save excel

    :param newfile: new file path or name  
    :return:  
    """  
    assert type(newfile) is str, ''  
    newfile = dealpath(newfile) or self.m\_filename  
    if not newfile or (self.m\_exists and newfile == self.m\_filename):  
        self.m\_book.Save()  
    else:  
        pathname = os.path.dirname(newfile)  
        # 不存在则创建  
        if not os.path.isdir(pathname):  
            os.makedirs(pathname)  
            self.m\_filename = newfile  
            self.m\_book.SaveAs(newfile)  
        else:  
            self.m\_filename = newfile  
            self.m\_book.SaveAs(newfile)  
    self.close()

def close(self):  
    """  
    close the application  
    """

    self.m\_book.Close(SaveChanges=1)  
    self.m\_excel.Quit()  
    time.sleep(2)  
    self.reset()

def addSheet(self, sheetname=None):  
    """add new sheet, the name of sheet can be modify,but the workbook can't

    :param sheetname: sheet name  
    :return:  
    """  
    sht = self.m\_book.Worksheets.Add()  
    sht.Name = sheetname if sheetname else sht.Name  
    return sht

def getSheet(self, sheet):  
    """get the sheet object by the sheet index

    :param sheet:sheet index or sheet name  
    :return: sheet object  
    """  
    return self.m\_book.Worksheets\[sheet\]

def getSheetCount(self):  
    """

    get the number of sheet  
    """  
    return self.m\_book.Worksheets.Count

def getCell(self, sheet, row=1, col=1):  
    """  
    get the cell object

    :param sheet:  sheet name  
    :param row: row index  
    :param col: col index  
    :return: cell data  
    """  
    # assert row > 0 and col > 0, 'the row and column index must bigger then 0'  
    return self.getSheet(sheet).Cells(row, col)

def getRow(self, sheet, row=1):  
    """  
    get the row object

    :param sheet: sheet name  
    :param row: row index  
    :return: row data  
    """  
    assert row > 0, 'the row index must bigger then 0'  
    return self.getSheet(sheet).Rows(row)

def getCol(self, sheet, col):  
    """get the column object

    :param sheet: sheet name or index  
    :param col: column index  
    :return: column data  
    """  
    # assert col > 0, 'the column index must bigger then 0'  
    return self.getSheet(sheet).Columns(col)

def getRange(self, sheet, row1, col1, row2, col2):  
    """get the range object

    :param sheet:sheet name or sheet index  
    :param row1:  
    :param col1:  
    :param row2:  
    :param col2:  
    :return:  
    """  
    sht = self.getSheet(sheet)  
    return sht.Range(self.getCell(sheet, row1, col1), self.getCell(sheet, row2, col2))

def getCellValue(self, sheet, row, col):  
    """Get value of one cell

    :param sheet: sheet name or sheet index  
    :param row: row index  
    :param col: column index  
    :return:  
    """  
    return self.getCell(sheet, row, col).Value

def setCellValue(self, sheet, row, col, value):  
    """set value of one cell

    :param sheet:  
    :param row:  
    :param col:  
    :param value:  
    """  
    self.getCell(sheet, row, col).Value = value

def getRowValue(self, sheet, row):  
    """get the row values

    :param sheet:  
    :param row:  
    :return:  
    """  
    return self.getRow(sheet, row).Value

def setRowValue(self, sheet, row, values):  
    """set the row values

    :param sheet:  
    :param row:  
    :param values:  
    """  
    self.getRow(sheet, row).Value = values

def getColValue(self, sheet, col):  
    """get the row values

    :param sheet:  
    :param col:  
    :return:  
    """  
    return self.getCol(sheet, col).Value

def setColValue(self, sheet, col, values):  
    """set the row values

    :param sheet:  sheet name or sheet index  
    :param col: column index  
    :param values: value string  
    """  
    self.getCol(sheet, col).Value = values

def getRangeValue(self, sheet, row1, col1, row2, col2):  
    """  
    return a tuples of tuple)

    :param sheet: sheet name or index  
    :param row1: start row index  
    :param col1: start col index  
    :param row2: end row index  
    :param col2: end col index  
    :return:  
    """  
    return self.getRange(sheet, row1, col1, row2, col2).Value

def setRangeValue(self, sheet, row1, col1, data):  
    """  
    set the range values

    :param sheet: sheet name or index  
    :param row1: Start row index  
    :param col1: Start col index  
    :param data:Two-dimensional array \[\['A','B'\],\['C','D'\]\]  
    """  
    row2 = row1 + len(data) - 1  
    if data\[0\]:  
        col2 = col1 + len(data\[0\]) - 1  
    else:  
        col2 = col1

    range = self.getRange(sheet, row1, col1, row2, col2)  
    range.ClearContents()  
    range.Value = data

def getMaxRow(self, sheet):  
    """get the max row number, not the count of used row number

    :param sheet:sheet name or sheet index  
    :return:  
    """  
    return self.getSheet(sheet).Rows.Count

def getCellName(self, sheet, row, col):  
    """

    :param sheet:  
    :param row:  
    :param col:  
    :return:  
    """  
    addr = self.getCell(sheet, row, col).Address  
    return re.sub(r"\[\\s+\\.\\!\\/\_,$%^\*(+\\"\\')\]+|\[+——()?【】“”！，。？、~@#￥%……&\*（）\]+'", "", addr)

def getMaxCol(self, sheet):  
    """get the max col number, not the count of used col number

    :param sheet:  
    :return:  
    """  
    return self.getSheet(sheet).Columns.Count

def clearCell(self, sheet, row, col):  
    """clear the content of the cell

    :param sheet:  
    :param row:  
    :param col:  
    """  
    self.getCell(sheet, row, col).Clear()

def deleteCell(self, sheet, row, col):  
    """delete the cell

    :param sheet:sheet name or sheet index  
    :param row:  
    :param col:  
    """  
    self.getCell(sheet, row, col).Delete()

def clearRow(self, sheet, row):  
    """clear the content of the row

    :param sheet:  
    :param row:  
    """  
    self.getRow(sheet, row).Clear()

def deleteRow(self, sheet, row):  
    """delete the row

    :param sheet:  
    :param row:  
    """  
    self.getRow(sheet, row).Delete()

def clearCol(self, sheet, col):  
    """clear the col

    :param sheet:sheet name or sheet index  
    :param col:column index  
    """  
    self.getCol(sheet, col).Clear()

def deleteCol(self, sheet, col):  
    """delete the col

    :param sheet:sheet name or sheet index  
    :param col:column index  
    """  
    self.getCol(sheet, col).Delete()

def deleteColByName(self, sheet, col):  
    """

    :param sheet:sheet name or sheet index  
    :param col:  
    """  
    self.getColByName(sheet, col).Delete()

def getColByName(self, sheet, col):  
    """

    :param sheet:  
    :param col:  
    :return:  
    """  
    return self.getSheet(sheet).Columns\[col\]

def clearSheet(self, sheet):  
    """clear the hole sheet  
    just all the content and formula  
    :param sheet:  
    """  
    maxrow = self.getMaxRow(sheet)  
    maxcol = self.getMaxCol(sheet)  
    rh = self.getRange(sheet, 1, 1, maxrow, maxcol)  
    return rh.ClearContents()

def deleteSheet(self, sheet):  
    """delete the hole sheet  
    :param sheet:  
    """  
    self.getSheet(sheet).Delete()

def deleteRows(self, sheet, fromRow, count=1):  
    """delete count rows of the sheet  
    :param sheet:  
    :param fromRow:  
    :param count:  
    :return:  
    """  
    maxrow = self.getMaxRow(sheet)  
    maxcol = self.getMaxCol(sheet)  
    endrow = fromRow + count - 1  
    if fromRow > maxrow or endrow < 1:  
        return  
    self.getRange(sheet, fromRow, 1, endrow, maxcol).Delete()

def deleteCols(self, sheet, fromCol, count=1):  
    """delete count cols of the sheet  
    :param sheet:  
    :param fromCol:  
    :param count:  
    :return:  
    """  
    maxrow = self.getMaxRow(sheet)  
    maxcol = self.getMaxCol(sheet)  
    endcol = fromCol + count - 1  
    if fromCol > maxcol or endcol < 1:  
        return  
    self.getRange(sheet, 1, fromCol, maxrow, endcol).Delete()

def clearRange(self, sheet, row1, col1, row2, col2):  
    """clear range of sheet  
    :param sheet:  
    :param row1:  
    :param col1:  
    :param row2:  
    :param col2:  
    """  
    self.getRange(sheet, row1, col1, row2, col2).Clear()

def copyPasteRange(self, sheet, row1, col1, row2, col2):  
    """  
     copy sheet range to another sheet, keep the same sheet name

    :param sheet:sheet name or sheet index  
    :param row1:start row  
    :param col1:start column  
    :param row2:end row  
    :param col2:end column  
    """  
    self.getRange(sheet, row1, col1, row2, col2).Copy()  
    sh\_name = self.getSheet(sheet).Name  
    self.addSheet(sheetname='Sheet1')  
    self.pasteRange(sheet='Sheet1', row1=row1, col1=col1, row2=row2, col2=col2)  
    self.deleteSheet(sh\_name)  
    self.getSheet(sheet='Sheet1').Name = sh\_name

def sortSheet(self, sheet, col, ascending=False):  
    """  
    sort sheet by column name or column index

    :param sheet:sheet name or sheet index  
    :param col:column name or column index  
    :param ascending:Ascending/descending  
    :return:  
    """  
    maxrow = self.getMaxRow(sheet)  
    maxcol = self.getMaxCol(sheet)

    if isinstance(col, int):  
        cell\_name = self.getCellName(sheet, 1, col)  
    else:  
        dic = self.getColDict()  
        cell\_name = self.getCellName(sheet, 1, dic\[col\])

    sh = self.m\_book.Worksheets\[sheet\]

    ra = sh.Range(cell\_name)  
    if ascending:  
        order = 1  
    else:  
        order = 2  
    self.getRange(sheet, 1, 1, maxrow, maxcol).Sort(Key1=ra, Header=1, Order1=order, Orientation=1)  
    return self

def keepCol(self, sheet, row=5):  
    """  
    keep some columns and delete the other columns  
    :param sheet:sheet name  
    :param row: row index  
    """  
    maxcol = 60  
    for item in range(maxcol, 0, -1):  
        value = self.getCellValue(sheet, row, item)  
        if value is None or value == '':  
            self.deleteCol(sheet=sheet, col=item)  
    return self

def getLastRow(self, sheet, col=None):  
    """get the last row index

    :param sheet:sheet name or sheet index  
    :param col:column index  
    :return:  
    """  
    maxrow = self.getMaxRow(sheet)

    for item in range(maxrow):  
        item += 1  
        if self.getCellValue(sheet=sheet, row=item, col=col) is None:  
            return item - 1

def getLast(self, sheet, col=None, start=None):  
    """get the last row index

    :param sheet:sheet name or sheet index  
    :param col:column index  
    :return:  
    """  
    maxrow = self.getMaxRow(sheet)

    for item in range(start, maxrow):  
        item += 1  
        if self.getCellValue(sheet=sheet, row=item, col=col) is None:  
            return item - 1

def pasteRange(self, sheet, row1, col1, row2, col2):  
    """  
    paste range

    :param sheet: sheet name or sheet index  
    :param row1: start row  
    :param col1: start column  
    :param row2: end row  
    :param col2: end column  
    :return:  
    """  
    return self.getSheet(sheet).Paste(self.getRange(sheet, row1, col1, row2, col2))

def insertRow(self, sheet, row, data):  
    """  
    insert one row

    :param sheet: sheet name or sheet index  
    :param row: row index  
    :param data: data list  
    """  
    lent = len(data\[0\])  
    sh = self.m\_book.Worksheets\[sheet\]  
    sh.Rows(row).Insert(1)  
    range = self.getRange(sheet, row, 1, row, lent)  
    range.Clear()  
    range.Value = data\[0\]

def dataShift(self, sheet, fromc, tocol, colcount, incheader=False):  
    """  
    data shift from a range to another

    :param sheet:sheet name or sheet index  
    :param fromc: from column  
    :param tocol: to column  
    :param colcount: column count  
    :param incheader:True -include header;False -exclude header  
    """  
    if incheader:  
        srow = 1  
    else:  
        srow = 2

    erow = self.getLastRow(sheet=sheet, col=fromc)

    ecol = fromc + colcount  
    etocol = tocol + colcount

    self.getRange(sheet, srow, fromc, erow, ecol).Cut()  
    self.pasteRange(sheet, srow, tocol, erow, etocol)

def dataShift1(self, sheet, fromc, tocol, starow, colcount, rowcount):  
    """  
    data shift from one range to another

    :param sheet: sheet name or sheet index  
    :param fromc:from column  
    :param tocol:to column  
    :param starow: start row  
    :param colcount: column count  
    :param rowcount: row count  
    """  
    srow = starow  
    erow = starow + rowcount - 1

    ecol = fromc + colcount - 1  
    etocol = tocol + colcount - 1

    self.getRange(sheet, srow, fromc, erow, ecol).Cut()  
    self.pasteRange(sheet, srow, tocol, erow, etocol)

def negativeData(self, sheet, collist=None, includeHeader=False):  
    """  
    Some columns, take the opposite number

    :param sheet:sheet name or sheet index  
    :param collist: column list to process  
    :param includeHeader: True -include header; False -exclude header  
    :return:  
    """  
    if collist is None:  
        return  
    if includeHeader:  
        srow = 1  
    else:  
        srow = 2

    lastrow = self.getLastRow(sheet=sheet, col=collist\[0\])  
    ind = srow  
    for item in collist:  
        ind = srow  
        while ind <= lastrow:  
            emp = self.m\_book.Worksheets(sheet).Cells(ind, item).value  
            self.m\_book.Worksheets(sheet).Cells(ind, item).value = -emp  
            ind += 1

def del\_colwithcolor(self, sheet, column, includehead=False):  
    """  
    delete rows that with color

    :param sheet:sheet name or sheet index  
    :param column: column index  
    :param includehead: True -include header; False -exclude header  
    """  
    lastrow = self.getLastRow(sheet=sheet, col=column)

    if includehead:  
        srow = 1  
    else:  
        srow = 2  
    sh = self.getSheet(sheet)  
    for item in range(lastrow, srow-1, -1):  
        if sh.Cells(item, column).Interior.ColorIndex > 0:  
            self.deleteRow(sheet, item)

def del\_rowby(self, sheet, column, includhead=False, null=False):  
    """  
    delete rows by one column value is null or not null

    :param sheet: sheet name or sheet index  
    :param column: column index  
    :param includhead: True- include header; False - exclude header  
    :param null:True -when not null ->delete; False- when null ->delete  
    """  
    lastrow = self.getLastRow(sheet=sheet, col=1)  
    if includhead:  
        srow = 1  
    else:  
        srow = 2  
    sh = self.getSheet(sheet)  
    for item in range(lastrow, srow-1, -1):  
        if null:  
            if sh.Cells(item, column).value is not None:  
                self.deleteRow(sheet, item)  
        else:  
            if sh.Cells(item, column).value is None:  
                self.deleteRow(sheet, item)

def del\_rowStartWith(self, sheet, col, st='', tof=False, head=False):  
    """  
    delete rows by one column value, which start/or not start with a str value

    :param sheet: sheet name or sheet index  
    :param col: value condition column  
    :param st: string  
    :param tof:Ture - start with; False- not start with  
    :param head:Ture -  include header; False - not include header  
    """  
    lastrow = self.getLastRow(sheet=sheet, col=1)  
    if head:  
        srow = 1  
    else:  
        srow = 2  
    sh = self.getSheet(sheet)  
    for item in range(lastrow, srow-1, -1):  
        if tof:  
            if str(sh.Cells(item, col).value).startswith(st):  
                self.deleteRow(sheet, item)  
        else:  
            if not str(sh.Cells(item, col).value).startswith(st):  
                self.deleteRow(sheet, item)

def plus\_cols(self, sheet, cols=None, rescol='', startrow=1):  
    """  
    Write the sum of several columns into another column

    :param sheet:sheet name or sheet index  
    :param cols: columns index list for plus  
    :param rescol: column index for total to write  
    :param startrow: startrow index  
    :return:  
    """  
    if cols is None:  
        return  
    sh = self.getSheet(sheet)  
    while sh.Cells(startrow, cols\[0\]).value is not None:  
        val = 0  
        for item in cols:  
            val = val + sh.Cells(startrow, item).value  
        sh.Cells(startrow, rescol).value = val  
        startrow += 1

def cls\_not\_head(self, sheet, col):  
    """  
    clear columns not include the first row

    :param sheet: sheet name or sheet index  
    :param col: columns index(not columns name)  
    """  
    head = self.getSheet(sheet).Cells(1, col).value  
    self.clearCol(sheet, col)  
    self.getSheet(sheet).Cells(1, col).value = head

def with\_color(self, sheet, col, row, date=False, pcstr=''):  
    """  
    special function: get dic of \[col\].value and \[col+1\].value with colors

    :param sheet: sheet name or sheet index  
    :param col: column index  
    :param row: row index  
    :param date: if column value is date or not  
    :return: dic  
    """  
    dic = {}  
    strow = row  
    maxrow = self.getMaxRow(sheet)  
    sh = self.getSheet(sheet)  
    while strow <= maxrow and sh.Cells(strow, col).value is not None:  
        if sh.Cells(strow, col).Interior.ColorIndex > 0:  
            if date:  
                dic\[sh.Cells(strow, col).value.strftime(pcstr)\] = sh.Cells(strow, col+1).value  
            else:  
                dic\[sh.Cells(strow, col).value\] = sh.Cells(strow, col+1).value

        strow = strow + 1  
    return dic

2. Config Code

import os.path
import datetime as dt
from pathhandle import *
import configparser
import xlrd

class Config(object):
def __init__(self):
"""
:self.xls ->excel Suffix
:self.xlsx ->excel Suffix
:self.rfkrtim ->excel Prefix
:self.customer ->excel Prefix
:self.recon ->excel Prefix
:self.input ->excel file name[input.xlsx]
:self.emp ->excel file name[EMP.xlsx]
:self.inputfm ->Date format of excel
:self.template ->file folder
:self.result ->file folder
:self.pend ->Suffix part of file name
:self.path ->work folder to store excel files[user config]
:self.path1 ->path of file [RFKRTIMyyyy.mm.dd.xls]
:self.path2 ->path of file [S_ALR_87012357 customer_yyyy.mm.dd.xlsx]
:self.path3 ->path of file [FBL3N recon_yyyy.mm.dd.xlsx]
:self.path4 ->same as path1
:self.path5 ->path of file [input.xls]
:self.path6 ->path of file [2021 Summary.xlsx]
:self.path7 ->path of file [EMP.xlsx]
:self.path8 ->path of file [RFKRTIMyyyy.mm.ddpending.xls](second time download)
:self.path9 ->path of file [건별세금계산서_20210401.xlsx]
:self.path10 ->path of file [Customer master.xlsx]
:self.path11 ->path of file [SB upload20210401.xlsx]
:self.folder ->folder name generate by date of yesterday [YYYYMMDD]
:self.work_folder ->path [self.path + self.folder]
:self.sheet4 ->sheet name of file [RFKRTIMyyyy.mm.dd.xls] format [YYYY.MM.DD]
:self.sh_pending ->sheet name of file [RFKRTIMyyyy.mm.dd.xls] [Pending] [customer config]
:self.sh_final ->sheet name of file [2021 Summary.xlsx] [Pending_final]
:self.sapexe ->path of sap exe [customer config]
:self.h_budat ->column name [Posting Date]
:self.user ->user name of sap
:self.passw ->password of sap
:self.downloadp -> download path of sap
:self.window ->window name of sap
:self.yest ->date of yesterday
:self.sh_rerun ->sheet name of file[RFKRTIMyyyy.mm.ddpending.xls]
:self.sh_reco -sheet name of file[2021 Summary.xlsx] [Reconciliation]
:self.sapdatefm -> date format of sap
:self.fmstr ->python date format string depending on sap date
:self.yyyymmdd ->header info of sheet [YYYY.MM.DD]
:self.pending ->header info of sheet [Pending]
:self.timestamp = []
:self.currentmonth = sheet header of current month[YYYY.MM]
:self.holiday_dict ->holiday list 20号 13号
:self.post_date ->posting date list
"""
self.xls = '.xls'
self.xlsx = '.xlsx'
self.rfkrtim = 'RFKRTIM'
self.customer = 'S_ALR_87012357 customer_'
self.recon = 'FBL3N recon_'
self.input = 'input'
self.emp = 'EMP'
self.inputfm = '%Y.%m.%d'
self.template = 'template'
self.result = 'result'
self.pend = 'pending'
self.automatic = 'Y'

    self.visible = ''  
    self.log1 = ''  
    self.log2 = ''  
    self.read = ''  
    self.path = ''  
    self.path1 = ''  
    self.path1\_bk = ''  
    self.path2 = ''  
    self.path2\_bk = ''  
    self.path3 = ''  
    self.path3\_bk = ''  
    self.path4 = ''  
    self.path5 = ''  
    self.path6 = ''  
    self.path7 = ''  
    self.path8 = ''  
    self.path9 = ''  
    self.path10 = ''  
    self.path11 = ''  
    self.path12 = ''  
    self.folder = ''  
    self.work\_folder = ''

    self.sheet4 = ''  
    self.sh\_pending = ''  
    self.sh\_final = ''  
    self.h\_budat = ''  
    self.sh\_rerun = ''  
    self.sh\_reco = ''  
    self.sh\_manual = ''

    self.sapexe = ''  
    self.user = ''  
    self.passw = ''  
    self.window = ''

    self.downloadp = ''  
    self.yest = ''  
    self.yestfname = ''  
    self.start = ''  
    self.end = ''  
    self.today = dt.date.today()  
    # self.today = dt.datetime.strptime('2021.08.10', self.inputfm)

    self.sapdatefm = ''  
    self.pcdatefm = ''  
    self.fmstr = ''  
    self.pcstr = ''

    self.yyyymmdd = \[\]  
    self.pending = \[\]  
    self.timestamp = \[\]  
    self.currentmonth = \[\]  
    self.holiday\_dict = {}  
    self.post\_date = \[\]

def loadConfig(self, configfile='./config.ini'):  
    """parse config file"""  
    file = dealPath(configfile)  
    if not os.path.isfile(file):  
        print('Can not find the config.ini!')  
        return False  
    parser = configparser.ConfigParser()  
    parser.read(file, encoding='UTF-8')

    self.folder = self.get\_foldername()  
    # self.folder = '2021.05.20'  
    # self.yest = '2021.05.20'  
    self.automatic = parser.get('pathconfig', 'automatic').strip()  
    self.path = parser.get('pathconfig', 'path').strip()  
    self.path5 = self.path + '\\\\' + self.template + '\\\\' + self.input + self.xls  
    self.sapdatefm = parser.get('otherconfig', 'sapdatefm').strip()  
    if self.sapdatefm == 'MM/DD/YYYY':  
        self.fmstr = '%m/%d/%Y'  
    elif self.sapdatefm == 'DD/MM/YYYY':  
        self.fmstr = '%d/%m/%Y'  
    elif self.sapdatefm == 'DD.MM.YYYY':  
        self.fmstr = '%d.%m.%Y'  
    elif self.sapdatefm == 'MM-DD-YYYY':  
        self.fmstr = '%d-%d-%Y'  
    elif self.sapdatefm == 'YYYY.MM.DD':  
        self.fmstr = '%Y.%m.%d'  
    elif self.sapdatefm == 'YYYY/MM/DD':  
        self.fmstr = '%Y/%m/%d'  
    elif self.sapdatefm == 'YYYY-MM-DD':  
        self.fmstr = '%Y-%m-%d'

    self.get\_holiday()  
    self.Date\_start\_end()  
    self.yest = self.get\_yest()

    self.work\_folder = self.path + "\\\\" + self.folder  
    self.log1 = self.path + "\\\\" + self.folder + '\\\\' + 'log1.txt'  
    self.log2 = self.path + "\\\\" + self.folder + '\\\\' + 'log2.txt'  
    self.path1 = self.path + '\\\\' + self.folder + '\\\\' + self.rfkrtim + self.yest + self.xls  
    self.path1\_bk = self.path + '\\\\' + self.folder + '\\\\' + 'backup' + '\\\\' + self.rfkrtim + self.yest + self.xls

    self.sheet4 = self.rfkrtim + self.yest  
    self.path2 = self.path + '\\\\' + self.folder + '\\\\' + self.customer + self.yest + self.xlsx  
    self.path2\_bk = self.path + '\\\\' + self.folder + '\\\\' + 'backup' + '\\\\' + self.customer + self.yest + self.xlsx  
    self.h\_budat = parser.get('pathconfig', 'h\_budat').strip()  
    self.path3 = self.path + '\\\\' + self.folder + '\\\\' + self.recon + self.yest + self.xlsx  
    self.path3\_bk = self.path + '\\\\' + self.folder + '\\\\' + 'backup' + '\\\\' + self.recon + self.yest + self.xlsx  
    self.path4 = self.path1  
    self.sh\_pending = parser.get('pathconfig', 'sh\_pending').strip()  
    self.sh\_final = parser.get('pathconfig', 'sh\_final').strip()

    self.path6 = parser.get('pathconfig', 'path6').strip()  
    self.path6 = self.path + '\\\\' + self.template + '\\\\' + self.path6  
    self.sh\_reco = parser.get('pathconfig', 'sh\_reco').strip()  
    self.path7 = self.path + '\\\\' + self.folder + '\\\\' + self.emp + self.xlsx  
    self.path8 = self.path + '\\\\' + self.folder + '\\\\' + self.rfkrtim + self.yest + self.pend + self.xls  
    self.sh\_rerun = self.rfkrtim + self.yest + self.pend  
    self.path9 = parser.get('pathconfig', 'path9').strip()  
    self.path9 = self.path + '\\\\' + self.template + '\\\\' + self.path9 + self.folder + self.xlsx  
    self.path10 = parser.get('pathconfig', 'path10').strip()  
    self.path10 = self.path + '\\\\' + self.template + '\\\\' + self.path10  
    self.path11 = parser.get('pathconfig', 'path11').strip()

    self.path12 = self.path + '\\\\' + self.folder + '\\\\' + self.path11 + self.folder + self.xlsx  
    self.path11 = self.path + '\\\\' + self.template + '\\\\' + self.path11 + self.xlsx

    self.pcdatefm = parser.get('otherconfig', 'pcdatefm').strip()

    self.visible = parser.get('otherconfig', 'visible').strip()

    if self.pcdatefm == 'MM/DD/YYYY':  
        self.pcstr = '%m/%d/%Y'  
    elif self.pcdatefm == 'DD/MM/YYYY':  
        self.pcstr = '%d/%m/%Y'  
    elif self.pcdatefm == 'DD.MM.YYYY':  
        self.pcstr = '%d.%m.%Y'  
    elif self.pcdatefm == 'MM-DD-YYYY':  
        self.pcstr = '%m-%d-%Y'  
    elif self.pcdatefm == 'YYYY.MM.DD':  
        self.pcstr = '%Y.%m.%d'  
    elif self.pcdatefm == 'YYYY/MM/DD':  
        self.pcstr = '%Y/%m/%d'  
    elif self.pcdatefm == 'YYYY-MM-DD':  
        self.pcstr = '%Y-%m-%d'

    ymd = parser.get('otherconfig', 'yyyymmdd').strip()  
    pending = parser.get('otherconfig', 'pending').strip()  
    timestamp = parser.get('otherconfig', 'timestamp').strip()  
    currentmonth = parser.get('otherconfig', 'currentmonth').strip()

    self.read = parser.get('otherconfig', 'read').strip()  
    self.user = parser.get('otherconfig', 'user').strip()  
    self.passw = parser.get('otherconfig', 'pass').strip()  
    self.window = parser.get('otherconfig', 'window').strip()  
    self.sapexe = parser.get('pathconfig', 'sapexe').strip()  
    self.downloadp = self.work\_folder

    if ymd:  
        a = list(map(str, ymd.split(";")))  
        self.yyyymmdd.append(a)

    if pending:  
        a = list(map(str, pending.split(";")))  
        self.pending.append(a)

    if timestamp:  
        a = list(map(str, timestamp.split(";")))  
        self.timestamp.append(a)

    if currentmonth:  
        a = list(map(str, currentmonth.split(";")))  
        self.currentmonth.append(a)

    self.get\_post\_date()

def get\_foldername(self):  
    """

    :return:  
    """  
    yesterday = self.today + dt.timedelta(-1)  
    self.sh\_manual = yesterday.strftime('%m%d')  
    return yesterday.strftime('%Y%m%d')

def get\_yest(self):  
    end = ''  
    if self.start:  
        start = dt.datetime.strptime(self.start, self.fmstr).strftime('%Y%m%d')  
    if self.end:  
        end = dt.datetime.strptime(self.end, self.fmstr).strftime('%Y%m%d')

    if end != '':  
        return start + '-' + end  
    else:  
        return start

def get\_holiday(self):  
    wb\_input = xlrd.open\_workbook(self.path5, encoding\_override='utf-8')

    ws\_input = wb\_input.sheets()\[0\]  
    row\_num = ws\_input.nrows  
    key = ws\_input.col\_values(0)  
    values = ws\_input.col\_values(1)  
    for i in range(row\_num):  
        self.holiday\_dict\[key\[i\]\] = values\[i\]  
    self.holiday\_dict.pop('Holiday')

def get\_post\_date(self):  
    """  
    :  
    """  
    sunmmay = xlrd.open\_workbook(self.path6)  
    sunmmay\_Date = sunmmay.sheet\_by\_name(u'Pending\_final')  
    detail\_Date = sunmmay\_Date.col\_values(3)

    print(len(detail\_Date))  
    detail\_Date.remove('Postg Date')  
    while '' in detail\_Date:  
        detail\_Date.remove('')  
    detail\_Date = list(set(detail\_Date))  
    detail\_Date.sort()  
    num = 0  
    while num <= len(detail\_Date) - 1:  
        detail\_Date\[num\] = self.Data\_Name\_Format(detail\_Date\[num\])  
        num = num + 1  
    self.post\_date = detail\_Date

def Data\_Name\_Format(self, data):  
    if isinstance(data, float):  
        DateObj = xlrd.xldate\_as\_datetime(data, 0)  
        return DateObj.strftime(self.fmstr)  
    else:  
        return dt.datetime.strptime(data, self.inputfm).strftime(self.fmstr)

def Date\_start\_end(self):  
    if self.automatic == 'Y':  
        # 如果是节假日  
        # 这里判断的是字符串，所以如果节假日在list中，但是节假日以date的形式出现，会判断不准确  
        if self.today.strftime(self.inputfm) in self.holiday\_dict.keys():

            during = self.holiday\_dict\[self.today.strftime(self.inputfm)\].split("-")  
            print(during\[0\])  
            self.start = self.Data\_Name\_Format(during\[0\])  
            self.end = self.Data\_Name\_Format(during\[1\])  
            print(self.start)  
            print(self.end)  
            print('yesterday is holiday')  
        # 如果是周一  
        elif self.today.strftime("%w") == '1':  
            print(str(self.today + dt.timedelta(days=-3)))  
            self.start = self.Data\_Name\_Format((self.today + dt.timedelta(days=-3)).strftime(self.inputfm))  
            self.end = self.Data\_Name\_Format((self.today + dt.timedelta(days=-1)).strftime(self.inputfm))  
            print(self.start)  
            print(self.end)  
            print('today is Monday')  
        else:  
            self.start = self.Data\_Name\_Format((self.today + dt.timedelta(days=-1)).strftime(self.inputfm))  
            self.end = ''  
            print('today is normal day')  
            print(self.start)  
    else:  
        self.start = input('Please input start date:YYYY.MM.DD :')  
        self.end = input('Please input end date:YYYY.MM.DD :')  
        self.start = self.Data\_Name\_Format(self.start)  
        self.end = self.Data\_Name\_Format(self.end)

        if self.start == self.end:  
            self.end = ''

3. Sort handle code

import pandas as pd
from easyexcel import *
import datetime as dt
import win32timezone

def get_datelist(conf=None) -> list:
"""

@param conf:  
@return: date list  
"""  
start = None  
end = None  
days = 0  
day\_list = \[\]  
start = dt.datetime.strptime(conf.start, conf.fmstr)

if conf.start != '':  
    day\_list.append(conf.start)

if conf.end != '':  
    end = dt.datetime.strptime(conf.end, conf.fmstr)  
    days = (end - start).days  
    for item in range(1, days):  
        em = start + dt.timedelta(days=item)  
        day\_list.append(em.strftime(conf.fmstr))

    day\_list.append(conf.end)  
return day\_list

def pd_datefm(df, col=None, fm=''):
try:
df[col] = df[col].apply(lambda x: x.strftime(fm) if x != '' else '')
except ValueError:
pass
return df

def pd_filter1(source, cond=None, conf=None) -> pd.DataFrame:
"""

@param source: DataFrame data  
@param cond: posting date range  
@param strfm: date format  
@return:  
"""  
if cond is None:  
    cond = \[\]

cond = list(map(lambda x: dt.datetime.strptime(x, conf.fmstr).strftime(conf.pcstr), cond))  
lent = len(cond)

df = source\[source\['Document Header Text'\].isnull()\]  
if lent == 1:  
    df1 = df\[df\['Posting Date'\] == cond\[0\]\]  
else:  
    df1 = df\[(cond\[0\] <= df\['Posting Date'\]) & (df\['Posting Date'\] <= cond\[lent-1\])\]

df1 = df1.dropna(axis=0, subset=\['Fiscal Year'\])

return df1

def pd_write_toexcel(path='', sheet_name='', resource=None, from_cols=None, to_cols=None):
"""

:param path: save path  
:param sheet\_name:sheet name  
:param resource: resource data with type DataFrame  
:param from\_cols:columns name of the source data ( list from\_cols and to\_cols must have the same length)  
:param to\_cols: columns name of the target data ( list from\_cols and to\_cols must have the same length)  
:return:  
"""

if path == '':  
    return

all\_sheet = pd.read\_excel(path, sheet\_name=None)  
keys = list(all\_sheet.keys())  
assert isinstance(keys.index(sheet\_name), int), 'sheet name' % sheet\_name % 'does not exist'

'''target file sheet list'''  
pro\_sheet = all\_sheet\[sheet\_name\]

'''header info'''  
head = resource.columns.values.tolist()

'''delete the columns unused'''  
for item in head:  
    try:  
        from\_cols.index(item)  
    except ValueError:  
        resource.drop(item, axis=1, inplace=True)

listr = resource.values.tolist()  
pro\_sheet.loc\[0:len(listr), tuple(to\_cols)\] = listr  
writer = pd.ExcelWriter(path)

for item in keys:  
    if item != sheet\_name:  
        pd.DataFrame(all\_sheet\[item\]).to\_excel(writer, sheet\_name=item, index=False)

pd.DataFrame(pro\_sheet).to\_excel(writer, sheet\_name=sheet\_name, index=False)  
writer.save()  
return

def pd_write_excel(path='', source=None, sheet_name=''):
all_sheet = pd.read_excel(path, sheet_name=None)
keys = list(all_sheet.keys())
writer = pd.ExcelWriter(path)

for item in keys:  
    pd.DataFrame(all\_sheet\[item\]).to\_excel(writer, sheet\_name=item, index=False)

pd.DataFrame(source).to\_excel(writer, sheet\_name=sheet\_name, index=False)  
writer.save()  
return

def pd_sumData(file='', sheet_name='', header=''):
"""

@param file:  
@param sheet\_name:  
@param header:  
@return:  
"""  
df = pd.read\_excel(file, sheet\_name=sheet\_name)  
return df\[header\].sum()

def pd_getyes(yest=None):
if yest is None or yest == '':
yesterday = dt.date.today() + dt.timedelta(-1)
return yesterday.strftime('%Y.%m.%d')
else:
return yest

def pd_getCelValue(file, sheet_name, header='', cond=None, col=None):
cond = list(map(lambda x: dt.datetime.strptime(x, '%m/%d/%Y'), cond))

df = pd.read\_excel(file, sheet\_name=sheet\_name)  
if len(cond) == 1:  
    df\[df\[header\] == cond\[0\]\]  
value = 0  
for item in cond:  
    ind = df\[df\[header\] == item\].index  
    assert df.iloc\[ind, col\].values\[0\], 'Can not get value of ' % item  
    if df.iloc\[ind, col\].values\[0\] is not None:  
        value = value + df.iloc\[ind, col\].values\[0\]  
return value

def pd_pivot(df_data=None, values=None, index=None):
res = df_data.pivot_table(values=values, index=index, columns=None, aggfunc='sum', fill_value=None, dropna=True,
margins=False, margins_name='All')
return res

def get_Holiday(file):
dic = {}
df = pd.read_excel(file, 'Sheet1')
res = df.values.tolist()
for item in res:
dic[item[0]] = item[1]
return dic

def pd_onetotwo(lst):
b = []
for item in lst:
a = []
if str(item) == 'nan':
a.append('')
b.append(a)
else:
a.append(item)
b.append(a)
return b

def pd_vlookup(taba, tabb, a_col, b_col, value_cols=None):
"""

:param taba: source data  
:param tabb: condition data  
:param a\_col: column of taba for 'ON'  
:param b\_col: column of tabb for 'ON'  
:param value\_cols:  
:return: data frame after vlook up  
"""  
heada = taba.columns.values.tolist()  
headb = tabb.columns.values.tolist()

try:  
    heada.index(a\_col)  
except ValueError:  
    print('column' % a\_col % 'do not exist!')  
    return

try:  
    headb.index(b\_col)  
except ValueError:  
    print('column' % b\_col % 'do not exist!')  
    return

for item in value\_cols:  
    try:  
        heada.index(item)  
    except ValueError:  
        print('column' % item % 'do not exist!')  
        return

for item in heada:  
    try:  
        if item != a\_col and not isinstance(value\_cols.index(item), int):  
            taba.drop(item, axis=1, inplace=True)  
    except ValueError:  
        taba.drop(item, axis=1, inplace=True)

for item in headb:  
    if item != b\_col:  
        tabb.drop(item, axis=1, inplace=True)

lista = taba.values.tolist()  
listb = tabb.values.tolist()

tabc = pd.DataFrame(lista, columns=value\_cols)  
tabd = pd.DataFrame(listb, columns=\[a\_col\])

res = tabc.merge(tabd, on=a\_col, how='right')  
return res

def pd_get_sheets(org_path='', sheet_name=None):
return pd.read_excel(org_path, sheet_name)

def pd_get_rows(df, row_list=None):
if row_list is None:
row_list = []
return df.loc[row_list]

def pd_get_header(df) -> list:
"""

:param df: DataFrame  
:return: list of header info, first row info of excel sheet

example :  
df = pd.read\_excel('test.xlsx', sheet\_name='sheet1')  
pd\_get\_header(df)  
"""  
assert isinstance(df, pd.DataFrame), 'df is Not a DataFrame'  
return df.columns.values.tolist()

def pd_get_sortby(head: list, cond: list, con_tp=1) -> list:
"""

:param head: header info list  
:param cond: condition list  
:param con\_tp: condition type 1 sort by column name;2 sort by column index  
:return: sort by list

example:  
head = \['Name','Age','Class','Gender'\]  
cond = \['A','B','C'\]  
con\_tp = 1  
pd\_get\_sortby(head, cond, con\_tp)  
"""  
excel = EasyExcel(visible=False)  
col\_dic = excel.getColDict()  
sort\_list = \[\]  
if con\_tp == 1:  
    for con in cond:  
        sort\_list.append(head\[col\_dic\[con\] - 1\])  
elif con\_tp == 2:  
    for con in cond:  
        sort\_list.append(head\[con - 1\])  
elif con\_tp == 3:  
    sort\_list = cond

return sort\_list

def pd_delete_data(df, cond_list=None, cond_type=0):
"""

:param df: DataFrame  
:param cond\_list: delete condition  
:param cond\_type:  
0:delete by row index  
1:delete by column name\['A','B','C'\]  
2:delete by column index  
3:delete by header (first row of sheet)  
:return:  
"""

if cond\_list is None:  
    cond\_list = \[\]  
if cond\_type == 0:  
    df.dorp(df.index\[cond\_list\], inplace=True)  
elif cond\_type == 1:  
    header = pd\_get\_header(df)  
    del\_list = pd\_get\_sortby(header, cond\_list, cond\_type)  
    df.dorp(del\_list, axis=1, inplace=True)  
elif cond\_type == 2:  
    df.dorp(df.columns\[cond\_list\], axis=1, inplace=True)  
elif cond\_type == 3:  
    df.dorp(cond\_list, axis=1, inplace=True)  
return df

def pd_sort_data(org_path='', tag_path='', sheet_name='', ascending=False, cond_list=None, save=False, cond_type=1):
"""
note: this function just Suitable for the sheet data with header info
:param cond_type: 1 cond_list = columns name;2 cond_list = columns index;3 cond_list = header(first row info)
:param cond_list:[A,B,C….] OR[1,2,3….],[header1,header2,….]
:param ascending: Ascending or Descending
:param save: if save Immediately
:param org_path: original path of excel
:param tag_path: target path of excel
:param sheet_name: sheet to be process
:return:target data after sort process

 example:  
 pd\_sort\_data(org\_path='test1.xlsx', sheet\_name='S1', cond\_list=\['A', 'B'\], cond\_type=1, ascending=False, save=True)  
 """

if cond\_list is None:  
    cond\_list = \[\]  
if org\_path:  
    org\_file = pd.read\_excel(org\_path, sheet\_name=None)  
else:  
    return  
tag\_file: dict = {}  
for sh\_name in org\_file.keys():  
    if sh\_name == sheet\_name:  
        header = pd\_get\_header(org\_file\[sh\_name\])  
        sort = pd\_get\_sortby(header, cond\_list, cond\_type)  
        tag\_file\[sh\_name\] = org\_file\[sh\_name\].sort\_values(by=sort, axis=0, ascending=ascending)  
    else:  
        tag\_file\[sh\_name\] = org\_file\[sh\_name\]  
if save:  
    if not tag\_path == '':  
        writer = pd.ExcelWriter(tag\_path)  
    else:  
        writer = pd.ExcelWriter(org\_path)  
    for sh in tag\_file.keys():  
        pd.DataFrame(tag\_file\[sh\]).to\_excel(writer, sheet\_name=sh, index=False)  
    writer.save()  
return tag\_file

def write_to_reco(conf, dates, f, sum4=0):
print('start write amount sheet->')
f.write('start write amount sheet->' + '\n')
try:
cond = list(map(lambda x: dt.datetime.strptime(x, conf.fmstr), dates))
lenth = len(cond)
df = pd.read_excel(conf.path6, sheet_name=conf.sh_reco)
if lenth == 1:
df = df[df['GL by Day'] == cond[0]]
else:
df = df[(cond[0] <= df['GL by Day']) & (df['GL by Day'] <= cond[lenth-1])]
df['GL by Day'] = df['GL by Day'].astype(str).apply(lambda x: dt.datetime.strptime(x, '%Y-%m-%d').strftime(conf.pcstr))
df = df[['GL by Day', 'Unnamed: 7']]
sum4 = df['Unnamed: 7'].sum()
if df.empty:
exl = EasyExcel(visible=conf.visible).open(filename=conf.path3)
print('file open:', conf.path3)
f.write('file open:' + conf.path3 + '\n')
f.flush()
try:
recon = exl.with_color(sheet='Sheet1', col=16, row=2, date=True, pcstr=conf.pcstr)
exl.close()
print(conf.path3, ':Closed')
f.write(conf.path3 + ':Closed' + '\n')
f.flush()
for item in cond:
k = item.strftime(conf.pcstr)
try:
recon[k]
except Exception as e:
str(e)
recon[k] = 0
exl = EasyExcel(visible=conf.visible).open(conf.path6)
print(conf.path6, ':Open')
f.write(conf.path6 + ':Open' + '\n')
f.flush()
lstrow = exl.getLastRow(sheet=conf.sh_reco, col=7)
ls_sort = sorted(recon)
for item in ls_sort:
lstrow = lstrow + 1
exl.setCellValue(sheet=conf.sh_reco, row=lstrow, col=7, value=item)
exl.setCellValue(sheet=conf.sh_reco, row=lstrow, col=8, value=abs(recon[item]))
sum4 = sum4 + recon[item]
exl.save()
print(conf.path6, 'saved')
f.write(conf.path6 + ':saved' + '\n')
f.flush()
except Exception as e:
exl.close()
str(e)
print(str(e))
f.write(str(e))
f.flush()
sum4 = 0
return sum4
except Exception as e:
str(e)
print(str(e))
f.write(str(e))
f.flush()
return sum4

4. sap download

from datetime import datetime
import win32com.client
import subprocess
import win32con
import win32gui
import datetime
import time
import sys
import os

class KoreaDownLoad(object):
def __init__(self, conf=None):
self.date = ''
self.year = ''
self.start = conf.start
self.end = conf.end
self.session = None
self.process = None
self.conf = conf
self.detail_Date = conf.post_date
self.start_Date = conf.post_date[0]
self.end_Date = conf.post_date[len(conf.post_date) - 1]
self.start_Year = datetime.datetime.strptime(self.start_Date, conf.fmstr).strftime('%Y')
self.end_Year = datetime.datetime.strptime(self.end_Date, conf.fmstr).strftime('%Y')

    # 处理月,获取到上上个月的最后一天  
    last = datetime.date(conf.today.year, conf.today.month, 1) - datetime.timedelta(1)  
    last = datetime.date(last.year, last.month, 1) - datetime.timedelta(1)

    self.result\_last = last.strftime('%Y%m%d')  
    self.copylist = \[\]  
    self.Get\_copylist()  
    self.first\_Date = datetime.date(conf.today.year, conf.today.month - 1, 1).strftime(  
        conf.fmstr)  
    self.yesterday = (conf.today + datetime.timedelta(-1)).strftime(conf.fmstr)

def Get\_copylist(self):  
    for i in self.detail\_Date:  
        em = datetime.datetime.strptime(i, self.conf.fmstr).strftime('%Y%m%d')  
        if em <= self.result\_last:  
            self.copylist.append(i)  
    return self.copylist

def Date\_Format(self, data):  
    return datetime.datetime.strptime(data, self.conf.inputfm).strftime(self.conf.fmstr)

def Refresh(self):  
    self.date = ''  
    self.year = ''  
    self.start = ''  
    self.end = ''  
    self.session = None

def OpenSap(self, user='', passw='', path='', window='SAP'):  
    # 打开SAP

    self.process = subprocess.Popen(  
        path,  
        shell=True)  
    while win32gui.FindWindow(None, window) == 0:  
        time.sleep(0.5)  
    else:  
        sap\_logon = win32gui.FindWindow(None, window)

    i\_id = win32gui.FindWindowEx(sap\_logon, 0, 'Edit', None)  
    win32gui.SendMessage(i\_id, win32con.WM\_SETTEXT, None, user)

    i\_password = win32gui.GetWindow(win32gui.FindWindowEx(sap\_logon, 0, 'Edit', None), win32con.GW\_HWNDNEXT)  
    win32gui.SendMessage(i\_password, win32con.WM\_SETTEXT, None, passw)

    logon\_button = win32gui.FindWindowEx(sap\_logon, 0, 'Button', '&Log On')  
    win32gui.SendMessage(sap\_logon, win32con.WM\_COMMAND, 1, logon\_button)

    while win32gui.FindWindow(None, 'SAP Easy Access') == 0:  
        time.sleep(0.5)  
    else:  
        sap\_gui = win32com.client.GetObject('SAPGUI')  
        application = sap\_gui.GetScriptingEngine  
        connection = application.Children(0)  
        self.session = connection.Children(0)  
    return self.session

def CloseSap(self, session=None):  
    if session is None:  
        session = self.session

    session.findById("wnd\[0\]").close()  
    session.findById("wnd\[1\]/usr/btnSPOP-OPTION1").press()  
    os.system('taskkill /f /im saplogon.exe')  
    self.Refresh()

def Download\_FBL3N(self):  
    # 进入FBL3N，下载  
    self.session.findById("/app/con\[0\]/ses\[0\]/wnd\[0\]/tbar\[0\]/okcd").text = "FBL3N"  
    self.session.findById("/app/con\[0\]/ses\[0\]/wnd\[0\]").sendVKey(0)  
    self.session.findById("wnd\[0\]/tbar\[1\]/btn\[17\]").press()  
    self.session.findById("wnd\[1\]/usr/txtV-LOW").text = "KR OUTPUT"  
    self.session.findById("wnd\[1\]/usr/txtENAME-LOW").text = ""  
    self.session.findById("wnd\[1\]/usr/txtENAME-LOW").setFocus()  
    self.session.findById("wnd\[1\]/usr/txtENAME-LOW").caretPosition = 0  
    self.session.findById("wnd\[1\]/tbar\[0\]/btn\[8\]").press()

    self.session.findById("wnd\[0\]/usr/ctxtSO\_BUDAT-LOW").text = self.start  
    self.session.findById("wnd\[0\]/usr/ctxtSO\_BUDAT-HIGH").text = self.end

    self.session.findById("wnd\[0\]/usr/ctxtSO\_BUDAT-LOW").setFocus()  
    self.session.findById("wnd\[0\]/usr/ctxtSO\_BUDAT-LOW").caretPosition = 5  
    self.session.findById("wnd\[0\]/tbar\[1\]/btn\[8\]").press()  
    self.session.findById("wnd\[0\]/usr/lbl\[139,5\]").setFocus()  
    self.session.findById("wnd\[0\]/usr/lbl\[139,5\]").caretPosition = 5  
    self.session.findById("wnd\[0\]").sendVKey(2)  
    self.session.findById("wnd\[0\]/tbar\[1\]/btn\[25\]").press()  
    self.session.findById("wnd\[0\]/mbar/menu\[0\]/menu\[3\]/menu\[1\]").select()  
    self.session.findById("wnd\[1\]/tbar\[0\]/btn\[0\]").press()  
    # 此处为存放的路径，需要设置为变量  
    self.session.findById("wnd\[1\]/usr/ctxtDY\_PATH").text = self.conf.work\_folder

    self.session.findById("wnd\[1\]/usr/ctxtDY\_FILENAME").text = self.conf.recon + self.conf.yest + self.conf.xlsx  
    self.session.findById("wnd\[1\]/usr/ctxtDY\_FILENAME").caretPosition = 10  
    self.session.findById("wnd\[1\]/tbar\[0\]/btn\[0\]").press()

    s = win32gui.FindWindow(None, self.conf.recon + self.conf.yest + self.conf.xlsx + " - Excel")  
    while s == 0:  
        time.sleep(0.5)  
        s = win32gui.FindWindow(None, self.conf.recon + self.conf.yest + self.conf.xlsx + " - Excel")

    win32gui.PostMessage(s, win32con.WM\_CLOSE, 0, 0)

def Download\_S\_ALR\_87012357(self, f):  
    time.sleep(1)  
    self.session.findById("wnd\[0\]/tbar\[0\]/okcd").text = "/nS\_ALR\_87012357"  
    self.session.findById("wnd\[0\]").sendVKey(0)  
    self.session.findById("wnd\[0\]/tbar\[1\]/btn\[17\]").press()  
    self.session.findById("wnd\[1\]/usr/txtV-LOW").text = "KR OUTPUT"  
    self.session.findById("wnd\[1\]/usr/txtENAME-LOW").text = ""  
    self.session.findById("wnd\[1\]/usr/txtENAME-LOW").setFocus()  
    self.session.findById("wnd\[1\]/usr/txtENAME-LOW").caretPosition = 0  
    self.session.findById("wnd\[1\]/tbar\[0\]/btn\[8\]").press()  
    # self.session.findById("wnd\[0\]/usr/txtBR\_GJAHR-LOW").text = self.start\_Year  
    # self.session.findById("wnd\[0\]/usr/txtBR\_GJAHR-HIGH").text = self.end\_Year  
    self.session.findById("wnd\[0\]/usr/txtBR\_GJAHR-HIGH").setFocus()  
    self.session.findById("wnd\[0\]/usr/txtBR\_GJAHR-HIGH").caretPosition = 4  
    self.session.findById("wnd\[0\]/usr/btn%\_BR\_BUDAT\_%\_APP\_%-VALU\_PUSH").press()  
    self.session.findById("wnd\[1\]/tbar\[0\]/btn\[16\]").press()  
    # 此处需要一个循环，输入所有小于上上个月最后一天的日期  
    f.write('posting date as follow:' + '\\n')  
    for i in range(0, len(self.copylist)):  
        print(self.copylist\[i\])  
        f.write(self.copylist\[i\] + '\\n')  
        self.session.findById("wnd\[1\]/tbar\[0\]/btn\[13\]").press()  
        self.session.findById(  
            "wnd\[1\]/usr/tabsTAB\_STRIP/tabpSIVA/ssubSCREEN\_HEADER:SAPLALDB:3010/tblSAPLALDBSINGLE/ctxtRSCSEL\_255-SLOW\_I\[1,0\]").text = \\  
            self.copylist\[i\]  
        self.session.findById(  
            "wnd\[1\]/usr/tabsTAB\_STRIP/tabpSIVA/ssubSCREEN\_HEADER:SAPLALDB:3010/tblSAPLALDBSINGLE/ctxtRSCSEL\_255-SLOW\_I\[1,0\]").caretPosition = 10

    self.session.findById("wnd\[1\]/usr/tabsTAB\_STRIP/tabpINTL").select()  
    self.session.findById(  
        "wnd\[1\]/usr/tabsTAB\_STRIP/tabpINTL/ssubSCREEN\_HEADER:SAPLALDB:3020/tblSAPLALDBINTERVAL/ctxtRSCSEL\_255-ILOW\_I\[1,0\]").text = self.first\_Date  
    self.session.findById(  
        "wnd\[1\]/usr/tabsTAB\_STRIP/tabpINTL/ssubSCREEN\_HEADER:SAPLALDB:3020/tblSAPLALDBINTERVAL/ctxtRSCSEL\_255-IHIGH\_I\[2,0\]").text = self.yesterday  
    self.session.findById(  
        "wnd\[1\]/usr/tabsTAB\_STRIP/tabpINTL/ssubSCREEN\_HEADER:SAPLALDB:3020/tblSAPLALDBINTERVAL/ctxtRSCSEL\_255-IHIGH\_I\[2,0\]").setFocus()  
    self.session.findById(  
        "wnd\[1\]/usr/tabsTAB\_STRIP/tabpINTL/ssubSCREEN\_HEADER:SAPLALDB:3020/tblSAPLALDBINTERVAL/ctxtRSCSEL\_255-IHIGH\_I\[2,0\]").caretPosition = 6  
    self.session.findById("wnd\[1\]/tbar\[0\]/btn\[8\]").press()  
    self.session.findById("wnd\[0\]/tbar\[1\]/btn\[8\]").press()  
    self.session.findById("wnd\[0\]/tbar\[1\]/btn\[43\]").press()  
    self.session.findById("wnd\[1\]/tbar\[0\]/btn\[0\]").press()  
    self.session.findById("wnd\[1\]/usr/ctxtDY\_PATH").text = self.conf.work\_folder  
    self.session.findById("wnd\[1\]/usr/ctxtDY\_FILENAME").text = self.conf.customer + self.conf.yest + self.conf.xlsx  
    self.session.findById("wnd\[1\]/usr/ctxtDY\_FILENAME").caretPosition = 17  
    self.session.findById("wnd\[1\]/tbar\[0\]/btn\[0\]").press()  
    self.session.findById("wnd\[0\]/tbar\[0\]/btn\[12\]").press()

    s = win32gui.FindWindow(None, self.conf.customer + self.conf.yest + self.conf.xlsx + " - Excel")  
    while s == 0:  
        time.sleep(0.5)  
        s = win32gui.FindWindow(None, self.conf.customer + self.conf.yest + self.conf.xlsx + " - Excel")

    win32gui.PostMessage(s, win32con.WM\_CLOSE, 0, 0)

def Download\_RFKRTIM(self):  
    self.session.findById("wnd\[0\]/tbar\[0\]/okcd").text = "/nRFKRTIM"  
    self.session.findById("wnd\[0\]").sendVKey(0)  
    time.sleep(3)  
    if self.session.findById("/app/con\[0\]/ses\[0\]/wnd\[0\]/titl").text == "Time-Stamp Tax Invoices South Korea)":  
        self.session.findById("wnd\[0\]/tbar\[1\]/btn\[17\]").press()  
        self.session.findById("wnd\[1\]/usr/cntlALV\_CONTAINER\_1/shellcont/shell").currentCellRow = 1  
    else:  
        time.sleep(5)  
        self.session.findById("wnd\[0\]/tbar\[1\]/btn\[17\]").press()  
        self.session.findById("wnd\[1\]/usr/cntlALV\_CONTAINER\_1/shellcont/shell").currentCellRow = 1  
    self.session.findById("wnd\[1\]/usr/cntlALV\_CONTAINER\_1/shellcont/shell").selectedRows = "1"  
    self.session.findById("wnd\[1\]/usr/cntlALV\_CONTAINER\_1/shellcont/shell").doubleClickCurrentCell()

    self.session.findById("wnd\[0\]/usr/ctxtBR\_BUDAT-LOW").text = self.conf.start  
    self.session.findById("wnd\[0\]/usr/ctxtBR\_BUDAT-HIGH").text = self.conf.end  
    last\_day = datetime.date(datetime.date.today().year, 12, 31).strftime(self.conf.fmstr)

    print(last\_day)

    self.session.findById("wnd\[0\]/usr/ctxtS\_BLDAT-HIGH").text = last\_day  
    self.session.findById("wnd\[0\]/usr/txtBR\_GJAHR-HIGH").setFocus()  
    self.session.findById("wnd\[0\]/usr/txtBR\_GJAHR-HIGH").caretPosition = 4  
    self.session.findById("wnd\[0\]/tbar\[1\]/btn\[8\]").press()  
    self.session.findById("wnd\[0\]/mbar/menu\[3\]/menu\[5\]/menu\[2\]/menu\[1\]").select()  
    self.session.findById(  
        "wnd\[1\]/usr/subSUBSCREEN\_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG\[1,0\]").select()  
    self.session.findById(  
        "wnd\[1\]/usr/subSUBSCREEN\_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG\[1,0\]").setFocus()  
    self.session.findById("wnd\[1\]/tbar\[0\]/btn\[0\]").press()  
    self.session.findById("wnd\[1\]/usr/ctxtDY\_PATH").text = self.conf.work\_folder  
    self.session.findById("wnd\[1\]/usr/ctxtDY\_FILENAME").text = self.conf.rfkrtim + self.conf.yest + ".xls"  
    self.session.findById("wnd\[1\]/usr/ctxtDY\_FILENAME").caretPosition = 14  
    self.session.findById("wnd\[1\]/tbar\[0\]/btn\[0\]").press()

def Download(self, session=None, path='', datelist=None):  
    if session is None:  
        session = self.session

    session.findById("wnd\[0\]/tbar\[0\]/okcd").text = "/nRFKRTIM"  
    session.findById("wnd\[0\]").sendVKey(0)  
    time.sleep(3)  
    if session.findById("/app/con\[0\]/ses\[0\]/wnd\[0\]/titl").text == "Time-Stamp Tax Invoices South Korea)":  
        session.findById("wnd\[0\]/tbar\[1\]/btn\[17\]").press()  
        session.findById("wnd\[1\]/usr/cntlALV\_CONTAINER\_1/shellcont/shell").currentCellRow = 1  
    else:  
        time.sleep(5)  
        session.findById("wnd\[0\]/tbar\[1\]/btn\[17\]").press()  
        session.findById("wnd\[1\]/usr/cntlALV\_CONTAINER\_1/shellcont/shell").currentCellRow = 1  
    session.findById("wnd\[1\]/usr/cntlALV\_CONTAINER\_1/shellcont/shell").selectedRows = "1"  
    session.findById("wnd\[1\]/usr/cntlALV\_CONTAINER\_1/shellcont/shell").doubleClickCurrentCell()  
    session.findById("wnd\[0\]/usr/btn%\_BR\_BUDAT\_%\_APP\_%-VALU\_PUSH").press()  
    session.findById(  
        "wnd\[1\]/usr/tabsTAB\_STRIP/tabpSIVA/ssubSCREEN\_HEADER:SAPLALDB:3010/tblSAPLALDBSINGLE/ctxtRSCSEL\_255-SLOW\_I\[1,0\]").text = ''

    for item in datelist:  
        session.findById("wnd\[1\]/tbar\[0\]/btn\[13\]").press()  
        session.findById(  
            "wnd\[1\]/usr/tabsTAB\_STRIP/tabpSIVA/ssubSCREEN\_HEADER:SAPLALDB:3010/tblSAPLALDBSINGLE/ctxtRSCSEL\_255-SLOW\_I\[1,0\]").text = item  
        session.findById(  
            "wnd\[1\]/usr/tabsTAB\_STRIP/tabpSIVA/ssubSCREEN\_HEADER:SAPLALDB:3010/tblSAPLALDBSINGLE/ctxtRSCSEL\_255-SLOW\_I\[1,0\]").caretPosition = 10  
    session.findById("wnd\[1\]/tbar\[0\]/btn\[8\]").press()  
    session.findById("wnd\[0\]/usr/txtBR\_GJAHR-HIGH").setFocus()  
    session.findById("wnd\[0\]/usr/txtBR\_GJAHR-HIGH").caretPosition = 4  
    session.findById("wnd\[0\]/tbar\[1\]/btn\[8\]").press()  
    session.findById("wnd\[0\]/mbar/menu\[3\]/menu\[5\]/menu\[2\]/menu\[1\]").select()  
    session.findById(  
        "wnd\[1\]/usr/subSUBSCREEN\_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG\[1,0\]").select()  
    session.findById(  
        "wnd\[1\]/usr/subSUBSCREEN\_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG\[1,0\]").setFocus()  
    session.findById("wnd\[1\]/tbar\[0\]/btn\[0\]").press()

    '''"C:\\\\Users\\\\cheny140\\\\OneDrive - Medtronic PLC\\\\Desktop\\\\Project\\\\Korea eTax"'''  
    session.findById(  
        "wnd\[1\]/usr/ctxtDY\_PATH").text = path

    session.findById("wnd\[1\]/usr/ctxtDY\_FILENAME").text = "RFKRTIM" + self.conf.yest + 'pending' + ".xls"  
    session.findById("wnd\[1\]/usr/ctxtDY\_FILENAME").caretPosition = 14  
    session.findById("wnd\[1\]/tbar\[0\]/btn\[0\]").press()  
    print('Download success!')