Query regarding Export file for Numerical fields

[priyanka.barik]

Hello Team,



One of our customer was using the export capabilities of SmartGWT and was relying so much on how the data was formatted in the exported file. They had VARCHAR2 fields having only numeric values. Mostly used as ID fields say Customer ID, Carrier ID etc



Issue is before we had an upgrade in 2018, these numeric fields were coming formatted as General fields on which they can apply some anaysis right away. But after the upgrade, the same fields are now coming with a warning "Number formatted as Text".
Is it possible to get it formatted as in the earlier versions?



Note: We already tried by passing DSRequest.setExportNumbersAsFormattedString(false) associated with ListGrid.exportData(). But result was same.



Thanks

[Isomorphic]

What kind of export are you doing (Excel-specific .xls, OOXML, etc) and what software is giving you this weird error message? Please specify the software, version of it, and platform you are running it on.

A while ago, we enhanced our POI-based export to include number formatters for numeric fields (if you set e.g. dataSourceField.format / exportFormat). This does exactly what you want in all typical spreadsheet software (Excel, OpenOffice, Google Sheets): it keeps the value as a number so that you can use it for calculations, but still formats it as a currency (or whatever you want).

No one has ever reported an error message like this. Our best guess would be that you are using some kind of software that doesn't actually support OOXML (as in, shortcuts were taken).

[priyanka.barik]

Apologies for replying late but below are the details from our application-
What kind of export are you doing?: .xls
Its Not an error message, but a warning "The number in this cell is formatted as text or preceded by an apostrophe"

Apps Versions :-
  Software: Microsoft® Excel® for Microsoft 365 MSO (Version 2302 Build 16.0.16130.20806) 64-bit
  Platform: Windows 10 Enterprise (Version 22H2)
  poi-5.2.4.jar
  GWT 2.10.0

[Blama]

Hi all,

I can reproduce with this modified sample (v13.0p_2024-02-01, Microsoft® Excel® for Microsoft 365 MSO (Version 2401 Build 16.0.17231.20194) 32-bit):

isc.ListGrid.create({
    ID: "countryList",
    width:500, height:250, top:70, alternateRecordStyles:true,
    dataSource: worldDSExport,
    autoFetchData: true,
    initialCriteria: {operator: "lessThan", fieldName: "pk", value: 5},
    fields:[
        {name:"pk", title:"ID"},
        {name:"countryName", title:"Country"},
        {name:"capital", title:"Capital"},
        {name:"continent", title:"Continent"},
        {name:"independence", title:"Nationhood", width:100},
        {name:"population", title:"Population"}
    ],
    showFilterEditor: true
});

isc.DynamicForm.create({
    ID: "exportForm",
    width:300,
    fields: [
        { name: "exportType", title: "Export Type", type:"select", width:"*",
            defaultToFirstOption: true,
            valueMap: {
                "csv" : "CSV" ,
                "xml" : "XML",
                "json" : "JSON",
                "xls" : "XLS (Excel97)",
                "ooxml" : "OOXML (Excel2007)"
            }
        },
        { name: "showInWindow", title: "Show in Window", type: "boolean", align:"left" }
    ]
});

isc.Button.create({
   ID: "exportButton",
   title: "Export",
   left: 320,
   click: function () {
       var exportAs = exportForm.getField("exportType").getValue();
       var showInWindow = exportForm.getField("showInWindow").getValue();
       if (exportAs == "json") {
           // JSON exports are server-side only, so use the OperationBinding on the DataSource
           countryList.exportData({ operationId: "customJSONExport",
               exportDisplay: showInWindow ? "window" : "download"});
       } else {
           // exportAs is not JSON, so we can set that with requestProperties
           countryList.exportData({ exportAs: exportAs,
               exportDisplay: showInWindow ? "window" : "download"
           });
       }
   }
});

isc.Button.create({
   ID: "updateButton",
   title: "Update Capital to number",
   left: 450,
   click: function () {
      worldDSExport.updateData({"pk": 1, "capital": "100"}, "countryList.refreshData()");
      worldDSExport.updateData({"pk": 2, "capital": "200"}, "countryList.refreshData()");
      worldDSExport.updateData({"pk": 3, "capital": "300"}, "countryList.refreshData()");
      worldDSExport.updateData({"pk": 4, "capital": "400"}, "countryList.refreshData()");
   }
});

Best regards
Blama

[Blama]

But to be honest, this could also be an Excel feature – the field is VARCHAR2 and I assume type="text" in the ds.xml. So the output as text actually would make sense.
Perhaps try setting exportFormat on your .ds.xml field?

Best regards
Blama

[Isomorphic]

In both the original report and this repro case, it seems that you have a field declared as text, and you are putting numeric values in it, and you want to deal with those values as numeric values in Excel.

If you want numeric values, declare the field as a numeric type instead.

Unless we're missing something, this is not a bug, and the system is behaving as designed.

[priyanka.barik]

Expectation is the export file should look like this(above), but the actual export file has the below formatting-

[Isomorphic]

Yes, we understand. Please see previous message: we have already explained that there is no bug here and you seem to have misconfigured the system.