Skip to main content

KnowledgeBase

Excel functions

Reading and writing with Excel

excel_keyvisual.png

The scripting function in the FVA-Workbench makes it possible to read data from or write values to an Excel file. For example, input values for a gear can be read directly from an Excel file in order to set the corresponding attributes in the gearbox model in the FVA-Workbench. The results of the calculation can then be written to the Excel file.

Formatting of the Excel file or cells can be done directly in the script using functions such as setBackgroundColor("B4", Red). Alternatively, a formatted Excel file can be imported as a template. The formatting of this file is used, and only the values in the specified cells will be added or replaced.

The functions for reading, writing, and editing Excel documents are all performed on a workbook object. The workbook is a data object that references a concrete Excel document.

The layout and contents of the specified Excel document are transferred.

let excelfile = "c:/example/excelfile.xlsx";
let workbook = workbookFactory.createWorkbook(excelfile);

The functions are then executed on the respective workbook object using dot notation.

workbook.setValue("A1", 5.241);

The following function is used to overwrite the Excel document with any changes made:

workbook.writeToFile(excelfile);

If the file specified in writeToFile() does not exist, it will be created.

Notice

A workbook object can also be created without specifying a concrete Excel document.

let workbook = workbookFactory.createNewWorkbook();

This procedure is recommended if no Excel template is used and the formatting is done exclusively using script functions.

Reading data

getValue()
Table 62. Reads the value of a cell.

getValue(CELL);

Parameter

Type

Description

CELL

string

Address of the Excel cell (e.g., C12)



Example Read a value from an Excel cell

1let excelfile = "c:/example/excelfile.xlsx";
2let workbook = workbookFactory.createWorkbook(excelfile);
3let value = workbook.getValue("B3");
4println(value);

1

Assigns the the path of the Excel file to the excelfile variable.

2

Creates an Excel data object and assigns it to the workbook variable.

3

Reads the contents of cell B3 and writes it to the value variable.

4

Outputs the value of the value variable to the Scripting Monitor.

getValuesRow()
Table 63. Reads the values of an Excel row, beginning at the specified cell address.

getValuesRow(CELL, length);

Parameter

Type

Description

CELL

string

Address of the Excel cell (e.g., C12)

length

int

Number of cells to be read in the horizontal direction.

Possible errors

The Excel file does not exist.



Example Read a row from an Excel file

1let excelfile = "c:/example/excelfile.xlsx";
2let workbook = workbookFactory.createWorkbook(excelfile);
3let array = workbook.getValuesRow("B3", 5);
4println(array);

1

Assigns the path of the Excel file to the excelfile variable.

2

Creates an Excel data object and assigns it to the workbook variable.

3

Beginning at cell B3, reads 5 cells to the right and writes them as an array to the array variable.

4

Outputs the array variable to the Scripting Monitor.

getValuesColumn()
Table 64. Reads the values of an Excel column, beginning at the specified cell address.

getValuesColumn(CELL, length);

Parameter

Type

Description

CELL

string

Address of the Excel cell (e.g., C12)

length

int

Number of cells to be read in the vertical direction.

Possible errors

The Excel file does not exist.



Example Read an array from a column of an Excel file

1let excelfile = "c:/example/excelfile.xlsx";
2let workbook = workbookFactory.createWorkbook(excelfile);
3let array = workbook.getValuesColumn("B3", 5);
4println(array);

1

Assigns the path of the Excel file to the excelfile variable.

2

Creates an Excel data object and assigns it to the workbook variable.

3

Beginning at cell B3, reads 5 cells down and writes them as an array to the array variable.

4

Outputs the array variable to the Scripting Monitor.

getValuesMatrix()
Table 65. Reads the values from a matrix, beginning at the specified cell address.

getValuesMatrix(CELL, rowlength, collength);

Parameter

Type

Description

CELL

string

Address of the Excel cell (e.g., C12)

rowlength

int

