Reading values from Excel component into Flownex Scripts!

By: Miles Adkins
– February 4, 2022
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

Certified Elite Channel Partner

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

Product Development
Platinum Partner

Technical Expertise to Enable your Addictive 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: Phoenix Analysis and Design Technologies, 7755 S. Research Dr., Tempe, AZ, 85284, https://www.padtinc.com. 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:

Share on twitter
Share on facebook
Share on linkedin
Share on pinterest

Upcoming Events

10/06/2022

Fluids Updates in Ansys 2022 R2 - Webinar

10/05/2022

Experience Stratasys Truck Tour - Colorado Springs

09/29/2022

White Hat Life Science Investor Conference - 2022

09/28/2022

2022 AZBio Awards

09/28/2022

Simulation Best Practices for Rotating Machinery Design & Development

09/21/2022

ExperienceIT NM 2022

09/21/2022

Additive Updates in Ansys 2022 R2 - Webinar

09/14/2022

Rocky Mountain Life Sciences Investor & Partnering Conference

09/08/2022

Ansys Optics Simulation User Group Meeting - Virtual

09/08/2022

Ansys Optics Simulation User Group Meeting

09/07/2022

SI & PI Updates in Ansys 2022 R2 - Webinar

08/31/2022

Simulation Best Practices for Developing Medical Devices - Webinar

08/24/2022

Mechanical Updates in Ansys 2022 R2 - Webinar

08/10/2022

Tucson after5 Tech Mixer: Ruda-Cardinal

08/05/2022

Flagstaff Tech Tour, 2022

08/02/2022

2022 CEO Leadership Retreat

08/01/2022

2022 CEO Leadership Retreat

07/27/2022

Thermal Integrity Updates in Ansys 2022 R1 - Webinar

07/20/2022

Simulation Best Practices for the Pharmaceutical Industry - Webinar

07/14/2022

NCMS Technology Showcase: Corpus Christi Army Depot

07/13/2022

NCMS Technology Showcase: Corpus Christi Army Depot

07/13/2022

Additive & Structural Optimization Updates in Ansys 2022 R1 - Webinar

07/07/2022

Arizona AADM Conference, 2022

06/29/2022

LS-DYNA Updates & Advancements in Ansys 2022 R1 - Webinar

06/23/2022

Simulation Best Practices for Wind Turbine Design - Webinar

06/15/2022

MAPDL Updates & Advancements in Ansys 2022 R1 - Webinar

06/01/2022

Mechanical Updates in Ansys 2022 R1 - pt. 2 Webinar

05/26/2022

Modelling liquid cryogenic rocket engines in Flownex - Webinar

05/25/2022

SMR & Advanced Reactor 2022

05/25/2022

05/24/2022

SMR & Advanced Reactor 2022

05/19/2022

RAPID + tct 2022

05/19/2022

Venture Cafe Roundtable: AI & Healthcare

05/18/2022

Tucson after5 Tech Mixer: World View

05/18/2022

RAPID + tct 2022

More Info

05/18/2022

Signal & Power Integrity Updates in Ansys 2022 R1 - Webinar

05/18/2022

Simulation World 2022

05/17/2022

RAPID + tct 2022

05/11/2022

Experience Stratasys Manufacturing Virtual Event

05/04/2022

Mechanical Meshing Updates in Ansys 2022 R1 - Webinar

04/27/2022

04/22/2022

12TH ANNUAL TUCSON GOLF TOURNAMENT

04/21/2022

04/20/2022

Additional Fluids Updates in Ansys 2022 R1

04/20/2022

Experience Stratasys Tour – Tempe Arizona

04/18/2022

Experience Stratasys Tour - Flagstaff Arizona

04/14/2022

D&M West | MD&M West

04/13/2022

D&M West | MD&M West

04/13/2022

Experience Stratasys Tour - Albuquerque New Mexico

04/12/2022

D&M West | MD&M West

04/12/2022

Experience Stratasys Tour - Los Alamos New Mexico

04/12/2022

Optimizing Engineering Workflows f​​​​or Propulsion System Design

04/07/2022

Experience Stratasys Tour - Austin Texas

04/07/2022

37th Space Symposium - Arizona Space Industry

04/06/2022

Transforming Digital Engineering with Ansys Discovery 2022 R1

04/06/2022

37th Space Symposium - Arizona Space Industry

04/05/2022

37th Space Symposium - Arizona Space Industry

04/04/2022

37th Space Symposium - Arizona Space Industry

03/30/2022

Simulation Best Practices for Vehicle Engineering - Webinar

03/23/2022

03/23/2022

High & Low Frequency Electromagnetics Updates in Ansys 2022 R1

02/24/2022

Arizona Technology Council After 5 Tech Mixer "Pandemic Pivot Pizza Pa

02/23/2022

SciTech Festival: Spend an Hour with 3D Printing Experts

02/11/2022

Webinar: Mechanical overview for Ansys 2022 R1

More Info

02/09/2022

Webinar: Product Development 101 (FAKE)

02/08/2022

Webinar: Navigating the Additive Landscape

01/27/2022

Arizona Technology Council 1st Quarter VIP Tech Mixer

More Info

01/26/2022

Simulation Best Practices for Gas Turbine Design & Development - Webin

More Info

01/19/2022

Arizona Photonics Days

More Info

11/04/2021

ExperienceIT, New Mexico

More Info

11/03/2021

Additive Manufacturing & Structural Optimization in Ansys 2021 R2 - We

More Info

11/03/2021

Optics Valley Technical Series: The Future of Simulation in the Optics

More Info

11/02/2021

SBIR Liftoff AZTC Virtual Breakfast Series

More Info

10/10/2021

Stratasys Mobile Truck Stop - Tucson Arizona

More Info

Search in PADT site

1h

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: