def save_workbook(self, filename: str, completed_codes: dict): self.comment_index = self.first_comment_index cell = self.first_comment_col + str(self.comment_index) val = self.sheet[cell].value vals = self.get_completed_code_values(completed_codes) comment_count = len(vals) change_count = 0 def get_cols() -> dict: col_count = 0 cols = {} for col in self.sheet.iter_cols(min_row=3, max_row=3): for cell in col: if cell.value in vals: cols[cell.value] = ''.join(list(str(cell).split('.')[-1])[:-2]) col_count += 1 if col_count == comment_count: return cols cols = get_cols() while change_count < comment_count: if val in completed_codes.keys(): for key in vals: _cell = cols[key] + str(self.comment_index) self.sheet[_cell].value = 1.0 change_count += 1 self.next_comment() cell = self.first_comment_col + str(self.comment_index) val = self.sheet[cell].value save_workbook(self.work_book, filename)
def gen_outputs (db, group_len): wb = openpyxl.Workbook () masters = list (map (lambda x: x[0], db.get_column_unique_values ('master_name'))) sheet = wb.create_sheet ('groups') gen_groups (masters, group_len, db, sheet) gen_groups_by_mindmap_type (db, group_len, wb) gen_weight_by_mindmap_type (db, wb) save_workbook (wb, '../out/log.xlsx')
def end_serialization(self): filename = self.stream.name if filename == '<stdout>': # If there is no file, dump a CSV representation to stdout out_str = self.csv(self.ws) self.stream.write(out_str) else: # The default stream is opened in text mode, but we need binary self.stream.close() # Just use the openpyxl saving method save_workbook(self.wb, filename)
def save(self, filename): """Save the current workbook under the given `filename`. Use this function instead of using an `ExcelWriter`. .. warning:: When creating your workbook using `optimized_write` set to True, you will only be able to call this function once. Subsequents attempts to modify or save the file will raise an :class:`openpyxl.shared.exc.WorkbookAlreadySaved` exception. """ if self.__optimized_write: save_dump(self, filename) else: save_workbook(self, filename)
def save(self, filename): """Save the current workbook under the given `filename`. Use this function instead of using an `ExcelWriter`. .. warning:: When creating your workbook using `write_only` set to True, you will only be able to call this function once. Subsequents attempts to modify or save the file will raise an :class:`openpyxl.shared.exc.WorkbookAlreadySaved` exception. """ if self.read_only: raise TypeError("""Workbook is read-only""") if self.write_only and not self.worksheets: self.create_sheet() save_workbook(self, filename)
def writer(data, **kwargs): """ Formulas are easy with this one, just do '=F1+F2' """ def handle_formula(f): return "{}".format(f) book = Workbook(**kwargs) sheet = book.worksheets[0] sheet.title = u'Sheet 1' for i in range(len(data)): row = data[i] for j in range(len(row)): item = row[j] if not isinstance( row[j], spreadsheet.Formula) else handle_formula(row[j]) if item is not None: cell = sheet.cell(row=i, column=j) cell.value = "{}".format(item) result = BytesIO() save_workbook( book, result) # Known to cause problems with Python 2.7, at this time. return result.getvalue()
def export_file(self): if self.file_string.get()=="": messagebox.showerror("错误","未指定输出文件") return self.get_old_data() wb=Workbook() ws=wb.worksheets[0] ws.title='已发运数据' for i in range(0,16): ws.cell(row=1,column=i+1).value=xls_header[i] i_row=2 for row in self.rows_lib: for j in range(0,16): ws.cell(row=i_row, column=j+1).value=row[j] i_row=i_row+1 ws1=wb.create_sheet() ws1.title='未发运数据' for i in range(0,19): ws1.cell(row=1,column=i+1).value=xls_header[i] i_row=2 for row in self.rows_lib_new: for j in range(0,19): ws1.cell(row=i_row,column=j+1).value=row[j] i_row=i_row+1 ws2=wb.create_sheet() ws2.title="指定WBS数据" for i in range(0,19): ws2.cell(row=1,column=i+1).value=xls_header[i] if self.b_new: i_row=2 for row in self.rows_lib_new2: for j in range(0,19): ws2.cell(row=i_row,column=j+1).value=row[j] i_row=i_row+1 if excel_xlsx.save_workbook(workbook=wb, filename=self.file_string.get()): messagebox.showinfo("输出","成功输出!")
def save(self, filename): """ shortcut """ if self.__optimized_write: save_dump(self, filename) else: save_workbook(self, filename)
def test_write_empty_workbook(): wb = Workbook() dest_filename = os.path.join(TMPDIR, "empty_book.xlsx") save_workbook(wb, dest_filename) assert os.path.isfile(dest_filename)
def _get_xlsx_stream(self): """ build a xlsx file in memory, based on the test app model.""" workbook = Workbook() # Remove auto-created sheet: name = workbook.get_sheet_names()[0] sheet = workbook.get_sheet_by_name(name) workbook.remove_sheet(sheet) # Add sheet: workbook.create_sheet('myapp.Person') # Add header: workbook['myapp.Person'].cell(row=1, column=1, value='id') workbook['myapp.Person'].cell(row=1, column=2, value='name') workbook['myapp.Person'].cell(row=1, column=3, value='age') # Add content 1: workbook['myapp.Person'].cell(row=2, column=1, value=1) workbook['myapp.Person'].cell(row=2, column=2, value='Person 1') workbook['myapp.Person'].cell(row=2, column=3, value=21) # Add content 2: workbook['myapp.Person'].cell(row=3, column=1, value=2) workbook['myapp.Person'].cell(row=3, column=2, value='Person 2') workbook['myapp.Person'].cell(row=3, column=3, value=22) # Add content 3: workbook['myapp.Person'].cell(row=4, column=1, value=3) workbook['myapp.Person'].cell(row=4, column=2, value='Person 3') workbook['myapp.Person'].cell(row=4, column=3, value=23) # Add sheet: workbook.create_sheet('myapp.Ingredient') # Add header: workbook['myapp.Ingredient'].cell(row=1, column=1, value='id') workbook['myapp.Ingredient'].cell(row=1, column=2, value='name') # Add content 1: workbook['myapp.Ingredient'].cell(row=2, column=1, value=1) workbook['myapp.Ingredient'].cell(row=2, column=2, value='Ingredient 1') # Add content 2: workbook['myapp.Ingredient'].cell(row=3, column=1, value=2) workbook['myapp.Ingredient'].cell(row=3, column=2, value='Ingredient 2') # Add content 3: workbook['myapp.Ingredient'].cell(row=4, column=1, value=3) workbook['myapp.Ingredient'].cell(row=4, column=2, value='Ingredient 3') # Add content 4: workbook['myapp.Ingredient'].cell(row=5, column=1, value=4) workbook['myapp.Ingredient'].cell(row=5, column=2, value='Ingredient 4') # Add sheet: workbook.create_sheet('myapp.Recipe') # Add header: workbook['myapp.Recipe'].cell(row=1, column=1, value='id') workbook['myapp.Recipe'].cell(row=1, column=2, value='name') workbook['myapp.Recipe'].cell(row=1, column=3, value='owner') workbook['myapp.Recipe'].cell(row=1, column=4, value='cooking_time') workbook['myapp.Recipe'].cell(row=1, column=5, value='created_at') workbook['myapp.Recipe'].cell(row=1, column=6, value='rtype') ## Add content 1: workbook['myapp.Recipe'].cell(row=2, column=1, value=1) workbook['myapp.Recipe'].cell(row=2, column=2, value='Recipe 1') workbook['myapp.Recipe'].cell(row=2, column=3, value=1) workbook['myapp.Recipe'].cell(row=2, column=4, value=None) workbook['myapp.Recipe'].cell(row=2, column=5, value='2019-09-10 15:17:34:623000') workbook['myapp.Recipe'].cell(row=2, column=6, value='V') # Add content 2: workbook['myapp.Recipe'].cell(row=3, column=1, value=2) workbook['myapp.Recipe'].cell(row=3, column=2, value='Recipe 2') workbook['myapp.Recipe'].cell(row=3, column=3, value=1) workbook['myapp.Recipe'].cell(row=3, column=4, value=None) workbook['myapp.Recipe'].cell(row=3, column=5, value='2019-09-10 15:17:34:623000') workbook['myapp.Recipe'].cell(row=3, column=6, value='V') # Add sheet: workbook.create_sheet('myapp.Association') # Add header: workbook['myapp.Association'].cell(row=1, column=1, value='id') workbook['myapp.Association'].cell(row=1, column=2, value='recipe') workbook['myapp.Association'].cell(row=1, column=3, value='ingredient') workbook['myapp.Association'].cell(row=1, column=4, value='amount') # Generate in memory stream: in_memory_file = BytesIO() save_workbook(workbook, in_memory_file) return in_memory_file
def test_write_empty_workbook(tmpdir): tmpdir.chdir() wb = Workbook() dest_filename = 'empty_book.xlsx' save_workbook(wb, dest_filename) assert os.path.isfile(dest_filename)
def test_write_empty_workbook(): wb = Workbook() dest_filename = os.path.join(TMPDIR, 'empty_book.xlsx') save_workbook(wb, dest_filename) assert os.path.isfile(dest_filename)
def produce_single_report(video_id): summary = {} results = Single_Report_View.query.filter_by(V_ID = video_id).order_by(Single_Report_View.month_id).all() # did we get reporting data? If not, complain if not results: return render_template('no_report_data.html') #get current month stats from database current_month_stats = Current_Month_Stats.query.all() # unable to retrieve the current reportng month/year, show error page if not current_month_stats: return render_template('error.html',error_message='Unable to retrieve the current reportng month/year, check database') current_month = current_month_stats[0].month_name # March, April ..... current_year = current_month_stats[0].month_year # 2015, 2016 ..... current_month_number = current_month_stats[0].month_number # 1 = Jan, 3 = Mar...... current_month_id = current_month_stats[0].month_id # 14,15,16 primary key for Month_Reports table # is this a single or masterclass video? masterclass = True single = False if results[0].VType == 'SINGLE': masterclass = False single = True # pie chart needs a list of list point = [] audience_profile = [] #dictionary for report header header = {} #dictionary for audience profile in spreadsheet audience_profile_dict = {} if single: #---------------------------------- # single report processing #---------------------------------- # loop through results set to find current month for result_row in results: if result_row.SPeriod == current_month and result_row.SYear == current_year : summary['total_views'] = result_row.total_views summary['total_viewing_duration'] = result_row.Total_Hours summary['video_duration'] = result_row.V_Duration summary['average_view'] = result_row.Avgerage_Minutes #TODO audience_profile_dict['Wirehouse Advisors'] = result_row.Wirehouse_Advisors audience_profile_dict['Independent B/D'] =result_row.Independent_BD audience_profile_dict['RIA'] = result_row.RIA audience_profile_dict['Insurance_CPAs_BankTrust'] = result_row.Insurance_CPAs_BankTrust audience_profile_dict['Investment Consultant'] = result_row.Investment_Consultant audience_profile_dict['Plan Sponsor'] = result_row.Plan_Sponsor audience_profile_dict['Endowment_Foundation'] = result_row.Endowment_Foundation audience_profile_dict['Asset Manager'] = result_row.Asset_Manager audience_profile_dict['Other'] = result_row.Other # URL for Image and Video links url_video = result_row.V_VideoLink url = result_row.V_ImageURL # #get the video caption(s) and Company Name from the database # caption_results = Video_Tag.query.filter_by(video_id = video_id )\ .order_by(Video_Tag.tag_name).all() caption = '' if caption_results: for c in caption_results: if c.tag_type =='People': header['speaker'] = c.tag_name if c.tag_type == 'Companies': header['company_name'] = c.tag_name caption = 'PARTICIPANTS | ' + header['speaker'] + ' of ' + header['company_name'] # # complete the report header # header['published_date'] = result_row.V_DatePublished.strftime('%B %d,%Y') #header['published_date'] = 'PUBLISHED | ' + result_row.V_DatePublished.strftime('%B %d,%Y') header['report_date'] = 'VIEWING REPORT | ' + str(current_month_number) + '/1/' + str(current_year) header['masterclass'] = masterclass header['single'] = single header['report_name'] = result_row.V_Title spreadsheet_name = result_row.V_Title + '_' + str(result_row.SPeriod) + '_' + str(result_row.SYear) + '.xlsm' # # top companies # top_companies =[] top_companies_result = TopCompanyView.query.filter_by(VTCVID = video_id).\ order_by(desc(TopCompanyView.VTCViews)).all() for index in range(len(top_companies_result)): if index == 10: break top_companies.append(top_companies_result[index].VTCCompany) # # get the data for the bar chart # barchart_data = [] barchart_ticks = [] for index in range(len(results)): if results[index].month_id <= current_month_id: # make ticks in the format of 1-JAN, 1-MAR , but look out for 1W if results[index].month_short_name[0].isdigit(): ## is the first character a digit barchart_ticks.append([index, results[index].month_short_name.encode("utf-8")]) else: barchart_ticks.append([index, '1-' + results[index].month_short_name.encode("utf-8")]) barchart_data.append([index, results[index].total_views]) try: warnings.simplefilter("ignore") workbook = load_workbook('spreadsheets/TEMPLATE_Non-Channel_online.xlsm',keep_vba=True) ws = workbook.get_sheet_by_name("Source") warnings.simplefilter("default") ws['A2'] = 'SINGLE' ws['B2'] = str(current_month_number) + '/1/' + str(current_year) ws['C2']= header['report_name'] # report title ws['D2'] = header['published_date'] ws['E2'] = summary['video_duration'] ws['F2'] = header['company_name'] # participants ws['G2'] = header['speaker'] # participants ws['H2'] = url_video # thumbnail image ws['I2'] = url ws['J2'] = summary['total_views'] ws['K2'] = summary['total_viewing_duration'] ws['L2'] = summary['average_view'] #top viewing companies index = 5 rank = 1 for t in top_companies: #top company data D17 to D26 ws['G' + str(index)] = t ws['H' + str(index)] = rank index = index +1 rank = rank + 1 # audience profile ws['E5'] = audience_profile_dict['Wirehouse Advisors'] ws['E6'] = audience_profile_dict['Independent B/D'] ws['E7'] = audience_profile_dict['RIA'] ws['E8'] = audience_profile_dict['Insurance_CPAs_BankTrust'] ws['E9'] = audience_profile_dict['Investment Consultant'] ws['E10'] = audience_profile_dict['Plan Sponsor'] ws['E11'] = audience_profile_dict['Endowment_Foundation'] ws['E12'] = audience_profile_dict['Asset Manager'] ws['E13'] = audience_profile_dict['Other'] #views timeline index = 5 for b in barchart_ticks: ws['A' + str(index)] = b[1] index = index + 1 index =5 for b in barchart_data: ws['B' + str(index)] = b[1] index = index + 1 #workbook.save(spreadsheet_name) save_workbook(workbook,spreadsheet_name) #return render_template('error.html',error_message='Good!') return send_file(os.path.abspath('.') + '\\' + spreadsheet_name, as_attachment=True, attachment_filename=spreadsheet_name) except Exception as e: print str(e) return render_template('error.html', error_message='Unable to complete spreadsheet') return render_template('error.html', error_message='No data for the current reporting period, check database') else: #---------------------------------- # masterclass report processing #---------------------------------- result_row = None # loop through results set to find current month for row in results: if row.SPeriod == current_month and row.SYear == current_year : result_row = row if not result_row: return render_template('error.html', error_message='No data for the current reporting period, check database') # get viewing stats summary['total_viewing_duration'] = 0 summary['total_views'] = 0 summary['video_duration'] = result_row.V_Duration for result_row in results: if result_row.month_id <= current_month_id : summary['total_views'] = summary['total_views'] + result_row.total_views summary['total_viewing_duration'] = result_row.Total_Hours summary['average_view'] = summary['total_viewing_duration'] * 60 / summary['total_views'] audience_profile_dict['Wirehouse Advisors'] = result_row.Wirehouse_Advisors audience_profile_dict['Independent B/D'] = result_row.Independent_BD audience_profile_dict['RIA'] = result_row.RIA audience_profile_dict['Insurance_CPAs_BankTrust'] = result_row.Insurance_CPAs_BankTrust audience_profile_dict['Investment Consultant'] = result_row.Investment_Consultant audience_profile_dict['Plan Sponsor'] = result_row.Plan_Sponsor audience_profile_dict['Endowment_Foundation'] = result_row.Endowment_Foundation audience_profile_dict['Asset Manager'] = result_row.Asset_Manager audience_profile_dict['Other'] = result_row.Other # these guys are NOT sums url_video = results[0].V_VideoLink url = results[0].V_ImageURL summary['video_duration'] = results[0].V_Duration # get the video caption(s) and Company Name from the database caption_results = Video_Tag.query.filter_by(video_id=video_id) \ .order_by(Video_Tag.tag_name).all() caption = '' if caption_results: for c in caption_results: if c.tag_type == 'Companies': caption = caption + c.tag_name + ', ' caption = caption.rstrip(', ') # remove last comma header['published_date'] = results[0].V_DatePublished.strftime('%B %d,%Y') header['report_date'] = 'VIEWING REPORT | ' + str(current_month_number) + '/1/' + str(current_year) header['masterclass'] = masterclass header['single'] = single # chop MASTERCLASS TITLE temp = results[0].V_Title.strip('MASTERCLASS:') dash = temp.find('-', 1) - 1 # back up 1 position in fron of dash header['report_name'] = temp[1: dash] spreadsheet_name = header['report_name'] + '_' + str(results[0].SPeriod) + '_' + str( results[0].SYear) + '.xlsm' # # top companies # top_companies = [] top_companies_result = db.session.query(Masterclass_Top_Companies).filter_by(VTCVID=video_id).\ order_by(desc('VW_TOP_COMPANIES_MASTERCLASS.SUM_VIEWS')).all() # # get the data for the bar chart # for index in range(len(top_companies_result)): if index == 10 : break else: top_companies.append(top_companies_result[index].VTCCompany) # # get the data for the bar chart # barchart_ticks = [] barchart_data = [] running_sum = 0 for index in range(len(results)): if results[index].month_id <= current_month_id: print results[index].month_id, current_month_id # make ticks in the format of 1-JAN, 1-MAR , but look out for 1W if results[index].month_short_name[0].isdigit(): ## is the first character a digit barchart_ticks.append([index, results[index].month_short_name.encode("utf-8")]) else: barchart_ticks.append([index, '1-' + results[index].month_short_name.encode("utf-8")]) running_sum = running_sum + results[index].total_views barchart_data.append([index, running_sum]) # get workbook try: warnings.simplefilter("ignore") workbook = load_workbook('spreadsheets/TEMPLATE_Non-Channel_online.xlsm',keep_vba=True) ws = workbook.get_sheet_by_name("Source") warnings.simplefilter("default") ws['A2'] = 'MASTERCLASS' ws['B2'] = str(current_month_number) + '/1/' + str(current_year) ws['C2'] = header['report_name'] # report title ws['D2'] = header['published_date'] ws['E2'] = summary['video_duration'] #ws['G2'] = header['company_name'] # participants ws['G2'] = caption # participants ws['H2'] = url_video # thumbnail image ws['I2'] = url ws['J2'] = summary['total_views'] ws['K2'] = summary['total_viewing_duration'] ws['L2'] = summary['average_view'] # top viewing companies index = 5 rank = 1 for t in top_companies: # top company data D17 to D26 ws['G' + str(index)] = t ws['H' + str(index)] = rank index = index + 1 rank = rank + 1 # audience profile ws['E5'] = audience_profile_dict['Wirehouse Advisors'] ws['E6'] = audience_profile_dict['Independent B/D'] ws['E7'] = audience_profile_dict['RIA'] ws['E8'] = audience_profile_dict['Insurance_CPAs_BankTrust'] ws['E9'] = audience_profile_dict['Investment Consultant'] ws['E10'] = audience_profile_dict['Plan Sponsor'] ws['E11'] = audience_profile_dict['Endowment_Foundation'] ws['E12'] = audience_profile_dict['Asset Manager'] ws['E13'] = audience_profile_dict['Other'] # views timeline index = 5 for b in barchart_ticks: ws['A' + str(index)] = b[1] index = index + 1 index = 5 for b in barchart_data: ws['B' + str(index)] = b[1] index = index + 1 workbook.save(spreadsheet_name) return send_file(os.path.abspath('.') + '\\' + spreadsheet_name,as_attachment = True, attachment_filename=spreadsheet_name) except Exception as e: print str(e) return render_template('error.html', error_message='Unable to complete spreadsheet')
from openpyxl import load_workbook, Workbook from openpyxl.writer.excel import save_workbook from ExportCandidates import export_candidates from LoadCandidates import load_candidates, load_history, load_comments workbook = load_workbook('AIHUB.xlsx') candidates = load_candidates(workbook) load_history(workbook, candidates) load_comments(workbook, candidates) result_workbook = Workbook() export_candidates(result_workbook, candidates) save_workbook(result_workbook, "output.xlsx")
def __init__(self, name: str): self.name = name if not os.path.exists(name): save_workbook(Workbook(), name) self._wb = xl.load_workbook(name, keep_vba=True)