I have a template pptx file with a bar chart of several percentages. Each bar represents one percentage. My plan is to keep the chart in place but just remove the existing bars and add new ones using xlsx4j.
Here's the relevant java code, which is updating the embedded .xlsx file of the bar chart.
......
WorksheetPart wsp = (WorksheetPart)partsMap.get(pairs.getKey());
List<Row> rows = wsp.getJaxbElement().getSheetData().getRow();
Row headerRow = rows.get(0);
rows.clear();
rows.add(headerRow);
Row newRow = new Row();
Cell newLabelCell = new Cell();
newLabelCell.setT(STCellType.STR);
newLabelCell.setV("Some Label");
newLabelCell.setParent(newRow);
newRow.getC().add(newLabelCell);
newRow.setParent(wsp.getJaxbElement().getSheetData());
Cell newValueCell = new Cell();
newValueCell.setV("75%");
newValueCell.setT(STCellType.STR);
newValueCell.setParent(newRow);
newRow.getC().add(newValueCell);
rows.add(newRow);
......
Above this code I'm updating the CTBarChart and CTBarSer objects, that code is working fine. When I open up the updated pptx file, it looks great. But when I right click the chart and click "Edit Data", the percentages are being treated as strings, not as percentages, so the chart gets messed up. It's easy enough to change the cell types to percentages in power point, but I need to do it in code.
I'm aware that I am setting the cell type to STCellType.STR. There is no STCellType.Percentage. If I use any other cell type besides STCellType.STR, I can't even open the xlsx data in powerpoint, it becomes corrupted or something.
So my question, how do I add new rows to the embedded .xslx file of a pptx chart where the cell is something other than a number, like a percentage.
If you'd like to see all the code I'll have to get it all together, as it is spread across several classes and methods.
Thanks
You will need to manipulate the S attribute of the cells in question. This attribute takes a Long value, and reflects the cell number format style. There are a number of in-built cell number formats in Excel, and a % type will be one of them (if you search cell number formats / Open XML, you should find the relevant cell number format value for what you want. I think the percentage types are 9 and 10, but you'd need to test it).
Once you know what value you need, setting a cell up is pretty straightforward. Some sample code:
// Create a cell
Cell cellToUpdate = Context.getsmlObjectFactory().createCell();
// Set its reference in the worksheet
cellToUpdate.setV("B14");
// Set its value
cellToUpdate.setV("56.3");
// Set its data type (here a 'stand-alone' string)
cellToUpdate.setT(STCellType.STR);
// Set its style (i.e. number format)
cellToUpdate.setS(yourNumberFormat);