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?
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😞
We only have to run through this object, prune some irrelevant fields and create our file!
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.
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.
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:
- 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");
}
});- SAP Managed Tags:
3 Comments
