Create Excel file using Java: Apache POI - PART - 2: Line Chart

This post is second part of the a series of post for creating excel files using Apache's POI library in Java.
If you don't know about basic creation of excel file using JAVA code, please refer the first part for that.

Link for first part: http://youthhworld.blogspot.com/2018/09/Java-Excel-Apache-POI-part1.html

In this post we will discuss about how to make Line charts for some sample data in excel file using Java code and Apache's POI library.

Interesting fact: Did you know? that excel file is nothing but it is a zipped package of XML files. The data of these XML files is represented in tabular form using these Excel or other similar software. Try it yourself: change an excel file's extension from .xlsx or .xls to ZIP and the open that file using WinZip WinRar or other available Zip file opener software. You will find the sheet and chart data in the xml file format present inside this sheet. It will also be having other metadata information store in this zip package.

Let's get started!

First of all create a sample data using in the excel file, either using java code or directly in the excel sheet as per your requirement. If you don't know how to write data in excel file using java please refer the link of part -1 post of this series given at the top of this post.

My sample data looks as below:

As mentioned in the first part also : In order to create charts we require full version of this jar dependency ooxml-schemas.jar

Here is the maven link to download jar file or include the maven dependency in you project: https://mvnrepository.com/artifact/org.apache.poi/ooxml-schemas/1.3

Tip: In order to relate the methods and classes in below code, first create a line chart manually in excel file for above data, save the file and rename the file by changing the extension to .zip. Open the zip -> xl ->charts -> open the chart.xml file for this chart you just made. You would see xml tags like image below (Click image to enlarge) which we can relate to the methods and classes used in the below code.

Creating Line Chart: 

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47

//=========================GRAPH CODE STARTS HERE============================ Drawing drawing = sheet.createDrawingPatriarch(); ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 5, 3, 8, 14); Chart chart = drawing.createChart(anchor); ChartLegend legend = chart.getOrCreateLegend(); legend.setPosition(LegendPosition.TOP_RIGHT); LineChartData data = chart.getChartDataFactory().createLineChartData(); // Use a category axis for the bottom axis. ChartAxis bottomAxis = chart.getChartAxisFactory().createCategoryAxis(AxisPosition.BOTTOM); ValueAxis leftAxis = chart.getChartAxisFactory().createValueAxis(AxisPosition.LEFT); leftAxis.setCrosses(AxisCrosses.AUTO_ZERO); ChartDataSource<Number> xs = DataSources.fromNumericCellRange(sheet, new CellRangeAddress(1, 13, 0, 0)); ChartDataSource<Number> ys1 = DataSources.fromNumericCellRange(sheet, new CellRangeAddress(1, 13, 0, 0)); data.addSeries(xs, ys1); chart.plot(data, bottomAxis, leftAxis); CTPlotArea plotArea = ((XSSFChart) chart).getCTChart().getPlotArea(); CTLineChart[] lines=plotArea.getLineChartArray(); for(CTLineChart line:lines){ CTLineSer[] sers =line.getSerArray(); CTShapeProperties prop=sers[0].addNewSpPr(); CTSolidColorFillProperties colors=prop.addNewSolidFill(); CTSRgbColor rgb=colors.addNewSrgbClr(); rgb.setVal(new byte[]{0,100,0}); // System.out.println("------------------------------"); } plotArea.getLineChartArray()[0].getSmooth(); CTBoolean ctBool = CTBoolean.Factory.newInstance(); ctBool.setVal(false); plotArea.getLineChartArray()[0].setSmooth(ctBool); for (CTLineSer ser : plotArea.getLineChartArray()[0].getSerArray()) { ser.setSmooth(ctBool); } //=============GRAPH CODE ENDS HERE================

Code Explanation:

• First of all the chart needs to be made on a drawing plot area, hence we have to create an object of class org.apache.poi.ss.usermodel.Drawing from the existing sheet object's createDrawingPatriarch() method
• Then we create an anchor point defining the top left corner point and bottom right corner point cell positions in the last four arguments of the constructor of object of class org.apache.poi.ss.usermodel.ClientAnchor class.
• last four arguments of constructor takes in first column, first row, last column last row.
• These anchor points are then considered for the drawing of chart, when we pass on the ClientAnchor object while creating chart using createChart() method of drawing object.

• Side -Note: Luckily due to implicit support for making Line charts using Apache POI, it is very easy to make these in comparision with Bar charts which we will cover in next part, which specially needs the XML file of chart to understand the hierarchy and flow to build chart using openxml-schema library attached with POI.

• Code line-8: As one would obviously guess - To make a chart we will have to create an object of class Chart on the drawing area object we created and we will pass on the ClientAnchor object as argument to indicate the size of chart to be built.
• line 9: As legends are part of the chart there is a method: getOrCreateLegend() in Chart class to build legend in that chart, it returns an object of class ChartLegend.
• Line 13-26: In this code section we have set the data to be considered for chart. The data to be displayed as X-axis and the data to be considered for Y-axis are assigned in this section of code.
• Here first LineChartData object is created, using the Chart class' getChartDataFactory method which returns ChartDataFactory class object which has a method createLineChartData to return LinceChartData class' object.
• To create the X-axis, that is the bottom axis, we have to get the ChartAxisFactory from chart object and create a category axis with Axis position as BOTTOM.
• Similarly to create Y-axis that is the value axis, we have to get the ChartAxisFacotry from char object and create value axis with Axis position as LEFT.
• The above code only creates the axis on chart and there are no values currently associated with them.
• To set the range of values to be considered for the above to axis we have to create ChartDataSource objects for both the axis. This is done using the DataSources Class' static method :  fromNumericCellRange() which takes in two parameters
1. sheet
2. CellRangeAddress(firstRow,lastRow,firstColumn,lastColumn)
• Then these ChartDataSource objects are added as series in our LineChartData object using addSeries method.
• This data is linked with the respective access using the plot() method of Chart class which takes in three parameters
1. The LineChartData object
2. The bottom/X-axis object
3. The left / value axis object
• Up to this point we have prepared our data to be shown, plot area to considered, category and values axis positions and alignments and respective data linkup in the Chart object.
• Code line 27 onwards: This data is then assigned some styling to the lines of line chart using the Shape Property method addSpPr and other smoothing methods.
• You can comment below in case there is any doubt in the code of line 27 onwards as I put this code in your self study section, for better self learning and adaptation of the documentation for these classes.

That's it for this post!

Feel free to share this with friends or include this post on your blog [Provided you give the link back here]

Happy Coding, Happy Learning.

Will meet in the next part of this post to look at creating Bar charts using Apache POI which is a bit complex then the Line chart code.