def wrapped_f(*args):
if name_function == "vlookup":
ws_des,loopkup_value_range,table_array,\
rang_lookup, plexcel,colum_to_get_value,\
Locc_result_value,lockrange = args
if lockrange:
table_array = add_sb_to_str(table_array)
cellstart = str_seplistint_strbystr(loopkup_value_range)[0]
re_value = returnliststr_from_str(table_array)[0]
cell_start_locvalue = str_seplistint_strbystr(Locc_result_value)[0]
# index column number
col_index_num = col2num(colum_to_get_value) - col2num(re_value) + 1
fomularex = "=IFERROR(VLOOKUP({2},{1}!{0},{3},{4}),{5})".format(table_array,
plexcel,
cellstart,
col_index_num,
rang_lookup,
'"' + "" + '"'
)
if name_function == "sumif":
f(ws_des,cell_start_locvalue,fomularex,Locc_result_value)
return wrapped_f
def tranderdatasheettosheet(self):
"""
transfer data formulas to another sheet
"""
for abccol in self.__lcolumnformulas:
if len(self.__valueim) != 0:
lvaluebyindecell_im = valuebyindexrowcell(
lindexcell=self.lindexrow_im,
col=abccol,
sheet=self.__sheet_des)
indexcol = col2num(abccol) - col2num(self.__col_key_msa) + 1
fomularex = "=IFERROR(VLOOKUP({1}{0},{2}!${1}${0}:${4}${6},{7},FALSE),{8})".format(
self.__startrow, self.__col_key_msa, self.__pfile,
self.__col_key_msa, self.__columnlra[1], self.__startrow,
self.__max_row_allsheet, indexcol, '"' + "" + '"')
self.__sheet_des.range("{0}{1}".format(
abccol, self.__startrow)).value = fomularex
vtformulas = self.__sheet_des.range("{0}{1}".format(
abccol, self.__startrow)).formula
self.__sheet_des.range("{0}{1}:{0}{2}".format(
abccol, self.__startrow, self.__max_row)).formula = vtformulas
if len(self.__valueim) != 0:
self.returnvaluekeyim(cola=abccol,
listvalue_im=lvaluebyindecell_im)
def __init__(self,
ws_des = None,
loopkup_value_range = None,
table_array = None,
rang_lookup = False,
plexcel = None,
colum_to_get_value = None,
Sub_VLOOKUP_Locc_result_value = None,
lockrange = True
):
self.__ws_des = ws_des
self.__table_array = table_array
if lockrange:
self.__table_array = add_sb_to_str(self.__table_array)
self.__plexcel = plexcel
self.__rang_lookup = rang_lookup
self.__loopkup_value_range = loopkup_value_range
self.__Sub_VLOOKUP_Locc_result_value = Sub_VLOOKUP_Locc_result_value
self.__cellstart = str_seplistint_strbystr(loopkup_value_range)[0]
re_value = returnliststr_from_str(table_array)[0]
self.__cell_start_locvalue = str_seplistint_strbystr(Sub_VLOOKUP_Locc_result_value)[0]
# index column number
self.__col_index_num = col2num(colum_to_get_value) - col2num(re_value) + 1
def __init__(self,
dictconf=None,
pathdes=None,
pathtocopy=None,
namesheet="AZB-30"):
self.__namesheet = namesheet
self.__pathdes = pathdes
self.__pathtocopy = pathtocopy
self.app = xw.App(visible=False)
self.desxw = xw.Book(pathdes)
self.copyxw = xw.Book(pathtocopy)
self.ws1 = self.copyxw.sheets[self.__namesheet]
#max row ws1
self.rows = self.ws1.api.UsedRange.Rows.count
#max colum ws1
self.cols = self.ws1.api.UsedRange.Columns.count
zab30_recor_l = dictconf["zab30_recor_l1"]
self.__zab30_valuelastrow = dictconf["zab30_valuelastrow"]
try:
self.__zab30_valuelastrow = float(self.__zab30_valuelastrow)
except:
pass
self.__azb30_ms = col2num(dictconf["azb30_ms"])
self.__zab30_recor_l_lint = returnseplistintbbystr(zab30_recor_l)
self.__listmaxrc = self.__zab30_recor_l_lint + [self.rows]
colunsande = [colnum_string(1), colnum_string(self.cols)]
self.__listrange = pairlistandlist(listm=self.__listmaxrc,
list_str=colunsande)
def azb40(self):
startrowhm = int(self.dicrowconf["zab40_recor_l1"])
maythicong_col = col2num(self.dicrowconf["azb40_hm"])
dongia = self.dicrowconf["azb40_dongia"]
dongia_int = col2num(dongia)
colmay_nc = self.dicrowconf["azb40_colmay_nc"]
colmay_nc_row = int(self.dicrowconf["azb40_colmay_nc_row"])
crow = startrowhm + 1
for sheetn in self.lsheet:
self.wsheet.range(crow, maythicong_col).value = sheetn
pfile = repathlinkexcel(dpath=self.dpath,
namefile=self.namefile,
namesheet=sheetn)
self.wsheet.range(crow, dongia_int).value = "={0}!${1}${2}".format(
pfile, colmay_nc, colmay_nc_row)
crow = crow + 1
def azb50(self):
#='C:\Users\HP\Desktop\test\[KE HOACH NGAN SACH.xlsx]B3-Factory'!$BX$7
startrowhm = int(self.dicrowconf["zab50_recor_l1"])
maythicong_col = col2num(self.dicrowconf["azb50_hm"])
dongia = self.dicrowconf["azb50_dongia"]
dongia_int = col2num(dongia)
colmay_nc = self.dicrowconf["azb50_colmay_nc"]
colmay_nc_row = int(self.dicrowconf["azb50_colmay_nc_row"])
crow = startrowhm + 1
for sheetn in self.lsheet:
self.wsheet.range(crow, maythicong_col).value = sheetn
pfile = repathlinkexcel(dpath=self.dpath,
namefile=self.namefile,
namesheet=sheetn)
self.wsheet.range(crow, dongia_int).value = "={0}!${1}${2}".format(
pfile, colmay_nc, colmay_nc_row)
crow = crow + 1
def returnloccellbyvalue(path=None,
min_row=1,
max_row=100,
min_col=3,
max_col=3,
valuetofind=1200,
namesheet=None):
""" return location of cell (row index and column index) ---->[cell.row,cell.column]"""
if type(min_col) == str:
min_col = col2num(min_col)
if type(max_col) == str:
max_col = col2num(max_col)
wb = openpyxl.load_workbook(path, read_only=True)
ws = wb[namesheet]
for row in ws.iter_rows(min_row=min_row,
max_row=max_row,
min_col=min_col,
max_col=max_col):
for cell in row:
if str(cell.value) == str(valuetofind):
return [cell.row, cell.column
] #change column number for any cell value you wants
def habz60(self):
"""handling data azb-60 sheet"""
col_msa = (self.dicrowconf["azb60_ms"])
startrowhm = int(self.dicrowconf["zab60_recor_l1"])
dongia = col2num(self.dicrowconf["azb60_dongia"])
dongia_abc = self.dicrowconf["azb60_dongia"]
rangeketcauthep = (self.dicrowconf["azb60_rangeketcauthep"])
valueim = returnlist_from_listinstr(
self.dicrowconf["zab60_valueim"].replace(":", ","))
m_row = self.wsheet.range(
col_msa + str(self.wsheet.cells.last_cell.row)).end('up').row
# return index row by value
if len(valueim) != 0:
lindexrow_im = lcellindexbyvalue(max_row=self.mrow,
min_row=startrowhm,
max_col=col_msa,
min_col=col_msa,
sheet=self.wsheet,
lvalue=valueim)
lvaluebyindecell_im = valuebyindexrowcell(lindexcell=lindexrow_im,
col=dongia_abc,
sheet=self.wsheet)
# return range number
rangese = returnseplistintbbystr(strint=rangeketcauthep)
sumvalue = "=" + self.__returnsumvalue(iden="other",
startrow60=startrowhm)
rangecopyrefsamesheet(sheet=self.wsheet,
formulasfirstcell=sumvalue,
col_index=dongia,
startrow=startrowhm,
endrow=m_row)
# rewwrite ket cau thep
sumvalue = "=" + self.__returnsumvalue(iden="kct",
startrow60=rangese[0])
rangecopyrefsamesheet(sheet=self.wsheet,
formulasfirstcell=sumvalue,
col_index=dongia,
startrow=rangese[0],
endrow=rangese[1])
if len(valueim) != 0:
returnvaluekeyim(cola=dongia_abc,
listvalue_im=lvaluebyindecell_im,
sheet=self.wsheet,
indexrow_im=lindexrow_im)
def itemiden(self):
"""item identification"""
self.__azb30_starcolumn = col2num(self.dicrowconf["azb30_starcolumn"])
self.__azb30_rowhm = int(self.dicrowconf["azb30_rowhm"])
#lsheetnamehm = []
lsheetnames = [sheet.name for sheet in self.__wb1.sheets]
lsheetnamehm = [self.wsheet.range(self.__azb30_rowhm , k).value \
for k in range (self.__azb30_starcolumn ,self.mcol) \
if self.wsheet.range(self.__azb30_rowhm , k).value in lsheetnames]
if len(lsheetnamehm) == 0:
messagebox.showerror(
"Error", "Review {0}, has it processed?".format("AZB-30"))
# convet to csv file
listtocsvbyarow(listvalue=lsheetnamehm, pathcsv=self.pathlsn)
def __init__(self,
dictconf=None,
wsheetdes=None,
mrowwscopy=None,
mcolumnwscopy=None,
wsheetcopy=None):
self.__wsheetcopy = wsheetcopy
self.__mrowwscopy = mrowwscopy
self.__mcolumnwscopy = mcolumnwscopy
self.__wsheetdes = wsheetdes
self.__azb10startr = int(dictconf["azb10startr"])
#line azb10s folow row at mother
self.__zab10_recor_l1m = int(dictconf["zab10_recor_l1m"])
self.__zab10_recor_l2m = int(dictconf["zab10_recor_l2m"])
self.__zab10_recor_l3m = int(dictconf["zab10_recor_l3m"])
# azb10 for both
self.__zab10_totalpaodstr = (dictconf["zab10_totalpaod"])
self.__zab10_totalpaod = col2num(dictconf["zab10_totalpaod"])
self.__zab10_frmpstr = (dictconf["zab10_frmp"])
self.__zab10_frmp = col2num(dictconf["zab10_frmp"])
self.__zab10_refundstr = (dictconf["zab10_refund"])
self.__zab10_refund = col2num(dictconf["zab10_refund"])
self.__zab10_efastr = (dictconf["zab10_efa"])
self.__zab10_efa = col2num(dictconf["zab10_efa"])
self.__zab10_finalabstr = (dictconf["zab10_finalab"])
self.__zab10_finalab = col2num(dictconf["zab10_finalab"])
self.__zab10_nbstr = (dictconf["zab10_nb"])
self.__azb10_ms = col2num(dictconf["azb10_ms"])
self.__zab10_nb = col2num(dictconf["zab10_nb"])
self.__listsubtal = [
self.__zab10_totalpaod, self.__zab10_frmp, self.__zab10_refund,
self.__zab10_efa, self.__zab10_finalab, self.__zab10_nb
]
self.__listsubtalstr = [
self.__zab10_totalpaodstr, self.__zab10_frmpstr,
self.__zab10_refundstr, self.__zab10_efastr,
self.__zab10_finalabstr, self.__zab10_nbstr
]
self.__indexvaluerc = [
self.__zab10_recor_l1m, self.__zab10_recor_l2m,
self.__zab10_recor_l3m
]
self.__azb10netbi = col2num(dictconf["azb10netbi"])
self.__listmaxrc = self.__indexvaluerc + [self.__mrowwscopy]
def habz30(self):
"""handling data azb-30 sheet"""
self.__hm_maxrow = int(self.dicrowconf["hm_maxrow"])
self.__azb30_starcolumn = col2num(self.dicrowconf["azb30_starcolumn"])
self.__azb30_rowhm = int(self.dicrowconf["azb30_rowhm"])
self.__azb30_maxrowhm = int(self.dicrowconf["azb30_maxrowhm"])
self.__azb30_startrowhm = int(self.dicrowconf["zab30_recor_l1"])
self.__azb30_msa = (self.dicrowconf["azb30_ms"])
lsheetnamehm = []
lsheetnames = [sheet.name for sheet in self.__wb1.sheets]
for k in range(self.__azb30_starcolumn, self.mcol):
hmname = self.wsheet.range(self.__azb30_rowhm, k).value
if hmname in lsheetnames:
self.fomuluasfcol(k, hmname=hmname)
lsheetnamehm.append(hmname)
# convet to csv file
listtocsvbyarow(listvalue=lsheetnamehm, pathcsv=self.pathlsn)
def __init__(self, pathconf=None):
self.__pathconf = pathconf
dirparhconf = parentdirectory(self.__pathconf)
dicrowconf = returndictrowforcsv(path=pathconf)
self.__sheetnametor = dicrowconf["khns_sheetnamekhns"]
self.__khns_namfile = dicrowconf["khns_namfile"]
self.__khns_rangenumbermct_ptvt = dicrowconf[
"khns_rangenumbermct_ptvt"]
self.__valuenotnone = dicrowconf["valuenotnone"]
self.__valueall = dicrowconf["valueall"]
self.__dictvalue = dicrowconf["dictvalue"]
self.__mvta = (dicrowconf["khns_mavatu"])
self.__mvt = col2num(self.__mvta)
#self.__fpath = returnactivewbpath(namefile=self.__khns_namfile)
self.__fpath = activeworkbook_fullname()
self.__rel = credict(pathfull=self.__fpath,
namesheet=self.__sheetnametor,
engine="xlwings",
rangea=self.__khns_rangenumbermct_ptvt)
# csv for dict
self.pathtovalue = refullpath(dirparhconf, self.__dictvalue)
# csv for value
self.valuenotnone = refullpath(dirparhconf, self.__valuenotnone)
# csv for all value
self.pathvalueall = refullpath(dirparhconf, self.__valueall)
# get all key redic incule len != 0
self.redic_all = self.__rel.redictvaluesandvaluecol(
columnumber=self.__mvt, removeemtyvalue=False)
#self.redic =self.__rel.redictvaluesandvaluecol(columnumber=self.__mvt)
self.redic = {
key: val
for key, val in self.redic_all.items() if len(val) != 0
}
self.valueredicttocsv()
self.valuelisttocsv()
def __init__ (self, fpath = None,
pathconf = None,
pathconfigexcelcopy = None
):
self.pathconf = pathconf
self.pathconfigexcelcopy = pathconfigexcelcopy
self.dicrowconf = returndictrowforcsv(path=pathconf)
self.__fpath = fpath
dirparhconf = parentdirectory(pathconf)
self.__sheetnametor=self.dicrowconf["khns_sheetnamekhns"]
self.__namefile=self.dicrowconf["khns_namfile"]
self.__rangeg =self.dicrowconf["hm_rangege"]
self.__rangeintct = returnseplistintbbystr(self.__rangeg)
self.__rangestrct = returnliststr_from_str(self.__rangeg)
self.__mvta = (self.dicrowconf["khns_mavatu"])
self.__mvt = col2num (self.__mvta)
self.__khns_ndcva = (self.dicrowconf["khns_noidungcongviec"])
self.__khns_ndcv = col2num (self.__khns_ndcva)
self.__khns_dvta = (self.dicrowconf["khns_dvt"])
self.__khns_dvt = col2num (self.__khns_dvta)
self.__khns_muchaophi = (self.dicrowconf["khns_muchaophi"])
self.__khns_muchaophi_int = col2num(self.__khns_muchaophi)
self.__hm_mvt = int(self.dicrowconf["hm_mvt"])
self.__hm_ndcv = int(self.dicrowconf["hm_noidungcongviec"])
self.__hm_dvt = int(self.dicrowconf["hm_dvt"])
self.__hm_dgth_str = (self.dicrowconf["hm_dgth"])
self.__hm_dgth = col2num(self.__hm_dgth_str)
self.__hm_ttnt_str = (self.dicrowconf["hm_ttnt"])
self.__hm_ttnt = col2num(self.__hm_ttnt_str)
self.__hm_startrowvalue = int(self.dicrowconf["hm_startrowvalue"])
self.__hm_vta = self.dicrowconf["hm_vt"]
self.__hm_nca = self.dicrowconf["hm_nc"]
self.__hm_mtca = self.dicrowconf["hm_mtc"]
self.__hm_tha = self.dicrowconf["hm_th"]
self.__hm_ct = self.dicrowconf["hm_ct"]
self.__hm_ct_int = col2num(self.__hm_ct)
self.__hm_vt =col2num (self.dicrowconf["hm_vt"])
self.__hm_nc =col2num (self.dicrowconf["hm_nc"])
self.__hm_mtc =col2num (self.dicrowconf["hm_mtc"])
self.__hm_th =col2num (self.dicrowconf["hm_th"])
self.__valuenotnone =self.dicrowconf["valuenotnone"]
valueall =self.dicrowconf["valueall"]
self.__dictvalue =self.dicrowconf["dictvalue"]
# csv for dict
self.pathtovalue = refullpath(dirparhconf,self.__dictvalue)
# csv for value
self.valuenotnone = refullpath(dirparhconf,self.__valuenotnone)
# path all value
self.pathvalueall = refullpath(dirparhconf,valueall)
self.__khns_rangenumbermct_ptvt =self.dicrowconf["khns_rangenumbermct_ptvt"]
# hangmuccongtac
self.__hm_startpasterange = self.dicrowconf["hm_startpasterange"]
self.__startpasterange = returnseplistintbbystr(self.__hm_startpasterange)
self.__hm_congtac = self.dicrowconf["hm_congtac"]
# add new##########################################################################33
self.__khns_macongtac = self.dicrowconf["khns_macongtac"]
self.__khns_noidungcongviec = self.dicrowconf["khns_noidungcongviec"]
self.__khns_vattu = self.dicrowconf["khns_vattu"]
self.__khns_nhancong = self.dicrowconf["khns_nhancong"]
self.__khns_maytc = self.dicrowconf["khns_maytc"]
self.__hm_th_formulas = self.dicrowconf["hm_th_formulas"]
self.__khns_startrow = int(self.dicrowconf["khns_startrow"])
# return list ma cong tac not node in cell value of ptvl by csv
self.getvaluelist = convertcsvtolist(path=self.valuenotnone)
# return all value from csv
self.getallvalue = convertcsvtolist(path=self.pathvalueall)
self.pathlsn = refullpath(dirpath=getdirpath(pathconf),
filename=self.dicrowconf["listsheetnamehm"])
try:
self.lsheetname = convertcsvto1list(path=self.pathlsn)
except:
pass
self.__returnothervalue()
self.__returnlistcongtac()