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!
Miles Adkins 