Number of rows to be read.

collength

int

Number of columns to be read.

Possible errors

The Excel file does not exist.



Example Read a matrix from an Excel file

1let excelfile = "c:/example/excelfile.xlsx";
2let workbook = workbookFactory.createWorkbook(excelfile);
3let matrix = workbook.getValuesMatrix("B3", 4, 5);
4println(matrix);

1

Assigns the path of the Excel file to the excelfile variable.

2

Creates an Excel data object and assigns it to the workbook variable.

3

Beginning at cell B3, reads a matrix 4 cells wide and 5 columns high and writes it to the matrix variable.

4

Outputs the matrix variable to the Scripting Monitor.

getValuesMatrixT()
Table 66. Reads the values of a matrix and transposes them.

getValuesMatrixT(CELL, rowlength, collength);

Parameter

Type

Description

CELL

string

Address of the Excel cell (e.g., C12)

rowlength

int

Number of rows to be read.

collength

int

Number of columns to be read.

Possible errors

The Excel file does not exist.



Example Read and transpose a matrix from an Excel file

1let excelfile = "c:/example/excelfile.xlsx";
2let workbook = workbookFactory.createWorkbook(excelfile);
3let matrix = workbook.getValuesMatrixT("B3", 4, 5);
4println(matrix);

1

Assigns the path of the Excel file to the excelfile variable.

2

Creates an Excel data object and assigns it to the workbook variable.

3

Beginning at cell B3, reads a matrix 5 cells wide and 4 columns high and writes it to the matrix variable.

4

Outputs the matrix variable to the Scripting Monitor.

Writing data

setValue()
Table 67. Writes a value to a cell.

setValue(CELL, value);

Parameter

Type

Description

CELL

string

Address of the Excel cell (e.g., C12)

value

int

double

string

Value to be written in the cell.

Possible errors

The user does not have write permissions for the specified Excel file.

The Excel file is open in another application.



Example Write a value to an Excel cell

1let excelfile = "c:/example/excelfile.xlsx";
2let workbook = workbookFactory.createWorkbook(excelfile);
3workbook.setValue("B3", 12.3);
4workbook.writeToFile(excelfile);

1

Assigns the path of the Excel file to the excelfile variable.

2

Creates an Excel data object and assigns it to the workbook variable.

3

Writes a value of 12.3 in cell B3.

4

Writes the changes to the Excel file.

setValuesRow()
Table 68. Writes the values of an array as a row (horizontal), starting with the specified cell address.

setValuesRow(CELL, array);

Parameter

Type

Description

CELL

string

Address of the Excel cell (e.g., C12)

array

int array

double array

string array

Array whose values are to be exported.

Possible errors

The user does not have write permissions for the specified Excel file.

The Excel file is open in another application.



Example Write an array as a row in an Excel file

1let excelfile = "c:/example/excelfile.xlsx";
2let workbook = workbookFactory.createWorkbook(excelfile);
3let array = [1.2, 1.4, 1,7, 1.9];
4workbook.setValuesRow("B3", array);
5workbook.writeToFile(excelfile);

1

Assigns the path of the Excel file to the excelfile variable.

2

Creates an Excel data object and assigns it to the workbook variable.

3

Writes an array consisting of multiple double values to the array variable.

4

Writes the array, starting at address B3.

4

Writes the changes to an Excel file.

setValuesRow.png
setValuesColumn()
Table 69. Writes the values of an array as a column (vertical), starting at the specified cell address.

setValuesColumn(CELL, array);

Parameter

Type

Description

CELL

string

Address of the Excel cell (e.g., C12)

array

int array

double array

string array

Array whose values are to be exported.

Possible errors

The user does not have write permissions for the specified Excel file.

The Excel file is open in another application.



Example Write an array of values to a column in Excel.

