def test_image(temp_file, browser, screenshot_regression, fixture_file):
browser.driver.set_window_size(1280, 1024)
out_file = temp_file()
xlsx2html(fixture_file("image.xlsx"),
out_file,
locale="en",
parse_formula=True)
browser.visit("file://" + out_file)
screenshot_regression()
def main():
logging.getLogger().setLevel(logging.INFO)
logging.getLogger("boto3").setLevel(logging.CRITICAL)
logging.getLogger("botocore").setLevel(logging.CRITICAL)
logging.info("Gathering metrics")
# benchmarks = gather_benchmarks()
# pickle.dump(benchmarks, open("benchmarks.pkl", "wb"))
benchmarks = pickle.load(open("benchmarks.pkl", "rb"))
print(pprint(benchmarks))
gen_report("benchmark_ai.xlsx", benchmarks)
xlsx2html("benchmark_ai.xlsx", "benchmark_ai.html")
return 0
def convert_xlsx_to_html(xlsx_path):
tmp_path = "/tmp/tmp.html"
xlsx2html.xlsx2html(xlsx_path, tmp_path)
f = open(tmp_path, "rt")
cnt = ""
while 1:
line = f.readline()
if len(line) == 0:
break
cnt += line
# os.system("rm -rf %s" % xlsx_path)
# os.system("rm -rf %s" % tmp_path)
return cnt
def get_table_of_ratio_data(ratio_data: Dict) -> None:
"""Get table with names of cities, hotels in each city, populations in each city and ratio."""
wb = Workbook()
ws = wb.active
logging.info('Making table.')
ws['A1'] = 'Город'
ws['B1'] = 'Кол-во отелей'
ws['C1'] = 'Население'
ws['D1'] = 'Соотношение'
for city_info in zip(ratio_data['cities'], ratio_data['hotels_amounts'],
ratio_data['cities_populations'], ratio_data['ratio']):
city_info = list(city_info)
ws.append(city_info)
fname1 = DATA_PATH / 'ratio_data.xlsx'
wb.save(fname1)
html_table = xlsx2html(fname1)
html_table.seek(0)
html_table = html_table.read()
fname2 = DATA_PATH / 'ratio_data.html'
fname2.write_text(html_table)
set_lang_and_table_style(fname2, "cp1251", "ru", "1", "5", "5",
"border: 1px solid black; font-size: 20.0px; height: 19px")
logging.info('Table is done.')
def get_table_of_prices_by_star(cities: List) -> None:
apartaments_prices = {}
for city in cities:
"""Get tuple with minimal price, average minimal price, average price, average maximal price, maximal price for city"""
apartaments_prices = get_average_prices_for_city(city, by_stars=True)
wb = Workbook()
ws = wb.active
logging.info('Making table.')
ws['A1'] = 'Звездность'
ws['B1'] = 'Мин. цена'
ws['C1'] = 'Макс. цена'
ws['D1'] = 'Средняя цена'
ws['E1'] = 'Нормированный диапазон от средней цены (%)'
for star, information in apartaments_prices.items():
if information:
prices = [information['min_price'], information['max_price'], information['avg_price'], information['range']]
prices.insert(0, star)
else:
prices = [star, 0, 0, 0, 0]
ws.append(prices)
fname1 = DATA_PATH / f'prices_data_by_star_{city}.xlsx'
wb.save(fname1)
html_table = xlsx2html(fname1)
html_table.seek(0)
html_table = html_table.read()
fname2 = DATA_PATH / f'prices_data_by_star_{city}.html'
fname2.write_text(html_table)
set_lang_and_table_style(fname2, "cp1251", "ru", "1", "5", "5",
"border: 1px solid black; font-size: 20.0px; height: 19px")
logging.info(f'Prices table by stars is done for {city}.')
def get_table_of_prices(cities: List) -> None:
apartaments_prices = {}
for city in cities:
"""Get tuple with minimal price, average minimal price, average price, average maximal price, maximal price for city"""
apartaments_prices[city] = get_average_prices_for_city(city)
wb = Workbook()
ws = wb.active
logging.info('Making table.')
ws['A1'] = 'Город'
ws['B1'] = 'Минимальная цена'
ws['C1'] = 'Минимальная средняя цена'
ws['D1'] = 'Средняя цена'
ws['E1'] = 'Максимальная средняя цена'
ws['F1'] = 'Максимальная цена'
for city, value in apartaments_prices.items():
prices = list(value)
prices.insert(0, city)
ws.append(prices)
fname1 = DATA_PATH / 'prices_data.xlsx'
wb.save(fname1)
html_table = xlsx2html(fname1)
html_table.seek(0)
html_table = html_table.read()
fname2 = DATA_PATH / 'prices_data.html'
fname2.write_text(html_table)
set_lang_and_table_style(fname2, "cp1251", "ru", "1", "5", "5",
"border: 1px solid black; font-size: 20.0px; height: 19px")
logging.info('Table is done.')
def get_table_of_facilities(facilities_by_star) -> None:
needed_facilities = ['Бар', 'Бассейн', 'Фитнес-центр', 'Завтрак']
wb = Workbook()
ws = wb.active
logging.info('Making table.')
ws['A1'] = 'Звездность'
ws['B1'] = 'Бар'
ws['C1'] = 'Бассейн'
ws['D1'] = 'Фитнес-центр'
ws['E1'] = 'Завтрак'
ws['F1'] = 'Кол-во отелей'
stars = ['-', 1, 2, 3, 4, 5]
for star in stars:
facilities_ratio = get_needed_facilities_data(facilities_by_star[star], needed_facilities)
facilities_ratio.insert(0, star)
facilities_ratio.append(facilities_by_star[star]['amount'])
ws.append(facilities_ratio)
fname1 = DATA_PATH / 'facilities_ratio.xlsx'
wb.save(fname1)
html_table = xlsx2html(fname1)
html_table.seek(0)
html_table = html_table.read()
fname2 = DATA_PATH / 'facilities_ratio.html'
fname2.write_text(html_table)
set_lang_and_table_style(fname2, "cp1251", "ru", "1", "5", "5",
"border: 1px solid black; font-size: 20.0px; height: 19px")
logging.info(f'Facilities ratio by stars')
def convert_xlsx2html(excel_path, title):
try:
""" wb = pandas.read_excel(excel_path)
return (wb.to_html()) """
out_stream = xlsx2html(excel_path)
out_stream.seek(0)
html = out_stream.read()
html = html.replace(
'<table style="border-collapse: collapse" border="0" cellspacing="0" cellpadding="0"><colgroup>',
'<table style="border-collapse: collapse" border="1px" cellspacing="5px" cellpadding="5px"><colgroup>'
)
html = html.replace('<title>Title</title>',
'<title>{0}</title>'.format(title))
return (html)
except Exception as e:
return str(e)
ws.cell(row=r, column=c, value=cell_data)
#Applying Red and Bold formatting on the basics of date
if datetime.datetime.strptime(
temp_df.loc[i_row][2],
"%d/%m/%Y").date() <= for_red_font:
ws.cell(row=r, column=c).border = border_ready
ws.cell(row=r, column=c).font = red_bold_font
else:
ws.cell(row=r, column=c).border = border_ready
ws.cell(row=r, column=c).font = bold_font
c += 1
r += 1
r += 1
wb.save('{}{}.xlsx'.format(file_storage_diractory, cb))
xlsx2html('{}{}.xlsx'.format(file_storage_diractory, cb),
'{}{}.html'.format(file_storage_diractory, cb)).seek(0)
html_files.append('{}{}.html'.format(file_storage_diractory, cb))
########################
elif report_type == 'With Zero Speed Vehicles':
gps_working_veh_list = full_df[
full_df['IsGpsWorking']]['Vehicle'].to_list()
##################################################################
filt_zero_speed = full_df['Speed'] == 0
full_df['Zero Speed'] = filt_zero_speed
full_df[filt_zero_speed]['Location']
filt_toll = full_df['Location'].str.contains('toll plaza', case=False)
full_df['Toll Plaza'] = filt_toll
full_df['Geofence'] = full_df['Location'].map(lambda x: True
if x.upper() == x else False)
def exceltohtml(workbook_save, new_flabel):
from xlsx2html import xlsx2html
xlsx2html(workbook_save, new_flabel + "_Readout-Summary.html")
def generate_report(filename_prefix, benchmarks):
"""
Generate a report from benchmark data.
Parameters
----------
filename_prefix : string
the output filename minus the extension for the report. Both an xlsx and html file will be
(over-)written for thei filename_prefix.
benchmarks : Benchmarks object
the benchmark data for the report
"""
html_filename = filename_prefix + HTML_EXTENSION
xlsx_filename = filename_prefix + XLSX_EXTENSION
try:
os.remove(html_filename)
except OSError as e:
pass
assert not os.path.exists(html_filename)
try:
os.remove(xlsx_filename)
except OSError as e:
pass
assert not os.path.exists(xlsx_filename)
# Create format references in the workbook.
workbook = xlsxwriter.Workbook(xlsx_filename)
formats = {
'header' : workbook.add_format(
{
'align': 'center',
'bold': True,
'border': 2,
'border_color': '#9ea3aa',
'bg_color': '#dadfe8'
}
),
'categorical' : workbook.add_format(
{
'align': 'center',
'border': 1,
'border_color': '#9ea3aa',
'bg_color': '#f2f6fc'
}
),
'number' : workbook.add_format(
{
'align': 'center',
# 'num_format': '0.00', Doesn't appear to be working.
'border': 1,
'border_color': '#9ea3aa'
}
)
}
worksheet = workbook.add_worksheet()
worksheet.set_column('A:A', 20)
row = 0
row = _add_report(worksheet, formats, row, benchmarks, 'Inference')
row += 3
row = _add_report(worksheet, formats, row, benchmarks, 'Training CV')
row += 3
_add_report(worksheet, formats, row, benchmarks, 'Training NLP')
workbook.close()
xlsx2html(xlsx_filename, html_filename)
def exportToHTML(excel_file,export_file_name):
out_html = xlsx2html(excel_file)
out_html.seek(0)
with open(export_file_name,'w') as file:
file.write(out_html.read())
val=sheet['AK27'].value
sheet.merge_cells('AD27:AK27')
sheet['AD27'].value=val
sheet['AL27'].value=Nds+total
total+=Nds
buf=[]
buf+="Всего наименований, на сумму "
buf+=str(total)
buf+="руб."
inp=''.join(buf)
sheet['B28'].value=inp
print("Итого=",total)
print("словами")
inp=input()
sheet['B29'].value=inp
sheet['M37'].value="Петров А.А."
sheet['AJ37'].value="Иванов Б.Б."
wb.save('blank2.xlsx')
xlsx2html('blank2.xlsx', 'bill.html')
with io.open('bill.html', 'r', encoding='ANSI') as f:
text = f.read()
with io.open('bill.html', 'w', encoding='utf8') as f:
f.write(text)
pdfkit.from_file('bill.html','total.pdf', configuration=config)
html.append('</tr>')
def append_linenr(trow, i):
trow.append('<td style="%(stl)s text-align: right;">%(linenr)d</td>' % {
'stl': COL_AND_LIN_STYLE,
'linenr': i
})
infilename = "F.O. 09- Brazão Interpatium . branco ibiza.xlsx"
#in_stream = xlsx2html(infilename)
#in_stream.seek(0)
#print(in_stream.read())
xlsx2html(infilename,
'output.html',
append_headers=append_headers,
append_linenr=append_linenr,
default_cell_border="1px dotted gray")
"""
<style>
table, th, td {
border: 1px solid !important;
border-color: yellow;
white-space: nowrap;
}
</style>
"""
def Report(self, url):
"""
Function for call node js report method and get xls or xlsx file
"""
args = {} #variable for arguments or body
report_path = url[4:] #cut 4 symbols from url start, work only if it will be rep/
sesid = self.get_cookie('sesid') or self.request.headers.get('Auth') #get session id cookie
log(url, 'args: ' + str(self.request.arguments) +
'; sess: ' + sesid + '; type: 1')
if primaryAuthorization == "1" and sesid == '':
self.set_status(401,None)
self.write('{"message":"No session"}')
return
args = self.request.arguments
for k in args:
args[k] = args.get(k)[0].decode('utf-8')
if args.get('filename') is None:
showError('{"message":"filename is empty"}', self)
return
injson = {'injson':args, 'sess':sesid, 'report_path':report_path}
squery = 'select * from reports.fn_call_report(injson:=%s)'
result = None
try:
result = yield self.db.execute(squery,(extras.Json(injson),))
except Exception as e:
log(url + '_Error',' args: ' +
str(extras.Json(args)) + '; sess: ' +
sesid + '; type: 1; Error:' + str(e))
showError(str(e), self)
return
res = result.fetchone()[0]
data = res.get('outjson')
reqBody = {'template':'..' + res.get('template_path'),'data':dumps(data), 'filename':args.get('filename')}
http_client = AsyncHTTPClient();
req = HTTPRequest(
url=reports_url,
method='POST',
headers={'Content-Type':'application/json'},
body=dumps(reqBody),
connect_timeout=200.0,
request_timeout=200.0
);
try:
req = yield http_client.fetch(req)
except HTTPError as e:
if e.response and e.response.body:
e = e.response.body.decode('utf-8')
log(url + '_Error_NodeJs',' args: ' +
str(extras.Json(args)) + '; sess: ' +
sesid + '; type: 1; Error:' + str(e))
showError(str(e), self)
return
except Exception as err:
system('cd reports && node index.js') # try start reports server
try:
req = yield http_client.fetch(req)
except Exception as err:
showError('No connection to the report server',self)
return
if res.get('ishtml'):
html_report = StringIO()
reportFilePath = './files/' + str(uuid4()) + '.xlsx'
reportFile = open(reportFilePath, 'wb')
reportFile.write(req.buffer.read())
reportFile.close()
html = xlsx2html(reportFilePath, html_report)
html_report.seek(0)
html_report = html_report.read()
self.set_header('Content-Type', 'text/html')
html_report += (
'<script>window.print()</script>' +
'<style type="text/css" media="print">' +
'@page { size: auto; margin: 0mm; } </style>'
)
self.write(html_report)
else:
self.set_header('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
self.set_header('Cache-Control', 'public')
self.set_header('Content-Disposition', 'attachment; filename=' + args.get('filename') + '.xlsx')
self.set_header('Content-Description', 'File Transfer')
self.write(req.body)
self.set_status(200)
self.finish()
#!/usr/bin/env python
# -*- coding: utf-8 -*-
import sys
from xlsx2html import xlsx2html
if __name__ == '__main__':
xlsx2html(sys.argv[1], sys.argv[2])
itogo+=HDC
buf=[]
buf+="Всего наименований, на сумму "
buf+=str(itogo)
buf+="руб."
stroka=''.join(buf)
sheet['B28'].value=stroka
val=sheet['B29'].value
print("Итого=",itogo)
print("прим. Два миллиона пятьсот тысяч рублей 00 копеек -",end='')
stroka=input()
sheet['B29'].value=stroka
val=sheet['B37'].value
print(val,'-',end='')
sheet['M37'].value=input()
val=sheet['Z37'].value
print(val,'-',end='')
sheet['AJ37'].value=input()
wb.save('newtab.xlsx')
xlsx2html('newtab.xlsx', 'page.html')
pdfkit.from_file('page.html','Blank.pdf')