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]

Advertisements
This entry was posted in Excel, JDO. Bookmark the permalink.

5 Responses to Writing data to Excel from Java – the simple way

  1. TapaGeuR says:

    As you said, this is a REALLY simple example of how to generate XLS with POI. String text are way simpler then the numeric values to represent (like percent and currency).

    Like

  2. andy says:

    Simply a response to a blog “demonstrating” POI. In the same way JDO (or JPA) can be used to persist numerics and many other Java types (including relation fields) into an XLS/OOXML spreadsheet (or ODF too for that matter). The only significant thing that we need to add (to DataNucleus) is allow some control over formatting of the cells, but that is easily doable … just need some time 😉

    Like

  3. TapaGeuR says:

    It's just that as soon as you start playing with cell format, my recommendation would be to also start considering localization for the cell format. You want the currency to be displayed properly for your users and that is where your unit tests will come handy!

    Like

  4. Anonymous says:

    Hello! How can I write xls? I mean, What is the code for write and save the changes?

    Thank you!

    Esteban From Argentina

    Like

  5. andy says:

    The above blog tells you what the code is to write (makePersistent and tx.commit). Have you tried it ? Another tutorial that does the same thing is at
    http://www.peternewhook.com/2011/01/jdo-datanucleus-excel-eclipse/

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s