Apache POI XLSX Line Chart Java Example Program

In some of our blog posts earlier, we discussed how to generate charts in Excel workbook using Apache POI and JFreechart library. With POI 3.10, it is now possible to create charts directly in POI and in this post, let us discuss how to create a simple line chart in Apache POI with examples. The benefit of creating charts directly in POI are that we are not dependent on external libraries like JFreechart and we can also edit our charts on the fly if required. In the JFreechart approach, we had to embed the chart as a picture object into Excel that is no longer the case. So, download POI 3.10 and have all the JAR files required in your class path and we are ready to start with the basic Java example.

Java Example - Create Line Chart - Apache POI 


 import java.io.FileOutputStream;
 import org.apache.poi.ss.usermodel.*;
 import org.apache.poi.ss.usermodel.charts.*;
 import org.apache.poi.ss.util.CellRangeAddress;
 import org.apache.poi.xssf.usermodel.*;
 import org.apache.poi.xssf.usermodel.charts.*;
 /* Line Chart Example in Apache POI */
 public class LineChart
 {
   public static void main(String[] args)
     throws Exception
   {
                         /* Create a Workbook object that will hold the final chart */
                        XSSFWorkbook my_workbook = new XSSFWorkbook();
                         /* Create a worksheet object for the line chart. This worksheet will contain the chart */
                        XSSFSheet my_worksheet = my_workbook.createSheet("LineChart_Example");
                        
                        /* Let us now create some test data for the chart */
                        /* Later we can see how to get this test data from a CSV File or SQL Table */
                        /* We use a 4 Row chart input with 5 columns each */
                        for (int rowIndex = 0; rowIndex < 4; rowIndex++)
                {
                        /* Add a row that contains the chart data */
                        XSSFRow my_row = my_worksheet.createRow((short)rowIndex);
                        for (int colIndex = 0; colIndex < 5; colIndex++)
                {
                        /* Define column values for the row that is created */
                        XSSFCell cell = my_row.createCell((short)colIndex);
                        cell.setCellValue(colIndex * (rowIndex + 1));
                }
                }               
                        /* At the end of this step, we have a worksheet with test data, that we want to write into a chart */
                        /* Create a drawing canvas on the worksheet */
                        XSSFDrawing xlsx_drawing = my_worksheet.createDrawingPatriarch();
                        /* Define anchor points in the worksheet to position the chart */
                        XSSFClientAnchor anchor = xlsx_drawing.createAnchor(0, 0, 0, 0, 0, 5, 10, 15);
                        /* Create the chart object based on the anchor point */
                        XSSFChart my_line_chart = xlsx_drawing.createChart(anchor);
                        /* Define legends for the line chart and set the position of the legend */
                        XSSFChartLegend legend = my_line_chart.getOrCreateLegend();
                        legend.setPosition(LegendPosition.BOTTOM);     
                        /* Create data for the chart */
                        LineChartData data = my_line_chart.getChartDataFactory().createLineChartData();     
                        /* Define chart AXIS */
                        ChartAxis bottomAxis = my_line_chart.getChartAxisFactory().createCategoryAxis(AxisPosition.BOTTOM);
                        ValueAxis leftAxis = my_line_chart.getChartAxisFactory().createValueAxis(AxisPosition.LEFT);
                        leftAxis.setCrosses(AxisCrosses.AUTO_ZERO);     
                        /* Define Data sources for the chart */
                        /* Set the right cell range that contain values for the chart */
                        /* Pass the worksheet and cell range address as inputs */
                        /* Cell Range Address is defined as First row, last row, first column, last column */
                        ChartDataSource<Number> xs = DataSources.fromNumericCellRange(my_worksheet, new CellRangeAddress(0, 0, 0, 4));
                        ChartDataSource<Number> ys1 = DataSources.fromNumericCellRange(my_worksheet, new CellRangeAddress(1, 1, 0, 4));
                        ChartDataSource<Number> ys2 = DataSources.fromNumericCellRange(my_worksheet, new CellRangeAddress(2, 2, 0, 4));
                        ChartDataSource<Number> ys3 = DataSources.fromNumericCellRange(my_worksheet, new CellRangeAddress(3, 3, 0, 4));
                        /* Add chart data sources as data to the chart */
                        data.addSerie(xs, ys1);
                        data.addSerie(xs, ys2);
                        data.addSerie(xs, ys3);
                        /* Plot the chart with the inputs from data and chart axis */
                        my_line_chart.plot(data, new ChartAxis[] { bottomAxis, leftAxis });
                        /* Finally define FileOutputStream and write chart information */               
                        FileOutputStream fileOut = new FileOutputStream("xlsx-line-chart.xlsx");
                        my_workbook.write(fileOut);
                        fileOut.close();
   }
 }
Note that this example is for XLSX format, and you can easily change this to support the XLS format is required. Just change the class file names suitable to point to XLS format and you are done.

A sample output of this program is provided below:

POI-3.10- Java Line Chart Example Program - Output
POI-3.10- Java Line Chart Example Program - Output

Try your chart with this new approach and tell us how it works.

6 comments:

  1. hii.. i tried your example but I am not able to create line chart. I have successfully created scatter chart. I have imported all the the necessary jar files. The error while creating Line Chart is :
    Exception in thread "main" java.lang.Error: Unresolved compilation problems:
    The method createLineChartData() is undefined for the type XSSFChartDataFactory
    The method createCategoryAxis(AxisPosition) is undefined for the type XSSFChart

    Is this a bug in Apaache Poi?

    ReplyDelete
  2. I want have 2 y axis. How to do that?

    ReplyDelete
  3. I am also interested in how to plot a second y axis...

    ReplyDelete
  4. Above code is not working on my machine.
    I am getting same error mentioned above.
    I am getting error java.lang.AbstractMethodError for createLineChartData() method and for createCategoryAxis(AxisPosition) method.

    ReplyDelete
  5. Above code is not working on my machine.
    I am getting java.lang.AbstractMethodError for createLineChartData() method and createCategoryAxis(AxisPosition) method.

    ReplyDelete
  6. This is the code for creating the bar chart(actually creating column chart) using the NPOI 2.4.1 Nuget package.

    private XSSFWorkbook createBarchart()
    {

    XSSFWorkbook workbook = new XSSFWorkbook();
    XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet("WorkForceAnalytics");

    XSSFCellStyle styleHeader = (XSSFCellStyle)workbook.CreateCellStyle();
    styleHeader.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
    styleHeader.SetFont(getNewXSSFFont(workbook, styleHeader));

    XSSFCellStyle style = (XSSFCellStyle)workbook.CreateCellStyle();
    style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;


    XSSFRow row1 = (XSSFRow)sheet.CreateRow(0);
    row1.CreateCell(0).SetCellValue("");

    List lstdatase = new List(4);
    lstdatase.Add("A1");
    lstdatase.Add("B1");
    lstdatase.Add("C1");
    lstdatase.Add("D1");
    lstdatase.Add("E1");

    for (int i = 1; i < 5; i++)
    {
    row1.CreateCell(i).SetCellValue(lstdatase[i - 1].ToString());
    row1.GetCell(i).CellStyle = styleHeader;
    }

    int rowvalue = 1;
    List lstdata = new List(8);
    lstdata.Add("A");
    lstdata.Add("B");
    lstdata.Add("C");
    lstdata.Add("D");
    lstdata.Add("E");
    lstdata.Add("F");
    lstdata.Add("G");
    lstdata.Add("H");

    int d = 10;

    XSSFDrawing drawing = (XSSFDrawing)sheet.CreateDrawingPatriarch();
    XSSFClientAnchor anchor = (XSSFClientAnchor)drawing.CreateAnchor(0, 0, 0, 0, 6, 1, 15, 18);

    IChart chart = drawing.CreateChart(anchor);
    IChartLegend legend = chart.GetOrCreateLegend();
    legend.Position = (LegendPosition.TopRight);

    IBarChartData data = chart.ChartDataFactory.CreateBarChartData();

    IChartAxis bottomAxis = chart.ChartAxisFactory.CreateCategoryAxis(AxisPosition.Bottom);
    IValueAxis leftAxis = chart.ChartAxisFactory.CreateValueAxis(AxisPosition.Left);
    leftAxis.Crosses = AxisCrosses.AutoZero;
    leftAxis.SetCrossBetween(AxisCrossBetween.Between);

    IChartDataSource xs = DataSources.FromStringCellRange(sheet, new NPOI.SS.Util.CellRangeAddress(0, 0, 1, 5-1));

    for (int ii = 0; ii < 8; ii++)
    {
    XSSFRow rownew = (XSSFRow)sheet.CreateRow(rowvalue);
    rownew.CreateCell(0).SetCellValue(lstdata[ii].ToString());

    for (int i = 1; i < 5; i++)
    {
    rownew.CreateCell(i).SetCellValue(d);
    d++;
    rownew.GetCell(i).CellStyle = style;
    }
    rowvalue++;


    IChartDataSource ys = DataSources.FromNumericCellRange(sheet, new NPOI.SS.Util.CellRangeAddress(ii + 1, ii + 1, 1, 5 - 1));
    data.AddSeries(xs, ys).SetTitle(lstdata[ii].ToString());
    }

    chart.Plot(data, bottomAxis, leftAxis);

    sheet.ForceFormulaRecalculation = true;

    string fileName = "WorkforceAnalyticsReport.xlsx";
    using (var exportData = new MemoryStream())
    {
    Response.Clear();
    workbook.Write(exportData);
    Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", fileName));
    Response.BinaryWrite(exportData.ToArray());
    Response.End();
    }
    return workbook;
    }

    ReplyDelete