Missformated amounts in excel exports of oData-V4 Fiori Apps

[mark05]

Hello Community,

I am currently struggling with an issue that seems to affect all Fiori Elements apps that use the V4-oData model. When I assign a currency field to an amount there via annotation, the currency is written to a cell with the amount (as a string) during Excel export. Further processing in Excel is then not directly possible. In addition, the language setting is also ignored, the amounts are always delivered with "." as a comma separator.

With V2 apps, on the other hand, only the amount is written to the cell, the currency is then displayed via the formatting options. Further processing is possible without any problems. In addition, a "," is used here as a comma separator (correct).

Here is the output with the V2 app:

And here the output of the V4 app:

DB schema:

aspect CommonCostsModel : managed, cuid {
    ...
    @Semantics.currencyCode
    currency       : Currency                    @title : '{i18n>Currency}';
    ...
}
entity Test : CommonCostsModel {
    ...
    @Semantics.amount.currencyCode :                                    'currency'
    netCost          : Decimal(15, 7)              @title :             '{i18n>NetCost}';
    ...
}

Annotations CDS of the application:

annotate service.Test with {
    ...
    netCost                 @(Measures.ISOCurrency : currency_code);
    ...

}<br>

Do I need some other annotations for the V4 variant of the oData service? I could not find anything in the documentation regarding this.

UPDATE 2021-12-22:

The application was developed with SAPUI5 version 1.87.9. This version is no longer LTS, so I have now updated the version to 1.96.2 or 1.97.0. Unfortunately, the problem has even worsened with both newer versions. Now, in addition to the wrong format, an error message appears when opening the xlsx file:

"We have detected a problem with some content in 'XXX.xlsx'. Should we restore as much as possible? If you trust this workbook, click 'Yes'."

Now when I click Yes, the Excel looks like it did before, but still with the formatting problem.

-----------------------------

UPDATE 2022-01-20:

I've debugged into the ExportBase class to find the column settings for the Excel Export. I've done this for the V2 and the V4 app to find any difference. Furthermore I've compared the oData metadata file and also the values from the response.

Column settings | UI5 1.87.9 | oData V2 (working)

Column settings | UI5 1.87.9 | oData V4 (wrong format in xlsx file)

Metadata | oData V2

Metadata | oData V4

oData Response | oData V2

oData Response | oData V4

-----------------------------

Thank you very much in advance!

Best Regads,

Mark

[leoirudayam]

Is there any solution to that so far?

[mark05]

Unfortunately, we have not found a solution. Is this a known issue at SAP?

[HerzogIgzorn]

