def the_mayhem(f1,f2):
print f1, f2
t1 = time.time()
w1 = load_workbook(f1)
w2 = load_workbook(f2)
w1n = f1[:f1.find('.')]
w2n = f2[:f2.find('.')]
wbsucc = "Pass"
sdic = {}
#Gives the sheet number of the workbook by index
#s1 = w1.worksheets[0]
#s2 = w2.worksheets[0]
for i in xrange(len(w1.worksheets)):
for j in xrange(i,i+1):
# print "comparing for sheet " + str(j+1)+ "..."
smsg = compare(w1.worksheets[j], w2.worksheets[j],w1n,w2n)
sdic[w1.worksheets[j].title] = smsg[0],smsg[1]
w2.save(f2)
for e in sdic.values():
if "Pass" not in e[0]:
wbsucc = "Fail"
t2 = time.time()
ttime = round((t2-t1),2)
#print sdic
return [ttime,w1n,w2n,wbsucc,sdic]
def get_COV_excel(input_excel_path,file_name,output_excel_path,col,template_dir):
for key in keys:
input_excel_name = key + "_" + file_name + "-Tests.xlsx"
input_excel_name = os.path.join(input_excel_path,input_excel_name)
output_excel_name = os.path.join(output_excel_path, key + "_" + "cov" + ".xlsx" )
wb = openpyxl.load_workbook(input_excel_name)
if col == col_start:
excel_name = os.path.join(template_dir, key + "_Template.xlsx")
else:
excel_name = output_excel_name
rb = openpyxl.load_workbook(excel_name)
rows = row_start
for sheets in wb.get_sheet_names():
#rb = openpyxl.load_workbook(output_excel_path)
ip_sheet = wb.get_sheet_by_name(sheets)
op_sheet = rb.get_sheet_by_name(sheets)
op_sheet.cell(row=row_start-1, column=col).value = file_name
while ip_sheet.cell(row= rows,column = 12).value != None:
op_sheet.cell(row = rows, column = col ).value = ip_sheet.cell(row= rows,column = 12).value
rows += 1
rows = row_start
rb.save(output_excel_name)
def saveresult(tprint, c_central, c_upstream, method):
pos_anal, c_anal = [], []
# load the analytical solution
# T = 50 days
wbsheet = load_workbook('cp2_t_50_3rd.xlsx')['Sheet1'].columns
pos_anal.append([cell.value for cell in wbsheet[1][1:]])
c_anal.append([cell.value for cell in wbsheet[2][1:]])
# T = 100 days
wbsheet = load_workbook('cp2_t_100_3rd.xlsx')['Sheet1'].columns
pos_anal.append([cell.value for cell in wbsheet[1][1:]])
c_anal.append([cell.value for cell in wbsheet[2][1:]])
for i in xrange(len(tprint)):
t = tprint[i]
c_anal_v = c_anal[i]
c_central_v = c_central[i]
c_upstream_v = c_upstream[i]
with open(method + "_T" + str(t) + "_values.csv", 'w') as csvfile:
fieldnames = ['location', 'analytical', 'central', 'upstream']
writer = csv.DictWriter(csvfile, fieldnames=fieldnames)
writer.writeheader()
for j in xrange(len(x_pos)):
writer.writerow({
'location': '%.2f' % x_pos[j],
'analytical': '%.5f' % c_anal_v[j],
'central': '%.5f' % c_central_v[j],
'upstream': '%.5f' % c_upstream_v[j]
})
def getData(sensor):
#wb with data
data = openpyxl.load_workbook('data.xlsx')
sheet = data.get_sheet_by_name('Sheet1')
#wb with reference titles
titles = openpyxl.load_workbook('reference.xlsx')
title_sheet = titles.get_sheet_by_name('Sheet1')
#wb to save data to
results = openpyxl.Workbook()
new_sheet = results.active
sheet.title = sensor + ' Results'
counter = 1;
for row in range(1, sheet.max_row):
new_sheet.cell(row = counter, column = row).value = title_sheet.cell(row = counter, column = row).value
counter += 1;
#print('Working...')
#iterate through .xlsx file
for row in range(1,sheet.max_row):
data = str(sheet['C' + str(row)].value).lower()
if sensor.lower() in data:
for cell in range(1, row):
new_sheet.cell(row = counter, column = cell).value = sheet.cell(row = row, column = cell).value
counter = counter + 1;
#print('Creating new file...')
results.save(sensor + ' Data.xlsx')
def main():
args = docopt(USAGE)
wb1 = load_workbook(args['<controlled.xlsx>'])
keywords = {}
for r in wb1.worksheets[0]:
if not isinstance(r[0].value, long):
continue
keywords[r[0].value] = {
u'title': {
u'en': r[1].value,
u'fr': r[2].value
},
u'keywords': {
u'en': [r[1].value],
u'fr': [r[2].value]
}
}
wb2 = load_workbook(args['<uncontrolled.xlsx>'])
for r in wb2.worksheets[0]:
if not isinstance(r[0].value, long):
continue
keywords[r[0].value][u'keywords'][lang[r[2].value]].append(r[1].value)
for keyword_id in keywords:
print json.dumps({
u'owner_org': u'statcan',
u'private': False,
u'type': u'keyword',
u'name': u'keyword-'+unicode(keyword_id),
u'title': keywords[keyword_id][u'title'],
u'controlled_keyword_code': keyword_id,
u'keywords': keywords[keyword_id][u'keywords']
})
def main():
print(full_path)
workbook=openpyxl.load_workbook(full_path)
sh_names = workbook.get_sheet_names()
for sh_name in sh_names:
print("processing:",sh_name)
new_full_path = os.path.join(path_dir,'{}_split.xls'.format(sh_name))
copyfile(full_path, new_full_path)
print("Created:",new_full_path)
new_workbook=openpyxl.load_workbook(full_path)
new_sh_names = new_workbook.get_sheet_names()
print(new_sh_names)
for new_sh_name in new_sh_names:
print("Sheet:",new_sh_name,end="")
if new_sh_name == sh_name:
print("... skipping:")
else:
print("... deleting:")
sheet_to_delete = new_workbook.get_sheet_by_name(new_sh_name)
new_workbook.remove_sheet(sheet_to_delete)
new_workbook.save(new_full_path)
print("Finished splitting",full_path)
def plot_c(tprint, c_central, c_upstream, method):
pos_anal, val_anal = [], []
# load the analytical solution
# T = 50 days
wbsheet = load_workbook('cp2_t_50_3rd.xlsx')['Sheet1'].columns
pos_anal.append([cell.value for cell in wbsheet[1][1:]])
val_anal.append([cell.value for cell in wbsheet[2][1:]])
# T = 100 days
wbsheet = load_workbook('cp2_t_100_3rd.xlsx')['Sheet1'].columns
pos_anal.append([cell.value for cell in wbsheet[1][1:]])
val_anal.append([cell.value for cell in wbsheet[2][1:]])
for i in xrange(len(tprint)):
fig, ax = plt.subplots()
ax.plot(x_pos, c_upstream[i], '--', label="upstream")
ax.plot(x_pos, c_central[i], 'o-', label="central")
ax.plot(pos_anal[i], val_anal[i], label="analytical")
ax.legend(loc="upper right")
ax.set_ylim([0, 1.5])
ax.set_ylabel('c(x)')
ax.set_xlabel('x')
ax.set_title('Method: %s; Time: %s' % (method, str(tprint[i])))
plt.savefig(method + "_" + str(tprint[i]) + ".png")
def xlsx_to_tsv(file_object, f='web'):
'''
Converts xlsx SBtab file to tsv format.
Parameters
----------
file_object: xlsx file object
SBtab table as xlsx file object.
f: str
String indicating how the file object is represented ('web' for online file handling, otherwise normal file handling)
Returns: str
SBtab table as tsv string.
'''
import openpyxl
from io import BytesIO
if f == 'web': wb = openpyxl.load_workbook(filename = BytesIO(file_object))
else: wb = openpyxl.load_workbook(filename = file_object)
ws = wb.active
ranges = wb[ws.title]
table_string = ''
for row in ranges:
for column in row:
if str(row[0].value).startswith('!'):
if column.value != None and str(column.value) != '':
table_string += str(column.value) + '\t'
else:
table_string += str(column.value) + '\t'
table_string = table_string[:-1] + '\n'
return table_string
def populateDataAuto():
wb = openpyxl.load_workbook('stats.xlsx', data_only=True)
# mainSheet = wb.get_sheet_by_name("Indian_Sites")
# mainSheet["B49"] = "=SUM(B2:B47)-SUM(H2:H47)"
# wb.save("stats.xlsx")
# mainSheet = wb.get_sheet_by_name("Indian_Sites")
# mainSheet["B49"] = str(int(mainSheet["B49"].value) + int(mainSheet["I49"].value) + int(mainSheet["J49"].value))
# wb.save("stats.xlsx")
sheets = wb.get_sheet_names()
print sheets
sheets.remove("ICICI_Sites")
sheets.remove("Ireland")
sheets.remove("RBC")
sheets.remove("Sheet1")
calcInternational(wb)
x = ['Canada', 'Indian_Sites', 'ANZ_Sites', 'UK_Sites', 'SouthAfrican_Sites']
for i in sheets:
if i not in x:
continue
fillData(wb, i)
fillFormula(wb, i)
wb.save("res.xlsx")
wb = openpyxl.load_workbook('res.xlsx', data_only=True)
getInternationalImpact(wb)
print "Stats Added"
def Open(self):
try:
if not os.path.isfile(self.v_filename):
raise Spartacus.Utils.Exception('File {0} does not exist or is not a file.'.format(self.v_filename))
if self.v_extension == 'csv':
self.v_file = open(self.v_filename, encoding=self.v_encoding)
v_sample = self.v_file.read(1024)
self.v_file.seek(0)
v_sniffer = csv.Sniffer()
if not v_sniffer.has_header(v_sample):
raise Spartacus.Utils.Exception('CSV file {0} does not have a header.'.format(self.v_filename))
v_dialect = v_sniffer.sniff(v_sample)
if self.v_delimiter is not None:
v_dialect.delimiter = self.v_delimiter
self.v_object = csv.DictReader(self.v_file, self.v_header, None, None, v_dialect)
self.v_open = True
elif self.v_extension == 'xlsx':
self.v_object = openpyxl.load_workbook(self.v_filename, read_only=True)
self.v_open = True
elif self.v_extension == 'xls':
v_tmp_file = tempfile.NamedTemporaryFile(suffix='.xlsx')
v_tmp_file.file.close()
pyexcel.save_book_as(file_name=self.v_filename, dest_file_name=v_tmp_file.name)
self.v_object = openpyxl.load_workbook(v_tmp_file.name, read_only=True)
self.v_open = True
else:
raise Spartacus.Utils.Exception('File extension "{0}" not supported.'.format(self.v_extension))
except Spartacus.Utils.Exception as exc:
raise exc
except Exception as exc:
raise Spartacus.Utils.Exception(str(exc))
def populate(studentsfile,marksfile):
wb=openpyxl.load_workbook(studentsfile)
college_sheet = wb["Colleges"]
for row in range(2, college_sheet.max_row+1):
c=College(name=college_sheet.cell(row=row,column=1).value,Acronym=college_sheet.cell(row=row,column=2).value,
location=college_sheet.cell(row=row,column=3).value,contact=college_sheet.cell(row=row,column=4).value)
c.save()
c=College.objects.all()
current_students_sheet = wb["Current"]
for row in range(2, current_students_sheet.max_row + 1):
s = Student(name=current_students_sheet.cell(row=row, column=1).value,
college_id=c.get(Acronym=current_students_sheet.cell(row=row, column=2).value).id,
emailid=current_students_sheet.cell(row=row, column=3).value,
dbnames=current_students_sheet.cell(row=row, column=4).value,
droppedout=0)
s.save()
deleted_students_sheet = wb["Deletions"]
for row in range(2, deleted_students_sheet.max_row + 1):
s = Student(name=deleted_students_sheet.cell(row=row, column=1).value,
college_id=c.get(Acronym=deleted_students_sheet.cell(row=row, column=2).value).id,
emailid=deleted_students_sheet.cell(row=row, column=3).value,
dbnames=deleted_students_sheet.cell(row=row, column=4).value,
droppedout=1)
s.save()
s=Student.objects.all()
marks_wb=openpyxl.load_workbook(marksfile)
marks_sheet = marks_wb.active
for row in range(2, marks_sheet.max_row + 1):
m = Mark(student_id=s.get(dbnames=marks_sheet.cell(row=row, column=1).value.split('_')[2]).id,
transform=marks_sheet.cell(row=row, column=2).value,
from_custom_base26=marks_sheet.cell(row=row, column=3).value,
get_pig_latin=marks_sheet.cell(row=row, column=4).value,
top_chars=marks_sheet.cell(row=row, column=5).value,
total = marks_sheet.cell(row=row, column=6).value)
m.save()
def compare_excels(excel1, excel2):
"""Compare two excels based on row iteration."""
# load workbooks
wb1 = load_workbook(excel1, use_iterators=True)
wb2 = load_workbook(excel2, use_iterators=True)
# check if sheets have same names
if not wb1.get_sheet_names() == wb2.get_sheet_names():
return False
# iterate sheets
for ws1, ws2 in zip(wb1.worksheets, wb2.worksheets):
# iterate rows
for row1, row2 in zip(ws1.iter_rows(), ws2.iter_rows()):
# iterate cells
for cell1, cell2 in zip(row1, row2):
# evaluate cells value for equality
if not cell1.value == cell2.value:
return False
return True
def test_export_tabular_xlsx(self):
pj = json.loads(open("files/test.boris").read())
obs_id = "observation #1"
parameters = {"selected subjects": ["subject1", "subject2"],
"selected behaviors": ["p", "s"]}
file_name = "test_export_events_tabular.xlsx"
output_format = "xlsx"
r, msg = export_observation.export_events(parameters,
obs_id,
pj[OBSERVATIONS][obs_id],
pj[ETHOGRAM],
"output/" + file_name,
output_format)
ref_all_cells = []
wb = load_workbook(filename=f'files/{file_name}', read_only=True)
for ws_name in wb.sheetnames:
ref_all_cells.extend([cell.value for row in wb[ws_name].rows for cell in row])
test_all_cells = []
wb = load_workbook(filename=f'output/{file_name}', read_only=True)
for ws_name in wb.sheetnames:
test_all_cells.extend([cell.value for row in wb[ws_name].rows for cell in row])
assert ref_all_cells == test_all_cells
def the_mayhem(f1,f2):
"""
Gets the two files to compare, Extracts out the names of the workbooks.
Iterates over each sheet in both the workbooks and gives sheets to the 'compare' function: returns success message.
Forms a dictionary for each sheet with success message/exception if any.
Finally: returns the time took, workbook names, final Success Message and the sheet level dictionary.
"""
t1 = time.time()
w1 = load_workbook(f1)
w2 = load_workbook(f2)
w1n = f1[f1.rfind('/')+1:f1.rfind('.')]
w2n = f2[f2.rfind('/')+1:f2.rfind('.')]
wbsucc = "Pass"
sdic = {}
for i in xrange(len(w1.worksheets)):
for j in xrange(i,i+1):
smsg = compare(w1.worksheets[j], w2.worksheets[j],w1n,w2n)
sdic[w1.worksheets[j].title] = smsg[0],smsg[1]
w2.save(f2)
for e in sdic.values():
if "Pass" not in e[0]:
wbsucc = "Fail"
t2 = time.time()
ttime = round((t2-t1),2)
#print sdic
return [ttime,w1n,w2n,wbsucc,sdic]
def the_mayhem(f1,f2,col):
print f1, f2
t1 = time.time()
if f1.endswith('.xls') and f2.endswith('.xls'):
w1 = open_xls_as_xlsx(f1)
w2 = open_xls_as_xlsx(f2)
else:
w1 = load_workbook(f1)
w2 = load_workbook(f2)
w1n = f1[f1.rfind('/')+1:f1.find('.')]
w2n = f2[f2.rfind('/')+1:f2.find('.')]
wbsucc = "Pass"
sdic = {}
for i in xrange(len(w1.worksheets)):
for j in xrange(i,i+1):
smsg = compare(w1.worksheets[j], w2.worksheets[j],w1n,w2n,col)
sdic[w1.worksheets[j].title] = smsg[0],smsg[1]
if f2.endswith('.xls'):
w2.save(w2n + str('.xlsx'))
else:
w2.save(f2)
for e in sdic.values():
if "Pass" not in e[0]:
wbsucc = "Fail"
t2 = time.time()
ttime = round((t2-t1),2)
#print sdic
return [ttime,w1n,w2n,wbsucc,sdic]
def show_list_prop(request):
if request.method == 'POST' and request.POST.get('year', None) and request.POST.get('prop', None):
year = request.POST.get('year')
prop = request.POST.get('prop')
try:
wb = openpyxl.load_workbook('..\..\Data\WPP2015_POP_F01_2_TOTAL_POPULATION_MALE.xlsx')
m = get_list_population(wb[prop], year)
wb = openpyxl.load_workbook('..\..\Data\WPP2015_POP_F01_3_TOTAL_POPULATION_FEMALE.xlsx')
w = get_list_population(wb[prop], year)
if m and w:
list_pop = []
for x in w.keys():
list_pop.append([x, w[x], m[x], w[x] + m[x]])
list_pop.sort(key=lambda y: y[3])
return render_to_response('showListOfPopularityWithProp.html',
{'year': year, 'prop': prop, 'list_of_popularity': list_pop},
context_instance=RequestContext(request))
return render_to_response('showListOfPopularityWithProp.html', {'year': year},
context_instance=RequestContext(request))
except KeyError:
print('bad prop!')
return render_to_response('showListOfPopularityWithProp.html', {'error': 'bad property :|'},
context_instance=RequestContext(request))
return render_to_response('showListOfPopularityWithProp.html', {}, context_instance=RequestContext(request))
def update_information(request):
if request.method == 'POST':
if request.POST.get('country', None) and request.POST.get('year', None):
country = request.POST.get('country', None)
year = request.POST.get('year', None)
men = request.POST.get('men', None)
women = request.POST.get('women', None)
if ProtectedCountry.objects.filter(name_country=country).count() == 0:
message_action = 'Please enter number for edit Men Or Women'
if men:
wb = openpyxl.load_workbook('..\..\Data\WPP2015_POP_F01_2_TOTAL_POPULATION_MALE.xlsx')
message_action = update_information_popularity_on_year(wb['ESTIMATES'], country, year, men) + '-> Men \n'
wb.save('..\..\Data\WPP2015_POP_F01_2_TOTAL_POPULATION_MALE.xlsx')
if women:
wb = openpyxl.load_workbook('..\..\Data\WPP2015_POP_F01_3_TOTAL_POPULATION_FEMALE.xlsx')
message_action += update_information_popularity_on_year(wb['ESTIMATES'], country, year, women) + '-> Women'
wb.save('..\..\Data\WPP2015_POP_F01_3_TOTAL_POPULATION_FEMALE.xlsx')
return render_to_response('update_information.html', {'message_action': message_action},
context_instance=RequestContext(request))
else:
message_action = country + "'s data is lock! you can't update information"
return render_to_response('update_information.html', {'message_action': message_action},
context_instance=RequestContext(request))
return render_to_response('update_information.html', {}, context_instance=RequestContext(request))
def loadSouthwireBook():
#return load_workbook('C:\\PaulScripts\\Wire Matrix\\southwire.xlsx', data_only = True)
wirebook = load_workbook('C:\\PaulScripts\\Wire Matrix\\wirebooks\\sw.xlsx', data_only = True)
mc = load_workbook('C:\\PaulScripts\\Wire Matrix\\wirebooks\\mc.xlsx', data_only = True)
sheets = [None]*6
for each in wirebook.get_sheet_names():
if "commercial cu" in each.lower():
sheets[0] = wirebook.get_sheet_by_name(each)
if "residential cu" in each.lower():
sheets[1] = wirebook.get_sheet_by_name(each)
if "bare copper" in each.lower():
sheets[2] = wirebook.get_sheet_by_name(each)
if "commercial al" in each.lower():
sheets[3] = wirebook.get_sheet_by_name(each)
if "residential al" in each.lower():
sheets[4] = wirebook.get_sheet_by_name(each)
ar = mc.get_sheet_names()
for each in mc.get_sheet_names():
if "mc alum 14-10 awg" in each.lower():
sheets[5] = mc.get_sheet_by_name(each)
break
print(sheets)
return sheets
def make_todo_messages(self):
''' Take a formated SMS bank and create language specific todo files'''
sms_bank = xl.load_workbook(self.paths.bank)
try:
final_wb = xl.load_workbook(self.paths.final)
old_translations = sms.message_dict(final_wb.active,sms.FinalRow)
except IOError as e:
old_translations = None
translations = sms.read_sms_bank(sms_bank,old_translations,'anc','postpartum','visits','special')
print "Total Found: {} Todo: {}".format( len(translations),
len([t for t in translations if t.is_todo()]) )
swahili_wb = make_language_todos(translations,'swahili')
luo_wb = make_language_todos(translations,'luo')
swahili_wb.save(self.paths.todo_swahili)
luo_wb.save(self.paths.todo_luo)
if self.options['copy']:
swahili_copy_path = path_with_date(self.paths.todo_swahili)
luo_copy_path = path_with_date(self.paths.todo_luo)
swahili_wb.save(swahili_copy_path)
luo_wb.save(luo_copy_path)
def copy_sheet_to_workbook(self,src_wb_name,src_ws_name,dest_wb_name):
dest_wb = load_workbook(dest_wb_name)
is_csv_file = False
if re.search('.csv$', src_wb_name) is not None:
is_csv_file = True
if src_ws_name in dest_wb.get_sheet_names():
ws_to_del = dest_wb.get_sheet_by_name(src_ws_name)
dest_wb.remove_sheet(ws_to_del)
dest_ws = dest_wb.create_sheet()
dest_ws.title = src_ws_name
if is_csv_file:
with open(src_wb_name) as f:
reader = csv.reader(f)
for r,row in enumerate(reader):
dest_ws.append(row)
if r % 500 == 0 and r > 0:
print("\r" + str(r) + " rows written", end= ' ')
# dest_wb.save(dest_wb_name)
gc.collect()
else:
src_wb = load_workbook(src_wb_name)
assert src_ws_name in src_wb.get_sheet_names()
src_ws = src_wb.get_sheet_by_name(src_ws_name)
for i in range(src_ws.get_highest_column()):
for j in range(src_ws.get_highest_row()):
dest_ws.cell(row=j+1,column=i+1).value = src_ws.cell(row=j+1,column=i+1).value
print("\nSaving file, this may take a few minutes")
dest_wb.save(dest_wb_name)
def naiveBayes(percent_split,file):
global accuracy
global precision
global recall
wb2 = load_workbook(file)
ws=wb2.active
wb3 = load_workbook(file)
ws1=wb3.active
max_rows = ws.get_highest_row()
max_columns = ws.get_highest_column()
indx = calculateIndex(percent_split,ws)+1
if(indx>=max_rows):
indx=indx-1
for index in range(indx,max_rows+1):
predict(index,ws,ws1,file)
wb3.save('results.xlsx')
(TP,FP,TN,FN,sum) = predictCompare(indx,ws1)
# print (TP,FP,TN,FN,sum)
# print max_rows
accuracy=accuracy+float(TP+TN)/sum
try:
recall=recall+float(TP)/(TP+FN)
except:
recall=recall+1
try:
precision=precision+float(TP)/(FP+TP)
except:
precision=precision+1
return (accuracy,precision,recall)
def clearmod(dat):
"""
Clear all the Modifications older than the date dat in the Excel document.
:param dat: the date from which you begin to clear.
:type dat: datetime
:example:
>> clearall(datetime.now())
clear all the Modifications stored in the Excel document that have been add before now.
"""
module_dir = os.path.dirname(__file__)
file_path = os.path.join(module_dir, 'sauvegarde.xlsx')
file_path2 = os.path.join(module_dir, 'tempo.xlsx')
wb = openpyxl.load_workbook(file_path)
sheet = wb.get_sheet_by_name('Modification')
mc = sheet.max_column
mr = sheet.max_row
for i in range (2,mr+1):
if (isinstance(sheet.cell(row=i,column=1).value,float)):
typ=convfdat(sheet.cell(row=i,column=1).value)
else:
typ=sheet.cell(row=i,column=1).value
if (typ!=None and typ < dat):
for j in range (1,mc+1):
sheet.cell(row=i,column=j).value = None
wb.save(file_path2)
wb2 = openpyxl.load_workbook(file_path2)
wb2.save(file_path)
def read(iso_data, datafiles, old):
"""Read all the input Excel files and produce an enormous data dict."""
# Output to populate
dataset = {}
# Workbooks to read
print 'Reading %s...' % datafiles['q_xlsx']
q_workbook = load_workbook(filename=datafiles['q_xlsx'])
print 'Reading %s...' % datafiles['g_xlsx']
g_workbook = load_workbook(filename=datafiles['g_xlsx'])
print 'Reading %s...' % datafiles['a_xlsx']
a_workbook = load_workbook(filename=datafiles['a_xlsx'])
print 'Reading %s...' % datafiles['av_xlsx']
av_workbook = load_workbook(filename=datafiles['av_xlsx'])
# Generate dataset
if old:
dataset['country_old'] = _read_answers(a_workbook, iso_data, datafiles['a_xlsx_sheet'], datafiles['years'])
dataset['question_old'] = _read_questions(q_workbook, datafiles['q_xlsx_sheet'])
dataset['groupings_old'] = _read_groupings(g_workbook, datafiles['g_xlsx_qsheet'])
dataset['regions_old'] = _read_regions(g_workbook, iso_data, datafiles['g_xlsx_csheet'])
dataset['availability_old'] = _read_availability(av_workbook, iso_data, datafiles['av_xlsx_sheets'])
else:
print 'Reading %s...' % datafiles['pp_xlsx']
pp_workbook = load_workbook(filename=datafiles['pp_xlsx'])
dataset['country'] = _read_answers(a_workbook, iso_data, datafiles['a_xlsx_sheet'], datafiles['years'])
dataset['question'] = _read_questions(q_workbook, datafiles['q_xlsx_sheet'])
dataset['groupings'] = _read_groupings(g_workbook, datafiles['g_xlsx_qsheet'])
dataset['regions'] = _read_regions(g_workbook, iso_data, datafiles['g_xlsx_csheet'])
dataset['availability'] = _read_availability(av_workbook, iso_data, datafiles['av_xlsx_sheets'])
dataset['public_participation'] = _read_participation(pp_workbook, iso_data, datafiles['pp_xlsx_sheet'])
return dataset
def getDatax3(self):
import datetime
now = datetime.datetime.now()
now = now.replace(microsecond=0,second = 0)
now = now.strftime("%d_%B_%y,%I-%M_%p")
now = now+".xlsx"
if len(temp) != 0:
wb1 = openpyxl.load_workbook('..\database\database.xlsx')
ws1 = wb1.active
wb2 = openpyxl.load_workbook('..\_frame\_frame.xlsx')
ws2 = wb2.active
for i in xrange(2,len(temp)+2):
for j in xrange(1,22):
ws2.cell(row = i, column = j).value = ws1.cell(row = temp[i-2], column = j).value
wb2.save('..\Result\\'+now)
tempstart = '..\Result\\'+now
self.lbl5d.config(text="File is :: "+"\""+now+"\"")
os.system("start "+tempstart)
self.entry11.delete(0, 'end')
self.entry12.delete(0, 'end')
self.entry13.delete(0, 'end')
self.entry9.delete(0, 'end')
def processWorkBook(path):
#workbook loaded but it display formulas instead of values of formulas so we need two loaded object one with formula and one with value, both work side by side
workBook = openpyxl.load_workbook(settings.PROJECT_ROOT+"/media/"+path)
#value object
workBookValued = openpyxl.load_workbook(settings.PROJECT_ROOT+"/media/"+path,data_only=True)
workBookSheets=workBook.get_sheet_names()
listofdData[:] = []
sheetList[:] = []
#for each sheet we process
for sheet in workBookSheets:
workSheetFormula = workBook.get_sheet_by_name(sheet)
workSheetValued = workBookValued.get_sheet_by_name(sheet)
hr=workSheetFormula.get_highest_row()
hc=workSheetFormula.get_highest_column()
sheetList.append(sheet)
for row in range(1, hr+1):
for col in range(1, hc+1):
val=extractDataFromSheet(workSheetFormula,workSheetValued,col,row,sheet)
if(val!=0):
listofdData.append(val)
return settings.PROJECT_ROOT+"/media/documents/data.hdf5"
def excelCode128(file,bar_width,bar_height,bar_fontSize,bar_margin):
'''
批量生成条码
file(test.xlsx) excel A列 商品编码 文件名。
file1(pic.xlsx) excel A列生成的条形码。
'''
wb = load_workbook(file)
sheet = wb.active
file1 = BAR_CODE_PATH + 'pic.xlsx'
if os.path.exists(file1):
os.remove(file1)
shutil.copyfile(BAR_CODE_PATH + 'start.xlsx',file1)
wb1 = load_workbook(file1)
sheet1 = wb1.active
f = []
for r in range(1,sheet.max_row+1):
# A列数据生成条码
var = sheet.cell(column=1,row=r).value
filename = str(var)+'.png'
#ttf_fontsize:字号、label_border：行距（条码与字之间的距离）、bottom_border：字与下边框之间的距离、height：矩形框高度、bar_width:矩形框宽度
encoder = Code128Encoder(str(var),\
options={"ttf_fontsize":bar_fontSize,"bottom_border":5,\
"height":bar_height, "label_border":bar_margin})
encoder.save(filename, bar_width=bar_width)
#Code128Encoder(str(var)).save(filename,bar_width=1
# 另一个表 A列数据插入条码
img = Image(filename)
A = 'A'+str(r)
sheet1.add_image(img,A)
f.append(filename)
wb1.save(file1)
[os.remove(filename) for filename in f]
print("批量生成条码成功！共计%s条！" %str(len(f)))
def __call__(self):
form = self.request.form
portal = getSite()
workbook = None
if 'setupexisting' in form and 'existing' in form and form['existing']:
fn = form['existing'].split(":")
self.dataset_project = fn[0]
self.dataset_name = fn[1]
path = 'setupdata/%s/%s.xlsx' % \
(self.dataset_name, self.dataset_name)
filename = resource_filename(self.dataset_project, path)
workbook = load_workbook(filename=filename) # , use_iterators=True)
elif 'setupfile' in form and 'file' in form and form['file'] and 'projectname' in form and form['projectname']:
self.dataset_project = form['projectname']
tmp = tempfile.mktemp()
file_content = form['file'].read()
open(tmp, 'w').write(file_content)
workbook = load_workbook(filename=tmp) # , use_iterators=True)
self.dataset_name = 'uploaded'
assert(workbook is not None)
adapters = [[name, adapter]
for name, adapter
in list(getAdapters((self.context, ), ISetupDataImporter))]
for sheetname in workbook.get_sheet_names():
transaction.savepoint()
ad_name = sheetname.replace(" ", "_")
if ad_name in [a[0] for a in adapters]:
adapter = [a[1] for a in adapters if a[0] == ad_name][0]
adapter(self, workbook, self.dataset_project, self.dataset_name)
adapters = [a for a in adapters if a[0] != ad_name]
for name, adapter in adapters:
transaction.savepoint()
adapter(self, workbook, self.dataset_project, self.dataset_name)
check = len(self.deferred)
while len(self.deferred) > 0:
new = self.solve_deferred()
logger.info("solved %s of %s deferred references" % (
check - new, check))
if new == check:
raise Exception("%s unsolved deferred references: %s" % (
len(self.deferred), self.deferred))
check = new
logger.info("Rebuilding bika_setup_catalog")
bsc = getToolByName(self.context, 'bika_setup_catalog')
bsc.clearFindAndRebuild()
logger.info("Rebuilding bika_catalog")
bc = getToolByName(self.context, 'bika_catalog')
bc.clearFindAndRebuild()
logger.info("Rebuilding bika_analysis_catalog")
bac = getToolByName(self.context, 'bika_analysis_catalog')
bac.clearFindAndRebuild()
message = PMF("Changes saved.")
self.context.plone_utils.addPortalMessage(message)
self.request.RESPONSE.redirect(portal.absolute_url())
def read_real_data():
try:
wb = openpyxl.load_workbook('%s\inputData\Data\TumOnline\Read15S.xlsx' % (PROJECT_PATH))
except:
wb = openpyxl.load_workbook('%s/inputData/Data/TumOnline/Read15S.xlsx' % (PROJECT_PATH))
ws = wb.get_sheet_by_name('Conflicts')
ws2 = wb.get_sheet_by_name('Students')
ws3 = wb.get_sheet_by_name('Raum')
maxRow = ws.max_row
maxRow3 = ws3.max_row
# create number of exams and room
data = {'n': maxRow-1, 'r': maxRow3-1}
# create Conflict Matrix
data['Q'] = [[int(cell.value) if not cell.value is None else 0 for cell in row] for row in ws.iter_rows("B2:%s%s" % (openpyxl.utils.get_column_letter(maxRow),maxRow))]
# create number of students participating
data['s'] = [int(ws2["A%s" % (i)].value) for i in range(1,maxRow)]
# create room capacities
data['c'] = sorted([ int(ws3["D%s" % (k)].value) for k in range(2,maxRow3+1)], reverse=True)
return data
def loadwb(self, bookname, create=False):
"""loads specified workbook into class"""
if self.verbose is True:
sys.stdout.write('\rLoading workbook "%s" into memory' % bookname)
try:
wb = op.load_workbook(bookname) # try loading specified excel workbook
except IOError:
bookname = self.correctextension(bookname) # attempts to correct the extension of the provided workbook
if self.verbose is True:
sys.stdout.write('\rLoading workbook "%s" into memory' % bookname)
try:
wb = op.load_workbook(bookname)
except IOError:
if self.verbose is True:
sys.stdout.write(' FAIL\n')
if create is True:
"""
Due to write-only mode, creating and using that book breaks the cell calls
The workbook is therefore created, saved, and reloaded
The remove sheet call is to remove the default sheet
"""
if self.verbose is True:
sys.stdout.write('Creating workbook "%s" and loading it into memory' % bookname)
# wb = op.Workbook(bookname,write_only=False) # create workbook
wb = op.Workbook(bookname) # create workbook
wb.save(bookname) # save it
wb = op.load_workbook(bookname) # load it
wb.remove(wb.worksheets[0]) # remove the old "Sheet"
else:
raise IOError(
'\nThe excel file "%s" could not be found in the current working directory.' % (bookname))
if self.verbose is True:
sys.stdout.write(' DONE\n')
return wb, bookname
def saveall():
"""
Save all the table of the data base on the Excel document.
"""
t=time()
clearall(datetime.now())
module_dir = os.path.dirname(__file__)
file_path = os.path.join(module_dir, 'sauvegarde.xlsx')
file_path2 = os.path.join(module_dir, 'tempo.xlsx')
wb = openpyxl.load_workbook(file_path)
svallpers(wb)
svallgrp(wb)
svallcour(wb)
svallcour2(wb)
svalluv(wb)
svallmodu(wb)
svallnot(wb)
svallsal(wb)
svallmod(wb)
wb.save(file_path2)
wb2 = openpyxl.load_workbook(file_path2)
wb2.save(file_path)
t=time()-t
print('done in', t, 'sec')
def principal(m1,m2):
doc = openpyxl.load_workbook('cifrado.xlsx')
doc.get_sheet_names()
hoja = doc.get_sheet_by_name('Hoja1')
prueba = []
for fila in hoja.rows:
for columna in fila:
boln1 = encryptInt(int(columna.value))
return boln1
boln2 = bin(m2)
boln1Encrypt = []
boln2Encrypt = []
sumEncrypt = []
mulEnctypt = []
res = []
aux = []
if(len(boln1) > len(boln2)):
print len(boln1) - len(boln2)
for i in range(0, len(boln1) - len(boln2)):
aux.append(0)
boln2 = aux + boln2
else:
print len(boln2) - len(boln1)
for i in range(0, len(boln2) - len(boln1)):
aux.append(0)
boln1 = aux + boln1
key = map(keygen,boln1)
boln1Encrypt = map(encrypt,key,boln1)
boln2Encrypt = map(encrypt,key,boln2)
sumEncrypt = map(add,boln1Encrypt,boln2Encrypt)
strEncriptSum = ''.join(str(e) for e in sumEncrypt)
mulEnctypt = map(mult,boln1Encrypt, boln2Encrypt)
resSuma = map (decrypt, key, sumEncrypt)
strSuma = ''.join(str(e) for e in resSuma)
workbook = xlsxwriter.Workbook('enc1.xlsx')
worksheet = workbook.add_worksheet()
i=2
celda = 'A' + repr(i)
celda2 = 'B' + repr(i)
worksheet.write(celda, strEncriptSum)
worksheet.write(celda2, str(len(sumEncrypt) ))
workbook.close()
decSuma = int(strSuma, 2)
#start_time = time()
resMult = map (decrypt, key, mulEnctypt)
#elapsed_time = time() - start_time
#return elapsed_time
strMult = ''.join(str(e) for e in resMult)
decMult = int(strMult, 2)
return decSuma
# Fetch data from the excel file using python module openpyxl
# import module
import openpyxl
# give location of file
path = 'data9.xlsx'
# assign your workbook to a variable
data9_wb = openpyxl.load_workbook(path)
# can use "data9.xlsx" instead of path, but path variable is easier
# e.g. if we need to use it many times and the file is not in the same folder
# storing the data from active sheet into an object called data9
data9_object = data9_wb.active
data9_wb.title = 'Trainees'
data9_title = data9_object.title
# retrieves data in cell A4
cell_value = data9_object.cell(row=1, column=1)
print(cell_value.value)
print(data9_title)
# print total number of rows and columns
count_column = data9_object.max_column
count_row = data9_object.max_row
print("Number of rows: " + str(count_row) + "; Number of columns: " +
str(count_column))
import numpy as np
import matplotlib.pyplot as plt
from mpl_toolkits.mplot3d import Axes3D
from sklearn.cluster import KMeans
from sklearn.datasets import make_blobs
from openpyxl import load_workbook
from openpyxl import Workbook
from sklearn.neighbors import LocalOutlierFactor
plt.rcParams['figure.figsize'] = (16, 9)
dataused = 13
clusters = 7
#load workbook
wb = load_workbook('genes.xlsx')
ws = wb['Sheet1']
#fill data matrix
d = np.zeros((3583,dataused))
for i in range(2,2+dataused):
for j in range(2,3585):
d[j-2][i-2] = ws.cell(row=j,column=i).value
#fill parallel names list
names = []
for i in range(2,3585):
names.append(ws.cell(row = i, column = 1).value)
#outlier removal
clf = LocalOutlierFactor(n_neighbors=20)
y_pred = clf.fit_predict(d)
iterator = 0
import requests
from bs4 import BeautifulSoup
from openpyxl import load_workbook
work_book = load_workbook('./prac01.xlsx')
work_sheet = work_book['prac']
# ajax를 쓰면 기본적으로 json 파일을 가져오는 것이지만, HTML 형식으로 데이터를 달라는 의미임
headers = {
'User-Agent':
'Mozilla/5.0 (Windows NT 10.0; Win64; x64)AppleWebKit/537.36 (KHTML, like Gecko) Chrome/73.0.3683.86 Safari/537.36'
}
data = requests.get(
'https://movie.naver.com/movie/sdb/rank/rmovie.nhn?sel=pnt&date=20190909',
headers=headers)
soup = BeautifulSoup(data.text, 'html.parser')
movies = soup.select('#old_content > table > tbody > tr')
# movies (tr들) 의 반복문을 돌리기
rank = 1
row = 2
for movie in movies:
# movie 안에 a 가 있으면,
a_tag = movie.select_one('td.title > div > a')
if a_tag is not None:
# a의 text를 찍어본다.
title = a_tag.text
star = movie.select('td.point')[0].text
def make_array():
book = openpyxl.load_workbook('ASL_Glossary.xlsx')
ws = book.active
signs = []
for col in ws.iter_cols(min_row=3, max_row=10, min_col=2, max_col=2):
for cell in col:
if cell.value != None and cell.value != " ":
sign_name = cell.value
next_row = cell.row + 1
next_cell = ws['B' + str(next_row)]
empty_block = [] # will contain list of cells between signs
while next_cell.value == None or next_cell.value == " ":
empty_block.append(next_cell)
next_row = int(next_row) + 1
next_cell = ws['B' + str(next_row)]
# Iterate over empty_block to check corresponding cells for movie files
ds_handshapes = []
de_handshapes = []
nds_handshapes = []
nde_handshapes = []
videos = []
for empty_cell in empty_block:
empty_row = empty_cell.row
# Hand shapes (dominant and non dominant)
d_start = ws['F' + str(empty_row)]
d_end = ws['H' + str(empty_row)]
nd_start = ws['G' + str(empty_row)]
nd_end = ws['I' + str(empty_row)]
# Dominant starting hand shape
if d_start.value != None and d_start.value != " " and d_start.value != "------------":
ds_handshapes.append(d_start.value)
if d_end.value != None and d_end.value != " " and d_end.value != "------------":
de_handshapes.append(d_end.value)
if nd_start.value != None and nd_start.value != " " and nd_start.value != "------------":
nds_handshapes.append(nd_start.value)
# Non Dominant ending hand shape
if nd_end.value != None and nd_end.value != " " and nd_end.value != "------------":
nde_handshapes.append(nd_end.value)
# Finds the most commonly used hand shape for that sign
if ds_handshapes == []:
ds_handshape = None
else:
try:
ds_handshape = statistics.mode(ds_handshapes)
except statistics.StatisticsError:
ds_handshape = ds_handshapes
if nds_handshapes == []:
nds_handshape = None
hand_number = 1
else:
try:
nds_handshape = statistics.mode(nds_handshapes)
except statistics.StatisticsError:
nds_handshape = nds_handshapes
hand_number = 2
if de_handshapes == []:
de_handshape = None
else:
try:
de_handshape = statistics.mode(de_handshapes)
except statistics.StatisticsError:
de_handshape = de_handshapes
if nde_handshapes == []:
nde_handshape = None
else:
try:
nde_handshape = statistics.mode(nde_handshapes)
except statistics.StatisticsError:
nde_handshape = nde_handshapes
dominant_handshapes = [ds_handshape, de_handshape]
nondominant_handshapes = [nds_handshape, nde_handshape]
handshapes = [dominant_handshapes, nondominant_handshapes]
sign = {
'sign': sign_name,
'hands': hand_number,
'handshape': handshapes,
'position': [],
'palmface': '',
'motion': {
'type': '',
'dir': []
}
}
signs.append(sign)
return signs
import openpyxl
# 获取当前格式化-年月日：时分秒
nowTimee = datetime.datetime.now().strftime("%Y%m%d_%H%M%S")
# 获取当前格式化-年月日：时分秒
nowTime = time.strftime('%Y%m%d_%H%M%S', time.localtime(time.time()))
# 本地xlsx测试文件的绝对路径
filepath = r'用户信息数据库表.xlsx'
newfilepath = r'.\新用户信息数据库表.xlsx'
print("--------------------------------------------------------------")
print("一、创建和打印工作表名称")
# 打开excel文件,获取工作簿对象
wb = openpyxl.load_workbook(filepath)
# 从（workbook工作薄）中获取一个(sheet工作表)对象
sheets = wb.sheetnames
print(f"1.打印当前所有工作表：{sheets}\n2.当前工作表类型： {type(sheets)}")
# 创建一个sheet新工作表
mySheet = wb.create_sheet('newSheet')
print(f"3.创建新工作表成功，新工作表名称：{wb.sheetnames[-1]}")
# 读取指定的sheet工作表内容
count = 0
sheet1 = wb.get_sheet_by_name('用户信息数据库表')
sheet2 = wb['newSheet']
print(f"4.当前工作表清单：")
for sheet in wb:
#!/usr/bin/env python
from jinja2 import Environment, FileSystemLoader
from openpyxl import load_workbook
import getpass
from netmiko import ConnectHandler
if __name__ == "__main__":
while True:
file = raw_input("Please enter the Excel file name : ")
un = raw_input("Please enter your Username : "******"Please enter your Password :"******"ATM.j2")
conf_temp = tunnel_temp.render(ip=mlist[1])
with open('config.txt', 'w') as fp:
fp.writelines(conf_temp)
#device = ConnectHandler(device_type='cisco_ios', ip=mlist[0], username=un, password=pwd)
#conf = device.send_config_from_file('config.txt')
raw_input("Press Enter to exit")
break
b_sht_name = 'Sheet1'
d_file_name = "表D-结果数据.xlsx"
d_sht_name = 'Sheet1'
# 更新后的A表\D表名称
a_new_file_name = '更新后的表A.xlsx'
d_new_file_name = "更新后的表D.xlsx"
# A\B\D文件路径
base_dir = os.path.dirname(os.path.abspath(__file__))
a_file_path = os.path.join(base_dir, a_file_name)
b_file_path = os.path.join(base_dir, b_file_name)
d_file_path = os.path.join(base_dir, d_file_name)
# 提取D表身份证、账号数据
d_wb = load_workbook(d_file_path)
d_ws = d_wb[d_sht_name]
d_id_cards_tuple = d_ws['G'][1:]
d_issue_account_tuple = d_ws['B'][1:]
d_id_cards_list = []
d_issue_account_list = []
for i in d_id_cards_tuple:
d_id_cards_list.append(i.value)
for i in d_issue_account_tuple:
d_issue_account_list.append(i.value)
print("D表数据获取完成")
# 提取A表身份证、账号数据
a_table = load_workbook(a_file_path)
a_sheet1 = a_table[a_sht1_name]
a_sheet2 = a_table[a_sht2_name]
def __init__(self, filename):
self.filename = filename
self.wb = load_workbook(self.filename)
self.ws = self.wb.active
django.setup()
from bookstore.models import Genero, Recurso, Usuario, Prestamo
"""
FILOSOFIA
"""
genero = Genero(nombre="Filsofía y pensamiento")
genero.save()
recursos = Recurso.objects.filter(genero=genero)
if len(recursos) > 0:
for r in recursos:
r.delete()
wb2 = load_workbook('Filosofia.xlsx')
ws = wb2.active
for row in ws['A2':'H327']:
recurso = Recurso(titulo=row[0].value,
autor=row[1].value,
anio=str(int(row[3].value)),
editorial=row[4].value,
genero=genero,
codigo=row[7].value)
recurso.save()
for row in ws['A336':'H468']:
recurso = Recurso(titulo=row[0].value,
autor=row[1].value,
anio=str(int(row[3].value)),
def loadWStoEMED(type, wsdata, dbh):
# Get a curstor into the database
cur = dbh.cursor(mdb.cursors.DictCursor)
wb = load_workbook(filename=wsdata[type]['fname'])
ws = wb[wsdata[type]['input_sheetname']]
skip_header = False
if type == 'App' and ws["A1"].value == "AppName":
skip_header = True
if type == 'Trap' and ws["A1"].value == "EventName":
skip_header = True
if type == 'Internal' and ws["A1"].value == "EventName":
skip_header = True
if type == 'App':
sql_preamble = 'INSERT INTO `eventsDynamic` ( AppName, EventName, AlertMessage, \
ThresholdValue, ThresholdUnit, EventSeverity, EventID, EventMessage, \
AlertName, ThresholdName, EventGUID, AppGUID, AlertGUID, ThresholdGUID, \
ThresholdLocalID, Formula \
) VALUES ('
if type == 'Trap':
sql_preamble = 'INSERT INTO `eventsTrap` ( `EventName`, `EventSeverity`, \
`EventMessage`, `EventGUID`, `PowerpackGUID` \
) VALUES ('
if type == 'Internal':
sql_preamble = 'INSERT INTO `eventsInternal` ( `EventName`, `EventSeverity`, \
`EventMessage`, `EventGUID`, `PowerpackGUID` \
) VALUES ('
sql_epilogue = ' );'
rowcount = 1
for row in ws.rows:
sql_values = ''
#print "Row %d" % (rowcount)
for cell in row:
if skip_header == True and rowcount != 1:
#pp.pprint(cell.value)
#pp.pprint(sql_values)
if isinstance(cell.value, basestring):
newvalue = cell.value.replace("'", "")
else:
newvalue = cell.value
#sql_values = sql_values + ' \'' + cell.value + '\','
sql_values = '%s \'%s\',' % (sql_values, newvalue)
#print(cell.value)
rowcount = rowcount + 1
sql_values = sql_values[:-1]
if skip_header == False or rowcount >= 3:
sql_statement = sql_preamble + sql_values + sql_epilogue
#print sql_statement
try:
cur.execute(sql_statement)
except e:
print "Error %d: %s" % (e.args[0], e.args[1])
dbh.rollback
dbh.commit()
cur.close()
print() # blank line, end of headers
form = cgi.FieldStorage()
wb_name = html.escape(form["wb_name"].value)
u_sheet = html.escape(form["u_sheet"].value)
b_sheet = html.escape(form["b_sheet"].value)
#print("Adicione o nome da planilha em a extensao:")
#wb_name = input()
print("\n")
print(wb_name)
print(b_sheet)
print(u_sheet)
time.sleep(1)
try:
workBook = openpyxl.load_workbook(wb_name + str(".xlsx"))
except IOError:
print("arquivo nao encontrado")
sys.exit()
print("Eu encontrei as seguintes abas na planilha")
print("\n")
j = 1
for sheets in workBook.sheetnames:
print("(" + str(j) + "). " + str(sheets))
j += 1
print("\n")
print("Informe o nome da primeira aba")
#b_sheet = aba1
print("\n")
try:
bankSheet = workBook.get_sheet_by_name(b_sheet)
#!/usr/bin/env python
import openpyxl
book = openpyxl.load_workbook('sheets.xlsx')
sheet = book.get_sheet_by_name("March")
sheet.sheet_properties.tabColor = "0072BA"
book.save('sheets3.xlsx')
import openpyxl
import os
import glob
from openpyxl import load_workbook
from openpyxl import Workbook
import time
workbook = load_workbook('updated_info.xlsx')
sheets = workbook.sheetnames
ws1 = workbook[sheets[0]]
names = []
for row in ws1.iter_rows(min_row=1, max_row=None, min_col=2, max_col=2):
for cell in row:
if "Helotes" in cell.value:
cell.offset(0, 1).value = "Helotes"
elif "Shavano Park" in cell.value:
cell.offset(0, 1).value = "Shavano Park"
elif "Hollywood Park" in cell.value:
cell.offset(0, 1).value = "Hollywood Park"
elif "Live Oak" in cell.value:
cell.offset(0, 1).value = "Live Oak"
elif "Converse" in cell.value:
cell.offset(0, 1).value = "Converse"
current_time = time.strftime("%Y_%m_%d%H_%M_%S")
# print(current_time)
workbook.save('addresses_cities{}.xlsx'.format(current_time))
#----------- save python data
save_file = 'check_extraction.sav'
pickle.dump({'device_i':i,'check_DB':check_DB}, open(save_file, 'wb'))
#----------- save to files
if len(check_SQL_last) == 0:
print('0 checks remained to save to the excel file...')
i += 1
continue
print('Saving', len(check_SQL_last), 'extracted failes to the excel file...')
if not os.path.isfile(excel_path):
check_SQL_last.to_excel(excel_path, index = False)
else: # appending to the excell file
book = load_workbook(excel_path)
last_row = book['Sheet1'].max_row
writer = pd.ExcelWriter(excel_path, engine='openpyxl')
# writer = pd.ExcelWriter(excel_path)
# check_SQL_last.to_excel(writer)
# writer.save()
writer.book = book
writer.sheets = dict((ws.title, ws) for ws in book.worksheets)
check_SQL_last.to_excel(writer, startrow = last_row+1,index = False, header = False)
writer.save()
print("""
=========== tabel saved =====
=============================
"""
)
i += 1
def process_one(org_id, thedate=None, test=True):
if thedate is None:
today = datetime.datetime.now()
else:
today = thedate
# 获取上周六
startDate = (today - datetime.timedelta(days=time.localtime().tm_wday + 2))
start_time = datetime.datetime(startDate.year, startDate.month,
startDate.day, 21)
# 获取这周六
endDate = (today - datetime.timedelta(days=time.localtime().tm_wday - 5))
end_time = datetime.datetime(endDate.year, endDate.month, endDate.day, 21)
conn = db.connect_torndb()
mongo = db.connect_mongo()
df, _ = data_code.run(conn, mongo, start_time.strftime("%Y-%m-%d"),
end_time.strftime("%Y-%m-%d"))
df = df[(df.publishDateMerge >= start_time)
& (df.publishDateMerge < end_time)]
nameMap = {}
string = u'''首次披露时间 项目名称 领域 是否国内 一句话简介 完整简介 融资详情
publishDateMerge companyName sector location brief description investmentDetail'''
stringrows = string.split('\n')
index = 0
for column in stringrows[1].split():
nameMap[column] = stringrows[0].split()[index]
index += 1
df = df.rename(columns=nameMap)
title = "烯牛数据融资事件表（%s ~ %s）" % (start_time.strftime("%m-%d"),
end_time.strftime("%m-%d"))
fileName = "funding (%s ~ %s).xlsx" % (start_time.strftime("%m-%d"),
end_time.strftime("%m-%d"))
from openpyxl import load_workbook
import pandas as pd
writer = pd.ExcelWriter(fileName, engin='openpyxl')
book = load_workbook('template/template.xlsx')
ws = book.active
ws['b9'] = u'数据包含了%s至%s一周的国内外融资事件。' % (start_time.strftime("%Y年%m月%d日"),
end_time.strftime("%Y年%m月%d日"))
writer.book = book
df.to_excel(excel_writer=writer,
sheet_name=u"数据明细",
index=0,
columns=stringrows[0].split())
writer.save()
writer.close()
path = '/data/task-201606/spider2/aggregator/funding'
path = sys.path[0]
path = os.path.join(path, fileName)
content = '''Hello,<br /><br />
以下是本周（%s ~ %s）披露的国内外投融资事件列表，请查收！''' % (start_time.strftime("%m-%d"),
end_time.strftime("%m-%d"))
content = '''<!DOCTYPE html><html xmlns="http://www.w3.org/1999/xhtml" xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office"><head> <title></title> <!--[if !mso]><!-- --> <meta http-equiv="X-UA-Compatible" content="IE=edge"> <!--<![endif]--><meta http-equiv="Content-Type" content="text/html; charset=UTF-8"><meta name="viewport" content="width=device-width, initial-scale=1.0"><style type="text/css"> #outlook a { padding: 0; } .ReadMsgBody { width: 100%; } .ExternalClass { width: 100%; } .ExternalClass * { line-height:100%; } body { margin: 0; padding: 0; -webkit-text-size-adjust: 100%; -ms-text-size-adjust: 100%; } table, td { border-collapse:collapse; mso-table-lspace: 0pt; mso-table-rspace: 0pt; } img { border: 0; height: auto; line-height: 100%; outline: none; text-decoration: none; -ms-interpolation-mode: bicubic; } p { display: block; margin: 13px 0; }</style><!--[if !mso]><!--><style type="text/css"> @media only screen and (max-width:480px) { @-ms-viewport { width:320px; } @viewport { width:320px; } }</style><!--<![endif]--><!--[if mso]><xml> <o:OfficeDocumentSettings> <o:AllowPNG/> <o:PixelsPerInch>96</o:PixelsPerInch> </o:OfficeDocumentSettings></xml><![endif]--><!--[if lte mso 11]><style type="text/css"> .outlook-group-fix { width:100% !important; }</style><![endif]--><!--[if !mso]><!--> <link href="https://fonts.googleapis.com/css?family=Ubuntu:300,400,500,700" rel="stylesheet" type="text/css"> <style type="text/css"> @import url(https://fonts.googleapis.com/css?family=Ubuntu:300,400,500,700); </style> <!--<![endif]--><style type="text/css"> @media only screen and (min-width:480px) { .mj-column-per-100 { width:100%!important; } }</style></head><body style="background: #FFFFFF;"> <div class="mj-container" style="background-color:#FFFFFF;"><!--[if mso | IE]> <table role="presentation" border="0" cellpadding="0" cellspacing="0" width="600" align="center" style="width:600px;"> <tr> <td style="line-height:0px;font-size:0px;mso-line-height-rule:exactly;"> <![endif]--><div style="margin:0px auto;max-width:600px;background:以上为本次追踪内容。 如有疑问，欢迎联系我们：） 烯牛数据团队 www.xiniudata.com;"><table role="presentation" cellpadding="0" cellspacing="0" style="font-size:0px;width:100%;background:以上为本次追踪内容。 如有疑问，欢迎联系我们：） 烯牛数据团队 www.xiniudata.com;" align="center" border="0"><tbody><tr><td style="text-align:center;vertical-align:top;direction:ltr;font-size:0px;padding:9px 0px 9px 0px;"><!--[if mso | IE]> <table role="presentation" border="0" cellpadding="0" cellspacing="0"> <tr> <td style="vertical-align:top;width:600px;"> <![endif]--><div class="mj-column-per-100 outlook-group-fix" style="vertical-align:top;display:inline-block;direction:ltr;font-size:13px;text-align:left;width:100%;"><table role="presentation" cellpadding="0" cellspacing="0" width="100%" border="0"><tbody><tr><td style="word-wrap:break-word;font-size:0px;padding:0px 20px 0px 20px;" align="center"><div style="cursor:auto;color:#000000;font-family:Ubuntu, Helvetica, Arial, sans-serif;font-size:11px;line-height:22px;text-align:center;"><p>Hi ，</p><p>附件是本周披露的国内外投融资事件列表，请查收！</p><p></p><p></p><p>如有疑问，欢迎联系我们：）</p><p>烯牛数据团队</p><p><a href="http://sctrack.sc.gg/track/click/eyJtYWlsbGlzdF9pZCI6IDAsICJ0YXNrX2lkIjogIiIsICJlbWFpbF9pZCI6ICIxNTMwMTgzNjU0NDk1XzYwMTE0XzgxNjRfNDczOS5zYy0xMF85XzRfNDAtaW5ib3VuZDAkYXJ0aHVyQHhpbml1ZGF0YS5jb20iLCAic2lnbiI6ICJkNWQ5MjZhM2I3YWM3M2E2NDQwMTMwYzRlZjUzYTg1NiIsICJ1c2VyX2hlYWRlcnMiOiB7fSwgImxhYmVsIjogMCwgImxpbmsiOiAiaHR0cCUzQS8vd3d3Lnhpbml1ZGF0YS5jb20iLCAidXNlcl9pZCI6IDYwMTE0LCAiY2F0ZWdvcnlfaWQiOiAxMTI1OTh9.html" target="_blank">www.xiniudata.com</a></p><p><img src="http://www.xiniudata.com/resources/image/icon-system/verify/ios-normal.jpeg"></p><p></p></div></td></tr></tbody></table></div><!--[if mso | IE]> </td></tr></table> <![endif]--></td></tr></tbody></table></div><!--[if mso | IE]> </td></tr></table> <![endif]--></div></body></html>
'''
users = conn.query(
"select * from org_track_user "
"where active='Y' and orgId=%s", org_id)
for user in users:
if user["email"] is None or user["email"].strip() == "":
continue
if test is True:
if user["email"] not in ["*****@*****.**"]:
continue
logger.info("%s", user["email"])
# email_helper.send_mail("烯牛数据","烯牛数据", "*****@*****.**", user["email"], title, content)
email_helper.send_mail_file("烯牛数据", "烯牛数据", "*****@*****.**",
user["email"], title, content, path)
# pass
conn.close()
mongo.close()
async def get_the_work_book(self, pathToCheck): excelWorkBook = openpyxl.load_workbook(pathToCheck); return excelWorkBook;
def exp_to_excel_form3(doc, iperiod, iregion, mode, stat = None): # mode = 0 по региону или группе больниц mode = 1 - по конкретной больнице
res = calc_sum_form3(doc)
speriod = get_period_namef(iperiod)
sregion = get_region_name(mode,doc,iregion)
name_file = get_name("/static/Form/Form3.xlsx")
wb = openpyxl.load_workbook(name_file)
# print(wb.get_sheet_names())
sheet = wb['stat']
sheet1 = wb['1000']
sheet2 = wb['2000']
sheet3 = wb['3000']
sheet4 = wb['4000']
sheet5 = wb['5000']
sheet6 = wb['5001']
sheet7 = wb['6000']
sheet8 = wb['7000']
# sheet = wb.active
sheet['B1'] = "Период:"
sheet['C1'] = speriod
sheet['B2'] = "Учереждение"
sheet['C2'] = sregion
if mode==0:
sheet['B3'] = "Статистика по отчету"
sheet['B4'] = "Организаций предоставляющих, Всего"
sheet['C4'] = stat.rec_all
sheet['B5'] = "Отобрано в отчет, Всего"
sheet['C5'] = stat.rec_fltr
sheet['B6'] = "Завершено"
sheet['C6'] = stat.rec_complete
sheet['B7'] = "Согласование"
sheet['C7'] = stat.rec_soglas
sheet['B8'] = "Корректировка"
sheet['C8'] = stat.rec_correct
sheet['B9'] = "Редактирование"
sheet['C9'] = stat.rec_edit
# Вне табличные данные
d = res['doc']
# ТаблицыS
# 1000
startrow = 7
i = 0
t = res['tab1000']
for line in t:
sheet1["C" + str(startrow + i)] = line[5] + line[9]
sheet1["D" + str(startrow + i)] = line[6] + line[10]
sheet1["E" + str(startrow + i)] = line[7] + line[11]
sheet1["F" + str(startrow + i)] = line[8] + line[12]
sheet1["G" + str(startrow + i)] = line[5]
sheet1["H" + str(startrow + i)] = line[6]
sheet1["I" + str(startrow + i)] = line[7]
sheet1["J" + str(startrow + i)] = line[8]
sheet1["K" + str(startrow + i)] = line[9]
sheet1["L" + str(startrow + i)] = line[10]
sheet1["M" + str(startrow + i)] = line[11]
sheet1["N" + str(startrow + i)] = line[12]
i += 1
# 2000
startrow = 6
i = 0
t = res['tab2000']
for line in t:
sheet2["C" + str(startrow + i)] = line[1]
if i != 0 and i != 19:
sheet2["D" + str(startrow + i)] = line[2]
sheet2["E" + str(startrow + i)] = line[3]
sheet2["F" + str(startrow + i)] = line[4]
i += 1
# 3000
startrow = 6
i = 0
t = res['tab3000']
for line in t:
sheet3["C" + str(startrow + i)] = line[1]
sheet3["D" + str(startrow + i)] = line[2]
sheet3["E" + str(startrow + i)] = line[3]
sheet3["F" + str(startrow + i)] = line[4]
if i != 13 and i != 14:
sheet3["G" + str(startrow + i)] = line[5]
i += 1
sheet3['F25'] = d[0]
# 4000
startrow = 6
i = 0
t = res['tab4000']
for line in t:
sheet4["D" + str(startrow + i)] = line[1]
sheet4["E" + str(startrow + i)] = line[2]
sheet4["F" + str(startrow + i)] = line[3]
sheet4["H" + str(startrow + i)] = line[4]
sheet4["I" + str(startrow + i)] = line[5]
sheet4["J" + str(startrow + i)] = line[6]
i += 1
sheet4['H19'] = d[1]
sheet4['H20'] = d[2]
sheet4['K21'] = d[3]
sheet4['K22'] = d[4]
# 5000
startrow = 6
i = 0
t = res['tab5000']
for line in t:
sheet5["D" + str(startrow + i)] = line[1]
sheet5["E" + str(startrow + i)] = line[2]
sheet5["F" + str(startrow + i)] = line[3]
sheet5["H" + str(startrow + i)] = line[4]
sheet5["I" + str(startrow + i)] = line[5]
sheet5["J" + str(startrow + i)] = line[6]
sheet5["P" + str(startrow + i)] = line[7]
i += 1
# 5001
startrow = 6
i = 0
t = res['tab5001']
for line in t:
sheet6["D" + str(startrow + i)] = line[1]
sheet6["E" + str(startrow + i)] = line[2]
sheet6["F" + str(startrow + i)] = line[3]
sheet6["H" + str(startrow + i)] = line[4]
sheet6["I" + str(startrow + i)] = line[5]
sheet6["J" + str(startrow + i)] = line[6]
sheet6["P" + str(startrow + i)] = line[7]
i += 1
# 6000
startrow = 6
i = 0
t = res['tab6000']
for line in t:
sheet7["D" + str(startrow + i)] = line[1]
sheet7["E" + str(startrow + i)] = line[2]
sheet7["F" + str(startrow + i)] = line[3]
sheet7["H" + str(startrow + i)] = line[4]
sheet7["I" + str(startrow + i)] = line[5]
sheet7["J" + str(startrow + i)] = line[6]
sheet7["P" + str(startrow + i)] = line[7]
i += 1
# 7000
startrow = 6
i = 0
t = res['tab7000']
for line in t:
sheet8["C" + str(startrow + i)] = line[1]
sheet8["D" + str(startrow + i)] = line[2]
sheet8["E" + str(startrow + i)] = line[3]
sheet8["F" + str(startrow + i)] = line[4]
sheet8["G" + str(startrow + i)] = line[5]
sheet8["H" + str(startrow + i)] = line[6]
i += 1
startrow = 15
i = 0
for i in range(21):
sheet8["G" + str(startrow + i)] = d[i + 5]
sheet['B12'] = "Выведено в системе ЯрВебМед " + str(datetime.now())
sheet['B12'].font = Font(size=5)
# name_file = get_name("\\medicament\\Form\\rep" + str(int(random()*100000000)) + ".xlsx")
name_file = get_name("/medicament/Form/rep" + str(int(random()*100000000)) + ".xlsx")
wb.save(name_file)
return name_file
def write_back(self,file_name,sheet_name,i,result,TestResult): #写回测试数据
wb=load_workbook(file_name)
sheet=wb[sheet_name]
sheet.cell(i,7).value=result
sheet.cell(i,8).value=TestResult
wb.save(file_name)
import os
import shutil
import openpyxl
from openpyxl import Workbook
from openpyxl.utils import cell
from openpyxl.worksheet.worksheet import Worksheet
os.chdir("./resources/challenge/Michael/")
studentInfoPath = "学生资料"
studentRegionPath = "学生地区.xlsx"
# 学生:地区
student_region = dict()
# 提取excel中信息
regionSheet: Worksheet = openpyxl.load_workbook(
filename=studentRegionPath)["地区表"]
# 遍历行数据
for rowData in regionSheet.iter_rows(min_row=2):
# 姓名
name = rowData[0].value
# 地区
region = rowData[1].value
# 添加键值对
student_region[name] = region
studentInfoItems = os.listdir(studentInfoPath)
for studentInfo in studentInfoItems:
'''
:param studentInfo: 文件名（格式：姓名.docx）
'''
name = os.path.splitext(studentInfo)[0]
#!/usr/bin/python3
# spreadsheet_cell_inverter.py - Inverts the rows and columns in a spreadsheet.
# For example, the value at row 5, column 3 will be inverted to row 3, column
# 5. This affects all rows and columns in the spreadsheet.
import openpyxl
# Load workbooks and define sheets
filename = str(input('Enter an Excel file to load: '))
source_wb = openpyxl.load_workbook(filename)
source_sheet = source_wb.active
target_wb = openpyxl.Workbook()
target_sheet = target_wb.active
# Use nested for loops to read the spreadsheet's data into a list of lists
max_row = source_sheet.max_row
max_col = source_sheet.max_column
print('Inverting cells...')
for row in range(1, max_row+1):
for col in range(1, max_col+1):
target_sheet.cell(row=col, column=row).value = \
source_sheet.cell(row=row, column=col).value
# Save the workbook as a copy
target_wb.save('Copy_of_' + filename)
print('Done.')
def update_tel(filename,sheet_name,tel,): #将测试数据更新excel中
wb=load_workbook(filename)
sheet=wb[sheet_name]
sheet.cell(2,1).value=tel
wb.save(filename)
'''
Created on 2017-5-19
@author: sy
'''
#!/usr/bin/python
# -*- coding: UTF-8 -*-
import os
import xlrd # @UnresolvedImport
from openpyxl import Workbook # @UnresolvedImport
from openpyxl import load_workbook
if __name__ == '__main__':
workbook1= load_workbook(filename='C:\Users\sy\Desktop\\result2.xlsx')
sheet1 = workbook1.get_sheet_by_name('Sheet5')
workbook2= load_workbook(filename='C:\Users\sy\Desktop\\coverage.xlsx')
sheet2 = workbook2.get_sheet_by_name('Sheet1')
for index1 in range(3,59):
key=sheet2.cell(row=index1,column=1).value
print "key: ", key
for index2 in range(3,48):
print "cell: ",str(sheet1.cell(row=index2,column=1).value).strip(),str(sheet1.cell(row=index2,column=1).value).strip().lower().startswith(str(key).strip().lower())
if str(sheet1.cell(row=index2,column=1).value).strip().lower().startswith(str(key).strip().lower()):
sheet2.cell(row=index1,column=6).value =sheet1.cell(row=index2,column=4).value
sheet2.cell(row=index1,column=11).value = sheet1.cell(row=index2,column=3).value
print "find"
break
workbook2.save("C:\Users\sy\Desktop\\coverage.xlsx")
import openpyxl
wb = openpyxl.load_workbook('example.xlsx')
sheet = wb.get_sheet_by_name('Sheet1')
print(sheet['A1'].value)
def main(args):
"""
The main function that does all the heavy lifting.
"""
db = BibDatabase()
db.entries = []
wb2 = load_workbook(args.input)
ws = wb2[wb2.sheetnames[0]]
row_count = ws.max_row - 1 # Skipping header
# Iterate over the rows in the Excel-sheet but skip the header.
for row in tqdm(ws.iter_rows(row_offset=1), total=row_count):
number = row[0].value
title = row[2].value
type = row[1].value
if number is None:
continue
if args.xelatex:
url = "http://www.3gpp.org/\-DynaReport/\-{}.htm".format(
number.replace(".", ""))
else:
url = "http://www.3gpp.org/DynaReport/{}.htm".format(
number.replace(".", ""))
entry = {
'ID': "3gpp.{}".format(number),
'ENTRYTYPE': "techreport",
'title': "{{{}}}".format(title),
'type': type,
'author': "3GPP",
'institution': "{3rd Generation Partnership Project (3GPP)}",
'number': number,
'url': url
}
if row[0].hyperlink is not None:
# entry['url'] = row[0].hyperlink.target
page = requests.get(row[0].hyperlink.target)
tree = html.fromstring(page.content)
for release in range(2):
row = tree.xpath(
('//tr[@id="SpecificationReleaseControl1_rpbReleases_i{}'
'_ctl00_specificationsVersionGrid_ctl00__0"]/td/div/a'
).format(release))
if len(row) > 0:
daterow = tree.xpath(
('//tr[@id="SpecificationReleaseControl1_rpbReleases'
'_i{}_ctl00_specificationsVersionGrid_ctl00__0"]/td'
).format(release))
entry['note'] = "Version {}".format(row[1].text.strip())
if daterow[2].text.strip() is not "":
date = daterow[2].text.split('-')
entry['day'] = date[2].strip()
entry['year'] = date[0].strip()
entry['month'] = date[1].strip()
break
db.entries.append(entry)
writer = BibTexWriter()
if args.output is not None:
with open(args.output, 'w') as bibfile:
bibfile.write(writer.write(db))
else:
print(writer.write(db))
ycenter = 130e3
u0d, u0s = -4.0, -0.0
################ DATA ###############
print sep, "Set up the location of observation, Prior Information, and model: ", (
time.time() - start) / 60.0, "minutes", sep
origin = np.array(
[-85.1688,
8.6925]) # origin of top surface of mesh - used as point of rotation
theta = 0.816 # angle of rotation between latitude line and fault trace (42 deg)
if event == 'EQ':
nobs = 37
data_file = dir_path + 'data/Data.xlsx'
wb = pyxl.load_workbook(data_file)
gps_data = wb['GPS_data']
X_gps, Y_gps, Ux_gps, Uy_gps, Uz_gps, err_x, err_y, err_z = GPSlatlon2XY(
gps_data, origin, theta)
stddev = np.concatenate((err_x.reshape(nobs, 1), err_y.reshape(
nobs, 1), err_z.reshape(nobs, 1)),
axis=1).reshape(3 * nobs, )
else:
startday = 0
endday = 60
print "Running afterslip model for ", str(endday), " days"
X_gps, Y_gps, Ux_gps, Uy_gps, Uz_gps \
= importGPSdata(startday, endday, origin, theta, PE = False)
nobs = len(X_gps)
error = 1e-3
errorz = 100 * error
import os, zipfile, openpyxl
zf = zipfile.ZipFile('rogaikopyta.zip', 'r')
zf.extractall('rogaikopyta')
zf.close()
print(os.listdir())
os.chdir(r'D:/MyUniversity/Python ВШЭ/rogaikopyta')
total = []
for files in os.listdir():
exel = openpyxl.load_workbook(files)
sheet = exel.active
total.append([sheet['b2'].value, sheet['d2'].value])
with open(r'D:/MyUniversity/Python ВШЭ/rogaikopyta.txt', 'w',
encoding='utf-8') as outfile:
for el in sorted(total):
print(*el, file=outfile)
# dir1 = os.getcwd()
# print(type(dir1))
# print(os.listdir())
def getExcelDoc():
waiverDoc = openpyxl.load_workbook('people.xlsx')
return waiverDoc
def count_lines():
wb = load_workbook("test.xlsx")
sheets_list = wb.sheetnames
sheet_active = wb[sheets_list[0]]
count = sheet_active.max_row
return count
from openpyxl import load_workbook
from source.common import *
import re
filename = 'database/SpoonFed.xlsx'
wb = load_workbook(filename)
sheet = wb['SpoonFed']
hanzi = ''
for row in range(2, sheet.max_row+1):
sentence = sheet['C' + str(row)].value
for char in re.findall('[\u2E80-\u2FD5\u3400-\u4DBF\u4E00-\u9FCC]',sentence):
if char in hanzi:
continue
hanzi += char
batch = len(hanzi)//60 + 1
level = []
for start in range(0, len(hanzi), batch):
level += [hanzi[start:start+batch]]
for string in level:
printText(string)
