使用 Apache poi 在 Stacked bar 上方显示 SUM 值 [英] Show SUM Value above Stacked bar with Apache poi

问题描述

我目前正在研究功能，即应该在 .pptx 文件中生成堆叠图表.

为此，我使用这里的代码:

这就是我需要的样子:

除了 StackOverflow 上的一些线程之外，我找不到任何文档或任何内容.你们对如何实现这一目标有一些想法吗?

编辑 1

在 Alex 的帮助下，我能够打印高于 BAR 的 SUM 值.现在我只需要摆脱这些 0 值:

编辑 2

Axel 提到了一个问题，导致显示零(来自 EDIT 1).Axel 在他的回答中编辑了代码，所以现在零不见了.

解决方案

你会如何使用 PowerPoint 做到这一点?我看到的唯一方法是使用堆叠条形图和折线图的组合，其中折线图显示总和值并设置为不可见.所以只有折线图的数据标签是可见的.How将总计标签添加到 Excel 中的堆积柱形图?.

使用当前的 apache poi 4.1.2 这可以使用新的 XDDF 东西来实现.对于 Excel 图表，我已经在 如何使用 apache poi 4.0.1 和 java 生成可编辑的堆积条形图?.

我还将展示一个 PowerPoint 图表的完整示例:

import java.io.*;导入 org.apache.poi.xslf.usermodel.*;导入 org.apache.poi.ss.util.*;导入 org.apache.poi.util.Units;导入 org.apache.poi.xddf.usermodel.*;导入 org.apache.poi.xddf.usermodel.chart.*;导入 java.util.*;公共类 CreatePowerPointStackedBarChartXDDFChart {public static void main(String[] args) 抛出异常 {试试 (XMLSlideShow slideShow = new XMLSlideShow()) {XSLFSlide 幻灯片 = slideShow.createSlide();//创建数据String[] 类别 = new String[]{KW1"、KW2"、KW3"、KW4"、KW5"、KW6"};int numOfPoints = 类别长度；双[][]值=新双[][] {新双[]{10d, 0d, 20d, 5d, 30d, 10d},新双[]{15d, 35d, 25d, 15d, 10d, 8d},新双[]{5d, 15d, 0d, 25d, 15d, 0d},新双[]{10d, 5d, 30d, 30d, 20d, 12d}};Double[] sums = new Double[numOfPoints];for (int i = 0; i < sums.length; i++) {双和 = 0;for (Double[] valueRow : values) {sum += valueRow[i];}总和[i] = 总和；}//创建图表XSLFChart 图表 = slideShow.createChart();//添加图表到幻灯片slide.addChart(chart, new java.awt.geom.Rectangle2D.Double(1d*Units.EMU_PER_CENTIMETER, 1d*Units.EMU_PER_CENTIMETER, 20d*Units.EMU_PER_CENTIMETER, 15d*Units.EMU_PER_CENTIMETER);//条形图//创建数据源String categoryDataRange = chart.formatRange(new CellRangeAddress(1, numOfPoints, 0, 0));XDDFDataSourcecategoryData = XDDFDataSourcesFactory.fromArray(categories, categoryDataRange, 0);列表<XDDFNumericalDataSource<Double>>valuesData = new ArrayList>();int c = 1;for (Double[] valueRow : values) {String valuesDataRange = chart.formatRange(new CellRangeAddress(1, numOfPoints, c, c));valuesData.add(XDDFDataSourcesFactory.fromArray(valueRow, valuesDataRange, c));C++;}//创建轴XDDFCategoryAxis bottomAxis = chart.createCategoryAxis(AxisPosition.BOTTOM);XDDFValueAxis leftAxis = chart.createValueAxis(AxisPosition.LEFT);leftAxis.setCrosses(AxisCrosses.AUTO_ZERO);//设置AxisCrossBetween，使左轴与类别之间的类别轴交叉.//否则第一个和最后一个类别正好在交叉点上，条形只有一半可见.leftAxis.setCrossBetween(AxisCrossBetween.BETWEEN);//创建图表数据XDDFChartData 数据 = chart.createData(ChartTypes.BAR, bottomAxis, leftAxis);((XDDFBarChartData)data).setBarDirection(BarDirection.COL);//堆积条形图((XDDFBarChartData)data).setBarGrouping(BarGrouping.STACKED);((XDDFBarChartData)data).setOverlap((byte)100);//创建系列如果 (valuesData.size() == 1) {//如果只有一个系列不为每个条形改变颜色((XDDFBarChartData)data).setVaryColors(false);} 别的 {//如果多个系列确实改变了系列的颜色((XDDFBarChartData)data).setVaryColors(true);}for (int s = 0; s < valuesData.size(); s++) {XDDFChartData.Series series = data.addSeries(categoriesData, valuesData.get(s));series.setTitle("Series"+(s+1), chart.setSheetTitle("Series"+(s+1), s+1));}//绘制图表数据图表(数据)；//添加数据标签for (int s = 0 ; s < valuesData.size(); s++) {chart.getCTChart().getPlotArea().getBarChartArray(0).getSerArray(s).addNewDLbls();chart.getCTChart().getPlotArea().getBarChartArray(0).getSerArray(s).getDLbls().addNewDLblPos().setVal(org.openxmlformats.schemas.drawingml.x2006.chart.STDLblPos.CTR);chart.getCTChart().getPlotArea().getBarChartArray(0).getSerArray(s).getDLbls().addNewNumFmt();chart.getCTChart().getPlotArea().getBarChartArray(0).getSerArray(s).getDLbls().getNumFmt().setSourceLinked(false);chart.getCTChart().getPlotArea().getBarChartArray(0).getSerArray(s).getDLbls().getNumFmt().setFormatCode("0;-0;");chart.getCTChart().getPlotArea().getBarChartArray(0).getSerArray(s).getDLbls().addNewShowVal().setVal(true);chart.getCTChart().getPlotArea().getBarChartArray(0).getSerArray(s).getDLbls().addNewShowLegendKey().setVal(false);chart.getCTChart().getPlotArea().getBarChartArray(0).getSerArray(s).getDLbls().addNewShowCatName().setVal(false);chart.getCTChart().getPlotArea().getBarChartArray(0).getSerArray(s).getDLbls().addNewShowSerName().setVal(false);chart.getCTChart().getPlotArea().getBarChartArray(0).getSerArray(s).getDLbls().addNewShowPercent().setVal(false);chart.getCTChart().getPlotArea().getBarChartArray(0).getSerArray(s).getDLbls().addNewShowBubbleSize().setVal(false);}//折线图c = values.length + 1;//创建数据源String sumDataRange = chart.formatRange(new CellRangeAddress(1, numOfPoints, c, c));XDDFNumericalDataSourcesumData = XDDFDataSourcesFactory.fromArray(sums, sumDataRange, c);//轴必须在那里，但必须不可见bottomAxis = chart.createCategoryAxis(AxisPosition.BOTTOM);bottomAxis.setVisible(false);leftAxis = chart.createValueAxis(AxisPosition.LEFT);leftAxis.setVisible(false);//设置正确的交叉轴bottomAxis.crossAxis(leftAxis);leftAxis.crossAxis(bottomAxis);数据 = chart.createData(ChartTypes.LINE, bottomAxis, leftAxis);XDDFChartData.Series series = data.addSeries(categoriesData, sumData);series.setTitle(sum", chart.setSheetTitle(sum", c));((XDDFLineChartData.Series)series).setSmooth(false);((XDDFLineChartData.Series)series).setMarkerStyle(MarkerStyle.NONE);//不显示线XDDFShapeProperties shapeProperties = new XDDFShapeProperties();shapeProperties.setLineProperties(new XDDFLineProperties(new XDDFNoFillProperties()));series.setShapeProperties(shapeProperties);//绘制图表数据图表(数据)；//更正 id 和 order，不能再从 0 开始，因为已经有柱状系列了chart.getCTChart().getPlotArea().getLineChartArray(0).getSerArray(0).getIdx().setVal(c);chart.getCTChart().getPlotArea().getLineChartArray(0).getSerArray(0).getOrder().setVal(c);//添加数据标签chart.getCTChart().getPlotArea().getLineChartArray(0).getSerArray(0).addNewDLbls();chart.getCTChart().getPlotArea().getLineChartArray(0).getSerArray(0).getDLbls().addNewDLblPos().setVal(org.openxmlformats.schemas.drawingml.x2006.chart.STDLblPos.T);chart.getCTChart().getPlotArea().getLineChartArray(0).getSerArray(0).getDLbls().addNewNumFmt();chart.getCTChart().getPlotArea().getLineChartArray(0).getSerArray(0).getDLbls().getNumFmt().setSourceLinked(false);chart.getCTChart().getPlotArea().getLineChartArray(0).getSerArray(0).getDLbls().getNumFmt().setFormatCode("0;-0;");chart.getCTChart().getPlotArea().getLineChartArray(0).getSerArray(0).getDLbls().addNewShowVal().setVal(true);chart.getCTChart().getPlotArea().getLineChartArray(0).getSerArray(0).getDLbls().addNewShowLegendKey().setVal(false);chart.getCTChart().getPlotArea().getLineChartArray(0).getSerArray(0).getDLbls().addNewShowCatName().setVal(false);chart.getCTChart().getPlotArea().getLineChartArray(0).getSerArray(0).getDLbls().addNewShowSerName().setVal(false);chart.getCTChart().getPlotArea().getLineChartArray(0).getSerArray(0).getDLbls().addNewShowPercent().setVal(false);chart.getCTChart().getPlotArea().getLineChartArray(0).getSerArray(0).getDLbls().addNewShowBubbleSize().setVal(false);//将输出写入文件尝试(FileOutputStream fileOut = new FileOutputStream(CreatePowerPointStackedBarChartXDDFChart.pptx")){幻灯片.写(文件输出)；}}}}

I'm currently working on functionality, that is supposed to generate stacked chart inside .pptx file.

For this I'm using code from here: java create a chart in a powerpoint using APACHE POI

I did some modifications. Mainly I set grouping to Stacked and overlap to 100 (so the subBars looks like one bigger bar).

Now I need to display SUM of subBars values above each Bar. Aaaaand here comes my question. How can I achieve this (second pohoto below) using Apache Poi and openxmlformats.schemas.drawingml.x2006.chart?

One idea was to create another SubBar on top, make it transparent and set it's label to my desired SUM, but I cannot find a way, to set label (only Value and in this case, my transparent SubBar takes too much space and it just looks terrible - as in the photo below).

This is what I need it to look like:

I cannot find any documentation or anything apart from some threads here on StackOverflow. Do you guys have some idea, on how to achieve this?

EDIT 1

With help from Alex I was able to print SUM values above BARs. Now I just need to get rid of these 0 values:

EDIT 2

There was a problem mentioned by Axel, that caused zeroes to be displayed (from EDIT 1). Axel edited the code in his answer, so now zeroes are gone.

解决方案

How would you do that using PowerPoint? The only way I see is using a combination of stacked bar chart with line chart where the line chart displays the sum values and is set invisible. So only the data labels of the line chart are visible. The way as described in How to add total labels to stacked column chart in Excel?.

Using current apache poi 4.1.2 this can be achieved using the new XDDF stuff. For a Excel chart, I have shown that in How to generate editable Stacked-bar-chart using apache poi 4.0.1 and java?.

I will show a complete example for a PowerPoint chart as well:

import java.io.*;

import org.apache.poi.xslf.usermodel.*;

import org.apache.poi.ss.util.*;
import org.apache.poi.util.Units;

import org.apache.poi.xddf.usermodel.*;
import org.apache.poi.xddf.usermodel.chart.*;

import java.util.*;

public class CreatePowerPointStackedBarChartXDDFChart {

 public static void main(String[] args) throws Exception {
  try (XMLSlideShow slideShow = new XMLSlideShow()) {

   XSLFSlide slide = slideShow.createSlide();

   // create the data
   String[] categories = new String[]{"KW1", "KW2", "KW3", "KW4", "KW5", "KW6"};
   int numOfPoints = categories.length;

   Double[][] values = new Double [][] {
    new Double[]{10d, 0d, 20d, 5d, 30d, 10d},
    new Double[]{15d, 35d, 25d, 15d, 10d, 8d},
    new Double[]{5d, 15d, 0d, 25d, 15d, 0d},
    new Double[]{10d, 5d, 30d, 30d, 20d, 12d}
   };
   Double[] sums = new Double[numOfPoints];
   for (int i = 0; i < sums.length; i++) {
    double sum = 0;
    for (Double[] valueRow : values) {
     sum += valueRow[i];
    }
    sums[i] = sum;
   }

   // create the chart
   XSLFChart chart = slideShow.createChart();

   // add chart to slide
   slide.addChart(chart, new java.awt.geom.Rectangle2D.Double(1d*Units.EMU_PER_CENTIMETER, 1d*Units.EMU_PER_CENTIMETER, 20d*Units.EMU_PER_CENTIMETER, 15d*Units.EMU_PER_CENTIMETER));

   // bar chart

   // create data sources
   String categoryDataRange = chart.formatRange(new CellRangeAddress(1, numOfPoints, 0, 0));
   XDDFDataSource<String> categoriesData = XDDFDataSourcesFactory.fromArray(categories, categoryDataRange, 0);

   List<XDDFNumericalDataSource<Double>> valuesData = new ArrayList<XDDFNumericalDataSource<Double>>();
   int c = 1;
   for (Double[] valueRow : values) {
    String valuesDataRange = chart.formatRange(new CellRangeAddress(1, numOfPoints, c, c));
    valuesData.add(XDDFDataSourcesFactory.fromArray(valueRow, valuesDataRange, c));
    c++;
   }

   // create axis
   XDDFCategoryAxis bottomAxis = chart.createCategoryAxis(AxisPosition.BOTTOM);
   XDDFValueAxis leftAxis = chart.createValueAxis(AxisPosition.LEFT);
   leftAxis.setCrosses(AxisCrosses.AUTO_ZERO);
   // Set AxisCrossBetween, so the left axis crosses the category axis between the categories.
   // Else first and last category is exactly on cross points and the bars are only half visible.
   leftAxis.setCrossBetween(AxisCrossBetween.BETWEEN);

   // create chart data
   XDDFChartData data = chart.createData(ChartTypes.BAR, bottomAxis, leftAxis);
   ((XDDFBarChartData)data).setBarDirection(BarDirection.COL);
   // stacked bar chart
   ((XDDFBarChartData)data).setBarGrouping(BarGrouping.STACKED);
   ((XDDFBarChartData)data).setOverlap((byte)100);

   // create series
   if (valuesData.size() == 1) {
    // if only one series do not vary colors for each bar
    ((XDDFBarChartData)data).setVaryColors(false);
   } else {
    // if more than one series do vary colors of the series
    ((XDDFBarChartData)data).setVaryColors(true);
   }

   for (int s = 0; s < valuesData.size(); s++) {
    XDDFChartData.Series series = data.addSeries(categoriesData, valuesData.get(s));
    series.setTitle("Series"+(s+1), chart.setSheetTitle("Series"+(s+1), s+1));
   }

   // plot chart data
   chart.plot(data);

   // add data labels
   for (int s = 0 ; s < valuesData.size(); s++) {
    chart.getCTChart().getPlotArea().getBarChartArray(0).getSerArray(s).addNewDLbls();
    chart.getCTChart().getPlotArea().getBarChartArray(0).getSerArray(s).getDLbls()
     .addNewDLblPos().setVal(org.openxmlformats.schemas.drawingml.x2006.chart.STDLblPos.CTR);

    chart.getCTChart().getPlotArea().getBarChartArray(0).getSerArray(s).getDLbls().addNewNumFmt();
    chart.getCTChart().getPlotArea().getBarChartArray(0).getSerArray(s).getDLbls().getNumFmt()
     .setSourceLinked(false);
    chart.getCTChart().getPlotArea().getBarChartArray(0).getSerArray(s).getDLbls().getNumFmt()
     .setFormatCode("0;-0;");

    chart.getCTChart().getPlotArea().getBarChartArray(0).getSerArray(s).getDLbls().addNewShowVal().setVal(true);
    chart.getCTChart().getPlotArea().getBarChartArray(0).getSerArray(s).getDLbls().addNewShowLegendKey().setVal(false);
    chart.getCTChart().getPlotArea().getBarChartArray(0).getSerArray(s).getDLbls().addNewShowCatName().setVal(false);
    chart.getCTChart().getPlotArea().getBarChartArray(0).getSerArray(s).getDLbls().addNewShowSerName().setVal(false);
    chart.getCTChart().getPlotArea().getBarChartArray(0).getSerArray(s).getDLbls().addNewShowPercent().setVal(false);
    chart.getCTChart().getPlotArea().getBarChartArray(0).getSerArray(s).getDLbls().addNewShowBubbleSize().setVal(false);
   }


   // line chart
   c = values.length + 1;
   // create data source
   String sumDataRange = chart.formatRange(new CellRangeAddress(1, numOfPoints, c, c));
   XDDFNumericalDataSource<Double> sumData = XDDFDataSourcesFactory.fromArray(sums, sumDataRange, c);

   // axis must be there but must not be visible
   bottomAxis = chart.createCategoryAxis(AxisPosition.BOTTOM);
   bottomAxis.setVisible(false);
   leftAxis = chart.createValueAxis(AxisPosition.LEFT);
   leftAxis.setVisible(false);

   // set correct cross axis
   bottomAxis.crossAxis(leftAxis);
   leftAxis.crossAxis(bottomAxis);

   data = chart.createData(ChartTypes.LINE, bottomAxis, leftAxis);
   XDDFChartData.Series series = data.addSeries(categoriesData, sumData);
   series.setTitle("sum", chart.setSheetTitle("sum", c));
   ((XDDFLineChartData.Series)series).setSmooth(false);
   ((XDDFLineChartData.Series)series).setMarkerStyle(MarkerStyle.NONE);
   // don't show the line
   XDDFShapeProperties shapeProperties = new XDDFShapeProperties();
   shapeProperties.setLineProperties(new XDDFLineProperties(new XDDFNoFillProperties()));
   series.setShapeProperties(shapeProperties);

   // plot chart data
   chart.plot(data);

   // correct the id and order, must not start 0 again because there are bar series already
   chart.getCTChart().getPlotArea().getLineChartArray(0).getSerArray(0).getIdx().setVal(c);
   chart.getCTChart().getPlotArea().getLineChartArray(0).getSerArray(0).getOrder().setVal(c);
            
   // add data labels
   chart.getCTChart().getPlotArea().getLineChartArray(0).getSerArray(0).addNewDLbls();
   chart.getCTChart().getPlotArea().getLineChartArray(0).getSerArray(0).getDLbls()
    .addNewDLblPos().setVal(org.openxmlformats.schemas.drawingml.x2006.chart.STDLblPos.T);

   chart.getCTChart().getPlotArea().getLineChartArray(0).getSerArray(0).getDLbls().addNewNumFmt();
   chart.getCTChart().getPlotArea().getLineChartArray(0).getSerArray(0).getDLbls().getNumFmt()
    .setSourceLinked(false);
   chart.getCTChart().getPlotArea().getLineChartArray(0).getSerArray(0).getDLbls().getNumFmt()
    .setFormatCode("0;-0;");

   chart.getCTChart().getPlotArea().getLineChartArray(0).getSerArray(0).getDLbls().addNewShowVal().setVal(true);
   chart.getCTChart().getPlotArea().getLineChartArray(0).getSerArray(0).getDLbls().addNewShowLegendKey().setVal(false);
   chart.getCTChart().getPlotArea().getLineChartArray(0).getSerArray(0).getDLbls().addNewShowCatName().setVal(false);
   chart.getCTChart().getPlotArea().getLineChartArray(0).getSerArray(0).getDLbls().addNewShowSerName().setVal(false);
   chart.getCTChart().getPlotArea().getLineChartArray(0).getSerArray(0).getDLbls().addNewShowPercent().setVal(false);
   chart.getCTChart().getPlotArea().getLineChartArray(0).getSerArray(0).getDLbls().addNewShowBubbleSize().setVal(false);
 
   // Write the output to a file
   try (FileOutputStream fileOut = new FileOutputStream("CreatePowerPointStackedBarChartXDDFChart.pptx")) {
    slideShow.write(fileOut);
   }
  }
 }

}

这篇关于使用 Apache poi 在 Stacked bar 上方显示 SUM 值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！