1let excelfile = "c:/example/excelfile.xlsx";
2let workbook = workbookFactory.createWorkbook(excelfile);
3let array = [1.2, 1.4, 1,7, 1.9];
4workbook.setValuesColumn("B3", array);
5workbook.writeToFile(excelfile);

1

Assigns the path of the Excel file to the excelfile variable.

2

Creates an Excel data object and assigns it to the workbook variable.

3

Writes an array consisting of multiple double values to the array variable.

4

Writes the array, starting at address B3.

4

Writes the changes to an Excel file.

setValuesColumn.png
setValuesMatrix()
Table 70. Writes the values of a matrix, starting with the specified cell address.

setValuesMatrix(CELL, matrix);

Parameter

Type

Description

CELL

string

Address of the Excel cell (e.g., C12)

matrix

int matrix

double matrix

string matrix

Matrix whose values are to be exported.

Possible errors

The user does not have write permissions for the specified Excel file.

The Excel file is open in another application.



Example Write a matrix of data to an Excel file

1let stageID = getCompByType('cylindrical_mesh')[0];
2let excelfile = "c:/example/excelfile.xlsx";
3let workbook = workbookFactory.createWorkbook(excelfile);
4let matrix = getAttr("local_methods_grid_pressure_z", stageID, RDAT);
5workbook.setValuesMatrix("B3", matrix);
6workbook.writeToFile(excelfile);

1

Assigns the component ID of the first cylindrical gear stage of the model to the stageID variable.

2

Assigns the path of the Excel file to the excelfile variable.

3

Creates an Excel data object and assigns it to the workbook variable.

4

Reads the flank pressure matrix via getAttr() and writes it to the matrix variable.

5

Writes the array, starting at address B3.

6

Writes the changes to an Excel file.

setValuesMatrixT()
Table 71. Writes the transposed values of a matrix (rows & columns exchanged), starting with the specified cell address.

setValuesMatrix(CELL, matrix);

Parameter

Type

Description

CELL

string

Address of the Excel cell (e.g., C12)

matrix

int matrix

double matrix

string matrix

Matrix whose transposed values are to be exported.

Possible errors

The user does not have write permissions for the specified Excel file.

The Excel file is open in another application.



Example Write a transposed matrix of data to an Excel file

1let stageID = getCompByType('cylindrical_mesh')[0];
2let excelfile = "c:/example/excelfile.xlsx";
3let workbook = workbookFactory.createWorkbook(excelfile);
4let matrix = getAttr("local_methods_grid_pressure_z", stageID, RDAT);
5workbook.setValuesMatrixT("B3", matrix);
6workbook.writeToFile(excelfile);

1

Assigns the component ID of the first cylindrical gear stage of the model to the stageID variable.

2

Assigns the path of the Excel file to the excelfile variable.

3

Creates an Excel data object and assigns it to the workbook variable.

4

Reads the flank pressure matrix via getAttr() and writes it to the matrix variable.

5

Writes the array, starting at address B3.

6

Writes the changes to an Excel file.

Auxiliary functions

getRelCellAddr()
Table 72. Returns the cell address relative to a reference cell. The horizontal and vertical distance to the reference cell are specified.

getRelCellAddr(referenceCellAddress, distanceHorizontal, distanceVertical);

Parameter

Type

Description

referenceCellAddress

string

Address of the reference Excel cell (e.g., C12)

distanceHorizontal

int

Horizontal distance to the reference cell

distanceVertical

int

Vertical distance to the reference cell



Example Query the relative cell address

1let excelfile = "c:/example/excelfile.xlsx";
2let workbook = workbookFactory.createWorkbook(excelfile);
3let relativeAdress = workbook.getRelCellAddr("B3", 5, 3);
4println(relativeAdress);

1

Assigns the path of the Excel file to the excelfile variable.

2

Creates an Excel data object and assigns it to the workbook variable.

3

Queries the cell address, which is located 5 cells to the right and 3 cells below B3.

4

Outputs the relative cell address "G6" on the Scripting Monitor.

