Repository navigation
Bug: the number format is not updated after updating the XSLFChart in the presentation #1007
Description
I have a presentation template with a chart created and populated with data from Excel. It's a combo chart with three series, all of which are clustered column, with one series on the secondary axis. The numbers on the chart are formatted with an option "Linked to source" in Format Data Labels.
First, I load the presentation into memory, get a slide, find the chart, and process it.
From the chart, I get an Excel workbook, create a data format, create two new cell styles and assign each.
XSSFWorkbook workbook = chart.getWorkbook();
XSSFDataFormat dataFormat = workbook.createDataFormat();
XSSFCellStyle decimalCellStyle = workbook.createCellStyle();
decimalCellStyle.setDataFormat(dataFormat.getFormat("#,##0.###"));
XSSFCellStyle intCellStyle = workbook.createCellStyle();
intCellStyle.setDataFormat(dataFormat.getFormat("#,##0"));
Next, I get a page, update the cell values and style.
XSSFSheet sheet = workbook.getSheetAt(0);
updateCategoryCells(periods, sheet);
updateValueCells(vals1, sheet, 1, decimalCellStyle, intCellStyle);
updateValueCells(vals2, sheet, 2, decimalCellStyle, intCellStyle);
updateValueCells(vals3, sheet, 3, decimalCellStyle, intCellStyle);
private void updateCategoryCells(String[] periods, XSSFSheet sheet) {
for (int i = 0; i < periods.length; i++) {
XSSFRow row = sheet.getRow(i + 1);
if (row == null) row = sheet.createRow(i + 1);
XSSFCell categoryCell = row.getCell(0, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
categoryCell.setCellValue(periods[i]);
}
}
private void updateValueCells(
BigDecimal[] values,
XSSFSheet sheet,
int cellNum,
XSSFCellStyle decimalCellStyle,
XSSFCellStyle intCellStyle
) {
for (int i = 0; i < values.length; i++) {
XSSFRow row = sheet.getRow(i + 1);
if (row == null) row = sheet.createRow(i + 1);
XSSFCell cell = row.getCell(cellNum, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
if (values[i] == null) {
cell.setBlank();
} else {
cell.setCellValue(values[i].doubleValue());
setCellStyle(values[i], cell, decimalCellStyle, intCellStyle);
}
}
}
private void setCellStyle(
BigDecimal value,
XSSFCell cell,
XSSFCellStyle decimalCellStyle,
XSSFCellStyle intCellStyle
) {
if (value.stripTrailingZeros().scale() > 0) {
cell.setCellStyle(decimalCellStyle);
} else {
cell.setCellStyle(intCellStyle);
}
}
With data source factory I create data sources, get data lists from the chart, then get the series and replace the data. For each data list, I call the plot method.
XDDFCategoryDataSource categoryDataSource = XDDFDataSourcesFactory.fromStringCellRange(
sheet,
new CellRangeAddress(1, Math.max(1, periods.length), 0, 0)
);
XDDFNumericalDataSource<Double> vals1DataSource = XDDFDataSourcesFactory.fromNumericCellRange(
sheet,
new CellRangeAddress(1, Math.max(1, periods.length), 1, 1)
);
XDDFNumericalDataSource<Double> vals2DataSource = XDDFDataSourcesFactory.fromNumericCellRange(
sheet,
new CellRangeAddress(1, Math.max(1, periods.length), 2, 2)
);
XDDFNumericalDataSource<Double> vals3DataSource = XDDFDataSourcesFactory.fromNumericCellRange(
sheet,
new CellRangeAddress(1, Math.max(1, periods.length), 3, 3)
);
List<XDDFChartData> chartDataList = chart.getChartSeries();
XDDFChartData firstAndSecondValsChartData = chartDataList.getFirst();
XDDFChartData thirdValsChartData = chartDataList.get(1);
firstAndSecondValsChartData.getSeries(0).replaceData(categoryDataSource, vals1DataSource);
firstAndSecondValsChartData.getSeries(1).replaceData(categoryDataSource, vals2DataSource);
thirdValsChartData.getSeries(0).replaceData(categoryDataSource, vals3DataSource);
chartDataList.forEach(chart::plot);
After that I import data to a new slide show, write into new file, save and open it. The ranges are displayed correctly, but the numbers are not formatted. However, after opening Excel, the chart updates and the numbers are displayed with formatting.
I suspect the problem lies in the plot method.
Activity
BENGOBRO commentedon Feb 13, 2026
Add full code and pptx template
Main.java
PresentationService.java
input.pptx
Keep cell number formats when plotting chart data from a sheet range (#…