Join the #BuildWithBuildAgent Challenge! Get recognized, earn exclusive swag, and inspire the ServiceNow Community with what you can build using Build Agent.  Join the Challenge.

Format created Excel file

DoDo labs___
Mega Sage

2 hours ago

“I have the script below that creates an Excel file and attaches it to an Incident. How can I place the last three cells (Test 7, Test 8, Test 9) under the Test6 cell? Just like in the picture.”

 

DoDolabs____0-1763315168954.png

 

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' ";
columns_of_excel += "xmlns:x='urn:schemas-microsoft-com:office:excel' ";
columns_of_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'/>";
columns_of_excel += "</Style>";

columns_of_excel += "<Style ss:ID='BlueCell'>";
columns_of_excel += "<Interior ss:Color='#0000FF' ss:Pattern='Solid'/>";
columns_of_excel += "<Font ss:Color='#FFFFFF' ss:Bold='1'/>";
columns_of_excel += "<Alignment ss:Horizontal='Center' ss:Vertical='Center' ss:WrapText='1'/>";
columns_of_excel += "<Borders>";
columns_of_excel += "<Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='2'/>";
columns_of_excel += "<Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='2'/>";
columns_of_excel += "<Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='2'/>";
columns_of_excel += "<Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='2'/>";
columns_of_excel += "</Borders>";
columns_of_excel += "</Style>";

columns_of_excel += "<Style ss:ID='BorderedCell'>";
columns_of_excel += " <Borders>";
columns_of_excel += " <Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/>";
columns_of_excel += " <Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/>";
columns_of_excel += " <Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/>";
columns_of_excel += " <Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/>";
columns_of_excel += " </Borders>";
columns_of_excel += "</Style>";

columns_of_excel += "</Styles>";

columns_of_excel += "<Worksheet ss:Name='NMHH havi támogatási riport'>";
columns_of_excel += "<Table>";

columns_of_excel += "<Row>";
columns_of_excel += "<Cell ss:StyleID='BoldHeader'><Data ss:Type='String'>Átalánydíjas teljesítés részletezése:</Data></Cell>";
columns_of_excel += "</Row>";

rows_data += "<Row>";
rows_data += "<Cell ss:StyleID='BlueCell' ss:MergeAcross='17'><Data ss:Type='String'>A teljesítési időszakban lezárt hibák</Data></Cell>";
rows_data += "</Row>";

rows_data += "<Row>";
rows_data += "<Cell ss:StyleID='BorderedCell' ss:MergeDown='1'><Data ss:Type='String'>Test1</Data></Cell>";
rows_data += "<Cell ss:StyleID='BorderedCell' ss:MergeDown='1'><Data ss:Type='String'>Test2</Data></Cell>";
rows_data += "<Cell ss:StyleID='BorderedCell' ss:MergeDown='1'><Data ss:Type='String'>Test3</Data></Cell>";
rows_data += "<Cell ss:StyleID='BorderedCell' ss:MergeDown='1'><Data ss:Type='String'>Test4</Data></Cell>";
rows_data += "<Cell ss:StyleID='BorderedCell' ss:MergeDown='1'><Data ss:Type='String'>Test5</Data></Cell>";
rows_data += "<Cell ss:StyleID='BorderedCell' ss:MergeAcross='3'><Data ss:Type='String'>Test6</Data></Cell>";
rows_data += "<Cell ss:StyleID='BorderedCell'><Data ss:Type='String'>Test7</Data></Cell>";
rows_data += "<Cell ss:StyleID='BorderedCell'><Data ss:Type='String'>Test8</Data></Cell>";
rows_data += "<Cell ss:StyleID='BorderedCell'><Data ss:Type='String'>Test9</Data></Cell>";
rows_data += "</Row>";

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

var columns_and_rows = columns_of_excel;

var inc = new GlideRecord('incident');
inc.get('e61cf9248751761003560dc8cebb35b8');

var attachment = new GlideSysAttachment();
attachment.write(inc, "NMHH.xls", "application/vnd.ms-excel", columns_and_rows);

0 Helpfuls
  • 180 Views
3 REPLIES 3

MaxMixali
Tera Guru

an hour ago

Hi, I hope can help: following the code:

 

To place Test 7, Test 8, and Test 9 under the Test 6 cell (as a second row of headers under the merged Test 6 cell), you need to add another row with those three cells positioned correctly.

Here's the corrected code:

 
 
javascript
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' ";
columns_of_excel += "xmlns:x='urn:schemas-microsoft-com:office:excel' ";
columns_of_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'/>";
columns_of_excel += "</Style>";

