new named ranges are not written to the file

[Rameez ali]

wb = load_workbook(filename="filename.xlsx")

range = openpyxl.workbook.defined_name.DefinedName('newRange', attr_text='namedRangeValue') 

wb.defined_names.append(range)

wb.save("filename.xlsx")

Code above adds a new range to workbook but does not persist in file. 

Seems like new ranges are not written to the file!

However modifying existing range persist in file after save.

Am i missing something? Thanks

[Charlie Clark]

On 12 Aug 2021, at 15:43, Rameez ali wrote:

wb = load_workbook(filename="filename.xlsx")

range = openpyxl.workbook.defined_name.DefinedName('newRange',

attr_text='namedRangeValue')

wb.defined_names.append(range)

wb.save("filename.xlsx")

Code above adds a new range to workbook but *does not persist *in file.

Seems like new ranges are not written to the file!

However modifying existing range persist in file after save.

Am i missing something? Thanks

What do you mean by "not persisted"? Have you looked at the generated file?

Based on your example:

from openpyxl import Workbook, load_workbook
wb = Workbook()
import openpyxl

range = openpyxl.workbook.defined_name.DefinedName('newRange',

 attr_text='namedRangeValue')

wb.defined_names.append(range)
wb.save("filename.xlsx"

)
wb = load_workbook("filename.xlsx")
wb.defined_names
<openpyxl.workbook.defined_name.DefinedNameList object>
Parameters:
definedName=[<openpyxl.workbook.defined_name.DefinedName object>
Parameters:
name='newRange', comment=None, customMenu=None, description=None, help=None, statusBar=None, localSheetId=None, hidden=None, function=None, vbProcedure=None, xlm=None, functionGroupId=None, shortcutKey=None, publishToServer=None, workbookParameter=None, attr_text='namedRangeValue']

Looks like persistence to me.

Charlie

--
Charlie Clark
Managing Director
Clark Consulting & Research
German Office
Waldlehne 23
Düsseldorf
D- 40489
Tel: +49-203-3925-0390
Mobile: +49-178-782-6226

[Rameez ali]

Thanks for the quick reply Charlie,  

Yes, after appending "defined name" save the file and when you reopen it in python to check if the new range exist, the new ranges are appended to defined names but does not show in excel file.

Excel file does not show new named ranges.  

If you want, i can post pictures too.

[Charlie Clark]

On 12 Aug 2021, at 16:20, Rameez ali wrote:

Thanks for the quick reply Charlie,

Yes, after appending "defined name" save the file and when you reopen it

in python to check if the new range exist, the new ranges are* appended to

defined names* but does not show in excel file.

Excel file *does not show *new named ranges.

If you want, i can post pictures too.

No need. You've defined a global range which is visible for the whole workbook. AFAIK however the Excel GUI only shows those available for particular (scoped) worksheets.

So, this is not an openpyxl problem.

[Rameez ali]

Have you checked Excel GUI, after adding global or scoped named ranges?

Newly added named ranges are not visible in any Excel GUI worksheet, global or private doesn't matter.

i'm using version 3.0.5. 

I have tested a dozen times at least. Let me test again to make sure.

I'll post the code below.

[Rameez ali]

That's my existing range

(Pdb) wb.defined_names

[

<openpyxl.workbook.defined_name.DefinedNameList object>

Parameters:

name='d_range4', comment=None, customMenu=None, description=None, help=None, statusBar=None, localSheetId=None, hidden=None, function=None, vbProcedure=None, xlm=None, functionGroupId=None, shortcutKey=None, publishToServer=None, workbookParameter=None, attr_text="'2.CAD NCCF'!$A$1"

]

Now adding a new scoped range with localSheetId = 2

range = DefinedName("NEWRANGE", attr_text="CAD.01'!$Z8:AD$9", localSheetId=2)

(wb.defined_name.append(range)

wb.defined_names.append(range)

wb.defined_names

[

<openpyxl.workbook.defined_name.DefinedNameList object>

Parameters:

name='d_range4', comment=None, customMenu=None, description=None, help=None, statusBar=None, localSheetId=None, hidden=None, function=None, vbProcedure=None, xlm=None, functionGroupId=None, shortcutKey=None, publishToServer=None, workbookParameter=None, attr_text="'2.CAD NCCF'!$A$1",

<openpyxl.workbook.defined_name.DefinedName object>

Parameters:

name='NEWRANGE', comment=None, customMenu=None, description=None, help=None, statusBar=None, localSheetId=2, hidden=None, function=None, vbProcedure=None, xlm=None, functionGroupId=None, shortcutKey=None, publishToServer=None, workbookParameter=None, attr_text="CAD.01'!$Z8:AD$9"

]

Now adding global range

(Pdb) range = DefinedName("GLOBALRANGE", attr_text="CAD.01'!$Z8:AD$9")

(Pdb) wb.defined_names.append(range)

wb.defined_names

[

<openpyxl.workbook.defined_name.DefinedNameList object>

Parameters:

name='d_range4', comment=None, customMenu=None, description=None, help=None, statusBar=None, localSheetId=None, hidden=None, function=None, vbProcedure=None, xlm=None, functionGroupId=None, shortcutKey=None, publishToServer=None, workbookParameter=None, attr_text="'2.CAD NCCF'!$A$1",

<openpyxl.workbook.defined_name.DefinedName object>

Parameters:

name='NEWRANGE', comment=None, customMenu=None, description=None, help=None, statusBar=None, localSheetId=2, hidden=None, function=None, vbProcedure=None, xlm=None, functionGroupId=None, shortcutKey=None, publishToServer=None, workbookParameter=None, attr_text="CAD.01'!$Z8:AD$9", 

<openpyxl.workbook.defined_name.DefinedName object>

Parameters:

name='GLOBALRANGE', comment=None, customMenu=None, description=None, help=None, statusBar=None, localSheetId=None, hidden=None, function=None, vbProcedure=None, xlm=None, functionGroupId=None, shortcutKey=None, publishToServer=None, workbookParameter=None, attr_text="CAD.01'!$Z8:AD$9"

]

wb.save("range_test.xlsx")

None of the new ranges are showing in Excel GUI, just checked. Please let me know if it's working for you.

Thanks

[Rameez ali]

Just found the solution, If DefinedName object has space in name attribute or attr_text is invalid (does not conform to format specified by excel), it wont show in Excel GUI. 

There is no indication from either Excel GUI or openpyxl that the name or attr_text are invalid, only if you add them manually to Excel GUI, it shows you the error.

I would highly recommend adding validation to DefinedName object attributes

Thanks for your time.

[Charlie Clark]

On 12 Aug 2021, at 21:38, Rameez ali wrote:

Just found the solution, If DefinedName object has space in *name *attribute

or *attr_text *is invalid (does not conform to format specified by excel),

it wont show in *Excel GUI. *

There is *no indication *from either Excel GUI or openpyxl that the

name or *attr_text *are invalid, only if you add them manually to *Excel

GUI, it shows you the error.*

I would highly recommend adding validation to *DefinedName *object

attributes

Glad you got it working but, as I said, the problem is with Excel. The specification makes no such restriction and the value can be any kind of string, which makes validation nearly impossible. Openpyxl follows the specification as closely as possible, because this is at least documentd.