Writing data to Excel from Java – the simple way

On the web you can find some blogs about how to use Apache POI to create an Excel spreadsheet. They typically contain reams of code just to add a simple table and some rows of data. It’s always amazing the amount of suffering people put themselves through to perform simple tasks. An example is this blog (there are others) which demonstrates some typical calls you will make using Apache POI to create a simple spreadsheet. Whilst that may represent what Apache POI requires, a user choosing to follow this way is inflicting unnecessary suffering on themselves, really. Read on …

Here’s how we would achieve the same thing as the referenced blog using JDO and DataNucleus. Firstly lets create a class that equates to a row of data in the table.

@PersistenceCapable(table="SampleDataSheet1")
public class SampleData
{
    @PrimaryKey @Column(name="Employer Name")
    String employerName;
    @Column(name="Designation")
    String designation;
    @Column(name="Country")
    String country;

    public SampleData(String e, String d, String c)
    {
       this.employerName = e;
       this.designation = d;
       this.country = c;
    }
}

Now we define our datastore persistence properties (datanucleus.properties)

javax.jdo.PersistenceManagerFactoryClass=
    org.datanucleus.jdo.JDOPersistenceManagerFactory
javax.jdo.option.ConnectionURL=excel:file:test.xls

Finally let’s do some persistence

PersistenceManagerFactory pmf = 
    JDOHelper.getPersistenceManagerFactory(“datanucleus.properties”);
PersistenceManager pm = pmf.getPersistenceManager();
Transaction tx = pm.currentTransaction();
tx.begin();
SampleData ds =
    new SampleData(“Intelligent User”, “Software Engineer”, “Bolivia”);
pm.makePersistent(ds);
tx.commit();

and this generates the same output that was shown in the blog, just that the code is now readable, shorter, and uses standardised APIs, never mind the fact that you could conceivably just persist the same data to RDBMS, XML, ODF, ODBMS, Cassandra and many more datastores with a simple change of that datastore URL. Additionally it allows you to focus on objects, which is likely why you chose Java in the first place. Why put yourself through more pain ?

[Needless to say, DataNucleus would also allow you to do the same using JPA]