Page 1 of 1

How to add a Defined Name to a cell

PostPosted: Sat Feb 09, 2019 2:30 am
by bjenkins
Can someone provide me a SIMPLE working example of how to setup and define a name on a cell?

Re: How to add a Defined Name to a cell

PostPosted: Mon Feb 11, 2019 7:21 pm
by jason
This is just:

Syntax: [ Download ] [ Hide ]
Using xml Syntax Highlighting
    <definedName name="_sheet_scope_xyz" localSheetId="0">Sheet1!$C$4</definedName>
    <definedName name="_workbook_scope_abc" comment="A comment">Sheet1!$B$2</definedName>
Parsed in 0.001 seconds, using GeSHi

in your workbook part.

From that example, the docx4j webapp at generates:

Syntax: [ Download ] [ Hide ]
Using java Syntax Highlighting
org.xlsx4j.sml.ObjectFactory smlObjectFactory = new org.xlsx4j.sml.ObjectFactory();

DefinedNames definednames = smlObjectFactory.createDefinedNames();
    // Create object for definedName
    CTDefinedName definedname = smlObjectFactory.createCTDefinedName();
    definednames.getDefinedName().add( definedname);
        definedname.setLocalSheetId( new Long(0) );
        definedname.setName( "_sheet_scope_xyz");
        definedname.setValue( "Sheet1!$C$4");
    // Create object for definedName
    CTDefinedName definedname2 = smlObjectFactory.createCTDefinedName();
    definednames.getDefinedName().add( definedname2);
        definedname2.setName( "_workbook_scope_abc");
        definedname2.setValue( "Sheet1!$B$2");
        definedname2.setComment( "A comment");
Parsed in 0.016 seconds, using GeSHi

So please try something like that :-)

Re: How to add a Defined Name to a cell

PostPosted: Tue Feb 12, 2019 7:23 am
by bjenkins
The problem I'm facing is that all my previous work to make a spreadsheet all started with:

SpreadsheetMLPackage pkg = SpreadsheetMLPackage.createPackage();

but DefinedNames are located in Workbook object which can be created like so:

Workbook workbook = smlObjectFactory.createWorkbook();

The only way to SAVE a spreadsheet is to use a Saver object which takes a SpreadsheetMLPackage instance.

How do I CONNECT UP or make a LINK BETWEEN the Workbook instance and the SpreadsheetMLPackage instance?

Re: How to add a Defined Name to a cell

PostPosted: Tue Feb 12, 2019 1:38 pm
by jason
OK, so reviewing the basics, each spreadsheet package has a workbook part, and some number of worksheet parts.

Syntax: [ Download ] [ Hide ]
Using java Syntax Highlighting
SpreadsheetMLPackage pkg = SpreadsheetMLPackage.createPackage()
Parsed in 0.013 seconds, using GeSHi

automatically creates the workbook part:

Syntax: [ Download ] [ Hide ]
Using java Syntax Highlighting
        public static SpreadsheetMLPackage createPackage() throws InvalidFormatException {
                // Create a package
                SpreadsheetMLPackage xlsPack = new SpreadsheetMLPackage();

                try {
                        xlsPack.wb = new WorkbookPart();
Parsed in 0.015 seconds, using GeSHi

and you can access it from the pkg, with pkg.getWorkbookPart()

So to glue in what I posted yesterday, use pkg.getWorkbookPart().getContents().setDefinedNames(definednames);

Re: How to add a Defined Name to a cell

PostPosted: Wed Feb 13, 2019 1:37 am
by bjenkins
Thanks! That was the missing piece! I must have spent an hour stuck at pkg.getWorkbookPart. and then carefully going over the intellisense popup from my Eclipse IDE trying to work out how to connect to defined names. I did not know there was an intermediate function call to getContents(). I had hoped that SpreadsheetMLPackage factory would provide me the Worbook object (which now I know does), but was convinced it was not because when I uploaded a simple spreadsheet created using SpreadsheetMLPackage to the online webapp you provide, it looked quit different from a similar spreadsheet created from within Excel. I'll put those details in another post.