Search code examples
odatasapui5export-to-excel

Export business data from UI5 to Excel

I have a table with product details like ID, description, price etc.. I am trying to export these data to an Excel.

Issue

If I just do getModel("A"), and then bind few properties of "A", which are not nested, the excel is downloaded fine. But if there is any other structure, which I am trying to access for example, getModel("A").getProperty("/Person/PersonFullName"), it will leave that column blank.

{ // Controller
  onExport: function() {
    var aCols, aProducts, oSettings;
    aCols = this.createColumnConfig();
    aProducts = this.getView().getModel("A"); // A has nested/deep entities
    oSettings = {
      workbook: { columns: aCols },
      dataSource: aProducts
    };
    var oSpreadsheet = new Spreadsheet(oSettings); // required "sap/ui/export/Spreadsheet"
    oSpreadsheet.build();
  },            

  createColumnConfig: function() {
    return [
      {
        label: 'Product ID',
        property: 'name'
      },
      {
        label: 'Category',
        property: 'BillTo/BillToName', //Not able to get this property
      }
    ];
  },

}
Solution

  • According to the API reference of sap/ui/export/Spreadsheet, the dataSource setting object awaits one of the following arguments:

    • Map of data source properties, // See API reference for available options
    • URL to an OData service
    • JSON array

    But in your code, the aProduct, which is assigned to the dataSource, is a reference to the "A" model which is invalid.

    The dataSource should be set, depending on the model type, accordingly:

    With ODataModel

    const odataListBinding = this.byId("myResponsiveTable").getBinding("items");
const serviceEntryPath = "/sap.app/dataSources/<sourceName>/uri"; // See manifest.json for <sourceName>
const serviceUrl = this.getOwnerComponent().getManifestEntry(serviceEntryPath);

    { // Constructor settings of sap/ui/export/Spreadsheet
  dataSource: {
    type: "OData",
    useBatch: true,
    serviceUrl: serviceUrl,
    headers: odataListBinding.getModel().getHeaders(),
    dataUrl: odataListBinding.getDownloadUrl(), // serviceUrl + "/Orders" + added queries ($expand, $select, ...)
    count: odataListBinding.getLength(),
    sizeLimit: /*e.g.*/1000,
  },
  worker: true, // false if working with mock server or if CSP is enabled.
  fileName: "myExportedDataFromODataV2.xlsx"
}

    Note: Please make sure that the corresponding $expand parameter is included in the dataUrl. Otherwise, nested properties won't be exported.

    Samples

    With client-side models (e.g. JSONModel)

    { // Constructor settings of sap/ui/export/Spreadsheet
  dataSource: myJSONModel.getProperty("/SomeCollection"), // returns an array
  fileName: "myExportedDataFromPlainJSON.xlsx"
}

    Samples