使用Apache poi在堆叠条上方显示SUM值 [英] Show SUM Value above Stacked bar with Apache poi
问题描述
我目前正在研究功能,应该在.pptx文件中生成堆积图.
为此,我在这里使用代码:
这就是我需要的样子:
除了StackOverflow上的某些线程之外,我找不到任何文档或其他内容.你们对如何实现这一目标有想法吗?
编辑1
在Alex的帮助下,我能够在BAR上方打印SUM值.现在,我只需要摆脱这些0值:
编辑2
Axel提到了一个问题,导致显示零(来自EDIT 1).阿克塞尔(Axel)在他的答案中编辑了代码,所以现在零都消失了.
您将如何使用 PowerPoint
来做到这一点?我看到的唯一方法是将堆叠的条形图与折线图结合使用,其中折线图显示总和值并且设置为不可见.因此,只有折线图的数据标签可见.如何将总计标签添加到Excel中堆积的柱形图中?.
使用当前的 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 Using current I will show a complete example for a
这篇关于使用Apache poi在堆叠条上方显示SUM值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋! apache poi 4.1.2
,可以使用新的 XDDF
来实现.对于 Excel
图表,我已经在 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?.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?.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);
}
}
}
}