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)