Skip to Content
Author's profile photo Navya Shree

Download the Model Data to a CSV/Excel file in UI5

We can download the data present in JSON or the data retrieved from oData to an csv  file.
The bolow code snippets can be used to achieve the same.

Data.Json:

JSON is a very lightweight format for storing data and can be directly used as a data source for SAPUI5 applications. Now create a JSON file within the model folder of the application.

Specify the data in the JSON file as below:

{
	"items": [{
		"firstName": "Navya",
		"lastName": "Gowda",
		"jobTitle": "Senior System Engineer",
		"location": "India",
		"department": "EASSAP"
	}, {
		"firstName": "John",
		"lastName": "Smith",
		"jobTitle": "Technology Architect",
		"location": "USA",
		"department": "EASSAP"
	}, {
		"firstName": "Emma",
		"lastName": "Watson",
		"jobTitle": "System Engineer",
		"location": "Europe",
		"department": "Testing"
	}, {
		"firstName": "Rahul",
		"lastName": "Singh",
		"jobTitle": "Manager",
		"location": "India",
		"department": "EASSAP"
	}, {
		"firstName": "Sinchi",
		"lastName": "Kotaro",
		"jobTitle": "System Engineer",
		"location": "UK",
		"department": "EASSAP"
	}]
}

To access the above JSON file throughout the application, specify the JSON file URI in the model under sap.ui5 section in the manifest.json file.
we specify the type as JSON and URI which is the path to the JSON data to instantiate the JSON Model as shown below.

"sap.ui5": {
…………………………….
		"models": {
			"i18n": {
				"type": "sap.ui.model.resource.ResourceModel",
				"settings": {
					"bundleName": "DownloadToExcel.i18n.i18n"
				}
			},
			"Data": {
				"Data": {
				"type": "sap.ui.model.json.JSONModel",
				"uri": "model/Data.json"
				}
			}
			
		},
		"resources": {
			"css": [{
				"uri": "css/style.css"
			}]
		}
	}

 

View:

The view contains a Button as shown below,
on clicking the button “onDataExport” function will be executed to download the Data to an CSV file.

	<Button icon="sap-icon://download" text="Download" press="onDataExport" tooltip="{i18n>download}" id="download"/>

 

Controller:

In the init() function of the Controller fetch the json data and make it available for the controller to access.

Add the below Required libraries to the Controller.

“sap/ui/core/util/Export”, “sap/ui/core/util/ExportTypeCSV”.

onInit: function() {
	var oModel = new sap.ui.model.json.JSONModel("model/Data.json");
	sap.ui.getCore().setModel(oModel, "oModel");
}

“onDataExport” code to download the data to an CSV file is shown below.

	onDataExport: sap.m.Table.prototype.exportData || function() {

			var oModel = sap.ui.getCore().getModel("oModel");
			var oExport = new Export({

				exportType: new ExportTypeCSV({
					fileExtension: "csv",
					separatorChar: ";"
				}),

				models: oModel,

				rows: {
					path: "/items"
				},
				columns: [{
					name: "First Name",
					template: {
						content: "{firstName}"
					}
				}, {
					name: "Last name",
					template: {
						content: "{lastName}"
					}
				}, {
					name: "Job Title",
					template: {
						content: "{jobTitle}"
					}
				}, {
					name: "Location",
					template: {
						content: "{location}"
					}
				}, {
					name: "Department",
					template: {
						content: "{department}"
					}
				}]
			});
			console.log(oExport);
			oExport.saveFile().catch(function(oError) {

			}).then(function() {
				oExport.destroy();
			});
		}

 

OUTPUT:

Clicking on the Download Button will download the data to an CSV File.

Use ‘Convert Text to Column Property’ To convert the text to Column as shown below.

 

Note: you can use fileExtension as ‘xls’ t download the data into an Excel File, but you get a warning while opening the File which you can ignore.

Assigned tags

      13 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Srikar Nagadevara
      Srikar Nagadevara

      Good Work..Keep it up...

      Author's profile photo Navya Shree
      Navya Shree
      Blog Post Author

      Thank you Srikar 🙂

      Author's profile photo Zoltan Buzas
      Zoltan Buzas

      The CSV download for JSON model works me fine. But if the model is a ODATA service its download only the first 100 row of the table? How can I get to download the whole table?

      Author's profile photo Venkat Reddy Ravula
      Venkat Reddy Ravula

      Hello Buzas,

       

      I think you might need to increase model size with following code example: oModel.setSizeLimit(600);

       

      Author's profile photo Chitrak Shaw
      Chitrak Shaw

      Thanks for the blog. When I try to download in 'xls' format, I get a blank spreadsheet. But, when I do the same in csv it is working fine. I just modified code to following:

      // Type that will be used to generate the content. Own ExportType's can be created to support other formats
      				exportType : new ExportType({
      					fileExtension : "xls"
      				}),

       

      Is there anything else I have to take care of?

       

      Thanks in advance.

      Author's profile photo maithre mareppa
      maithre mareppa

      HI shree ,

      here is i am new  to UI5  but i  have  task .what  my requirement   i displayed table  ,when i press button the data should  be download as PDF or TEXT Formate ,  but table contain data form  backend system ,

       

       

      Author's profile photo Navya Shree
      Navya Shree
      Blog Post Author

      Hi Maithre,

      You can go for DocXLib(Third party library) to download data in text file 🙂

      You can refer the below link for the same.

      https://docxtemplater.com/
      https://github.com/open-xml-templating/docxtemplater

       

      Thanks and Regards,

      Navya

      Author's profile photo maithre mareppa
      maithre mareppa

      HI Navya !!

      i am the new to Ui5/Fiori and  even i don't know how to approach for  DocXLib(Third party library).

      could you  please share indetails .

      Author's profile photo Akshaya Parthasarathy
      Akshaya Parthasarathy

      Hi,

      How to use the convert text to column property?

      I changed fileExtension to 'xls' but it is still showing as CSV . Pelase help

       

      Author's profile photo Mark Tsiareshka
      Mark Tsiareshka

      Hello Akshaya,

      To separate columns while file generating you can use "\t" separator char. Then there is no need to convert text to columns in MS Excel.

      Kind regards,

      Mark

      Author's profile photo Nooruddin Bohra
      Nooruddin Bohra

      Excellent Blog! helped a lot. Thanks.

      Few suggestions

      • If want to download a CVS then use "," as separator afterwards you won't be required to convert text to columns.
      • If you want a Tab delimited file(which will work will file extension XLS) then as a separator put "\t" and fileExtension: "xls" and you're done.

      Regards,

      Nooruddin Bohra

      Author's profile photo Kshitija U Shetty
      Kshitija U Shetty

      Hi Navya,

      Can I customize the name of the file in download? I want to change the name from "data" to something more descriptive.

      Thanks and Regards

      Kshitija U Shetty

      Author's profile photo Vaibhav Sapra
      Vaibhav Sapra

      Yes Kshitija, you can change the name of downloaded file by using .saveFile method of oExport.

      oExport.saveFile('Your File Name').catch(function(oErr){

      //whatever

      }).then(function(){

      //whatever

      });