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"
Beispiel #2
0
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()
Beispiel #4
0
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'))
Beispiel #5
0
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)
Beispiel #6
0
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"))
Beispiel #7
0
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)
Beispiel #8
0
 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()
Beispiel #9
0
    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)
Beispiel #10
0
    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)
Beispiel #11
0
    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()
Beispiel #12
0
    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()
Beispiel #13
0
 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)
Beispiel #14
0
    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)
Beispiel #15
0
 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"
Beispiel #17
0
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)
Beispiel #19
0
    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()
Beispiel #20
0
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)
Beispiel #21
0
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()
Beispiel #23
0
    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)
Beispiel #24
0
 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()
Beispiel #26
0
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)
Beispiel #27
0
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
Beispiel #28
0
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()
Beispiel #29
0
 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  
     
Beispiel #30
0
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']
Beispiel #32
0
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')
Beispiel #35
0
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
Beispiel #36
0
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()
Beispiel #37
0
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
Beispiel #38
0
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
Beispiel #39
0
    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()
Beispiel #40
0
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)
Beispiel #41
0
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)
Beispiel #42
0
    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()
Beispiel #43
0
    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()
Beispiel #44
0
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)
Beispiel #45
0
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
Beispiel #46
0
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
Beispiel #47
0
    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)
Beispiel #48
0
    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)
Beispiel #49
0
    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
Beispiel #51
0
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()
Beispiel #52
0
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
Beispiel #53
0
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
Beispiel #54
0
    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)