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!

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. Every month we share news about PADT, our partners, and our customers. We also share links to useful information on simulation, product development, and 3D Printing.  Sign up, and let's stay in touch.


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

07/18/2024

Stratafest - Denver, CO

08/05/2024

2024 CEO Leadership Retreat

09/09/2024

Turbine Engine Technology Symposium 2024

09/10/2024

New Mexico Tech Summit 2024

09/12/2024

Rocky Mountain Life Sciences Investor & Partnering Conference

09/13/2024

Rocky Mountain Life Sciences Investor & Partnering Conference

10/23/2024

PADT30 | Nerdtoberfest 2024

Search the 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: