Skip to Content

Have you stumbled across the requirement to export a OData resouce as a flat file? This post will help you with that! What’s in it for me?

  • How to export any OData or potentially other data as flat file
  • Extendable code framework for general flat file export and code sample for CSV export

Exporting OData

OData is standardized way to expose and interact with RESTful resources. In SAP we make heavily use of OData as it has proven to be stable, understandable and standardized. Now you might run a SAPUI5 project on the SAP Cloud Platform to build new standalone or extension apps and very likely you will access another SAP system by using OData.

Now you want to give your business users the possibility to easily extract the data they see in your awesome app as a flat file. How can you do that? Well, with JavaScript this should be no problem!

If you look at the response you get from an OData call you will notice that the data is very easily accessible in JSON (sample from Northwind service):

{
        "d": {
                "results": [
                        {
                                "__metadata": {
                                        "uri": "http://services.odata.org/V2/northwind/Northwind.svc/Customers('ALFKI')",
                                        "type": "NorthwindModel.Customer"
                                },
                                "CustomerID": "ALFKI",
                                "CompanyName": "Alfreds Futterkiste",
                                "ContactName": "Maria Anders",
                                "ContactTitle": "Sales Representative",
                                "Address": "Obere Str. 57",
                                "City": "Berlin",
                                "Region": null,
                                "PostalCode": "12209",
                                "Country": "Germany",
                                "Phone": "030-0074321",
                                "Fax": "030-0076545",
                                "Orders": {
                                        "__deferred": {
                                                "uri": "http://services.odata.org/V2/northwind/Northwind.svc/Customers('ALFKI')/Orders"
                                        }
                                },
                                "CustomerDemographics": {
                                        "__deferred": {
                                                "uri": "http://services.odata.org/V2/northwind/Northwind.svc/Customers('ALFKI')/CustomerDemographics"
                                        }
                                }
                        },
...

We only have to run through this object, prune some irrelevant fields and create our file!

Code Framework & Code Samples

As we now know how the OData response looks like we will have a look at how to implement a file generator. Now I could give you a very short and efficient piece of code to solve this. But this would only be half the fun! Let’s implement something which is modular and easy to understand.

First we start off with an abstract class FileGenerator. This one will do the laborious part of stripping unnecessary attributes, looping over the data and sanitizing the values of each cell.

sap.ui.define([
	"sap/ui/base/Object"
], function(BaseObject) {
   "use strict";

	/**
	 * Abstract class which generates files based on OData REST sources.
	 * 
	 * The implementing subclasses define which specific file type will be generated. 
	 * This class is not tuned for a maximum of performance but for easy extendability. 
	 * 
	 * @class
	 * @public
	 * @name com.utils.FileGenerator
	 */
	var FileGenerator = BaseObject.extend("com.utils.FileGenerator", {
   	
   		/**
   		 * @param {array} [aSkipAttributes] Array of attributes which will be skipped during file generation.
   		 *									This is useful to hide fields and metadata. If the parameter is null,
   		 *									only the default attribute "__metadata" will be skipped.
   		 * @public
   		 */
   		constructor: function(aSkipAttributes) {
   			/**
   			 * Array of attributes which will be skipped during file generation.
   			 * @private
   			 */
   			this._aSkipAttributes = aSkipAttributes || ["__metadata"];
   		},
   		
   		/**
   		 * Extracts the header row and applies the cell processor function on each label.
   		 * @param {array} aRows An array of rows. This is usually the result set from the OData response.
   		 * @param {function} fnCellProcessor Function which will be called on each data field. The function
   		 *									 will be given the data field as the only parameter.
   		 * @param {function} fnLineBreakProcessor Function which will be called after each row to handle any line breaks.
   		 * @private
   		 */
   		_extractHeader: function(aRows, fnCellProcessor, fnLineBreakProcessor) {
   			if (aRows.length > 0) {
   				this._deleteSkippedAttributes(aRows[0]);
   				Object.keys(aRows[0]).forEach(function(sHeader) {
   					fnCellProcessor(sHeader);
   				});
   				fnLineBreakProcessor();
   			}
   		},
   		
   		/**
   		 * Loops over each row in aRows and applies the cell processor function on each data cell.
   		 * The first row in aRows is treated as the column header. 
   		 * @param {array} aRows An array of rows. This is usually the result set from the OData response.
   		 * @param {function} fnCellProcessor Function which will be called on each data field. The function
   		 *									 will be given the data field as the only parameter.
   		 * @param {function} fnLineBreakProcessor Function which will be called after each row to handle any line breaks.
   		 * @protected
   		 */
   		loopDataRows: function(aRows, fnCellProcessor, fnLineBreakProcessor) {
   			var self = this;
   			
   			this._extractHeader(aRows, fnCellProcessor, fnLineBreakProcessor);
			
   			aRows.forEach(function(oRow) {
   				// Remove unwanted attributes
   				self._deleteSkippedAttributes(oRow);
   				// Process each data cell (key, value)
   				Object.keys(oRow).forEach(function(sKey) {
   					// Processor can now use the cell value to create a file
					fnCellProcessor(self._sanitizeCellValue(oRow[sKey]));
				});
				// Allow to handle line breaks
   				fnLineBreakProcessor();
   			});
   		},
   
   		 /**
   		 * Sanitizes the cell value by ignoring objects and only handling primitve value types.
   		 * @param sCell The cell value to sanitize
   		 * @return {string} Returns the parameter value if it is a string otherwise the empty string will be returend.
   		 * @private
   		 */
   		_sanitizeCellValue: function(sCell) {
   			return sCell === undefined || sCell === null || typeof sCell === "object" ? "" : sCell;
   		},
   		
   		/**
   		 * Deletes every key of the skipAttributes member from the oRow.
   		 * @param {object} oRow A single row (object) of the result set.
   		 * @private
   		 */
   		_deleteSkippedAttributes: function(oRow) {
   			this._aSkipAttributes.forEach(function(oAttribute) {
   				delete oRow[oAttribute];
   			});
   		}
   		
   		/**
		 * Generates a file (type specified by the subclass) from the odata result set.
		 * 
		 * @param {array} aRows An array of rows. This is usually the result set from the OData response.
		 * @name com.utils.FileGenerator.prototype.generate
		 * @function
		 * @public
		 */

		/**
		 * Triggers a download from the browser to the generated file.
		 * 
		 * @param {string} sFilename Filename of the download.
		 * @name com.utils.FileGenerator.prototype.download
		 * @function
		 * @public
		 */
 
   });
   
   return FileGenerator;
});

At the end of the file you should have noticed two JSDoc sections which are describing non-existent functions. These functions are “abstract” and must be implemented by the subclass. Note that we are implementing our solution in a very object oriented way (hello template method pattern) with some functional aspects. But keep in mind that in JavaScript you have many more paradigms available.

In our case we will have one class CSVGenerator which generates a CSV file. It will store the resulting CSV file in a member variable of type string. You may run across some outdated recommendations to do string concatenations with the Array.join function to improve performance. With the latest browser versions this is not the case anymore and the default concatenation operator “+” will work and perform just fine.

The constructor allows you to define the escape, separator and line break symbols.To finally trigger the download an invisible link will be injected to the DOM which points to the CSV string.

/* global document */
sap.ui.define([
	"com/utils/FileGenerator"
], function(
	FileGenerator
) {
	"use strict";

	/**
	 * Creates a CSV file from an OData result set.
	 * 
	 * @class
	 * @public
	 * @name com.utils.CSVGenerator
	 */
	var CSVGenerator =  FileGenerator.extend("com.utils.CSVGenerator", {

		/**
		 * @param {string} [sEscape] Escape symbol which is used to wrap each data field. Default is ".
		 * @param {string} [sSeparator] Data field separator symbol. Default is ";".
		 * @param {string} [sLinebreak] Line break symbol. Default is '\r\n'.
		 * @public 
		 */
		constructor: function(sEscape, sSeparator, sLinebreak) {
			FileGenerator.call(this);
			
			this._sOutput = undefined;
			this._sEscape = sEscape || '"';
			this._sSeparator = sSeparator || ";";
			this._sLinebreak = sLinebreak || '\r\n';
		},
		
		/**
		 * Appends the value to the output field. Separator and escape symbols are applied.
		 * @param {string} sValue The value of the data field.
		 * @private
		 */
		_processCell: function(sValue) {
			this._sOutput += this._sEscape + sValue + this._sEscape + this._sSeparator;
		},
		
		/**
		 * Appends the line break symbol to the output.
		 * @private
		 */
		_processLineBreak: function() {
			this._sOutput += this._sLinebreak;
		},

		/**
		 * Generates a CSV file from the odata result set.
		 * 
		 * @param {array} aRows An array of rows. This is usually the result set from the OData response.
		 *						The result set may be modified by this function.
		 * @public
		 */
		generate: function(aRows) {
			this._sOutput = "";
			
			// In parent object
			this.loopDataRows(aRows, this._processCell.bind(this), this._processLineBreak.bind(this));
		},

		/**
		 * Triggers a download from the browser to the generated file.
		 * This is done by placing a hidden link in the DOM and triggering a click event on it.
		 * After the download the link is removed again.
		 * Ensure that the generate function has been called before, otherwise the download will not work.
		 * 
		 * @param {string} sFilename Filename of the download.
		 * @public
		 */
		download: function(sFilename) {
			if (this._sOutput !== undefined) {
				var oLink = document.createElement("a");
				oLink.href = "data:text/csv;charset=utf-8," + escape(this._sOutput);
	
				oLink.style = "visibility:hidden";
				oLink.download = sFilename + ".csv";
	
				document.body.appendChild(oLink);
				oLink.click();
				document.body.removeChild(oLink);
			}
		}

	});
	
	return CSVGenerator;
});

Now you have all the code you need to quickly export any OData / JSON data as a CSV file. And more importantly: You can quickly extend the solution to other file formats by implementing a new subclass which adheres to the FileGenerator interface.

To feed any OData to the file generator you could use the OData model class from SAPUI5 and call the read function in your controller class:

var oCSVGenerator = new CSVGenerator();
this.getView().getModel().read("/MyODataResource", {
	success: function(oData) {
		oCSVGenerator.generate(oData.results);
		oCSVGenerator.download("OData_Export");
	}
});

 

 

To report this post you need to login first.

3 Comments

You must be Logged on to comment or reply to a post.

  1. Mike Doyle

    Thanks for sharing this interesting approach Simon.  There is a SAP-standard sap.ui.core.util.Export class but it is quite limited.  What my customers want is for users to be able to download a table or list exactly as they see it on screen. They want client-side filters to be applied to the downloaded data but they don’t want the ‘growing’ limit to be applied (i.e. they want all the records in the download, not just the first 50, say).  Have you got a good solution for this?  I’ve acheived this outcome but my approach lacks elegance.

    (0) 
    1. Simon Kessler
      Post author

      Hi Doyle, the Export class is indeed a very good reference. The custom code in this post is more tailored to be enhanced and is suitable if you want to heavily customize the generation process.

      Regarding your issue: You could just take all the applied filters and apply them to another OData read call. I guess you are going through each cell manually so the grow limit is getting in your way.

      (0) 
  2. Richard Nagel

    Hi Simon,

    Interesting post!

    How about “the other way around” e.g. reading data from a CSV file and populating S/4HANA Cloud with these?

    thank you, Richard

     

    (0) 

Leave a Reply