relativeCellAddress.png
isFileWriteable()
Table 73. Checks whether an Excel file can be written. The return value is true or false.

isFileWriteable(pathToFile);

Parameter

Type

Description

pathToFile

string

Path to Excel file to be checked.



Example

Before changes are written to the Excel file, the script checks whether the file can be written. If not, a message dialog is displayed asking the user to close the file. If the user clicks OK and the file still cannot be written, the dialog is displayed again. If the file still cannot be written at this point, the script is terminated and a message is shown on the Scripting Monitor.

1let excelfile = "c:/example/excelfile.xlsx";
2let workbook = workbookFactory.createWorkbook(excelfile);
3let messageCount = 0;
4while(workbook.isFileWriteable(excelfile) == false){
5    if(messageCount == 2){
6        stopScript("Script could not be executed")
        }
7	alert("Output file is still open. Please close.");
8	messageCount++;
    }
9workbook.writeToFile(excelfile);

1

Assigns the path of the Excel file to the excelfile variable.

2

Creates an Excel data object and assigns it to the workbook variable.

3

The messageCount variable stores how often the dialog has already been displayed.

4

While loop that runs until the function isFileWriteable() returns true.

5

When the messageCount variable is equal to 2 ...

6

execution of the script is terminated and the message "Script could not be executed" is displayed.

7

Displays a dialog box with the message "Output file is still open. Please close."

8

Increases the messageCount variable by 1 for each loop pass.

9

Writes the changes to the Excel file.

evaluateAllFormulaCells()
Table 74. Evaluates all cells of an Excel document that contain formulas and writes the results into the cells. The stored formulas are retained.

evaluateAllFormulaCells();

Parameter

Type

Description

-

-

-



Example

let excelfile = "c:/example/excelfile.xlsx";
let workbook = workbookFactory.createWorkbook(excelfile);
workbook.evaluateAllFormulaCells();
workbook.writeToFile(excelfile);

Worksheets

isSheet()
Table 75. Checks whether a worksheet exists with the specified name.

isSheet(sheetName);

Parameter

Type

Description

sheetName

string

Name of the worksheet to be checked.

Return value

boolean

true - the worksheet exists

false - no worksheet with this name is available



Example

Check whether a worksheet exists with the name "results." If so, set it as the active worksheet. If not, create a new worksheet with the name "results" and set it as the active worksheet.

1let excelfile = "c:/example/excelfile.xlsx";
2let workbook = workbookFactory.createWorkbook(excelfile);
3let sheetForResults = "results";
4if (workbook.isSheet(sheetForResults)) {    
5    workbook.setCurrentSheet(sheetForResults);
6}else {    
7    workbook.createSheet(sheetForResults, true);
    }
8workbook.writeToFile(excelfile);

1

Assigns the path of the Excel file to the excelfile variable.

2

Creates an Excel data object and assigns it to the workbook variable.

3

Assigns the name of the worksheet to be written ("results") to the sheetForResults variable.

4

If a worksheet with that name already exists...

5

it is set as the active worksheet.

6

Otherwise...

7

A new worksheet with the name "results" is created and set as the active worksheet.

8

Writes the changes to the Excel file.

setCurrentSheet()
Table 76. Specifies the Excel worksheet to be edited (active worksheet).

setCurrentSheet(sheetName);

Parameter

Type

Description

sheetName

string

Name of the worksheet to be edited.

Possible errors

The specified worksheet does not exist.



Example Specify the worksheet to be edited

1let excelfile = "c:/example/excelfile.xlsx";
2let workbook = workbookFactory.createWorkbook(excelfile);
3workbook.setCurrentSheet("results");

1

Assigns the path of the Excel file to the excelfile variable.

2

Creates an Excel data object and assigns it to the workbook variable.

3

Sets the worksheet with the name "results" as the active worksheet.

getCurrentSheet()
Table 77. Returns the name of the current Excel worksheet.

getCurrentSheet();