columns_of_excel += "<Style ss:ID='BlueCell'>";
columns_of_excel += "<Interior ss:Color='#0000FF' ss:Pattern='Solid'/>";
columns_of_excel += "<Font ss:Color='#FFFFFF' ss:Bold='1'/>";
columns_of_excel += "<Alignment ss:Horizontal='Center' ss:Vertical='Center' ss:WrapText='1'/>";
columns_of_excel += "<Borders>";
columns_of_excel += "<Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='2'/>";
columns_of_excel += "<Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='2'/>";
columns_of_excel += "<Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='2'/>";
columns_of_excel += "<Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='2'/>";
columns_of_excel += "</Borders>";
columns_of_excel += "</Style>";

columns_of_excel += "<Style ss:ID='BorderedCell'>";
columns_of_excel += " <Borders>";
columns_of_excel += " <Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/>";
columns_of_excel += " <Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/>";
columns_of_excel += " <Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/>";
columns_of_excel += " <Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/>";
columns_of_excel += " </Borders>";
columns_of_excel += "</Style>";

columns_of_excel += "</Styles>";
columns_of_excel += "<Worksheet ss:Name='NMHH havi támogatási riport'>";
columns_of_excel += "<Table>";

columns_of_excel += "<Row>";
columns_of_excel += "<Cell ss:StyleID='BoldHeader'><Data ss:Type='String'>Átalánydíjas teljesítés részletezése:</Data></Cell>";
columns_of_excel += "</Row>";

// Blue header row
rows_data += "<Row>";
rows_data += "<Cell ss:StyleID='BlueCell' ss:MergeAcross='17'><Data ss:Type='String'>A teljesítési időszakban lezárt hibák</Data></Cell>";
rows_data += "</Row>";

// First header row - Test1 through Test5 span 2 rows, Test6 spans 3 columns
rows_data += "<Row>";
rows_data += "<Cell ss:StyleID='BorderedCell' ss:MergeDown='1'><Data ss:Type='String'>Test1</Data></Cell>";
rows_data += "<Cell ss:StyleID='BorderedCell' ss:MergeDown='1'><Data ss:Type='String'>Test2</Data></Cell>";
rows_data += "<Cell ss:StyleID='BorderedCell' ss:MergeDown='1'><Data ss:Type='String'>Test3</Data></Cell>";
rows_data += "<Cell ss:StyleID='BorderedCell' ss:MergeDown='1'><Data ss:Type='String'>Test4</Data></Cell>";
rows_data += "<Cell ss:StyleID='BorderedCell' ss:MergeDown='1'><Data ss:Type='String'>Test5</Data></Cell>";
// Test6 spans across 3 columns (Test7, Test8, Test9 will be under it)
rows_data += "<Cell ss:StyleID='BorderedCell' ss:MergeAcross='2'><Data ss:Type='String'>Test6</Data></Cell>";
rows_data += "</Row>";

// Second header row - Only Test7, Test8, Test9 (under Test6)
rows_data += "<Row>";
// First 5 cells are merged down from previous row, so we skip them (no Cell elements)
rows_data += "<Cell ss:StyleID='BorderedCell'><Data ss:Type='String'>Test7</Data></Cell>";
rows_data += "<Cell ss:StyleID='BorderedCell'><Data ss:Type='String'>Test8</Data></Cell>";
rows_data += "<Cell ss:StyleID='BorderedCell'><Data ss:Type='String'>Test9</Data></Cell>";
rows_data += "</Row>";

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

var columns_and_rows = columns_of_excel;

var inc = new GlideRecord('incident');
inc.get('e61cf9248751761003560dc8cebb35b8');

var attachment = new GlideSysAttachment();
attachment.write(inc, "NMHH.xls", "application/vnd.ms-excel", columns_and_rows);
```

## Key Changes Explained:
### **Original Problem:**You had all cells (Test1 through Test9) in a single row, which places Test7, Test8, Test9 next to Test6 horizontally.
### **Solution:**1. **First header row:**   - Test1 through Test5: Use `ss:MergeDown='1'` to span 2 rows vertically   - Test6: Use `ss:MergeAcross='2'` to span 3 columns horizontally (covering the space for Test7, Test8, Test9)
2. **Second header row:**   - Only contains Test7, Test8, Test9   - The first 5 columns are automatically skipped because they're merged down from the previous row   - Test7, Test8, Test9 appear under the Test6 header
### **Visual Result:**```
┌───────┬───────┬───────┬───────┬───────┬───────────────────────┐│ Test1 │ Test2 │ Test3 │ Test4 │ Test5 │        Test6          ││       │       │       │       │       ├───────┬───────┬───────┤│       │       │       │       │       │ Test7 │ Test8 │ Test9 │└───────┴───────┴───────┴───────┴───────┴───────┴───────┴───────┘
0 Helpfuls

