这个代码主要用于从Excel文件中读取数据，处理数据并生成模板。它包含多个函数，如just_open()用于打开Excel文件，readSFLxlsx1()用于读取特定工作表的数据，readBrdXlsx()和krreadBrdXlsx()分别用于读取不同文件格式的数据。此外，还有write_to_excel()用于将数据写入Excel模板，以及几个特定用途的函数，如readBrdXlsxForDate()、readBrdXlsxForSingopreDaily()等，它们根据日期和工作表类型填充数据。最后，代码还提供了将数据写入CSV文件和从CSV文件中读取数据的辅助功能。
Data_Process.py
import openpyxl
import sys
import traceback
from pathlib import Path
import xlrd
import csv
import codecs
from datetime import datetime
from openpyxl.reader.excel import load_workbook
from xlrd import xldate_as_tuple
sys.path.append(Path(file).parent.resolve())
import openpyxl
from openpyxl.styles import PatternFill, colors
from demo.src.testmain import test_download_files_batch,main,downLoadingMappingTable
import warnings
warnings.filterwarnings(‘ignore’)
import os
import time
import logging
import datetime
from openpyxl.styles import PatternFill, colors
from win32com.client import Dispatch
from xlrddate import excelHandle
#excel加载公式计算
def just_open(filename):
xlApp = Dispatch(“Excel.Application”)
xlApp.Visible = False
xlBook = xlApp.Workbooks.Open(filename)
xlBook.Save()
xlBook.Close()
#mapping table 数据源获取
def readSFLxlsx1():
file_path = os.path.join(r’C:\Users\Public\AdjustmentFile’, ‘Mapping Table.xlsx’)
# wb = load_workbook(r’C:\wz\SourceFile\Mapping Table.xlsx’, data_only=True)
wb = load_workbook(file_path, data_only=True)
# print(wb)
sh = wb[“Sheet1”]
all_datas = []
# 1、拿到字典的key值
# print(list(sh.rows)[0]) # 按行读取数据 (<Cell ‘login’.A1>, <Cell ‘login’.B1>, <Cell ‘login’.C1>)
titles = [] # 定义一个空列表
for item in list(sh.rows)[0]: # 遍历第1行当中每一列
titles.append(item.value) # 列表添加值
# print(titles)
for item in list(sh.rows)[1:]: # 遍历数据行
values = []
for val in item: # 获取每一行的值
values.append(val.value)
# res = dict(zip(titles, values)) # title和每一行数据，打包成字典
# res[“expected”] = eval(res[“expected”]) # 将expected的字符串，转换为字典对象。
all_datas.append(values) # 追加到列表
return all_datas
def readBrdXlsx(BuDatexlsx):
print('readBrdXlsx : ‘)
# file_path = os.path.join(os.path.dirname(os.path.abspath(file)), “Mapping Table.xlsx”)
filepath = r’C:\Users\Public\SourceFile’
filename =BuDatexlsx
print(‘BuDatexlsx:’+BuDatexlsx)
file_path = os.path.join(filepath,filename)
print(‘file_path:’+file_path)
wb = load_workbook(file_path, data_only=True)
# print(wb)
sh = wb[“FTM”]
all_datas = []
# 1、拿到字典的key值
# print(list(sh.rows)[0]) # 按行读取数据 (<Cell ‘login’.A1>, <Cell ‘login’.B1>, <Cell ‘login’.C1>)
titles = [] # 定义一个空列表
for item in list(sh.rows)[0]: # 遍历第1行当中每一列
titles.append(item.value) # 列表添加值
# print(titles)
for item in list(sh.rows)[20:]: # 遍历数据行
values = []
for val in item: # 获取每一行的值
values.append(val.value)
# res = dict(zip(titles, values)) # title和每一行数据，打包成字典
# res[“expected”] = eval(res[“expected”]) # 将expected的字符串，转换为字典对象。
all_datas.append(values) # 追加到列表
return all_datas
def krreadBrdXlsx(BuDatexlsx):
filepath = r’C:\Users\Public\SourceFile’
file_path = os.path.join(filepath,BuDatexlsx)
workbook = xlrd.open_workbook(file_path)
sheet1 = workbook.sheet_by_name(‘FTM’)
x = 3
alldata = []
for x in range(int(sheet1.nrows)):
if sheet1.row_values(x)[1] == None:
break
if sheet1.row_values(x)[1] == ‘AIAIM’:
alldata.append(sheet1.row_values(x))
return alldata
def krreadBrdXlsxStrDate(BuDatexlsx):
filepath = r’C:\Users\Public\SourceFile’
file_path = os.path.join(filepath,BuDatexlsx)
# print(‘file_path:’)
# print(file_path)
just_open(file_path)
workbook = xlrd.open_workbook(file_path)
# wb = load_workbook(file_path)
# ws = wb[‘FTM’]
wb = load_workbook(file_path, data_only=True)
sh = wb["FTM"]
strdate = sh[5][0].value
# print('strdate in krreadBrdXlsxStrDate:')
# print(strdate)
# strdate1 = time.strftime("%Y-%m-%d", strdate)
return strdate
def readKRBrdXlsx(BuDatexlsx):
print('readKRBrdXlsx : ‘)
# file_path = os.path.join(os.path.dirname(os.path.abspath(file)), “Mapping Table.xlsx”)
filepath = r’C:\Users\Public\SourceFile’
filename =BuDatexlsx
print(‘BuDatexlsx:’+BuDatexlsx)
file_path = os.path.join(filepath,filename)
print(‘file_path:’+file_path)
wb = load_workbook(file_path, data_only=True)
# print(wb)
sh = wb[“FTM”]
all_datas = []
# 1、拿到字典的key值
# print(list(sh.rows)[0]) # 按行读取数据 (<Cell ‘login’.A1>, <Cell ‘login’.B1>, <Cell ‘login’.C1>)
titles = [] # 定义一个空列表
for item in list(sh.rows)[4]: # 遍历第1行当中每一列
titles.append(item.value) # 列表添加值
# print(titles)
for item in list(sh.rows)[20:]: # 遍历数据行
values = []
for val in item: # 获取每一行的值
values.append(val.value)
# res = dict(zip(titles, values)) # title和每一行数据，打包成字典
# res[“expected”] = eval(res[“expected”]) # 将expected的字符串，转换为字典对象。
all_datas.append(values) # 追加到列表
return all_datas
# print(all_datas)
def readBrdXlsx_bak():
# file_path = os.path.join(os.path.dirname(os.path.abspath(file)), “Mapping Table.xlsx”)
wb = load_workbook(r’C:\wz\SourceFile\Ph_11-01-2021.xlsx’, data_only=True)
# print(wb)
sh = wb[“FTM”]
all_datas = []
# 1、拿到字典的key值
# print(list(sh.rows)[0]) # 按行读取数据 (<Cell ‘login’.A1>, <Cell ‘login’.B1>, <Cell ‘login’.C1>)
titles = [] # 定义一个空列表
for item in list(sh.rows)[0]: # 遍历第1行当中每一列
titles.append(item.value) # 列表添加值
# print(titles)
for item in list(sh.rows)[20:]: # 遍历数据行
values = []
for val in item: # 获取每一行的值
values.append(val.value)
# res = dict(zip(titles, values)) # title和每一行数据，打包成字典
# res[“expected”] = eval(res[“expected”]) # 将expected的字符串，转换为字典对象。
all_datas.append(values) # 追加到列表
return all_datas
def readBrdXlsForSingopreDaily(BuDatexlsx):
file_path = os.path.join(r’C:\Users\Public\SourceFile’, BuDatexlsx)
workbook = xlrd.open_workbook(file_path)
sheet1 = workbook.sheet_by_name(‘working’)
x = 9
alldata = []
for x in range(int(sheet1.nrows)):
if sheet1.row_values(x)[3] == ‘’:
continue
if sheet1.row_values(x)[3] != ‘’:
alldata.append(sheet1.row_values(x))
alldata1 = []
for x in range(len(alldata)):
if alldata[x][3] == ’ ':
continue
if alldata[x][3] != ’ ':
alldata1.append(alldata[x])
return alldata1
def readBrdXlsxForSingopreDaily(brdfile):
print(‘brdfile’)
print(brdfile)
# file_path = os.path.join(os.path.dirname(os.path.abspath(file)), “Mapping Table.xlsx”)
file_path = os.path.join(r’C:\Users\Public\SourceFile’,brdfile)
wb = load_workbook(file_path, data_only=True)
# print(wb)
sh = wb[“working”]
all_datas = []
# 1、拿到字典的key值
# print(list(sh.rows)[0]) # 按行读取数据 (<Cell ‘login’.A1>, <Cell ‘login’.B1>, <Cell ‘login’.C1>)
# titles = [] # 定义一个空列表
# for item in list(sh.rows)[0]: # 遍历第1行当中每一列
# titles.append(item.value) # 列表添加值
# # print(titles)
for item in list(sh.rows)[9:]: # 遍历数据行
values = []
for val in item: # 获取每一行的值
values.append(val.value)
# res = dict(zip(titles, values)) # title和每一行数据，打包成字典
# res[“expected”] = eval(res[“expected”]) # 将expected的字符串，转换为字典对象。
all_datas.append(values) # 追加到列表
return all_datas
def readBrdXlsForSingopreWeekly(BuDatexlsx):
file_path = os.path.join(r’C:\Users\Public\SourceFile’, BuDatexlsx)
# wb = load_workbook(r’C:\wz\SourceFile\SG_11-08-2021_Weekly.xlsx’, data_only=True)
# wb = load_workbook(file_path, data_only=True)
# print(wb)
workbook = xlrd.open_workbook(file_path)
# sh = wb[“Funds Transfer”]
sheet1 = workbook.sheet_by_name(‘Funds Transfer’)
x = 9
alldata = []
for x in range(int(sheet1.nrows)):
print(x)
print(type(sheet1.row_values(x)[2]))
print(sheet1.row_values(x)[2])
if sheet1.row_values(x)[2] == ‘’:
continue
if sheet1.row_values(x)[2] != ‘’:
alldata.append(sheet1.row_values(x))
return alldata
def readBrdXlsxForSingopreWeekly(BuDatexlsx):
# file_path = os.path.join(os.path.dirname(os.path.abspath(file)), “Mapping Table.xlsx”)
file_path = os.path.join(r’C:\Users\Public\SourceFile’,BuDatexlsx)
# wb = load_workbook(r’C:\wz\SourceFile\SG_11-08-2021_Weekly.xlsx’, data_only=True)
wb = load_workbook(file_path, data_only=True)
# print(wb)
sh = wb[“Funds Transfer”]
all_datas = []
# 1、拿到字典的key值
# print(list(sh.rows)[0]) # 按行读取数据 (<Cell ‘login’.A1>, <Cell ‘login’.B1>, <Cell ‘login’.C1>)
# titles = [] # 定义一个空列表
# for item in list(sh.rows)[0]: # 遍历第1行当中每一列
# titles.append(item.value) # 列表添加值
# print(titles)
for item in list(sh.rows)[9:]: # 遍历数据行
values = []
for val in item: # 获取每一行的值
values.append(val.value)
# res = dict(zip(titles, values)) # title和每一行数据，打包成字典
# res[“expected”] = eval(res[“expected”]) # 将expected的字符串，转换为字典对象。
all_datas.append(values) # 追加到列表
all_datas1 = []
for x in range(len(all_datas)):
if all_datas[x][2] != None:
all_datas1.append(all_datas[x])
# print(len(all_datas1))
return all_datas1
def readBrdXlsxForDate(BuDatexlsx):
print(‘readBrdXlsxForDate:’)
file_path = os.path.join(r’C:\Users\Public\SourceFile’, BuDatexlsx)
print('file_path : ')
print(file_path)
wb = load_workbook(file_path, data_only=True)
sh = wb["For Email"]
# str(sh["B5"].value))
# print(time.strftime('%y-%m-%d','2021-10-28'))
# print('sh["B5"].value')
# print(sh["B5"].value)
return sh["B5"].value
def krreadBrdXlsxForDate(BuDatexlsx):
print(‘readBrdXlsxForDate:’)
file_path = os.path.join(r’C:\Users\Public\SourceFile’, BuDatexlsx)
print('file_path : ')
print(file_path)
wb = load_workbook(file_path, data_only=True)
sh = wb["FTM"]
# str(sh["B5"].value))
# print(time.strftime('%y-%m-%d','2021-10-28'))
# print('sh["B5"].value')
# print(sh["B5"].value)
return sh["A5"].value
def decode(self, filename, sheetname):
try:
filename = filename.encode(‘utf-8’).decode(‘utf-8’)
sheetname = sheetname.encode(‘utf-8’).decode(‘utf-8’)
except Exception:
print( traceback.print_exc())
return filename, sheetname
def readSgBrdXlsForDate(brdfile):
path1 = r’C:\Users\Public\SourceFile’
file_path = os.path.join(path1, brdfile)
sheetname = ‘working’
# filename, sheetname = self.decode(file_path, sheetname)
filename = decode(file_path)
sheetname = decode(sheetname)
rbook = xlrd.open_workbook(filename)
sheet = rbook.sheet_by_name(sheetname)
cell = sheet.cell_value(1, 2)
print(cell)
date = datetime(*xldate_as_tuple(cell, 0))
cell = date.strftime(’%Y/%d/%m’)
print(cell)
return cell
def readSgBrdXlsxForDate(brdfile):
# path = r’C:\wz\SourceFile\SG_11-08-2021_Weekly.xlsx’
path1 = r’C:\Users\Public\SourceFile’
path = os.path.join(path1,brdfile)
wb = load_workbook(path, data_only=True)
# wb = load_workbook(r’C:\wz\SourceFile\SG_11-08-2021.xlsx’, data_only=True)
# print(wb)
sh = wb[“working”]
# str(sh[“B5”].value))
# print(time.strftime(’%y-%m-%d’,‘2021-10-28’))
return sh[“C2”].value
def readSFLxlsx():
print(‘start to read Fund Scope List’)
wb = load_workbook()
sheetnames = wb.get_sheet_names(‘sheet1’)
print(‘该表格共有%d个sheet格分别为：%s’ % (len(sheetnames), sheetnames))
for k in range(0, len(sheetnames)):
ws = wb.get_sheet_by_name(sheetnames[k])
rows = ws.max_row
columns = ws.max_column
if rows - 1 == 0 and columns - 1 == 0:
break
else:
print(‘loading…正在打印第%d个sheet数据…’ % (k + 1))
print(‘此为第%d个sheet，共有%d行，%d列’ % (k + 1, rows - 1, columns))
for i in range(3, rows + 1):
core_list = []
for j in range(1, columns + 1):
str = ws.cell(i, j).value
core_list.append(str)
print(core_list)
def write_to_excel(path: str, info, data):
# 实例化一个workbook对象
workbook = openpyxl.Workbook()
# 激活一个sheet
sheet1 = workbook.create_sheet(“mysheet”)
sheet = workbook.active
# 为sheet设置一个title
sheet.title = ‘Template’
# 添加表头（不需要表头可以不用加）
data.insert(0, list(info))
# 开始遍历数组
for row_index, row_item in enumerate(data):
for col_index, col_item in enumerate(row_item):
# 写入
print('col_item:')
print(col_item)
sheet.cell(row=row_index + 1, column=col_index + 1, value=col_item)
print('插入一条记录')
# 写入excel文件 如果path路径的文件不存在那么就会自动创建
workbook.save(path)
time.sleep(0.1)
print('写入成功')
def write_to_excel1(path: str, sheetStr, info, data,bu):
# 实例化一个workbook对象
workbook = openpyxl.Workbook()
# 激活一个sheet
sheet1 = workbook.create_sheet(“mysheet”)
sheet = workbook.active
# 为sheet设置一个title
sheet.title = ‘Template’
# 添加表头（不需要表头可以不用加）
data.insert(0, list(info))
# 开始遍历数组
for row_index, row_item in enumerate(data):
for col_index, col_item in enumerate(row_item):
# 写入
sheet.cell(row=row_index + 1, column=col_index + 1, value=col_item)
# 写入excel文件 如果path路径的文件不存在那么就会自动创建
workbook.save(path)
print('写入成功')
def readCashFlowlAdjuestmentFilexlsx(Cash_Flow_Template):
# file_path = os.path.join(os.path.dirname(os.path.abspath(file)), “Mapping Table.xlsx”)
path = r’C:\Users\Public\AdjustmentFile’
file_path = os.path.join(path, Cash_Flow_Template)
print(‘readCashFlowlAdjuestmentFilexlsx’)
print('file_path : ‘+file_path)
just_open(file_path)
# tempFileName = Cash Flow Adjustment File(Temp).xlsx
# file_path = os.path.join(os.path.dirname(os.path.abspath(file)), “Mapping Table.xlsx”)
# wb = load_workbook(r’C:\wz\SourceFile\Cash Flow Adjustment File(Temp).xlsx’, data_only=True)
wb = load_workbook(file_path, data_only=True)
# wb = load_workbook(file_path, data_only=True)
# print(wb)
sh = wb[“Template”]
all_datas = []
# 1、拿到字典的key值
# print(list(sh.rows)[0]) # 按行读取数据 (<Cell ‘login’.A1>, <Cell ‘login’.B1>, <Cell ‘login’.C1>)
titles = [] # 定义一个空列表
for item in list(sh.rows)[0]: # 遍历第1行当中每一列
titles.append(item.value) # 列表添加值
# print(titles)
for item in list(sh.rows)[1:]: # 遍历数据行
values = []
for val in item: # 获取每一行的值
values.append(val.value)
# res = dict(zip(titles, values)) # title和每一行数据，打包成字典
# res[“expected”] = eval(res[“expected”]) # 将expected的字符串，转换为字典对象。
all_datas.append(values) # 追加到列表
return all_datas
# print(all_datas)
#写入excel
def write_to_cfexcel(path: str, info, data):
# 实例化一个workbook对象
workbook = openpyxl.Workbook()
# 激活一个sheet
# sheet1 = workbook.create_sheet(“mysheet”)
sheet = workbook.active
# 为sheet设置一个title
sheet.title = ‘Template’
# 添加表头（不需要表头可以不用加）
data.insert(0, list(info))
# 开始遍历数组
for row_index, row_item in enumerate(data):
for col_index, col_item in enumerate(row_item):
# 写入
sheet.cell(row=row_index + 1, column=col_index + 1, value=col_item)
# 写入excel文件 如果path路径的文件不存在那么就会自动创建
workbook.save(path)
print('写入成功')
#写入vacationforbu
def write_to_vacationbuexcel(path,data):
# 实例化一个workbook对象
# data = [‘holiday’,‘specialworkday’]
workbook = openpyxl.Workbook()
# 激活一个sheet
# SG_sheet = sgworkbook.active
# sheet1 = workbook.create_sheet(“mysheet”)
# 为sheet设置一个title
SG_sheet = workbook.create_sheet(“SG”,0)
SG_sheet.cell(row=1, column=1, value=data[0])
SG_sheet.cell(row=1, column=2, value=data[1])
PH_sheet = workbook.create_sheet("PH",1)
PH_sheet.cell(row=1, column=1, value=data[0])
PH_sheet.cell(row=1, column=2, value=data[1])
KR_sheet = workbook.create_sheet("KR",2)
KR_sheet.cell(row=1, column=1, value=data[0])
KR_sheet.cell(row=1, column=2, value=data[1])
workbook.save(path)
print('写入成功')
#生成template
def templateFileOutput(BuDatexlsx):
Brdnum = readBrdXlsx(BuDatexlsx)
num = readSFLxlsx1()
bucode = ‘PH’
frquency = ‘daily’
bUnum = []
for x in range(len(num)):
if num[x][0] == bucode:
bUnum.append(num[x])
temp = []
flp = r’C:\Users\Public\SourceFile’
fn = BuDatexlsx
file_path1 = os.path.join(flp, BuDatexlsx)
print('file_path1:')
print(file_path1)
#BuDatexlsx
strDate = str(readBrdXlsxForDate(file_path1))
print(strDate.split(' ')[0])
for x in range(len(bUnum)):
strv = '' # 获取v的数值大小
for j in range(len(Brdnum)):
if bUnum[x][1] == Brdnum[j][0]:
strv = Brdnum[j][21]
# print(strv>0)
if strv == '':
continue
if strv > 0:
# 0:BU Code 1:Fund Name 2:AIA Fund Code 3:FA Fund Code 4:Frequency 5:BBG Acc Code 6:CCY 7:Tran type 8:Date
# 9:Daliy/weekly flows 10:Reinvestment(Manual input) 11:Reinvestment(Manual input) 12:Fee payment(Manual input)
# 13:Addition cash 1(Manual input) 14:Addition cash 2(Manual input) 15:Addition cash 3(Manual input)
# 16:Addition cash 4(Manual input) 17:Addition cash 5(Manual input) 18:Final Flow
var = [bucode, bUnum[x][3], bUnum[x][1], bUnum[x][2], frquency, bUnum[x][4], bUnum[x][5], 'SUB',
strDate.split(' ')[0], strv, '', '', '', '', '', '', '',
'=SUM(J' + str(x + 2) + ':Q' + str(x + 2) + ')']
temp.append(var)
elif strv < 0:
# var = [bucode, bUnum[x][3], bUnum[x][1], bUnum[x][2], frquency, bUnum[x][4], bUnum[x][5], 'RED',
# strDate.split(' ')[0], strv, '', '', '', '', '', '', '',
# '=SUM(J' + str(x + 2) + ':Q' + str(x + 2) + ')']
# temp.append(var)
var = [bucode, bUnum[x][3], bUnum[x][1], bUnum[x][2], frquency, bUnum[x][4], bUnum[x][5], 'RED',
strDate.split(' ')[0], strv, '', '', '', '', '', '', '',
'=SUM(J' + str(x + 2) + ':Q' + str(x + 2) + ')']
temp.append(var)
elif strv == 0:
var = [bucode, bUnum[x][3], bUnum[x][1], bUnum[x][2], frquency, bUnum[x][4], bUnum[x][5], '',
strDate.split(' ')[0], "Transfer Amount equal 0", '', '', '', '', '', '', '',
'=SUM(J' + str(x + 2) + ':Q' + str(x + 2) + ')']
temp.append(var)
# print(len(temp))
info = ['BU Code', 'Fund Name', 'AIA Fund Code', 'FA Fund Code', 'Frequency', 'BBG Acc Code', 'CCY',
'Tran type', 'Date',
'Daily/weekly flows', 'Reinvestment', 'Fee payment', 'Addition cash 1', 'Addition cash 2',
'Addition cash 3', 'Addition cash 4', 'Addition cash 5', 'Final Flow']
# path = r'C:\Users\Public\SourceFile\crashflowtemplate.xlsx'
ffpath = r'C:\Users\Public\AdjustmentFile'
dtf = time.strftime("%Y%m%d", time.localtime())
# ffn = 'CashFlow_PH_Daily_Template.xlsx'
ffn = 'CashFlow_PH_Adjustment_Daily'+dtf+'.xlsx'
# file = open(r"C:\Users\Public\SourceFile\fileName.txt", "a")
with open(r"C:\Users\Public\SourceFile\pageConfig.txt", "w", encoding='utf-8') as f:
f.write(ffn)
ffnpath = os.path.join(ffpath, ffn)
# write_to_excel(ffnpath, info, temp)
write_to_excel(ffnpath, info, temp)
time.sleep(0.1)
#设置行的颜色
wb = load_workbook(ffnpath)
sht = wb.worksheets[0]
time.sleep(0.1)
path = r'C:\Users\Public\AdjustmentFile'
path1 = os.path.join(path, ffn)
wb = openpyxl.load_workbook(path1, data_only=False)
fill_3 = PatternFill("solid", fgColor='9c0006')
names = wb.sheetnames
sheet = wb[names[0]]
sht = wb.worksheets[0]
str1 = []
for one_column_data in sheet.iter_rows():
print(one_column_data[7].value)
if one_column_data[7].value == None:
str1.append(1)
else:
str1.append('no fill')
print(str1)
for x in range(len(str1)):
if str1[x] == 1:
strx = str(x + 1)
Ax = "A" + strx
Bx = "B" + strx
Cx = "C" + strx
Dx = "D" + strx
Ex = "E" + strx
Fx = "F" + strx
Gx = "G" + strx
Hx = "H" + strx
Ix = "I" + strx
jx = "j" + strx
Kx = "K" + strx
Lx = "L" + strx
Mx = "M" + strx
Nx = "N" + strx
Ox = "O" + strx
Px = "P" + strx
Qx = "Q" + strx
Rx = "R" + strx
print(Ax)
sht[Ax].fill = fill_3
sht[Bx].fill = fill_3
sht[Cx].fill = fill_3
sht[Dx].fill = fill_3
sht[Ex].fill = fill_3
sht[Fx].fill = fill_3
sht[Gx].fill = fill_3
sht[Hx].fill = fill_3
sht[Ix].fill = fill_3
sht[jx].fill = fill_3
sht[Kx].fill = fill_3
sht[Lx].fill = fill_3
sht[Mx].fill = fill_3
sht[Nx].fill = fill_3
sht[Ox].fill = fill_3
sht[Px].fill = fill_3
sht[Qx].fill = fill_3
sht[Rx].fill = fill_3
print('fill done!')
wb.save(path1)
os.startfile(ffnpath)
#KR
def templateKRFileOutput(BuDatexlsx):
# readKRBrdXlsx()
Brdnum = krreadBrdXlsx(BuDatexlsx)
print('Brdnum')
print(Brdnum)
num = readSFLxlsx1()
print('num')
print(num)
bucode = 'KR'
frquency = 'daily'
# bUnum = []
# for x in range(len(num)):
# if num[x][0] == bucode:
# bUnum.append(num[x])
temp = []
flp = r'C:\Users\Public\SourceFile'
file_path1 = os.path.join(flp, BuDatexlsx)
print('file_path1:')
print(file_path1)
#BuDatexlsx
# strDate = str(krreadBrdXlsxForDate(file_path1))
# print(strDate.split(' ')[0])
# strDate = '2021-11-26'
strDate = str(krreadBrdXlsxStrDate(BuDatexlsx))
strDate = str(strDate).split(' ')[0]
dt = datetime.datetime.strptime(strDate, "%Y-%m-%d")
strDate = dt.strftime("%Y-%m-%d")
print('strDate:')
print(strDate)
print('---')
# getnull = []
# for x in range(len(num)):
# strv1 = '' # 获取v的数值大小
# for j in range(len(Brdnum)):
# if num[x][1] == Brdnum[j][2]:
# # strv1 = Brdnum[j][8]
# strv1 = Brdnum[j][9]
# if strv1 == '':
# getnull.append(1)
for x in range(len(num)):
strv = '' # 获取v的数值大小
# print('len(num):')
# print(len(num))
# print('len(Brdnum):')
# print(len(Brdnum))
for j in range(len(Brdnum)):
# print('Fundcode:')
Fundcode = str(Brdnum[j][2]).split('.')[0]
# print(Fundcode)
# print('num[x][1]:')
# print(num[x][1])
# print('num[x][1] == Fundcode:')
# print(num[x][1] == Fundcode)
if num[x][1] == Brdnum[j][2]:
print(num[x][1] == Fundcode)
print('Brdnum[j][9]')
print(Brdnum[j][9])
strv = Brdnum[j][9]
# print('strv:')
# strv = str(strv).split('.')[0]
# print('strv:')
# print(strv)
# strv =int(float(strv))
# strv = int(strv)
if strv == '':
continue
if strv > 0:
# 0:BU Code 1:Fund Name 2:AIA Fund Code 3:FA Fund Code 4:Frequency 5:BBG Acc Code 6:CCY 7:Tran type 8:Date
# 9:Daliy/weekly flows 10:Reinvestment(Manual input) 11:Reinvestment(Manual input) 12:Fee payment(Manual input)
# 13:Addition cash 1(Manual input) 14:Addition cash 2(Manual input) 15:Addition cash 3(Manual input)
# 16:Addition cash 4(Manual input) 17:Addition cash 5(Manual input) 18:Final Flow
# '=SUM(J' + str(x + 2) + ':Q' + str(x + 2) + ')'
var = [bucode, num[x][3], num[x][1], num[x][2], frquency, num[x][4], num[x][5], 'SUB',
strDate, strv, '', '', '', '', '', '', '']
temp.append(var)
elif strv < 0:
# var = [bucode, bUnum[x][3], bUnum[x][1], bUnum[x][2], frquency, bUnum[x][4], bUnum[x][5], 'RED',
# strDate.split(' ')[0], strv, '', '', '', '', '', '', '',
# '=SUM(J' + str(x + 2) + ':Q' + str(x + 2) + ')']
# temp.append(var)
var = [bucode, num[x][3], num[x][1], num[x][2], frquency, num[x][4], num[x][5], 'RED',
strDate, strv, '', '', '', '', '', '', '']
temp.append(var)
elif strv == 0:
var = [bucode, num[x][3], num[x][1], num[x][2], frquency, num[x][4], num[x][5], '',
strDate, "Net Amount equal 0", '', '', '', '', '', '', '']
temp.append(var)
# print(len(temp))
for x in range(len(temp)):
# print(x)
temp[x].append('=SUM(J' + str(x + 2) + ':Q' + str(x + 2) + ')')
info = ['BU Code', 'Fund Name', 'AIA Fund Code', 'FA Fund Code', 'Frequency', 'BBG Acc Code', 'CCY',
'Tran type', 'Date',
'Daily/weekly flows', 'Reinvestment', 'Fee payment', 'Addition cash 1', 'Addition cash 2',
'Addition cash 3', 'Addition cash 4', 'Addition cash 5', 'Final Flow']
# path = r'C:\Users\Public\SourceFile\crashflowtemplate.xlsx'
print('temp:')
print(temp)
print(temp[0][-1])
ffpath = r'C:\Users\Public\AdjustmentFile'
# CrashFlow_KR_Daily_Template
dtf = time.strftime("%Y%m%d", time.localtime())
# ffn = 'CashFlow_KR_Daily_Template.xlsx'
ffn = 'CashFlow_KR_Adjustment_Daily'+dtf+'.xlsx'
# file = open(r"C:\Users\Public\SourceFile\fileName.txt", "a")
with open(r"C:\Users\Public\SourceFile\pageConfig.txt", "w", encoding='utf-8') as f:
f.write(ffn)
ffnpath = os.path.join(ffpath, ffn)
# write_to_excel(ffnpath, info, temp)
path = os.path.join(r'C:\Users\Public\AdjustmentFile', ffn)
write_to_excel(path, info, temp)
time.sleep(0.1)
#设置行的颜色
wb = load_workbook(ffnpath)
sht = wb.worksheets[0]
time.sleep(0.1)
path = r'C:\Users\Public\AdjustmentFile'
path1 = os.path.join(path, ffn)
wb = openpyxl.load_workbook(path1, data_only=False)
fill_3 = PatternFill("solid", fgColor='9c0006')
names = wb.sheetnames
sheet = wb[names[0]]
sht = wb.worksheets[0]
str1 = []
for one_column_data in sheet.iter_rows():
print(one_column_data[7].value)
if one_column_data[7].value == None:
str1.append(1)
else:
str1.append('no fill')
print(str1)
for x in range(len(str1)):
if str1[x] == 1:
strx = str(x + 1)
Ax = "A" + strx
Bx = "B" + strx
Cx = "C" + strx
Dx = "D" + strx
Ex = "E" + strx
Fx = "F" + strx
Gx = "G" + strx
Hx = "H" + strx
Ix = "I" + strx
jx = "j" + strx
Kx = "K" + strx
Lx = "L" + strx
Mx = "M" + strx
Nx = "N" + strx
Ox = "O" + strx
Px = "P" + strx
Qx = "Q" + strx
Rx = "R" + strx
print(Ax)
sht[Ax].fill = fill_3
sht[Bx].fill = fill_3
sht[Cx].fill = fill_3
sht[Dx].fill = fill_3
sht[Ex].fill = fill_3
sht[Fx].fill = fill_3
sht[Gx].fill = fill_3
sht[Hx].fill = fill_3
sht[Ix].fill = fill_3
sht[jx].fill = fill_3
sht[Kx].fill = fill_3
sht[Lx].fill = fill_3
sht[Mx].fill = fill_3
sht[Nx].fill = fill_3
sht[Ox].fill = fill_3
sht[Px].fill = fill_3
sht[Qx].fill = fill_3
sht[Rx].fill = fill_3
print('fill done!')
wb.save(path1)
os.startfile(ffnpath)
#KR
def templateKRFileOutput_bak(BuDatexlsx):
# readKRBrdXlsx()
Brdnum = krreadBrdXlsx(BuDatexlsx)
print(‘Brdnum’)
print(Brdnum)
num = readSFLxlsx1()
print(‘num’)
print(num)
bucode = 'KR'
frquency = 'daily'
# bUnum = []
# for x in range(len(num)):
# if num[x][0] == bucode:
# bUnum.append(num[x])
temp = []
flp = r'C:\Users\Public\SourceFile'
file_path1 = os.path.join(flp, BuDatexlsx)
print('file_path1:')
print(file_path1)
#BuDatexlsx
# strDate = str(krreadBrdXlsxForDate(file_path1))
# print(strDate.split(' ')[0])
strDate = '2021-11-26'
getnull = []
for x in range(len(num)):
strv1 = '' # 获取v的数值大小
for j in range(len(Brdnum)):
if num[x][1] == Brdnum[j][2]:
# strv1 = Brdnum[j][8]
strv1 = Brdnum[j][9]
if strv1 == '':
getnull.append(1)
nulllen = len(getnull)
for x in range(len(num)):
strv = '' # 获取v的数值大小
# print('len(num):')
# print(len(num))
# print('len(Brdnum):')
# print(len(Brdnum))
for j in range(len(Brdnum)):
# print('Fundcode:')
Fundcode = str(Brdnum[j][2]).split('.')[0]
# print(Fundcode)
# print('num[x][1]:')
# print(num[x][1])
# print('num[x][1] == Fundcode:')
# print(num[x][1] == Fundcode)
if num[x][1] == Brdnum[j][2]:
print(num[x][1] == Fundcode)
print('Brdnum[j][9]')
print(Brdnum[j][9])
strv = Brdnum[j][9]
# print('strv:')
# strv = str(strv).split('.')[0]
# print('strv:')
# print(strv)
# strv =int(float(strv))
# strv = int(strv)
if strv == '':
continue
if strv > 0:
# 0:BU Code 1:Fund Name 2:AIA Fund Code 3:FA Fund Code 4:Frequency 5:BBG Acc Code 6:CCY 7:Tran type 8:Date
# 9:Daliy/weekly flows 10:Reinvestment(Manual input) 11:Reinvestment(Manual input) 12:Fee payment(Manual input)
# 13:Addition cash 1(Manual input) 14:Addition cash 2(Manual input) 15:Addition cash 3(Manual input)
# 16:Addition cash 4(Manual input) 17:Addition cash 5(Manual input) 18:Final Flow
# '=SUM(J' + str(x + 2) + ':Q' + str(x + 2) + ')'
var = [bucode, num[x][3], num[x][1], num[x][2], frquency, num[x][4], num[x][5], 'SUB',
strDate, strv, '', '', '', '', '', '', '','=SUM(J' + str(x + 2 -nulllen) + ':Q' + str(x + 2 - nulllen) + ')']
temp.append(var)
elif strv < 0:
# var = [bucode, bUnum[x][3], bUnum[x][1], bUnum[x][2], frquency, bUnum[x][4], bUnum[x][5], 'RED',
# strDate.split(' ')[0], strv, '', '', '', '', '', '', '',
# '=SUM(J' + str(x + 2) + ':Q' + str(x + 2) + ')']
# temp.append(var)
var = [bucode, num[x][3], num[x][1], num[x][2], frquency, num[x][4], num[x][5], 'RED',
strDate.split(' ')[0], strv, '', '', '', '', '', '', '',
'=SUM(J' + str(x + 2) + '=SUM(J' + str(x + 2 -nulllen) + ':Q' + str(x + 2 -nulllen) + ')']
temp.append(var)
elif strv == 0:
var = [bucode, num[x][3], num[x][1], num[x][2], frquency, num[x][4], num[x][5], 'blank',
strDate.split(' ')[0], "Transfer Amount equal 0", '', '', '', '', '', '', '',
'=SUM(J' + str(x + 2 -nulllen) + ':Q' + str(x + 2 -nulllen) + ')']
temp.append(var)
# print(len(temp))
info = ['BU Code', 'Fund Name', 'AIA Fund Code', 'FA Fund Code', 'Frequency', 'BBG Acc Code', 'CCY',
'Tran type', 'Date',
'Daily/weekly flows', 'Reinvestment', 'Fee payment', 'Addition cash 1', 'Addition cash 2',
'Addition cash 3', 'Addition cash 4', 'Addition cash 5', 'Final Flow']
# path = r'C:\Users\Public\SourceFile\crashflowtemplate.xlsx'
print('temp:')
print(temp)
print(temp[0][-1])
ffpath = r'C:\Users\Public\AdjustmentFile'
# CrashFlow_KR_Daily_Template
ffn = 'CashFlow_KR_Daily_Template.xlsx'
# file = open(r"C:\Users\Public\SourceFile\fileName.txt", "a")
with open(r"C:\Users\Public\SourceFile\pageConfig.txt", "w", encoding='utf-8') as f:
f.write(ffn)
ffnpath = os.path.join(ffpath, ffn)
# write_to_excel(ffnpath, info, temp)
path = os.path.join(r'C:\Users\Public\AdjustmentFile', 'CashFlow_KR_Daily_Template.xlsx')
write_to_excel(path, info, temp)
time.sleep(0.1)
#设置行的颜色
wb = load_workbook(ffnpath)
sht = wb.worksheets[0]
time.sleep(0.1)
path = r'C:\Users\Public\AdjustmentFile'
path1 = os.path.join(path, ffn)
wb = openpyxl.load_workbook(path1, data_only=False)
fill_3 = PatternFill("solid", fgColor='9c0006')
names = wb.sheetnames
sheet = wb[names[0]]
sht = wb.worksheets[0]
str1 = []
for one_column_data in sheet.iter_rows():
print(one_column_data[7].value)
if one_column_data[7].value == None:
str1.append(1)
else:
str1.append('no fill')
print(str1)
for x in range(len(str1)):
if str1[x] == 1:
strx = str(x + 1)
Ax = "A" + strx
Bx = "B" + strx
Cx = "C" + strx
Dx = "D" + strx
Ex = "E" + strx
Fx = "F" + strx
Gx = "G" + strx
Hx = "H" + strx
Ix = "I" + strx
jx = "j" + strx
Kx = "K" + strx
Lx = "L" + strx
Mx = "M" + strx
Nx = "N" + strx
Ox = "O" + strx
Px = "P" + strx
Qx = "Q" + strx
Rx = "R" + strx
print(Ax)
sht[Ax].fill = fill_3
sht[Bx].fill = fill_3
sht[Cx].fill = fill_3
sht[Dx].fill = fill_3
sht[Ex].fill = fill_3
sht[Fx].fill = fill_3
sht[Gx].fill = fill_3
sht[Hx].fill = fill_3
sht[Ix].fill = fill_3
sht[jx].fill = fill_3
sht[Kx].fill = fill_3
sht[Lx].fill = fill_3
sht[Mx].fill = fill_3
sht[Nx].fill = fill_3
sht[Ox].fill = fill_3
sht[Px].fill = fill_3
sht[Qx].fill = fill_3
sht[Rx].fill = fill_3
print('fill done!')
wb.save(path1)
os.startfile(ffnpath)
def templateFileOutputSgDaily(BuDatexlsx):
# daily 数据封装
num = readSFLxlsx1()
print(‘num:’)
print(num)
if BuDatexlsx.split('.')[1] == 'xls':
SgBrdnum = readBrdXlsForSingopreDaily(BuDatexlsx)
eh = excelHandle()
# filename = r'C:\Users\Public\SourceFile\SG_2021-12-29_Weekly.xls'
filename = os.path.join(r'C:\Users\Public\SourceFile',BuDatexlsx)
sheetname = 'working'
strDate = eh.read_excel(filename, sheetname)
else:
SgBrdnum = readBrdXlsxForSingopreDaily(BuDatexlsx)
strDate = readSgBrdXlsxForDate(BuDatexlsx)
bucode = 'SG'
getnull = []
for x in range(len(num)):
strv1 = '' # 获取v的数值大小
for j in range(len(SgBrdnum)):
if num[x][1] == SgBrdnum[j][2]:
strv1 = SgBrdnum[j][8]
if strv1 == '':
getnull.append(1)
n = 0;
temp = []
for x in range(len(num)):
strM = '' # 获取M列的数值大小
strN = '' # 获取N列的数值大小
for j in range(len(SgBrdnum)):
if num[x][1] == SgBrdnum[j][3]:
print('bUnum[x][1] == SgBrdnum[j][3]:')
print(num[x][1] == SgBrdnum[j][3])
print('bUnum[x][1]:')
print(num[x][1])
print('SgBrdnum[j][3]:')
print(SgBrdnum[j][3])
strM = SgBrdnum[j][12]
# print('strM:'+strM)
strN = SgBrdnum[j][13]
if strM == 0 and strN != 0:
print('strN:')
print(strN)
print('x:')
print(x)
var = [bucode, num[x][3], num[x][1], num[x][2], 'Daily', num[x][4], num[x][5], 'RED', strDate,
strN, '', '', '', '', '', '', '']
temp.append(var)
if strM != 0 and strN == 0:
print('strM:')
print(strM)
print('x:')
print(x)
var = [bucode, num[x][3], num[x][1], num[x][2], 'Daily', num[x][4], num[x][5],
'SUB', strDate, strM, '', '', '', '', '', '', '']
temp.append(var)
if strM == 0 and strN == 0: # The row is to colour fill by red as an alert to users.
print('x:')
print(x)
n = x
var = [bucode, num[x][3], num[x][1], num[x][2], 'Daily', num[x][4], num[x][5],
'', strDate, 'Both SUB & RED equal 0', '', '', '', '', '', '', '']
temp.append(var)
if strM != 0 and strN != 0: # The row is to colour fill by red as an alert to users.
print('x:')
print(x)
n = x
var = [bucode, num[x][3], num[x][1], num[x][2], 'Daily', num[x][4], num[x][5],
'', strDate, 'Both SUB & RED not equal 0', '', '', '', '', '', '', '']
temp.append(var)
for x in range(len(temp)):
# print(x)
temp[x].append('=SUM(J' + str(x + 2) + ':Q' + str(x + 2) + ')')
info = ['BU Code', 'Fund Name', 'AIA Fund Code', 'FA Fund Code', 'Frequency', 'BBG Acc Code', 'CCY', 'Tran type',
'Date', 'Daily/weekly flows', 'Reinvestment', 'Fee payment', 'Addition cash 1', 'Addition cash 2',
'Addition cash 3', 'Addition cash 4', 'Addition cash 5', 'Final Flow']
dtf = time.strftime("%Y%m%d", time.localtime())
# ffn = 'CashFlow_SG_template_daily.xlsx'
ffn = 'CashFlow_SG_Adjustment_daily'+dtf+'.xlsx'
with open(r"C:\Users\Public\SourceFile\pageConfig.txt", "w", encoding='utf-8') as f:
f.write(ffn)
# path = r'C:\wz\SourceFile\crashflow_sg_template.xlsx'
path = os.path.join(r'C:\Users\Public\AdjustmentFile', ffn)
write_to_excel(path, info, temp)
time.sleep(0.1)
wb = openpyxl.load_workbook(path, data_only=False)
fill_3 = PatternFill("solid", fgColor='9c0006')
names = wb.sheetnames
sheet = wb[names[0]]
sht = wb.worksheets[0]
str1 = []
for one_column_data in sheet.iter_rows():
print(one_column_data[7].value)
if one_column_data[7].value == None:
str1.append(1)
else:
str1.append('no fill')
# print(str1)
for x in range(len(str1)):
if str1[x] == 1:
strx = str(x + 1)
Ax = "A" + strx
Bx = "B" + strx
Cx = "C" + strx
Dx = "D" + strx
Ex = "E" + strx
Fx = "F" + strx
Gx = "G" + strx
Hx = "H" + strx
Ix = "I" + strx
jx = "j" + strx
Kx = "K" + strx
Lx = "L" + strx
Mx = "M" + strx
Nx = "N" + strx
Ox = "O" + strx
Px = "P" + strx
Qx = "Q" + strx
Rx = "R" + strx
print(Ax)
sht[Ax].fill = fill_3
sht[Bx].fill = fill_3
sht[Cx].fill = fill_3
sht[Dx].fill = fill_3
sht[Ex].fill = fill_3
sht[Fx].fill = fill_3
sht[Gx].fill = fill_3
sht[Hx].fill = fill_3
sht[Ix].fill = fill_3
sht[jx].fill = fill_3
sht[Kx].fill = fill_3
sht[Lx].fill = fill_3
sht[Mx].fill = fill_3
sht[Nx].fill = fill_3
sht[Ox].fill = fill_3
sht[Px].fill = fill_3
sht[Qx].fill = fill_3
sht[Rx].fill = fill_3
print('fill done!')
wb.save(path)
os.startfile(path)
def templateFileOutputSgWeekly(BuDatexlsx):
# 生成singapore weekly template
num = readSFLxlsx1()
if BuDatexlsx.split(’.’)[1] == ‘xls’:
SgBrdnum = readBrdXlsForSingopreWeekly(BuDatexlsx)
eh = excelHandle()
filename = os.path.join(r’C:\Users\Public\SourceFile’,BuDatexlsx)
sheetname = ‘working’
strDate = eh.read_excel(filename, sheetname)
else:
SgBrdnum = readBrdXlsxForSingopreWeekly(BuDatexlsx)
strDate = readSgBrdXlsxForDate(BuDatexlsx)
print(‘SgBrdnum : ‘)
print(SgBrdnum)
print(’—’)
for e in range(len(SgBrdnum)):
print(SgBrdnum[e][3])
print(SgBrdnum[e][5])
print('---')
# 获取BUCode为SG的list,bUnum
bucode = 'SG'
frquency = 'Weekily'
bUnum = []
for x in range(len(num)):
if num[x][0] == bucode:
bUnum.append(num[x])
# 比对mapping table已筛选出SG的bUnum已筛选出Singpore source file 的记录
MpTab_Sg = [] # 已筛选list
for x in range(len(bUnum)):
for j in range(len(SgBrdnum)):
if bUnum[x][1] == SgBrdnum[j][2]:
MpTab_Sg.append(bUnum[x])
# print('MpTab_Sg')
# print(MpTab_Sg)
strDate = str(strDate).split(' ')[0]
# arrstrDate = strDate1.split('-')
# strDate = arrstrDate[0] + arrstrDate[1] + arrstrDate[2]
# strDate = datetime.datetime.strptime()(strDate)
# strDate = strDate.strftime('%Y%m%d %H:%M:%S')
# print('strDate:')
# print(strDate)
temp = []
for x in range(len(bUnum)):
# print(x)
strD = '' # 获取M列的数值大小
strF = '' # 获取N列的数值大小
for j in range(len(SgBrdnum)):
if bUnum[x][1] == SgBrdnum[j][2]:
strD = SgBrdnum[j][3]#Col D
strF = SgBrdnum[j][5]#Col F
print('strD:')
print(strD)
print('strF:')
print(strF)
if strD == '' and strF != '':
print('--oo--')
print('strF:')
print(strF)
var = [bucode, bUnum[x][3], bUnum[x][1], bUnum[x][2], 'Weekly', bUnum[x][4],bUnum[x][5],
'RED', strDate, strF, '', '', '', '', '', '', '']
temp.append(var)
if strD != '' and strF == '':
print('--oo--')
print('strD:')
print(strD)
var = [bucode, bUnum[x][3], bUnum[x][1], bUnum[x][2], 'Weekly', bUnum[x][4],bUnum[x][5],
'SUB', strDate, strD, '', '', '', '', '', '', '']
temp.append(var)
if strD == '' and strF == '': # The row is to colour fill by red as an alert to users.
print('--oo--')
print('strD:')
print(strD)
print('strF:')
print(strF)
var = [bucode, bUnum[x][3], bUnum[x][1], bUnum[x][2], 'Weekly', bUnum[x][4],bUnum[x][5],
'', strDate, 'Both SUB & RED equal 0', '', '', '', '', '', '', '']
temp.append(var)
if strD != '' and strF != '': # The row is to colour fill by red as an alert to users.
print(strD)
print(strD != None)
print(strF)
print(strF != None)
print('--oo--1')
print('strD:')
print(strD)
print('strF:')
print(strF)
var = [bucode, bUnum[x][3], bUnum[x][1], bUnum[x][2], 'Weekly', bUnum[x][4], bUnum[x][5],
'', strDate, 'Both SUB & RED not equal 0', '', '', '', '', '', '', '']
temp.append(var)
for x in range(len(temp)):
# print(x)
temp[x].append('=SUM(J' + str(x + 2) + ':Q' + str(x + 2) + ')')
info = ['BU Code', 'Fund Name', 'AIA Fund Code', 'FA Fund Code', 'Frequency', 'BBG Acc Code', 'CCY', 'Tran type',
'Date', 'Daily/weekly flows', 'Reinvestment', 'Fee payment', 'Addition cash 1', 'Addition cash 2',
'Addition cash 3', 'Addition cash 4', 'Addition cash 5', 'Final Flow']
pathc = r'C:\Users\Public\SourceFile'
pathconfig = os.path.join(pathc,'pageConfig.txt')
# ffn = 'CashFlow_SG_template_Weekly.xlsx'
dtf = time.strftime("%Y%m%d", time.localtime())
ffn = 'CashFlow_SG_Adjustment_Weekly'+dtf+'.xlsx'
path = r'C:\Users\Public\AdjustmentFile'
path1 = os.path.join(path,ffn)
with open(pathconfig, "w", encoding='utf-8') as f:
f.write(ffn)
write_to_excel(path1, info, temp)
time.sleep(0.1)
wb = openpyxl.load_workbook(path1, data_only=False)
fill_3 = PatternFill("solid", fgColor='9c0006')
names = wb.sheetnames
sheet = wb[names[0]]
sht = wb.worksheets[0]
str1 = []
for one_column_data in sheet.iter_rows():
# print(one_column_data[7].value)
if one_column_data[7].value == None:
str1.append(1)
else:
str1.append('no fill')
# print(str1)
for x in range(len(str1)):
if str1[x] == 1:
strx = str(x + 1)
Ax = "A" + strx
Bx = "B" + strx
Cx = "C" + strx
Dx = "D" + strx
Ex = "E" + strx
Fx = "F" + strx
Gx = "G" + strx
Hx = "H" + strx
Ix = "I" + strx
jx = "j" + strx
Kx = "K" + strx
Lx = "L" + strx
Mx = "M" + strx
Nx = "N" + strx
Ox = "O" + strx
Px = "P" + strx
Qx = "Q" + strx
Rx = "R" + strx
# print(Ax)
sht[Ax].fill = fill_3
sht[Bx].fill = fill_3
sht[Cx].fill = fill_3
sht[Dx].fill = fill_3
sht[Ex].fill = fill_3
sht[Fx].fill = fill_3
sht[Gx].fill = fill_3
sht[Hx].fill = fill_3
sht[Ix].fill = fill_3
sht[jx].fill = fill_3
sht[Kx].fill = fill_3
sht[Lx].fill = fill_3
sht[Mx].fill = fill_3
sht[Nx].fill = fill_3
sht[Ox].fill = fill_3
sht[Px].fill = fill_3
sht[Qx].fill = fill_3
sht[Rx].fill = fill_3
# print('fill done!')
wb.save(path1)
os.startfile(path1)
def DoubleFileOfSgToTemplate(sgfile1,sgfile2):
arr1 = sgfile1.split(’’)
arr2 = sgfile2.split(’’)
if len(arr1) == 2:
sgfile1daily = sgfile1
sgfile1weekily = sgfile2
elif len(arr1) == 3:
sgfile1daily = sgfile1
sgfile1weekily = sgfile2
#daily 数据封装
num = readSFLxlsx1()
print('num:')
print(num)
if sgfile1daily.split('.')[1] == 'xls':
SgBrdnum = readBrdXlsForSingopreDaily(sgfile1daily)
eh = excelHandle()
filename = os.path.join(r'C:\Users\Public\SourceFile', sgfile1daily)
sheetname = 'working'
strDate = eh.read_excel(filename, sheetname)
else:
SgBrdnum = readBrdXlsxForSingopreDaily(sgfile1daily)
strDate = readSgBrdXlsxForDate(sgfile1daily)
bucode = 'SG'
# strDate = readSgBrdXlsxForDate(sgfile1daily)
# strDate = str(strDate).split(' ')[0]
# bUnum = []
# for x in range(len(num)):
# if num[x][0] == bucode:
# bUnum.append(num[x])
# 比对mapping table已筛选出SG的bUnum已筛选出Singpore source file 的记录
# MpTab_Sg = [] # 已筛选list
# for x in range(len(bUnum)):
# # strv = ''#获取v的数值大小
# for j in range(len(SgBrdnum)):
# if bUnum[x][1] == SgBrdnum[j][3]:
# MpTab_Sg.append(bUnum[x])
# strv = SgBrdnum[j][3]
getnull = []
for x in range(len(num)):
strv1 = '' # 获取v的数值大小
for j in range(len(SgBrdnum)):
if num[x][1] == SgBrdnum[j][2]:
strv1 = SgBrdnum[j][8]
if strv1 == '':
getnull.append(1)
n = 0;
temp = []
for x in range(len(num)):
strM = '' # 获取M列的数值大小
strN = '' # 获取N列的数值大小
for j in range(len(SgBrdnum)):
if num[x][1] == SgBrdnum[j][3]:
print('bUnum[x][1] == SgBrdnum[j][3]:')
print(num[x][1] == SgBrdnum[j][3])
print('bUnum[x][1]:')
print(num[x][1])
print('SgBrdnum[j][3]:')
print(SgBrdnum[j][3])
strM = SgBrdnum[j][12]
# print('strM:'+strM)
strN = SgBrdnum[j][13]
if strM == 0 and strN != 0:
print('strN:')
print(strN)
print('x:')
print(x)
var = [bucode, num[x][3], num[x][1], num[x][2], 'Daily', num[x][4], num[x][5], 'RED', strDate,
strN, '', '', '', '', '', '', '']
temp.append(var)
if strM != 0 and strN == 0:
print('strM:')
print(strM)
print('x:')
print(x)
var = [bucode, num[x][3], num[x][1], num[x][2], 'Daily', num[x][4], num[x][5],
'SUB', strDate, strM, '', '', '', '', '', '', '']
temp.append(var)
if strM == 0 and strN == 0: # The row is to colour fill by red as an alert to users.
print('x:')
print(x)
n = x
var = [bucode, num[x][3], num[x][1], num[x][2], 'Daily', num[x][4], num[x][5],
'', strDate, 'Both SUB & RED equal 0', '', '', '', '', '', '', '']
temp.append(var)
if strM != 0 and strN != 0: # The row is to colour fill by red as an alert to users.
print('x:')
print(x)
n = x
var = [bucode, num[x][3], num[x][1], num[x][2], 'Daily', num[x][4], num[x][5],
'', strDate, 'Both SUB & RED not equal 0', '', '', '', '', '', '', '']
temp.append(var)
print('temp1:')
print(len(temp))
beforlen = len(temp)
i = 1
# weekily 数据封装
num = readSFLxlsx1()
if sgfile1weekily.split('.')[1] == 'xls':
SgBrdnum = readBrdXlsForSingopreWeekly(sgfile1weekily)
eh = excelHandle()
filename = os.path.join(r'C:\Users\Public\SourceFile', sgfile1weekily)
sheetname = 'working'
strDate = eh.read_excel(filename, sheetname)
else:
SgBrdnum = readBrdXlsxForSingopreWeekly(sgfile1weekily)
strDate = readSgBrdXlsxForDate(sgfile1weekily)
bucode = 'SG'
frquency = 'Weekily'
bUnum = []
for x in range(len(num)):
if num[x][0] == bucode:
bUnum.append(num[x])
MpTab_Sg = [] # 已筛选list
for x in range(len(bUnum)):
# strv = ''#获取v的数值大小
for j in range(len(SgBrdnum)):
if bUnum[x][1] == SgBrdnum[j][2]:
MpTab_Sg.append(bUnum[x])
# strDate = readSgBrdXlsxForDate(sgfile1weekily)
# strDate = str(strDate).split(' ')[0]
for x in range(len(bUnum)):
strM = '' # 获取M列的数值大小
strN = '' # 获取N列的数值大小
for j in range(len(SgBrdnum)):
if bUnum[x][1] == SgBrdnum[j][2]:
strD = SgBrdnum[j][3] # Col D
strF = SgBrdnum[j][5] # Col F
#
# if strD == None:
# strD = 0
# if strF == None:
# strF = 0
if strD == '' and strF != '':
var = [bucode, bUnum[x][3], bUnum[x][1], bUnum[x][2], 'Weekly', bUnum[x][4], bUnum[x][5],
'RED', strDate, strF, '', '', '', '', '', '', '']
temp.append(var)
if strD != '' and strF == '':
var = [bucode, bUnum[x][3], bUnum[x][1], bUnum[x][2], 'Weekly', bUnum[x][4], bUnum[x][5],
'SUB', strDate, strD, '', '', '', '', '', '', '']
temp.append(var)
if strD == '' and strF == '': # The row is to colour fill by red as an alert to users.
var = [bucode, bUnum[x][3], bUnum[x][1], bUnum[x][2], 'Weekly', bUnum[x][4], bUnum[x][5],
'', strDate, 'Both SUB & RED equal 0', '', '', '', '', '', '', '']
temp.append(var)
if strD != '' and strF != '': # The row is to colour fill by red as an alert to users.
var = [bucode, bUnum[x][3], bUnum[x][1], bUnum[x][2], 'Weekly', bUnum[x][4], bUnum[x][5],
'', strDate, 'Both SUB & RED not equal 0', '', '', '', '', '', '', '']
temp.append(var)
for x in range(len(temp)):
# print(x)
temp[x].append('=SUM(J' + str(x + 2) + ':Q' + str(x + 2) + ')')
print('final temp:')
print(temp)
info = ['BU Code', 'Fund Name', 'AIA Fund Code', 'FA Fund Code', 'Frequency', 'BBG Acc Code', 'CCY',
'Tran type',
'Date', 'Daily/weekly flows', 'Reinvestment', 'Fee payment', 'Addition cash 1', 'Addition cash 2',
'Addition cash 3', 'Addition cash 4', 'Addition cash 5', 'Final Flow']
path = r'C:\Users\Public\AdjustmentFile'
dtf = time.strftime("%Y%m%d", time.localtime())
# ffn = 'CashFlow_SG_Adjustment_Daily&Weekly.xlsx'
ffn = 'CashFlow_SG_Adjustment_Daily&Weekly'+dtf+'.xlsx'
path2 = r'C:\Users\Public\SourceFile'
pageconfig = 'pageConfig.txt'
path2 = os.path.join(path2, pageconfig)
path1 = os.path.join(path, ffn)
with open(path2, "w", encoding='utf-8') as f:
f.write(ffn)
write_to_excel(path1, info, temp)
time.sleep(0.1)
#填写背景颜色
# ffn = 'crashflow_sg_template_Weekly.xlsx'
path = r'C:\Users\Public\AdjustmentFile'
path1 = os.path.join(path, ffn)
wb = openpyxl.load_workbook(path1, data_only=False)
fill_3 = PatternFill("solid", fgColor='9c0006')
names = wb.sheetnames
sheet = wb[names[0]]
sht = wb.worksheets[0]
str1 = []
for one_column_data in sheet.iter_rows():
# print(one_column_data[7].value)
if one_column_data[7].value == None:
str1.append(1)
else:
str1.append('no fill')
# print(str1)
for x in range(len(str1)):
if str1[x] == 1:
strx = str(x + 1)
Ax = "A" + strx
Bx = "B" + strx
Cx = "C" + strx
Dx = "D" + strx
Ex = "E" + strx
Fx = "F" + strx
Gx = "G" + strx
Hx = "H" + strx
Ix = "I" + strx
jx = "j" + strx
Kx = "K" + strx
Lx = "L" + strx
Mx = "M" + strx
Nx = "N" + strx
Ox = "O" + strx
Px = "P" + strx
Qx = "Q" + strx
Rx = "R" + strx
# print(Ax)
sht[Ax].fill = fill_3
sht[Bx].fill = fill_3
sht[Cx].fill = fill_3
sht[Dx].fill = fill_3
sht[Ex].fill = fill_3
sht[Fx].fill = fill_3
sht[Gx].fill = fill_3
sht[Hx].fill = fill_3
sht[Ix].fill = fill_3
sht[jx].fill = fill_3
sht[Kx].fill = fill_3
sht[Lx].fill = fill_3
sht[Mx].fill = fill_3
sht[Nx].fill = fill_3
sht[Ox].fill = fill_3
sht[Px].fill = fill_3
sht[Qx].fill = fill_3
sht[Rx].fill = fill_3
print('fill done!')
wb.save(path1)
os.startfile(path1)
#trans to csv
def xlsx_to_csv():
workbook = xlrd.open_workbook(r’C:\Users\Public\AdjustmentFile\Cash_Flow_Final_2021-11-19_.xlsx’)
table = workbook.sheet_by_index(0)
with codecs.open(r’C:\Users\Public\AdjustmentFile\Cash_Flow_Final_2021-11-19_.csv’, ‘w’, encoding=‘utf-8’) as f:
write = csv.writer(f)
for row_num in range(table.nrows):
row_value = table.row_values(row_num)
write.writerow(row_value)
#get value from xlsx
def readbuvacation(path,bu,specialdatefile):
# file_path = os.path.join(os.path.dirname(os.path.abspath(file)), “Mapping Table.xlsx”)
file_path = os.path.join(path,specialdatefile)
# wb = load_workbook(r’C:\wz\SourceFile\SG_11-08-2021_Weekly.xlsx’, data_only=True)
wb = load_workbook(file_path, data_only=True)
# print(wb)
sh = wb[bu]
all_datas = []
# 1、拿到字典的key值
# print(list(sh.rows)[0]) # 按行读取数据 (<Cell ‘login’.A1>, <Cell ‘login’.B1>, <Cell ‘login’.C1>)
# titles = [] # 定义一个空列表
# for item in list(sh.rows)[0]: # 遍历第1行当中每一列
# titles.append(item.value) # 列表添加值
# print(titles)
for item in list(sh.rows)[1:]: # 遍历数据行
values = []
for val in item: # 获取每一行的值
# values.append(val.value.strftime("%Y-%m-%d"))
if val.value == None:
values.append(’’)
if val != None:
if type(val.value) is datetime.datetime:
values.append(val.value.strftime("%Y-%m-%d"))
if type(val.value) is str:
values.append(val.value)
all_datas.append(values) # 追加到列表
holiydaydata = []
workdaydata = []
all_datas1 = []
for x in range(len(all_datas)):
holiydaydata.append(all_datas[x][0])
workdaydata.append(all_datas[x][1])
all_datas1.append(holiydaydata)
all_datas1.append(workdaydata)
return all_datas1
judge the day if it is the workday.
def judgeworkday(datevar,filepath,filename,bu):
print(‘datevar:’)
datevar1=datevar.strftime("%Y-%m-%d")
print(datevar1)
# print(type(datevar))
holiyday = readbuvacation(filepath, bu, filename)[0]
workday = readbuvacation(filepath, bu, filename)[1]
print('workday[]')
print(workday)
print('--------')
print('holiyday[]')
print(holiyday)
workdaytrue = []
for y in range(len(workday)):
if datevar1 == workday[y]:
workdaytrue.append(datevar1)
print('workdaytrue:')
print(workdaytrue)
if len(workdaytrue) > 0:
print(True)
return True
if len(workdaytrue) == 0:
holiydaytrue = []
for x in range(len(holiyday)):
if datevar1 == holiyday[x]:
holiydaytrue.append(datevar1)
break
print('holiydaytrue:')
print(holiydaytrue)
if len(holiydaytrue) == 0:
# date1 = datetime.datetime.strptime(datevar, '%Y-%m-%d')
dateint = datevar.weekday()
print(dateint)
if dateint > 4:
print(False)
return False
if dateint <= 4:
print(True)
return True
if len(holiydaytrue) > 0:
print(False)
return False
#get the lastworkday by config
def getthelastworkday(date,filepath,filename,bu):
# date = ‘2022-01-24’
# filepath = ‘C:\wz\Moses’
# filename = ‘vacationforbu.xlsx’
# bu = ‘SG’
# date= str(date)
date = datetime.datetime.strptime(date, “%Y-%m-%d”)
while True:
out_date = (date - datetime.timedelta(days=1)).strftime("%Y-%m-%d")
out_date = datetime.datetime.strptime(out_date, “%Y-%m-%d”)
date = out_date
date = date.strftime("%Y-%m-%d")
date = datetime.datetime.strptime(date, “%Y-%m-%d”)
print(‘judgeworkday:<’)
print(date)
print(judgeworkday(date,filepath,filename,bu))
print(’>’)
if judgeworkday(date,filepath,filename,bu) == True:
print(‘judgeworkday:’)
print(date)
break
stryymmdd = str(date).split(’-’)[0] + str(date).split(’-’)[1] + str(date).split(’-’)[2]
stryymmdd = stryymmdd.split(’ ')[0]
print()
print('lastworkday: ’ + stryymmdd)
return stryymmdd
#generate final output file
def finaoutputfilexport(Cash_Flow_Template):
print('Cash_Flow_Template : '+Cash_Flow_Template)
dataCfaft = readCashFlowlAdjuestmentFilexlsx(Cash_Flow_Template)
finalOutPutInfo = [‘EFFECTIVE_DATE’, ‘C_PCONTROL_CODE’, ‘CASH_AMOUNT’, ‘UNIT_AMOUNT’, ‘TRANSACTION_TYPE’, ‘PRICE_TYPE’, ‘PRICE_DATE’, ‘PRICE_RUN_TYPE’,
‘C_ORIGINATOR_CODE’,‘DATA_SOURCE’, ‘FULL_REDEMPTION’]
num = readSFLxlsx1()
print('dataCfaft : ')
print(dataCfaft)
for x in range(len(num)):
for i in range(len(dataCfaft)):
if num[x][1] == dataCfaft[i][2]:
dataCfaft[i].insert(6,num[x][5])
temp1 = []
for x in range(len(dataCfaft)):
# 0:EFFECTIVE_DATE 1:C_PCONTROL_CODE 2:CASH_AMOUNT 3:UNIT_AMOUNT 4:TRANSACTION_TYPE 5:PRICE_TYPE 6:PRICE_DATE 7:PRICE_RUN_TYPE 8:C_ORIGINATOR_CODE
#9:DATA_SOURCE 10:FULL_REDEMPTION
strTransAction_Type = ''
if dataCfaft[x][8] == 'SUB':
strTransAction_Type = 'SUBSCRIPTION'
if dataCfaft[x][8] == 'RED':
strTransAction_Type = 'REDEMPTION'
# print(dataCfaft[x][8] - datetime.timedelta(days=1)).strftime("%Y-%m-%d")
in_date = dataCfaft[x][9]
# in_date = str(in_date)
print('in_date')
print(in_date)
sg_xlsx_in_date =in_date
# in_date.split(' ')
if len(str(sg_xlsx_in_date).split(' ')) == 2:
dt = datetime.datetime.strptime(str(sg_xlsx_in_date).split(' ')[0], "%Y-%m-%d")
dt = dt.strftime("%Y-%m-%d")
print('dt xlsx')
print(dt)
# out_date = (dt - datetime.timedelta(days=1)).strftime("%Y%m%d")
# out_date = calcthelastworkday(dataCfaft[x][0],dt)
# out_date = getthelastworkday(dt, r'C:\Users\Public\pythonAutomationConfig','vacationforbu.xlsx',
# dataCfaft[x][0])
out_date = getthelastworkday(dt, r'C:\Users\Public\pythonAutomationConfig', 'vacationforbu.xlsx',
dataCfaft[x][0])
else:
dt = datetime.datetime.strptime(in_date, "%Y-%m-%d")
dt = dt.strftime("%Y-%m-%d")
print('dt No xlsx')
print(dt)
print(dataCfaft[x][0])
# dd = datetime.datetime.strptime(dd, "%Y%m%d")
# out_date = (dt - datetime.timedelta(days=1)).strftime("%Y%m%d")
# out_date = calcthelastworkday(dataCfaft[x][0], dt)
out_date = getthelastworkday(dt,r'C:\Users\Public\pythonAutomationConfig','vacationforbu.xlsx',
dataCfaft[x][0])
# print("out_date:"+out_date)
strDATA_SOURCE = "AIA_"+dataCfaft[x][0]
strC_ORIGINATOR_CODE = "AIA_PAS_"+dataCfaft[x][6]
# print('dataCfaft[x][17] :')
# print(dataCfaft[x][17])
in_date = str(in_date)
arrin_date = in_date.split('-')
efeactivedate = arrin_date[0]+arrin_date[1]+arrin_date[2]
# var = [dataCfaft[x][9], dataCfaft[x][3],abs(dataCfaft[x][18]), 0, strTransAction_Type, 'Swing Price', out_date,
# 'Daily Pricing', strC_ORIGINATOR_CODE, strDATA_SOURCE, 'N']
var = [efeactivedate, dataCfaft[x][3], abs(dataCfaft[x][18]), 0, strTransAction_Type, 'Swing Price', out_date,
'Daily Pricing', strC_ORIGINATOR_CODE, strDATA_SOURCE, 'N']
temp1.append(var)
fOutfp = r'C:\Users\Public\AdjustmentFile'
strbu = Cash_Flow_Template.split('_')[1]
dtf = time.strftime("%Y%m%d%H%M%S", time.localtime())
Cash_Flow_FinalExport ='U110_Cashflow_AIA_'+strbu+'_'+dtf+'.xlsx'
print('Cash_Flow_FinalExport:')
print(Cash_Flow_FinalExport)
path1 = os.path.join(fOutfp,Cash_Flow_FinalExport)
with open(r"C:\Users\Public\SourceFile\pageConfig1.txt", "r", encoding='utf-8') as f:
lastfinaloutfilename = f.readline()
dellastfinaloutfilenamepath = os.path.join(fOutfp,lastfinaloutfilename)
#remove 因为多次点击reviewbutton生成的finaloutfile
if lastfinaloutfilename != '':
if os.path.exists(dellastfinaloutfilenamepath) == True:
os.remove(dellastfinaloutfilenamepath)
with open(r"C:\Users\Public\SourceFile\pageConfig1.txt", "w", encoding='utf-8') as f:
f.write(Cash_Flow_FinalExport)
write_to_cfexcel(path1,finalOutPutInfo,temp1)
os.startfile(path1)
#Cash Flow Adjustment File is Empty 生成final output file
def finaoutputfilexportempty(downloadfile,date,BU):
print('downloadfile : ’ + downloadfile)
finalOutPutInfo = ['EFFECTIVE_DATE', 'C_PCONTROL_CODE', 'CASH_AMOUNT', 'UNIT_AMOUNT', 'TRANSACTION_TYPE',
'PRICE_TYPE', 'PRICE_DATE', 'PRICE_RUN_TYPE',
'C_ORIGINATOR_CODE', 'DATA_SOURCE', 'FULL_REDEMPTION']
temp1 = []
for x in range(1):
# 0:EFFECTIVE_DATE 1:C_PCONTROL_CODE 2:CASH_AMOUNT 3:UNIT_AMOUNT 4:TRANSACTION_TYPE 5:PRICE_TYPE 6:PRICE_DATE 7:PRICE_RUN_TYPE 8:C_ORIGINATOR_CODE
# 9:DATA_SOURCE 10:FULL_REDEMPTION
# strTransAction_Type = ''
# print(dataCfaft[x][8] - datetime.timedelta(days=1)).strftime("%Y-%m-%d")
# in_date = date
# dt = datetime.datetime.strptime(in_date, "%Y-%m-%d")
# out_date = date
# print("out_date:"+out_date)
strDATA_SOURCE = "AIA_" + BU
strC_ORIGINATOR_CODE = "AIA_PAS"
# print('dataCfaft[x][17] :')
# print(dataCfaft[x][17])
# var = [dataCfaft[x][8],dataCfaft[x][3],abs(dataCfaft[x][17]),0,strTransAction_Type,'Swing Price',out_date,'Daily Pricing',strC_ORIGINATOR_CODE,strDATA_SOURCE,'N']
var = [date, 'NO_RECORDS', 0, 0, '', '', date,
'Daily Pricing', 'AIA_PAS', strDATA_SOURCE, 'N']
# var = [dataCfaft[x][8], "NO_RECORDS", 0, 0, '', '',dataCfaft[x][8], 'Daily Pricing', "AIA_PAS", strDATA_SOURCE, 'N']#for Cash Flow Adjustment File is Empty
temp1.append(var)
# path = r'C:\Users\Public\SourceFile\Cash Flow1.xlsx'
fOutfp = r'C:\Users\Public\AdjustmentFile'
# print(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()))
dtf = time.strftime("%Y%m%d%H%M%S", time.localtime())
Cash_Flow_FinalExport = 'U110_Cashflow_AIA_' + BU + '_' + dtf + '.xlsx'
print('Cash_Flow_FinalExport:')
print(Cash_Flow_FinalExport)
path1 = os.path.join(fOutfp, Cash_Flow_FinalExport)
with open(r"C:\Users\Public\SourceFile\pageConfig1.txt", "w", encoding='utf-8') as f:
f.write(Cash_Flow_FinalExport)
write_to_cfexcel(path1, finalOutPutInfo, temp1)
os.startfile(path1)
#GenerateLog
def writelog(logfilepath,logcontent):
with open(logfilepath, “a+”, encoding=‘utf-8’) as f:
f.write(logcontent)
if name == ‘main’:
# print(readSgBrdXlsForDate(‘SG_2021-12-29_Weekly.xls’))
# print(’--------------’)
# print(readSgBrdXlsxForDate(‘SG_2021-12-22_Weekly.xlsx’))
# print(’------------------’)
# print(readBrdXlsForSingopreDaily(‘SG_2021-12-29.xls’))
# print(krreadBrdXlsxStrDate(‘KR_2021-12-25.xlsx’))
# print(readBrdXlsx(‘PH_2021-12-22.xlsx’))
# print(calcthelastworkday(‘KR’, ‘2022-02-28’))
# print(calcthelastworkday(‘KR’, ‘2022-02-27’))
# print(calcthelastworkday(‘PH’, ‘2022-01-01’))
# print(calcthelastworkday(‘SG’, ‘2020-01-02’))()
# date = ‘2022-03-07’
# # # print(‘orgin date’)
# filepath = r’C:\Users\Public\pythonAutomationConfig’
# filename = ‘vacationforbu.xlsx’
# bu = ‘KR’
# print(getthelastworkday(date,filepath,filename,bu))
# write_to_cfexcel(path1, finalOutPutInfo, temp1)
path = r'C:\Users\Public\pythonAutomationConfig'
xlsxvacationfobu = 'vacationforbu.xlsx'
path_xlsx = os.path.join(path,xlsxvacationfobu)
titleInfo = ['holiday','specialworkday']
# # finalOutPutInfo = ['EFFECTIVE_DATE', 'C_PCONTROL_CODE', 'CASH_AMOUNT', 'UNIT_AMOUNT', 'TRANSACTION_TYPE',
# # 'PRICE_TYPE', 'PRICE_DATE', 'PRICE_RUN_TYPE',
# # 'C_ORIGINATOR_CODE', 'DATA_SOURCE', 'FULL_REDEMPTION']
# # temp = ''
write_to_vacationbuexcel(path_xlsx,titleInfo)
xlrddate.py
import xlrd
import sys
reload(sys)
sys.setdefaultencoding(‘utf-8’)
import traceback
from datetime import datetime
from xlrd import xldate_as_tuple
class excelHandle:
def decode(self, filename, sheetname):
try:
filename = filename.encode(‘utf-8’).decode(‘utf-8’)
sheetname = sheetname.encode(‘utf-8’).decode(‘utf-8’)
except Exception:
print( traceback.print_exc())
return filename, sheetname
def read_excel(self, filename, sheetname):
filename, sheetname = self.decode(filename, sheetname)
# filename = decode(filename)
# sheetname = decode(sheetname)
rbook = xlrd.open_workbook(filename)
sheet = rbook.sheet_by_name(sheetname)
rows = sheet.nrows
cols = sheet.ncols
all_content = []
cell = sheet.cell_value(1, 2)
# print(cell)
date = datetime(*xldate_as_tuple(cell, 0))
cell = date.strftime('%Y-%m-%d')
# for i in range(rows):
# row_content = []
# for j in range(cols):
# ctype = sheet.cell(i, j).ctype # 表格的数据类型
# cell = sheet.cell_value(i, j)
# if ctype == 2 and cell % 1 == 0: # 如果是整形
# cell = int(cell)
# elif ctype == 3:
# # 转成datetime对象
# date = datetime(*xldate_as_tuple(cell, 0))
# cell = date.strftime('%Y/%d/%m %H:%M:%S')
# elif ctype == 4:
# cell = True if cell == 1 else False
# row_content.append(cell)
# all_content.append(row_content)
# print( '[' + ','.join("'" + str(element) + "'" for element in row_content) + ']')
# return all_content
return cell
if name == ‘main’:
eh = excelHandle()
filename = r’C:\Users\Public\SourceFile\SG_2021-12-29_Weekly.xls’
sheetname = ‘working’
print(eh.read_excel(filename, sheetname))
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
