def noteCompany(self, title, company, location, url, placeScraped,
datePosted):
if "path" in self.path and self.path['path'] != "":
columns = [
'Title', 'Company', 'Location', 'URL', 'Site', 'Date Posted',
'Date Scraped', 'Would You Like To Scrape Emails?',
'Emails Scraped/Attempted', 'Date Emails Scraped',
'Would you like to email?', 'Have Emailed', 'Date Emailed',
'Applied To (This one you\'ll have to manager on your own)',
'Applied To Date (Again, you\'ll have to manage this)'
]
job = [(title, company, location, url, placeScraped, datePosted,
datetime.datetime.now().strftime("%x"), None, None, None,
None, None, None, None, None)]
if os.path.exists(self.path['path'] +
'/jobs/scraped_jobs.xlsx') == False:
excel_writer = StyleFrame.ExcelWriter(
self.path['path'] + '/jobs/scraped_jobs.xlsx')
df = pd.DataFrame(job, columns=columns)
sf = StyleFrame(df)
sf.to_excel(excel_writer=excel_writer,
row_to_add_filters=0,
best_fit=('Title', 'Company', 'Location', 'URL',
'Site', 'Date Posted', 'Date Scraped'))
excel_writer.save()
else:
df = pd.read_excel(self.path['path'] +
'/jobs/scraped_jobs.xlsx',
index=False)
# check if job has already been scraped
if len(df.loc[(df.Title == title)
& (df.Company == company)]) == 0:
df2 = pd.DataFrame(job, columns=columns)
dfnew = df.append(df2, ignore_index=True)
excel_writer = StyleFrame.ExcelWriter(
self.path['path'] + '/jobs/scraped_jobs.xlsx')
sf = StyleFrame(dfnew)
sf.to_excel(excel_writer=excel_writer,
row_to_add_filters=0,
best_fit=('Title', 'Company', 'Location',
'URL', 'Site', 'Date Posted',
'Date Scraped'))
excel_writer.save()
else:
print(
"Remember that issue I noted earlier? Well, we finally hit it.\n I have wrote the scraped stuff into a text file that is found within this applications folders, and may be hard to reach..."
)
with open("./files/jobsearch.txt", "a+") as f:
text = title + " " + company + " " + location + " " + url + "\n"
f.write(text)
def noteEmails(self, emails, name, company, title, site, verified):
if "path" in self.path and self.path['path'] != "":
columns = [
"Name", "Email", "Title", "Company", "Site", "Verified",
"Want To Email", "Have Emailed"
]
if os.path.exists(self.path['path'] +
'/emails/scraped_emails.xlsx') == False:
emailsDf = []
for email in emails:
emailsDf.append((name, email, title, company, site,
verified, None, None))
excel_writer = StyleFrame.ExcelWriter(
self.path['path'] + '/emails/scraped_emails.xlsx')
df = pd.DataFrame(emailsDf, columns=columns)
sf = StyleFrame(df)
sf.to_excel(excel_writer=excel_writer,
row_to_add_filters=0,
best_fit=('Name', 'Email', 'Title', 'Company',
'Site', 'Verified'))
excel_writer.save()
else:
df = pd.read_excel(self.path['path'] +
'/emails/scraped_emails.xlsx',
index=False)
emailsDf = []
for email in emails:
# check if email has already been scraped
if len(df.loc[(df.Email == email)]) == 0:
emailsDf.append((name, email, title, company, site,
verified, None, None))
if len(emailsDf):
df2 = pd.DataFrame(emailsDf, columns=columns)
dfnew = df.append(df2, ignore_index=True)
excel_writer = StyleFrame.ExcelWriter(
self.path['path'] + '/emails/scraped_emails.xlsx')
sf = StyleFrame(dfnew)
sf.to_excel(excel_writer=excel_writer,
row_to_add_filters=0,
best_fit=('Name', 'Email', 'Title', 'Company',
'Site', 'Verified'))
excel_writer.save()
else:
print(
"Remember that issue I noted earlier? Well, we finally hit it.\n I have wrote the scraped stuff into a text file that is found within this applications folders, and may be hard to reach..."
)
with open("./files/jobsearch.txt", "a+") as f:
text = title + " " + company + " " + location + " " + url + "\n"
f.write(text)
def setUp(self):
self.ew = StyleFrame.ExcelWriter(TEST_FILENAME)
self.sf = StyleFrame({'a': ['col_a_row_1', 'col_a_row_2', 'col_a_row_3'],
'b': ['col_b_row_1', 'col_b_row_2', 'col_b_row_3']}, self.default_styler_obj)
self.apply_column_style = partial(self.sf.apply_column_style, styler_obj=self.styler_obj_1, width=10)
self.apply_style_by_indexes = partial(self.sf.apply_style_by_indexes, styler_obj=self.styler_obj_1, height=10)
self.apply_headers_style = partial(self.sf.apply_headers_style, styler_obj=self.styler_obj_1)
def get_daily_report_data(self):
file_name = "%s/report/data/lab_job_daily_report/lab_job_daily_report_%s.xlsx"
now = datetime.datetime.now()
now_s = now.strftime("%Y-%m-%d %H-%m-%s")
file_name = file_name % (settings.RUN_DIR, now_s)
sqls = self.get_yesterday_sql()
sql = sqls['lab_job_hc']
data = self.get_data(sql)
if len(data['items']) > 0:
ew = StyleFrame.ExcelWriter(file_name)
self.write2xl(ew, data['titles'], data['items'],
"HC订单明细%s" % sqls['title_parameters'])
for item in data['items']:
logging.critical(item.lab_number)
lbo = LabOrder.objects.get(lab_number=item.lab_number)
lbo.coating_sku = 'HMC'
lbo.coating_name = 'HMC'
lbo.comments_inner += '|System Changed HC to HMC'
lbo.save()
else:
return 'NULL'
return file_name
def get_daily_report_sales_data(self):
file_name = "%s/report/data/finance_daily_report/finance_daily_report_sales_%s.xlsx"
now = datetime.datetime.now()
now_s = now.strftime("%Y-%m-%d")
file_name = file_name % (settings.RUN_DIR, now_s)
ew = StyleFrame.ExcelWriter(file_name)
items = []
items.append('Day')
items.append('总销售额')
items.append('产品销售')
items.append('订单数量')
items.append('眼镜数量')
items.append('总运费')
items.append('加急运费')
items.append('普通运费')
items.append('平均单价')
items.append('平均眼镜')
items.append('车房单')
items.append('车房占比')
items.append('车房金额占比')
items.append('退款')
items.append('下单日退运费')
items.append('重做')
items.append('保险销售')
data = self.__get_daily_report_sales_data()
self.write2xl(ew, items, data['items'], "网站每日销售(美国西部时间)通报-%s" % now_s)
return file_name
def main():
word1 = input("要搜索的职业:")
word = urllib.request.quote(word1)
df_all = pd.DataFrame()
for page in range(1, 20):
print(f'正在读取第{page}页的数据')
df = get_all(word, page)
df_all = df_all.append(df)
df_all.to_excel('猎聘-{}.xlsx'.format(word1),
encoding='utf_8_sig',
index=False)
print(f'已读取总共{page}页的数据')
sf = StyleFrame.read_excel('猎聘-{}.xlsx'.format(word1))
print('data read successful!')
#sf.set_column_width(columns=['公司名','岗位信息'],width=200)
sf.set_column_width_dict(col_width_dict={
"公司名": 20,
"岗位信息": 100,
"招聘岗位": 20,
'申请网址': 30
})
ew = StyleFrame.ExcelWriter('猎聘-{}.xlsx'.format(word1))
sf.to_excel(ew)
ew.save()
print("data updated successful!")
def setUpClass(cls):
cls.ew = StyleFrame.ExcelWriter('test.xlsx')
cls.style_kwargs = dict(bg_color=utils.colors.blue,
bold=True,
font='Impact',
font_color=utils.colors.yellow,
font_size=20,
underline=utils.underline.single)
cls.styler_obj = Styler(**cls.style_kwargs)
cls.openpy_style_obj = cls.styler_obj.create_style()
def styled_excel(file_input, dividends, prices):
path = "C:\\Users\\matth\\Desktop\\style_version.xlsx"
i = True
if (dividends == True):
while i == True:
try:
quarters_back = int(
input(
'How many quarters back of dividends of dividend data would you like?\n'
))
i = False
except:
print("Please enter an integer")
#turn the info and earnings dataframes into spreadsheets
stock_df, ticker_list = formatData(file_input)
sf = StyleFrame(stock_df)
col_list = list(stock_df.columns)
writer = StyleFrame.ExcelWriter(path)
sf.to_excel(excel_writer=writer,
sheet_name='Stock Info',
best_fit=col_list)
earnings_df = get_earnings_dates(stock_df)
sf_earnings = StyleFrame(earnings_df)
col_list_earnings = list(earnings_df.columns)
sf_earnings.to_excel(excel_writer=writer,
sheet_name='Upcoming Earnings Dates',
best_fit=col_list_earnings)
if (dividends == True):
for ticker in ticker_list:
get_dividends(ticker, writer, quarters_back)
if (prices == True):
start_in = input("Start date (YYYY-MM-DD): ")
end_in = input("End date (YYYY-MM-DD): ")
start = (pd.to_datetime(start_in) +
pd.tseries.offsets.BusinessDay(n=0)).strftime("%Y-%m-%d")
end = (pd.to_datetime(end_in) +
pd.tseries.offsets.BusinessDay(n=1)).strftime("%Y-%m-%d")
for ticker in ticker_list:
get_prices(ticker, writer, start, end)
writer.save()
#plotting dividends and historical prices
if (dividends == True):
plot_dividends(ticker_list, path, quarters_back)
if (prices == True):
plot_prices(ticker_list, path, start, end)
def combine_csvs(self, delete_batches: bool = False, to_excel: bool = True):
"""Combines batch csv output files into the final csv file and optionally an excel file
Args:
delete_batches (bool): Whether the batch csv files should be deleted after stitching them to the combined
csv file. Defaults to False (not deleting the batch csv files)
to_excel (bool): Whether the csv file should also be saved as an excel file. Defaults to True (creating the
Excel file)
"""
logger = sip.MapsXmlParser.create_logger(self.log_file_path)
items = os.listdir(self.csv_base_path)
stitched_csvs = []
for name in items:
if name.endswith('_stitched.csv'):
stitched_csvs.append(name)
stitched_csvs.sort()
annotation_tiles = {}
for csv in stitched_csvs:
current_tiles = sip.MapsXmlParser.load_annotations_from_csv(self.base_header, self.csv_base_path / csv)
for key in current_tiles:
annotation_tiles[key] = current_tiles[key]
csv_output_path = self.csv_base_path / (self.project_name + '_fused' + '.csv')
sip.MapsXmlParser.save_annotation_tiles_to_csv(annotation_tiles, self.base_header, csv_output_path)
# Delete all the batch files if the option is set for it
if delete_batches:
for name in items:
os.remove(self.csv_base_path / name)
if to_excel:
logger.info('Saving the annotations csv as an excel file')
data_frame = pd.read_csv(csv_output_path)
excel_output_path = self.csv_base_path / (self.project_name + '_fused' + '.xlsx')
# Create a list of headers whose column should be expanded to fit the content
fitting_headers = list(data_frame.columns.values)
non_fitting_headers = ['img_path', 'surrounding_tile_names', 'surrounding_tile_exists']
for header in non_fitting_headers:
if header in fitting_headers:
fitting_headers.remove(header)
no_wrap_text_style = Styler(wrap_text=False, shrink_to_fit=False)
excel_writer = StyleFrame.ExcelWriter(excel_output_path)
styled_df = StyleFrame(data_frame, styler_obj=no_wrap_text_style)
styled_df.to_excel(excel_writer, 'MapsAnnotations', index=False, columns_and_rows_to_freeze='A1',
best_fit=fitting_headers)
excel_writer.save()
def setUpClass(cls):
cls.ew = StyleFrame.ExcelWriter(TEST_FILENAME)
cls.styler_obj_1 = Styler(
bg_color=utils.colors.blue,
bold=True,
font='Impact',
font_color=utils.colors.yellow,
font_size=20,
underline=utils.underline.single,
horizontal_alignment=utils.horizontal_alignments.left,
vertical_alignment=utils.vertical_alignments.center)
cls.styler_obj_2 = Styler(bg_color=utils.colors.yellow)
cls.openpy_style_obj_1 = cls.styler_obj_1.create_style()
cls.openpy_style_obj_2 = cls.styler_obj_2.create_style()
def convert_uber(filename):
df = pd.read_csv(filename)
df = df.fillna(0)
drivers = set(df['Driver Name'])
out_data = []
for driver in drivers:
driver_calc = {}
driver_data = df[df['Driver Name'] == driver]
name = driver_data['Driver Name'].values[0]
total = driver_data["Fare"].values.sum()
to_parking = total * PARK_PERCENTAGE_UBER
total_payment = driver_data["Total Payment"].values.sum()
to_driver = total_payment - to_parking
driver_calc['name'] = ' '.join(name.split()[:2])
driver_calc['total'] = round(total, 2)
driver_calc['total_payment'] = round(total_payment, 2)
driver_calc['to_driver'] = round(to_driver, 2)
driver_calc['to_parking'] = round(to_parking, 2)
out_data.append(driver_calc)
driver_out_data = {
'name': [d['name'] for d in out_data],
'total': [d['total'] for d in out_data],
'total_payment': [d['total_payment'] for d in out_data],
'to_driver': [d['to_driver'] for d in out_data],
'to_parking': [d['to_parking'] for d in out_data],
}
for key in driver_out_data.keys():
if key != 'name':
driver_out_data[key].append(round(sum(driver_out_data[key]), 2))
else:
driver_out_data[key].append(None)
df_out = pd.DataFrame(driver_out_data)
sf_out = StyleFrame(df_out)
sf_out.set_column_width(columns=sf_out.columns, width=30)
out_name, _ = os.path.splitext(filename)
excel_writer = StyleFrame.ExcelWriter('%s-out.xlsx' % (out_name))
sf_out.to_excel(
excel_writer=excel_writer,
header=True,
columns=['name', 'total', 'total_payment', 'to_driver', 'to_parking'])
excel_writer.save()
def notePerson(self, person):
if "path" in self.path:
df = pd.read_excel(self.path['path'] +
'/emails/scraped_emails.xlsx',
index=False)
df.loc[(df["Email"] == person["Email"], "Have Emailed")] = ["Y"]
excel_writer = StyleFrame.ExcelWriter(
self.path['path'] + '/emails/scraped_emails.xlsx')
sf = StyleFrame(df)
sf.to_excel(excel_writer=excel_writer,
row_to_add_filters=0,
best_fit=('Name', 'Email', 'Title', 'Company', 'Site',
'Verified'))
excel_writer.save()
else:
raise ValueError('Couldn\'t find the path file')
def noteCompany(self, company):
if "path" in self.path:
df = pd.read_excel(self.path['path'] + '/jobs/scraped_jobs.xlsx',
index=False)
df.loc[(df["Company"] == company["Company"],
["Have Emailed", "Date Emailed"
])] = ["Y", datetime.datetime.now().strftime("%x")]
excel_writer = StyleFrame.ExcelWriter(self.path['path'] +
'/jobs/scraped_jobs.xlsx')
sf = StyleFrame(df)
sf.to_excel(excel_writer=excel_writer,
row_to_add_filters=0,
best_fit=('Title', 'Company', 'Location', 'URL',
'Date Posted', 'Date Scraped'))
excel_writer.save()
else:
raise ValueError('Couldn\'t find the path file')
def styledf2excel(outfile_path, df_all, df_pivot, df_client_info, df_ave):
print(outfile_path)
ew = StyleFrame.ExcelWriter(outfile_path)
sf_all = style_df_all(df_all)
# sf_all = datetime_style_df(sf_all)
sf_pivot = style_df(df_pivot)
sf_client = style_df(df_client_info)
sf_ave = style_df(df_ave)
sf_all.to_excel(
ew,
sheet_name="原始流水标记",
columns_and_rows_to_freeze='A2',
row_to_add_filters=0,
)
sf_pivot.to_excel(ew, sheet_name="各月份有效流水统计", index=False)
sf_ave.to_excel(ew, sheet_name="日均存款余额", index=False)
sf_client.to_excel(ew, sheet_name="客户信息", index=False)
ew.save()
def df_to_xls(self, list_dfs, list_sheet_names, folder_path):
# create list of styleframes based on dfs
list_sfs = []
for df in list_dfs:
list_sfs.append(StyleFrame(df))
# apply cell patterns if any
if not list_dfs[0].empty: self.apply_highlights_mut_rows(list_sfs[0], list_dfs[0])
# export
file_path = self.ext_remover(str(folder_path), '.fasta') + '-report.xlsx'
# best_fit factors
StyleFrame.A_FACTOR = 5
StyleFrame.P_FACTOR = 1.2
with StyleFrame.ExcelWriter(file_path) as writer:
for df_idx in range(0, len(list_sfs)):
if not list_dfs[df_idx].empty:
list_sfs[df_idx].to_excel(
writer,
sheet_name=list_sheet_names[df_idx],
index=False,
best_fit=list(list_dfs[df_idx].columns.values)
)
def _handle_export_button_click(self):
utils.create_missing()
path = QtWidgets.QFileDialog.getSaveFileName(
self, "Сохранить",
os.path.join(
utils.get_exports_path(),
utils.get_file_name_for_export(self.company_id, self.period)),
"Файл Excel (*.xlsx)")
self.export_button.setEnabled(False)
if not path:
return
elif os.path.exists(path[0]):
os.remove(path[0])
headers = parsing.table_columns
with StyleFrame.ExcelWriter(path[0]) as writer:
export_data = [[float(cell) for cell in row[1:]]
for row in self.data]
export_string_codes = [int(row[0]) for row in self.data]
df = DataFrame(data=export_data,
columns=headers,
index=export_string_codes)
df.index.name = "Код строки"
sf = StyleFrame(df)
sf.apply_headers_style(Styler(bold=True, font_size=14))
sf.to_excel(writer,
sheet_name='Полная таблица',
index=True,
best_fit=headers)
writer.save()
self.export_button.setEnabled(True)
result = QtWidgets.QMessageBox.question(
self,
"Успех!",
"Данные успешно экспортированы!\nОткрыть файл?",
buttons=QtWidgets.QMessageBox.Yes | QtWidgets.QMessageBox.No)
if result == QtWidgets.QMessageBox.Yes:
command = 'start' if Runtime.is_windows else 'xdg-open'
subprocess.Popen([command, os.path.normpath(path[0])], shell=True)
def as_xlsx_file(self):
df = pd.DataFrame(
data=self.days,
columns=['number', 'start_hour', 'end_hour', 'amount'])
df.loc[len(df)] = [''] * 4 # Empty row.
total_working_days = self.total_working_days()
total_working_days_row = ['סה"כ ימים', total_working_days, '', '']
df.loc[len(df)] = total_working_days_row
total_working_hours = self.total_working_hours()
total_working_hours_row = ['סה"כ שעות', total_working_hours, '', '']
df.loc[len(df)] = total_working_hours_row
total_driving_km_row = ['נסיעות', self.driving_in_km, 'ק"מ', '']
df.loc[len(df)] = total_driving_km_row
user_info = [self.user.first_name, self.user.last_name, '', '']
df.loc[len(df)] = user_info
df.columns = ['יום', 'שעת התחלה', 'שעת סיום', 'מספר גנים']
sf = StyleFrame(df)
sf.set_column_width_dict({
('שעת התחלה', 'שעת סיום'): 16,
('יום', 'מספר גנים'): 13
})
sf.apply_style_by_indexes(indexes_to_style=sf.index[-4:-1],
styler_obj=Styler(bold=True),
cols_to_style='יום')
output = BytesIO()
ew = StyleFrame.ExcelWriter(output)
sf.to_excel(
ew,
right_to_left=True,
row_to_add_filters=0,
).save()
return output
def show_excel(self, style_frames: List[StyleFrame], sheet_names: List[str], parameters: GraphParameters,
graph_results: Callable[[GraphParameters], None],
coefficients: List[Tuple[List[List[float]], List[List[float]]]]=None,
baking_coefficients: List[List[float]]=None):
excel_writer = StyleFrame.ExcelWriter(self.excel_file_path)
for style_frame, sheet_name in zip(style_frames, sheet_names):
style_frame.to_excel(excel_writer=excel_writer, row_to_add_filters=0, sheet_name=sheet_name)
if coefficients is not None:
worksheet = excel_writer.book.create_sheet("Curve Fit")
self.write_curve_fit_coefficients(coefficients, worksheet)
if baking_coefficients is not None:
worksheet = excel_writer.book.create_sheet("Curve Fit")
self.write_coefficients(worksheet, 1, baking_coefficients)
parameters.chart_sheet = excel_writer.book.create_sheet(title="Chart")
parameters.data_sheet = excel_writer.sheets[sheet_names[0]]
graph_results(parameters)
excel_writer.book.create_sheet("Notes")
excel_writer.save()
excel_writer.close()
os.startfile('"{}"'.format(self.excel_file_path.replace("\\", "\\\\")))
df_fact_PCHITAZN = pd.read_sql(fact_PCHITAZN, conn_sib, params={'d': date})
df_fact_PCHITAZN['ДАТА'] = df_fact_PCHITAZN['ДАТА'].astype('str')
conn_eur.close()
conn_sib.close()
df_Four_x_stor_INT = df_Four_x_stor_INT_eur.append(df_Four_x_stor_INT_sib)
# Для PCHITAZN можно этот вариант форматирования массива и записи в xlsx
style = Styler(font_size=10,
horizontal_alignment=utils.horizontal_alignments.right)
columns = df_fact_PCHITAZN.axes[1]
PCHITAZN = StyleFrame(df_fact_PCHITAZN)
excel_writer = StyleFrame.ExcelWriter(path_PCHITAZN)
for s in columns:
PCHITAZN.set_column_width(s, 9 + str(s).__len__())
PCHITAZN.apply_column_style(cols_to_style=columns,
styler_obj=style,
style_header=True)
PCHITAZN.to_excel(excel_writer=excel_writer, index=False)
excel_writer.save()
excel_writer.close()
# Для склееного файла почему-то не катит предыдущий вариант форматирования массива данных, поэтому делаем так ->
# Экспортируем выгруженные данные в xlsx
df_Four_x_stor_INT.to_excel(path_inter, index=False)
def get_daily_report_data(self):
file_name = "%s/report/data/finance_daily_report/finance_daily_report_%s.xlsx"
now = datetime.datetime.now()
now_s = now.strftime("%Y-%m-%d")
file_name = file_name % (settings.RUN_DIR, now_s)
ew = StyleFrame.ExcelWriter(file_name)
sqls = self.get_yesterday_sql()
sqls['title_parameters'] = ""
# sql = sqls['sums']
# logging.debug(sql)
data = self._get_daily_sums()
self.write2xl(ew, data['titles'], data['items'],
"非自有库存镜片收货汇总%s" % sqls['title_parameters'])
sql = sqls['detail']
data = self.get_data(sql)
self.write2xl(ew, data['titles'], data['items'],
"非自有库存镜片收货明细%s" % sqls['title_parameters'])
sql = sqls['sums_wms_in_lens']
data = self.get_data(sql)
self.write2xl(ew, data['titles'], data['items'],
"自有库存镜片入库汇总-%s" % sqls['title_parameters'])
sql = sqls['detail_wms_in_lens']
data = self.get_data(sql)
self.write2xl(ew, data['titles'], data['items'],
"自有库存镜片入库明细-%s" % sqls['title_parameters'])
sql = sqls['sums_wms_out_lens']
data = self.get_data(sql)
self.write2xl(ew, data['titles'], data['items'],
"自有库存镜片出库汇总-%s" % sqls['title_parameters'])
sql = sqls['detail_wms_out_lens']
data = self.get_data(sql)
self.write2xl(ew, data['titles'], data['items'],
"自有库存镜片出库明细-%s" % sqls['title_parameters'])
sql = sqls['sums_wms_in']
data = self.get_data(sql)
self.write2xl(ew, data['titles'], data['items'],
"镜架和附件入库汇总-%s" % sqls['title_parameters'])
sql = sqls['detail_wms_in']
data = self.get_data(sql)
self.write2xl(ew, data['titles'], data['items'],
"镜架和附件入库明细-%s" % sqls['title_parameters'])
sql = sqls['sums_wms_out']
data = self.get_data(sql)
self.write2xl(ew, data['titles'], data['items'],
"镜架和附件出库汇总-%s" % sqls['title_parameters'])
sql = sqls['detail_wms_out']
data = self.get_data(sql)
self.write2xl(ew, data['titles'], data['items'],
"镜架和附件出库明细-%s" % sqls['title_parameters'])
sql = sqls['frame_wms_struct']
data = self.get_data(sql)
self.write2xl(ew, data['titles'], data['items'],
"镜架和附件最新库存量-%s" % sqls['title_parameters'])
sql = sqls['lens_wms_struct']
data = self.get_data(sql)
self.write2xl(ew, data['titles'], data['items'],
"镜片最新库存量-%s" % sqls['title_parameters'])
return file_name
df_c_sib = pd.read_sql(query_c_sib, conn_sib, params={':d': date})
if df_c_sib.shape[0] == 0:
print(
'Прервано! Отсутствуют данные по коэффициенту неготовности в БД Сибири'
)
raise SystemExit
df_c_eur['TARGET_DATE'] = df_c_eur['TARGET_DATE'].astype('str')
df_c_sib['TARGET_DATE'] = df_c_sib['TARGET_DATE'].astype('str')
# Сохраняем отчеты и форматируем их
style = Styler(font_size=10,
horizontal_alignment=utils.horizontal_alignments.right)
columns = df_c_avg.axes[1]
df_c_avg = StyleFrame(df_c_avg)
excel_writer = StyleFrame.ExcelWriter(path_c_avg)
for s in columns:
df_c_avg.set_column_width(s, 7 + str(s).__len__())
df_c_avg.apply_column_style(cols_to_style=columns,
styler_obj=style,
style_header=True)
df_c_avg.to_excel(excel_writer=excel_writer,
sheet_name='Среднее значение коэффициента',
index=False)
excel_writer.save()
excel_writer.close()
columns = df_c_eur.axes[1]
df_c_eur = StyleFrame(df_c_eur)
df_c_sib = StyleFrame(df_c_sib)
excel_writer = StyleFrame.ExcelWriter(path_c)
def setUpClass(cls):
cls.ew = StyleFrame.ExcelWriter(TEST_FILENAME)
cls.styler_obj = Styler(bg_color=utils.colors.blue, bold=True, font='Impact', font_color=utils.colors.yellow,
font_size=20, underline=utils.underline.single)
cls.openpy_style_obj = cls.styler_obj.create_style()
def parse_page (driver, links):
driver.execute_script("window.open('');")
tabs = driver.window_handles
driver.switch_to.window(tabs[1])
datalist = []
for link in links:
proc_info = []
proc_info.append(link)
if 'regNumber=' in link:
proc_info.append(link.split("regNumber=")[1])
driver.get(link)
try:
wait = WebDriverWait(driver, 10)
wait.until(EC.presence_of_all_elements_located((By.TAG_NAME, 'td')))
finally:
print('No exception')
elements = driver.find_elements_by_tag_name('td')
for el in elements:
has_protocols = True
###### получаем URL ПРОТОКОЛА
if ('ПРОТОКОЛ' in el.text):
el.click()
try:
wait = WebDriverWait(driver, 10)
wait.until(EC.presence_of_element_located((By.CLASS_NAME, 'toolTipMenu')))
except TimeoutException:
has_protocols = False
finally:
print('No exception')
if (has_protocols == True):
hover_link = driver.find_elements_by_class_name('toolTipMenu')[0]
hover = ActionChains(driver).move_to_element(hover_link)
try:
hover.perform()
elements = driver.find_elements_by_tag_name('li')
for el in elements:
if ('Печат' in el.text):
a = el.get_attribute('onclick')
url_prorocol = "{}{}".format(main_url, a.split("\'")[1])
print(url_prorocol)
proc_info.append(url_prorocol.split("=")[1])
except WebDriverException:
print ("Hover exception")
break
###### Получаем Общие сведения о закупке
try:
wait = WebDriverWait(driver, 10)
wait.until(EC.presence_of_all_elements_located((By.TAG_NAME, 'td')))
finally:
print('No exception')
td_menu = driver.find_elements_by_tag_name('td')
for tds in td_menu:
if ('ОБЩАЯ' in tds.text):
tds.click()
try:
wait = WebDriverWait(driver, 10)
wait.until(EC.presence_of_all_elements_located((By.TAG_NAME, 'td')))
finally:
print('No exception')
td_inside = driver.find_elements_by_tag_name('td')
previous_element = ''
for tds_in in td_inside:
if ('ИНН' in previous_element):
print('ИНН:' + tds_in.text)
proc_info.append(tds_in.text)
if ('Наименование организации' in previous_element):
print('Наименование организации:' + tds_in.text)
proc_info.append(tds_in.text)
if ('Дата размещения текущей редакции извещения' in previous_element):
print('Дата размещения текущей редакции извещения:' + tds_in.text)
proc_info.append(tds_in.text)
if ('Наименование закупки' in previous_element):
print('Наименование закупки:' + tds_in.text)
proc_info.append(tds_in.text)
previous_element = tds_in.text
break
###### Получаем ДОКУМЕНТЫ
"""
try:
wait = WebDriverWait(driver, 10)
wait.until(EC.presence_of_all_elements_located((By.TAG_NAME, 'td')))
finally:
print('No exception')
td_menu = driver.find_elements_by_tag_name('td')
for tds in td_menu:
if ('ДОКУМЕНТЫ' in tds.text):
tds.click()
try:
wait = WebDriverWait(driver, 10)
wait.until(EC.presence_of_all_elements_located((By.TAG_NAME, 'td')))
finally:
print('No exception')
elem = driver.find_elements_by_xpath("//a[@class='epz_aware']")
temp_str = ''
for epz_aware in elem:
temp_str = temp_str + "<a href=\"" + epz_aware.get_property('href') + "\">"+epz_aware.text+"</a>"
proc_info.append([temp_str])
break
"""
datalist.append(proc_info)
my_list = pd.DataFrame(datalist)
output_filename = 'links.xlsx'
st = Styler(wrap_text=True, shrink_to_fit=True)
StyleFrame(my_list, styler_obj=st)
writer = StyleFrame.ExcelWriter(output_filename)
my_list.to_excel(writer, 'Исходные(текущие)', index=False)
writer.save()
driver.close()
if valid_issue == False:
print ('Issue not assigned to anyone!')
continue
if 'status' in issue.raw['fields'] and not (issue.raw['fields']['status'] is None):
if 'statusCategory' in issue.raw['fields']['status'] and not (issue.raw['fields']['status']['statusCategory'] is None):
if 'name' in issue.raw['fields']['status']['statusCategory'] and not (issue.raw['fields']['status']['statusCategory']['name'] is None):
print ('Current status=' + issue.raw['fields']['status']['statusCategory']['name'])
state = issue.raw['fields']['status']['statusCategory']['name']
if state != 'Done':
new_dic[assignee][2] += 1
if (create_date > start_date and create_date < end_date):
print ('This issue created in this time')
new_dic[assignee][0] += 1
if (update_date > start_date) and (update_date < end_date) and (state == 'Done'):
print ('This issue completed in this time')
new_dic[assignee][1] += 1
df = pandas.DataFrame(new_dic)
df.index = ['Tasks Created', 'Tasks Completed', 'Tasks pending']
print(df.T)
#export output to excel sheet
writer = StyleFrame.ExcelWriter("taskreport.xlsx")
sf=StyleFrame(df.T)
sf.apply_column_style(cols_to_style=df.T.columns, styler_obj=Styler(bg_color=utils.colors.white, bold=True, font=utils.fonts.arial,font_size=8),style_header=True)
sf.apply_headers_style(styler_obj=Styler(bg_color=utils.colors.blue, bold=True, font_size=8, font_color=utils.colors.white,number_format=utils.number_formats.general, protection=False))
sf.to_excel(writer, sheet_name='Sheet1', index=True)
writer.save()
def __init__(self, input_path=None, output_path=None, input_json=None):
self.input_path = input_path
self.input_json = input_json
self.excel_writer = StyleFrame.ExcelWriter(output_path)
self.col_names_to_width = defaultdict(dict)
def convert_gett(filename):
#все данные из экселя
df = pd.read_excel(filename)
#уникальные индексы водителей
drivers = set(df['ID водителя'])
out_data = []
driver_sum = {
'name': 'ИТОГО',
'plan': 0,
'cash': 0,
'driver_salary': 0,
'parking_salary': 0,
'drives': 0,
'tips': 0,
'toll_roads': 0,
'cancels': 0,
}
for driver in drivers:
#данные по водителю
driver_data = df[df['ID водителя'] == driver]
#отменённые заказы
cancels = driver_data['Тип оплаты'].str.contains('Отмененн')
#состоявшиеся заказы
driver_data_good = driver_data[~cancels]
#не состоявшиеся заказы
driver_data_cancels = driver_data[cancels]
data_good_count = driver_data_good.shape[0] #XX
#если прошедших заказов нет (driver_data_good.shape[0] равно 0)
if not data_good_count:
continue
#ФИО
name = driver_data['Имя водителя'].values[0]
#план по состоявшимся заказам
to_drv_total = driver_data['Тариф для водителя всего'].values.sum() #I
#план по состоявшимся заказам без чаевых
to_drv_without_tips = driver_data_good[
'Тариф для водителя без чаевых'].values.sum() #O
#наличка от клиента
from_psgr = driver_data_good['Получено от клиента'].values.sum() #H
#чаевые от клиента
tips = driver_data_good['Чаевые для водителя'].values.sum() #K
#платные дороги
toll_roads_array = driver_data_good[
'Стоимость парковки / платной дороги'].values
toll_roads = sum([x for x in toll_roads_array if x >= 0]) #U
#отменённые
cancels_money = driver_data_cancels[
'Тариф для водителя всего'].values.sum()
#комиссия парка
to_parking = (to_drv_total + toll_roads) * PARK_PERCENTAGE_GETT #PP
#комиссия gett
to_gett = (to_drv_without_tips - toll_roads) * PERCENTAGE_GETT #PG
#выдать водителю
to_drv_perc = to_drv_total - from_psgr - to_parking - to_gett #SS
driver_calc = {}
driver_calc['name'] = ' '.join(name.split()[:2])
driver_calc['plan'] = round(to_drv_total, 2)
driver_calc['cash'] = round(from_psgr, 2)
driver_calc['driver_salary'] = round(to_drv_perc, 2)
driver_calc['parking_salary'] = round(to_parking, 2)
driver_calc['drives'] = driver_data.shape[0]
driver_calc['tips'] = round(tips, 2)
driver_calc['toll_roads'] = round(toll_roads, 2)
driver_calc['cancels'] = round(cancels_money, 2)
out_data.append(driver_calc)
driver_sum['plan'] += driver_calc['plan']
driver_sum['cash'] += driver_calc['cash']
driver_sum['driver_salary'] += driver_calc['driver_salary']
driver_sum['parking_salary'] += driver_calc['parking_salary']
driver_sum['drives'] += driver_calc['drives']
driver_sum['tips'] += driver_calc['tips']
driver_sum['toll_roads'] += driver_calc['toll_roads']
driver_sum['cancels'] += driver_calc['cancels']
out_data.append(driver_sum)
driver_out_data = {
COLLS_NAME['name']: [d['name'] for d in out_data],
COLLS_NAME['plan']: [d['plan'] for d in out_data],
COLLS_NAME['cash']: [d['cash'] for d in out_data],
COLLS_NAME['driver_salary']: [d['driver_salary'] for d in out_data],
COLLS_NAME['parking_salary']: [d['parking_salary'] for d in out_data],
COLLS_NAME['drives']: [d['drives'] for d in out_data],
COLLS_NAME['tips']: [d['tips'] for d in out_data],
COLLS_NAME['toll_roads']: [d['toll_roads'] for d in out_data],
COLLS_NAME['cancels']: [d['cancels'] for d in out_data],
}
"""
for key in driver_out_data.keys():
if key != 'name':
driver_out_data[key].append(round(sum(driver_out_data[key]), 2))
else:
driver_out_data[key].append(None)
"""
df_out = pd.DataFrame(driver_out_data)
sf_out = StyleFrame(df_out)
sf_out.set_column_width(columns=sf_out.columns, width=20)
out_name, ext = os.path.splitext(filename)
excel_writer = StyleFrame.ExcelWriter('%s-out%s' % (out_name, ext))
sf_out.to_excel(excel_writer=excel_writer,
header=True,
columns=[
COLLS_NAME['name'], COLLS_NAME['plan'],
COLLS_NAME['cash'], COLLS_NAME['driver_salary'],
COLLS_NAME['parking_salary'], COLLS_NAME['drives'],
COLLS_NAME['tips'], COLLS_NAME['toll_roads'],
COLLS_NAME['cancels']
])
try:
excel_writer.save()
except Exception as e:
print('Permission denied for output file')
print('Error message:\r\n' + str(e))
finally:
pass