DoDo labs___
Mega Sage
In response to MaxMixali

37m ago

No, unfortunatelly the generated Excel file cannot be opened. "The file is corrupted, so it cannot be opened."

0 Helpfuls

MaxMixali
Tera Guru
In response to DoDo labs___

22m ago

Hi can this updated 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' ";
columns_of_excel += "xmlns:x='urn:schemas-microsoft-com:office:excel' ";
columns_of_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'/>";
columns_of_excel += "</Style>";

columns_of_excel += "<Style ss:ID='BlueCell'>";
columns_of_excel += "<Interior ss:Color='#0000FF' ss:Pattern='Solid'/>";
columns_of_excel += "<Font ss:Color='#FFFFFF' ss:Bold='1'/>";
columns_of_excel += "<Alignment ss:Horizontal='Center' ss:Vertical='Center' ss:WrapText='1'/>";
columns_of_excel += "<Borders>";
columns_of_excel += "<Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='2'/>";
columns_of_excel += "<Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='2'/>";
columns_of_excel += "<Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='2'/>";
columns_of_excel += "<Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='2'/>";
columns_of_excel += "</Borders>";
columns_of_excel += "</Style>";

columns_of_excel += "<Style ss:ID='BorderedCell'>";
columns_of_excel += "<Borders>";
columns_of_excel += "<Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/>";
columns_of_excel += "<Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/>";
columns_of_excel += "<Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/>";
columns_of_excel += "<Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/>";
columns_of_excel += "</Borders>";
columns_of_excel += "</Style>";

columns_of_excel += "</Styles>";

// CORRETTO: Nome worksheet senza caratteri speciali
columns_of_excel += "<Worksheet ss:Name='Report'>";
columns_of_excel += "<Table>";

// Prima riga
columns_of_excel += "<Row>";
columns_of_excel += "<Cell ss:StyleID='BoldHeader'><Data ss:Type='String'>Atalanyijas teljesites reszletezese:</Data></Cell>";
columns_of_excel += "</Row>";

// Riga blu
rows_data += "<Row>";
rows_data += "<Cell ss:StyleID='BlueCell' ss:MergeAcross='7'><Data ss:Type='String'>A teljesitesi idoszakban lezart hibak</Data></Cell>";
rows_data += "</Row>";

// CORRETTO: Prima riga header con merge
rows_data += "<Row>";
rows_data += "<Cell ss:StyleID='BorderedCell' ss:MergeDown='1'><Data ss:Type='String'>Test1</Data></Cell>";
rows_data += "<Cell ss:StyleID='BorderedCell' ss:MergeDown='1'><Data ss:Type='String'>Test2</Data></Cell>";
rows_data += "<Cell ss:StyleID='BorderedCell' ss:MergeDown='1'><Data ss:Type='String'>Test3</Data></Cell>";
rows_data += "<Cell ss:StyleID='BorderedCell' ss:MergeDown='1'><Data ss:Type='String'>Test4</Data></Cell>";
rows_data += "<Cell ss:StyleID='BorderedCell' ss:MergeDown='1'><Data ss:Type='String'>Test5</Data></Cell>";
rows_data += "<Cell ss:StyleID='BorderedCell' ss:MergeAcross='2'><Data ss:Type='String'>Test6</Data></Cell>";
rows_data += "</Row>";

// CORRETTO: Seconda riga - NON mettere celle per Test1-5 (sono merged)
rows_data += "<Row>";
rows_data += "<Cell ss:Index='6' ss:StyleID='BorderedCell'><Data ss:Type='String'>Test7</Data></Cell>";
rows_data += "<Cell ss:StyleID='BorderedCell'><Data ss:Type='String'>Test8</Data></Cell>";
rows_data += "<Cell ss:StyleID='BorderedCell'><Data ss:Type='String'>Test9</Data></Cell>";
rows_data += "</Row>";

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

var columns_and_rows = columns_of_excel;

var inc = new GlideRecord('incident');
inc.get('e61cf9248751761003560dc8cebb35b8');

var attachment = new GlideSysAttachment();
attachment.write(inc, "NMHH.xls", "application/vnd.ms-excel", columns_and_rows);

 

0 Helpfuls