Write and Read XLSX Sheets with Teddy and CADiNP

Connecting Excel with SOFiSTiK is used quite often since SOFiSTiK Results offers Excel export. But being a one-way link, it isn’t a complete solution.

In this post, I want to highlight one of the features of SOFiSTiK 2023. A feature you might not find promoted across social media. But I believe many will benefit from it.

There isn’t a simple way to get data from Excel into SOFiSTiK. You can use the workflow explained in this online tutorial, which requires some experience with VBA. Or you could copy/paste, which works, but requires additional effort every time your data in Excel changes.

With SOFiSTiK 2023, a new CADiNP command has been added to open a new workflow. You might think that you need to know CADiNP instead of VBA to get data from Excel to SOFiSTiK. And you are right. But trust me, it isn’t that difficult to set it up, and worth the effort. Besides, you benefit from staying within SOFiSTiK, which is perfect.

Let’s find out more about the workflow.

Step 1: A new or existing project

Open an existing project or create a new one; it doesn’t matter if it’s a project built in SOFiSTiK structural Desktop or Teddy.

In SOFiSTiK Structural Desktop, add a new text task. You can find it at the very top of the task library. It’s the one with the yellow teddy bear icon called “Text Editor”—double-click on the newly inserted text task.

SOFiSTiK: Insert New Task

If you use Teddy, click at the start, end, or between two programme modules.

Step 2: Create an Excel file/sheet

If you use SOFiSTiK Structural Desktop or Teddy, enter the following input lines to create a TEMPLATE programme block.

+prog template
head
end

Between the input lines “head” and “end”, enter the following input lines:

sto#xls.name “D:\Test.xlsx”
sto#xls.sheet “Sheet1”

sto#xls.nameThe input line sto#xls.name “…” defines the Excel file. If the file doesn’t exist, a new one gets created. Furthermore, “xls.name” is a SOFiSTiK variable, which means you can have only one single excel file active in the project at the time.
If you want to use a different excel file, you will have to reenter this input line referring to that particular file.
sto#xls.sheetAs every excel file comes with sheets, this is also a required input covered by the input line sto#xls.sheet “…”. Same as for the input xls.name; this is a SOFiSTiK variable valid for the entire project.

Step 3: Write into the Excel sheet

The Excel file and sheet have been set in our project. Now it’s time to write information into it. You can also use SOFiSTiK Results to export results. And I assume it will be the preferred option. Or you might have an existing Excel file already, then you can jump ahead to Step 4, where you can read about how to read the excel sheet.

If you use CADiNP on an intermediate to expert level, writing data precisely to a certain cell in an Excel sheet can be very tempting. To learn that, keep reading.

Add the following input line:

let#xlsWrite xls.write(1,2,99)

let#xlsWriteThis is a dummy variable required to get the CADiNP command to work.
xls.write(1,2,99)This is where the magic happens. The xls.write command writes the value 99 into the cell “B1” in the excel sheet “Sheet1”
1 …Row, 2…Column, 99…Cell value

Your script should look like the one below.

+prog template
head
sto#xls.name “D:\Test.xlsx”
sto#xls.sheet “Sheet1”
let#xlsWrite xls.write(1,2,99)
end

As you actively write something in the Excel file, closing it before running the input is essential. Otherwise, you might run into an error while running the script.

It is also possible to use a variable instead of a value for the cell value. You can see it in the input below. The variable # has been used.

Final Write to Excel Script

Give the above script a try before moving on to Step 4. Play around with the values or add another xls.write command.

Do the following if you don’t know how to run the script in SOFiSTiK Structural Desktop.

  • Save the project.
  • Right-click on the Text input Task (the one with the yellow teddy bear) and calculate it.

If you run into an error during the calculation, ensure the excel file is closed, and you have the admin rights to create a new file in the directory.

Step 4: Read from an Excel file/sheet

We have a new Excel file with the value “99” in cell “B1”. Let’s see how we can read it and save it in a variable in CADiNP.

Create a new TEMPLATE programme module block and enter the following input lines. You can copy/paste it to save time.

+prog template
head
let#xlsRead xls.read(1,2)
txe xlsRead
end

Let me explain the input for reading data from an Excel sheet.

let#xlsRead xls.read(1,2)

let#xlsReadWhen reading a value, you want to save it in your project. The value gets saved in the local variable “xlsRead”, and you can print or use it otherwise afterwards. You can pick any variable name; it should make sense in the context of your project, though.
xls.read(1,2)To read the data with the xls.read command, only the Row and Column must be entered. In this case, the cell value “B1” gets read.
1…Row, 2…Column

You can keep the Excel file open while reading it. But keep in mind to save the Excel sheet to read updated values.

Only one last input line to explain: txe #xlsRead. The print text at the end of the report command will print the value of the variable xlsRead in the SOFiSTiK Report.

Final Read from Excel Script

Now, run the whole script and check the report. The chapter “Conclusion” shows the value “99”.

Read From Excel Printout

The new write and read commands can be used in any programme module; you are not limited to the TEMPLATE programme module. The advantage of the TEMPLATE module is that it doesn’t perform any analysis, design, or other heavy stuff. So you don’t mess around with the database (CDB)

Wrap up

I know it; many of you who work with CADiNP and Excel will love this new feature in SOFiSTiK 2023. I can think about the definition of 2-dimensional arrays, which isn’t possible in CADiNP, feeding information to predefined Excel design sheets, or connecting Excel as an input template for project data, to name a few use cases. And I’m sure there are many more. Another advantage; there is no need to get familiar with VBA, as everything can be done within CADiNP.

Learn more about the xls.write and xls.read commands.

Download

Download the WriteReadToExcel Example Script

More about the topic