16

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?

user902383
  • 8,420
  • 8
  • 43
  • 63
javdev
  • 794
  • 2
  • 10
  • 23
  • 1
    Here is a tutorial that explains [how to create chart in Excel with POI / JFreeChart](http://thinktibits.blogspot.com.au/2012/12/Java-POI-Excel-Create-Chart-Example-JFreeChart-Part-4.html) in Java. – user1933158 Dec 27 '12 at 21:35

6 Answers6

17

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.

liya
  • 782
  • 5
  • 6
  • 1
    Hi Liya, Thank you very much for the reply. I have tried it the way you have suggested. Dynamic named range(using offset function) is required in the template excel file (with empty chart having source data as that dynamic datasource). I have used the procedure as in the below link: 'http://www.youtube.com/watch?v=7le-m8YRP6M' – javdev Jul 30 '10 at 03:49
  • But I am facing following problem: After saving successfully a named range say 'testrange',whenever I click on any other cell in sheet and then check again the formula of 'testrange', it gets distored. for e.g. if I save it as "=OFFSET(A1,,,COUNTA(A:A),2)" and then click on some other cell. Now if again check the formula of 'testrange' it is shown something like: "=OFFSET(Sheet1!XFB11,,,COUNTA(Sheet1!XFB:XFB),2)" and this changes everytime I check it. I am facing this problem in Excel 2003 and 2007 also. Kindly suggest if I am doing something wrong in setting the dynamic named range. – javdev Jul 30 '10 at 03:50
  • Your formula use the relative address,please try the absolute address OFFSET($A$1,,,Count($A$A),2) – liya Jul 30 '10 at 10:07
  • @AbhinavMishra thanks a lot for info about dynamic named ranges. You saved me a lot of time and effort. – Piotr Sobczyk Mar 13 '12 at 13:16
6

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

See this discussion.

And in particular, the example.

Tshilidzi Mudau
  • 7,373
  • 6
  • 36
  • 49
prule
  • 2,536
  • 31
  • 32
  • 4
    (This question seems to be viewed quite a bit, so I thought adding a comment on a 3 years old answer can't hurt) Unfortunately, so far they have not added anything but scatter and line charts. Fortunately, the creation of new chart types does not seem to be all that difficult; building on ooxml-schemas (check the source code for ScatterChart in apache poi to see how), one can try to add new chart types. Thanks for those links @prule, helped me find that possibility! – G. Bach Apr 08 '15 at 14:30
4

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.

Adrian A.
  • 1,024
  • 8
  • 6
  • Hi Adrian, Thank you very much for the quick reply and valuable information. This approach seems reasonable but there are few concerns in it: 1. I want to support for both Excel 2003 and 2007. 2. The xml for chart generation is complex to understand. Is any other alternative to these problems. You have also mentioned that "There are a few other libraries too, but I'm not sure if the can write such a thing like a chart." --> Can you suggest a few (which are free of cost), which could provide this functionality. Thanks again, Abhinav – javdev Jul 29 '10 at 05:23
  • 2
    A few other excel libs: http://jexcelapi.sourceforge.net/ , http://sourceforge.net/projects/openxls/ , https://xlsql.dev.java.net/, than there are many Java to COM bridges that allow to use an Excel installation directly from Java, e.g. http://danadler.com/jacob/ (but there are many others too). Another option is to use OpenOffice (since it's quite easy to code to it's API from Java) and let OpenOffice export your results to the according Excel formats. – Adrian A. Jul 29 '10 at 12:44
2

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.

Karthic Raghupathi
  • 2,011
  • 5
  • 41
  • 57
  • Hi Karthik, Thanks for the reply. POI has the limitation as you figured out. And also I couldn't opt SmartXLS as it's not free. JFreeChart is extremely good API but I wanted dynamic excel charts. So, I used the approach as discussed in below posts with liya and dj. – javdev Oct 08 '10 at 05:51
  • Basically I used poi to dump the data into the excel file and used Excel macro to create the charts from that data whenever that excel is opened the first time. – javdev Oct 08 '10 at 05:52
  • Can you may be post a snippet of the macro that you use in excel to create the graph? I'm pretty sure I can google it. Seeing that you have a working solution, I would rather ask you and save some time than re-invent that wheel. Thanks for replying. – Karthic Raghupathi Oct 08 '10 at 20:37
1

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.

DJClayworth
  • 26,349
  • 9
  • 53
  • 79
  • Hi DJ, Thanks for the quick reply. Actually I am doing exactly the same way as you have suggested. But in this case, the speed of the macro execution is very slow in case of very large data, which is my concern. Also security issues are present in the macro usage. So, I want to create the charts using the POI library. Is it possible? Thanks and regards, Abhinav – javdev Jul 29 '10 at 05:10
  • I'm sure it is, but you may need to find out exactly what a file containing the charts looks like. I would still create an example with Excel and then reproduce it using POI. The full spec of the xlsx files are available, but extremely complicated. – DJClayworth Aug 03 '10 at 18:09
1

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.

Tshilidzi Mudau
  • 7,373
  • 6
  • 36
  • 49
Sumant
  • 276
  • 1
  • 12