def _load_sheet(self, sheet):
sheet_name = sheet['sheet_name']
default_cell_style = sheet.get('default_styles', {}).get('cells')
data = defaultdict(list)
for col in sheet['columns']:
col_name = col['col_name']
col_width = col.get('width')
if col_width:
self.col_names_to_width[sheet_name][col_name] = col_width
for cell in col['cells']:
provided_style = cell.get('style') or col.get(
'style') or default_cell_style or {}
unrecognized_styler_kwargs = set(
provided_style.keys()) - styler_kwargs
if unrecognized_styler_kwargs:
raise TypeError(
'Styler dict {} contains unexpected argument: {}.\n'
'Expected arguments: {}'.format(
provided_style, unrecognized_styler_kwargs,
styler_kwargs))
else:
data[col_name].append(
Container(cell['value'],
Styler(**(provided_style)).create_style()))
sf = StyleFrame(pd.DataFrame(data=data))
self._apply_headers_style(sf, sheet)
self._apply_cols_and_rows_dimensions(sf, sheet)
sf.to_excel(excel_writer=self.excel_writer,
sheet_name=sheet_name,
**sheet.get('extra_features', {}))
setattr(self, '{}_sf'.format(sheet_name), sf)
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 test_init_dataframe(self):
self.assertIsInstance(
StyleFrame(pd.DataFrame({
'a': [1, 2, 3],
'b': [1, 2, 3]
})), StyleFrame)
self.assertIsInstance(StyleFrame(pd.DataFrame()), StyleFrame)
def write2xl(self, ew, title_list, data_list, sheet_name):
logging.debug('method: write2xl')
logging.debug(title_list)
df = pd.DataFrame(columns=title_list, data=data_list)
defaults = {'font': utils.fonts.aharoni, 'font_size': 14}
list_sf = StyleFrame(df, styler_obj=Styler(**defaults))
# Style the headers of the table
header_style = Styler(bold=True, font_size=18)
list_sf.apply_headers_style(styler_obj=header_style)
# Change the columns width and the rows height
list_sf.set_column_width(columns=list_sf.columns, width=30)
list_sf.set_row_height(rows=list_sf.row_indexes, height=25)
# list_sf.apply_column_style(cols_to_style=title_list)
list_sf.to_excel(
excel_writer=ew,
sheet_name=sheet_name,
# Add filters in row 0 to each column.
row_to_add_filters=0,
# Freeze the columns before column 'A' (=None) and rows above '2' (=1),
# columns_and_rows_to_freeze='A2'
).save()
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 setUp(self):
self.sf = StyleFrame({'a': [1, 2, 3], 'b': [1, 2, 3]})
self.apply_column_style = partial(self.sf.apply_column_style,
**self.style_kwargs)
self.apply_style_by_indexes = partial(self.sf.apply_style_by_indexes,
**self.style_kwargs)
self.apply_headers_style = partial(self.sf.apply_headers_style,
**self.style_kwargs)
def setUp(self):
self.sf = StyleFrame({'a': [1, 2, 3], 'b': [1, 2, 3]})
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 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 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 test_init_styler_obj(self):
self.sf = StyleFrame({'a': [1, 2, 3], 'b': [1, 2, 3]}, styler_obj=self.styler_obj_1)
self.assertTrue(all(self.sf.at[index, 'a'].style.to_openpyxl_style()._style == self.openpy_style_obj_1
for index in self.sf.index))
sheet = self.export_and_get_default_sheet()
self.assertTrue(all(sheet.cell(row=i, column=j)._style == self.openpy_style_obj_1
for i in range(2, len(self.sf))
for j in range(1, len(self.sf.columns))))
with self.assertRaises(TypeError):
StyleFrame({}, styler_obj=1)
def create_style_frame(self, data_frame: pd.DataFrame):
defaults = {'font_size': 14}
style_frame = StyleFrame(data_frame, styler_obj=Styler(**defaults, shrink_to_fit=False, wrap_text=False))
header_style = Styler(bold=True, font_size=18)
style_frame.set_column_width(columns=style_frame.columns, width=35)
style_frame.apply_headers_style(styler_obj=header_style)
for fbg_name, hex_color in zip(self.fbg_names, HEX_COLORS):
style_frame.apply_column_style(cols_to_style=[col for col in data_frame.columns.values if fbg_name in col],
styler_obj=Styler(bg_color=hex_color))
temperature_headers = [col for col in data_frame.columns.values if "Temperature" in col]
style_frame.apply_column_style(cols_to_style=temperature_headers,
styler_obj=Styler(font_color=utils.colors.red))
return style_frame
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 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 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 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 test_read_excel_no_style(self):
self.export_and_get_default_sheet(save=True)
sf_from_excel = StyleFrame.read_excel(TEST_FILENAME)
# making sure content is the same
self.assertTrue(
all(
list(self.sf[col]) == list(sf_from_excel[col])
for col in self.sf.columns))
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 test_init_styler_obj(self):
self.sf = StyleFrame({
'a': [1, 2, 3],
'b': [1, 2, 3]
},
styler_obj=self.styler_obj)
self.assertTrue(
all(self.sf.ix[index, 'a'].style == self.openpy_style_obj
for index in self.sf.index))
sheet = self.export_and_get_default_sheet()
self.assertTrue(
all(
sheet.cell(row=i, column=j).style == self.openpy_style_obj
for i in range(2, len(self.sf))
for j in range(1, len(self.sf.columns))))
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 test_init_styler_obj(self):
self.sf = StyleFrame({'a': [1, 2, 3], 'b': [1, 2, 3]}, styler_obj=self.styler_obj)
self.assertTrue(all(self.sf.ix[index, 'a'].style == self.openpy_style_obj
for index in self.sf.index))
sheet = self.export_and_get_default_sheet()
self.assertTrue(all(sheet.cell(row=i, column=j).style == self.openpy_style_obj
for i in range(2, len(self.sf))
for j in range(1, len(self.sf.columns))))
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 style_df(df):
# Create StyleFrame object that wrap our DataFrame and assign default style.
defaults = {'font': utils.fonts.aharoni, 'font_size': 11}
sf = StyleFrame(df, styler_obj=Styler(**defaults))
# Style the headers of the table
header_style = Styler(bold=True, font_size=11)
sf.apply_headers_style(styler_obj=header_style)
# Change the columns width and the rows height
sf.set_column_width(columns=sf.columns, width=20)
sf.set_row_height(rows=sf.row_indexes, height=25)
return sf
def get_prices(ticker, writer, start_date, end_date):
df = yf.download(ticker, start=start_date[:10], end=end_date[:10])
i = 0
for val in df['Close']:
df.at[df.index[i], 'Close'] = int(val * 100) / 100
i += 1
df.insert(loc=0,
column='Date',
value=[datetime.strftime(d, "%Y-%m-%d") for d in df.index])
del df['Open']
del df['High']
del df['Adj Close']
del df['Low']
del df['Volume']
sf = StyleFrame(df)
sf.apply_column_style(
cols_to_style='Date',
styler_obj=Styler(number_format=utils.number_formats.date))
col_list = list(df.columns)
sf.to_excel(excel_writer=writer,
sheet_name=ticker + ' Price',
best_fit=col_list)
def get_dividends(ticker, writer, quarters_back):
stock = yf.Ticker(ticker)
div_df = stock.actions
if (len(div_df) == 0 or
(div_df.loc[div_df.index[0], 'Dividends'] == 0 and len(div_df) == 1)):
return
del div_df['Stock Splits']
quarters_back_calc = len(div_df.index) - quarters_back
kept_div_df = div_df.drop(div_df.index[list(range(quarters_back_calc))])
kept_div_df.insert(
loc=0,
column='Ex-Dates',
value=[datetime.strftime(d, "%Y-%m-%d") for d in kept_div_df.index])
sf_div = StyleFrame(kept_div_df)
sf_div.apply_column_style(
cols_to_style='Ex-Dates',
styler_obj=Styler(number_format=utils.number_formats.date))
col_list_div = list(kept_div_df.columns)
sf_div.to_excel(excel_writer=writer,
sheet_name=ticker + ' Dividends',
best_fit=col_list_div)
def test_read_excel_with_style_comments_styler_objects(self):
self.export_and_get_default_sheet(save=True)
sf_from_excel = StyleFrame.read_excel(TEST_FILENAME, read_style=True, read_comments=True)
# making sure content is the same
self.assertTrue(all(list(self.sf[col]) == list(sf_from_excel[col]) for col in self.sf.columns))
rows_in_excel = sf_from_excel.data_df.itertuples()
rows_in_self = self.sf.data_df.itertuples()
# making sure styles are the same
self.assertTrue(all(excel_cell.style == self_cell.style
for row_in_excel, row_in_self in zip(rows_in_excel, rows_in_self)
for excel_cell, self_cell in zip(row_in_excel[1:], row_in_self[1:])))
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 test_read_excel_style(self):
self.apply_headers_style()
self.export_and_get_default_sheet(save=True)
sf_from_excel = StyleFrame.read_excel(TEST_FILENAME, read_style=True)
# making sure content is the same
self.assertTrue(all(list(self.sf[col]) == list(sf_from_excel[col]) for col in self.sf.columns))
rows_in_excel = sf_from_excel.data_df.itertuples()
rows_in_self = self.sf.data_df.itertuples()
# making sure styles are the same
self.assertTrue(all(excel_cell.value == self_cell.value
for row_in_excel, row_in_self in zip(rows_in_excel, rows_in_self)
for excel_cell, self_cell in zip(row_in_excel, row_in_self)))
def test_read_excel_with_string_sheet_name(self):
self.export_and_get_default_sheet(save=True)
sf_from_excel = StyleFrame.read_excel(TEST_FILENAME, read_style=True, sheet_name='Sheet1',
use_openpyxl_styles=True)
# making sure content is the same
self.assertTrue(all(list(self.sf[col]) == list(sf_from_excel[col]) for col in self.sf.columns))
rows_in_excel = sf_from_excel.data_df.itertuples()
rows_in_self = self.sf.data_df.itertuples()
# making sure styles are the same
self.assertTrue(all(self_cell.style == Styler.from_openpyxl_style(excel_cell.style, [])
for row_in_excel, row_in_self in zip(rows_in_excel, rows_in_self)
for excel_cell, self_cell in zip(row_in_excel[1:], row_in_self[1:])))
def _load_sheet(self, sheet):
sheet_name = sheet['sheet_name']
default_cell_style = sheet.get('default_styles', {}).get('cells')
data = defaultdict(list)
for col in sheet['columns']:
col_name = col['col_name']
col_width = col.get('width')
if col_width:
self.col_names_to_width[sheet_name][col_name] = col_width
for cell in col['cells']:
data[col_name].append(
Container(
cell['value'],
Styler(
**(cell.get('style') or col.get('style')
or default_cell_style or {})).create_style()))
sf = StyleFrame(pd.DataFrame(data=data))
self._apply_headers_style(sf, sheet)
self._apply_cols_and_rows_dimensions(sf, sheet)
sf.to_excel(excel_writer=self.excel_writer,
sheet_name=sheet_name,
**sheet.get('extra_features', {}))
setattr(self, '{}_sf'.format(sheet_name), sf)
def run():
client = MongoClient('localhost', 3001)
db = client.meteor.patientinfo
for filename in os.listdir(FORMS_LOCATION):
df = pd.read_excel(os.path.join(FORMS_LOCATION, filename))
station_filename = filename.replace(".xlsx", "")
station = df.columns[0]
df = pd.read_excel(os.path.join(FORMS_LOCATION, filename), skiprows=1)
out_df = generate_data_frame(station, df, db)
StyleFrame(out_df).to_excel(os.path.join(
OUT_LOCATION, "{}_Report.xlsx".format(station_filename)),
index=False).save()
print("Finished", station)
class StyleFrameTest(unittest.TestCase):
@classmethod
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 setUp(self):
self.sf = StyleFrame({"a": [1, 2, 3], "b": [1, 2, 3]})
self.apply_column_style = partial(self.sf.apply_column_style, **self.style_kwargs)
self.apply_style_by_indexes = partial(self.sf.apply_style_by_indexes, **self.style_kwargs)
self.apply_headers_style = partial(self.sf.apply_headers_style, **self.style_kwargs)
def export_and_get_default_sheet(self):
self.sf.to_excel(
excel_writer=self.ew,
right_to_left=True,
columns_to_hide=self.sf.columns[0],
row_to_add_filters=0,
columns_and_rows_to_freeze="A2",
allow_protection=True,
)
return self.ew.book.get_sheet_by_name("Sheet1")
def test_init_styler_obj(self):
self.sf = StyleFrame({"a": [1, 2, 3], "b": [1, 2, 3]}, styler_obj=self.styler_obj)
self.assertTrue(all(self.sf.ix[index, "a"].style == self.openpy_style_obj for index in self.sf.index))
sheet = self.export_and_get_default_sheet()
self.assertTrue(
all(
sheet.cell(row=i, column=j).style == self.openpy_style_obj
for i in range(2, len(self.sf))
for j in range(1, len(self.sf.columns))
)
)
def test_init_dataframe(self):
self.assertIsInstance(StyleFrame(pd.DataFrame({"a": [1, 2, 3], "b": [1, 2, 3]})), StyleFrame)
self.assertIsInstance(StyleFrame(pd.DataFrame()), StyleFrame)
def test_init_styleframe(self):
self.assertIsInstance(StyleFrame(StyleFrame({"a": [1, 2, 3]})), StyleFrame)
def test_len(self):
self.assertEqual(len(self.sf), len(self.sf.data_df))
self.assertEqual(len(self.sf), 3)
def test_apply_column_style(self):
self.apply_column_style(cols_to_style=["a"])
self.assertTrue(
all(
[
self.sf.ix[index, "a"].style == self.openpy_style_obj
and self.sf.ix[index, "b"].style != self.openpy_style_obj
for index in self.sf.index
]
)
)
sheet = self.export_and_get_default_sheet()
# range starts from 2 since we don't want to check the header's style
self.assertTrue(all(sheet.cell(row=i, column=1).style == self.openpy_style_obj for i in range(2, len(self.sf))))
def test_apply_style_by_indexes_single_col(self):
self.apply_style_by_indexes(self.sf[self.sf["a"] == 2], cols_to_style=["a"])
self.assertTrue(
all(
[
self.sf.ix[index, "a"].style == self.openpy_style_obj
for index in self.sf.index
if self.sf.ix[index, "a"] == 2
]
)
)
sheet = self.export_and_get_default_sheet()
self.assertTrue(
all(
sheet.cell(row=i, column=1).style == self.openpy_style_obj
for i in range(1, len(self.sf))
if sheet.cell(row=i, column=1).value == 2
)
)
def test_apply_style_by_indexes_all_cols(self):
self.apply_style_by_indexes(self.sf[self.sf["a"] == 2])
self.assertTrue(
all(
[
self.sf.ix[index, "a"].style == self.openpy_style_obj
for index in self.sf.index
if self.sf.ix[index, "a"] == 2
]
)
)
sheet = self.export_and_get_default_sheet()
self.assertTrue(
all(
sheet.cell(row=i, column=j).style == self.openpy_style_obj
for i in range(1, len(self.sf))
for j in range(1, len(self.sf.columns))
if sheet.cell(row=i, column=1).value == 2
)
)
def test_apply_headers_style(self):
self.apply_headers_style()
self.assertEqual(self.sf.columns[0].style, self.openpy_style_obj)
sheet = self.export_and_get_default_sheet()
self.assertEqual(sheet.cell(row=1, column=1).style, self.openpy_style_obj)
def test_apply_column_style_styler_obj(self):
self.sf.apply_column_style(cols_to_style=["a"], styler_obj=self.styler_obj)
self.assertTrue(
all(
[
self.sf.ix[index, "a"].style == self.openpy_style_obj
and self.sf.ix[index, "b"].style != self.openpy_style_obj
for index in self.sf.index
]
)
)
sheet = self.export_and_get_default_sheet()
# range starts from 2 since we don't want to check the header's style
self.assertTrue(all(sheet.cell(row=i, column=1).style == self.openpy_style_obj for i in range(2, len(self.sf))))
def test_apply_style_by_indexes_single_col_styler_obj(self):
self.sf.apply_style_by_indexes(self.sf[self.sf["a"] == 2], cols_to_style=["a"], styler_obj=self.styler_obj)
self.assertTrue(
all(
self.sf.ix[index, "a"].style == self.openpy_style_obj
for index in self.sf.index
if self.sf.ix[index, "a"] == 2
)
)
sheet = self.export_and_get_default_sheet()
self.assertTrue(
all(
sheet.cell(row=i, column=1).style == self.openpy_style_obj
for i in range(1, len(self.sf))
if sheet.cell(row=i, column=1).value == 2
)
)
def test_apply_style_by_indexes_all_cols_styler_obj(self):
self.sf.apply_style_by_indexes(self.sf[self.sf["a"] == 2], styler_obj=self.styler_obj)
self.assertTrue(
all(
[
self.sf.ix[index, "a"].style == self.openpy_style_obj
for index in self.sf.index
if self.sf.ix[index, "a"] == 2
]
)
)
sheet = self.export_and_get_default_sheet()
self.assertTrue(
all(
sheet.cell(row=i, column=j).style == self.openpy_style_obj
for i in range(1, len(self.sf))
for j in range(1, len(self.sf.columns))
if sheet.cell(row=i, column=1).value == 2
)
)
def test_apply_headers_style_styler_obj(self):
self.sf.apply_headers_style(styler_obj=self.styler_obj)
self.assertEqual(self.sf.columns[0].style, self.openpy_style_obj)
sheet = self.export_and_get_default_sheet()
self.assertEqual(sheet.cell(row=1, column=1).style, self.openpy_style_obj)
def test_set_column_width(self):
self.sf.set_column_width(columns=["a"], width=20)
self.assertEqual(self.sf._columns_width["a"], 20)
sheet = self.export_and_get_default_sheet()
self.assertEqual(sheet.column_dimensions["A"].width, 20)
def test_set_column_width_dict(self):
width_dict = {"a": 20, "b": 30}
self.sf.set_column_width_dict(width_dict)
self.assertEqual(self.sf._columns_width, width_dict)
sheet = self.export_and_get_default_sheet()
self.assertTrue(all(sheet.column_dimensions[col.upper()].width == width_dict[col] for col in width_dict))
def test_set_row_height(self):
self.sf.set_row_height(rows=[1], height=20)
self.assertEqual(self.sf._rows_height[1], 20)
sheet = self.export_and_get_default_sheet()
self.assertEqual(sheet.row_dimensions[1].height, 20)
def test_set_row_height_dict(self):
height_dict = {1: 20, 2: 30}
self.sf.set_row_height_dict(height_dict)
self.assertEqual(self.sf._rows_height, height_dict)
sheet = self.export_and_get_default_sheet()
self.assertTrue(all(sheet.row_dimensions[row].height == height_dict[row] for row in height_dict))
def test_rename(self):
original_columns_name = list(self.sf.columns)
names_dict = {"a": "A", "b": "B"}
# testing rename with inplace = True
self.sf.rename(columns=names_dict, inplace=True)
self.assertTrue(all(new_col_name in self.sf.columns for new_col_name in names_dict.values()))
new_columns_name = list(self.sf.columns)
# check that the columns order did not change after renaming
self.assertTrue(
all(
original_columns_name.index(old_col_name) == new_columns_name.index(new_col_name)
for old_col_name, new_col_name in names_dict.items()
)
)
# using the old name should raise a KeyError
with self.assertRaises(KeyError):
# noinspection PyStatementEffect
self.sf["a"]
# testing rename with inplace = False
names_dict = {v: k for k, v in names_dict.items()}
new_sf = self.sf.rename(columns=names_dict, inplace=False)
self.assertTrue(all(new_col_name in new_sf.columns for new_col_name in names_dict.values()))
# using the old name should raise a KeyError
with self.assertRaises(KeyError):
# noinspection PyStatementEffect
new_sf["A"]
def setUp(self):
self.sf = StyleFrame({"a": [1, 2, 3], "b": [1, 2, 3]})
self.apply_column_style = partial(self.sf.apply_column_style, **self.style_kwargs)
self.apply_style_by_indexes = partial(self.sf.apply_style_by_indexes, **self.style_kwargs)
self.apply_headers_style = partial(self.sf.apply_headers_style, **self.style_kwargs)
