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()