Excel functions

Reading and writing with Excel

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.

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.

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.

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

By default, Excel offers 56 colors that are combined in a palette. These can be addressed via the colorindex. The default palette differs from one Excel installation to another.

The following script creates an Excel file containing the colors of the custom palette:

let workbook = workbookFactory.createNewWorkbook();
for (i = 1; i < 56; i++) {
    let cell = workbook.getRelCellAddr("A1", 0, i);
    workbook.setBackgroundColor(cell, i);
    workbook.setValue(cell, "index: " + i);
}
workbook.writeToFile("c:/example/colorindex.xlsx");

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

By default, Excel offers 56 colors that are combined in a palette. These can be addressed via the colorindex. The default palette differs from one Excel installation to another.

The following script creates an Excel file containing the colors of the custom palette:

let workbook = workbookFactory.createNewWorkbook();
for (i = 1; i < 56; i++) {
    let cell = workbook.getRelCellAddr("A1", 0, i);
    workbook.setBackgroundColor(cell, i);
    workbook.setValue(cell, "index: " + i);
}
workbook.writeToFile("c:/example/colorindex.xlsx");

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.

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: 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: 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.

In this section:

KnowledgeBase

Excel functions

Reading and writing with Excel

Notice

Reading data

getValue()

getValuesRow()

getValuesColumn()

getValuesMatrix()

getValuesMatrixT()

Writing data

setValue()

setValuesRow()

setValuesColumn()

setValuesMatrix()

setValuesMatrixT()

Auxiliary functions

getRelCellAddr()

isFileWriteable()

evaluateAllFormulaCells()

Worksheets

isSheet()

setCurrentSheet()

getCurrentSheet()

createSheet()

deleteSheet()

renameCurrentSheet()

cloneSheet()

Layout

setBackgroundColor()

setFontColor()

mergeCells()

setCellAlign()

setVCellAlign()

Search results