How to format cell type?

[Maxxon]

Hello. I am new in docx4j and openxml. I have a necessity to create the excel file which is contain N separate tables (existing template) with pre-created charts according these tables. Actually, I need to replace some text placeholders with the values from database and show to the user completely generated Excel document.
I checked the sample with replacing values, it is working for me after obtaining the latest patch in the source code. Unfortunately, my charts cannot be evaluated according formulas because the replaced values are in string type.
Ok, I can initially mark some rows for the future converting them to the numbers, but when I set the STCellType.N for these rows, the necessary values were missing and unknown values have been inserted to this place.

Source xlsm file contain:

Code: Select all

F5 = ${gender:1}     G5 = ${gender:1:value}      H5 = =IF(G5>0; G5/SUM($G$5:$G$6)*100; "")
F6 = ${gender:2}     G6 = ${gender:2:value}      H6 = =IF(G6>0; G6/SUM($G$5:$G$6)*100; "")


After replacements I see in the result xlsm file:

Code: Select all

F5 = Man       G5 = 30      H5 = #VALUE!
F6 = Woman     G6 = 32      H6 = #VALUE!


Note: I'm inserting "20" and "30", not "30" and "32".

here is my code:

Code: Select all

        List<String> numbers = new ArrayList<String>();  //convert to number the cell type with this address when all placeholders will be replaced
        numbers.add("G5");
        numbers.add("G6");

        JaxbSmlPart smlPart = (JaxbSmlPart)pkg.getParts().get(new PartName("/xl/sharedStrings.xml"));
        HashMap<String, String> mappings = new HashMap<String, String>();
        mappings.put("gender:1", "Man");
        mappings.put("gender:1:value", "20");
        mappings.put("gender:2", "Woman");
        mappings.put("gender:2:value", "30");
        smlPart.variableReplace(mappings);

        //when replacements done, iterate over all rows and columns in the sheet and set the type "Number" for the selected cells
        for (int i=0; i<sheetData.getRow().size(); i++) {
            for (int j=0;j<sheetData.getRow().get(i).getC().size();j++) {
                if (numbers.contains(sheetData.getRow().get(i).getC().get(j).getR())) {
                    sheetData.getRow().get(i).getC().get(j).setT(STCellType.N);
                }
            }
        }


Can you please tell me what I am doing wrong and is there a possibility to convert the string value to number (not a changing the type of the cell)?
I also tried to use the online service http://webapp.docx4java.org, but didn't find anything useful in this case

Thanks!

[jason]

Cross posted; locking.

See http://stackoverflow.com/questions/2935 ... ith-xlsx4j