how can i export data into excel [.xlsx] via script

[snavuluri]

Hi All,

 Please guide on,  how i can export data into excel [.xlsx] via script.

Regards,

Suresh.

[smk]

Hi Ankur,

The above script is working well but this code works only for CSV.

Is there any solution to get in proper XLS or XLSX format? The attachment is getting inserted, after downloading and opening excel it opens with message as 'file is corrupted or unsafe'

var attachment = new GlideSysAttachment();
var attachmentRec = attachment.write(currentObject, "export.csv", "text/csv", data);

Tried the below formats replacing content_type "text/csv"

xlsx: application/xlsx OR application/vnd.openxmlformats-officedocument.spreadsheetml.sheet

xls: application/vnd.ms-excel

[gabez]

Were you able to find a solution for this problem? I have been having the same issue and have tried multiple solutions to no avail. here is a snippet of some things i have tried (other solutions are commented out): 

		var contents = XLSX.writeXLSX(wb, {
			bookType: "xlsx",
			type: "base64"
		});
		/**/

		var strEncodedString = contents;

		var gsUtilGs = gs.base64Decode(strEncodedString);
		gs.info(gsUtilGs);
		gsUtilGs = gs.base64Encode(gsUtilGs);

		var fileName = fname + ".xlsx";

		var gr = new GlideRecord('sys_properties');
		gr.initialize();
		gr.setValue('property_name', 'excel');
		gr.insert();
		var record_sys_id = gr.sys_id;

		var rec = new GlideRecord('sys_properties');
		rec.get(record_sys_id);

		var contentType = 'application/vnd.ms-excel';
		//var contentType = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet';

		var base64Encodedcontent = contents;

		var attachment = new GlideSysAttachment();

		//var agr = attachment.writeBase64( rec, fileName, contentType, "data:"+ contentType +";base64,"+base64Encodedcontent);
		var agr = attachment.write( rec, fileName, contentType, "data:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;base64,"+base64Encodedcontent);
		//var agr = attachment.write( rec, fileName, contentType, gsUtilGs);
		//var agr = attachment.write( rec, fileName, contentType, "data:"+ contentType +";base64,"+base64Encodedcontent);
		//var agr = attachment.write( rec, fileName, contentType, base64Encodedcontent);

		action.setRedirectURL("/sys_attachment.do?sys_id=" + agr);

, the first line uses sheetJS to create the xlsx file essentially, returns the file in base64 string. 

[snow123]

Hi,

  I have updated in UI Action, but the "on click" action is not working and not getting download . Any other inputs.

 Where the same working in background script, which, will show the attachment in sys_attachment table.

Regards,

Suresh

[Ankur Bawiskar]

@snow123 

I would suggest to raise new question as the question for this thread is different and already answered.

Regards
Ankur

[AJAY1231]

Hi , I was able to acheive this by bringing to Excel (.xlsx) instead of csv.

Just below copy paste the code, and u will have excel created in the specified record.

Script:

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'>";

// Define the Styles for bold headers

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>";

columns_of_excel += "<Worksheet ss:Name='Incident Data'>";

columns_of_excel += "<Table>";

// Define the headers and apply the bold style

columns_of_excel += "<Row>";

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

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

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

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

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

columns_of_excel += "</Row>";

// Retrieve the incidents for the 'software' category

var gr = new GlideRecord('incident');

gr.addQuery('category', 'software');

gr.query();

while (gr.next()) {

    rows_data += "<Row>";

    rows_data += "<Cell><Data ss:Type='String'>" + gr.number + "</Data></Cell>";

    rows_data += "<Cell><Data ss:Type='String'>" + gr.opened_at + "</Data></Cell>";

    rows_data += "<Cell><Data ss:Type='String'>" + gr.short_description + "</Data></Cell>";

    rows_data += "<Cell><Data ss:Type='String'>" + gr.sys_created_on + "</Data></Cell>";

    rows_data += "<Cell><Data ss:Type='String'>" + gr.caller_id.getDisplayValue() + "</Data></Cell>";

    rows_data += "</Row>";

}

// Close the XML structure

columns_of_excel += rows_data;

columns_of_excel += "</Table>";

columns_of_excel += "</Worksheet>";

columns_of_excel += "</Workbook>";

// Combine the columns (headers) and rows data

var columns_and_rows = columns_of_excel; // complete data including headers and rows

// Get the current user record for attaching the file

var user = new GlideRecord('sys_user');

user.addQuery('sys_id', gs.getUserID());

user.query();

user.next();

// Create the attachment

var attachment = new GlideSysAttachment();

attachment.write(user, "Incident software.xls", "application/vnd.ms-excel", columns_and_rows);

gs.print("Excel file exported and attached successfully. Please check.");