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,

Answer 1

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();

 }
}