How to edit an embedded xlsx values programatically.

[Raj]

I created a docx document, with an embedded xlsx (a bar chart). I was able to edit the chart values programatically by using

Code: Select all

CTChartSpace ch = chartPart.getJaxbElement();

and related lines. Now I am able to refresh data as per my requirement.
However when the user double clicks on the chart he is able to view the xlsx sheet with the old values. Is there a way I can change the embedded spreadsheet values also? Otherwise it will not look good when the user views old values when he clicks on the chart.

Thank You.

[jason]

Hi,

you'll see the embedded spreadsheet is an EmbeddedPackagePart:

Code: Select all

        Part /word/charts/chart1.xml [org.docx4j.openpackaging.parts.DrawingML.Chart] http://schemas.openxmlformats.org/officeDocument/2006/relationships/chart containing JaxbElement:org.docx4j.dml.chart.CTChartSpace
            Part /word/embeddings/Microsoft_Excel_Worksheet1.xlsx [org.docx4j.openpackaging.parts.WordprocessingML.EmbeddedPackagePart] http://schemas.openxmlformats.org/officeDocument/2006/relationships/package


This is a BinaryPart, so you can use getBuffer() to get it as a ByteBuffer, edit it using xlsx4j, then replace the original contents using setBinaryData method.

[ksnpavan]

Hi,

I am trying exactly the same use case of editing an embedded xlsx programatically.
I tried your suggestion of

so you can use getBuffer() to get it as a ByteBuffer, edit it using xlsx4j, then replace the original contents using setBinaryData method.

Following is the piece of code that I am using to update a cell in embedded excel.

Code: Select all

Map<PartName, Part> partsMap = wordMLPackage.getParts().getParts();

SpreadsheetMLPackage pck = (SpreadsheetMLPackage) SpreadsheetMLPackage.load(BufferUtil.newInputStream(((EmbeddedPackagePart) partsMap.get(new PartName("/word/embeddings/Microsoft_Office_Excel_Worksheet1.xlsx"))).getBuffer()));

((WorksheetPart) pck.getParts().getParts().get((new PartName("/xl/worksheets/sheet1.xml")))).getJaxbElement().getSheetData().getRow().get(1).getC().get(1).setV("20");      


Now, how can use setBinaryData() on EmbeddedPackagePart to set the updated content back into embedded excel.
Because setBinaryData() can take bytes[], ByteBuffer, InputStream and I am not able to find required utils to get these from SpreadsheetMLPackage.

Can you please let me know how this can be achieved.

Thanks,
KSN Pavan.

[jason]

Have a look at the pptx4j EditEmbeddedCharts sample

[ksnpavan]

Hi Jason,

Thanks for your reply,
I tired the same way and was getting following exception

Code: Select all

[com.sun.istack.internal.SAXException2: unable to marshal type "org.xlsx4j.sml.CTTable" as an element because it is missing an @XmlRootElement annotation]

To over come the above exception I tried the solution provided in forum http://www.docx4java.org/forums/pptx-java-f14/editing-charts-and-spreadsheet-with-pptx4j-t1238.html. Added the @XmlRootElement(name = "table") in CTTable.java, compiled the class and added the class file to docx4j jar. So, the CTTable.java annotations now look as follows:

Code: Select all

@XmlRootElement(name = "table")
@XmlAccessorType(XmlAccessType.FIELD)
@XmlType(name = "CT_Table", propOrder = {
    "autoFilter",
    "sortState",
    "tableColumns",
    "tableStyleInfo",
    "extLst"
})

With this I am able to overcome the above mentioned exception and I am able to set the binary data back to embedded part.

Now the issue that I am facing is, while trying to edit the chart data using MS Word, it is not opening the editable embedded excel file.
I extracted the .docx and tried opening the embedded Microsoft_Office_Excel_Worksheet1.xlsx.
MS Excel is throwing an error "Excel found unreadable content in Microsoft_Office_Excel_Worksheet1.xlsx. Do you want to recover the contents of this workbook?"
When I said yes to above, it opened the excel with message "Repaired Records: Table from /xl/tables/table1.xml part (Table)".

Content of table1.xml is as follows:

Code: Select all

<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>
  <table xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" tableBorderDxfId="0" totalsRowShown="false" ref="A1:B4" displayName="Table1" name="Table1" id="1">
    <tableColumns count="2">
      <tableColumn name="" id="1" />
      <tableColumn name="Severity Chart" id="2" />
    </tableColumns>
    <tableStyleInfo showColumnStripes="false" showRowStripes="true" showLastColumn="false" showFirstColumn="false" />
  </table>

Can you please let me know what could have gone wrong and how make the embedded excel working with out any issues.

Note: I did not update any thing in embedded excel programatically, I got the binary data and then just set it back again with out modifying anything.

Thanks,
KSN Pavan.

[jason]

Please use the current code from GitHub; there is a pre-compiled nightly at http://www.docx4java.org/docx4j/docx4j- ... 130201.jar

[ksnpavan]

Thank you very much for the info Jason. Will try with the latest jar and let you know.

Thanks,
KSN Pavan.

[ksnpavan]

Hi Jason,

Haven't tried with the latest nightly build yet.
After some trial and error found the root cause why the embedded excel got corrupt.

After getting the binary data and setting it back to embedded part, sharedStrings.xml file got corrupt.

With out updating the embedded excel programatically sharedString.xml consists of as follows:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" count="6" uniqueCount="6">
<si>
<t xml:space="preserve"> </t>
</si>
<si>
<t>To resize chart data range, drag lower right corner of range.</t>
</si>

after getting binary data and setting it back sharedString.xml consists of as follows:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" uniqueCount="6" count="6">
<si>
<t> </t>
</si>
<si>
<t>To resize chart data range, drag lower right corner of range.</t>
</si>

Is this issue already addressed earlier? or do we have any work around to get past this?
Can you please share the info.

Thanks,
KSN Pavan.

[arimmer]

I had a similar problem with spaces in spreadsheets.

The 'xml:space="preserve" attribute* is only needed if the space is leading or trailing, so I got around this by ensuring I had no leading or trailing spaces in the sharedStrings.xml file.

MS Excel sometimes 'chops' what appears to be a single string in a cell into several shared strings - if this is what it has done in your case, you can replace the several shared strings entries with a single one that has spaces within but not at the ends of the string.

If the space is in an 'empty' cell, you can successfully create and save cells with no content - they do not need to contain a space character. I have also done this where necessary.

Either of the above could give you a work around for this problem,although it will require looking for all uses of the shared string that contains only a space, and replacing them as described above.

*as far as I could tell from the standards documentation on which these libraries are based, this attribute is not documented, which would be why it has not been implemented.

[ksnpavan]

Jason,

Using the latest nightly build docx4j-nightly-20130201.jar resolved my following issues that I specified.

1) [com.sun.istack.internal.SAXException2: unable to marshal type "org.xlsx4j.sml.CTTable" as an element because it is missing an @XmlRootElement annotation] exception \ while saving the SpreadsheetMLPackage.
2) sharedStrings.xml getting corrupt and <t xml:space="preserve"> </t> element is getting lost.

Arimmer,
Thanks for your suggestions, will consider these options instead of having cells with just a space character.

Thanks,
KSN Pavan.