Question Re: Export Excel with multiple Headers Using Spreadsheet in SAPUI5 in Technology Q&A

Export Excel with multiple Headers Using Spreadsheet in SAPUI5

former_member494689 — Wed, 01 Jul 2020 02:44:13 GMT

Hi Experts,

Is it possible to configure the columns to show the following result using spreadsheet in SAPUI5?

If yes, kindly let us know the sample/reference. Thanks.

To export excel, I refered this link. But, it is just one line header and no merge cells.

https://sapui5.hana.ondemand.com/sdk/#/entity/sap.ui.export.Spreadsheet/sample/sap.ui.export.sample.json/code/Spreadsheet.controller.js

Best Regards,

Thiri

Re: Export Excel with multiple Headers Using Spreadsheet in SAPUI5

felipe_dimmu23 — Wed, 01 Jul 2020 11:47:29 GMT

Hi Thiri,

Yyou can use class sap.ui.export.Spreadsheet to set up a lot of things in a custom export to excell button of yours, but if I understood correctly, as explained in this, grouping headers is not possible.

Regards,

Felipe

Re: Export Excel with multiple Headers Using Spreadsheet in SAPUI5

former_member494689 — Thu, 02 Jul 2020 00:47:12 GMT

Hi Felipe,

Thanks for your feedback.

Yes, I am using sap.ui.export.Spreadsheet.

I also found no sample documents or blogs for merge header cell pattern yet. 😞

Best Regards,

Thiri

Re: Export Excel with multiple Headers Using Spreadsheet in SAPUI5

former_member494689 — Thu, 02 Jul 2020 06:04:49 GMT

Hi Felipe,

If you know any other ways, please let us know.

Thank You.

Best Regards,

Thiri

Re: Export Excel with multiple Headers Using Spreadsheet in SAPUI5

former_member494689 — Mon, 06 Jul 2020 00:54:59 GMT

Hi all,

I can export the above file format (merged cells in exported excel file) using third-party library 'XLSX.js'.

1. Download the library file from the link : https://github.com/SheetJS/sheetjs

2. Add this library file to your project.

3. Declare the library in your controller.

4. Configure the following code in your controller.

var data = [ *set your data here* ]

/* generate worksheet */

var ws = XLSX.utils.aoa_to_sheet(data);

/* merged cells settings */

var mergedCells = { s: {r: 0, c: 1}, e: {r: 0, c: 2}};

if (!ws["!merges"]) ws["!merges"] = [];

ws["!merges"].push(mergedCells);

/* generate workbook */

var wb = XLSX.utils.book_new();

XLSX.utils.book_append_sheet(wb, ws, "sheet1");

XLSX.writeFile(wb, "test.xlsx");

Best Regards,

Thiri