Create excel chart using Apache POI

Question

I need to create excel sheet from my Java code which contains charts like Bar chart, Line Chart etc using the Apache POI library. Is it possible? I am not able to find any useful code example for the same.

Is there any other alternative for this apart from POI library for Java?

Answer 1

You can only use Excel template to preserve chart with POI. It mean you create an Excel template file with chart in it and link the chart's datasource to one definedName,then you can load the template file from POI and using code to change the definedName. Current POI does not support to create Chart from scratch.

Answer 2

In poi-3.8 support for charts seems to be coming.

See this discussion.

And in particular, the example.

Answer 3

Is there any other alternative for this apart from POI library for Java?

There are a few other libraries too, but I'm not sure if the can write such a thing like a chart.

If you can use the newer Office versions (the ones that use the XML based format files), than you could use a different approach:

• create a an Excel file with a newer version of Office, containing the charts you need, and some dummy data.
• unzip that excel file and extract the XML files from inside. Of most interest will be the files xl\worksheets\sheet1.xml xl\worksheets\sheet2.xml or xl\worksheets\sheet3.xml (depending on what sheets were used)
• Take a look at the file format (it's more complicated than using POI), but it shouldn't be that hard to identify the "dummy" data you entered before.
• Use Velocity or FreeMarker to transform that xml file into a template (by replacing your dummy data with variables and macros what would produce the same result)
• Write a small program (just a few lines) that takes you real data, merges with the template, and packs everything back in a zip, but puts the *.xlsx extension.

I know that the above steps look a little complicated, but if you don't have too complicated Excel files, it should be easier than it looks.

Answer 4

The thread looks old and I do not know if you have already figured out a way or are still looking for one.

But here is what I would do. There is a free library called JFreeChart. You can use that to generate either a JPG or PNG file which you can then insert into excel file when you create it with Apache POI.

But the disadvantage with this method is that the data in the graph will not change dynamically when you change the data in the spreadsheet as is the case with Excel. Now that is something I cannot live with.

So I'm going to do some research now. I'm pretty sure that since the question has been asked there must be an addition of a feature in Apache POI or another elegant way of doing things. If I find any I will be sure to post my findings here.

---- UPDATE ----

In my research lasting for about an hour, I could only find one suitable library called SmartXLS (please google it, I could not post the link because i'm a newbie and the spam prevention mechanism kicked in) that is remotely close to what I would use. You can generate both the excel and the chart via a program. The website is very simple and I could not find any licensing information so I'm assuming it is free for personal and commercial use. I was able to download the library without any problem. I have not used it yet. Give it a shot and let us know how it works out.

Answer 5

This will be extremely complicated to do from scratch, as you will have to figure out what needs to go in the Excel file to create the charts. I would go a different route.

Create an Excel file that includes a macro that creates the Bar Chart etc for some data (using Excel in the usual way). Then use Apache POI to create a file with the data you want and start Excel from Java, executing the macro that creates the charts.

Answer 6

Here is the working example.

• I have data in JSON, and .xlsm template file with macro. - no chart, data or range name needed in excel

• Clones the template sheet every time it need to create a sheet with chart, places a required chart type at z1 and change default line chart which was inserted to appropriate chart type. Removes chart type from z1 to avoid multiple execution.

Hope it helps.