java - ScatterChart 使用 LineMarker 作为 ScatterStyle 而不是仅使用 Marker

Question

构建 XSSFScatterChartData 并使用方法 XSSFChart.plot(ChartData data, ChartAxis...chartAxis) 填充它后，该图包含标记，但通过线..

我认为问题来自于默认设置 STScatterStyle.LINE_MARKER 的方法 XSSFScatterChartData.addStyle。

这是我用来生成图表的方法的副本:

private void setTrainingTimeGraph(Sheet trainingTimeSheet, Sheet resultsSheet) {
  Drawing drawing = trainingTimeSheet.createDrawingPatriarch();
  ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 5, 5, 20, 30);
  XSSFChart chart = (XSSFChart) drawing.createChart(anchor);
  ChartLegend legend = chart.getOrCreateLegend();
  legend.setPosition(LegendPosition.TOP_RIGHT);
  chart.setTitleText("Training time over Fscore");
  XSSFScatterChartData data = chart.getChartDataFactory().createScatterChartData();
  ValueAxis bottomAxis = chart.getChartAxisFactory().createValueAxis(AxisPosition.BOTTOM);
  ValueAxis leftAxis = chart.getChartAxisFactory().createValueAxis(AxisPosition.LEFT);
  setValueAxisTitle((XSSFChart) chart,0,"Fscore");
  setValueAxisTitle((XSSFChart) chart,1, "Training Time");
  leftAxis.setCrosses(AxisCrosses.AUTO_ZERO);
  bottomAxis.setCrosses(AxisCrosses.AUTO_ZERO);
  ChartDataSource<Number> xs = DataSources.fromNumericCellRange(resultsSheet, new CellRangeAddress(16, 29, 10, 10));
  ChartDataSource<Number> ys = DataSources.fromNumericCellRange(resultsSheet, new CellRangeAddress(16, 29, 18, 18));
  data.addSerie(xs, ys);
  chart.plot(data,bottomAxis, leftAxis);

}

更新

因此添加 @AxelRichter 代码以在我的散点图数据系列中设置为不填充:

...
data.addSerie(xs, ys);
chart.plot(data,bottomAxis, leftAxis);
//set line properties of first scatter chart data serie to no fill:
((XSSFChart)chart).getCTChart().getPlotArea().getScatterChartArray(0).getSerArray(0)
    .addNewSpPr().addNewLn().addNewNoFill();

我设法摆脱了连接标记的线..终于!

但第二部分不是我想要的。让我更好地解释一下:

当我将鼠标悬停在散点图中的每个点时，会弹出一些文本(“label/x_value”，x_value ，y_value )。图例中的值与其“label/x_value”相同。我想为每个数据点和图例中的每个值设置其“label/x_value”。

提前致谢!

Answer 1

Excel 散点图的标记之间也始终有线条。如果您不想看到这条线，则必须将其填充颜色设置为无填充。

引用文献: CTScatterSer -> CTShapeProperties -> CTLineProperties -> CTLineProperties.addNewNoFill

为了拥有数据标签，该系列需要 CTDLbl设置。

完整示例:

import java.io.FileOutputStream;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.usermodel.charts.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFChart;
import org.apache.poi.ss.util.CellRangeAddress;

public class CreateExcelScatterChart {

 public static void main(String[] args) throws Exception {

  Workbook wb = new XSSFWorkbook();
  Sheet sheet = wb.createSheet("chart");
  final int NUM_OF_ROWS = 2;
  final int NUM_OF_COLUMNS = 20;

  Row row;
  Cell cell;
  for (int rowIndex = 0; rowIndex < 1; rowIndex++) {
   row = sheet.createRow((short) rowIndex);
   for (int colIndex = 0; colIndex < NUM_OF_COLUMNS; colIndex++) {
    cell = row.createCell((short) colIndex);
    cell.setCellValue(4*colIndex * (rowIndex + 1));
   }
  }
  for (int rowIndex = 1; rowIndex < NUM_OF_ROWS; rowIndex++) {
   row = sheet.createRow((short) rowIndex);
   for (int colIndex = 0; colIndex < NUM_OF_COLUMNS; colIndex++) {
    cell = row.createCell((short) colIndex);
    cell.setCellValue(Math.sin(Math.PI*colIndex/10*2));
   }
  }

  Drawing<?> drawing = sheet.createDrawingPatriarch();
  ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 3, 13, 20);

  Chart chart = drawing.createChart(anchor);
  ChartLegend legend = chart.getOrCreateLegend();
  legend.setPosition(LegendPosition.TOP_RIGHT);

  ScatterChartData data = chart.getChartDataFactory().createScatterChartData();

  ValueAxis bottomAxis = chart.getChartAxisFactory().createValueAxis(AxisPosition.BOTTOM);
  ValueAxis leftAxis = chart.getChartAxisFactory().createValueAxis(AxisPosition.LEFT);

  leftAxis.setCrosses(AxisCrosses.AUTO_ZERO);
  bottomAxis.setCrosses(AxisCrosses.AUTO_ZERO);

  ChartDataSource<Number> xs = DataSources.fromNumericCellRange(sheet, new CellRangeAddress(0, 0, 0, NUM_OF_COLUMNS - 1));
  ChartDataSource<Number> ys1 = DataSources.fromNumericCellRange(sheet, new CellRangeAddress(1, 1, 0, NUM_OF_COLUMNS - 1));

  ScatterChartSeries chartSerie = data.addSerie(xs, ys1);
  chartSerie.setTitle("My Title");

  chart.plot(data, bottomAxis, leftAxis);

  //set line properties of first scatter chart data serie to no fill:
  ((XSSFChart)chart).getCTChart().getPlotArea().getScatterChartArray(0).getSerArray(0)
   .addNewSpPr().addNewLn().addNewNoFill();

  //set data labels for first scatter chart data serie 
  ((XSSFChart)chart).getCTChart().getPlotArea().getScatterChartArray(0).getSerArray(0)
   .addNewDLbls()
   .addNewNumFmt().setFormatCode("0.0");
  ((XSSFChart)chart).getCTChart().getPlotArea().getScatterChartArray(0).getSerArray(0)
   .getDLbls().getNumFmt()
   .setSourceLinked(false);
  ((XSSFChart)chart).getCTChart().getPlotArea().getScatterChartArray(0).getSerArray(0)
   .getDLbls()
   .addNewShowLegendKey().setVal(false);
  ((XSSFChart)chart).getCTChart().getPlotArea().getScatterChartArray(0).getSerArray(0)
   .getDLbls()
   .addNewShowCatName().setVal(false);
  ((XSSFChart)chart).getCTChart().getPlotArea().getScatterChartArray(0).getSerArray(0)
   .getDLbls()
   .addNewShowSerName().setVal(false);
  ((XSSFChart)chart).getCTChart().getPlotArea().getScatterChartArray(0).getSerArray(0)
   .getDLbls()
   .addNewShowPercent().setVal(false);
  ((XSSFChart)chart).getCTChart().getPlotArea().getScatterChartArray(0).getSerArray(0)
   .getDLbls()
   .addNewShowBubbleSize().setVal(false);
  ((XSSFChart)chart).getCTChart().getPlotArea().getScatterChartArray(0).getSerArray(0)
   .getDLbls()
   .addNewShowVal().setVal(true);

  wb.write(new FileOutputStream("CreateExcelScatterChart.xlsx"));
  wb.close();

 }

}

此代码需要所有架构 ooxml-schemas-1.3.jar 的完整 jar，如 FAQ-N10025 中所述。 .

此代码使用当前最新的稳定版本 apache poi 版本 3.17 运行。注意:apache poi 的 Chart 代码目前正在高度开发中。因此该代码可能无法在以后的版本中工作。