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 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 save_adjust_xlsx(df, file='test.xlsx', width=60):
'''save and re-adjust excel format'''
df = df.reset_index(drop='index').fillna('')
StyleFrame.A_FACTOR = 5
StyleFrame.P_FACTOR = 1.2
sf = StyleFrame(df,
Styler(wrap_text=False, shrink_to_fit=True, font_size=12))
if ('add_index' in df.columns.tolist()):
sf.apply_style_by_indexes(
indexes_to_style=sf[sf['add_index'] == 'new'],
styler_obj=Styler(bg_color='yellow'),
overwrite_default_style=False)
sf.apply_column_style(cols_to_style=['当事人', '诉讼代理人', '地址'],
width=width,
styler_obj=Styler(wrap_text=False,
shrink_to_fit=True))
else:
sf.set_column_width_dict(
col_width_dict={('当事人', '诉讼代理人', '地址'): width})
if len(df):
sf.to_excel(file,
best_fit=sf.data_df.columns.difference(
['当事人', '诉讼代理人', '地址']).tolist()).save()
else:
sf.to_excel(file).save()
print_log('>>> 保存文件 => 文件名 \'%s\' => 数据保存成功...' % (file))
return df
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 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 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 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 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 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 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 _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 _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)
("Picture"):
25.5,
("Stars", "Number of Reviews", "Average Cost", "Japanese Name", "English Name"):
20,
("URL"):
65.5
})
# 設定行高
all_rows = sf.row_indexes
sf.set_row_height_dict(row_height_dict={all_rows[1:]: 120})
# 將資料存入Excel檔
sf.to_excel(ExcelDataLocation,
sheet_name='Sheet1',
right_to_left=False,
columns_and_rows_to_freeze='A1',
row_to_add_filters=0).save()
print('Data Saved to ' + ExcelDataLocation)
col = 0
wb = load_workbook(ExcelDataLocation)
ws = wb.worksheets[0]
#圖片依取得時間排序
searchedfiles = sorted(glob.glob(pictureFolder + '*.jpg'),
key=os.path.getmtime) #圖片依取得時間排序
# 將圖片匯入Excel檔
for fn in searchedfiles:
import pandas as pd
from StyleFrame import StyleFrame, Styler
# df1 = pd.read_excel('D:/DSR/BIDW_Daily_load_status_04_14_2020_SO.xlsx',index_col=None)
df2 = pd.read_excel('D:/DSR/DRR.xlsx', index_col=False, header=None)
# df3 = pd.read_excel('D:/DSR/Daily_load_status _4_14_2020_INTLAR.xlsx',index_col=None)
df4 = pd.read_excel('D:/DSR/IURR.xlsx', index_col=False, header=None)
# df5 = pd.read_excel('D:/DSR/Daily_Status_Report_04_14_2020_AR.xlsx',index_col=None)
final = pd.concat([df2, df4])
# final.to_excel('D:/DSR/final_dsr.xlsx',index=False)
sf = StyleFrame(final)
sf.apply_style_by_indexes(sf[sf['Status'] == 'Running on track'],
cols_to_style='Status',
styler_obj=Styler(bg_color='green'))
sf.apply_style_by_indexes(sf[sf['Status'] == 'Delayed'],
cols_to_style='Status',
styler_obj=Styler(bg_color='red'))
sf.apply_style_by_indexes(sf[sf['Status'] == 'Completed'],
cols_to_style='Status',
styler_obj=Styler(bg_color='green'))
sf.apply_style_by_indexes(sf[sf['Status'] == 'On Hold'],
cols_to_style='Status',
styler_obj=Styler(bg_color='purple'))
sf.to_excel('D:/DSR/final_dsr.xlsx').save()
sf.apply_headers_style()
def get_sheet_table(self):
"""
需要处理的时将excel的内容读出来和修改内容并保存
:return:
"""
# 读取一个excel的文本文件（当前默认时读一个文件的一个sheet页）
ex = pd.read_excel(unicode(self.filepath, "utf8"))
# 用pd格式化
df = pd.DataFrame(ex)
# 迭代器遍历sheet页里的内容
for row in df.itertuples(name="RowData"):
# 实例化一个数据模型对象
ds = DataStructer()
# 用读到的excel行数据来填充这个对象
self.data_mapping(row, ds)
# 通过这个对象的属性值，来发起一次request请求，在请求的过程把结果及校验的数据处理完后，
self.send_req(ds)
print ds.__dict__
# 接口发起后的结果写入到excel对应行的对应列中
# 执行修改操作
df.update(
pd.Series(ds.result, name="test_result", index=[row.Index]))
df.update(pd.Series(ds.notes, name="test_notes",
index=[row.Index]))
# 执行数据更新后的保存操作：这里有个问题就是源文件覆盖保存，会没有特定的样式，需要再升级一下
# df.to_excel(unicode(self.filepath, "utf8"))
# 创建StyleFrame对象，该对象包装我们的DataFrame并分配默认样式。
defaults = {'font': utils.fonts.aharoni, 'font_size': 12}
sf = StyleFrame(df, styler_obj=Styler(**defaults))
"""
# Style the headers of the table
header_style = Styler(bold=True, font_size=14)
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=90)
"""
sf.set_column_width_dict(
col_width_dict={
('cid', 'type', 'method', 'result'):
7,
('project', 'module', 'function', 'desc', 'protocol', 'assertion'):
13,
('url', ):
20,
('header', 'cookie', 'entity', 'assertion', 'notes'):
30
})
row_num = sf.row_indexes
sf.set_row_height_dict(row_height_dict={
row_num[0]: 28,
row_num[1:]: 90
})
sf.apply_headers_style(styler_obj=Styler(bg_color=utils.colors.grey))
sf.to_excel(unicode(self.filepath, "utf8")).save()
print 30 * "*"
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']
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"]
class StyleFrameTest(unittest.TestCase):
@classmethod
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 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)
@classmethod
def tearDownClass(cls):
try:
os.remove(TEST_FILENAME)
except OSError as ex:
print(ex)
def export_and_get_default_sheet(self, save=False):
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)
if save:
self.ew.save()
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_1)
self.assertTrue(
all(self.sf.loc[index, 'a'].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 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)
with self.assertRaises(TypeError):
StyleFrame({}, styler_obj=1)
def test_len(self):
self.assertEqual(len(self.sf), len(self.sf.data_df))
self.assertEqual(len(self.sf), 3)
def test_str(self):
self.assertEqual(str(self.sf), str(self.sf.data_df))
def test__get_item__(self):
self.assertEqual(self.sf['a'].tolist(), self.sf.data_df['a'].tolist())
self.assertTrue(self.sf.data_df[['a',
'b']].equals(self.sf[['a',
'b']].data_df))
def test__getattr__(self):
self.assertEqual(self.sf.fillna, self.sf.data_df.fillna)
with self.assertRaises(AttributeError):
self.sf.non_exisiting_method()
def test_apply_column_style(self):
# testing some edge cases
with self.assertRaises(TypeError):
self.sf.apply_column_style(cols_to_style='a', styler_obj=0)
with self.assertRaises(KeyError):
self.sf.apply_column_style(cols_to_style='non_existing_col',
styler_obj=Styler())
# actual tests
self.apply_column_style(cols_to_style=['a'])
self.assertTrue(
all([
self.sf.loc[index, 'a'].style == self.openpy_style_obj_1
and self.sf.loc[index, 'b'].style != self.openpy_style_obj_1
for index in self.sf.index
]))
sheet = self.export_and_get_default_sheet()
self.assertEqual(sheet.column_dimensions['A'].width, 10)
# 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_1
for i in range(2, len(self.sf))))
def test_apply_style_by_indexes_single_col(self):
with self.assertRaises(TypeError):
self.sf.apply_style_by_indexes(indexes_to_style=0, styler_obj=0)
self.apply_style_by_indexes(self.sf[self.sf['a'] == 2],
cols_to_style=['a'])
self.assertTrue(
all(self.sf.loc[index, 'a'].style == self.openpy_style_obj_1
for index in self.sf.index if self.sf.loc[index, 'a'] == 2))
sheet = self.export_and_get_default_sheet()
self.assertTrue(
all(
sheet.cell(row=i, column=1).style == self.openpy_style_obj_1
for i in range(1, len(self.sf))
if sheet.cell(row=i, column=1).value == 2))
self.assertEqual(sheet.row_dimensions[3].height, 10)
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.loc[index, 'a'].style == self.openpy_style_obj_1
for index in self.sf.index if self.sf.loc[index, 'a'] == 2))
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(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_style_by_indexes_with_single_index(self):
self.apply_style_by_indexes(self.sf.index[0])
self.assertTrue(
all(self.sf.iloc[0, self.sf.columns.get_loc(col)].style ==
self.openpy_style_obj_1 for col in self.sf.columns))
sheet = self.export_and_get_default_sheet()
# row=2 since sheet start from row 1 and the headers are row 1
self.assertTrue(
all(
sheet.cell(row=2, column=col).style == self.openpy_style_obj_1
for col in range(1, len(self.sf.columns))))
def test_apply_style_by_indexes_all_cols_with_multiple_indexes(self):
self.apply_style_by_indexes([1, 2])
self.assertTrue(
all(self.sf.iloc[index, self.sf.columns.get_loc(col)].style ==
self.openpy_style_obj_1 for index in [1, 2]
for col in self.sf.columns))
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 [3, 4] # sheet start from row 1 and headers are row 1
for j in range(1, len(self.sf.columns))))
def test_apply_headers_style(self):
self.apply_headers_style()
self.assertEqual(self.sf.columns[0].style, self.openpy_style_obj_1)
sheet = self.export_and_get_default_sheet()
self.assertEqual(
sheet.cell(row=1, column=1).style, self.openpy_style_obj_1)
def test_set_column_width(self):
# testing some edge cases
with self.assertRaises(TypeError):
self.sf.set_column_width(columns='a', width='a')
with self.assertRaises(ValueError):
self.sf.set_column_width(columns='a', width=-1)
# actual tests
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):
with self.assertRaises(TypeError):
self.sf.set_column_width_dict(None)
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):
# testing some edge cases
with self.assertRaises(TypeError):
self.sf.set_row_height(rows=[1], height='a')
with self.assertRaises(ValueError):
self.sf.set_row_height(rows=[1], height=-1)
with self.assertRaises(ValueError):
self.sf.set_row_height(rows=['a'], height=-1)
# actual tests
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):
with self.assertRaises(TypeError):
self.sf.set_row_height_dict(None)
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):
with self.assertRaises(TypeError):
self.sf.rename(columns=None)
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 test_read_excel_no_style(self):
self.apply_headers_style()
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 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_row_indexes(self):
self.assertEqual(self.sf.row_indexes, (1, 2, 3, 4))
def test_style_alternate_rows(self):
styles = [self.styler_obj_1, self.styler_obj_2]
openpy_styles = [self.openpy_style_obj_1, self.openpy_style_obj_2]
self.sf.style_alternate_rows(styles)
self.assertTrue(
all(self.sf.iloc[index.value,
0].style == styles[index %
len(styles)].create_style()
for index in self.sf.index))
sheet = self.export_and_get_default_sheet()
# sheet start from row 1 and headers are row 1, so need to add 2 when iterating
self.assertTrue(
all(
sheet.cell(row=i.value +
2, column=1).style == openpy_styles[i % len(styles)]
for i in self.sf.index))
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)
# Создаем шаблоны стилей
border = Border(left=Side(border_style='thin', color='FF000000'),
right=Side(border_style='thin', color='FF000000'),
top=Side(border_style='thin', color='FF000000'),
bottom=Side(border_style='thin', color='FF000000'))
align_head = Alignment(horizontal='center',
vertical='center',
def group_by_color(
filepath,
row_height,
column_width,
include_sources,
output_name,
):
from StyleFrame import StyleFrame, Styler, utils
import numpy as np
import pandas as pd
if not filepath.endswith(".xlsx"):
filepath += ".xlsx"
sf = StyleFrame.read_excel(filepath, read_style=True)
color_groupings = {}
for c_index, c in enumerate(sf.columns):
if "Cues" in c.value:
for value in sf[c]:
if type(value.value) is not str and np.isnan(value.value):
continue
if value.style.bg_color + "_value" not in color_groupings:
color_groupings[value.style.bg_color + "_value"] = []
if include_sources and (
value.style.bg_color + "_source" not in color_groupings
):
color_groupings[value.style.bg_color + "_source"] = []
color_groupings[value.style.bg_color + "_value"].append(
value.value
)
if include_sources:
color_groupings[value.style.bg_color + "_source"].append(
sf.iloc[0,c_index - 1].value
)
# colors of our stuff
for k,length in zip(color_groupings.keys(), map(len, color_groupings.values())):
print( "color", k + ":", length, "entries")
# pad data so it is all the same size
maxlen = max(map(len, color_groupings.values()))
for k,v in color_groupings.items():
color_groupings[k] = v + ['']*(maxlen - len(v))
print("old length:", len(v), "|| new length:", len(color_groupings[k]))
# add padded data to df, print 'er out
new_df = pd.DataFrame(color_groupings)
new_df
defaults = {'font': utils.fonts.calibri, 'font_size': 9}
new_sf = StyleFrame(new_df, styler_obj=Styler(**defaults))
for c in new_sf.columns:
# color to use
color = c.value.strip("_source").strip("_value")
# apply style to ALL values (incl headers)
new_sf.apply_column_style(
cols_to_style=[c.value],
styler_obj=Styler(bold=True, font_size=10, bg_color=color,
font_color=utils.colors.white),
style_header=True,
)
# revert style for non-headers
new_sf.apply_column_style(
cols_to_style=[c.value],
styler_obj=Styler(**defaults),
style_header=False
)
# row height
all_rows = new_sf.row_indexes
new_sf.set_row_height_dict(
row_height_dict={
all_rows[0]: 24,
all_rows[1:]: row_height
}
)
# col width
all_cols = tuple(map(lambda x: x.value, new_sf.columns))
new_sf.set_column_width_dict(
col_width_dict={
all_cols: column_width
}
)
# save to excel file
if not output_name.endswith(".xlsx"):
output_name += ".xlsx"
new_sf.to_excel(output_name).save()
return 0
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()
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)
for s in columns:
df_c_eur.set_column_width(s, 12 + 0.5 * str(s).__len__())
df_c_sib.set_column_width(s, 12 + 0.5 * str(s).__len__())
df_c_eur.apply_column_style(cols_to_style=columns,
styler_obj=style,
style_header=True)
df_c_sib.apply_column_style(cols_to_style=columns,
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
class StyleFrameTest(unittest.TestCase):
@classmethod
def setUpClass(cls):
cls.default_styler_obj = Styler(wrap_text=False)
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,
comment_text='styler_obj_1 comment')
cls.styler_obj_2 = Styler(bg_color=utils.colors.yellow, comment_text='styler_obj_2 comment')
cls.openpy_style_obj_1 = cls.styler_obj_1.to_openpyxl_style()._style
cls.openpy_style_obj_2 = cls.styler_obj_2.to_openpyxl_style()._style
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)
@classmethod
def tearDownClass(cls):
try:
os.remove(TEST_FILENAME)
except OSError as ex:
print(ex)
def export_and_get_default_sheet(self, save=False):
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)
if save:
self.ew.save()
return self.ew.sheets['Sheet1']
def get_cf_rules(self, sheet):
conditional_formatting = sheet.conditional_formatting
try:
return conditional_formatting.cf_rules
except AttributeError:
return conditional_formatting
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 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)
with self.assertRaises(TypeError):
StyleFrame({}, styler_obj=1)
def test_len(self):
self.assertEqual(len(self.sf), len(self.sf.data_df))
self.assertEqual(len(self.sf), 3)
def test_str(self):
self.assertEqual(str(self.sf), str(self.sf.data_df))
def test__getitem__(self):
self.assertEqual(self.sf['a'].tolist(), self.sf.data_df['a'].tolist())
self.assertTrue(self.sf.data_df[['a', 'b']].equals(self.sf[['a', 'b']].data_df))
def test__setitem__(self):
self.sf['a'] = range(3)
self.sf['b'] = range(3, 6)
self.sf['c'] = 5
self.sf['d'] = self.sf['a'] + self.sf['b']
self.sf['e'] = self.sf['a'] + 5
self.assertTrue(all(self.sf.applymap(lambda x: isinstance(x, Container)).all()))
def test__getattr__(self):
self.assertEqual(self.sf.fillna, self.sf.data_df.fillna)
self.assertTrue(self.sf['a'].equals(self.sf.a))
with self.assertRaises(AttributeError):
self.sf.non_exisiting_method()
def test_apply_column_style(self):
# testing some edge cases
with self.assertRaises(TypeError):
# noinspection PyTypeChecker
self.sf.apply_column_style(cols_to_style='a', styler_obj=0)
with self.assertRaises(KeyError):
self.sf.apply_column_style(cols_to_style='non_existing_col', styler_obj=Styler())
# actual tests
self.apply_column_style(cols_to_style=['a'])
self.assertTrue(all([self.sf.at[index, 'a'].style.to_openpyxl_style()._style == self.openpy_style_obj_1
and self.sf.at[index, 'b'].style.to_openpyxl_style()._style != self.openpy_style_obj_1
for index in self.sf.index]))
sheet = self.export_and_get_default_sheet()
self.assertEqual(sheet.column_dimensions['A'].width, 10)
# 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_1 for i in range(2, len(self.sf))))
def test_apply_column_style_no_override_default_style(self):
# testing some edge cases
with self.assertRaises(TypeError):
# noinspection PyTypeChecker
self.sf.apply_column_style(cols_to_style='a', styler_obj=0)
with self.assertRaises(KeyError):
self.sf.apply_column_style(cols_to_style='non_existing_col', styler_obj=Styler())
# actual tests
self.apply_column_style(cols_to_style=['a'], overwrite_default_style=False)
self.assertTrue(all([self.sf.at[index, 'a'].style == Styler.combine(self.default_styler_obj, self.styler_obj_1)
and self.sf.at[index, 'b'].style == self.default_styler_obj
for index in self.sf.index]))
sheet = self.export_and_get_default_sheet()
self.assertEqual(sheet.column_dimensions['A'].width, 10)
# 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 == Styler.combine(self.default_styler_obj, self.styler_obj_1).to_openpyxl_style()._style
for i in range(2, len(self.sf))))
def test_apply_style_by_indexes_single_col(self):
with self.assertRaises(TypeError):
# noinspection PyTypeChecker
self.sf.apply_style_by_indexes(indexes_to_style=0, styler_obj=0)
self.apply_style_by_indexes(self.sf[self.sf['a'] == 'col_a_row_2'], cols_to_style=['a'])
self.assertTrue(all(self.sf.at[index, 'a'].style.to_openpyxl_style()._style == self.openpy_style_obj_1
for index in self.sf.index if self.sf.at[index, 'a'] == 'col_a_row_2'))
sheet = self.export_and_get_default_sheet()
self.assertTrue(all(sheet.cell(row=i, column=1)._style == self.openpy_style_obj_1 for i in range(1, len(self.sf))
if sheet.cell(row=i, column=1).value == 2))
self.assertEqual(sheet.row_dimensions[3].height, 10)
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.at[index, 'a'].style.to_openpyxl_style()._style == self.openpy_style_obj_1
for index in self.sf.index if self.sf.at[index, 'a'] == 2))
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(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_style_by_indexes_complement_style(self):
self.apply_style_by_indexes(self.sf[self.sf['a'] == 'col_a_row_1'], complement_style=self.styler_obj_2)
self.assertTrue(all(self.sf.at[index, 'a'].style.to_openpyxl_style()._style == self.openpy_style_obj_1
for index in self.sf.index if self.sf.at[index, 'a'] == 'col_a_row_1'))
self.assertTrue(all(self.sf.at[index, 'a'].style.to_openpyxl_style()._style == self.openpy_style_obj_2
for index in self.sf.index if self.sf.at[index, 'a'] != 'col_a_row_1'))
def test_apply_style_by_indexes_with_single_index(self):
self.apply_style_by_indexes(self.sf.index[0])
self.assertTrue(all(self.sf.iloc[0, self.sf.columns.get_loc(col)].style.to_openpyxl_style()._style == self.openpy_style_obj_1
for col in self.sf.columns))
sheet = self.export_and_get_default_sheet()
# row=2 since sheet start from row 1 and the headers are row 1
self.assertTrue(all(sheet.cell(row=2, column=col)._style == self.openpy_style_obj_1
for col in range(1, len(self.sf.columns))))
def test_apply_style_by_indexes_all_cols_with_multiple_indexes(self):
self.apply_style_by_indexes([1, 2])
self.assertTrue(all(self.sf.iloc[index, self.sf.columns.get_loc(col)].style.to_openpyxl_style()._style == self.openpy_style_obj_1
for index in [1, 2]
for col in self.sf.columns))
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 [3, 4] # sheet start from row 1 and headers are row 1
for j in range(1, len(self.sf.columns))))
def test_apply_headers_style(self):
self.apply_headers_style()
self.assertEqual(self.sf.columns[0].style.to_openpyxl_style()._style, self.openpy_style_obj_1)
sheet = self.export_and_get_default_sheet()
self.assertEqual(sheet.cell(row=1, column=1)._style, self.openpy_style_obj_1)
def test_set_column_width(self):
# testing some edge cases
with self.assertRaises(TypeError):
# noinspection PyTypeChecker
self.sf.set_column_width(columns='a', width='a')
with self.assertRaises(ValueError):
# noinspection PyTypeChecker
self.sf.set_column_width(columns='a', width=-1)
# actual tests
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):
with self.assertRaises(TypeError):
# noinspection PyTypeChecker
self.sf.set_column_width_dict(None)
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):
# testing some edge cases
with self.assertRaises(TypeError):
self.sf.set_row_height(rows=[1], height='a')
with self.assertRaises(ValueError):
self.sf.set_row_height(rows=[1], height=-1)
with self.assertRaises(ValueError):
self.sf.set_row_height(rows=['a'], height=-1)
# actual tests
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):
with self.assertRaises(TypeError):
# noinspection PyTypeChecker
self.sf.set_row_height_dict(None)
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):
with self.assertRaises(TypeError):
# noinspection PyTypeChecker
self.sf.rename(columns=None)
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 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 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 test_read_excel_with_style_openpyxl_objects(self):
self.export_and_get_default_sheet(save=True)
sf_from_excel = StyleFrame.read_excel(TEST_FILENAME, read_style=True, 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 test_read_excel_with_style_styler_objects(self):
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.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 test_read_excel_with_style_comments_openpyxl_objects(self):
self.export_and_get_default_sheet(save=True)
sf_from_excel = StyleFrame.read_excel(TEST_FILENAME, read_style=True, read_comments=True,
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 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 test_row_indexes(self):
self.assertEqual(self.sf.row_indexes, (1, 2, 3, 4))
def test_style_alternate_rows(self):
styles = [self.styler_obj_1, self.styler_obj_2]
openpy_styles = [self.openpy_style_obj_1, self.openpy_style_obj_2]
self.sf.style_alternate_rows(styles)
self.assertTrue(all(self.sf.iloc[index.value, 0].style.to_openpyxl_style() == styles[index.value % len(styles)].to_openpyxl_style()
for index in self.sf.index))
sheet = self.export_and_get_default_sheet()
# sheet start from row 1 and headers are row 1, so need to add 2 when iterating
self.assertTrue(all(sheet.cell(row=i.value + 2, column=1)._style == openpy_styles[i.value % len(styles)]
for i in self.sf.index))
def test_add_color_scale_conditional_formatting_start_end(self):
self.sf.add_color_scale_conditional_formatting(start_type=utils.conditional_formatting_types.percentile,
start_value=0, start_color=utils.colors.red,
end_type=utils.conditional_formatting_types.percentile,
end_value=100, end_color=utils.colors.green)
sheet = self.export_and_get_default_sheet(save=True)
cf_rules = self.get_cf_rules(sheet=sheet)
rules_dict = cf_rules['A1:B4']
self.assertEqual(rules_dict[0].type, 'colorScale')
self.assertEqual(rules_dict[0].colorScale.color[0].rgb, utils.colors.red)
self.assertEqual(rules_dict[0].colorScale.color[1].rgb, utils.colors.green)
self.assertEqual(rules_dict[0].colorScale.cfvo[0].type, utils.conditional_formatting_types.percentile)
self.assertEqual(rules_dict[0].colorScale.cfvo[0].val, 0.0)
self.assertEqual(rules_dict[0].colorScale.cfvo[1].type, utils.conditional_formatting_types.percentile)
self.assertEqual(rules_dict[0].colorScale.cfvo[1].val, 100.0)
def test_add_color_scale_conditional_formatting_start_mid_end(self):
self.sf.add_color_scale_conditional_formatting(start_type=utils.conditional_formatting_types.percentile,
start_value=0, start_color=utils.colors.red,
mid_type=utils.conditional_formatting_types.percentile,
mid_value=50, mid_color=utils.colors.yellow,
end_type=utils.conditional_formatting_types.percentile,
end_value=100, end_color=utils.colors.green)
sheet = self.export_and_get_default_sheet(save=True)
cf_rules = self.get_cf_rules(sheet=sheet)
rules_dict = cf_rules['A1:B4']
self.assertEqual(rules_dict[0].type, 'colorScale')
self.assertEqual(rules_dict[0].colorScale.color[0].rgb, utils.colors.red)
self.assertEqual(rules_dict[0].colorScale.color[1].rgb, utils.colors.yellow)
self.assertEqual(rules_dict[0].colorScale.color[2].rgb, utils.colors.green)
self.assertEqual(rules_dict[0].colorScale.cfvo[0].type, utils.conditional_formatting_types.percentile)
self.assertEqual(rules_dict[0].colorScale.cfvo[0].val, 0.0)
self.assertEqual(rules_dict[0].colorScale.cfvo[1].type, utils.conditional_formatting_types.percentile)
self.assertEqual(rules_dict[0].colorScale.cfvo[1].val, 50.0)
self.assertEqual(rules_dict[0].colorScale.cfvo[2].type, utils.conditional_formatting_types.percentile)
self.assertEqual(rules_dict[0].colorScale.cfvo[2].val, 100.0)
