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 `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')
def save(self, filename):
""" shortcut """
if self.__optimized_write:
save_dump(self, filename)
else:
save_workbook(self, filename)
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)
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)