Parameter

Type

Description

-

-

-



Example Display the name of the current Excel workbook on the Scripting Monitor

1let excelfile = "c:/example/excelfile.xlsx";
2let workbook = workbookFactory.createWorkbook(excelfile);
3let workSheet = workbook.getCurrentSheet();
4println(workSheet);

1

Assigns the path of the Excel file to the excelfile variable.

2

Creates an Excel data object and assigns it to the workbook variable.

3

Writes the name of the current worksheet to the workSheet variable.

4

Outputs the name of the Excel worksheet to the Scripting Monitor.

createSheet()
Table 78. Creates a new Excel workbook. Optionally, the worksheet can also be set as the active worksheet.

createSheet(sheetName, setAsCurrentSheet);

Parameter

Type

Description

sheetName

string

Name of the worksheet to be created.

setAsCurrentSheet

boolean

true - the worksheet will be set as the active worksheet after it is created.

false - the worksheet will only be created; it will not be set as the active worksheet.

Possible errors

A worksheet with the specified name already exists.



Example Create a new worksheet

1let excelfile = "c:/example/excelfile.xlsx";
2let workbook = workbookFactory.createWorkbook(excelfile);
3workbook.createSheet("Input data", true);
4workbook.writeToFile(excelfile);

1

Assigns the path of the Excel file to the excelfile variable.

2

Creates an Excel data object and assigns it to the workbook variable.

3

Sets the worksheet with the name "results" as the active worksheet.

4

Writes the changes to the Excel file.

deleteSheet()
Table 79. Deletes the specified worksheet.

deleteSheet(sheetName);

Parameter

Type

Description

sheetName

string

Name of the worksheet to be deleted.

Possible errors

The specified worksheet does not exist.



Example Delete a worksheet

1let excelfile = "c:/example/excelfile.xlsx";
2let workbook = workbookFactory.createWorkbook(excelfile);
3workbook.deleteSheet("Load spectrum");
4workbook.writeToFile(excelfile);

1

Assigns the path of the Excel file to the excelfile variable.

2

Creates an Excel data object and assigns it to the workbook variable.

3

Deletes the worksheet with the name "load spectrum."

4

Writes the changes to the Excel file.

renameCurrentSheet()
Table 80. Renames the current worksheet.

renameCurrentSheet(newSheetName);

Parameter

Type

Description

newSheetName

string

New name for the worksheet.



Example Rename a worksheet

1let excelfile = "c:/example/excelfile.xlsx";
2let workbook = workbookFactory.createWorkbook(excelfile);
3workbook.renameCurrentSheet("Worksheet 123");
4workbook.writeToFile(excelfile);

1

Assigns the path of the Excel file to the excelfile variable.

2

Creates an Excel data object and assigns it to the workbook variable.

3

Changes the name of the current worksheet to "Worksheet 123."

4

Writes the changes to the Excel file.

cloneSheet()
Table 81. Copies the specified worksheet

cloneSheet(sheetName, clonedSheetName, active);

Parameter

Type

Description

sheetName

string

Name of the worksheet to be copied

clonedSheetName

string

Name of the copy

active

boolean

true - the copied worksheet is set as the active worksheet

false - the active worksheet is not changed



Example Display the name of the current Excel workbook on the Scripting Monitor

1let excelfile = "c:/example/excelfile.xlsx";
2let workbook = workbookFactory.createWorkbook(excelfile);
3cloneSheet("Worksheet1", "Worksheet1Copy", true);
4workbook.writeToFile(excelfile);

1

Assigns the path of the Excel file to the excelfile variable.

2

Creates an Excel data object and assigns it to the workbook variable.

3

Copies the worksheet with the name "Worksheet1" and assigns the name "Worksheet1Copy" to the copy. The copy is set as the active worksheet.

4

Writes the changes to the Excel file.

Layout

setBackgroundColor()
Table 82. Changes the background color of a cell.

