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

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

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