def to_excel(self, directory, filename, startrow, startcol, sheet_name): """Сохранить собранные данные в эксель-файл.""" directory = directory + self.DIR_NAME if not os.path.exists(directory): os.makedirs(directory) filename = directory + filename + self.EXTENSION writer = None try: book = load_workbook(filename) writer = ExcelWriter(filename, engine='openpyxl') writer.book = book except FileNotFoundError: pass df = DataFrame( data={k: v for k, v in self.__dict__.items() if k in self.COLUMNS}) df.to_excel(excel_writer=writer or filename, sheet_name=sheet_name, startrow=startrow, startcol=startcol, index=False) if writer: writer.save() writer.close() print('Сохранено в ' + filename + ' на лист ' + sheet_name)
def fake_start(self): ''' This method will be abandoned. ''' data_not_match = [] for i in self.get_pvt(): name = i[0] row_data = i[1] # if row_data['产品(项目)\n编号-企业'] in self.product_json: if name in self.product_json: print(row_data) self.product_json[name].append(row_data) else: data_not_match.append(row_data) print('not match in self.product_json', name, '\n', row_data) print('-' * 5) from pandas import ExcelWriter from openpyxl import Workbook wb = Workbook() wb.save('./data.xlsx') wter = ExcelWriter('./data.xlsx', engine='openpyxl') wter.book = wb for i in self.product_json: d = concat(self.product_json[i], axis=1, join='outer').T d.to_excel(wter, sheet_name=str(i)) wter.save() continue d_not_match = concat(data_not_match, axis=1, join='outer').T d_not_match.to_excel(wter, sheet_name='notMatch') wter.save() pass
def load_excel(filename, sheet_name, truncate_sheet): startrow = None writer = ExcelWriter(filename, engine='openpyxl') try: # try to open an existing workbook writer.book = load_workbook(filename) # get the last row in the existing Excel sheet # if it was not specified explicitly if startrow is None and sheet_name in writer.book.sheetnames: startrow = writer.book[sheet_name].max_row # truncate sheet if truncate_sheet and sheet_name in writer.book.sheetnames: # index of [sheet_name] sheet idx = writer.book.sheetnames.index(sheet_name) # remove [sheet_name] writer.book.remove(writer.book.worksheets[idx]) # create an empty sheet [sheet_name] using old index writer.book.create_sheet(sheet_name, idx) # copy existing sheets writer.sheets = {ws.title: ws for ws in writer.book.worksheets} except FileNotFoundError: # file does not exist yet, we will create it pass return (startrow, writer)
def join(self, split_sheet=False): ''' Parameters ---------- split_sheet : TYPE, optional DESCRIPTION. The default is False. Returns ------- None. ''' from pandas import concat if split_sheet == True: from pandas import ExcelWriter from openpyxl import Workbook wter = ExcelWriter(self.save_path, engine='openpyxl') wb = Workbook() wb.save(self.save_path) wter.book = wb wb.close() for xl in self._xl_obj_set: xl.save(self.save_path) print('One Excel book, different sheets.') else: print('Join: One Excel book, one sheet.') d = concat(self.data_set, axis=0, join='outer') d.to_excel(self.save_path) pass pass
def writeExcelFileByRep(owner_value, output_folder): owner = str(owner_value) # Filter by owner df_abridged = df[df['Sales Representative'] == owner] rows_target = dataframe_to_rows(df_abridged) # ------------ # Write to Excel # ------------ FILE_PATH = output_folder print(FILE_PATH) book = load_workbook(FILE_PATH) writer = ExcelWriter(FILE_PATH, engine='openpyxl') writer.book = book for sheet in book.worksheets: if sheet.title == 'Contacts': for row in sheet['A1:H4']: for cell in row: cell.value = None # Replenish for r_idx, row in enumerate(rows_target, 1): for c_idx, value in enumerate(row, 1): sheet.cell(row=r_idx, column=c_idx, value=value) constant_tries = 2000 tries = 2000 assert tries > 0 error = None result = None while tries: try: writer.save() writer.close() except IOError as e: error = e tries -= 1 print('Attempt #', (constant_tries - tries) + 1) except ValueError as e: error = e tries -= 1 print('Attempt #', (constant_tries - tries) + 1) else: break if not tries: print('Attempt #', (constant_tries - tries) + 1) raise error print('Attempt #', (constant_tries - tries) + 1) #print(df_abridged.loc[:,'Company':'Industry'].head(5)) print("Done writing Excel file!")
def to_excel(self, write_mode='a', highest_score=True): out_put_path = self.output_file.with_suffix('.xlsx') columns = self._init_columns() dict_data_list = self.get_list_dict_data(self.gcms, highest_score=highest_score) df = DataFrame(dict_data_list, columns=columns) if write_mode == 'a' and out_put_path.exists(): writer = ExcelWriter(out_put_path, engine='openpyxl') # try to open an existing workbook writer.book = load_workbook(out_put_path) # copy existing sheets writer.sheets = dict( (ws.title, ws) for ws in writer.book.worksheets) # read existing file reader = read_excel(out_put_path) # write out the new sheet df.to_excel(writer, index=False, header=False, startrow=len(reader) + 1) writer.close() else: df.to_excel(self.output_file.with_suffix('.xlsx'), index=False, engine='openpyxl') self.write_settings(self.output_file, self.gcms)
def start_multi_sample(self, trans_accid=True): ''' Multi-Thread sample. ''' self.logw('multi-thread multiSample start....') print('multi-thread multiSample start....') print('%d accounts to sample in total.' % len(self.acctli)) from openpyxl import load_workbook, Workbook from pandas import ExcelWriter wb = Workbook() wb.save(self.savedir) wb.close() wb = load_workbook(self.savedir) wter = ExcelWriter(self.savedir, engine='openpyxl') wter.book = wb self.logw('==multiSample==') thread_list = [] for i in self.acctli: acct = Acct(i, self.chart.getna(i, trans_accid)) print('account:', acct.__dict__) th = MultiThread(acct, self, wter) thread_list.append(th) continue for i in thread_list: i.start() for i in thread_list: i.join() wter.save()
def join_to_book(self, bookname='join', split=True, write=False): ''' parameters: bookname: Name of the export Excel. split: If true,save to different sheets of the Excel Workbook. Else, save to single sheet of the Excel Workbook. ''' print('start join_to_book...', get_time_str()) savename = ''.join([self.prefix, bookname, self.suffix, r'.xlsx']) savepath = os.path.join(self.savedir, savename) self.read_tarsht() if split == True: n = 1 for i in self.data_list: if write == True: wb = Workbook() wb.save(savepath) wb.close() wter = ExcelWriter(path=savepath, engine='openpyxl') wter.book = wb i.to_excel(wter, sheet_name=str(n)) pass else: print('cannot show multi sheets.') return n += 1 continue print('finish join_to_book!', get_time_str()) pass else: d = concat(self.data_list, axis=0, join='outer') if write == True: wb = Workbook() wb.save(savepath) wb.close() wter = ExcelWriter(path=savepath, engine='openpyxl') wter.book = wb d.to_excel(wter, sheet_name='join_to_book') print('finish join_to_book!', get_time_str()) pass else: print('finish join_to_book!', get_time_str()) return d pass
def multiSample(self): ''' Single Thread and multi-account sample. ''' print('%d accounts to sample in total.' % len(self.acctli)) from openpyxl import load_workbook, Workbook from pandas import ExcelWriter wb = Workbook() wb.save(self.savedir) wb.close() wb = load_workbook(self.savedir) wter = ExcelWriter(self.savedir, engine='openpyxl') wter.book = wb self.logw('==multiSample==') # thread_list=[] for i in self.acctli: acct = Acct(i, self.chart.getna(i)) # th=MultiThread(acct,self) # thread_list.append(th) print('==start:%s==' % str(acct.accid)) self.logw('==start:%s==' % acct.accid) m_sample = self.getSample(acct) # one of the multi-samples. # try: # m_sample=self.getSample(acct) # one of the multi-samples. # except: # from pandas import DataFrame # m_sample=self.getSample(acct) # one of the multi-samples. # # m_sample= # print('sample for this account failed:') # print(acct.accid,'\t',acct.accna) # logline='\t'.join([acct.accid,acct.accna]) # self.logw(logline) # self.logw('sample for this account failed:') # lgline=acct.accid+'\t'+acct.accna # self.logw(lgline) # pass if m_sample.shape[0] == 0: self.logw('no sample for this account:') no_sample_line = ''.join( [str(acct.accid), '\t', str(acct.accna)]) self.logw(no_sample_line) pass else: m_sample.to_excel(wter, sheet_name=str(acct.accid + acct.accna)) # yield list(m_sample.loc[:,'glid'].drop_duplicates()) # print(m_sample) # 查看样本 wter.save() # yield m_sample print('==end:%s==' % str(acct.accid)) self.logw('==end:%s==' % acct.accid) wter.close() return
def to_excel_file(dataframe, file_name, if_index): writer = ExcelWriter(file_name, engine='openpyxl') if not os.path.exists(file_name): dataframe.to_excel(writer, dataframe.name, index=if_index) else: writer.book = load_workbook(writer.path) dataframe.to_excel(excel_writer=writer, sheet_name=dataframe.name, index=if_index) writer.save() writer.close()
def writeExcelData(x,excelfile,sheetname,startrow,startcol): from pandas import DataFrame, ExcelWriter from openpyxl import load_workbook df=DataFrame(x) book = load_workbook(excelfile) writer = ExcelWriter(excelfile, engine='openpyxl') writer.book = book writer.sheets = dict((ws.title, ws) for ws in book.worksheets) df.to_excel(writer, sheet_name=sheetname,startrow=startrow-1, startcol=startcol-1, header=False, index=False) writer.save() writer.close()
def __save_to_file(self, save_path): # save to existing Excel file. print('save to an existing file.') from pandas import ExcelWriter wb = load_workbook(save_path) wter = ExcelWriter(save_path) wter.book = wb self.data.to_excel(wter, sheet_name='-'.join( [self.sheet_name, self.file_name_pure])) wter.save() pass
def appendExcel(data_frame, file_name,sc=0,use_index=False): #Define and load excel file writefilestr = file_name book = load_workbook(writefilestr) writer = ExcelWriter(writefilestr, engine='openpyxl') writer.book = book #Get list of sheets in excel file writer.sheets = dict((ws.title, ws) for ws in book.worksheets) #Write and save data to excel file data_frame.to_excel(writer,'Sheet1',index=use_index,startrow=0, startcol=sc,header=None) writer.save() writer.close()
def __save_to_dir(save_path): file_name=''.join([file_nickname,get_time_str(woc=True),r'.xlsx']) save_path=osjoin(save_path,file_name) if isfile(save_path): __save_to_file(save_path) else: wb=Workbook() wb.save(save_path) wter=ExcelWriter(save_path) wter.book=wb df.to_excel(wter,sheet_name=sheet_name) wter.save()
def save_session(df, session_name): try: book = load_workbook('trans_record.xlsx') writer = ExcelWriter('trans_record.xlsx', engine='openpyxl') writer.book = book df.to_excel(writer, sheet_name=session_name, index=False) writer.save() writer.close() except: print( "ERROR: Cannot load/save to workbook. Ensure validity of files and that the sheet is not open." )
def save_cut(cut_df, i, name): """Cutting full file into parts according to the lengh""" book = load_workbook(template_short_path) writer = ExcelWriter(output_path + u'Реестр' + name + u'{} часть_'.format(i + 1) + '{}'.format(cut_df.shape[0]) + u' записей.xlsx', engine='openpyxl') writer.book = book writer.sheets = dict((ws.title, ws) for ws in book.worksheets) cut_df.to_excel(writer, u'Прил 2', startrow=26, header=False, index=False) writer.save()
def write_log_to_excel(log, xls_filename, sheet_name): from openpyxl import load_workbook from pandas import ExcelWriter, DataFrame df = DataFrame(["'" + l for l in log.strip().splitlines()]) book = load_workbook(xls_filename) writer = ExcelWriter(xls_filename, engine='openpyxl') writer.book = book writer.sheets = dict((ws.title, ws) for ws in book.worksheets) df.to_excel(writer, sheet_name, index=False) writer.save()
def export_material_data(self, savepath): ''' Save self.data, self.price_matrix, product_table and material_table to a Microsoft Excel file. ''' from pandas import ExcelWriter from openpyxl import Workbook, load_workbook if os.path.isfile(savepath) == True: wb = load_workbook(savepath) elif os.path.isdir(savepath) == True: from autk.fileAssistant.skjoinxl import get_time_str file_name = r'-'.join(['inventory', get_time_str(), r'.xlsx']) savepath = os.path.join(file_name, savepath) wb = Workbook() wb.save(savepath) else: wb = Workbook() wb.save(savepath) wter = ExcelWriter(savepath, engine='openpyxl') wter.book = wb if self.item_json['date'] is not None: d = self.filter_data([[r'^\d.+$', 'date', True, True]], over_write=False) else: d = self.data d_material = d.set_index(['year', 'material', 'month' ]).sort_values(['year', 'material', 'month'], ascending=True, axis=0) d_product = d.set_index(['year', 'product', 'month' ]).sort_values(['year', 'product', 'month'], ascending=True, axis=0) from numpy import sum d_pivot_product = d_product.pivot_table( values=['recal_amount_allocated'], index=['year', 'material'], columns=['product'], aggfunc=sum) print(d_pivot_product) self.data.to_excel(wter, sheet_name='inventory') # self.price_matrix.to_excel(wter,sheet_name='price') d_material.to_excel(wter, sheet_name='material') d_product.to_excel(wter, sheet_name='product') d_pivot_product.to_excel(wter, sheet_name='material-product-pvt') # for mater in self.material_json: # for y in self.material_json[mater]: # for m in self.material_json[mater][y]: # month_df=m wter.save() return
def pandas_dbexcel(viewname, con): book = load_workbook(dbtbltype + '.xlsx') writer = ExcelWriter(dbtbltype + '.xlsx', engine='openpyxl') writer.book = book writer.sheets = dict((ws.title, ws) for ws in book.worksheets) df = pd.read_sql_query("SELECT * from " + viewname, con) #verify that result of SQL query is stored in the dataframe #print(df) df.to_excel(writer, sheet_name=viewname, encoding='utf8') writer.save()
def pd_html_excel(): book = load_workbook('tables.xlsx') writer = ExcelWriter('tables.xlsx', engine='openpyxl') writer.book = book writer.sheets = dict((ws.title, ws) for ws in book.worksheets) for x in range(0, len(df)): df[x].to_excel(writer, sheet_name="table_" + str(x)) writer.save() file_2.write( "Success !!! Please check tables.xlsx for extracted tables from the webpage." + '\n' + '\n')
def to_excel_ExcelWriter( df, file, rowNames = False, masterFile = '', promptIfLocked = False, xlsxEngine = 'openpyxl', #xlsxwriter ): writer = ExcelWriter(file, engine=xlsxEngine) DataFrame().to_excel(writer, list(df.keys())[0]) #ensure the file is not locked. try: #create the file even before we try to add any data to ensure it isn't locked writer.save() except: counter = 0 file_updated = False #if the file is locked/RO, sleep to give user time to close it. After 10 failures, wait for user input. while( not file_updated ): try: writer.save() file_updated = True except: print(' >> Waiting on ' + os.path.split(file)[1] + ' for 15 seconds' ) counter = counter + 1 if( counter >= 10 ): pdb.set_trace() sleep(15) if(masterFile != ''): # book = xlrd.open_workbook(masterFile) #create an archived copy of the master book = openpyxl.load_workbook(masterFile) writer.book = book writer.sheets = dict((ws.title, ws) for ws in book.worksheets) for sheetName, this_df in df.items(): if( not len( this_df ) ): print( ' ++ No data for sheet ' + sheetName + '. Moving on.') continue this_df.to_excel(writer, sheetName, index = rowNames) writer.save() return(1)
def save_all(all_df): """Concating all into one file""" book = load_workbook(template_all_path) writer = ExcelWriter( output_path + u'Реестр_полный_ {} записей.xlsx'.format(all_df.shape[0]), engine='openpyxl') writer.book = book writer.sheets = dict((ws.title, ws) for ws in book.worksheets) try: del all_df['uid_first'] del all_df['uid'] del all_df['true_index'] except: pass all_df.to_excel(writer, u'Прил 2', startrow=26, header=False, index=False) writer.save()
def export_json_material(self, savepath, depth='year'): ''' Export self.material_json into Microsoft Excel file. self.material_json is like: { year_1:{ material_1:{ January:DataFrame, Febuary:DataFrame, ... }, material_2:{ January:DataFrame, Febuary:DataFrame, ... } }, year_2:{ material_1:{ January:DataFrame, Febuary:DataFrame, ... } } } ''' from pandas import ExcelWriter from openpyxl import Workbook, load_workbook if os.path.isfile(savepath) == True: wb = load_workbook(savepath) elif os.path.isdir(savepath) == True: from autk.fileAssistant.skjoinxl import get_time_str file_name = r'-'.join(['inventory', get_time_str(), r'.xlsx']) savepath = os.path.join(file_name, savepath) wb = Workbook() wb.save(savepath) else: wb = Workbook() wb.save(savepath) wter = ExcelWriter(savepath, engine='openpyxl') wter.book = wb pass
def append_df_to_excel( df: DataFrame, filename: str, sheet_name='Sheet1', **to_excel_kwargs ) -> None: file_path = path.join(environ['USERPROFILE'], 'Desktop', filename) if not path.exists(file_path): df.to_excel( file_path, sheet_name=sheet_name, **to_excel_kwargs) return None writer = ExcelWriter(file_path, engine='openpyxl', mode='a') writer.book = load_workbook(file_path) start_row = writer.book[sheet_name].max_row writer.sheets = {ws.title: ws for ws in writer.book.worksheets} df.to_excel(writer, sheet_name, startrow=start_row, header=False, **to_excel_kwargs) writer.save() writer.close() return None
def write_sample(self, filterIdCol, account, savedir): ''' account is an instance object of class Acct with attributes of 'accid' and 'name'. ''' from openpyxl import load_workbook, Workbook wb = Workbook() wb.save(savedir) wb.close() wb = load_workbook(savedir) from pandas import ExcelWriter wter = ExcelWriter(savedir, engine='openpyxl') wter.book = wb s1 = self.accum_sample(account.accid, filterIdCol, acquired_rate=0.81, drcrdesc=self.drcrdesc) s1.to_excel(wter, sheet_name=account.name) wter.save() wter.close() pass
def ControlTreatedUnique(item, expgroup): controlfile = Controls[item][0] pathc = 'DEG_Identification/' + item + '/' + controlfile for expfile in expgroup: print 'Finding Common Probes for File : ' + expfile pathe = 'DEG_Identification/' + item + '/' + expfile exprobes = pd.read_excel(open(pathe, 'rb'), sheetname='ABS_CALL', index=False)['Probename'] controlprobes = pd.read_excel(open(pathc, 'rb'), sheetname='ABS_CALL', index=False)['Probename'] commonprobes = list(set(exprobes) & set(controlprobes)) expdata = pd.read_excel(open(pathe, 'rb'), sheetname='ABS_CALL', index=False) commondf = expdata[expdata['Probename'].isin(commonprobes)] writernw = ExcelWriter(pathe, engine='openpyxl') book = load_workbook(pathe) writernw.book = book writernw.sheets = dict((ws.title, ws) for ws in book.worksheets) tme = expfile.split('_')[1].split('.')[0] commondf.to_excel(writernw, 'COMMN_' + tme, index=False) writernw.save()
def main(): excelfile = "/Users/ChiYuChen/Intro to Machine Learning and Data Mining/Assignment 5/Assignment_5_Data_and_Template_Original.xlsx" sheetname = "Data" xs = np.array(readExcel(excelfile, sheetname=sheetname, startrow=2, endrow=951, endcol=2), dtype=np.float64) # print data # print data[0] # print data[-1] dims = xs.shape n = dims[0] d = dims[1] # print d c = 3 # initial random guesses for parameters np.random.seed(0) scales = np.array([np.divide(1, c, dtype=np.float64)] * c) # print scales maxs = np.amax(xs, axis=0) # print maxs mins = np.amin(xs, axis=0) # print mins mus = np.zeros((c, d)) for i in range(c): for j in range(d): mus[i, j] = (maxs[j] - mins[j]) * np.random.random() + mins[j] # print j, maxs[j], mins[j], mus[i, j] # print mus vs = np.var(xs, axis=0) # print vars sigmas = np.zeros((c, d, d)) for i in range(c): for j in range(d): sigmas[i, j, j] = vs[j] # print sigmas ps, scales, mus, sigmas = em_gmm_orig(xs, scales, mus, sigmas, iterations=10000) print scales amounts = np.round(scales * n) print amounts print mus idx_male = np.argmax(mus[:, 0]) idx_children = np.argmin(mus[:, 0]) idx_female = 3 - idx_male - idx_children print idx_male print idx_female print idx_children labelnames = ["F", "F", "F"] labelnames[idx_male] = "M" labelnames[idx_female] = "F" labelnames[idx_children] = "C" print labelnames print ps label_indices = np.argmax(ps, axis=1) print label_indices labels = np.array([labelnames[label_idx] for label_idx in label_indices]) print labels confs = np.array( [ps[idx, label_idx] for idx, label_idx in enumerate(label_indices)]) print confs totals = np.array( [amounts[idx_male], amounts[idx_female], amounts[idx_children]]) print totals # In the below plots the white dots represent the observed heights. from pandas import DataFrame, ExcelWriter from openpyxl import load_workbook excelfile = "/Users/ChiYuChen/Intro to Machine Learning and Data Mining/Assignment 5/Assignment_5_Data_and_Template.xlsx" book = load_workbook(excelfile) writer = ExcelWriter(excelfile, engine='openpyxl') writer.book = book writer.sheets = dict((ws.title, ws) for ws in book.worksheets) sheetname = "Results" # print labels df = DataFrame(labels) df.to_excel(writer, sheet_name=sheetname, startrow=1, startcol=0, header=False, index=False) # print confs df = DataFrame(confs) df.to_excel(writer, sheet_name=sheetname, startrow=1, startcol=1, header=False, index=False) # print totals df = DataFrame(totals) df.to_excel(writer, sheet_name=sheetname, startrow=1, startcol=5, header=False, index=False) writer.save() writer.close()
from tkinter import * from pandas import DataFrame from pandas import ExcelWriter import datetime import openpyxl from openpyxl import load_workbook root = Tk() root.title("Travel Transport Employee Clock") root.geometry("600x600") writer = ExcelWriter(r'test.xlsx', engine='openpyxl') book = load_workbook(r'test.xlsx') writer.book = book writer.sheets = {ws.title: ws for ws in book.worksheets} check1 = False check2 = False check3 = False inTime1 = 0 outTime1 = 0 inTime2 = 0 outTime2 = 0 inTime3 = 0 outTime3 = 0 #Functions def enterval(): global check1 global check2 global check3 global inTime1 global outTime1 global inTime2 global outTime2
################################################################################### for index, row in filesystem.iterrows(): filepath = 'DEG_Identification/' + row['Accession'] + '/' + row['FileName'] print "Finding Up & Down Regulated for File : " + row['FileName'] lastsheet = pd.read_excel(open(filepath, 'rb'), sheet_name='AVG_FC_CALC') UpRegulatedGenes = lastsheet[lastsheet['LFC'] >= 1] DownRegulatedGenes = lastsheet[lastsheet['LFC'] <= -1] writernw = ExcelWriter(filepath, engine='openpyxl') book = load_workbook(filepath) writernw.book = book writernw.sheets = dict((ws.title, ws) for ws in book.worksheets) UpRegulatedGenes.to_excel(writernw, 'UpRegulated', index=False) writernw.save() writernw = ExcelWriter(filepath, engine='openpyxl') book = load_workbook(filepath) writernw.book = book writernw.sheets = dict((ws.title, ws) for ws in book.worksheets) DownRegulatedGenes.to_excel(writernw, 'DownRegulated', index=False) writernw.save() ###################################################################################
def CreateFileExcel(pathin,pathout): #filename = "Config_Setting.csv" pathconf = PathSteel(dir_path =pathin, FileName = "Config_Setting.csv")\ .refpath() #fullpath = os.path.join(pathin,filename) # get path store data to handling Right_Genneral_All_path = PathFromFileNameAndDirpath(dir_path =pathin, filename ="Right_Genneral_All.csv" ) Left_Genneral_All_path = PathFromFileNameAndDirpath(dir_path =pathin, filename ="Left_Genneral_All.csv" ) # key value keyvalue = ["ValueGeneral", "Columnmove", "LocationCellForMoveColumn", "GenneralColumnNotChange", "GeneralConcernRaffter", "Genneral_Select", "LocationOfRowLeft", "LocationOfRowRight", "ExcelCellForMoveColumnRight", "LocationOfPurlin", "startrow", ] #location in conf locvalue = [12,16,17,19,20,21,23,24,25,26,30] credict_c = credict(KeyValues = keyvalue, LocConf = locvalue, Config_Setting_Path = pathconf) credict_list = credict_c.Dictfromkeyandvalueconf() # create arr from keyvalue valgen = credict_list.get("ValueGeneral", "") colmv = credict_list.get("Columnmove", "") locmvcol = credict_list.get("LocationCellForMoveColumn", "") gencolnotchg = credict_list.get("GenneralColumnNotChange", "") genconraf = credict_list.get("GeneralConcernRaffter", "") gensel = credict_list.get("Genneral_Select", "") locrowleft = credict_list.get("LocationOfRowLeft", "") locrowright = credict_list.get("LocationOfRowRight", "") excemvcolright = credict_list.get("ExcelCellForMoveColumnRight", "") locpur = credict_list.get("LocationOfPurlin", "") strow = credict_list.get("startrow", "") # data template if IsRunningInPyinstallerBundle(): #NameFile = ExtractFileNameFromPath(DataExcel) DataExcel = resource_path_is_from_pyinstall_and_dev(FileName = 'DataALL - Template.xlsx', Subfolder="Data", Is_Directory_Path_To_SubFolder= True, dir_path=sys._MEIPASS) else: # get file excel from template excel (full path) DataExcel = PathSteel(modulename =templatexc, FileName ='DataALL - Template.xlsx')\ .getpathmodule() """ DataExcel = os.path.join((os.path.dirname(templatexc.__file__)), 'DataALL - Template.xlsx') """ book = load_workbook(DataExcel) writer = ExcelWriter(DataExcel, engine='openpyxl') writer.book = book writer.sheets = dict((ws.title, ws) for ws in book.worksheets) for path in [Left_Genneral_All_path, Right_Genneral_All_path]: df1 = pd.read_csv(path, delimiter=',', index_col = 0) dfCount = df1.shape df1.to_csv(path) #write Left to Excel dfValueGeneral = pd.read_csv(path, delimiter=',', usecols = valgen, nrows= 1) dfValueGeneral.to_excel(writer,'General Member', index=False,header=True , startcol=0,startrow \ = strow[0]) worksheet = writer.sheets['General Member'] # create frame to excel excellframe = toexcel(worksheet = worksheet, path = path, path_conf =pathconf, lpath=Left_Genneral_All_path, rpath = Right_Genneral_All_path) if path == Left_Genneral_All_path: usecolsArr = locrowleft LocationMoveColumn = locmvcol #write path to excel excellframe.wrivaltoexc() else: usecolsArr = locrowright LocationMoveColumn = excemvcolright #write path to excel excellframe.wrivaltoexc() #Write genneral to excel DfChangegenneral = pd.read_csv(path, delimiter=',', usecols = gencolnotchg, nrows= 1 ) DfChangegenneral.to_excel(writer, 'General Member', index=False, header=True, startcol=0, startrow= int(usecolsArr[1]) ) #write Genneral Concern Raffter DfChangegenneral = pd.read_csv(path, delimiter=',', usecols = genconraf ) DfChangegenneral.to_excel(writer, 'General Member', index=False, header=True , startcol=0, startrow=int(usecolsArr[2]) ) #write genneral selected DfChangegenneral = pd.read_csv(path, delimiter=',', usecols = gensel, nrows= 1) DfChangegenneral.to_excel(writer, 'General Member', index=False, header=True , startcol=0, startrow= int(usecolsArr[0]) ) #write purlin roof DfChangegenneral = pd.read_csv(path, delimiter=',', usecols = locpur, nrows= 1) DfChangegenneral.to_excel(writer, 'General Member', index=False, header=True , startcol=0, startrow= int(usecolsArr[3])) worksheet = writer.sheets['General Member'] #Wirte move Column to excel excellframe.writemovecol(LocationMoveColumn, colmv) # create full path from dirpath path = PathSteel(dir_path =pathout, FileName ='new_big_file.xlsx')\ .refpath() #path = os.path.join(pathout,'new_big_file.xlsx') book.save(path)