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 ?

[sumanta pal]

Yes, you can add formatting or formulas to an Excel file using ServiceNow scripting. However, it's important to note that ServiceNow's GlideSysAttachment API does not directly support Excel formatting or formulas. You would need to use a third-party library or API that supports Excel file manipulation, such as Apache POI for Java.

Here are the general steps you would need to follow:

1. Generate your data in ServiceNow that you want to export to Excel.
2. Use a third-party library or API that supports Excel file manipulation to create an Excel file with your desired formatting and formulas. This would likely involve writing a script include that uses the third-party library or API.
3. Use the GlideSysAttachment API to attach the Excel file to the desired record.

Here is a sample code snippet that demonstrates how you might use the Apache POI library to create an Excel file with formatting:

java
// Import the necessary Apache POI classes
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

// Create a new workbook and sheet
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet("My Sheet");

// Create a row and cell
Row row = sheet.createRow(0);
Cell cell = row.createCell(0);

// Set the cell value and apply a formula
cell.setCellValue(1);
cell.setCellFormula("A1*2");

// Create a cell style and apply it to the cell
CellStyle style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cell.setCellStyle(style);

// Write the workbook to an output stream
FileOutputStream fileOut = new FileOutputStream("workbook.xlsx");
workbook.write(fileOut);
fileOut.close();

// Close the workbook
workbook.close();

Please note that this is a Java code snippet and would need to be adapted for use in ServiceNow. Also, you would need to add the Apache POI library to your ServiceNow instance, which may require administrative privileges.

nowKB.com

[AylinTurhan]

It sounds like you're looking to add some formatting or formulas to the Excel file you're generating using GlideSysAttachment().write(). While GlideScript itself doesn’t provide direct methods for adding Excel formulas or formatting, you can actually use an approach with Excel's built-in XML format (xlsx) to insert formulas or apply styles.