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!')
