CODEWITHSUNDEEP
javaexcelms-wordapache-poidocx
Sascha
Sascha

Reputation: 11

Change a value in embedded xlsx with apache poi

I tried to change a value of a xlsx-file which is embedded in a docx-file with apache poi. Sadly, I havent found a proper solution to this problem. After running my program, the new created docx-file with the embedded xlsx-table hasnt changed. Here is what I have tried so far:

 FileInputStream fis = new FileInputStream("old.docx");
 XWPFDocument xdoc = new XWPFDocument(OPCPackage.open(fis));
     System.out.println(xdoc.getAllEmbedds().get(0));
     File file2 = new File("new.docx");
     
     for (PackagePart pPart : xdoc.getAllEmbedds()) {
        String contentType = pPart.getContentType();
       
        // Excel Workbook - OpenXML file format
        if (contentType.equals("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")) {
            XSSFWorkbook embeddedWorkbook = new XSSFWorkbook(pPart.getInputStream());
            XSSFSheet sheet = embeddedWorkbook.getSheetAt(0);
            sheet.getRow(1).getCell(1).setCellValue("someValue"); //change value here
            embeddedWorkbook.write(pPart.getOutputStream());
        }
    }
    xdoc.write(new FileOutputStream(file2));

any idea how to fix this problem?

Upvotes: 1

Views: 976

Answers (2)

Markus Pscheidt
Markus Pscheidt

Reputation: 7351

Setting this before saving the document:

// ask user to refresh fields upon opening the document
document.enforceUpdateFields();

updates the Excel preview image for me, when the user opens the file and accepts field update by clicking Yes.

Upvotes: 0

Axel Richter
Axel Richter

Reputation: 61975

I don't believe that this will be possible with fulfillment of all wishes. In fact, the embedded XSSFWorkbook is updated. But what you see if you open the new.docx is not the XSSFWorkbook but a EMF picture of it. This picture will only be updated if you doubleclick it to opening the embedded XSSFWorkbook and then click outside it to close it again.

Some other posts suggest that it will be updated while opening the Word file if this EMF picture is not actually within the ZIP archive. But it will not.

Try:

  for (PackagePart pPart : xdoc.getPackage().getPartsByName(Pattern.compile(".*emf$"))) {
    System.out.println(pPart.getPartName());
    //xdoc.getPackage().removePartRecursive(pPart.getPartName());
    xdoc.getPackage().removePart(pPart.getPartName());
  }

So the "solution" would be, new creating a EMF snapshot picture form the updated XSSFWorkbook and replace the old EMF picture with this new one. Not really possible in my opinion.

Seems as if the program routine for creating the EMF snapshot picture is part of the Word application if the embedded XSSFWorkbook is closed. But it is not part of apache-poi until now. And of course it is not part of the XML program routines.

Upvotes: 2

Related Questions