12.0p: exportClientData column width problems with XLSX export

[Blama]

Hi Isomorphic,

please see this modified testcase (v12.0p_2020-01-04).
If you hit "Export", the generated file has the problem that the "Empty column" has width zero, while you can see the column in the GUI and it also has autoFitWidthApproach: "both". IMHO there are two problems here:

• It would be good to see the column in Excel in order to know what column has no data.
• Also, there is a problem with the "˛" in "km˛" in the area column.

I noticed this trying to create a testcase for a similar problem (did not succeed so far):
For me (v12.0p_2019-12-14) it also happens for columns with data that they have no width. They all have setCellFormatter(), but this seems not to be the reason, as you can see in the sample where Nationhood and Area do have widths and use the SmartClient equivalent of above function, formatCellValue.
Do you have an other idea what might influence column width in the export here?

Thank you & Best regards
Blama

isc.ListGrid.create({
    ID: "countryList",
    width: 800,
    height: 250,
    top: 50,
    autoFitWidthApproach: "both",
    alternateRecordStyles: true,
    dataSource: worldDSExport,
    autoFetchData: true,
    fields: [{
            name: "countryName",
            title: "Country"
        },
        {
            name: "capital",
            title: "Capital"
        },
        {
            name: "continent",
            title: "Continent"
        },
        {
            name: "area",
            title: "Area",
            type: "number",
            formatCellValue: "isc.NumberUtil.format(value, ',0') + ' km²'"
        },
        {
            name: "independence",
            title: "Nationhood",
            type: "date",
            width: "25%",
            formatCellValue: function(value) {
                if (isc.isA.Date(value)) {
                    return (new Date().getYear() - value.getYear()) + " years ago";
                }
            }
        },
        {
            name: "emptycolumn",
            title: "Empty column"
        },
        {
            name: "member_g8"
        },
        {
            name: "population",
            type: "number",
            title: "Population",
            format: ",0"
        },
        {
            name: "gdp",
            title: "GDP",
            type: "float",
            format: ",0.00"
        }
    ],
    showFilterEditor: true,
    headerSpans: [{
            fields: ["countryName", "capital"],
            title: "Master data"
        },
        {
            fields: ["area", "independence"],
            title: "Formatted with formatCellValue"
        },
        {
            fields: ["population", "gdp"],
            title: "Formatted with format"
        }
    ]
});

isc.Button.create({
    ID: "exportButton",
    title: "Export",
    top: 10,
    click: function() {
        countryList.exportClientData({
            exportAs: "ooxml",
            exportDisplay: "download",
            lineBreakStyle: "dos",
            exportDatesAsFormattedString: true
        });
    }
});

[Isomorphic]

Set exportFieldWidths to true if you want us to try to make the Excel widths approximately equal to the visible ListGrid field widths.

Otherwise that's Excel's own internal logic providing the widths, and Excel seems to think a near-invisible column is the right approach for empty data.

We'll check on whether the superscript issue can be corrected.

[Blama]

Hi Isomorphic,

thanks, in the sample this makes a difference when used for the whole ListGrid (better for columns without data, worse for others). I'll try if this also changes it in my application.

I understand correctly, that if I don't set exportFieldWidths: true, all sizing logic is done by Excel / LibreOffice?
Because then the problem I have in my application, where even columns with data (in a ListGrid with HeaderSpans) have size 0 might not be solveable at all.

Best regards
Blama

[Isomorphic]

We don't know why Excel would use a small size for a column *with* data. We'd need a way to reproduce that to look further.

[Blama]

OK, it seems that in my application as it is (without exportFieldWidths: true) the column width in Excel depends on the order of the rows, which is of course crazy.
I'll try with exportFieldWidths: true and a higher than default exportWidthScale (0.2 seems to work for me in the sample) then.

Thanks again,
Blama

[Blama]

Crossposting, will provide showcase-based sample for you.

[Blama]

Hi Isomorphic,

OK, got the testcase. Please see the width of column in the export in this sample if you order by category (default) or category DESC (click column title to do so).
You will see that the column width of the category column in the document is different (I'm using LibreOffice 6.3.3.2 (x64) on Windows 10) in both exports.

isc.ListGrid.create({
    ID: "supplyItemList",
    // exportFieldWidths: true, exportWidthScale:0.2,
    dataSource: "supplyItem",
    dataFetchMode: "local",
    sortField: "category",
    canEdit: true,
    width: "100%",
    height: 550,
    top: 50,
    autoFetchData: true,
    autoFitWidthApproach: "both",
    alternateRecordStyles: true,
    fields: [{
            name: "itemID",
        },
        {
            name: "itemName",
        },
        {
            name: "SKU",
        },
        {
            name: "category",
        },
        {
            name: "units",
        },
        {
            name: "unitCost"
        },
        {
            name: "inStock",
        },
        {
            name: "nextShipment",
        }
    ],
    showFilterEditor: true,
    allowFilterExpressions: true,
    initialCriteria: {
        _constructor: "AdvancedCriteria",
        operator: "or",
        criteria: [{
                fieldName: "category",
                operator: "equals",
                value: "Computer Consumables"
            },
            {
                fieldName: "category",
                operator: "equals",
                value: "Canteen and Washroom Products"
            }
        ]
    }

});

isc.Button.create({
    ID: "exportButton",
    title: "Export",
    top: 10,
    click: function() {
        supplyItemList.exportClientData({
            exportAs: "ooxml",
            exportDisplay: "download",
            lineBreakStyle: "dos",
            exportDatesAsFormattedString: true
        });
    }
});

Best regards
Blama

[Isomorphic]

You've got exportFieldWidths commented out, so what you're seeing is just what LibreOffice does by default, it appears. Probably, they size the column based on just the first few entries, which sometimes works poorly.

[Blama]

Hi Isomorphic,

yes, I agree. Depending on Excel / LibreOffice only and poor sizing logic there. Seems to depend on the last rows.
I just made the testcase as you asked for it in #4.

For me, I'll go with exportFieldWidths: true and a higher than default exportWidthScale (0.2 seems to work for me in the sample).

Thank you & Best regards
Blama

[Isomorphic]

Bug with HTML entities making into the Excel ("˛" in "km˛" in your case) is fixed and will be available for download in nightly builds since Feb 2 (tomorrow).