Using tSM Spreadsheet

This guide offers an overview of how to work with the tSM Spreadsheet model, from initializing and retrieving spreadsheets to managing individual sheets, rows, columns, and cells. It covers both the structural components of a spreadsheet and the styles that can be applied to cells.

1. tSM Spreadsheet Initialization

This section covers how to create and initialize a blank spreadsheet or load an existing spreadsheet from an Excel file.

Initialize blank spreadsheet Working

#spreadsheet

Get excel attachment and initialize spreadsheet Working

#with(
#fileId=@attachmentPublicService.getAttachmentsByOwningEntity("Order", #order.id).first.id
).do(
#spreadsheet = @excelPublicService.excelAsSpreadsheet(#fileId)
)

2. Cell Properties in tSM Spreadsheets

In the TSM Spreadsheet model, each cell in a spreadsheet is represented by a set of properties that define its content, appearance, and behavior. These properties are typically stored in a compact format using shorthand notation, which makes it easy to represent cells efficiently in JSON or serialized formats.

Example of spreadsheet structure a it’s key properties

{
      "r": 5,
      "c": 95,
      "t": 1,
      "v": "Tunel 4\nSite - HUB",
      "f": null,
      "s": "oQhcDh"
}

Explanation of key properties

r (Row Index):

Represents the row where the cell is located.
This is a 0-based index, meaning the first row starts at 0.
Example: A cell in the second row would have “r”: 1.

c (Column Index):
- Represents the column where the cell is located.
- This is also a 0-based index, so the first column starts at 0.
- Example: A cell in the third column would have “c”: 2.
t (Type):
- Defines the data type of the cell's content.
- The cell type can be:
  - STRING for text values: "t": 1
  - NUMBER for numeric values: "t": 2
  - BOOLEAN for true/false values: "t": 3,
- This helps ensure the correct handling of the data.
- Example: If a cell contains the text "Hello", then “t”: 1".
v (Value):
- Represents the actual content of the cell, which can be a number, text, boolean, or even an empty value.
- This is the primary data stored in the cell.
- Example: If a cell contains the number 100, then “v”: 100.
f (Formula):
- Holds the formula used in the cell, if any.
- Formulas are expressed in standard spreadsheet syntax (e.g., =SUM(A1:B2)).
- Example: A cell that calculates a sum might have “f”: "=SUM(A1:B2)".
s (Style ID):
- Refers to the style applied to the cell, such as font, color, or alignment.
- This style is defined separately, and the ID links the cell to the correct style settings.
- Example: A cell with bold formatting might have “s”: "oQhcDh".

3. Spreadsheet Methods

This chapter details the available methods for managing the spreadsheet object, including accessing and modifying sheets and styles within the spreadsheet.

1. getSheet(name: String) Working

Retrieves a sheet by its name.

Summary: If the sheet exists, it returns the sheet; otherwise, it throws an exception.

#spreadsheet.getSheet("Sheet1")

2. getStyle(styleId: String?)

Retrieves a style by its ID.

Summary: If the style ID is not null and exists, it returns the style; otherwise, it returns null or throws an exception.

#spreadsheet.getStyle("style1")

3. addSheet(name: String) Working

Adds a new sheet to the spreadsheet.

Summary: Creates a new empty sheet and adds it to the model.

#spreadsheet.addSheet("NewSheet")

4. addStyle(id: String) Working

Adds a new style to the spreadsheet.

Summary: Creates a new empty style with the given ID.

#spreadsheet.addStyle("style1")

5. deleteSheet(name: String) Working

Removes a sheet by its name.

Summary: If the sheet exists, it removes it and returns true; otherwise, returns false.

#spreadsheet.deleteSheet("Sheet1")

6. deleteStyle(id: String)

Removes a style by its ID.

Summary: If the style exists and is not used by any sheet's cells, it removes it and returns true. Otherwise, it throws an exception.

#spreadsheet.deleteStyle("style1")

7. Spreadsheet[index: Int] Not working

Retrieves a sheet by its index.

Summary: Returns the sheet at the specified index.

#spreadsheet[0]

8. Spreadsheet[name: String] Working

