Exporting .xlsx files with multiple worksheets

Matty3 · ‎03-10-2023

My current requirement is to create a UI Action that queries a table and attaches the results to a form as an .xlsx attachment. I've seen a few suggestions on the forum for creating a spreadsheet from a query, but I couldn't find any questions about multiple worksheets. So, I guess I'm really asking two separate questions.

Is there a recommended way to create an .xlsx from a query?

Is it possible to create an .xlsx with two or more worksheets?

Thank you!

Matty3 · ‎03-13-2023

Can you provide a reference to the Excel Export API? I was unable to find it in ServiceNow or online. Thank you!

Craig Talbert4 · 3 weeks ago

The sn_export_data.ExcelExport API referenced above doesn't exist. I tested on Yokohama and the constructor is not defined. The sample code appears to be AI-generated.

Here's an approach that actually works. A .xlsx file is just a ZIP archive of XML files (ECMA-376 / Office Open XML). The Java ZIP classes are whitelisted and accessible in global scope:

java.util.zip.ZipOutputStream
java.util.zip.ZipEntry
java.io.ByteArrayOutputStream

You build six XML strings (content types, relationships, workbook, workbook rels, styles, worksheet), zip them, and attach the byte[] via GlideSysAttachment.write(). The output is a real .xlsx that Excel opens with zero format warnings. No more having to worry about "file format and extension don't match" dialogs.

Here's the core pattern (stripped to essentials):

var ZipOutputStream = Packages.java.util.zip.ZipOutputStream;
var ZipEntry = Packages.java.util.zip.ZipEntry;
var ByteArrayOutputStream = Packages.java.io.ByteArrayOutputStream;

var baos = new ByteArrayOutputStream();
var zip = new ZipOutputStream(baos);

function addEntry(zip, path, content) {
    zip.putNextEntry(new ZipEntry(path));
    var bytes = new java.lang.String(content).getBytes('UTF-8');
    zip.write(bytes, 0, bytes.length);
    zip.closeEntry();
}

// 1. [Content_Types].xml -- package manifest
addEntry(zip, '[Content_Types].xml',
    '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' +
    '<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">' +
    '<Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/>' +
    '<Default Extension="xml" ContentType="application/xml"/>' +
    '<Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/>' +
    '<Override PartName="/xl/worksheets/sheet1.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>' +
    '<Override PartName="/xl/styles.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml"/>' +
    '</Types>');

// 2. _rels/.rels
addEntry(zip, '_rels/.rels',
    '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' +
    '<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">' +
    '<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/>' +
    '</Relationships>');

// 3. xl/workbook.xml
addEntry(zip, 'xl/workbook.xml',
    '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' +
    '<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">' +
    '<sheets><sheet name="Sheet1" sheetId="1" r:id="rId1"/></sheets></workbook>');

// 4. xl/_rels/workbook.xml.rels
addEntry(zip, 'xl/_rels/workbook.xml.rels',
    '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' +
    '<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">' +
    '<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet1.xml"/>' +
    '<Relationship Id="rId2" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles" Target="styles.xml"/>' +
    '</Relationships>');

// 5. xl/styles.xml (font 0 = normal, font 1 = bold, cellXf 1 = bold)
addEntry(zip, 'xl/styles.xml',
    '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' +
    '<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">' +
    '<fonts count="2"><font><sz val="11"/><name val="Calibri"/></font>' +
    '<font><b/><sz val="11"/><name val="Calibri"/></font></fonts>' +
    '<fills count="2"><fill><patternFill patternType="none"/></fill>' +
    '<fill><patternFill patternType="gray125"/></fill></fills>' +
    '<borders count="1"><border><left/><right/><top/><bottom/><diagonal/></border></borders>' +
    '<cellStyleXfs count="1"><xf numFmtId="0" fontId="0" fillId="0" borderId="0"/></cellStyleXfs>' +
    '<cellXfs count="2"><xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0"/>' +
    '<xf numFmtId="0" fontId="1" fillId="0" borderId="0" xfId="0" applyFont="1"/></cellXfs>' +
    '</styleSheet>');

// 6. xl/worksheets/sheet1.xml -- your data goes here
var ws = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' +
    '<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">' +
    '<cols><col min="1" max="1" width="15"/><col min="2" max="2" width="40"/>' +
    '<col min="3" max="3" width="12"/></cols><sheetData>';

// Bold header row (s="1" = cellXf index 1)
ws += '<row r="1">';
ws += '<c r="A1" s="1" t="inlineStr"><is><t>Number</t></is></c>';
ws += '<c r="B1" s="1" t="inlineStr"><is><t>Short Description</t></is></c>';
ws += '<c r="C1" s="1" t="inlineStr"><is><t>Priority</t></is></c>';
ws += '</row>';

// Data rows
var gr = new GlideRecord('incident');
gr.addEncodedQuery('active=true');
gr.setLimit(100); // limit for demo
gr.query();
var r = 2;
while (gr.next()) {
    ws += '<row r="' + r + '">';
    ws += '<c r="A' + r + '" t="inlineStr"><is><t>' + xmlEsc(gr.getValue('number')) + '</t></is></c>';
    ws += '<c r="B' + r + '" t="inlineStr"><is><t>' + xmlEsc(gr.getDisplayValue('short_description')) + '</t></is></c>';
    ws += '<c r="C' + r + '" t="inlineStr"><is><t>' + xmlEsc(gr.getDisplayValue('priority')) + '</t></is></c>';
    ws += '</row>';
    r++;
}
ws += '</sheetData></worksheet>';
addEntry(zip, 'xl/worksheets/sheet1.xml', ws);
zip.close();

// Attach to any GlideRecord -- use the byte[] overload, NOT writeContentStream
var sa = new GlideSysAttachment();
sa.write(targetGr, 'report.xlsx',
    'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
    baos.toByteArray());

function xmlEsc(s) {
    if (!s) return '';
    return s.toString().replace(/&/g,'&amp;').replace(/</g,'&lt;').replace(/>/g,'&gt;').replace(/"/g,'&quot;');
}

Key things to know:

Use GlideSysAttachment.write(GlideRecord, String, String, byte[]) -- the byte[] overload. writeContentStream() expects GlideScriptableInputStream and won't work with ByteArrayInputStream.
All six ZIP entries are required. If [Content_Types].xml is missing an Override for any part, Excel opens a blank file with no error.
t="inlineStr" on every cell prevents formula injection (=, +, -, @).
Multi-tab: add more Override entries in content types, more sheet entries in workbook.xml, more relationships, and more sheetN.xml files.
Same ~50K row memory ceiling as SpreadsheetML, but output is ~10x smaller thanks to ZIP compression.

I wrote a full runnable background script (with email sending via staged insertion) as a Developer Article here: https://www.servicenow.com/community/developer-forum/generate-real-xlsx-files-in-servicenow-without-...

Tested on Yokohama. 4,300 rows, 222KB output, zero warnings.