Question Re: Colored rows while Export to Excel in SAPUI5 in Technology Q&A

Colored rows while Export to Excel in SAPUI5

Former Member — Thu, 06 Jul 2017 00:21:49 GMT

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

Re: Colored rows while Export to Excel in SAPUI5

former_member340030 — Thu, 06 Jul 2017 07:41:29 GMT

Hi ,

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

Thanks

Viplove

Re: Colored rows while Export to Excel in SAPUI5

Former Member — Thu, 06 Jul 2017 20:17:04 GMT

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

Re: Colored rows while Export to Excel in SAPUI5

former_member340030 — Fri, 07 Jul 2017 13:05:20 GMT

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

Re: Colored rows while Export to Excel in SAPUI5

Former Member — Sat, 08 Jul 2017 00:43:26 GMT

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

Re: Colored rows while Export to Excel in SAPUI5

former_member340030 — Mon, 10 Jul 2017 09:27:42 GMT

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 ..

Re: Colored rows while Export to Excel in SAPUI5

Former Member — Tue, 11 Jul 2017 00:15:29 GMT

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

Re: Colored rows while Export to Excel in SAPUI5

former_member340030 — Wed, 12 Jul 2017 09:33:51 GMT

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-is-not-working-properl

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

thanks

Viplove

Re: Colored rows while Export to Excel in SAPUI5

Former Member — Thu, 13 Jul 2017 00:51:27 GMT

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

Re: Colored rows while Export to Excel in SAPUI5

devendervb — Sat, 15 Jul 2017 02:31:45 GMT

Use can use addStyleclass() on the control as we

Re: Colored rows while Export to Excel in SAPUI5

Former Member — Sun, 16 Jul 2017 08:19:00 GMT

Hi Devender,

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

Thanks,

Saurabh

Re: Colored rows while Export to Excel in SAPUI5

sankar_narayana — Wed, 05 Sep 2018 13:30:33 GMT

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