Reading values from Excel component into Flownex Scripts!

Categories:

Today we’re going to explore how we can directly reference specific cells in Excel workbooks via script. By building the direct reference in the script we can avoid having to assign specific cells as outputs and we can also avoid having to use data transfer links which can clutter our work canvas.

Code Snippets

Depending on when we want the script to execute we should choose the appropriate function to make this part of. Since I want this to be called every cycle I will make this part of the “Execute” function.

//script main execution function - called every cycle
public override void Execute(double Time)
{
//new code to go here	
}

The first bit of code we’re going to use will link our script to the excel file “Workbook (2).xlsx” in the Flownex project directory.

SpreadsheetGear.IWorkbookSet workbookSet = SpreadsheetGear.Factory.GetWorkbookSet(); 

// Path to where Flownex Project is located
System.IO.DirectoryInfo projectpath = new System.IO.DirectoryInfo(Project.ProjectRootPath);	        
		
// Create Workbook object linked to .xlsx file or .csv
SpreadsheetGear.IWorkbook workbook = workbookSet.Workbooks.Open(projectpath + "\Tasks\ExcelWorkBooks\Workbook (2).xlsx"); 

Now that we’ve created our workbook object connected to the Excel file we can read in values from cells in a couple of different ways.

To read from an explicit cell (A1),

// Read from Cell A1 in Sheet 1
double excel_value_1 = Convert.ToDouble(workbook.Worksheets["Sheet1"].Cells["A1"].Value); 

to read from a set row and column,

// Read from Row 4, Column 1 in Sheet 2 (Note: row and column indices start at 0)
double excel_value_2 = Convert.ToDouble(workbook.Worksheets["Sheet2"].Cells[3,0].Value); 

If the value in the Excel cell is a string we can use the following,

// Read text from Cell B1 in Sheet 1
string excel_value_3 = workbook.Worksheets["Sheet1"].Cells["B1"].Value as string; 

There we have it! These are now internal variables to the script. To assign them as output variables we can use the following syntax (this is all still within the Execute function).

//Save Value to Output Script Variable
Output1.Value = excel_value_1;
Output2.Value = excel_value_2;
Output3.Value = excel_value_3;

Then, as per the usual, we’ll need a bit of code to initialize these output variables and make them visible/usable outside of the script.

//constructer initializes parameters
	public Script()
	{
		_Output1 = new IPS.Properties.Double();
		_Output2 = new IPS.Properties.Double();
		_Output3 = new IPS.Properties.Text();
	}

	//property declarations to make
	//parameters visible to outside world
	[PropertyUsage(UseProperty.DYNAMIC)]
	public IPS.Properties.Double Output1
	{
		get
		{
			return _Output1;
		}
	}
	
	[PropertyUsage(UseProperty.DYNAMIC)]
	public IPS.Properties.Double Output2
	{
		get
		{
			return _Output2;
		}
	}
	
	[PropertyUsage(UseProperty.DYNAMIC)]
	public IPS.Properties.Text Output3
	{
		get
		{
			return _Output3;
		}
	}
}

Happy Friday and Happy Scripting!

Categories

Get Your Ansys Products & Support from the Engineers who Contribute to this Blog.

Technical Expertise to Enable your Additive Manufacturing Success.

PADT’s Pulse Newsletter

Keep up to date on what is going on at PADT by subscribing to our newsletter.


By submitting this form, you are consenting to receive marketing emails from: . You can revoke your consent to receive emails at any time by using the SafeUnsubscribe® link, found at the bottom of every email. Emails are serviced by Constant Contact

Share this post:

Upcoming Events

03/28/2024

SAF Blue Carpet Event

03/28/2024

2024 Arizona Space Summit

04/03/2024

Low Frequency Updates in Ansys 2024 R1 - Webinar

04/03/2024

Venture Madness Conference Reception + Expo

04/03/2024

Stratasys F3300: Game Changing Throughput - Webinar

04/08/2024

39th Space Symposium

04/09/2024

39th Space Symposium

04/10/2024

Discovery Updates in Ansys 2024 R1 - Webinar

04/10/2024

39th Space Symposium

04/11/2024

39th Space Symposium

04/22/2024

Experience Stratasys Truck Tour: Houston, TX

04/24/2024

Structures Updates in Ansys 2024 R1 (2)

04/24/2024

Experience Stratasys Truck Tour: Houston, TX

05/07/2024

Experience Stratasys Truck Tour: Albuquerque, NM

05/08/2024

Fluent Materials Processing Updates in Ansys 2024 R1 - Webinar

05/09/2024

Experience Stratasys Truck Tour: Los Alamos, NM

05/14/2024

Simulation World 2024

05/15/2024

Simulation World 2024

05/16/2024

Simulation World 2024

05/22/2024

Optics Updates in Ansys 2024 R1 - Webinar

06/12/2024

Connect Updates in Ansys 2024 R1 - Webinar

06/26/2024

Structures Updates in Ansys 2024 R1 (3) - Webinar

06/27/2024

E-Mobility and Clean Energy Summit

07/10/2024

Fluids Updates in Ansys 2024 R1 - Webinar

08/05/2024

2024 CEO Leadership Retreat

10/23/2024

PADT30 | Nerdtoberfest 2024

Search in PADT site

Contact Us

Most of our customers receive their support over the phone or via email. Customers who are close by can also set up a face-to-face appointment with one of our engineers.

For most locations, simply contact us: