Krzysztof Majewski Krzysztof Majewski - 2 months ago 35
Apache Configuration Question

Apache poi Excel line chart points

I am creating an Excel file with line chart. I've created chart and filled it with data but I cannot create points on my chart. Does anyone know, is there a way I could generate these points(triangles, squares, circles etc.) in a chart using apache poi?

enter image description here

This is my code for generating current char:

public static void main(String[] args) throws Exception {
Workbook wb = new XSSFWorkbook();
Sheet dataSheet = wb.createSheet("linechart");

final int NUM_OF_ROWS = 10;
final int NUM_OF_COLUMNS = 4;

Row row;
Cell cell;
for (int rowIndex = 0; rowIndex < NUM_OF_ROWS; rowIndex++) {
row = dataSheet.createRow((short) rowIndex);
for (int colIndex = 0; colIndex < NUM_OF_COLUMNS; colIndex++) {
cell = row.createCell((short) colIndex);
cell.setCellValue(rowIndex * ((colIndex + 1) + ((int) (Math.random() * 10))));
}
}

Drawing drawing = dataSheet.createDrawingPatriarch();
ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, NUM_OF_COLUMNS + 2, 3, NUM_OF_COLUMNS + 15, 20);

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

LineChartData data = chart.getChartDataFactory().createLineChartData();

ChartAxis bottomAxis = chart.getChartAxisFactory().createCategoryAxis(AxisPosition.BOTTOM);
ValueAxis leftAxis = chart.getChartAxisFactory().createValueAxis(AxisPosition.LEFT);
leftAxis.setCrosses(AxisCrosses.AUTO_ZERO);

ChartDataSource<Number> xs = DataSources.fromNumericCellRange(dataSheet, new CellRangeAddress(0, NUM_OF_ROWS - 1, 0, 0));
ChartDataSource<Number> ys1 = DataSources.fromNumericCellRange(dataSheet, new CellRangeAddress(0, NUM_OF_ROWS - 1, 1, 1));
ChartDataSource<Number> ys2 = DataSources.fromNumericCellRange(dataSheet, new CellRangeAddress(0, NUM_OF_ROWS - 1, 2, 2));
ChartDataSource<Number> ys3 = DataSources.fromNumericCellRange(dataSheet, new CellRangeAddress(0, NUM_OF_ROWS - 1, 3, 3));

LineChartSeries series1 = data.addSeries(xs, ys1);
series1.setTitle("one");
LineChartSeries series2 = data.addSeries(xs, ys2);
series2.setTitle("two");
LineChartSeries series3 = data.addSeries(xs, ys3);
series3.setTitle("three");

chart.plot(data, bottomAxis, leftAxis);

XSSFChart xssfChart = (XSSFChart) chart;
CTPlotArea plotArea = xssfChart.getCTChart().getPlotArea();
plotArea.getLineChartArray()[0].getSmooth();
CTBoolean ctBool = CTBoolean.Factory.newInstance();
ctBool.setVal(false);
plotArea.getLineChartArray()[0].setSmooth(ctBool);
for (CTLineSer ser : plotArea.getLineChartArray()[0].getSerArray()) {
ser.setSmooth(ctBool);
}

FileOutputStream fileOut = new FileOutputStream("chart.xlsx");
wb.write(fileOut);
fileOut.close();
}

Answer

Solution:

CTPlotArea plotArea = xssfChart.getCTChart().getPlotArea();
CTMarker ctMarker = CTMarker.Factory.newInstance();
ctMarker.setSymbol(CTMarkerStyle.Factory.newInstance());
for (CTLineSer ser : plotArea.getLineChartArray()[0].getSerArray()) {
    ser.setMarker(ctMarker);
}