Retrieves or creates a sheet by its name.

Summary: If the sheet exists, it returns it; otherwise, it creates and returns a new sheet.

#spreadsheet["Sheet1"]

9. Spreadsheet[index: Int] = newSheet Not working

Updates a sheet at the specified index.

Summary: Sets the sheet at the specified index to the new sheet value

#spreadsheet[0] = newSheet

10. sheets(): List Working

Retrieves all sheets in the model.

Summary: Returns the list of all sheets.

#spreadsheet.sheets()

11. styles(): List Working

Retrieves all styles in the model.

Summary: Returns the list of all styles.

#spreadsheet.styles()

12. toJson() No autocomplete

Serializes the model to JSON.

Summary: Converts the entire spreadsheet model to a formatted JSON string.

#spreadsheet.toJson()

13. validate() Check

Validates the consistency of the model.

Summary: Logs errors if there are any inconsistencies in the model (e.g., sheets reference non-existing styles).

#spreadsheet.validate()

4. Sheet Methods

This section explains how to manage individual sheets in a spreadsheet. Sheets contain rows and columns, which can be accessed or modified using the following methods.

Set variable as sheet

#sheet = #spreadsheet.addSheet("Sheet1")

1. headers() Working

Retrieves all headers (row 0) in the sheet.

Summary: Returns a list of all header values in the first row of the #sheet

#sheet.headers()

2. rows() Working

Retrieves all rows in the sheet.

Summary: Returns a list of all rows, excluding empty rows and cells.

#sheet.rows()

3. columns() Working

Retrieves all columns in the sheet.

Summary: Returns a list of all columns, excluding empty rows and cells.

#sheet.columns()

4. rowsWithoutHeader() Working

Retrieves all rows except the header row.

Summary: Returns a list of all rows excluding the header row (row 0).

#sheet.rowsWithoutHeader()

5. row(row: Int) Working

Retrieves or creates a row by its index.

Summary: Returns the row with the specified index, creating it if it doesn't exist.

#sheet.row(1)

6. cell(row: Int, column: Int) Working

Retrieves or creates a cell by row and column indices.

Summary: Returns a cell at the specified row and column, creating it if it doesn't exist.

#sheet.cell(1, 2)

7. cell(label: String) Working

Retrieves or creates a cell by its excel like reference (e.g., F25).

Summary: Returns a cell at the specified label (like A1, F25), creating it if it doesn't exist.

#sheet.cell("F25")

8. addCell(cell: SpreadsheetCell)

Adds a new cell to the sheet.

Summary: Adds the given cell to the sheet and updates the internal cell map.

#sheet.addCell(newCell)

9. addCell(row: Int, column: Int) Working

Adds a new cell by row and column indices.

Summary: Creates and adds a new cell at the specified row and column.

#sheet.addCell(1, 2)

10. columnIndex(columnName: String)

Retrieves the index of a column based on its header name.

Summary: Returns the 0-based index of a column, given its header name from row 0.

#sheet.columnIndex("Name")

11. columnName(columnIndex: Int)

Retrieves the name of a column based on its index.

Summary: Returns the header name of the column at the given 0-based index.

#sheet.columnName(1)

12. withHeaderRowIndex(headerIndex: Int)

Changes the index of the header row.

Summary: Sets the index of the row to be considered as the header row.

#sheet.withHeaderRowIndex(1)

5. Row Methods

This section outlines how to work with rows in a sheet. Rows contain cells and can be manipulated with the following methods:

Set variable as row

#row = #spreadsheet.addSheet("Sheet1").row(1)

1. columns() : List<SpreadsheetCell>

Retrieves all columns for a row.

Summary: Returns a list of all the cells (columns) in the row.

#row.columns()

2. values(): List<SpreadsheetCell>

Retrieves all column values for a row.

Summary: Returns a list of the actual values of all cells (columns) in the row.

#row.values()

3. getOrAdd(column: Int): SpreadsheetCell

Gets or creates a cell in the specified column.

Summary: Returns the cell in the specified column. If the cell doesn't exist, a new one is created.

#row.getOrAdd(2)

