Apache POI generate excel chart

Question

All:

Pretty new to Apache POI and excel VBA, how can I implement something like this in Java with Apache POI or any other java lib:

VBA code in Excel:

Sheets("Sheet 1").ChartObjects("Line Chart")
.Chart.Axes(xlValue).MaximumScale = Sheets("Sheet 1").Range("A37")

I can not find any Chart related API working like this, any thought?

Another way around is: My goal is to use Java automatically update cell A37 and give its value as "Line Chart"'s xAxis Max scale, is there a way now to directly call this VBA code thru POI?

Thanks,

Apache POI had limited support for charts last time I used it (which was a few years ago. See here for example stackoverflow.com/a/38920290/1356423. As the POI docs note though..."You can however create a chart in Excel using Named ranges, modify the chart data values using HSSF and write a new spreadsheet out. This is possible because POI attempts to keep existing records intact as far as possible." poi.apache.org/spreadsheet/limitations.html — Alan Hay
@AlanHay Thanks, but sorry that I am still very new to POI, could you give one example? I can see the line chart has been already there in that Sheet, I know how to update cell A37, but I just have no idea how can pass that value to that Chart object in Java — Kuan

Axel Richter Axel Richter · Accepted Answer · 2018-03-06T16:47:39

Answer for current latest stable version apache poi 3.17. Note XSSFChart is in development. So we should using XDDFChart for later versions.

You can get a List of XSSFCharts from the sheet's drawing via XSSFDrawing.getCharts. From that List get your needed XSSFChart. Then get that chart's axes via XSSFChart.getAxis. Then get the appropriate XSSFValueAxis from that List. And then change it's maximum via XSSFChartAxis.setMaximum.

Example:

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.xssf.usermodel.charts.*;

import java.io.FileInputStream;
import java.io.FileOutputStream;

class ReadAndWriteExcelXSSFChart {

 public static void main(String[] args) throws Exception {

  Workbook workbook = WorkbookFactory.create(new FileInputStream("WBWithLineChart.xlsm"));
  Sheet sheet = workbook.getSheetAt(0);

  Row row = sheet.getRow(36); if (row == null) row = sheet.createRow(36);
  Cell cell = row.getCell(0); if (cell == null) cell = row.createCell(0);

  cell.setCellValue(10);

  double valueA37 = cell.getNumericCellValue();

  Drawing drawing = sheet.getDrawingPatriarch();
  if (drawing instanceof XSSFDrawing) {
   for (XSSFChart chart : ((XSSFDrawing)drawing).getCharts()) {
System.out.println(chart.getPackagePart().getPartName().getName());
    if (chart.getPackagePart().getPartName().getName().endsWith("chart1.xml")) { //first chart in sheet
     for (XSSFChartAxis axis : chart.getAxis()) { //all axes
System.out.println(axis);
      if (axis instanceof XSSFValueAxis) { //value axis
       axis.setMaximum(valueA37); // maximum = same value as in A37
System.out.println(axis.getMaximum());
      }
     }
    }
   }
  }

  workbook.write(new FileOutputStream("WBWithLineChart.xlsm"));
  workbook.close();

 }
}

Apache POI generate excel chart

1 Answers