如何使用Apache POI 4.0.1和Java生成可编辑的堆叠条形图? [英] How to generate editable Stacked-bar-chart using apache poi 4.0.1 and java?
问题描述
我想使用Apache poi 4.0.1和Java语言(用于Excel)创建堆叠的条形图
- 输出excel文件的扩展名应为.xlsx
- 生成的图表应具有图表标题和数据标签
- 生成的图表还应该能够在每一列的顶部显示所有数据的总和(您可以在黄色框中看到每一列的总数)
- 您可以参考下图，以更清楚地了解我要寻找的内容.
- 堆积条形图的数据
- Data for stacked bar chart
Date Category High Medium Low
10/01 3 0 3 0
10/02 3 0 2 1
10/03 3 0 2 1
10/04 4 1 2 1
10/05 11 1 7 3
10/08 14 1 10 3
10/09 15 1 11 3
10/10 15 1 11 3
10/11 15 0 11 4
10/12 8 0 6 2
推荐答案
Derived from the bar chart and line chart examples in https://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/xssf/usermodel/examples/ we also can get a combined chart as requested here. But until now not all requested is possible only using the high level XDDF classes. Some correcting are necessary for which we need using the underlying low level ooxml-schemas-1.4 beans. So ooxml-schemas-1.4.jar needs to be in class path.
在以下代码中，对官方示例进行了以下更改:
In following code the following changes where made to the official examples:
leftAxis.setCrossBetween(AxisCrossBetween.BETWEEN);已设置.因此类别轴与笔划之间的值轴交叉，而不是笔划的中点.否则，第一类和最后一类的条形只有一半宽.
leftAxis.setCrossBetween(AxisCrossBetween.BETWEEN); is set. So category axis crosses the value axis between the strokes and not midpoint the strokes. Else the bars are only half wide visible for first and last category.
chart.getCTChart().getPlotArea().getBarChartArray(0).addNewOverlap().setVal((byte)100);设置100％的重叠.单个系列的其他酒吧并没有真正堆叠，而是并排放置.
chart.getCTChart().getPlotArea().getBarChartArray(0).addNewOverlap().setVal((byte)100); sets an overlap of 100%. Else the bars of the single series are not really stacked but remains side by side.
只能使用底层的ooxml-schemas-1.4底层豆添加数据标签.
Adding data labels is only possible using the underlying low level ooxml-schemas-1.4 beans.
附加折线图必须具有自己的轴，且轴彼此正确交叉.但是这些轴必须是不可见的.
The additional line chart must have it's own axes which are crossing each other properly. But those axes must be invisible.
因为添加到图表中时，折线图不了解有关已存在的条形图的信息，所以ID再次从0开始.但这对于组合图表是错误的.因此，我们需要正确的ID和顺序.不能再从0开始，因为已经有三个小节系列.
Because while adding to the chart, the line chart does not knows something about the already present bar chart, it's IDs starts with 0 again. But this is wrong for an combined chart. So we need correct the id and order. It must not start with 0 again because there are three bar series already.
以下代码需要StackedBarAndLineChart.xlsx，其中问题中提供的数据在第一张纸中位于A1:E11范围内.
Following code needs a StackedBarAndLineChart.xlsx having the data provided in the question in first sheet in range A1:E11.
代码:
import java.io.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.ss.util.*;
import org.apache.poi.xddf.usermodel.*;
import org.apache.poi.xddf.usermodel.chart.*;
public class StackedBarAndLineChart {
public static void main(String[] args) throws IOException {
try (FileInputStream in = new FileInputStream("StackedBarAndLineChart.xlsx");
XSSFWorkbook wb = (XSSFWorkbook)WorkbookFactory.create(in)) {
XSSFSheet sheet = wb.getSheetAt(0);
// determine the type of the category axis from it's first category value (value in A2 in this case)
XDDFDataSource date = null;
CellType type = CellType.ERROR;
Row row = sheet.getRow(1);
if (row != null) {
Cell cell = row.getCell(0);
if (cell != null) {
type = cell.getCellType();
if (type == CellType.STRING) {
date = XDDFDataSourcesFactory.fromStringCellRange(sheet, new CellRangeAddress(1, 10, 0, 0));
} else if (type == CellType.NUMERIC) {
date = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(1, 10, 0, 0));
} else if (type == CellType.FORMULA) {
type = cell.getCachedFormulaResultType();
if (type == CellType.STRING) {
date = XDDFDataSourcesFactory.fromStringCellRange(sheet, new CellRangeAddress(1, 10, 0, 0));
} else if (type == CellType.NUMERIC) {
date = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(1, 10, 0, 0));
}
}
}
}
if (date != null) { // if no type of category axis found, don't create a chart at all
XDDFNumericalDataSource<Double> high = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(1, 10, 2, 2));
XDDFNumericalDataSource<Double> medium = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(1, 10, 3, 3));
XDDFNumericalDataSource<Double> low = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(1, 10, 4, 4));
XDDFNumericalDataSource<Double> category = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(1, 10, 1, 1));
XSSFDrawing drawing = sheet.createDrawingPatriarch();
XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 6, 0, 16, 20);
XSSFChart chart = drawing.createChart(anchor);
XDDFChartLegend legend = chart.getOrAddLegend();
legend.setPosition(LegendPosition.RIGHT);
// bar chart
XDDFCategoryAxis bottomAxis = chart.createCategoryAxis(AxisPosition.BOTTOM);
XDDFValueAxis leftAxis = chart.createValueAxis(AxisPosition.LEFT);
leftAxis.setTitle("Number of defects");
leftAxis.setCrosses(AxisCrosses.AUTO_ZERO);
// category axis crosses the value axis between the strokes and not midpoint the strokes
leftAxis.setCrossBetween(AxisCrossBetween.BETWEEN);
XDDFChartData data = chart.createData(ChartTypes.BAR, bottomAxis, leftAxis);
XDDFChartData.Series series1 = data.addSeries(date, high);
series1.setTitle("high", new CellReference(sheet.getSheetName(), 0, 2, true, true));
XDDFChartData.Series series2 = data.addSeries(date, medium);
series2.setTitle("medium", new CellReference(sheet.getSheetName(), 0, 3, true, true));
XDDFChartData.Series series3 = data.addSeries(date, low);
series3.setTitle("low", new CellReference(sheet.getSheetName(), 0, 4, true, true));
chart.plot(data);
XDDFBarChartData bar = (XDDFBarChartData) data;
bar.setBarDirection(BarDirection.COL);
// looking for "Stacked Bar Chart"? uncomment the following line
bar.setBarGrouping(BarGrouping.STACKED);
// correcting the overlap so bars really are stacked and not side by side
chart.getCTChart().getPlotArea().getBarChartArray(0).addNewOverlap().setVal((byte)100);
solidFillSeries(data, 0, PresetColor.CORNFLOWER_BLUE);
solidFillSeries(data, 1, PresetColor.LIGHT_SALMON);
solidFillSeries(data, 2, PresetColor.LIGHT_GRAY);
// add data labels
for (int s = 0 ; s < 3; 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().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);
}
// line chart
// 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);
XDDFLineChartData.Series series4 = (XDDFLineChartData.Series)data.addSeries(date, category);
series4.setTitle("total", null);
series4.setSmooth(false);
series4.setMarkerStyle(MarkerStyle.STAR);
chart.plot(data);
// correct the id and order, must not start 0 again because there are three bar series already
chart.getCTChart().getPlotArea().getLineChartArray(0).getSerArray(0).getIdx().setVal(3);
chart.getCTChart().getPlotArea().getLineChartArray(0).getSerArray(0).getOrder().setVal(3);
solidLineSeries(data, 0, PresetColor.YELLOW);
// add data labels
chart.getCTChart().getPlotArea().getLineChartArray(0).getSerArray(0).addNewDLbls();
chart.getCTChart().getPlotArea().getLineChartArray(0).getSerArray(0).getDLbls()
.addNewSpPr().addNewSolidFill().addNewSrgbClr().setVal(new byte[]{(byte)255,(byte)255,0});
chart.getCTChart().getPlotArea().getLineChartArray(0).getSerArray(0).getDLbls()
.addNewDLblPos().setVal(org.openxmlformats.schemas.drawingml.x2006.chart.STDLblPos.CTR);
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);
}
// Write the output to a file
try (FileOutputStream fileOut = new FileOutputStream("StackedBarAndLineChartResult.xlsx")) {
wb.write(fileOut);
}
}
}
private static void solidFillSeries(XDDFChartData data, int index, PresetColor color) {
XDDFSolidFillProperties fill = new XDDFSolidFillProperties(XDDFColor.from(color));
XDDFChartData.Series series = data.getSeries().get(index);
XDDFShapeProperties properties = series.getShapeProperties();
if (properties == null) {
properties = new XDDFShapeProperties();
}
properties.setFillProperties(fill);
series.setShapeProperties(properties);
}
private static void solidLineSeries(XDDFChartData data, int index, PresetColor color) {
XDDFSolidFillProperties fill = new XDDFSolidFillProperties(XDDFColor.from(color));
XDDFLineProperties line = new XDDFLineProperties();
line.setFillProperties(fill);
XDDFChartData.Series series = data.getSeries().get(index);
XDDFShapeProperties properties = series.getShapeProperties();
if (properties == null) {
properties = new XDDFShapeProperties();
}
properties.setLineProperties(line);
series.setShapeProperties(properties);
}
}
结果:
编辑2019-03-01:
Edit 2019-03-01:
我的代码有所改进.现在，它根据其第一个类别值(在这种情况下为A2中的值)确定类别轴的类型.对于数据标签，显式设置位置，并且显式设置为仅显示值，但不显示图例键，类别名称或系列名称.
I have improved my code a little bit. It now determines the type of the category axis from it's first category value (value in A2 in this case). And for the data labels explicitly the position is set and it is explicitly set that only the values should shown but not legend key, category name or series name.
这篇关于如何使用Apache POI 4.0.1和Java生成可编辑的堆叠条形图?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
