def DFtoExcel(df, FolderName, FileName): write_df = df.loc[:, ["FileName", "hyperlink", "Sheet Name"]] # Path Cell_Search_By_Key MainFolder = "C:\\Cell_Search_By_Key" FolderPath = os.path.join(MainFolder, FolderName) if not os.path.exists(FolderPath): os.makedirs(FolderPath) os.chdir(FolderPath) ExcelName = "%s.xlsx" % FileName writer = ExcelWriter(ExcelName) write_df.to_excel(writer, "Result", index=False) writer.save() # turn path into hyperlink Excel_Path = os.path.join(FolderPath, ExcelName) wb = Workbook(Excel_Path) # wb = Workbook.caller() checkArr = Range("B2").vertical.value i = 2 for check in checkArr: RangeName = "B%d" % (i) displayRange = "A%d" % (i) address = Range(RangeName).value display_name = Range(displayRange).value i += 1 try: Range(RangeName).add_hyperlink(address, text_to_display=address) except: pass wb.save() wb.close() return "FINISH"
class TestApplication: 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_screen_updating(self): self.wb.application.screen_updating = False assert_equal(self.wb.application.screen_updating, False) self.wb.application.screen_updating = True assert_equal(self.wb.application.screen_updating, True) def test_calculation(self): Range('A1').value = 2 Range('B1').formula = '=A1 * 2' self.wb.application.calculation = Calculation.xlCalculationManual Range('A1').value = 4 assert_equal(Range('B1').value, 4) self.wb.application.calculation = Calculation.xlCalculationAutomatic assert_equal(Range('B1').value, 8)
def write_array_to_xl_using_xlwings(ar, file, sheet): # Note: if file is opened In Excel, it must be first saved before writing # new output to it, but it may be left open in Excel application. wb = Workbook(file) Sheet(sheet).activate() Range(sheet, 'A1').value = ar.astype(str) wb.save()
def build_addins(): # transform code for addin use with open(os.path.join(par_dir, "xlwings", "xlwings.bas"), "r") as vba_module, \ open(os.path.join(this_dir, "xlwings_addin.bas"), "w") as vba_addin: content = vba_module.read().replace("ThisWorkbook", "ActiveWorkbook") content = content.replace('Attribute VB_Name = "xlwings"', 'Attribute VB_Name = "xlwings_addin"') vba_addin.write(content) # create addin workbook wb = Workbook() # remove unneeded sheets for sh in list(wb.xl_workbook.Sheets)[1:]: sh.Delete() # rename vbproject wb.xl_workbook.VBProject.Name = "xlwings" # import modules wb.xl_workbook.VBProject.VBComponents.Import(os.path.join(this_dir, "xlwings_addin.bas")) # save to xla and xlam wb.xl_workbook.IsAddin = True wb.xl_workbook.Application.DisplayAlerts = False # wb.xl_workbook.SaveAs(os.path.join(this_dir, "xlwings.xla"), FileFormat.xlAddIn) wb.xl_workbook.SaveAs(os.path.join(this_dir, "xlwings.xlam"), FileFormat.xlOpenXMLAddIn) wb.xl_workbook.Application.DisplayAlerts = True # clean up wb.close() os.remove(os.path.join(this_dir, 'xlwings_addin.bas'))
class TestApplication: 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_screen_updating(self): Application(wkb=self.wb).screen_updating = False assert_equal(Application(wkb=self.wb).screen_updating, False) Application(wkb=self.wb).screen_updating = True assert_equal(Application(wkb=self.wb).screen_updating, True) def test_calculation(self): Range('A1').value = 2 Range('B1').formula = '=A1 * 2' app = Application(wkb=self.wb) app.calculation = Calculation.xlCalculationManual Range('A1').value = 4 assert_equal(Range('B1').value, 4) app.calculation = Calculation.xlCalculationAutomatic app.calculate( ) # This is needed on Mac Excel 2016 but not on Mac Excel 2011 (changed behaviour) assert_equal(Range('B1').value, 8) Range('A1').value = 2 assert_equal(Range('B1').value, 4)
def build_addins(): # transform code for addin use with open(os.path.join(par_dir, "xlwings", "xlwings.bas"), "r") as vba_module, open( os.path.join(this_dir, "xlwings_addin.bas"), "w" ) as vba_addin: content = vba_module.read().replace("ThisWorkbook", "ActiveWorkbook") content = content.replace('Attribute VB_Name = "xlwings"', 'Attribute VB_Name = "xlwings_addin"') vba_addin.write(content) # create addin workbook wb = Workbook() # remove unneeded sheets for sh in list(wb.xl_workbook.Sheets)[1:]: sh.Delete() # rename vbproject wb.xl_workbook.VBProject.Name = "xlwings" # import modules wb.xl_workbook.VBProject.VBComponents.Import(os.path.join(this_dir, "xlwings_addin.bas")) # save to xla and xlam wb.xl_workbook.IsAddin = True wb.xl_workbook.Application.DisplayAlerts = False # wb.xl_workbook.SaveAs(os.path.join(this_dir, "xlwings.xla"), FileFormat.xlAddIn) wb.xl_workbook.SaveAs(os.path.join(this_dir, "xlwings.xlam"), FileFormat.xlOpenXMLAddIn) wb.xl_workbook.Application.DisplayAlerts = True # clean up wb.close() os.remove(os.path.join(this_dir, "xlwings_addin.bas"))
class TestApplication: 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_screen_updating(self): Application(wkb=self.wb).screen_updating = False assert_equal(Application(wkb=self.wb).screen_updating, False) Application(wkb=self.wb).screen_updating = True assert_equal(Application(wkb=self.wb).screen_updating, True) def test_calculation(self): Range('A1').value = 2 Range('B1').formula = '=A1 * 2' app = Application(wkb=self.wb) app.calculation = Calculation.xlCalculationManual Range('A1').value = 4 assert_equal(Range('B1').value, 4) app.calculation = Calculation.xlCalculationAutomatic app.calculate() # This is needed on Mac Excel 2016 but not on Mac Excel 2011 (changed behaviour) assert_equal(Range('B1').value, 8) Range('A1').value = 2 assert_equal(Range('B1').value, 4)
def test_two_wkb(self): wb2 = Workbook(app_visible=False, app_target=APP_TARGET) pic1 = Picture.add(sheet=1, name='pic1', filename=os.path.join(this_dir, 'sample_picture.png')) pic2 = Picture.add(sheet=1, name='pic1', filename=os.path.join(this_dir, 'sample_picture.png'), wkb=self.wb) assert_equal(pic1.name, 'pic1') assert_equal(pic2.name, 'pic1') wb2.close()
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_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_get_set_named_range(self): wb = Workbook() Range('A1').name = 'test1' assert_equal(Range('A1').name, 'test1') Range('A2:B4').name = 'test2' assert_equal(Range('A2:B4').name, 'test2') wb.close()
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 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_visible=False, app_target=APP_TARGET) Range('A1').value = 1 wb.close() shutil.move(dst, src)
def months_stat(self, q_months, year): q_df = self.period_calc(q_months, year) sum_q = self.period_stat(q_months, year) wb = Workbook() sh = Sheet.add("Summary", wkb = wb) row_flag = write_to_excel(q_df, sh = sh) row_flag = write_to_excel(sum_q, sh = sh, row_flag = row_flag) sh = Sheet.add("Master", wkb = wb) row_flag = write_to_excel(self.active_on_the_day(t_month_end(q_months[-1], year)) .data.pipe(ready_excel), sh = sh) sh1 = Sheet.add("Aggregate", wkb = wb) row_flag = write_to_excel('New Leases During the Period', sh = sh1) new_leases_list = self.new_analysis(t_month_start(q_months[0], year), t_month_end(q_months[-1], year)) .data.pipe(ready_excel) row_flag = write_to_excel(new_leases_list, sh = sh1, row_flag = row_flag) row_flag = write_to_excel('Expired During the Period', sh = sh1, row_flag = row_flag) expired_leases_list = self.old_analysis(t_month_start(q_months[0], year), t_month_end(q_months[-1], year)) .data.pipe(ready_excel) row_flag = write_to_excel(expired_leases_list, sh = sh1, row_flag = row_flag) r_expired_leases_list, r_new_leases_list, period_rate = self.renewal_a(q_months, year) sh1 = Sheet.add("Renewal", wkb = wb) row_flag = write_to_excel('Renewed Leases During the Period', sh = sh1) row_flag = write_to_excel('Original Leases', sh = sh1, row_flag = row_flag) row_flag = write_to_excel(r_expired_leases_list.pipe(ready_excel), sh = sh1, row_flag = row_flag) row_flag = write_to_excel('Renewed Leases', sh = sh1, row_flag = row_flag) row_flag = write_to_excel(r_new_leases_list.pipe(ready_excel), sh = sh1, row_flag = row_flag) row_flag = write_to_excel('Weighted Average Reversion Rate', sh = sh1, row_flag = row_flag) row_flag = write_to_excel(period_rate, sh = sh1, row_flag = row_flag) quarter = q_months[-1]//3 for tower in range(1,3): sh_new = Sheet.add("Tower {tower} {year} Q{quarter}".format(tower = tower, year = year, quarter = quarter), wkb = wb) row_flag = write_to_excel('Tower {tower} New Leases During the Period'.format(tower = tower), sh = sh_new) new_leases_list_T = new_leases_list.loc[new_leases_list['BLDG'] == tower].copy() row_flag = write_to_excel(new_leases_list_T, sh = sh_new, row_flag = row_flag) row_flag = write_to_excel('Tower {tower} Expired Leases During the Period'.format(tower = tower), sh = sh_new, row_flag = row_flag) expired_leases_list_T = expired_leases_list.loc[expired_leases_list['BLDG'] == tower].copy() row_flag = write_to_excel(expired_leases_list_T, sh = sh_new, row_flag = row_flag) Sheet('Sheet1').delete() wb.save("Operating Statistics Q{quarter} {year}".format(quarter = quarter, year = year)) #wb.close() return "OK"
def write_array_to_sheet(filepath, sheet, arr): path = _fullpath(filepath) # Workbook(path) seems to fail unless full path is provided if os.path.exists(path): wb = Workbook(path) Sheet(sheet).activate() Range("A1").value = arr wb.save() else: raise FileNotFound(path)
def reshape_forecasts_for_reporting(): Workbook.caller() r_data = forecast.generate_forecasts() pacing_data = forecast.merge_pacing_and_forecasts(r_data) tab = dr.tableau_pacing(pacing_data) forecast.output_forecasts(tab)
def savexlsMethod(self): print('Saving excel File') self.saveNameExcel = os.path.splitext(str(self.filepath).split("/")[-1])[0] wbOut = Workbook() Range('A1').value = ['Tempature [C]','Relative Humidity [%]','Dew Point [C]'] Range('A2').value = self.data wbOut.save()
def export_csv(streamp,startdt,enddt): # dc=DataClient() opt='EXCEL' # data=dc.load_data(streamp,startdt,enddt) if opt == 'EXCEL': wb=Workbook("test.xlsx") wb.caller() n = Range('Sheet1', 'B1').value # Write desired dimensions into Cell B1 rand_num = np.random.randn(n, n)
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 write_array_to_xl_using_xlwings(ar, file, sheet): # Note: if file is opened In Excel, it must be first saved before writing # new output to it, but it may be left open in Excel application. wb = Workbook(file) Sheet(sheet).activate() def nan_to_empty_str(x): return '' if type(x) == float and np.isnan(x) else x Range(sheet, 'A1').value = [[nan_to_empty_str(x) for x in row] for row in ar] wb.save()
def saveFile(self, accountManageFileName): """ Save the destination file. """ from xlwings import Workbook, Range wb = Workbook(accountManageFileName) for placeToWrite in self.infoToSave: sheet = placeToWrite[0] cell = placeToWrite[1] data = self.infoToSave[placeToWrite] Range(sheet, cell).value = data wb.save(accountManageFileName)
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 output_forecasts(pacing_data): pacing_data['Week'] = pacing_data['Date'].apply(lambda x: main.monday_week_start(x)) pacing_data = pd.pivot_table(pacing_data, index= ['Site', 'Tactic', 'Metric'], columns= ['Week'], values= 'value', aggfunc= np.sum).reset_index() wb = Workbook(main.dr_pacing_path()) Sheet('forecast_data').clear_contents() Range('forecast_data', 'A1', index= False).value = pacing_data wb.save() wb.close()
def xlo(df, filename=None): """ show pandas dataframe or series in excel sheet uses xlwings which allows writing to open file """ if not filename: filename = "_temp.xlsx" if not os.path.isfile(filename): wb = Workbook() Sheet("Sheet2").delete() Sheet("Sheet3").delete() else: wb = Workbook(filename) Sheet.add() Range("A1").value = df wb.save(filename)
def rand_numbers(): """ produces standard normally distributed random numbers with shape (n,n)""" wb = Workbook('Book1') # Creates a reference to the calling Excel file n = int(Range('Sheet1', 'B1').value) # Write desired dimensions into Cell B1 rand_num = np.random.randn(n, n) Range('Sheet1', 'C3').value = rand_num
def combobox(): """ This populates the ComboBox with the values from the database """ # Make a connection to the calling Excel file wb = Workbook.caller() # Place the database next to the Excel file db_file = os.path.join(os.path.dirname(wb.fullname), 'chinook.sqlite') # Database connection and creation of cursor con = sqlite3.connect(db_file, detect_types=sqlite3.PARSE_DECLTYPES | sqlite3.PARSE_COLNAMES) cursor = con.cursor() # Database Query cursor.execute("SELECT PlaylistId, Name FROM Playlist") # Write IDs and Names to hidden sheet Range('Source', 'A1').table.clear_contents() Range('Source', 'A1').value = cursor.fetchall() # Format and fill the ComboBox to show Names (Text) and give back IDs (Values) # TODO: implement natively in xlwings combo = "ComboBox1" wb.xl_workbook.ActiveSheet.OLEObjects(combo).Object.ListFillRange = \ 'Source!{}'.format(str(Range('Source', 'A1').table.xl_range.Address)) wb.xl_workbook.ActiveSheet.OLEObjects(combo).Object.BoundColumn = 1 wb.xl_workbook.ActiveSheet.OLEObjects(combo).Object.ColumnCount = 2 wb.xl_workbook.ActiveSheet.OLEObjects(combo).Object.ColumnWidths = 0 # Close cursor and connection cursor.close() con.close()
def read(*args): data = args[0] if len(args) == 1: wb = Workbook.caller() elif len(args) == 2: filename = args[1] wb = Workbook(filename) else: return None data.alpha = ExcelReaderFunctions.importAlpha() data.eps0 = ExcelReaderFunctions.importEpsilon() data.demand, data.numberOfTimePeriods, data.demandAsArray = ExcelReaderFunctions.importDemand() data.forwardCharVectors, data.numberOfForwardProducts = ExcelReaderFunctions.importForwardCharVectors() data.hpfcVectors, data.numberOfHpfcVectors = ExcelReaderFunctions.importHpfcVectors() data.forwardPrices = ExcelReaderFunctions.importForwardPrices() data.timePeriodNames = ExcelReaderFunctions.importHedgingPeriodNames() data.forwardNames = ExcelReaderFunctions.importForwardProductNames() data.name = filename.split('\\')[-1].split('/')[-1] #data.initialise() if len(args) == 2: #wb.close() pass return data, wb
class TestSheet: 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_activate(self): Sheet('Sheet2').activate() assert_equal(Sheet.active().name, 'Sheet2') Sheet(3).activate() assert_equal(Sheet.active().index, 3) def test_name(self): Sheet(1).name = 'NewName' assert_equal(Sheet(1).name, 'NewName') def test_index(self): assert_equal(Sheet('Sheet1').index, 1) def test_clear_content_active_sheet(self): Range('G10').value = 22 Sheet.active().clear_contents() cell = Range('G10').value assert_equal(cell, None) def test_clear_active_sheet(self): Range('G10').value = 22 Sheet.active().clear() cell = Range('G10').value assert_equal(cell, None) def test_clear_content(self): Range('Sheet2', 'G10').value = 22 Sheet('Sheet2').clear_contents() cell = Range('Sheet2', 'G10').value assert_equal(cell, None) def test_clear(self): Range('Sheet2', 'G10').value = 22 Sheet('Sheet2').clear() cell = Range('Sheet2', 'G10').value assert_equal(cell, None)
def FillPrice(row): #Open original excel wb = Workbook(Excel1) wb = Workbook.caller() #fill in the price RangeText=('%s%d' % (UnoPriceCol,row['Item Row Num1'])) Range(RangeText).value=row['Item Price2']
class TestChart: 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_chart_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_add_keywords(self): name = 'My Chart' chart_type = ChartType.xlLine Range('A1').value = chart_data chart = Chart.add(chart_type=chart_type, name=name, source_data=Range('A1').table) chart_actual = Chart(name) name_actual = chart_actual.name chart_type_actual = chart_actual.chart_type assert_equal(name, name_actual) if sys.platform.startswith('win'): assert_equal(chart_type, chart_type_actual) else: assert_equal(kw.line_chart, chart_type_actual) def test_add_properties(self): name = 'My Chart' chart_type = ChartType.xlLine Range('Sheet2', 'A1').value = chart_data chart = Chart.add('Sheet2') chart.chart_type = chart_type chart.name = name chart.set_source_data(Range('Sheet2', 'A1').table) chart_actual = Chart('Sheet2', name) name_actual = chart_actual.name chart_type_actual = chart_actual.chart_type assert_equal(name, name_actual) if sys.platform.startswith('win'): assert_equal(chart_type, chart_type_actual) else: assert_equal(kw.line_chart, chart_type_actual)
def tableau_data(): Workbook.caller() save_path = str(dr_pivot_path()) save_path = save_path[:save_path.rindex('\\')] ddr_data = dr.raw_pivot() d = dr.tableau_campaign_data(ddr_data) s = search.merge_data() tableau = d.append(s) tableau['Quarter'] = qquarter() if Range('merged', 'A1').value is None: chunk_df(tableau, 'merged', 'A1') # If data is already present in the tab, the two data sets are merged together and then copied into the data tab. else: past_data = pd.read_excel(dr_pacing_path(), 'merged', index_col=None) past_data = past_data[past_data['Campaign'] != 'Search'] appended_data = past_data.append(tableau) Sheet('merged').clear() chunk_df(appended_data, 'merged', 'A1') #Range('Sheet3', 'AT1').value = pd.to_datetime(ddr_data['Date'].max()) + datetime.timedelta(days= 1) wb = Workbook() Sheet('Sheet1').name = 'DDR Data' chunk_df(ddr_data, 'DDR Data', 'A1') wb.save(save_path + '\\' + 'DR_Raw_Data.xlsx') wb.close()
def publisher_performance_emails(): pacing_wb = Workbook.caller() performance.generate_publisher_tables(performance.publisher_tactic_data()) performance.generate_publisher_emails(performance.publisher_overall_data(), performance.publisher_contact_info(), performance.brand_remessaging()) Application(wkb=pacing_wb).xl_app.Run('Format_Tables')
def VideoDescription(): url = Range('Sheet1', 'C3').value video = pafy.new(url) wb = Workbook.caller() # Creates a reference to the calling Excel file videoTitle = video.title Range('Sheet1', 'G3').value = videoTitle
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 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
def test_add_wkb(self): # test use of add with wkb argument # Connect to an alternative test file and make Sheet1 the active sheet xl_file = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'test_range_1.xlsx') wb_2nd = Workbook(xl_file, app_visible=False, app_target=APP_TARGET) n_before = [sh.name for sh in Sheet.all(wkb=wb_2nd)] Sheet.add(name="default", wkb=wb_2nd) Sheet.add(name="after1", after=1, wkb=wb_2nd) Sheet.add(name="before1", before=1, wkb=wb_2nd) n_after = [sh.name for sh in Sheet.all(wkb=wb_2nd)] n_before.append("default") n_before.insert(1, "after1") n_before.insert(0, "before1") assert_equal(n_before, n_after) wb_2nd.close()
def cointegration(Y, X, sym1, sym2): """Stationarity test for X and Y.""" wk = Workbook.active() dates = [dt.datetime.strptime(d, '%Y-%m-%d') for d in X.index] X.index = dates Y.index = dates X = sm.add_constant(X) model = sm.OLS(Y, X) results = model.fit() print(results.summary()) print('Parameters: ', results.params) print('Standard errors: ', results.bse) #print('Predicted values: ', results.predict()) print('P Value_F: ', results.f_pvalue) print('P Value_T: ', results.pvalues) print('RSQ: ', results.rsquared) print('RSQ_Adj: ', results.rsquared_adj) z = results.resid stdev = np.std(z) up_std = 1.5 * stdev down_std = -1.5 * stdev mn = z.mean() ''' fig = plt.figure() plt.title('%s vs. %s' % (sym1, sym2)) plt.plot(X.index, z ) plt.axhline(up_std, color='r') plt.axhline(down_std, color='g') plt.axhline(y= mn, color='g') plt.show() ''' sym = sym1 + ' vs. ' + sym2 stat_arb = pd.DataFrame(z, index=X.index, columns=[sym]) stat_arb['up_std'] = up_std stat_arb['down_std'] = down_std stat_arb['mean'] = mn Range('parameters', 'graph_data').clear_contents() Range('parameters', 'stat').value = stat_arb.tail(500) #------------------------------------------ #http://statsmodels.sourceforge.net/devel/generated/statsmodels.tsa.stattools.adfuller.html ##http://www.quantstart.com/articles/Basics-of-Statistical-Mean-Reversion-Testing x = z result = ts.adfuller(x, 1) # maxlag is now set to 1 print(result)
def main(): wb = Workbook() # Creates a connection with a new workbook Range('A1').value = 'Foo 1' print Range('A1').value # 'Foo 1' Range('A1').value = [['Foo 1', 'Foo 2', 'Foo 3'], [10.0, 20.0, 30.0]] print Range('A1').table.value # or: Range('A1:C2').value # [['Foo 1', 'Foo 2', 'Foo 3'], [10.0, 20.0, 30.0]] print Sheet(1).name # 'Sheet1' chart = Chart.add(source_data=Range('A1').table)
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_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()
class TestChart: 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_chart_1.xlsx') self.wb = Workbook(xl_file1) self.wb.activate('Sheet1') def tearDown(self): self.wb.close() def test_add_keywords(self): name = 'My Chart' chart_type = ChartType.xlLine Range('A1').value = chart_data chart = Chart().add(chart_type=chart_type, name=name, source_data=Range('A1').table) chart_actual = Chart(name) name_actual = chart_actual.name chart_type_actual = chart_actual.chart_type assert_equal(name, name_actual) assert_equal(chart_type, chart_type_actual) def test_add_properties(self): name = 'My Chart' chart_type = ChartType.xlLine Range('Sheet2', 'A1').value = chart_data chart = Chart().add('Sheet2') chart.chart_type = chart_type chart.name = name chart.set_source_data(Range('Sheet2', 'A1').table) chart_actual = Chart('Sheet2', name) name_actual = chart_actual.name chart_type_actual = chart_actual.chart_type assert_equal(name, name_actual) assert_equal(chart_type, chart_type_actual)
def main(): wb = Workbook.caller() # User Inputs num_simulations = int(Range('E3').value) time = Range('E4').value num_timesteps = int(Range('E5').value) dt = time / num_timesteps # Length of time period vol = Range('E7').value mu = np.log(1 + Range('E6').value) # Drift starting_price = Range('E8').value perc_selection = [5, 50, 95] # percentiles (hardcoded for now) # Animation if Range('E9').value.lower() == 'yes': animate = True else: animate = False # Excel: clear output, write out initial values of percentiles/sample path and set chart source # and x-axis values Range('O2').table.clear_contents() Range('P2').value = [ starting_price, starting_price, starting_price, starting_price ] Chart('Chart 5').set_source_data(Range((1, 15), (num_timesteps + 2, 19))) Range('O2').value = np.round( np.linspace(0, time, num_timesteps + 1).reshape(-1, 1), 2) # Preallocation price = np.zeros((num_timesteps + 1, num_simulations)) percentiles = np.zeros((num_timesteps + 1, 3)) # Set initial values price[0, :] = starting_price percentiles[0, :] = starting_price # Simulation at each time step for t in range(1, num_timesteps + 1): rand_nums = np.random.randn(num_simulations) price[t, :] = price[t - 1, :] * np.exp((mu - 0.5 * vol**2) * dt + vol * rand_nums * np.sqrt(dt)) percentiles[t, :] = np.percentile(price[t, :], perc_selection) if animate: Range((t + 2, 16)).value = percentiles[t, :] Range((t + 2, 19)).value = price[t, 0] # Sample path if sys.platform.startswith('win'): wb.application.screen_updating = True if not animate: Range('P2').value = percentiles Range('S2').value = price[:, :1] # Sample path
def obtenerResultados(hoja='Actual', wb=None): if wb is None: wb = Workbook() numq = 0 aciertos = ('U18','Z18','AE18') msg = 'RESULTADOS QUINIELA - {0} \n \n'.format(hoja) msg = msg + 'Distribucion = {0}-{1}-{2} \n'.format(int(Range(hoja,"T3").value), int(Range(hoja,"Y3").value), int(Range(hoja,"AD3").value)) for quiniela in ('BD18','BU18','CL18'): numq += 1 msg = msg + '\n Q{0} ({1} Aciertos sin reducir) \n'.format(numq, int(Range(hoja,aciertos[numq-1]).value)) l = Range(hoja, quiniela).table.value for n in range(9,15): msg = msg + '{0} Aciertos reales: {1} \n'.format(n, l.count(n)) return msg
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() 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)
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 summarize_sales(): """ Retrieve the account number and date ranges from the Excel sheet """ # Make a connection to the calling Excel file wb = Workbook.caller() # Retrieve the account number and dates account = Range('B2').value start_date = Range('D2').value end_date = Range('F2').value # Output the data just to make sure it all works Range('A5').value = account Range('A6').value = start_date Range('A7').value = end_date
def playlist(): """ Get the playlist content based on the ID from the Dropdown """ # Make a connection to the calling Excel file wb = Workbook.caller() # Place the database next to the Excel file db_file = os.path.join(os.path.dirname(wb.fullname), 'chinook.sqlite') # Database connection and creation of cursor con = sqlite3.connect(db_file, detect_types=sqlite3.PARSE_DECLTYPES | sqlite3.PARSE_COLNAMES) cursor = con.cursor() # Get PlaylistId from ComboBox playlist_id = wb.xl_workbook.ActiveSheet.OLEObjects( "ComboBox1").Object.Value # Database query cursor.execute( """ SELECT t.Name AS Track, alb.Title AS Album, art.Name AS Artist, t.Composer FROM PlaylistTrack pt INNER JOIN Track t ON pt.TrackId = t.TrackId INNER JOIN Album alb ON t.AlbumId = alb.AlbumId INNER JOIN Artist art ON alb.ArtistId = art.ArtistId WHERE PlaylistId = ? """, (playlist_id, )) # Get the result and column names col_names = [col[0] for col in cursor.description] rows = cursor.fetchall() # Clear the sheet and write the column names and result to Excel Range('A9').table.clear_contents() Range('A9').value = col_names if len(rows): Range('A10').value = rows else: Range('A10').value = 'Empty Playlist!' # Close cursor and connection cursor.close() con.close()
def xl_fibonacci(): """ This is a wrapper around fibonacci() to handle all the Excel stuff """ # Create a reference to the calling Excel Workbook wb = Workbook.caller() # Get the input from Excel and turn into integer n = Range('B1').options(numbers=int).value # Call the main function seq = fibonacci(n) # Clear output Range('C1').vertical.clear_contents() # Return the output to Excel in column orientation Range('C1').options(transpose=True).value = seq
def test(): credentials = authorization.get_credentials(SCOPES, CLIENT_SECRET_FILE, APPLICATION_NAME) service = build('gmail', 'v1', http=credentials.authorize(Http())) results = service.users().labels().list(userId='me').execute() labels = results.get('labels', []) if not labels: print 'No labels found.' else: print 'Labels:' i = 1 for label in labels: try: wb = Workbook('Book1') Range((i, 1)).value = label['name'].encode("shift-jis") except: print "error" i += 1
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)