xlsx4j - variableReplace() do nothing !

[schlebe]

Hello everybody,

it is the first time that I use doc4j to generate an Excel file from another Excel file (pseudo template file) in changing some values in some fields.

I have found an example in https://github.com/plutext/docx4j/commit/9dc7f5c4e303cfea4ec5b11e5700584d388034c5 and I have made some minor changes.

The program run correctly without crashing but the generated Excel file doesn't contains new values.

In the input template Excel file, some cells contains the following strings

#{ctc.BRAND-NAME}
#{ctc.TIRE-SIZE}

The goal of this little test is to replace the 2 previous pseudo variables by 2 specific values.

You can find the program below.

When I debug the program on Netbeans 8.1, I can see that oSharingString variable contains the 2 pseudo variable and I can also see that after calling variableReplace() function nothing have been changed !

What is also very strange is that if I don't call oSharedString.getXML(); the oSharedString is not populated !!!

What do I do wrong ?

For your information, I have tested with doc4j version 3.3.3 and 3.3.2 and 3.2.2 that I have found on Maven repository.

Thanks for any help

Code: Select all

/*******************************************************************************
* GenerateAuditReportForAuthority()
********************************************************************************
*/

package com.company.generatereport;

import java.io.File;
import java.util.HashMap;

import org.docx4j.openpackaging.packages.SpreadsheetMLPackage;
import org.docx4j.openpackaging.parts.PartName;
import org.docx4j.openpackaging.parts.SpreadsheetML.JaxbSmlPart;
import org.docx4j.openpackaging.parts.SpreadsheetML.WorkbookPart;
import org.docx4j.openpackaging.parts.SpreadsheetML.WorksheetPart;

import java.nio.file.Paths;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;

public class GenerateAuditReportForAuthority
    {
    public static void main(String[] args) throws Exception
        {
        DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd.HHmmss");
        String sDateTime = dateFormat.format(new Date());
        String sCurrentDir = Paths.get(".").toAbsolutePath().normalize().toString();
        String sTestDir = sCurrentDir + "/test";
        String sInputTemplate = sTestDir + "/AuditReport.TEMPLATE.xlsm";
        String sOutputFile = sTestDir + "/AuditReport." + sDateTime + ".xlsm";
       
        SpreadsheetMLPackage oExcel;
       
        try {
            oExcel = SpreadsheetMLPackage.load(new File(sInputTemplate));
            JaxbSmlPart oSharedString = (JaxbSmlPart)oExcel.getParts().get(new PartName("/xl/sharedStrings.xml"));

            oSharedString.getXML();
           
            HashMap<String, String> mappings = new HashMap<String, String>();

            mappings.put("#{ctc.BRAND-NAME}", "DUNLOP");
            mappings.put("#{ctc.TIRE-SIZE}", "125/45R18");

            oSharedString.variableReplace(mappings);

            oExcel.save(new File(sOutputFile));      
            }
        catch(Exception ex)
            {
            int iTrace = 0;
            }
        }
    } // END-OF-CLASS


[schlebe]

I use the following POM file.

Code: Select all

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>com.company</groupId>
    <artifactId>GenerateReport</artifactId>
    <version>1.0-SNAPSHOT</version>
    <packaging>jar</packaging>
    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <maven.compiler.source>1.8</maven.compiler.source>
        <maven.compiler.target>1.8</maven.compiler.target>
    </properties>

    <dependencies>
        <!-- https://mvnrepository.com/artifact/org.docx4j/docx4j -->
        <dependency>
            <groupId>org.docx4j</groupId>
            <artifactId>docx4j</artifactId>
            <version>3.2.2</version>
        </dependency>
    </dependencies>

</project>

[jason]

Use:

Code: Select all

        mappings.put("ctc.BRAND-NAME", "DUNLOP");
        mappings.put("ctc.TIRE-SIZE", "125/45R18");


with the attached sample input xlsx.

When I added entries to the sheet, I used for example:

Code: Select all

'${ctc.BRAND-NAME}


(note the preceding single quote)

[schlebe]

@Jason: thanks for your help.

I now use ${variable-name} instead #{variable-name} and I indicate only variable-name in mapping and this work fine.

Thanks

Question: the ${variable-name} notation is an Excel standard OR a xlsx4j notation ?

[jason]

an xlsx4j notation