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)
