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
  <definedNames>
    <definedName name="_sheet_scope_xyz" localSheetId="0">Sheet1!$C$4</definedName>
    <definedName name="_workbook_scope_abc" comment="A comment">Sheet1!$B$2</definedName>
  </definedNames>
 
Parsed in 0.001 seconds, using GeSHi 1.0.8.4


in your workbook part.

From that example, the docx4j webapp at http://webapp.docx4java.org/OnlineDemo/PartsList.html 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 1.0.8.4


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 1.0.8.4


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();
                        xlsPack.wb.setJaxbElement(
                                        Context.getsmlObjectFactory().createWorkbook()
                        );
                        xlsPack.addTargetPart(xlsPack.wb);     
 
Parsed in 0.014 seconds, using GeSHi 1.0.8.4


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.