Can you check the column settings on the export configuration? This can be done via debugging when the beforeExport event is fired. This event is fired in sap/ui/export/ExportBase line:187 (when using debug sources) or simply search for this.fireEvent('beforeExport' and set a breakpoint there. The exportSettings contain workbook.columns where all column related settings are stored. Once you have identified the column that is not formatted correctly, copy its configuration and attach it to the information above.

In addition to that, you need to check how the data is formatted when it comes from the OData service. The column settings define the desired output format and each output format requires a certain input format. For dates and numbers, there is a variety of supported input formats but they still have to match the expectations.

[mark05]

Hey Sebastian,

thanks for your response. I've added the column settings from the ExportBase class and some other information in the original post. Please inform me if you need some more information.

[HerzogIgzorn]

Is there a way you can share the generated file that shows the warning message when opening it? The incorrectly formatted value is one thing, but the warning is another thing that needs to be addressed. Maybe apply a filter on your application to show only one row which does not contain confidential data, export this entry and attach it to this post.

[mark05]

I updated the application back to version 1.97.2 to recreate the problem. Strangely, I can no longer reproduce the error message. The Excel file now opens without error. Also with version 1.97.0 the error does not appear anymore. So the problem seems to have solved itself. Possibly it was also a problem of Excel, which has now been fixed by an update.

Nevertheless, the problem with the formatting still exists in the new version. Therefore I have attached a sample file anyway (I'm not allowed to upload Excel files, so please rename the file-ending to ".xlsx" after download).

einzelkosten-11.txt

[HerzogIgzorn]

Hello Marc,

I could reproduce a similar issue but I need proof that your scenario is the same. You need to set a breakpoint in sap.ui.mdc.Table#_onExport function and then press the Export Button on the UI. It will stop at your breakpoint where you can access the variables in the current scope. Go to your console and enter:

this.getControlDelegate().fetchProperties

This will return the function coding of #fetchProperties in the console. Click on the coding and it will reveal its location in the "Sources" tab of the browsers dev tools. Please let me know whether this is sap/fe/macros/table/delegates/TableDelegate or a different delegate.

[mark05]

Hey Sebastian,

thanks for your research!

I placed the breakpoint as you described it above. The function's location is in "sap/fe/macros/table/delegates/TableDelagete.js", just like you guessed it.

[mark05]

You can see the source file here

[HerzogIgzorn]

Hello Marc,

an internal incident has been created to address this issue within the Fiori Elements template. As soon as the correction is available, I will update this post. Thank you for your cooparation.

Regards Sebastian

[mark05]

Hey Sebastian,

for your information: The issue has been fixed in the latest version of SAPUI5 (1.102.X).

Now the values are formatted with the corresponding unit and the values itself are of type number with the correct separator.

Thank you for forwarding it!

Best regards,

Mark

[HerzogIgzorn]

Hello Marc,

thank you for sharing this additional information. As you can see, the V2 configuration is of type "Currency" with all corresponding properties maintained. In contrast to V2, the V4 configuration is of type "String" and a template is applied on the input. This means the values of netCost and currency_code will be applied to the template and stored as a string. That's the reason why the editing bar in MS Excel also shows the currency code in V4. It is also not possible to apply formulas to the "String" output (at least no numerical formulas).

I assume that you are using Fiori ListReport template for your application? The difference between OData V2 and V4 is usually the type of table that is used by the different templates. While OData V2 utilizes the sap.ui.comp.SmartTable, the V4 template utilizes the sap.ui.mdc.Table which does not evaluate the $metadata directly. The sap.ui.mdc.Table is based on a Delegate that provides the propertyInfo to the table. This propertyInfo also contains specific exportSettings for each column. If there is no exportSettings available in the propertyInfo, it will use the default fallback to a column of type sap.ui.export.EdmType.String which will result in the current scenario.

The question that now remains is, whether your V4 application uses the sap.ui.mdc.Table and why the Delegate does not provide the corresponding exportSettings for this particular column.

What you can do in this situation is either creating a support ticket (if you are a licensed SAP customer) or gather more details in your application. In case the sap.ui.mdc.Table is used, you can set a breakpoint in the following function to get some insights:

Table.prototype._createExportColumnConfiguration

This function uses the PropertyHelper mixin to access the propertyInfo of the Delegate.

Regards Sebastian

[mark05]

Hey Sebastian,

first of all: Yes, I'm using the Fiori ListReport template.

I've now debugged a little bit into the sap.ui.mdc.Table object. I'm not sure if that is relevant, but I found a code line in the PropertyHelper, where the method gets the ColumnDataProperty, with the included ExportSettings. In this oProperty Object it contains the type on root level and some other type in the ExportSettings. And they are different:

description: "Nettokosten"
exportSettings:
  delimiter: false
  falseValue: undefined
  format: undefined
  inputFormat: undefined
  label: "Nettokosten"
  scale: 7
  template: "{0}  {1}"
  trueValue: undefined
  type: "String"
  [[Prototype]]: Object
filterExpression: "auto"
filterable: false
group: ""
groupLabel: ""
groupable: false
isAggregatable: ƒ ()
key: false
label: "Nettokosten"
maxConditions: null
maxLength: undefined
metadataPath: "/-SECRET-/@com.sap.vocabularies.UI.v1.LineItem/10"
name: "netCost"
path: null
precision: undefined
propertyInfos: (2) ['Property::netCost', 'Property::currency_code']
scale: undefined
sortable: false
type: "Edm.Decimal"
typeConfig: null
unit: undefined
visible: true

As you can see, the type on root level is Edm.Decimal, but in the ExportSettings it's String.

Apart from that, I have not been able to detect anything unusual.

Best regards,

Mark