Apply formating using script on attachment

[Rj27]

Hello,

I am generating an attachment of type excel using

GlideSysAttachment().write();
I want to add some formatting/formula to it.

Can we do it using script so that when user downloads the file, data is displayed based on the formatting ?

[cloudops]

Yes, you can add formatting and formulas to an Excel file generated in ServiceNow using scripts. However, it's important to note that ServiceNow doesn't natively support Excel formatting or formulas. You would need to use a third-party library such as Apache POI (for Java) or a similar library for JavaScript to create and manipulate Excel files.

Here are the general steps you would need to follow:

1. Import the necessary library into ServiceNow. This could be done by uploading the library as a script include.

2. Create a new script that uses the library to generate an Excel file. This script would need to do the following:

- Create a new Excel workbook.
- Add a new sheet to the workbook.
- Add data to the sheet, including any necessary formatting or formulas.
- Save the workbook as an attachment in ServiceNow.

3. Trigger the script when the user requests to download the file. This could be done using a UI action, a business rule, or another appropriate trigger.

Here's a very basic example of what the script might look like:

javascript
var ApachePOI = new ApachePOILibrary(); // Assuming you've imported Apache POI as a script include

var workbook = ApachePOI.createWorkbook();
var sheet = workbook.createSheet('Sheet 1');

// Add data to the sheet
var row = sheet.createRow(0);
var cell = row.createCell(0);
cell.setCellValue('Hello, world!');

// Add formatting
var cellStyle = workbook.createCellStyle();
cellStyle.setFillForegroundColor(ApachePOI.getColor('YELLOW'));
cell.setCellStyle(cellStyle);

// Add a formula
var formulaCell = row.createCell(1);
formulaCell.setCellFormula('A1*2');

// Save the workbook as an attachment
var outputStream = new GlideSysAttachmentOutputStream('example.xlsx');
workbook.write(outputStream);
outputStream.close();

For asking ServiceNow-related questions try this :
For a better and more optimistic result, please visit this website. It uses a Chat Generative Pre-Trained Transformer ( GPT ) technology for solving ServiceNow-related issues.
Link - nowgpt.ai

[Iraj Shaikh]

Hi @Rj27 

The `GlideSysAttachment().write()` method is used to create and attach files to records. However, this method does not provide functionality to directly apply formatting or formulas to Excel files. The `write()` method is used to attach a file as is, without any manipulation of the content.

To apply formatting or formulas to an Excel file, you would need to manipulate the file before attaching it to the ServiceNow record. Unfortunately, ServiceNow does not have built-in capabilities to manipulate Excel files at this level of detail.

Here are a couple of approaches you could consider:

1. Use a third-party library: You could use a third-party library that can be integrated with ServiceNow to manipulate Excel files. One such library is Apache POI, which is a Java library for manipulating various file formats based upon the Office Open XML standards (OOXML) and Microsoft's OLE 2 Compound Document format (OLE2). However, integrating such libraries with ServiceNow would require you to host the library externally and call it via web services, as ServiceNow does not allow the direct use of Java libraries within its scripting environment.

2. Manipulate the file externally: You could create and format the Excel file outside of ServiceNow using a server-side script or a tool that supports Excel file manipulation. Once the file is formatted as desired, you can then upload it to ServiceNow and attach it to the appropriate record using the `GlideSysAttachment().write()` method.

3. Generate the file in the desired format: If you have control over the generation of the Excel file, you could generate it with the desired formatting and formulas included. This would likely involve using an external system or service that can create formatted Excel files, which you would then import into ServiceNow and attach to the record.

4. Client-side manipulation: Another approach is to provide the raw data to the user and rely on client-side scripts or macros within Excel to apply the formatting and formulas after the user has downloaded the file. This would require the user to have some level of technical knowledge to run the scripts or macros.

Unfortunately, none of these solutions are as straightforward as simply applying formatting or formulas directly within a ServiceNow script. Each requires additional steps and potentially the use of external systems or services.

Please mark this response as correct or helpful if it assisted you with your question.

[Rj27]

Hi Iraj,
Thanks for the response. 
Yes one way is to use office templates but it involves cost, so trying to avoid that way.

[Ramesh Lohar]

Unfortunately, ServiceNow's GlideSysAttachment API does not support adding formatting or formulas to Excel files. The API is primarily used for creating, reading, and deleting attachments, but it does not provide functionality for manipulating the content of those attachments, especially not in a format-specific way like adding Excel formulas.

However, you can achieve this by following these steps:

1. Create the Excel file with the required formatting/formula using a third-party library like Apache POI (for Java) or Openpyxl (for Python). You would need to host this on a separate server as ServiceNow does not support these libraries.

2. Generate the data in ServiceNow that you want to write to the Excel file.

3. Send the data to the server hosting the third-party library (from step 1) via a REST API.

4. The server-side script uses the library to write the data to the Excel file and apply the necessary formatting/formula.

5. The script then saves the Excel file and sends it back to ServiceNow via the REST API.

6. In ServiceNow, use the GlideSysAttachment API to attach the received Excel file to the desired record.

Please note that this approach requires a separate server to host the third-party library and handle the Excel file manipulation. This is because ServiceNow's server-side scripting environment does not support these libraries.

nowKB.com