4. setOrAdd(column: Int, value: SpreadsheetCell)

Sets or adds a cell in the specified column.

Summary: Adds or replaces a cell in the specified column with the provided value.

#row.setOrAdd(2, newCell)

5. set(column: Int, value: SpreadsheetCell)

Directly sets a cell at the specified column index.

Summary: Assigns the specified value to the column at the provided index.

row[2] = newCell

6. column(columnIndex: Int): SpreadsheetCell

Retrieves a cell at the specified column index.

Summary: Returns the cell at the given column index, adding it if it doesn't exist.

#row.column(2)

7. column(columnName: String): SpreadsheetCell

Retrieves a cell based on its column name.

Summary: Returns the cell in the column with the given name (based on header row). Throws an exception if the column name doesn't exist.

#row.column("Name")

8. zipWithHeader()

Returns pairs of header names and corresponding cell values for the row.

Summary: Returns a map where each header from the header row is paired with its corresponding cell value in this row.

#row.zipWithHeader()

9. zipWith(other: SpreadsheetRowValues)

Returns a map of value pairs between this row and another row.

Summary: Zips two rows together, creating a map where the keys are the values of this row's cells, and the values are the corresponding values of the other row's cells.

#row.zipWith(otherRow)

10. forEach - Iterating Over Elements of a List

Summary: The .forEach function iterates over each element in a given list and evaluates a set of expressions for each element. It behaves similarly to a loop in traditional programming languages, allowing operations on individual list elements.

Syntax:

#list.forEach(variableReference, expression1, expression2, ...)

list: The list of elements to iterate over.
variableReference: The variable used to reference each element during the iteration. It must be the first argument following the list.
expression: One or more SpEL expressions to be evaluated for each element in the list. The final expression's result is added to the resultant list.

Special Variables

#index: A special variable that holds the current index of the element in the list (0-based index). It can be referenced within any of the provided expressions.

Example:

