0

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

Alex03
  • 131
  • 1
  • 1
  • 9

1 Answers1

0

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);
Ben
  • 7,548
  • 31
  • 45
  • My pptx file had custom number formats or something so I had to use different values, depending on the slide, for the "S" attribute. I'm guessing the numbers I used are the index or ID of the custom style in the styles xml. It looks like your solution will work for most pptx files, but my pptx file has some other attribute set that is affecting it. – Alex03 Aug 11 '14 at 15:17
  • Yeah, the IDs are usually some kind of index to the defined style. You're right, you can create your own and reference them: in fact, that can be better, as you know what you're getting. – Ben Aug 12 '14 at 09:14