如何通过Java POI在excel中移动图表的位置 [英] How to move position of chart in excel by Java POI

问题描述

我想通过 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.

为了运行代码，需要 ooxml-schemas-1.3.jar 如apache poi 常见问题

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

对我来说 ooxml-schema 对象文档的一个很好的资源是 grepcode

A good resource for documentation of the ooxml-schema objects for me is 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中移动图表的位置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！