How to move position of chart in excel by Java POI

I want to add row in excel by java POI and I try with both shiftRows() function and createRow() function

both function can add row in excel but below chart position is remain and not move

I also like to move (shift down) the position of chart

I use poi version 3.9

Can anyone give me the advice or idea to move the position of that chart image

As the fact, the data range of chart also not changed. I need not only to move the position of charts but also need to increase the data range of chart

thanks!!

The shifting of the drawing anchors which determine the chart positions is possible. The method void insertRowsShiftShapes(Sheet sheet, int startRow, int n) does this for all drawing anchors which are affected of row inserting process into the sheet.

The correcting of the chart data ranges which are affected of the row inserting into the sheet is complicated as said already. It is not well tested and not ready yet. But I will provide it as a working draft. I hope it is a useful start point for further programming.

For running the code the ooxml-schemas-1.3.jar is needed as mentioned in apache poi FAQ

A good resource for documentation of the ooxml-schema objects for me is grepcode

Examples: CTTwoCellAnchor, CTPieChart, CTPieSer

import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;

import java.io.*;

import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTTwoCellAnchor;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTPieChart;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTPieSer;

import java.util.List;

class InsertRowsAboveChart {

 //a method for shift rows and shift all anchors in drawing below the shifted rows
 private static void insertRowsShiftShapes(Sheet sheet, int startRow, int n) {
  java.util.List<CTTwoCellAnchor> drawingAnchors = ((XSSFDrawing)sheet.getDrawingPatriarch()).getCTDrawing().getTwoCellAnchorList();
  for (CTTwoCellAnchor drawingAnchor : drawingAnchors) {
   int fromRow = drawingAnchor.getFrom().getRow();
   int toRow = drawingAnchor.getTo().getRow();
   if (fromRow >= startRow) {
    drawingAnchor.getFrom().setRow(fromRow + n);
    drawingAnchor.getTo().setRow(toRow + n);
   }
  }
  sheet.shiftRows(startRow, sheet.getLastRowNum(), n);
  correctDataRangesOfCharts(sheet, startRow, n);
 }

 //a method for correcting data ranges for charts which are affected of the shifted rows
 //!!working draft, not ready yet!!
 private static void correctDataRangesOfCharts(Sheet sheet, int startRow, int n) {
  java.util.List<XSSFChart> charts = ((XSSFDrawing)sheet.getDrawingPatriarch()).getCharts();
  for (XSSFChart chart : charts) {

   //pie charts
   java.util.List<CTPieChart> piecharts = chart.getCTChart().getPlotArea().getPieChartList();
   for (CTPieChart piechart : piecharts) {
    java.util.List<CTPieSer> pieseries = piechart.getSerList();
    for (CTPieSer pieserie : pieseries) {
     boolean strRefchanged = false;
     if (pieserie.getCat().isSetMultiLvlStrRef()) {
      String strRef = pieserie.getCat().getMultiLvlStrRef().getF();
      //todo: this only corrects the end row of the ranges, should also correct start row if affected
      int strRefEndRow = Integer.parseInt(strRef.substring(strRef.lastIndexOf('$') + 1));
      if (strRefEndRow >= startRow) {
       strRef = strRef.substring(0, strRef.lastIndexOf('$') +1) + (strRefEndRow + n);    
       pieserie.getCat().getMultiLvlStrRef().setF(strRef);
       strRefchanged = true;
      }
     } else if (pieserie.getCat().isSetStrRef()) {
      String strRef = pieserie.getCat().getStrRef().getF();
      int strRefEndRow = Integer.parseInt(strRef.substring(strRef.lastIndexOf('$') + 1));
      if (strRefEndRow >= startRow) {
       strRef = strRef.substring(0, strRef.lastIndexOf('$') +1) + (strRefEndRow + n);    
       pieserie.getCat().getStrRef().setF(strRef);
       strRefchanged = true;
      }
     }
     if (strRefchanged) {
      String numRef = pieserie.getVal().getNumRef().getF();
      int numRefEndRow = Integer.parseInt(numRef.substring(numRef.lastIndexOf('$') + 1));
      if (numRefEndRow >= startRow) {
       numRef = numRef.substring(0, numRef.lastIndexOf('$') +1) + (numRefEndRow + n);    
       pieserie.getVal().getNumRef().setF(numRef);
      }
     }
    }
   }
   //pie charts end

  }
 }

 public static void main(String[] args) {
  try {

   InputStream inp = new FileInputStream("Workbook.xlsx");
   Workbook wb = WorkbookFactory.create(inp);

   Sheet sheet = wb.getSheetAt(0);

   //sheet.shiftRows(3, 5, 4);
   insertRowsShiftShapes(sheet, 2, 4);

   FileOutputStream fileOut = new FileOutputStream("Workbook.xlsx");
   wb.write(fileOut);
   wb.close();

  } catch (InvalidFormatException ifex) {
  } catch (FileNotFoundException fnfex) {
  } catch (IOException ioex) {
  }
 }
}