Is there a way to change the ordenation? I need to put the chart and the table in the same ordernation.
As you can see, the top element of the chart is the client 8 and the top element of the table is client 1.
So I need both the be exactly in the same order. How can I do that? What change should I do?
This is the partial of the code which is generating the chart above
public ByteArrayInputStream generateFileDetailsWithChart(ClientDataObjectRequest clientDataObjectRequest) {
ByteArrayOutputStream file = new ByteArrayOutputStream();
try{
try(XSSFWorkbook workbook = new XSSFWorkbook()){
XSSFSheet sheet = workbook.createSheet(clientDataObjectRequest.getMetricName());
Font headerFont = workbook.createFont();
headerFont.setBold(true);
headerFont.setUnderline(Font.U_SINGLE);
CellStyle headerCellStyle = workbook.createCellStyle();
headerCellStyle.setFillBackgroundColor(IndexedColors.GREY_40_PERCENT.getIndex());
headerCellStyle.setFont(headerFont);
XSSFDrawing drawing = sheet.createDrawingPatriarch();
XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 2, 1, 15, 15 + clientDataObjectRequest.getCompanyChartData().size());
Row headerRow = sheet.createRow(anchor.getRow2() + 1);
headerRow.createCell(0).setCellValue(Constants.CLIENT_NAME);
headerRow.createCell(1).setCellValue("Value ("+clientDataObjectRequest.getDataFormatCodeValue()+")");
for (int i = 0; i < headerRow.getLastCellNum(); i++) {
headerRow.getCell(i).setCellStyle(headerCellStyle);
}
CellStyle rowCellStyle = workbook.createCellStyle();
if (clientDataObjectRequest.getDataFormatCodeValue().equalsIgnoreCase(Constants.DATA_FORMAT_PERCENTAGE)) {
rowCellStyle.setDataFormat(workbook.createDataFormat().getFormat("0.00%"));
for (int i = 0; i < clientDataObjectRequest.getCompanyChartData().size(); i++) {
Row row = sheet.createRow(anchor.getRow2() + 2 + i);
row.createCell(0).setCellValue(clientDataObjectRequest.getCompanyChartData().get(i).getClientName());
row.createCell(1).setCellValue(clientDataObjectRequest.getCompanyChartData().get(i).getValue()/100);
//rowCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00%"));
row.getCell(1).setCellStyle(rowCellStyle);
}
}
if (clientDataObjectRequest.getDataFormatCodeValue().equalsIgnoreCase(Constants.DATA_FORMAT_CURRENCY)) {
rowCellStyle.setDataFormat(workbook.createDataFormat().getFormat("$0.00"));
for (int i = 0; i < clientDataObjectRequest.getCompanyChartData().size(); i++) {
Row row = sheet.createRow(anchor.getRow2() + 2 + i);
row.createCell(0).setCellValue(clientDataObjectRequest.getCompanyChartData().get(i).getClientName());
row.createCell(1).setCellValue(clientDataObjectRequest.getCompanyChartData().get(i).getValue());
row.getCell(1).setCellStyle(rowCellStyle);
}
}
XSSFChart chart = drawing.createChart(anchor);
chart.setTitleText(clientDataObjectRequest.getMetricName());
chart.getCTChart().getTitle().getTx().getRich().getPArray(0).getRArray(0).getRPr().setSz(2000);
chart.setTitleOverlay(false);
//XDDFChartLegend legend = chart.getOrAddLegend();
//legend.setPosition(LegendPosition.TOP_RIGHT);
XDDFCategoryAxis bottomAxis = chart.createCategoryAxis(AxisPosition.BOTTOM);
XDDFTitle title = getOrSetAxisTitle(bottomAxis);
title.setOverlay(false);
title.setText(Constants.CLIENT_NAME);
title.getBody().getParagraph(0).addDefaultRunProperties().setFontSize(12d);
XDDFValueAxis leftAxis = chart.createValueAxis(AxisPosition.LEFT);
title = getOrSetAxisTitle(leftAxis);
title.setOverlay(false);
title.setText("Value ("+clientDataObjectRequest.getDataFormatCodeValue()+")");
title.getBody().getParagraph(0).addDefaultRunProperties().setFontSize(12d);
if(clientDataObjectRequest.getDataFormatCodeValue().equalsIgnoreCase(Constants.DATA_FORMAT_PERCENTAGE)) {
leftAxis.setMaximum(1.0);
}
leftAxis.setCrosses(AxisCrosses.AUTO_ZERO);
leftAxis.setCrossBetween(AxisCrossBetween.BETWEEN);
sheet.shiftColumns(0,1,2);
sheet.autoSizeColumn(2);
sheet.autoSizeColumn(3);
XDDFDataSource<String> clientNames = XDDFDataSourcesFactory.fromStringCellRange(sheet,
new CellRangeAddress(anchor.getRow2() + 2,anchor.getRow2() + 1 + clientDataObjectRequest.getCompanyChartData().size(),2,2));
XDDFNumericalDataSource<Double> values = XDDFDataSourcesFactory.fromNumericCellRange(sheet,
new CellRangeAddress(anchor.getRow2() + 2,anchor.getRow2() + 1 + clientDataObjectRequest.getCompanyChartData().size(),3,3));
XDDFChartData data = chart.createData(ChartTypes.BAR, bottomAxis, leftAxis);
XDDFChartData.Series series = data.addSeries(clientNames, values);
series.setTitle(clientDataObjectRequest.getMetricName(), null);
setDataLabels(series,7,true); // pos 7 = INT_OUT_END, showVal = true
chart.plot(data);
XDDFBarChartData bar = (XDDFBarChartData) data;
bar.setBarDirection(BarDirection.BAR);
solidFillSeries(data, 0, PresetColor.BLUE);
Row bottomRow = sheet.createRow(anchor.getRow2() + clientDataObjectRequest.getCompanyChartData().size() + 3);
bottomRow.createCell(0).setCellValue(Constants.COPYRIGHT_FOOTER);
workbook.write(file);
}
} catch (Exception e){
}
return new ByteArrayInputStream(file.toByteArray());
}
And this is the body which my controller is receiving
{
"metricName":"Turnover Rate",
"dataFormatCodeValue": "currency",
"clientDataRequest":[
{
"clientName":"client 1",
"value":"1"
},
{
"clientName":"client 2",
"value":"2"
},
{
"clientName":"client 3",
"value":"3"
},
{
"clientName":"client 4",
"value":"4"
},
{
"clientName":"client 5555555",
"value":"5"
},
{
"clientName":"client 6",
"value":"6"
},
{
"clientName":"client 7",
"value":"7"
},
{
"clientName":"client 8",
"value":"8.5"
}
]
}
>Solution :
The default orientation of a y-axis in a chart is bottom-up. On bottom, where x-axis crosses, is min and on top is max. That is what you have.
This can be changed using XDDFChartAxis.setOrientation -> AxisOrientation.MAX_MIN. In your case:
...
XDDFCategoryAxis bottomAxis = ...
...
bottomAxis.setOrientation(AxisOrientation.MAX_MIN);
...
But you set leftAxis.setCrosses(AxisCrosses.AUTO_ZERO);. So, because of min (zero) is at top now, AxisCrosses.AUTO_ZERO is at top now also. So you need also setting:
...
XDDFValueAxis leftAxis = ...
...
leftAxis.setCrosses(AxisCrosses.MAX);
...