Export to Excel in XS Project

[Former Member]

Hi,

I want to export data from a table to excel in xs project.

Please help me.

[supriya_sahu]

Hi All,

May I know where the sample code is available in SAP SHINE.

Thanks,

Supriya

[yoppie_ariesthio]

Hi Spriya,

You can access SAP SHINE here :

yourip:port/sap/hana/democontent/epm/ui/NewLaunchpad.html

Please contact your basis to install SAP HANA SHINE democontent first.

Best Regards,

Yoppie

[amit_agarwal21]

Hi Thomas -

While doing this, can we also format the data before putting it in excel. For ex - Instead of using dot as decimal separator, can we change it to comma or vice-versa.

regards

Amit

[Former Member]

This message was moderated.

[thomas_jung]

Embedded in a web page - via XSJS?  If so there is a sample of this in the SHINE content.  I will cut and paste a bit of it here for you to view; but I really recommend studying the complete sample in SHINE.

function downloadExcel() {

                var
body = '';

                try {

                                var
query = 'SELECT TOP 25000 "PurchaseOrderId", "PartnerId",
"CompanyName", "CreatedByLoginName",
"History.CREATEDAT", "GrossAmount" '

                                                                +
'FROM "sap.hana.democontent.epm.data::purchaseOrderHeaderExternal"
order by "PurchaseOrderId"';

                                $.trace.debug(query);

                                var
conn = $.db.getConnection();

                                var
pstmt = conn.prepareStatement(query);

                                var
rs = pstmt.executeQuery();

                                body
= MESSAGES.getMessage('SEPM_POWRK', '002') + "\t" + // Purchase

                                                                                                                                                                                                                                                                                // Order ID

                                MESSAGES.getMessage('SEPM_POWRK',
'003') + "\t" + // Partner ID

                                MESSAGES.getMessage('SEPM_POWRK',
'001') + "\t" + // Company Name

                                MESSAGES.getMessage('SEPM_POWRK',
'004') + "\t" + // Employee

                                                                                                                                                                                                                                                //
Responsible

                                MESSAGES.getMessage('SEPM_POWRK',
'005') + "\t" + // Created At

                                MESSAGES.getMessage('SEPM_POWRK',
'006') + "\n"; // Gross Amount

                                while
(rs.next()) {

                                                body
+= rs.getNString(1) + "\t" + rs.getNString(2) + "\t"

                                                                                +
rs.getNString(3) + "\t" + rs.getNString(4) + "\t"

                                                                                +
rs.getDate(5) + "\t" + rs.getDecimal(6) + "\n";

                                }

                } catch
(e) {

                                $.response.status
= $.net.http.INTERNAL_SERVER_ERROR;

                                $.response.setBody(e.message);

                                return;

                }

                $.response.setBody(body);

                $.response.contentType
= 'application/vnd.ms-excel; charset=utf-16le';

                $.response.headers.set('Content-Disposition',

                                                'attachment;
filename=Excel.xls');

                $.response.status
= $.net.http.OK;

}

[Former Member]

Hi,

Actually my requirement is to call a stored procedure.

Which Internally query 2 different tables and export both the results to a single excel sheet.

So that 2 different tables will export to 2 different sheets in same excel file.

Please help me.

[thomas_jung]

So calling the stored procedure instead of a the SQL statement in the above example isn't really that much different.  Its all basic XSJS - SQL statement or Procedure doesn't change that much.  What will be complex will be two tabs in teh Excel.  Text tab delimited or csv doesn't support mutliple tabs. You will have to use the Excel XML format.  This doesn't really have anything directly to do with HANA or XS.  You will have to research this format on the internet.  Its much harder to work with than just creating one of the text based formats.  My advice, make sure the requirement is really worth the effort. You could MUCH easier create multiple text files - one per table.

[former_member196942]

Hi Thomas,

                    I have used the same for my project.

                    The issue is that when I enter the URL of xsjs file in browser, I am able to get the

                    excel file, but when I do it through UI (button click) I am not getting any dialog window/excel file. However, in the NETWORKS the xsjs file is successfully called and show "OK" messag.

Please guide.

[Former Member]

Hi,

I was able to get the export working with a similar code, but the German umlaut characters

are not recognised in the excel.

I am encoding it with utf-16le,  but the umlauts appear to be scrambled.

Any way to solve this ?

Kindly let me know.

Regards,

Kiran

[Former Member]

Hi Kiran,

I was facing the same problem with Portuguese characters, but I was solved after I prepended the file content with the correct Byter Order Mark, in this case, writing \uFEFF for UTF16-LE.

[shama_parween]

Hi Priya,

Were you able to resolve this issue? I am facing the same issue.

Thanks You