Create Excel file using Java: Apache POI - PART - 3: Bar Chart

Pre-requisite: If you haven't read the first two par or at least the first part of this series of posts and you don't have any prior knowledge to make simple excel files using java's Apache POI  library. Then please go ahead and have a look at them first. Below are the link for the same:

• Create Excel file using Java: Apache POI - PART - 1: Writing data 
• Create Excel file using Java: Apache POI - PART - 2: Line Chart

In this post we are going to see the code snippets on how to make Bar charts using Apache POI library in excel files. To better understand which classes and methods of those classes should be used in what hierarchy, please make a sample bar chart yourself on a sample data set in excel file using Microsoft word, and then change the file name extension to zip and open the file using WinZip or similar software and open the chart1.xml file as mentioned in the Part-2 article. This xml file is having the hierarchical structure of Bar chart and the nodes of this chart are defined as the class names in ooxml-schema library in order to make charts using java Apache poi.

Lets get started

Lets say I have the same kind of data set, which I had in second Post like below image

As explained in the Part 2 of this series, in order to make any type of chart we first need to make a Drawing object and create anchor for the plot area of the chart. Then we have to make chart object on top of this drawing object. Below is the code snippet for this.

1 2 3 4

Drawing drawing = sheet1.createDrawingPatriarch(); ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 4, 1, (aTDSize + 5) , 35); Chart chart = drawing.createChart(anchor);

The code from below for Bar chart creation is now some what different than that of the Line chart from Part -2 of this series.

If you have made a sample graph for the sample data set above and you have opened the XML file of chart

You can see the below xml portion in that file:

We just need to create this plot area region using the open xml library's classes. Which is quiet easy when we refer the above XML.

Lets start with creating an object of class  org.openxmlformats.schemas.drawingml.x2006.chart.CTChart . This object is the equivalent of <c:chart> as shown in the image above.

1 2 3

CTChart ctChart = ((XSSFChart)chart).getCTChart(); CTPlotArea ctPlotArea = ctChart.getPlotArea(); CTBarChart ctBarChart = ctPlotArea.addNewBarChart();

You can relate the above code and its hierarchy with that of the xml file code shown in the image above. This is how its intended to make the bar chart. We have create the CTPlotArea class object from CTChart object and then CTBarChart object from Plot area's object.




Now you can easily guess that in order to make the tags which are inside <c: barChart> tags we need to call up the CTBarChart methods for immediate  children tags and respective children class methods for their children methods.

Below is the code snippet for the rest of the code:

Code for <c:ser> couple tags inside <c:barChart>:

• In below code on we have given range for the String reference on series axis on line 5. But in the range we have written something like this SheetName!$FirstColumnName$RowNumber:$LastColumnName$LastRowNumber
• This is just to inform that what should be the values there. (Which is obvious)
• We have given the example on how it can be written, in the comment line on line 4. Which gives the range for Column B Row 2 to Column B Row 5th.
• Similarly the data range given on lines 10 and 14 can be given similarly and chosen data accordingly

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

CTBarSer ctBarSer = ctBarChart.addNewSer(); CTSerTx ctSerTx = ctBarSer.addNewTx(); CTStrRef ctStrRef = ctSerTx.addNewStrRef(); //Give range below in this kind of format ctStrRef.setF("Sheet1!$B$2$B$5"); ctStrRef.setF("SheetName!$FirstColumnName$RowNumber:$LastColumnName$LastRowNumber"); ctBarSer.addNewIdx().setVal(0); CTAxDataSource cttAxDataSource = ctBarSer.addNewCat(); ctStrRef = cttAxDataSource.addNewStrRef(); // ctStrRef.setF("Sheet1!$A$2:$A$5"); ctStrRef.setF("SheetName!$FirstColumnName$RowNumber:$LastColumnName$LastRowNumber"); CTNumDataSource ctNumDataSource = ctBarSer.addNewVal(); CTNumRef ctNumRef = ctNumDataSource.addNewNumRef(); // ctNumRef.setF("Sheet1!$B$2:$B$5"); ctNumRef.setF("SheetName!$FirstColumnName$RowNumber:$LastColumnName$LastRowNumber"); //at least the border lines in Libreoffice Calc ;-) ctBarSer.addNewSpPr().addNewLn().addNewSolidFill().addNewSrgbClr().setVal(new byte[] {0,0,0});

Code for <c:dLbls> data labels tag pair inside <c:barChart>:

1 2 3 4 5 6 7

CTDLbls dLbls = ctBarChart.addNewDLbls(); dLbls.addNewShowBubbleSize().setVal(false); dLbls.addNewShowLegendKey().setVal(false); dLbls.addNewShowCatName().setVal(false); dLbls.addNewShowSerName().setVal(false); dLbls.addNewShowPercent().setVal(false); dLbls.addNewShowVal().setVal(true);

Code for <c:axId> axis ids tags for category axis and value axis and the code for <c:catAx> category axis and <c:valAx> value axis inside <c:plotArea> tag:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

//telling the BarChart that it has axes and giving them Ids ctBarChart.addNewAxId().setVal(123456); ctBarChart.addNewAxId().setVal(123457); //cat axis CTCatAx ctCatAx = ctPlotArea.addNewCatAx(); ctCatAx.addNewAxId().setVal(123456); //id of the cat axis CTScaling ctScaling = ctCatAx.addNewScaling(); ctScaling.addNewOrientation().setVal(STOrientation.MIN_MAX); ctCatAx.addNewDelete().setVal(false); ctCatAx.addNewAxPos().setVal(STAxPos.B); ctCatAx.addNewCrossAx().setVal(123457); //id of the val axis ctCatAx.addNewTickLblPos().setVal(STTickLblPos.NEXT_TO); //val axis CTValAx ctValAx = ctPlotArea.addNewValAx(); ctValAx.addNewAxId().setVal(123457); //id of the val axis ctScaling = ctValAx.addNewScaling(); ctScaling.addNewOrientation().setVal(STOrientation.MIN_MAX); ctValAx.addNewDelete().setVal(false); ctValAx.addNewAxPos().setVal(STAxPos.L); ctValAx.addNewCrossAx().setVal(123456); //id of the cat axis ctValAx.addNewTickLblPos().setVal(STTickLblPos.NEXT_TO);

If you want to set a title for the chart you can do it using below code. This is optional and totaly depends on your requirement:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

CTTitle ctTitle = ctChart.addNewTitle(); CTTx tx = ctTitle.addNewTx(); if (tx.isSetStrRef()) { tx.unsetStrRef(); } CTTextBody rich = tx.addNewRich(); rich.addNewBodyPr(); // body properties must exist (but can be empty) rich.addNewLstStyle(); CTTextParagraph para = rich.addNewP(); CTTextParagraphProperties PPr = para.addNewPPr(); PPr.addNewDefRPr(); CTRegularTextRun run = para.addNewR(); run.setT("Weekly Downloads"); CTTextCharacterProperties cCPr = run.addNewRPr(); cCPr.setLang("en-IN"); cCPr.setSz(2800);

That's it. You just need to refer the XML and you can easily get to know the hierarchy in order to make this kind of utility to generate Excel file from Java's Apache POI library.



If you have any doubt in the code snippets of concept of this thing you can comment below, I will surely try to answer that. This Bar chart example is quiet rare and I learnt it by googling too much and thanks to stack overflow and some other sites.

Do share it with your friends as this is a very nice library to automate the something such as reports generation using Java in your projects.


Happy Coding, Happy reading, Enjoy, Have fun!