Is there any way to merge multiple excel sheets in a single sheet in different tabs using scripting?

[Rishav Kundu]

We have a requirement to send a report which will merge 4 excel files that is  available in sys_attachment table.We have to send a single excel file and it should be merge of that 4 excel sheets in different tabs(sheets).

Please guide if there is any way to achieve this requirement.

[Seema Hegde]

What is the source of these files? Integration or manual attachment, or are they existing records in your instance? What is the criteria for the merge? If they are external files, why not merge them outside SN? I would question the requirement, and understand the intent behind it. What do they truly want to acheive?

If it has to be done, one way I can think of is to pre-create a data source, import set table and set the target table and transform maps, create a record producer to submit the attachments (if using portal), have a flow that kicks off on submit or RP or associated record that processes the attachments one by one, loading to data source and triggering the import. Once all attachments are processed, then trigger the combined file export.

[Narsing1]

@Rishav Kundu  - You can do in this way.

• To read existing excel sheet from attachment we have an api called GlideExcelParser . You can observe examples.
• After getting the each row & column, write the same to a new attachment as below.  

Sample Code

var rows_data = "";
var columns_of_excel = "<?xml version='1.0' encoding='UTF-8'?>";
columns_of_excel += "<Workbook xmlns='urn:schemas-microsoft-com:office:spreadsheet' xmlns:x='urn:schemas-microsoft-com:office:excel' xmlns:ss='urn:schemas-microsoft-com:office:spreadsheet'>";

columns_of_excel += "<Styles>";
columns_of_excel += "<Style ss:ID='BoldHeader'>";
columns_of_excel += "<Font ss:Bold='1'/>"; // Make the font bold
columns_of_excel += "</Style>";
columns_of_excel += "</Styles>";

//Sheet 1
columns_of_excel += "<Worksheet ss:Name='Sample Data1'>";

columns_of_excel += "<Table>";

columns_of_excel += "<Row>";
columns_of_excel += "<Cell ss:StyleID='BoldHeader'><Data ss:Type='String'>Shee1data</Data></Cell>";
columns_of_excel += "</Row>";

rows_data += "<Row>";
rows_data += "<Cell><Data ss:Type='String'>Row1</Data></Cell>";
rows_data += "</Row>";

rows_data += "<Row>";
rows_data += "<Cell><Data ss:Type='String'>Row2</Data></Cell>";
rows_data += "</Row>";

columns_of_excel += rows_data;
columns_of_excel += "</Table>";
columns_of_excel += "</Worksheet>";

//Sheet 2
columns_of_excel += "<Worksheet ss:Name='Sample Data2'>";

columns_of_excel += "<Table>";

columns_of_excel += "<Row>";
columns_of_excel += "<Cell ss:StyleID='BoldHeader'><Data ss:Type='String'>Shee2data</Data></Cell>";
columns_of_excel += "</Row>";

rows_data = "";

rows_data += "<Row>";
rows_data += "<Cell><Data ss:Type='String'>Row1</Data></Cell>";
rows_data += "</Row>";

rows_data += "<Row>";
rows_data += "<Cell><Data ss:Type='String'>Row2</Data></Cell>";
rows_data += "</Row>";

columns_of_excel += rows_data;
columns_of_excel += "</Table>";
columns_of_excel += "</Worksheet>";

columns_of_excel += "</Workbook>";
//You may use your table here where you are storing all different attachments together.  
//Here I am using scheduled rep. table
var sr = new GlideRecord("sysauto_report");
if (sr.get("6d33288c83da1a10e9049370ceaad3aa")) { 
    var attachment = new GlideSysAttachment();
    attachment.write(sr, "excelwithmultisheets.xls", "application/vnd.ms-excel", columns_of_excel);
}

Reference: Click here 

Once it is completed, you can see a new attachment will be added to the record and it will look like this

Thanks,

Narsing

[kedarjangam]

Hi Narsing,

Can you please advice how to change row background color.

Thanks,

Kedar