Is there a way to change the scope of a named range from workbook to worksheet using Python?-CodePudding

I created a named ranges on my Excel sheet using the function define_name()

 with pd.ExcelWriter(excel_broker, engine='xlsxwriter') as writer:
        # Get xlsxwriter objects
        workbook = writer.book
        header = workbook.add_format({'bold':True, 'border' : 1 })
        format_float = workbook.add_format({'num_format': '# ###'})

        takerMaker_client.to_excel(writer, sheet_name="Maker Taker", index_label = ["", "", ""], 
                                                        startrow=5)
        worksheet = writer.sheets['Maker Taker']
        worksheet.set_column('D:E', 20, format_float)
        workbook.define_name('Client', '=OFFSET(\'Maker Taker\'!$D$7,,,COUNTA(\'Maker Taker\'!$D$7:$D$1048576),)')

but when I try to create a chart on PowerPoint using the named range 'Client', I notice that it is linked to the name of my Excel File and not the name of my Excel sheet.

='ExcelFile_Name.xlsx'!Client

Is there a way to set the scope of my named range from 'Workbook' to 'Worksheet' on Python to get:

='Maker Taker'!Client

I tried the function worksheet.define_name instead of workbook.define_name but this function doesn't eist and there is no scope parameter in the function define_name

CodePudding user response：

The problem is that writer.book does represent the whole file, but its actual type and associated methods can depend on the underlying engine. As you are using workbook.define_name which looks like the xlswriter method, I shall assume that your Pandas instance uses the xlswriter engine.

In that case, the xlswriter documentation says:

It is also possible to define a local/worksheet name by prefixing it with the sheet name using the syntax 'sheetname!definedname'

So you should use:

workbook.define_name("'Maker Taker'!Client",
    "=OFFSET('Maker Taker'!$D$7,,,COUNTA('Maker Taker'!$D$7:$D$1048576),)")

Beware: untested...