[1,2,3].forEach(#var, #var + #index)

This example iterates over the list [1, 2, 3], adding the index to each element. The result will be [1, 3, 5] as the values are modified by adding their respective indices.

Notes:

The .forEach function provides a flexible way to manipulate list elements within SpEL, ensuring that each element can be accessed and processed individually.
The use of the #index variable allows for index-specific operations during iteration.

6. Column Methods

Columns can be treated similarly to rows but span vertically through the sheet. This section covers how to manage columns.

1. rows(): List<SpreadsheetCell>

Summary: Returns all the cells (rows) present in the column.

columnValues.rows() // Returns all cells in the column as a list.

2. values(): List<Any?>

Summary: Retrieves the values for all cells in the column, mapping each cell to its content (value property).

columnValues.values() // Returns the values of all cells in the column.

3. getOrAdd(row: Int): SpreadsheetCell

Summary: Retrieves a cell at the specified row index within the column. If the cell does not exist, it creates a new one.

columnValues.getOrAdd(2) // Gets or adds a new cell in the column at row index 2.

4. setOrAdd(row: Int, value: SpreadsheetCell)

Summary: Sets a cell at a specific row within the column. If the row does not exist, it adds the cell at that row.

columnValues.setOrAdd(2, new SpreadsheetCell(2, columnIndex, "New Value"))

5. operator set(row: Int, value: SpreadsheetCell)

Summary: Allows for the setting of a cell at a specific row using the index directly.

columnValues[3] = new SpreadsheetCell(3, columnIndex, "Set Value")

6. toString():

Summary: Returns a string representation of the column, including its index and its rows.

columnValues.toString() // Returns a string summary of the column.

Example Workflow in SpEL

// Example of retrieving and modifying a column within a sheet:
var columnValues = #sheetcolumn(1) // Get the second column in the sheet
var rowsInColumn = columnValues.rows() // Get all the rows in that column

// Add or modify a cell in row 3:
columnValues.setOrAdd(3, new SpreadsheetCell(3, 1, "Updated Value"))

// Retrieve the value in row 3 of this column:
var cellValue = columnValues.getOrAdd(3).value

7. Cell Methods

This section explains how to manage individual cells in a spreadsheet. Cells contain the data within rows and columns.

1. setDateValue(year: Int, month: Int, day: Int)

Sets the value of the cell to a date converted into an Excel-compatible number.

Summary: Converts a given date (year, month, day) into a numerical value as used in Excel and assigns it to the cell.

cell.setDateValue(2024, 9, 10)

2. dateToExcelNumber(year: Int, month: Int, day: Int): Long

Converts a date to Excel's numeric date format.

Summary: Returns the number of days since 1900-01-01, adjusting for Excel's leap year bug.

cell.dateToExcelNumber(2024, 9, 10)

3. cellTypeByValue(cellValue: Any?): CellType

Determines the CellType of the value.

Summary: Based on the value's type (String, Boolean, Number), returns the corresponding CellType.

cell.cellTypeByValue("Test String") // Returns CellType.STRING

4. copy(...)

Creates a copy of the cell with optional modifications to row, column, type, value, formula, or style ID.

Summary: Returns a new SpreadsheetCell instance with the specified modifications.

cell.copy(row = 5, column = 2)

5. error(error: String)

Sets the value of the cell to an error message.

Summary: Changes the cell's value to the provided error string.

cell.error("Invalid Value")

6. getType()

Retrieves the type of the cell.

Summary: Returns the type of the cell (e.g., STRING, NUMBER, BOOLEAN).

cell.type

7. getValue()

Retrieves the value of the cell.

Summary: Returns the current value of the cell.

cell.value

8. getFormula()

Retrieves the formula of the cell, if set.

Summary: Returns the formula set in the cell or null if there is none.

cell.formula

9. getStyleId()

Retrieves the style ID of the cell.

Summary: Returns the style ID of the cell, which is typically a short string.

cell.styleId

Enums:

CellType Enum

Represents the type of the cell. It includes:

STRING
NUMBER
BOOLEAN

Example of getting the cell type:

cell.type // Returns one of the enums, e.g., CellType.STRING

8. Style Methods

Styles define the appearance of cells, including font, alignment, borders, and color. This section explains how to manage styles within a spreadsheet using SpEL (Spring Expression Language).

Creating and Managing Styles in SpELWorking

Styles are created and modified through the spreadsheet's API. Below is an example of how to add a style, set its properties, and apply it to a cell.

Example Workflow:

{
    #spreadsheet, 
    #style = #spreadsheet.addStyle("styleId"),
    #style.definition.bold = true
}

This adds a new style with the identifier "styleId" and makes the font bold. You can modify the style further by accessing its properties.

SpreadsheetCellStyle Check

Represents a style for a spreadsheet cell with an ID and a style definition.

Constructor:

SpreadsheetCellStyle(id: String, definition: SpreadsheetCellStyleDefinition)

Creates a style with a unique identifier and a style definition.

Example Usage:

To create and manipulate a style:

{
    #style = #spreadsheet.addStyle("styleId"),
    #style.definition.bold = true,
    #style.definition.italic = true,
    #style.definition.fontColor = new Color(Triple(255, 0, 0)), // Red text
    #style.definition.backgroundColor = new Color(Triple(255, 255, 0)) // Yellow background
}

SpreadsheetCellStyleDefinition

This class represents various style properties like font, alignment, borders, etc.

Key Properties:

bold: Determines if the font is bold. Working

Type: Boolean
- Default: false

#style.definition.bold = true

italic: Determines if the font is italic. Working

Type: Boolean
Default: false

#style.definition.italic = true

strikeThrough: Determines if the text is strikethrough. Working
- Type: Boolean
- Default: false

#style.definition.strikeThrough = true

underline: Determines if the text is underlined. Working

Type: Boolean
Default: false

#style.definition.underline = true

fontFamily: The font family used for the text (e.g., Arial). Working

Type: String
Default: null

#style.definition.fontFamily = "Arial"

fontSize: The size of the font. Working

Type: Short
Default: null

#style.definition.fontSize = 12

verticalAlignment: The vertical alignment of the text.

Type: VerticalAlignment (Enum)
Default: null

#style.definition.verticalAlignment = VerticalAlignment.MIDDLE

horizontalAlignment: The horizontal alignment of the text.

Type: HorizontalAlignment (Enum)
Default: null

#style.definition.horizontalAlignment = HorizontalAlignment.CENTER

textWrap: The text wrapping behavior.
- Type: TextWrap (Enum)
- Default: null

#style.definition.textWrap = TextWrap.WRAP

textRotation: The text rotation.

Type: TextRotation
Default: null

#style.definition.textRotation = new TextRotation(90)
``` backgroundColor: The background color in RGB format. 

  - Type: Color  
  - Default: null

```tsm-spell
#style.definition.backgroundColor = new Color(Triple(255, 255, 0)) // Yellow background

fontColor: The font color in RGB format.

Type: Color
Default: null

#style.definition.fontColor = new Color(Triple(255, 0, 0)) // Red text

border: The border style of the cell.

Type: Borders
Default: null

#style.definition.border = new Borders(new Border(BorderStyle.THIN, new Color(Triple(0, 0, 0))))

numberFormat: The number format for the cell.

Type: NumberFormat
Default: null

#style.definition.numberFormat("yyyy-mm-dd")

Enumerations

VerticalAlignment: Specifies vertical alignment options for the cell.

Values: UNSPECIFIED, TOP, MIDDLE, BOTTOM

VerticalAlignment.MIDDLE

HorizontalAlignment: Specifies horizontal alignment options for the cell.

Values: UNSPECIFIED, LEFT, CENTER, RIGHT, JUSTIFIED

HorizontalAlignment.CENTER
``` TextWrap: Specifies text wrapping options. 

  - Values: `UNSPECIFIED, OVERFLOW, CLIP, WRAP`

```tsm-spell
TextWrap.WRAP

TextRotation: Represents the rotation of the text within the cell.

new TextRotation(90, true)

Color: Represents a color in RGB format.

new Color(Triple(255, 255, 255)) // White color

Borders and Border: Defines the borders of the cell.

new Borders(new Border(BorderStyle.THIN, new Color(Triple(0, 0, 0))))
``` BorderStyle: Defines various border styles. 

  - Values: `NONE, THIN, HAIR, DOTTED, DASHED, DOUBLE, THICK`

```tsm-spell
BorderStyle.THIN

NumberFormat: Represents the number format for the cell.

new NumberFormat("yyyy-mm-dd")

9. Overview of Spreadsheet Methods

Type	Method Name	Method Signature / SpEL Example
Spreadsheet	Get Sheet by Name	getSheet(name: String): SpreadsheetSheet #spreadsheet.getSheet("Sheet1")
Spreadsheet	Add Sheet	addSheet(name: String): SpreadsheetSheet #spreadsheet.addSheet("NewSheet")
Spreadsheet	Get Style by ID	getStyle(styleId: String?): SpreadsheetCellStyle? #spreadsheet.getStyle("styleId")
Spreadsheet	Add Style	addStyle(id: String): SpreadsheetCellStyle #spreadsheet.addStyle("styleId")
Spreadsheet	Delete Sheet	deleteSheet(name: String): Boolean #spreadsheet.deleteSheet("Sheet1")
Spreadsheet	Delete Style	deleteStyle(id: String): Boolean #spreadsheet.deleteStyle("styleId")
Spreadsheet	Retrieve All Sheets	sheets(): List<SpreadsheetSheet> #spreadsheet.sheets()
Spreadsheet	Retrieve All Styles	styles(): List<SpreadsheetCellStyle> #spreadsheet.styles()
Spreadsheet	Convert to JSON	toJson(): String #spreadsheet.toJson()
Spreadsheet	Validate Spreadsheet	validate() #spreadsheet.validate()
Style	Set Bold	#style.definition.bold = true
Style	Set Italic	#style.definition.italic = true
Style	Set Font Size	#style.definition.fontSize = 12
Style	Set Font Family	#style.definition.fontFamily = "Arial"
Style	Set Font Color (RGB)	#style.definition.fontColor = @colorService.createRGB(255, 0, 0) // Red text
Style	Set Background Color (RGB)	#style.definition.backgroundColor = @colorService.createRGB(255, 255, 0) // Yellow background
Style	Set Underline	#style.definition.underline = true
Style	Set Strike-through	#style.definition.strikeThrough = true
Style	Set Vertical Alignment	#style.definition.verticalAlignment = VerticalAlignment.MIDDLE
Style	Set Horizontal Alignment	#style.definition.horizontalAlignment = HorizontalAlignment.CENTER
Style	Set Number Format	#style.definition.numberFormat("yyyy-mm-dd")
Sheet	Get Headers	headers(): List<String?> sheet.headers()
Sheet	Get All Rows	rows(): List<SpreadsheetRowValues> sheet.rows()
Sheet	Get All Columns	columns(): List<SpreadsheetColumnValues> sheet.columns()
Sheet	Get or Create Row	row(row: Int): SpreadsheetRowValues sheet.row(1)
Sheet	Get or Create Cell (Int)	cell(row: Int, column: Int): SpreadsheetCell sheet.cell(1, 2)
Sheet	Get or Create Cell (Label)	cell(label: String): SpreadsheetCell sheet.cell("F25")
Sheet	Add Cell (By Object)	addCell(cell: SpreadsheetCell): SpreadsheetCell sheet.addCell(newCell)
Sheet	Add Cell (By Row/Column)	addCell(row: Int, column: Int): SpreadsheetCell sheet.addCell(1, 2)
Row	Get or Add Cell in Row	getOrAdd(column: Int): SpreadsheetCell row.getOrAdd(2)
Row	Set or Add Cell in Row	setOrAdd(column: Int, value: SpreadsheetCell) row.setOrAdd(2, newCell)
Row	Get Cell by Column Index	column(columnIndex: Int): SpreadsheetCell row.column(2)
Row	Get Cell by Column Name	column(columnName: String): SpreadsheetCell row.column("Name")
Column	Get All Rows in Column	rows(): List<SpreadsheetCell> columnValues.rows()
Column	Get or Add Cell in Column	getOrAdd(row: Int): SpreadsheetCell columnValues.getOrAdd(2)
Column	Set or Add Cell in Column	setOrAdd(row: Int, value: SpreadsheetCell) columnValues.setOrAdd(2, new SpreadsheetCell(2, columnIndex, "New Value"))

1. tSM Spreadsheet Initialization

Initialize blank spreadsheet Working​

Get excel attachment and initialize spreadsheet Working​

2. Cell Properties in tSM Spreadsheets

Explanation of key properties​

3. Spreadsheet Methods

1. getSheet(name: String) Working​

2. getStyle(styleId: String?)​

3. addSheet(name: String) Working​

4. addStyle(id: String) Working​

5. deleteSheet(name: String) Working​

6. deleteStyle(id: String)​

7. Spreadsheet[index: Int] Not working​

8. Spreadsheet[name: String] Working​

9. Spreadsheet[index: Int] = newSheet Not working​

10. sheets(): List Working​

11. styles(): List Working​

12. toJson() No autocomplete​

13. validate() Check​

4. Sheet Methods

Set variable as sheet​

1. headers() Working​

2. rows() Working​

3. columns() Working​

4. rowsWithoutHeader() Working​

5. row(row: Int) Working​

6. cell(row: Int, column: Int) Working​

7. cell(label: String) Working​

8. addCell(cell: SpreadsheetCell)​

9. addCell(row: Int, column: Int) Working​

10. columnIndex(columnName: String)​

11. columnName(columnIndex: Int)​

12. withHeaderRowIndex(headerIndex: Int)​

5. Row Methods

Set variable as row​

1. columns() : List<SpreadsheetCell>​

2. values(): List<SpreadsheetCell>​

3. getOrAdd(column: Int): SpreadsheetCell​

4. setOrAdd(column: Int, value: SpreadsheetCell)​

5. set(column: Int, value: SpreadsheetCell)​

6. column(columnIndex: Int): SpreadsheetCell​

7. column(columnName: String): SpreadsheetCell​

8. zipWithHeader()​

9. zipWith(other: SpreadsheetRowValues)​

10. forEach - Iterating Over Elements of a List​

Special Variables​

Example:​

Notes:​