Colored rows while Export to Excel in SAPUI5

[Former Member]

Hi,

I created Export to Excel functionality as mentioned in below SAPUI5 Explored functionality -

https://sapui5.hana.ondemand.com/1.38.5/explored.html#/sample/sap.m.sample.TableExport/preview

But I need to add background color for some of the excel cells, like table headings and some text. Any pointers on how to achieve this?

I referred some blogs as well but it didn't work -

https://blogs.sap.com/2014/11/20/exporting-to-excel-from-sapmtable/

https://blogs.sap.com/2014/01/07/export-sapuitabletable-as-csv/

https://blogs.sap.com/2015/04/07/export-to-excel-customization-in-ui5/

Any kind of help will be highly appreciated.

Thanks & Regards,

Saurabh

[former_member340030]

Hi ,

You need to provide inline styling(background color) to the sap.m.table row to get the same styling in excel ..

Thanks

Viplove

[Former Member]

Thanks Viplove for the suggestion.

I am using XML view and sap.ui.table and want column headers (which are labels) with different color. I tried below inline styling but it did not work.

<Column id="col1">
    <m:Label text="Company" style="background-color:#ffcc00"/>
    <template>
         <m:Text text="{Company}"/>
    </template>
</Column>

Apart from table data, I am appending some custom text as well in the excel sheet (stored as a variable) which needs to be highlighted, any idea on that?

Thanks,

Saurabh

[former_member340030]

Hi Saurabh,

First of all there is no property known as style in SAP controls (Label) so style="background-color:#ffcc00" is wrong .. you need to do it through JavaScript in controller by getting the DOM element and add the style by changing the style property before download to excel ..

If you check the HTML rendering of the sap.m.table in developer options of chrome it will be a HTML table tag with td and th tags ... so you need to manipulate those using javascript for adding inline styles and also for adding new content you can add in the HTML of download content with style class to hide the text on the screen but it will come in download as the class styling will not have any affect on the excel download only inline has affect..

thanks

Viplove

[Former Member]

Hi Viplove,

Let's say I want to achieve above requirement for below table.

<m:Table id="testTable">
	<m:columns>
		<m:Column id="productCol" ><m:Text text="Product" /></m:Column>
		<m:Column id="supplierCol"><m:Text text="Supplier" /></m:Column>
		<m:Column id="priceCol" ><m:Text text="Price" /></m:Column>
	</m:columns>
	<m:items>
		<m:ColumnListItem>
			<m:cells>
				...
			</m:cells>
		</m:ColumnListItem>
	</m:items>
</m:Table>

And below is call from controller.

var prd= document.getElementById("__xmlview0--testTable-tblHead0"); // DOM element ID for 'Product' column which needs to be highlighted, which is a <th>


// Inline styling of 'Product' column
prd.setAttribute("style", "background-color: #ffcc00;");

// Creating Export object
var oExport = new sap.ui.core.util.Export({ 
	exportType : new sap.ui.core.util.ExportTypeCSV({
		separatorChar : ","
	}),
	
	models : oTable.getModel(),

	rows : {
		path : "/"
	},

	columns : [{
		name : "Product",
		template : {
			content : "{Product}"
		}
	}, {
		name : "Supplier",
		template : {
			content : "{Supplier}"
		}
	},  {
		name : "Price",
		template : {
			content : "{Price}"
		}
	}]
});

oExport.saveFile("Report").catch(function(oError) {
     MessageBox.error("Error when downloading data.\n\n" + oError);
}).then(function() {
   oExport.destroy();
});

Looks like there is a disconnect between DOM element and export object? How this element can bind to the export object?

Can you have a look and suggest?

Thanks,

Saurabh

[former_member340030]

Hi ,

Actually you are downloading through model data not the html table which is rendered on the screen , so the way i told you in above comments and answers will not help you .. And also one more thing the way you are exporting will not give the data in table format it will be much more like comma (,) separated values ..

thanks

Viplove ..

[Former Member]

Thanks Viplove for the inputs.

I also followed this blog and tried to go with a different approach but that also did not help.

Do you have any reference or working example on this? That will be of great help.

Thanks,

Saurabh

[former_member340030]

Hi Saurabh Mathur,

You can use javascript to download html table to excel , check out this link : https://stackoverflow.com/questions/22317951/export-html-table-data-to-excel-using-javascript-jquery...

So before downloading just provide the inline styling to the HTML to get styling the export excel

thanks

Viplove

[Former Member]

Yes, even I referred this and trying to implement my requirements.

This is doing the trick but throwing file format issues while opening the excel.

But once I open the excel, I can see my content in desired way.

Thanks,

Saurabh

[sankar_narayana]

Hi,

Could you please let me know what is the solution to make rows to bold and highlight the background color to blue only for the rows.

Very much urgent.

Thanks

[devendervb]

Use can use addStyleclass() on the control as we

[Former Member]

Hi Devender,

Method addStyleClass() did not work in excel. It worked on-screen though.

Thanks,

Saurabh