如何使用 apache poi 4.0.1 和 java 生成可编辑的堆叠条形图? [英] How to generate editable Stacked-bar-chart using apache poi 4.0.1 and java?

查看:65
本文介绍了如何使用 apache poi 4.0.1 和 java 生成可编辑的堆叠条形图?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我想使用 Apache poi 4.0.1 和 Java 语言为 Excel 创建堆积条形图

  • 输出的 excel 文件的扩展名应该是 .xlsx
  • 生成的图表应该有图表标题和数据标签
  • 生成的图表还应该能够在每列顶部显示所有数据的总和(您可以看到每列的总计显示在黄色框中)
  • 您可以参考以下图片,更清楚地了解我要查找的内容.
<小时>

<小时><块引用>
  • 堆积条形图的数据

<小时>

日期类别 高 中 低10/01 3 0 3 010/02 3 0 2 110/03 3 0 2 110/04 4 1 2 110/05 11 1 7 310/08 14 1 10 310/09 15 1 11 310/10 15 1 11 310/11 15 0 11 410/12 8 0 6 2

解决方案

源自

<小时>

编辑 2019-03-01:

我稍微改进了我的代码.现在,它根据第一个类别值(在本例中为 A2 中的值)确定类别轴的类型.对于数据标签,明确设置了位置,并且明确设置了仅显示值而不显示图例键、类别名称或系列名称.

I want to create stacked bar chart using Apache poi 4.0.1 and Java language for Excel

  • The output excel file's extension should be .xlsx
  • The generated chart should have Chart title and Data Labels
  • The generated chart should also be able to show the total sum of all data at the top of each column (you can see total for each column is shown in yellow boxes)
  • You can refer following image for more clarity about what I'm looking for.


  • 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); 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); sets an overlap of 100%. Else the bars of the single series are not really stacked but remains side by side.

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.

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.

Following code needs a StackedBarAndLineChart.xlsx having the data provided in the question in first sheet in range A1:E11.

Code:

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);
 }
}

Result:


Edit 2019-03-01:

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屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