如何通过Java POI在Excel中移动图表的位置

问题描述

我想通过java POI在excel中添加行，并且尝试同时使用shiftRows()函数和createRow()函数

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

两种功能都可以在excel中添加行，但仍保留在图表位置下方，并且不会移动

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

我使用poi 3.9版

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

谢谢！

推荐答案

可以移动确定图表位置的工程锚.方法void insertRowsShiftShapes(Sheet sheet, int startRow, int n)对所有受行插入图纸影响的图形锚点执行此操作.

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.

要运行代码，请按 apache poi常见问题解答ooxml-schemas-1.3.jar a>

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

grepcode

示例: 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) {
  }
 }
}

这篇关于如何通过Java POI在Excel中移动图表的位置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！