How to Create a Material Database from Spreadsheet Data

Lars Gregersen | March 31, 2014
List us on Facebook Folow us on Twitter Join us on Google+ Connect with us on LinkedIn

Modeling in COMSOL Multiphysics involves a lot of tasks, such as choosing the right physics, defining the geometry, and setting up boundary conditions and domain settings. Additionally, material properties have to be defined for the materials included in the model. Such material data may come from the Material Library, but it often has to be obtained experimentally or from literature and imported into COMSOL Multiphysics.

Using Spreadsheets for Handling and Storing Data

We often use spreadsheets to store numerical data originating from various sources. Since spreadsheet tools, such as Excel®, are used by most everyone in an organization, the data can be collected, stored, and transferred between people and departments without special regard for programs that must be used to process and utilize it. The data may originally have been obtained from online sources, in-house or external databases imported from other file formats, or simply entered directly into the spreadsheet when the measurement was obtained.

Spreadsheets are perfect for handling data, since you can easily sort and filter it. You are also able to validate the data by visualizing it graphically before using it in your models. While spreadsheets are geared more towards handling a variety of data sources, COMSOL Multiphysics® allows you to enter material data either directly in your models or in user-created material libraries that can be used by many models.

LiveLink™ for Excel®

When we introduced LiveLink™ for Excel®, we opened up the possibilities for collaboration across teams and departments. Now, your own or your colleague’s model data can be edited immediately in Excel®, and these edits can then be transferred back to COMSOL Multiphysics simply by pressing a button.

LiveLink™ for Excel® also includes a few features for exporting spreadsheet data into COMSOL software material data files, which can be used directly for subsequent modeling. The material data can even depend on field values, such as pressure and temperature.

Exporting Data

As mentioned previously, data can come from many sources. Here I’ll show an example using some data from Wikipedia. It’s from a table that contains values for the density of water for different temperatures, listed in descending order.
Material properties for the density of liquid water
We copy and paste the data directly into Excel® and it’s ready for exporting into a COMSOL Material Database file.
Exporting material properties into a COMSOL Material Database
Used with permission from Microsoft.

Now, we have to specify both where the field variable (temperature) and the property (density) are defined in the spreadsheet as well as what their units are. In COMSOL Multiphysics, you can define your data in almost any combination of units, but it’s important to specify which units you are using when you import the data. You do this by first clicking on the Settings button.
Settings button
Settings button.

A large dialog box will open that is used to define the data ranges for the inputs. The field variable and material properties are defined on two different tabs in the dialog box, as you can see below.
Field Dependent Data tab in the Settings window
Field Dependent Data tab in the Settings window.
Material Properties tab in the Settings window
Material Properties tab in the Settings window.

Finally, we are ready to export the data and click “OK”. We export the data by clicking on the “New” or “Append” buttons where we can also provide a file name for the Material Library. The next time we start up COMSOL Multiphysics and open the Material Browser, we’ll see the new library in the list of Material Libraries, ready for use.

Fitting Expressions to Data

When you import data using the method shown above, the data is interpolated automatically by COMSOL Multiphysics when solving the model. This is vital when the data varies a lot and an analytical expression cannot be fitted to the data.

Next up, I’ll show you a simple example using the thermal conductivity of copper’s dependency on temperature, using data from this source. Since there is only a small amount of data, the easiest way to import the values for copper is simply to write them in the spreadsheet. I then plot the data as a scatter plot, which allows me to fit a second order polynomial to the data. Since there are only three data points, I achieve a perfect fit, which looks reasonable in the given data range. Extrapolating outside the range can, however, be very dangerous.
Excel plot of the thermal conductivity of water
Used with permission from Microsoft.

The following fitted polynomial can be used directly from within COMSOL Multiphysics:

$$ k_c = -5 \cdot 10^{-5} T_c^2 -0.0025 T_c +401.09 $$

where k_c is the thermal conductivity of copper and T_c is the temperature in Celsius. The easiest way to use this expression in COMSOL Multiphysics is to define some expressions for these two variables.
Defining expressions in COMSOL Multiphysics
COMSOL Multiphysics will then automatically interpret these expressions and symbolically calculate any derivatives that may be needed during the solution process. The idea of being able to enter expressions anywhere a setting can be defined in the COMSOL user interface is important since it allows you to customize the built-in models and parameter data without having to resort to writing external libraries.

Additional Resources

Excel is a registered trademark of Microsoft Corporation in the United States and/or other countries.

Article Categories

Loading Comments...