BENGOBRO opened a new issue, #1007:
URL: https://github.com/apache/poi/issues/1007
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);
}
}
```
I get a table from sheet and update the area reference.
```
int lastRowIdx = periods.length + 1;
XSSFTable table = sheet.getTables().getFirst();
table.setArea(
new AreaReference(
new CellReference("A1"),
new CellReference("D" + lastRowIdx),
SpreadsheetVersion.EXCEL2007
)
);
```
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.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
