def __init__(self,
wsheet=None,
dpath=None,
namefile=None,
dicrowconf=None,
wbnsct=None,
pathconf=None):
self.dicrowconf = dicrowconf
self.dpath = dpath
self.wsheet = wsheet
self.namefile = namefile
#max colum ws1
self.mcol = self.wsheet.api.UsedRange.Columns.count
#max row ws1
self.mrow = self.wsheet.api.UsedRange.Rows.count
self.dirpathconf = getdirpath(pathconf)
self.__hm_rangege = (self.dicrowconf["hm_rangege"])
self.numberhm = int(sepnumberandstrfromstr(self.__hm_rangege)[1])
self.__wb1 = wbnsct
listsheetnamehm = (self.dicrowconf["listsheetnamehm"])
# return csv have list sheet name
self.pathlsn = refullpath(dirpath=self.dirpathconf,
filename=listsheetnamehm)
try:
self.lsheet = convertcsvto1list(path=self.pathlsn)
except:
pass
def getdirpathfromorigin(output1):
# Get path full
global pathinout
pathinout = getpathfromtk(output1)
filename = ExtractFileNameFromPath(pathinout)
filename1 = getfilenamewoexten(filename)
# get dirpath from full path
dn = getdirpath(pathinout)
ps = PathSteel(dir_path=dn, FileName=filename1 + ".csv")
pathf = ps.refpath()
return pathf
def repathlinkexcel(dpath=None,
namefile=None,
namesheet=None,
usingfullname=False,
fullname=None):
""" return path link excel """
if usingfullname:
dpath = getdirpath(fullname)
namefile = ExtractFileNameFromPath(path=fullname)
pfile = "'" + dpath + "/" + "[" + namefile + "]" + namesheet + "'"
else:
pfile = "'" + dpath + "/" + "[" + namefile + "]" + namesheet + "'"
return pfile
def __init__(self,
startrow=1,
col_key_msa=None,
lcolumnformulas=None,
valueim=None,
sheet_des=None,
sheet_copy=None,
col_dup=None,
lvaluehavechild=[],
formulasfor_col_dup=None):
self.__startrow = startrow
self.__col_key_msa = col_key_msa
# retrive full name of sheet_coy
psheet_copy = sheet_copy.book.fullname
# retrive dir path of sheet _copy
dirp_sheet_copy = getdirpath(psheet_copy)
# retrive file name of des copy
filename_sheet_copy = ExtractFileNameFromPath(path=psheet_copy)
# # retrive path link using for formulas
self.__pfile = repathlinkexcel(dpath=dirp_sheet_copy,
namefile=filename_sheet_copy,
namesheet=sheet_copy.name)
self.__columnlra = [
colnum_string(1),
colnum_string(sheet_copy.api.UsedRange.Columns.count)
]
self.__max_row = sheet_des.range(
col_key_msa + str(sheet_des.cells.last_cell.row)).end('up').row
self.__lcolumnformulas = lcolumnformulas
self.__valueim = valueim
self.__sheet_des = sheet_des
self.__sheet_copy = sheet_copy
self.__lvaluehavechild = lvaluehavechild
self.__max_row_allsheet = sheet_copy.api.UsedRange.Rows.count
self.__formulasfor_col_dup = formulasfor_col_dup
self.__col_dup = col_dup
if len(self.__valueim) != 0:
self.lindexrow_im = lcellindexbyvalue(
max_row=self.__max_row_allsheet,
min_row=self.__startrow,
max_col=self.__col_key_msa,
min_col=self.__col_key_msa,
sheet=self.__sheet_des,
lvalue=self.__valueim)
self.tranderdatasheettosheet()
if len(self.__col_dup) != 0:
self.hdataatdupcolumn()
def __init__(self, pathconf=None):
self.__pathconf = pathconf
dictconf = returndictrowforcsv(path=pathconf)
self.__hm_congtac = dictconf["hm_congtac"]
self.__hm_bombt = dictconf["hm_bombt"]
self.__hm_macmtc = dictconf["hm_macmtc"]
self.__hm_materiasvattu = dictconf["hm_materiasvattu"]
self.__hm_vlvk = dictconf["hm_vlvk"]
self.__hm_VLTP = dictconf["hm_vltp"]
self.__hm_MNCTP = dictconf["hm_mnctp"]
self.__hm_VTKCT = dictconf["hm_vtkct"]
self.__hm_NCKCT = dictconf["hm_nckct"]
self.__hm_startpasterange = dictconf["hm_startpasterange"]
sign_vk = dictconf["sign_vk"].replace(":", ",")
self.sign_vk = returnlist_from_listinstr(sign_vk)
sign_BT = dictconf["sign_bt"].replace(":", ",")
self.sign_BT = returnlist_from_listinstr(sign_BT)
self.__startpasterange = returnseplistintbbystr(
self.__hm_startpasterange)
self.pathlsn = refullpath(dirpath=getdirpath(pathconf),
filename=dictconf["listsheetnamehm"])
fct = dictconf["fct"]
try:
self.lsheetname = convertcsvto1list(path=self.pathlsn)
except:
pass
fname = dictconf["khns_namfile"]
if fct.strip() == "all":
self.wb = activeworkbook(namefile=fname, checknamefile=True)
for sheet in self.lsheetname:
print(sheet)
self.wb.sheets[sheet].activate()
self.__sheetdesactive = activesheet()
self.m_row = self.__sheetdesactive.range(
self.__hm_congtac +
str(self.__sheetdesactive.cells.last_cell.row)).end(
'up').row
self.__hdata()
#self.copyrangfromconf()
else:
self.__sheetdesactive = activesheet()
#self.copyrangfromconf()
self.m_row = self.__sheetdesactive.range(
self.__hm_congtac +
str(self.__sheetdesactive.cells.last_cell.row)).end('up').row
self.__hdata()
def __Getlistsheet(self):
self.__app = xw.App(visible=True, add_book=False)
self.__wb1 = self.__app.books.open(self.pathtocopy, update_links=False)
#self.__wb1 = xw.Book(self.pathtocopy)
#self.__wb1 = book_by_path(path=self.pathtocopy, visible = False)
self.names = self.__wb1.sheets
self.lsheetname = [sheet.name for sheet in self.names]
self.__ws1 = self.__wb1.sheets[self.names[0]]
self.__wsname = self.__ws1.name
# set active sheet
#max row ws1
self.rows = self.__ws1.api.UsedRange.Rows.count
#max colum ws1
self.cols = self.__ws1.api.UsedRange.Columns.count
# check name sheet (have to have AZB)
if self.namesheetchild in self.__wsname:
pass
else:
messagebox.showerror(
"error", "Name sheet must start \
from symbols {}...".format(
self.namesheetchild))
self.__dirpath = getdirpath(self.pathtocopy)
self.__fpath = refullpath(dirpath=self.__dirpath,
filename=self.__namefile)
#self.__wbthns = book_by_path(path=self.__fpath, visible = False)
#self.__wbthns = xw.Book(self.__fpath)
self.__wbthns = self.__app.books.open(
self.__fpath,
read_only=True,
ignore_read_only_recommended=True,
update_links=False)
self.__wb1.sheets[self.__wsname].activate()
if self.__wbthns == None:
messagebox.showerror(
"Error directory", "Directory {0} not exists, \
recheck Directory again Note: extension of excel maybe xls or xlsx,\
check file config {1} have parameter 'khns_namfile'"
.format(self.__fpath, self.pathconf))
def __init__(self, pathconf=None, pathconfigexcelcopy=None):
# call dict
self.pathconfigexcelcopy = pathconfigexcelcopy
dictconf = returndictrowforcsv(path=pathconf)
self.__hm_startcopyrange = dictconf["hm_startcopyrange"]
self.fname = dictconf["khns_namfile"]
self.__startcopyrange = returnseplistintbbystr(
self.__hm_startcopyrange)
self.__hm_startpasterange = dictconf["hm_startpasterange"]
self.__hm_hangmuc = dictconf["hm_hangmuc"]
# copy another range botton
self.__hm_startcopyrangebt = dictconf["hm_startcopyrange_bt"]
self.__startcopyrangebt = returnseplistintbbystr(
self.__hm_startcopyrangebt)
self.__hm_startpasterangebt = dictconf["hm_startpasterange_bt"]
self.pathlsn = refullpath(dirpath=getdirpath(pathconf),
filename=dictconf["listsheetnamehm"])
self.copyhm = dictconf["copyhm"]
def trans_sheet_to_excel_exist(self):
""" copy sheet name to excel existing """
# get dir path
dirpath = getdirpath(self.__pathtocopy)
# get extract file name from path
namefile = ExtractFileNameFromPath(self.__pathtocopy)
pfile = repathlinkexcel(dpath=dirpath,
namefile=namefile,
namesheet=self.__namesheet)
sheet_des = self.__desxw.sheets[self.__namesheet]
sheet_copy = self.__copyxw.sheets[self.__namesheet]
recor_l_lint = int(self.dictconf["recor_l1"])
valueim = returnlist_from_listinstr(self.dictconf["valueim"].replace(
":", ","))
valuehavechild = returnlist_from_listinstr(
self.dictconf["valuehavechild"].replace(":", ","))
msstr = self.dictconf["azb10_ms"]
forbydup = returnlist_from_listinstr(
self.dictconf["zab10_forbydup"].replace(":", ","))
locuseformulas = returnlist_from_listinstr(
self.dictconf["locuseformulas"].replace(":", ","))
col_dup = returnlist_from_listinstr(self.dictconf["dup"].replace(
":", ","))
# max row sheet des
self.m_row = sheet_des.range(
msstr + str(sheet_des.cells.last_cell.row)).end('up').row
hchild = hchildsheet(startrow=recor_l_lint,
col_key_msa=msstr,
pfile=pfile,
columnlra=self.colunsande,
max_row=self.m_row,
lcolumnformulas=locuseformulas,
valueim=valueim,
sheet_des=sheet_des,
sheet_copy=sheet_copy,
col_dup=col_dup,
max_row_allsheet=self.rows,
lvaluehavechild=valuehavechild,
formulasfor_col_dup=forbydup)
self.__copyxw.close()
self.__desxw.save()
self.__desxw.close()
self.__app.quit()
def returndirpath(self, filename1):
pathfulloutput = self.getfullnamefromoutput()
#filename1 = self.getfilenamefromoutput()
dbk = credirfol(getdirpath(pathfulloutput), filename1)
return dbk
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()