DataNucleus supports file-based Excel datastores currently.

The following persistence properties will connect to a local file on your local machine

datanucleus.ConnectionURL=excel:file:myfile.xls

replacing "myfile.xls" with your filename, which can be absolute or relative.

So you create your PersistenceManagerFactory or EntityManagerFactory with these properties. Thereafter you have the full power of the JDO or JPA APIs at your disposal, for your Excel datastore.

Access Platform allows you to query the objects in the datastore using the following

• JDOQL - language based around the objects that are persisted and using Java-type syntax
• JPQL - language based around the objects that are persisted and using SQL-like syntax

When using JDO you can do the following to get hold of the native "HSSFWorkbook"/"XSSFWorkbook" object

    tx.begin();
    ...

    // Get hold of native connection
    JDOConnection jdoConn = pm.getDataStoreConnection();
    HSSFWorkbook doc = (HSSFWorkbook)jdoConn.getNativeConnection();
    ... (do something with the document)
    jdoConn.close(); // Hands it back to JDO
    
    tx.commit();

Sadly JPA doesn't have such a feature

Excel datastore support assumes that objects of a class are persisted to a particular "sheet" of an Excel spreadsheet. Similarly a field of a class is persisted to a particular "column" index of that "sheet". This provides the basis for Excel persistence. When persisting a Java object to an Excel spreadsheet clearly the user would like some control over what worksheet a class is persisted to, and to what column number a field is persisted. DataNucleus provides extension metadata tags to allow this control. Here's an example, using JDO XML metadata

<jdo>
    <package name="mydomain">
        <class name="Person" detachable="true" table="People">
            <field name="personNum">
                <column position="0"/>
            </field>
            <field name="firstName">
                <column position="1"/>
            </field>
            <field name="lastName">
                <column position="2"/>
            </field>
        </class>
    </package>
</jdo>

Note the following metadata usages (all are optional)

• The table defines the name of the worksheet to use for that class.
• The column defines the column name to use for the field.
• The position of the column defines the position in the worksheet of this column. This is new in JDO3.1 and is optional (the default is to put the fields in alphabetical order).

Here's the same example using JDO Annotations

@PersistenceCapable(table="People")
public class Person
{
    @Column(position=0)
    long personNum;

    @Column(position=1)
    String firstName;

    @Column(position=2)
    String lastName;
}

Here's the same example using JPA Annotations (with DataNucleus @Extension/@Extensions annotations)

@Entity
@Table(name="People")
public class Person
{
    @Id
    @ColumnPosition(0)
    long personNum;

    @ColumnPosition(1)
    String firstName;

    @ColumnPosition(2)
    String lastName;
}

Obviously a spreadsheet cannot store related objects directly since each object is a row of a particular spreadsheet table. DataNucleus gets around this by storing the String-form of the identity of the related object in the relation cell. We also support using @Embedded to embed an object within the worksheet of another object.

Some references that may be of some use

• A JDO With DataNucleus AccessPlatform using Excel and Eclipse Tutorial That Actually Works