def getCaccran():
filename = "C:\Users\e022434\Desktop\Range Accrual\Cuadre\Libor\RangeAccrual.xls"
wb = Workbook(filename)
wb.set_current()
rawFrame = Range("LGMRangeAccrual", "rangeaccrual.fixings").value
columns = ("payment_date", "upper_bound", "in_rate", "out_rate",
"reference_tenor", "spread_date", "spread", "option_date",
"amort_date", "amort", "add_flow_date", "add_flow")
df = pd.DataFrame(rawFrame, columns = columns).dropna(how = 'all')
# Option
option_dates = df["option_date"].dropna().apply(datetime_to_xldate)
df["option_date"] = option_dates
df["notice_date"] = option_dates
df["option_idx"] = pd.Series(range(len(option_dates)), option_dates.index)
# Funding Leg
initial_date = 42207
spread_nominal = 1E6
spread_end_dates = df["spread_date"].dropna().apply(datetime_to_xldate)
spread_start_dates = ([initial_date] + spread_end_dates.values.tolist())[:-1]
df["spread_end_date"] = spread_end_dates
df["spread_start_date"] = pd.Series(spread_start_dates, spread_end_dates.index)
df["spread_nominal"] = pd.Series([spread_nominal] * len(spread_end_dates), spread_end_dates.index)
df["spread_idx"] = pd.Series(range(len(spread_end_dates)), spread_end_dates.index)
# Exotic leg
exotic_nominal = 1E6
reference_tenor = "USD_3M"
payment_dates = df["payment_date"].dropna().apply(datetime_to_xldate)
start_dates = ([initial_date] + payment_dates.values.tolist())[:-1]
df["payment_date"] = payment_dates
df["end_date"] = payment_dates
df["start_date"] = pd.Series(start_dates, payment_dates.index)
df["reference_tenor"] = pd.Series([reference_tenor]*len(start_dates), payment_dates.index)
df["nominal"] = pd.Series([exotic_nominal] * len(start_dates), payment_dates.index)
df["idx"] = pd.Series(range(len(start_dates)), payment_dates.index)
columns += ("notice_date", "option_idx", "spread_end_date",
"spread_start_date", "spread_nominal", "spread_idx",
"end_date", "start_date", "nominal", "idx")
df.columns = columns
result = "<deal>\n"
result += getOptions(df.dropna(subset = ("option_date",)))
result += getSwap()
result += getExoticLeg(df)
result += getFundingLeg(df)
result += "</deal>\n"
return result
def getIndex(workbook, index_header, indexes, shifters):
vol_header = "<volatility surface_type=\"FIXED\" type=\"PARAMS_INTERPOLATED\" " + \
"value=\"VOL_PARAM_SABR\" vol_nature=\"NORMAL\">\n" + \
"<interpolation extrapolate=\"true\" flat_extrapolation=\"true\" " + \
"left_side_derivatives=\"false\" magnitude=\"VOL\" method=\"LINEAR\"/>\n" + \
"<maturities_defaults type=\"SABR_NORMAL\"/>\n<maturities>\n"
item_sep = "<maturity value=\"{date}\">\n<parameters>\n"
item = "<parameter name=\"{param_name}\" value=\"{param_value}\" />\n"
item_padding = "</parameters>\n</maturity>\n"
index_padding = "</maturities>\n</volatility>\n</marketdata_index>\n"
lowerK = 0.0
upperK = 10
result = ""
wb = Workbook(workbook)
wb.set_current()
for index, shifter in zip(indexes, shifters):
result += index_header.format(index_name=index, tenor_shifter=shifter)
result += vol_header
rawFrame = Range(index, "CalibratedModelParams").value
for line in filter(lambda x: any(x), rawFrame[1:]):
date = line[0]
if isinstance(date, dt.datetime):
date = datetime_to_xldate(date)
result += item_sep.format(date = int(date))
result += item.format(param_name = "alpha", param_value=line[1])
result += item.format(param_name = "beta", param_value=line[2])
result += item.format(param_name = "rho", param_value=line[3])
result += item.format(param_name = "nu", param_value=line[4])
result += item.format(param_name = "lower_k", param_value=lowerK)
result += item.format(param_name = "upper_k", param_value=upperK)
result += item.format(param_name = "displacement", param_value=line[5])
result += item_padding
result += index_padding
wb.close()
return result
class TestWorkbook:
def setUp(self):
# Connect to test file and make Sheet1 the active sheet
xl_file1 = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'test_workbook_1.xlsx')
self.wb = Workbook(xl_file1)
Sheet('Sheet1').activate()
def tearDown(self):
self.wb.close()
def test_name(self):
assert_equal(self.wb.name, 'test_workbook_1.xlsx')
def test_active_sheet(self):
assert_equal(self.wb.active_sheet.name, 'Sheet1')
def test_current(self):
assert_equal(self.wb.xl_workbook, Workbook.current().xl_workbook)
def test_set_current(self):
wb2 = Workbook()
assert_equal(Workbook.current().xl_workbook, wb2.xl_workbook)
self.wb.set_current()
assert_equal(Workbook.current().xl_workbook, self.wb.xl_workbook)
wb2.close()
def test_get_selection(self):
Range('A1').value = 1000
assert_equal(self.wb.get_selection().value, 1000)
def test_reference_two_unsaved_wb(self):
"""Covers GH Issue #63"""
wb1 = Workbook()
wb2 = Workbook()
Range('A1').value = 2. # wb2
Range('A1', wkb=wb1).value = 1. # wb1
assert_equal(Range('A1').value, 2.)
assert_equal(Range('A1', wkb=wb1).value, 1.)
wb1.close()
wb2.close()
def pacing():
a = '2015-09-28'
b = '2015-10-04'
c = '2015-10-05'
d = '2015-12-27'
e = '2015-12-28'
f = '2015-12-31'
date_rng = pd.date_range(main.quarter_start(), periods=92, freq='D')
date_df = pd.DataFrame(date_rng, columns=['Date'])
pacing_wb = Workbook(main.dr_pacing_path())
pacing_wb.set_current()
Application(wkb=pacing_wb).xl_app.Run('Clean_Pacing_Data')
pacing_data = pd.DataFrame(Range('Q4 DDR Pacing by sub-tactic', 'Z1').table.value,
columns=Range('Q4 DDR Pacing by sub-tactic', 'Z1').horizontal.value)
pacing_data.drop(0, inplace=True)
Range('Q4 DDR Pacing by sub-tactic', 'Z1').table.clear_contents()
pacing_wb.close()
pacing_data.set_index('Week', inplace=True)
pacing_data = pacing_data.resample('1D', fill_method='pad')
pacing_data.fillna(method='ffill', inplace= True)
pacing_data.reset_index(inplace=True)
pacing_data.rename(columns={'Week': 'Date'}, inplace=True)
pacing_data = pd.merge(pacing_data, date_df, how='right', left_on='Date', right_on='Date')
pacing_data.fillna(method='ffill', inplace=True)
pacing_data.ix[a:b] = pacing_data.ix[a:b] / 4
pacing_data.ix[c:d] = pacing_data.ix[c:d] / 7
pacing_data.ix[e:f] = pacing_data.ix[e:f] / 4
pacing_data.set_index('Date', inplace= True)
return pacing_data
class TestWorkbook:
def setUp(self):
# Connect to test file and make Sheet1 the active sheet
xl_file1 = os.path.join(os.path.dirname(os.path.abspath(__file__)),
'test_workbook_1.xlsx')
self.wb = Workbook(xl_file1, app_visible=False, app_target=APP_TARGET)
Sheet('Sheet1').activate()
def tearDown(self):
self.wb.close()
def test_name(self):
assert_equal(self.wb.name, 'test_workbook_1.xlsx')
def test_active_sheet(self):
assert_equal(self.wb.active_sheet.name, 'Sheet1')
def test_current(self):
assert_equal(self.wb.xl_workbook, Workbook.current().xl_workbook)
def test_set_current(self):
wb2 = Workbook(app_visible=False, app_target=APP_TARGET)
assert_equal(Workbook.current().xl_workbook, wb2.xl_workbook)
self.wb.set_current()
assert_equal(Workbook.current().xl_workbook, self.wb.xl_workbook)
wb2.close()
def test_get_selection(self):
Range('A1').value = 1000
assert_equal(self.wb.get_selection().value, 1000)
def test_reference_two_unsaved_wb(self):
"""Covers GH Issue #63"""
wb1 = Workbook(app_visible=False, app_target=APP_TARGET)
wb2 = Workbook(app_visible=False, app_target=APP_TARGET)
Range('A1').value = 2. # wb2
Range('A1', wkb=wb1).value = 1. # wb1
assert_equal(Range('A1').value, 2.)
assert_equal(Range('A1', wkb=wb1).value, 1.)
wb1.close()
wb2.close()
def test_save_naked(self):
cwd = os.getcwd()
wb1 = Workbook(app_visible=False, app_target=APP_TARGET)
target_file_path = os.path.join(cwd, wb1.name + '.xlsx')
if os.path.isfile(target_file_path):
os.remove(target_file_path)
wb1.save()
assert_equal(os.path.isfile(target_file_path), True)
wb2 = Workbook(target_file_path,
app_visible=False,
app_target=APP_TARGET)
wb2.close()
if os.path.isfile(target_file_path):
os.remove(target_file_path)
def test_save_path(self):
cwd = os.getcwd()
wb1 = Workbook(app_visible=False, app_target=APP_TARGET)
target_file_path = os.path.join(cwd, 'TestFile.xlsx')
if os.path.isfile(target_file_path):
os.remove(target_file_path)
wb1.save(target_file_path)
assert_equal(os.path.isfile(target_file_path), True)
wb2 = Workbook(target_file_path,
app_visible=False,
app_target=APP_TARGET)
wb2.close()
if os.path.isfile(target_file_path):
os.remove(target_file_path)
def test_mock_caller(self):
_skip_if_not_default_xl()
Workbook.set_mock_caller(
os.path.join(os.path.dirname(os.path.abspath(__file__)),
'test_workbook_1.xlsx'))
wb = Workbook.caller()
Range('A1', wkb=wb).value = 333
assert_equal(Range('A1', wkb=wb).value, 333)
def test_unicode_path(self):
# pip3 seems to struggle with unicode filenames
src = os.path.join(os.path.dirname(os.path.abspath(__file__)),
'unicode_path.xlsx')
dst = os.path.join(os.path.dirname(os.path.abspath(__file__)),
'ünicödé_päth.xlsx')
shutil.move(src, dst)
wb = Workbook(os.path.join(os.path.dirname(os.path.abspath(__file__)),
'ünicödé_päth.xlsx'),
app_target=APP_TARGET)
Range('A1').value = 1
wb.close()
shutil.move(dst, src)
def unsaved_workbook_reference(self):
wb = Workbook(app_visible=False, app_target=APP_TARGET)
Range('B2').value = 123
wb2 = Workbook(wb.name, app_visible=False, app_target=APP_TARGET)
assert_equal(Range('B2', wkb=wb2).value, 123)
def publishers(dr):
cd, t2t, fbx, search, pros, aal = data_transform.dr_placement_types()
week = week_of(dr)
# Publisher Performance
pub_dr = dr[(dr['Campaign'] == 'DR') & (dr['Date'] >= main.quarter_start())]
pub_dr = pub_dr.groupby(['Site', 'Placement Messaging Type', 'Week', 'Date'])
pub_dr = pd.DataFrame(pub_dr.sum()).reset_index()
#pub_dr = pub_dr[(pub_dr['NTC Media Cost'] != 0)]
pub_dr['Tactic'] = np.where((pub_dr['Placement Messaging Type'].str.contains(cd) == True) &
(pub_dr['Placement Messaging Type'].str.contains(fbx) != True), 'C/D Remessaging',
np.where(pub_dr['Placement Messaging Type'].str.contains(fbx) == True,
'FBX Remessaging', pub_dr['Placement Messaging Type']))
# Quarter
q_dr = pub_dr[pub_dr['Date'] >= main.quarter_start()]
q_dr = q_dr.groupby(['Site', 'Tactic'])
q_dr = pd.DataFrame(q_dr.sum()).reset_index()
q_dr = goals(q_dr)
# Last Week
last_week = pub_dr[pub_dr['Week'] == pub_dr['Week'].max()]
last_week = last_week.groupby(['Site', 'Tactic'])
last_week = pd.DataFrame(last_week.sum()).reset_index()
last_week.rename(columns={'NET Media Cost': week}, inplace= True)
# Publishers Overall
sites = q_dr.groupby('Site')
sites = pd.DataFrame(sites.sum()).reset_index()
sites['CPGA'] = sites['NET Media Cost'] / sites['Total GAs']
# Brand Remessaging
br = dr[dr['Campaign'] == 'Brand Remessaging']
br_quarter = br[br['Date'] >= main.quarter_start()]
br_lw = br[br['Week'] == br['Week'].max()]
br_quarter = br_quarter.groupby('Site')
br_quarter = pd.DataFrame(br_quarter.sum().reset_index())
br_quarter['Traffic Yield'] = br_quarter['Total Traffic Actions'].astype(float) / \
br_quarter['Impressions'].astype(float)
br_lw = br_lw.groupby('Site')
br_lw = pd.DataFrame(br_lw.sum().reset_index())
pacing_wb = Workbook(main.dr_pacing_path())
pacing_wb.set_current()
Range('Publisher Performance', 'A13', index=False, header=False).value = q_dr[
['Site', 'Tactic', 'Total GAs']]
Range('Publisher Performance', 'E13', index= False, header= False).value = q_dr['NET Media Cost']
Range('Publisher Performance', 'D13', index= False, header= False).value = last_week[week]
Range('Publisher Performance', 'D12').value = week
Range('Publisher Performance', 'A32', index= False, header= False).value = sites[['Site', 'Total GAs', 'CPGA']]
Range('Publisher Performance', 'C7').value = pub_dr['Week'].max().strftime('%m/%d/%Y')
Range('Publisher Performance', 'B45', index= False, header= False).value = \
br_quarter[['Traffic Yield', 'Impressions', 'Total Traffic Actions']]
Range('Publisher Performance', 'G45', index= False, header= False).value = br_quarter['NET Media Cost']
Range('Publisher Performance', 'F45', index= False, header= False).value = br_lw['NET Media Cost']
Range('Publisher Performance', 'F44').value = week
pacing_wb.save()
pacing_wb.close()
class TestWorkbook:
def setUp(self):
# Connect to test file and make Sheet1 the active sheet
xl_file1 = os.path.join(os.path.dirname(os.path.abspath(__file__)),
'test_workbook_1.xlsx')
self.wb = Workbook(xl_file1)
Sheet('Sheet1').activate()
def tearDown(self):
self.wb.close()
def test_name(self):
assert_equal(self.wb.name, 'test_workbook_1.xlsx')
def test_active_sheet(self):
assert_equal(self.wb.active_sheet.name, 'Sheet1')
def test_current(self):
assert_equal(self.wb.xl_workbook, Workbook.current().xl_workbook)
def test_set_current(self):
wb2 = Workbook()
assert_equal(Workbook.current().xl_workbook, wb2.xl_workbook)
self.wb.set_current()
assert_equal(Workbook.current().xl_workbook, self.wb.xl_workbook)
wb2.close()
def test_get_selection(self):
Range('A1').value = 1000
assert_equal(self.wb.get_selection().value, 1000)
def test_reference_two_unsaved_wb(self):
"""Covers GH Issue #63"""
wb1 = Workbook()
wb2 = Workbook()
Range('A1').value = 2. # wb2
Range('A1', wkb=wb1).value = 1. # wb1
assert_equal(Range('A1').value, 2.)
assert_equal(Range('A1', wkb=wb1).value, 1.)
wb1.close()
wb2.close()
def test_save_naked(self):
cwd = os.getcwd()
wb1 = Workbook()
target_file_path = os.path.join(cwd, wb1.name + '.xlsx')
if os.path.isfile(target_file_path):
os.remove(target_file_path)
wb1.save()
assert_equal(os.path.isfile(target_file_path), True)
wb2 = Workbook(target_file_path)
wb2.close()
if os.path.isfile(target_file_path):
os.remove(target_file_path)
def test_save_path(self):
cwd = os.getcwd()
wb1 = Workbook()
target_file_path = os.path.join(cwd, 'TestFile.xlsx')
if os.path.isfile(target_file_path):
os.remove(target_file_path)
wb1.save(target_file_path)
assert_equal(os.path.isfile(target_file_path), True)
wb2 = Workbook(target_file_path)
wb2.close()
if os.path.isfile(target_file_path):
os.remove(target_file_path)
class TestWorkbook:
def setUp(self):
# Connect to test file and make Sheet1 the active sheet
xl_file1 = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'test_workbook_1.xlsx')
self.wb = Workbook(xl_file1, app_visible=False, app_target=APP_TARGET)
Sheet('Sheet1').activate()
def tearDown(self):
self.wb.close()
def test_name(self):
assert_equal(self.wb.name, 'test_workbook_1.xlsx')
def test_active_sheet(self):
assert_equal(self.wb.active_sheet.name, 'Sheet1')
def test_current(self):
assert_equal(self.wb.xl_workbook, Workbook.current().xl_workbook)
def test_set_current(self):
wb2 = Workbook(app_visible=False, app_target=APP_TARGET)
assert_equal(Workbook.current().xl_workbook, wb2.xl_workbook)
self.wb.set_current()
assert_equal(Workbook.current().xl_workbook, self.wb.xl_workbook)
wb2.close()
def test_get_selection(self):
Range('A1').value = 1000
assert_equal(self.wb.get_selection().value, 1000)
def test_reference_two_unsaved_wb(self):
"""Covers GH Issue #63"""
wb1 = Workbook(app_visible=False, app_target=APP_TARGET)
wb2 = Workbook(app_visible=False, app_target=APP_TARGET)
Range('A1').value = 2. # wb2
Range('A1', wkb=wb1).value = 1. # wb1
assert_equal(Range('A1').value, 2.)
assert_equal(Range('A1', wkb=wb1).value, 1.)
wb1.close()
wb2.close()
def test_save_naked(self):
cwd = os.getcwd()
wb1 = Workbook(app_visible=False, app_target=APP_TARGET)
target_file_path = os.path.join(cwd, wb1.name + '.xlsx')
if os.path.isfile(target_file_path):
os.remove(target_file_path)
wb1.save()
assert_equal(os.path.isfile(target_file_path), True)
wb2 = Workbook(target_file_path, app_visible=False, app_target=APP_TARGET)
wb2.close()
if os.path.isfile(target_file_path):
os.remove(target_file_path)
def test_save_path(self):
cwd = os.getcwd()
wb1 = Workbook(app_visible=False, app_target=APP_TARGET)
target_file_path = os.path.join(cwd, 'TestFile.xlsx')
if os.path.isfile(target_file_path):
os.remove(target_file_path)
wb1.save(target_file_path)
assert_equal(os.path.isfile(target_file_path), True)
wb2 = Workbook(target_file_path, app_visible=False, app_target=APP_TARGET)
wb2.close()
if os.path.isfile(target_file_path):
os.remove(target_file_path)
def test_mock_caller(self):
_skip_if_not_default_xl()
Workbook.set_mock_caller(os.path.join(os.path.dirname(os.path.abspath(__file__)), 'test_workbook_1.xlsx'))
wb = Workbook.caller()
Range('A1', wkb=wb).value = 333
assert_equal(Range('A1', wkb=wb).value, 333)
def test_unicode_path(self):
# pip3 seems to struggle with unicode filenames
src = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'unicode_path.xlsx')
dst = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'ünicödé_päth.xlsx')
shutil.move(src, dst)
wb = Workbook(os.path.join(os.path.dirname(os.path.abspath(__file__)), 'ünicödé_päth.xlsx'), app_target=APP_TARGET)
Range('A1').value = 1
wb.close()
shutil.move(dst, src)
def unsaved_workbook_reference(self):
wb = Workbook(app_visible=False, app_target=APP_TARGET)
Range('B2').value = 123
wb2 = Workbook(wb.name, app_visible=False, app_target=APP_TARGET)
assert_equal(Range('B2', wkb=wb2).value, 123)
class TestWorkbook:
def setUp(self):
# Connect to test file and make Sheet1 the active sheet
xl_file1 = os.path.join(os.path.dirname(os.path.abspath(__file__)),
'test_workbook_1.xlsx')
self.wb = Workbook(xl_file1, app_visible=False, app_target=APP_TARGET)
Sheet('Sheet1').activate()
def tearDown(self):
class_teardown(self.wb)
def test_name(self):
assert_equal(self.wb.name, 'test_workbook_1.xlsx')
def test_active_sheet(self):
assert_equal(self.wb.active_sheet.name, 'Sheet1')
def test_current(self):
assert_equal(self.wb.xl_workbook, Workbook.current().xl_workbook)
def test_set_current(self):
wb2 = Workbook(app_visible=False, app_target=APP_TARGET)
assert_equal(Workbook.current().xl_workbook, wb2.xl_workbook)
self.wb.set_current()
assert_equal(Workbook.current().xl_workbook, self.wb.xl_workbook)
wb2.close()
def test_get_selection(self):
Range('A1').value = 1000
assert_equal(self.wb.get_selection().value, 1000)
def test_reference_two_unsaved_wb(self):
"""Covers GH Issue #63"""
wb1 = Workbook(app_visible=False, app_target=APP_TARGET)
wb2 = Workbook(app_visible=False, app_target=APP_TARGET)
Range('A1').value = 2. # wb2
Range('A1', wkb=wb1).value = 1. # wb1
assert_equal(Range('A1').value, 2.)
assert_equal(Range('A1', wkb=wb1).value, 1.)
wb1.close()
wb2.close()
def test_save_naked(self):
cwd = os.getcwd()
wb1 = Workbook(app_visible=False, app_target=APP_TARGET)
target_file_path = os.path.join(cwd, wb1.name + '.xlsx')
if os.path.isfile(target_file_path):
os.remove(target_file_path)
wb1.save()
assert_equal(os.path.isfile(target_file_path), True)
wb2 = Workbook(target_file_path,
app_visible=False,
app_target=APP_TARGET)
wb2.close()
if os.path.isfile(target_file_path):
os.remove(target_file_path)
def test_save_path(self):
cwd = os.getcwd()
wb1 = Workbook(app_visible=False, app_target=APP_TARGET)
target_file_path = os.path.join(cwd, 'TestFile.xlsx')
if os.path.isfile(target_file_path):
os.remove(target_file_path)
wb1.save(target_file_path)
assert_equal(os.path.isfile(target_file_path), True)
wb2 = Workbook(target_file_path,
app_visible=False,
app_target=APP_TARGET)
wb2.close()
if os.path.isfile(target_file_path):
os.remove(target_file_path)
def test_mock_caller(self):
# Can't really run this one with app_visible=False
_skip_if_not_default_xl()
Workbook.set_mock_caller(
os.path.join(os.path.dirname(os.path.abspath(__file__)),
'test_workbook_1.xlsx'))
wb = Workbook.caller()
Range('A1', wkb=wb).value = 333
assert_equal(Range('A1', wkb=wb).value, 333)
def test_unicode_path(self):
# pip3 seems to struggle with unicode filenames
src = os.path.join(os.path.dirname(os.path.abspath(__file__)),
'unicode_path.xlsx')
dst = os.path.join(os.path.dirname(os.path.abspath(__file__)),
'ünicödé_päth.xlsx')
shutil.move(src, dst)
wb = Workbook(os.path.join(os.path.dirname(os.path.abspath(__file__)),
'ünicödé_päth.xlsx'),
app_visible=False,
app_target=APP_TARGET)
Range('A1').value = 1
wb.close()
shutil.move(dst, src)
def test_unsaved_workbook_reference(self):
wb = Workbook(app_visible=False, app_target=APP_TARGET)
Range('B2').value = 123
wb2 = Workbook(wb.name, app_visible=False, app_target=APP_TARGET)
assert_equal(Range('B2', wkb=wb2).value, 123)
wb2.close()
def test_delete_named_item(self):
Range('B10:C11').name = 'to_be_deleted'
assert_equal(Range('to_be_deleted').name, 'to_be_deleted')
del self.wb.names['to_be_deleted']
assert_not_equal(Range('B10:C11').name, 'to_be_deleted')
def test_names_collection(self):
Range('A1').name = 'name1'
Range('A2').name = 'name2'
assert_true('name1' in self.wb.names and 'name2' in self.wb.names)
Range('A3').name = 'name3'
assert_true('name1' in self.wb.names and 'name2' in self.wb.names
and 'name3' in self.wb.names)
def test_active_workbook(self):
# TODO: add test over multiple Excel instances on Windows
Range('A1').value = 'active_workbook'
wb_active = Workbook.active(app_target=APP_TARGET)
assert_equal(Range('A1', wkb=wb_active).value, 'active_workbook')
def test_workbook_name(self):
Range('A10').value = 'name-test'
wb2 = Workbook('test_workbook_1.xlsx',
app_visible=False,
app_target=APP_TARGET)
assert_equal(Range('A10', wkb=wb2).value, 'name-test')
class TestWorkbook:
def setUp(self):
# Connect to test file and make Sheet1 the active sheet
xl_file1 = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'test_workbook_1.xlsx')
self.wb = Workbook(xl_file1, app_visible=False)
Sheet('Sheet1').activate()
def tearDown(self):
self.wb.close()
def test_name(self):
assert_equal(self.wb.name, 'test_workbook_1.xlsx')
def test_active_sheet(self):
assert_equal(self.wb.active_sheet.name, 'Sheet1')
def test_current(self):
assert_equal(self.wb.xl_workbook, Workbook.current().xl_workbook)
def test_set_current(self):
wb2 = Workbook(app_visible=False)
assert_equal(Workbook.current().xl_workbook, wb2.xl_workbook)
self.wb.set_current()
assert_equal(Workbook.current().xl_workbook, self.wb.xl_workbook)
wb2.close()
def test_get_selection(self):
Range('A1').value = 1000
assert_equal(self.wb.get_selection().value, 1000)
def test_reference_two_unsaved_wb(self):
"""Covers GH Issue #63"""
wb1 = Workbook(app_visible=False)
wb2 = Workbook(app_visible=False)
Range('A1').value = 2. # wb2
Range('A1', wkb=wb1).value = 1. # wb1
assert_equal(Range('A1').value, 2.)
assert_equal(Range('A1', wkb=wb1).value, 1.)
wb1.close()
wb2.close()
def test_save_naked(self):
cwd = os.getcwd()
wb1 = Workbook(app_visible=False)
target_file_path = os.path.join(cwd, wb1.name + '.xlsx')
if os.path.isfile(target_file_path):
os.remove(target_file_path)
wb1.save()
assert_equal(os.path.isfile(target_file_path), True)
wb2 = Workbook(target_file_path, app_visible=False)
wb2.close()
if os.path.isfile(target_file_path):
os.remove(target_file_path)
def test_save_path(self):
cwd = os.getcwd()
wb1 = Workbook(app_visible=False)
target_file_path = os.path.join(cwd, 'TestFile.xlsx')
if os.path.isfile(target_file_path):
os.remove(target_file_path)
wb1.save(target_file_path)
assert_equal(os.path.isfile(target_file_path), True)
wb2 = Workbook(target_file_path, app_visible=False)
wb2.close()
if os.path.isfile(target_file_path):
os.remove(target_file_path)
class TestWorkbook:
def setUp(self):
# Connect to test file and make Sheet1 the active sheet
xl_file1 = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'test_workbook_1.xlsx')
self.wb = Workbook(xl_file1, app_visible=False, app_target=APP_TARGET)
Sheet('Sheet1').activate()
def tearDown(self):
class_teardown(self.wb)
def test_name(self):
assert_equal(self.wb.name, 'test_workbook_1.xlsx')
def test_active_sheet(self):
assert_equal(self.wb.active_sheet.name, 'Sheet1')
def test_current(self):
assert_equal(self.wb.xl_workbook, Workbook.current().xl_workbook)
def test_set_current(self):
wb2 = Workbook(app_visible=False, app_target=APP_TARGET)
assert_equal(Workbook.current().xl_workbook, wb2.xl_workbook)
self.wb.set_current()
assert_equal(Workbook.current().xl_workbook, self.wb.xl_workbook)
wb2.close()
def test_get_selection(self):
Range('A1').value = 1000
assert_equal(self.wb.get_selection().value, 1000)
def test_reference_two_unsaved_wb(self):
"""Covers GH Issue #63"""
wb1 = Workbook(app_visible=False, app_target=APP_TARGET)
wb2 = Workbook(app_visible=False, app_target=APP_TARGET)
Range('A1').value = 2. # wb2
Range('A1', wkb=wb1).value = 1. # wb1
assert_equal(Range('A1').value, 2.)
assert_equal(Range('A1', wkb=wb1).value, 1.)
wb1.close()
wb2.close()
def test_save_naked(self):
if sys.platform.startswith('darwin'):
folder = os.path.expanduser("~") + '/Library/Containers/com.microsoft.Excel/Data/'
if os.path.isdir(folder):
os.chdir(folder)
cwd = os.getcwd()
wb1 = Workbook(app_visible=False, app_target=APP_TARGET)
target_file_path = os.path.join(cwd, wb1.name + '.xlsx')
if os.path.isfile(target_file_path):
os.remove(target_file_path)
wb1.save()
assert_equal(os.path.isfile(target_file_path), True)
wb2 = Workbook(target_file_path, app_visible=False, app_target=APP_TARGET)
wb2.close()
if os.path.isfile(target_file_path):
os.remove(target_file_path)
def test_save_path(self):
if sys.platform.startswith('darwin'):
folder = os.path.expanduser("~") + '/Library/Containers/com.microsoft.Excel/Data/'
if os.path.isdir(folder):
os.chdir(folder)
cwd = os.getcwd()
wb1 = Workbook(app_visible=False, app_target=APP_TARGET)
target_file_path = os.path.join(cwd, 'TestFile.xlsx')
if os.path.isfile(target_file_path):
os.remove(target_file_path)
wb1.save(target_file_path)
assert_equal(os.path.isfile(target_file_path), True)
wb2 = Workbook(target_file_path, app_visible=False, app_target=APP_TARGET)
wb2.close()
if os.path.isfile(target_file_path):
os.remove(target_file_path)
def test_mock_caller(self):
# Can't really run this one with app_visible=False
_skip_if_not_default_xl()
Workbook.set_mock_caller(os.path.join(os.path.dirname(os.path.abspath(__file__)), 'test_workbook_1.xlsx'))
wb = Workbook.caller()
Range('A1', wkb=wb).value = 333
assert_equal(Range('A1', wkb=wb).value, 333)
def test_unicode_path(self):
# pip3 seems to struggle with unicode filenames
src = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'unicode_path.xlsx')
if sys.platform.startswith('darwin') and os.path.isdir(os.path.expanduser("~") + '/Library/Containers/com.microsoft.Excel/Data/'):
dst = os.path.join(os.path.expanduser("~") + '/Library/Containers/com.microsoft.Excel/Data/',
'ünicödé_päth.xlsx')
else:
dst = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'ünicödé_päth.xlsx')
shutil.copy(src, dst)
wb = Workbook(dst, app_visible=False, app_target=APP_TARGET)
Range('A1').value = 1
wb.close()
os.remove(dst)
def test_unsaved_workbook_reference(self):
wb = Workbook(app_visible=False, app_target=APP_TARGET)
Range('B2').value = 123
wb2 = Workbook(wb.name, app_visible=False, app_target=APP_TARGET)
assert_equal(Range('B2', wkb=wb2).value, 123)
wb2.close()
def test_delete_named_item(self):
Range('B10:C11').name = 'to_be_deleted'
assert_equal(Range('to_be_deleted').name, 'to_be_deleted')
del self.wb.names['to_be_deleted']
assert_not_equal(Range('B10:C11').name, 'to_be_deleted')
def test_names_collection(self):
Range('A1').name = 'name1'
Range('A2').name = 'name2'
assert_true('name1' in self.wb.names and 'name2' in self.wb.names)
Range('A3').name = 'name3'
assert_true('name1' in self.wb.names and 'name2' in self.wb.names and
'name3' in self.wb.names)
def test_active_workbook(self):
# TODO: add test over multiple Excel instances on Windows
Range('A1').value = 'active_workbook'
wb_active = Workbook.active(app_target=APP_TARGET)
assert_equal(Range('A1', wkb=wb_active).value, 'active_workbook')
def test_workbook_name(self):
Range('A10').value = 'name-test'
wb2 = Workbook('test_workbook_1.xlsx', app_visible=False, app_target=APP_TARGET)
assert_equal(Range('A10', wkb=wb2).value, 'name-test')
class ExcelTemplateRenderer(object):
"""
A class to render an Excel template using data stored
in a database and given a specification file.
Parameters
----------
db_engine : object
the :class:`sqlalchemy.engine.Engine` object used to
connect and query the database.
template_name : str
path to the `xlsx` template file.
spec_filename : str
path to the specification file (yaml format).
output_dirname : str
path to the directory where to store the
generated excel file(s) (i.e., the rendered template(s)).
jinja_env : object or None
allow to provide a :class:`jinja2.Environment` object,
helpful when using custom filters in the specification file
(optional).
Notes
-----
The specification file (yaml format) consists of a list
of render blocks. TODO: documentation on the format.
"""
def __init__(self, db_engine, template_name,
spec_filename, output_dirname, jinja_env=None):
self.db_engine = db_engine
self.template_name = template_name
self.spec_filename = spec_filename
self.output_dirname = os.path.abspath(output_dirname)
if jinja_env is None:
self.jinja_env = jinja2.Environment()
else:
self.jinja_env = jinja_env
with open(self.spec_filename, 'r', encoding='utf-8') as f:
self.render_blocks = yaml.load(f)
os.makedirs(self.output_dirname, exist_ok=True)
logger.info("output directory is %s", self.output_dirname)
def open_template_as_current_wkb(self):
self.wkb = Workbook(
os.path.abspath(self.template_name),
app_visible=False
)
self.wkb.set_current()
def save_current_wkb(self, filename):
filepath = os.path.join(self.output_dirname, filename)
self.wkb.save(filepath)
logger.info("created %s", filepath)
def save_current_wkb_as_pdf(self, filename, worksheet_name):
filepath = os.path.join(self.output_dirname, filename)
try:
ws = Sheet(worksheet_name, wkb=self.wkb)
ws.xl_sheet.ExportAsFixedFormat(0, filepath)
logger.info("created %s", filepath)
except Exception as e:
logger.error("failed to export pdf")
logger.error("detailled error: %s - %s",
e.__class__.__name__, str(e))
def close_current_wkb(self):
self.wkb.close()
def insert_one_series(self, series, cell_specification):
"""
Populate the current workbook given a single
:class=:`pandas.Series` object.
"""
if not len(series):
return
# contiguous cells
#TODO: (use vertical and horizontal properties of xlwings)
# non-contiguous user-defined cells
for cs in cell_specification.get('cells', []):
ws = cs.get('worksheet') or Sheet.active(self.wkb).name
content = self.jinja_env.from_string(cs['content']).render(**series)
logger.debug("insert content '%s' at cell '%s' in sheet '%s'",
content, cs['cell'], ws)
Range(ws, cs['cell']).value = content
def insert_one_dataframe(self, df, cell_specification):
"""
Populate the current workbook given a single
:class=:`pandas.DataFrame` object.
"""
if not len(df):
return
index = cell_specification.get('index', False)
header = cell_specification.get('header', False)
top_left_cell = cell_specification.get('top_left_cell', 'A0')
logger.debug("insert %d by %d rows/cols dataframe "
"at cell '%s' in sheet '%s'",
len(df), len(df.columns),
str(top_left_cell), Sheet.active(self.wkb).name)
Range(top_left_cell, index=index, header=header).value = df
def apply_render_block(self, render_block, query_context=None,
**kwargs):
"""
Apply a single render block in the specification file.
- `query_context` (mappable or None) is a context used when
rendering the database query with jinja2 (optional).
- **kwargs is used to overwrite any key/value pair
in the render block.
"""
# override render_block key/val with kwargs
render_block.update(kwargs)
logger.info("processing render block '%s'",
render_block.get('name', '<unamed>'))
# query the DB into a pandas DataFrame
if query_context is None:
query_context = dict()
query_template = self.jinja_env.from_string(
render_block['query'].strip()
)
query = query_template.render(**query_context)
logger.debug("rendered query: \n'''\n%s\n'''", query)
df = pd.read_sql(query, self.db_engine)
logger.debug("query returned %d record(s)", len(df))
# TODO: calculate extra columns and add it to the DataFrame
# activate worksheet if provided
ws_name = render_block['cell_specification'].get('worksheet') or None
if ws_name is not None:
ws2reactivate_name = Sheet.active(self.wkb).name
Sheet(ws_name, wkb=self.wkb).activate()
# apply the render_block, apply recusively included blocks,
# and save the rendered workbook(s) if needed
apply_by_row = render_block.get('apply_by_row', False)
save_as = render_block.get('save_as', None)
if apply_by_row and save_as is not None:
logger.info("%d file(s) to generate", len(df))
if apply_by_row:
for row, pseries in df.iterrows():
self.insert_one_series(
pseries, render_block['cell_specification']
)
for item in render_block.get('include', []):
if isinstance(item, dict):
block_name = item.pop('render_block')
override_vars = item
else:
block_name = item
override_vars = {}
block = [b for b in self.render_blocks
if b['name'] == block_name][0]
self.apply_render_block(block,
query_context=pseries,
**override_vars)
if save_as is not None:
tpl = save_as['filename']
filename = self.jinja_env.from_string(tpl).render(**pseries)
self.save_current_wkb(filename)
# save to pdf
if save_as.get('export_pdf', False):
filename_pdf = os.path.splitext(filename)[0] + '.pdf'
if ws_name is None:
logger.error(
"(export to pdf) no worksheet specified"
)
else:
self.save_current_wkb_as_pdf(filename_pdf, ws_name)
# re-open the template, re-activate the worksheet
self.close_current_wkb()
self.open_template_as_current_wkb()
if ws_name is not None:
Sheet(ws_name, wkb=self.wkb).activate()
else:
self.insert_one_dataframe(
df, render_block['cell_specification']
)
# TODO: include and save_as in this case
# re-activate former worksheet if needed
if ws_name is not None:
Sheet(ws2reactivate_name, wkb=self.wkb).activate()
def render(self):
"""Main render method."""
self.open_template_as_current_wkb()
save_render_blocks = [block for block in self.render_blocks
if 'save_as' in block.keys()]
for block in save_render_blocks:
self.apply_render_block(block)
self.wkb.close()
graded_folder_path = "/Users/evermal/Downloads/COMP248-Q-2"
student_id_regex = '.*([0-9]{8}).*'
# Create a connection with a new workbook.
template_workbook = Workbook('/Users/evermal/Documents/COMP248-Assigment2/examples/COMP248_Q_F2015_GradeSheet for A2.xlsx')
template_workbook.save('/Users/evermal/Documents/COMP248-Assigment2/examples/newCOMP248_Q_F2015_GradeSheet for A2.xlsx')
template_workbook.close()
# open nem workbook to work with
professors_workbook = Workbook('/Users/evermal/Documents/COMP248-Assigment2/examples/newCOMP248_Q_F2015_GradeSheet for A2.xlsx')
# go through each line of the table
for x in xrange(7,101):
# set the profesors spreedsheet as current workbook
professors_workbook.set_current()
student_id_cell = str(Range('B'+str(x)).value)
if student_id_cell is not None:
student_id_matcher = re.match(student_id_regex, student_id_cell)
if student_id_matcher is not None:
student_id = student_id_matcher.group(1)
print student_id
for root, dirs, files in os.walk(graded_folder_path):
for f in files:
if f == student_id+'.xlsx':
absolute_file_name = os.path.join(root, f)
print absolute_file_name
graded_workbook = Workbook(absolute_file_name)
graded_workbook.set_current()
a1 = str(Range('B8').value)
a2 = str(Range('B9').value)
class ExcelTemplateRenderer(object):
"""
A class to render an Excel template using data stored
in a database and given a specification file.
Parameters
----------
db_engine : object
the :class:`sqlalchemy.engine.Engine` object used to
connect and query the database.
template_name : str
path to the `xlsx` template file.
spec_filename : str
path to the specification file (yaml format).
output_dirname : str
path to the directory where to store the
generated excel file(s) (i.e., the rendered template(s)).
jinja_env : object or None
allow to provide a :class:`jinja2.Environment` object,
helpful when using custom filters in the specification file
(optional).
Notes
-----
The specification file (yaml format) consists of a list
of render blocks. TODO: documentation on the format.
"""
def __init__(self,
db_engine,
template_name,
spec_filename,
output_dirname,
jinja_env=None):
self.db_engine = db_engine
self.template_name = template_name
self.spec_filename = spec_filename
self.output_dirname = os.path.abspath(output_dirname)
if jinja_env is None:
self.jinja_env = jinja2.Environment()
else:
self.jinja_env = jinja_env
with open(self.spec_filename, 'r', encoding='utf-8') as f:
self.render_blocks = yaml.load(f)
os.makedirs(self.output_dirname, exist_ok=True)
logger.info("output directory is %s", self.output_dirname)
def open_template_as_current_wkb(self):
self.wkb = Workbook(os.path.abspath(self.template_name),
app_visible=False)
self.wkb.set_current()
def save_current_wkb(self, filename):
filepath = os.path.join(self.output_dirname, filename)
self.wkb.save(filepath)
logger.info("created %s", filepath)
def save_current_wkb_as_pdf(self, filename, worksheet_name):
filepath = os.path.join(self.output_dirname, filename)
try:
ws = Sheet(worksheet_name, wkb=self.wkb)
ws.xl_sheet.ExportAsFixedFormat(0, filepath)
logger.info("created %s", filepath)
except Exception as e:
logger.error("failed to export pdf")
logger.error("detailled error: %s - %s", e.__class__.__name__,
str(e))
def close_current_wkb(self):
self.wkb.close()
def insert_one_series(self, series, cell_specification):
"""
Populate the current workbook given a single
:class=:`pandas.Series` object.
"""
if not len(series):
return
# contiguous cells
#TODO: (use vertical and horizontal properties of xlwings)
# non-contiguous user-defined cells
for cs in cell_specification.get('cells', []):
ws = cs.get('worksheet') or Sheet.active(self.wkb).name
content = self.jinja_env.from_string(
cs['content']).render(**series)
logger.debug("insert content '%s' at cell '%s' in sheet '%s'",
content, cs['cell'], ws)
Range(ws, cs['cell']).value = content
def insert_one_dataframe(self, df, cell_specification):
"""
Populate the current workbook given a single
:class=:`pandas.DataFrame` object.
"""
if not len(df):
return
index = cell_specification.get('index', False)
header = cell_specification.get('header', False)
top_left_cell = cell_specification.get('top_left_cell', 'A0')
logger.debug(
"insert %d by %d rows/cols dataframe "
"at cell '%s' in sheet '%s'", len(df), len(df.columns),
str(top_left_cell),
Sheet.active(self.wkb).name)
Range(top_left_cell, index=index, header=header).value = df
def apply_render_block(self, render_block, query_context=None, **kwargs):
"""
Apply a single render block in the specification file.
- `query_context` (mappable or None) is a context used when
rendering the database query with jinja2 (optional).
- **kwargs is used to overwrite any key/value pair
in the render block.
"""
# override render_block key/val with kwargs
render_block.update(kwargs)
logger.info("processing render block '%s'",
render_block.get('name', '<unamed>'))
# query the DB into a pandas DataFrame
if query_context is None:
query_context = dict()
query_template = self.jinja_env.from_string(
render_block['query'].strip())
query = query_template.render(**query_context)
logger.debug("rendered query: \n'''\n%s\n'''", query)
df = pd.read_sql(query, self.db_engine)
logger.debug("query returned %d record(s)", len(df))
# TODO: calculate extra columns and add it to the DataFrame
# activate worksheet if provided
ws_name = render_block['cell_specification'].get('worksheet') or None
if ws_name is not None:
ws2reactivate_name = Sheet.active(self.wkb).name
Sheet(ws_name, wkb=self.wkb).activate()
# apply the render_block, apply recusively included blocks,
# and save the rendered workbook(s) if needed
apply_by_row = render_block.get('apply_by_row', False)
save_as = render_block.get('save_as', None)
if apply_by_row and save_as is not None:
logger.info("%d file(s) to generate", len(df))
if apply_by_row:
for row, pseries in df.iterrows():
self.insert_one_series(pseries,
render_block['cell_specification'])
for item in render_block.get('include', []):
if isinstance(item, dict):
block_name = item.pop('render_block')
override_vars = item
else:
block_name = item
override_vars = {}
block = [
b for b in self.render_blocks
if b['name'] == block_name
][0]
self.apply_render_block(block,
query_context=pseries,
**override_vars)
if save_as is not None:
tpl = save_as['filename']
filename = self.jinja_env.from_string(tpl).render(
**pseries)
self.save_current_wkb(filename)
# save to pdf
if save_as.get('export_pdf', False):
filename_pdf = os.path.splitext(filename)[0] + '.pdf'
if ws_name is None:
logger.error(
"(export to pdf) no worksheet specified")
else:
self.save_current_wkb_as_pdf(filename_pdf, ws_name)
# re-open the template, re-activate the worksheet
self.close_current_wkb()
self.open_template_as_current_wkb()
if ws_name is not None:
Sheet(ws_name, wkb=self.wkb).activate()
else:
self.insert_one_dataframe(df, render_block['cell_specification'])
# TODO: include and save_as in this case
# re-activate former worksheet if needed
if ws_name is not None:
Sheet(ws2reactivate_name, wkb=self.wkb).activate()
def render(self):
"""Main render method."""
self.open_template_as_current_wkb()
save_render_blocks = [
block for block in self.render_blocks if 'save_as' in block.keys()
]
for block in save_render_blocks:
self.apply_render_block(block)
self.wkb.close()
MainFolder = 'C:\\Search_Result'
FolderPath = os.path.join(MainFolder, FolderName)
if not os.path.exists(FolderPath):
os.makedirs(FolderPath)
TargetPath = os.path.join(MainFolder,FolderName,XlsName)
CWPath = '\\\\ecsbks01\\swap\\DDD00\\virtualenv\\WinPython-32bit-2.7.10.2\\python-2.7.10\\Project_Evaluate_Excel\\Search_History'
NewSheetName=row[3]+row[2]
try:
wbTarget = Workbook(TargetPath)
Excel_Path = os.path.join(CWPath, 'Result-Output.xlsx')
wb = Workbook(Excel_Path)
#rename worksheet
Sheet('Result',wkb=wb).name=NewSheetName
wb.set_current()
#Copy All Range
AllRRow = len(Range('A1').vertical.value)
AllRCol = len(Range('A1').horizontal.value)
print AllRCol,AllRRow
RangeLimit = ('A1:B%d' % AllRRow)
TempData = Range(RangeLimit).value
wbTarget.set_current()
lastSheetVal = Sheet.count()
OriginSArr=[]
for SheetName in xrange(1,lastSheetVal+1):
OriginSArr.append(Sheet(SheetName).name)
if NewSheetName not in OriginSArr:
print "I should Add new array"
Sheet.add('abc',after=lastSheetVal)
Sheet('abc').name=NewSheetName
import time
import shutil
import codecs
import subprocess
import pexpect
import sys
from xlwings import Workbook, Sheet, Range, Chart
password = sys.argv[1]
connection = None
# connect to the database
connection = psycopg2.connect(host='localhost', port='5432', database='comment_classification', user='******', password= password)
cursor = connection.cursor()
final_workbook = Workbook('/Users/evermal/Downloads/technical_debt_review.xls')
# go through each line of the table
for x in xrange(2,249):
# set the profesors spreedsheet as current workbook
final_workbook.set_current()
comment_id_cell = str(Range('A'+str(x)).value).replace('.0', '')
if comment_id_cell is not None:
final_classification_cell = Range('E'+str(x)).value
# print comment_id_cell
# print final_classification_cell
# print "update significative_sample set reviewerclassification = '"+final_classification_cell+"' where reviewer= 'Sultan' and processedcommentid ="+comment_id_cell
cursor.execute("update significative_sample set reviewerclassification = '"+final_classification_cell+"' where reviewer= 'Sultan' and processedcommentid ="+str(comment_id_cell))
connection.commit()