setBackgroundColor(CELL, colorindex);

Parameter

Type

Description

CELL

string

Address of the Excel cell (e.g., C12)

colorindex

int



Example Change the background color of an Excel cell

1let excelfile = "c:/example/excelfile.xlsx";
2let workbook = workbookFactory.createWorkbook(excelfile);
3workbook.setBackgroundColor("B3", 3);
4workbook.writeToFile(excelfile);

1

Assigns the path of the Excel file to the excelfile variable.

2

Creates an Excel data object and assigns it to the workbook variable.

3

Changes the background color of cell B3 to red.

4

Writes the changes to the Excel file.

setFontColor()
Table 83. Changes the color of the text in a cell.

setFontColor(CELL, colorindex);

Parameter

Type

Description

CELL

string

Address of the Excel cell (e.g., C12)

colorindex

int



Example Change the font color of an Excel cell

1let excelfile = "c:/example/excelfile.xlsx";
2let workbook = workbookFactory.createWorkbook(excelfile);
3workbook.setFontColor("B3", 4);
4workbook.writeToFile(excelfile);

1

Assigns the path of the Excel file to the excelfile variable.

2

Creates an Excel data object and assigns it to the workbook variable.

3

Changes the font color of cell B3 to yellow.

4

Writes the changes to the Excel file.

mergeCells()
Table 84. Combines multiple Excel cells into a single cell.

mergeCells(fromCELL, toCELL);

Parameter

Type

Description

fromCELL

string

Address of the upper left cell

toCELL

string

Address of the lower right cell



Example Combine cells B3 through D4

1let excelfile = "c:/example/excelfile.xlsx";
2let workbook = workbookFactory.createWorkbook(excelfile);
3workbook.mergeCells("B3", "D4");
4workbook.writeToFile(excelfile);

1

Assigns the path of the Excel file to the excelfile variable.

2

Creates an Excel data object and assigns it to the workbook variable.

3

Combines all cells spanning the range from A2 to D3.

4

Writes the changes to the Excel file.

merge_cells.png

When merging cells, only the value of the upper left cell is retained. All other values are discarded. By default, the contents of the merged cell are centered and bottom-aligned.

To change the value or layout of a merged cell, the address of the upper left cell must be specified in the corresponding functions.

setCellAlign()
Table 85. Changes the horizontal alignment of an Excel cell.

setCellAlign(CELL, alignment);

Parameter

Type

Description

CELL

string

Address of the Excel cell (e.g., C12)

alignment

string

The following formatting options are available:

horizontal_text_align_examples.png

The different alignment values are listed on the left. The "general" value can be used to reset the alignment to the default state.



Example Center the contents of a cell horizontally

1let excelfile = "c:/example/excelfile.xlsx";
2let workbook = workbookFactory.createWorkbook(excelfile);
3workbook.setCellAlign("B4", "center");
4workbook.writeToFile(excelfile);

1

Assigns the path of the Excel file to the excelfile variable.

2

Creates an Excel data object and assigns it to the workbook variable.

3

Centers the contents of cell B4 horizontally.

4

Writes the changes to the Excel file.

setVCellAlign()
Table 86. Changes the vertical alignment of an Excel cell.

setVCellAlign(CELL, alignment);

Parameter

Type

Description

CELL

string

Address of the Excel cell (e.g., C12)

alignment

string

The following formatting options are available:

vertical_text_align_examples.png

The different alignment values are listed on the left



Example Align the contents of a cell to the bottom

1let excelfile = "c:/example/excelfile.xlsx";
2let workbook = workbookFactory.createWorkbook(excelfile);
3workbook.setVCellAlign("B4", "bottom");
4workbook.writeToFile(excelfile);

1

Assigns the path of the Excel file to the excelfile variable.

2

Creates an Excel data object and assigns it to the workbook variable.

3

Aligns the contents of cell B4 to the bottom.

4

Writes the changes to the Excel file.