How to add a Defined Name to a cell

[bjenkins]

Can someone provide me a SIMPLE working example of how to setup and define a name on a cell?

[jason]

This is just:

Syntax: [ Download ] [ Hide ]

Syntax: [ Download ] [ Show ]

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 ]

Syntax: [ Download ] [ Show ]

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

[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?

[jason]

OK, so reviewing the basics, each spreadsheet package has a workbook part, and some number of worksheet parts.

Syntax: [ Download ] [ Hide ]

Syntax: [ Download ] [ Show ]

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 ]

Syntax: [ Download ] [ Show ]

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

[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.