Improve Excel compatibility

[jferard]

The goal of this issue is to follow the changes suggested by @ueen and discussed here.

[jferard]

@ueen: can you pull the last version and check if it works? I changed the name of the master page style and removed the empty forms tag.

[ueen]

Hey, i think you missed one crucial change https://github.com/jferard/fastods/blob/bdb22d6389a1a09b91ad7a0ca8102f59ad77bab2/fastods/src/main/java/com/github/jferard/fastods/style/PageStyleBuilder.java line 126 the first parameter must also be 'Default' for the Document to be accepted by Excel, thanks for your work so far!

[jferard]

I still think it's possible to avoid to hard code the name. With the last version, I get:

content.xml:

<office:automatic-styles>
    ....
    <style:style style:name="ta1" style:family="table" style:master-page-name="Default"><style:table-properties table:display="true" style:writing-mode="lr-tb"/></style:style>
    ...
</office:automatic-styles>

styles.xml:

<office:automatic-styles>
    ...
    <style:page-layout style:name="Default">...</style:page-layout>
</office:automatic-styles>
<office:master-styles>
    <style:master-page style:name="Default" style:page-layout-name="Default">
        ...
    </style:master-page>
</office:master-styles>

That should do the trick. Can you confirm if Excel complains or not?

[ueen]

Oh sorry, didnt get that, great solution!

[ueen]

Excel doesnt complain anymore, great work, thanks a lot!! Theres a small thing though, if a number is saved as string, excel displays a hint, saying so convert it to a number, i'd be great if there would be an option to try to parse to int and then save as number maybe like this

`cell.setStringValue(stringOrNumber, autoDetectNumber = true)`

----------------------------------------------------------------
if (autoDetectNumber) {
       try {
            Integer parse = Integer.parseInt(string);
            saveAsNumber(parse);
        } catch (NumberFormatException e) {
            saveAsString(string);
        }
}

[jferard]

Thanks for the feedback and your previous suggestions. I'm going to release the version 0.7.3 very soon (maybe today: would be available tomorrow on the maven repo).

About your last suggestion: I'd rather not pile up methods. But you can write your own function:

class MyUtil {
    public static void setStringOrNumberValue(TableCell cell, String stringOrNumber) {
        try {
            Integer parse = Integer.parseInt(string);
            cell.setFloatValue(parse);
        } catch (NumberFormatException e) {
           cell.setStringValue(string);
       }
   }
}

Usage:

    final TableCellWalker walker = table.getWalker();
    MyUtil.setStringOrNumberValue(walker, "foo");
    walker.next();
    MyUtil.setStringOrNumberValue(walker, "1");

If you are importing data from a CSV file, I'm working on a different method: https://github.com/jferard/fastods/issues/214

[ueen]

Alright, thanks i can do the evaluation before setting the value also, i just thought this might be a common issue and could be further developed into a setAutoValue maybe, that automatically detects the type for convenience, but i totally get that this might just be lazyness on my part that would clutter up the library :D so never mind - thanks for the quick replys you may close this issue as resolved :)

[ueen]

Another convenience method would be to create a sheet from a List (columns as fields from Objects) or a 2d string array, dont know if that would be something easy or useful to do, but i'm gonna put it outthere as something that i'd wish for current solution looks like this

val data: MutableList<List<String>> = ArrayList()
    data.add(listOf("title1","title2","title3","title4","title5"))
    for (e in elements) {
        data.add(listOf(e.date,e.number,e.string,e.anotherString))
    }
    val file = File(...)

    val writer = OdsFactory.create().createWriter()
    val document = writer.document()
    val table = document.addTable(args.filmtitle)
    data.forEachIndexed { i, e ->
        e.forEachIndexed { iE, eE ->
            table.getRow(i).getOrCreateCell(iE).setStringValue(eE)
        }
    }
    writer.saveAs(file)

[jferard]

I'm trying to stick to the spec and there's nothing like an "integer" value type in OpenDocument spec: http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part1.html#__RefHeading__1417680_253892949.

You need to create your own DataStyle, like here: https://github.com/jferard/fastods/blob/master/fastods-examples/src/main/java/com/github/jferard/fastods/examples/D_SettingTheCellDataStyle.java#L82, with decimalPlaces(0).

(Please open a new issue for a new question. )

[ueen]

Yea sorry, never mind, found the problem, see issue https://github.com/jferard/fastods/issues/217 and PR https://github.com/jferard/fastods/pull/218

[jferard]

I'm closing this issue for the moment. Many thanks to @ueen. Great help!