Generate Real .xlsx Files in ServiceNow Without External Libraries (OOXML ZIP Assembly)
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
an hour ago
If you've ever generated Excel files from script in ServiceNow, you've probably used one of two approaches:
- CSV -- works, but no formatting, no multi-tab, and your stakeholders have to click through import wizards.
- SpreadsheetML -- XML with an .xls extension. Supports bold headers, column widths, and multiple tabs. But Excel shows a format mismatch warning ("The file format and extension don't match") every single time the file is opened. For automated reports going to non-technical users, that warning erodes trust.
There's a third option that nobody seems to be talking about: build a real .xlsx file natively using Java ZIP classes that are already on the whitelist.
A .xlsx file is just a ZIP archive containing XML files (ECMA-376 / Office Open XML). The XML is the same complexity as SpreadsheetML, as in it's string concatenation of a known schema. The missing piece was always the ZIP packaging.
It turns out that java.util.zip.ZipOutputStream, java.util.zip.ZipEntry, and java.io.ByteArrayOutputStream are all whitelisted and accessible in global scope. I tested this on Yokohama.
What you get:
- A standards-compliant .xlsx that Excel, Google Sheets, and LibreOffice open with zero format warnings
- Bold headers, custom column widths, inline strings
- Formula injection prevention built in structurally via t="inlineStr"
- ~10x smaller files than SpreadsheetML for the same data (ZIP compression)
- No external libraries, no Packages Call Removal issues, no vendor tickets
How it works:
The script builds six XML files (content types, relationships, workbook, workbook relationships, styles, and the worksheet), pipes them through ZipOutputStream into a ByteArrayOutputStream, and attaches the resulting byte[] to a record using the binary overload of GlideSysAttachment.write().
The six required ZIP entries are:
ZIP Entry Purpose
| [Content_Types].xml | Package manifest. Declares MIME types for every part. Miss one and Excel rejects the file silently. |
| _rels/.rels | Root relationships. Points to the workbook. |
| xl/workbook.xml | Declares worksheet names and relationship IDs. |
| xl/_rels/workbook.xml.rels | Maps each sheet ID to its file path. Also links styles.xml. |
| xl/styles.xml | Font, fill, border, and cell format definitions. |
| xl/worksheets/sheet1.xml | The actual row and cell data. One file per tab. |
Gotchas:
- Use the byte[] overload of GlideSysAttachment.write(), not writeContentStream(). The streaming method expects GlideScriptableInputStream, not java.io.ByteArrayInputStream. It will fail silently on raw Java byte streams.
- Every ZIP entry matters. If [Content_Types].xml is missing an <Override> for any part, Excel opens a blank file with no error message. Ask me how I know.
- Multi-tab is straightforward: add more <Override> entries in content types, more <sheet> entries in the workbook, more relationships, and more xl/worksheets/sheetN.xml files. The pattern scales linearly.
- Memory ceiling is the same as SpreadsheetML (~50K rows) because the XML strings still have to fit in the Rhino heap before compression. For larger volumes, use Export Sets.
The script
Below is a complete, runnable background script. Change the RECIPIENT, TABLE, QUERY, and COLUMNS at the top, paste it into Scripts - Background, and run. It will:
- Query the table
- Build the .xlsx in memory
- Create a sys_email record using the staged insertion pattern (send-ignored -> attach -> bridge -> send-ready)
- The SMTP Sender picks it up on the next cycle
I've attached the full script as a .js file since the code block here will be hard to copy cleanly.
// =============================================================================
// Native .xlsx generation in ServiceNow using ZipOutputStream
// No external libraries. No Packages Call Removal issues. No format warnings.
//
// Run as: Background Script (global scope)
// Tested on: Yokohama
// Output: Sends an email with a real .xlsx attachment to the specified recipient
//
// How it works: A .xlsx file is just a ZIP archive containing XML files per the
// ECMA-376 / Office Open XML spec. The Java ZIP classes are whitelisted and
// accessible. This script builds six XML files, zips them, and attaches the
// result as a binary .xlsx via GlideSysAttachment.write(byte[]).
// =============================================================================
(function() {
// =========================================================================
// CONFIGURATION -- change these
// =========================================================================
var RECIPIENT = 'your.email@example.com';
var SUBJECT = 'Native .xlsx Demo -- OOXML ZIP Assembly';
var TABLE = 'incident';
var QUERY = 'active=true';
var SHEET_NAME = 'Active Incidents';
var FILE_NAME = 'active_incidents.xlsx';
// Columns: [fieldName, headerLabel, width]
var COLUMNS = [
['number', 'Number', 15],
['short_description', 'Short Description', 40],
['priority', 'Priority', 12],
['assigned_to', 'Assigned To', 25],
['state', 'State', 15],
['sys_created_on', 'Created', 20]
];
// =========================================================================
// JAVA IMPORTS
// =========================================================================
var ZipOutputStream = Packages.java.util.zip.ZipOutputStream;
var ZipEntry = Packages.java.util.zip.ZipEntry;
var ByteArrayOutputStream = Packages.java.io.ByteArrayOutputStream;
// =========================================================================
// HELPERS
// =========================================================================
function xmlEscape(s) {
if (!s) return '';
return s.toString()
.replace(/&/g, '&')
.replace(/</g, '<')
.replace(/>/g, '>')
.replace(/"/g, '"');
}
function colLetter(idx) {
// Supports A-Z (26 columns). Extend if you need AA+.
if (idx < 26) return String.fromCharCode(65 + idx);
return String.fromCharCode(64 + Math.floor(idx / 26)) +
String.fromCharCode(65 + (idx % 26));
}
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();
}
// =========================================================================
// BUILD THE .XLSX
// =========================================================================
var baos = new ByteArrayOutputStream();
var zip = new ZipOutputStream(baos);
// --- 1. [Content_Types].xml ---
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="' + xmlEscape(SHEET_NAME) + '" 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 Calibri 11pt, Font 1 = bold Calibri 11pt
// cellXf 0 = normal, cellXf 1 = bold (for headers)
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 ---
var ws = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' +
'<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">';
// Column widths
ws += '<cols>';
for (var c = 0; c < COLUMNS.length; c++) {
ws += '<col min="' + (c + 1) + '" max="' + (c + 1) + '" width="' + COLUMNS[c][2] + '" customWidth="1"/>';
}
ws += '</cols>';
ws += '<sheetData>';
// Header row (style index 1 = bold)
ws += '<row r="1">';
for (var h = 0; h < COLUMNS.length; h++) {
ws += '<c r="' + colLetter(h) + '1" s="1" t="inlineStr"><is><t>' +
xmlEscape(COLUMNS[h][1]) + '</t></is></c>';
}
ws += '</row>';
// Data rows
var gr = new GlideRecord(TABLE);
gr.addEncodedQuery(QUERY);
gr.query();
var rowNum = 2;
while (gr.next()) {
ws += '<row r="' + rowNum + '">';
for (var v = 0; v < COLUMNS.length; v++) {
var val = gr.getDisplayValue(COLUMNS[v][0]);
// t="inlineStr" prevents formula injection (=, +, -, @)
ws += '<c r="' + colLetter(v) + rowNum + '" t="inlineStr"><is><t>' +
xmlEscape(val || '') + '</t></is></c>';
}
ws += '</row>';
rowNum++;
}
ws += '</sheetData></worksheet>';
addEntry(zip, 'xl/worksheets/sheet1.xml', ws);
zip.close();
var rowCount = rowNum - 2;
gs.info('OOXML ZIP: Generated ' + rowCount + ' rows');
// =========================================================================
// CREATE EMAIL AND ATTACH
// =========================================================================
// Staged Insertion: create as send-ignored, attach, then release
var mail = new GlideRecord('sys_email');
mail.initialize();
mail.type = 'send-ignored';
mail.subject = SUBJECT;
mail.recipients = RECIPIENT;
mail.body = '<p>Attached: ' + FILE_NAME + ' (' + rowCount + ' rows)</p>' +
'<p>Generated by OOXML ZIP assembly. No external libraries.</p>';
mail.insert();
var mailSysId = mail.getUniqueValue();
// Attach the .xlsx (byte[] overload)
var sa = new GlideSysAttachment();
var attSysId = sa.write(
mail,
FILE_NAME,
'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
baos.toByteArray()
);
// Bridge record (defensive -- see community discussion on sys_email_attachment)
var bridge = new GlideRecord('sys_email_attachment');
bridge.initialize();
bridge.setValue('email', mailSysId);
bridge.setValue('attachment', attSysId);
bridge.setValue('content_disposition', 'attachment');
bridge.insert();
// Release to SMTP Sender
mail = new GlideRecord('sys_email');
mail.get(mailSysId);
mail.type = 'send-ready';
mail.update();
gs.info('OOXML ZIP: Email queued to ' + RECIPIENT + ' with ' + FILE_NAME);
})();I used this pattern for a monthly ITAM multi-device report. 1,078 users, 4,329 rows. The output here was 222KB (the same data as SpreadsheetML was ~2-3MB). The Excel file opened it instantly with no warnings. Bold headers, column widths, and typed cells all rendered correctly.
What I tried that didn't work:
Before landing on this approach I tested two other paths to native Excel generation:
- sn_export_data.ExcelExport: referenced in a community thread but the constructor doesn't exist on Yokohama. The thread where it appeared has a participant flagging the sample as AI-generated. Confirmed: the API is not real.
- com.glide.generators.ExcelExporter: this is the actual engine behind the platform's "Export to Excel" UI action, and it IS on the Tokyo whitelist. But its constructor requires com.glide.choice.ChoiceList, which throws SecurityException. The whitelist says yes, the security layer says no.
Why this matters:
SpreadsheetML has been the go-to for over a decade. It works. But that format warning has always been the ugly compromise. If you're sending automated reports to executives, customers, or external stakeholders, "click Yes to open the file" is not a great look. This approach eliminates that compromise entirely using classes that are already available to you.
0 REPLIES 0
