Well, this one was a pretty disappointing endeavour. After nailing the Journal Importing down I would have expected budget importing from an Excel spreadsheet to be fairly straight forward too. How wrong I was.

I had expected to maybe invest around 20 hours of casual work (working whilst watching some tv), I’d be well north of 80 hours now. And why you ask – or why would you spend so much time on this? I’m not sure 🙂

The worst part is that when I run the script in the “Script Tool” it works perfectly assuming in the RequestCompleted I have a <debug>.WriteLine() (which was well within the 20 hours I budgeted) – it just bombs when it is deployed.

BUS100 works a little differently to other programs which you will see below, let us use Trace to see what happens when we key in a budget normally.

We go to BUS100/B1 and key in a new Budget Number and a Budget Version

Then we hit Create, this will issue a “User interaction: Option -1 BUS100/B1” and takes us to BUS100/E

We fill in the details

And hit Next.

Our user interaction is “Key ENTER BUS100/E” and we end up in BUS101/B1

It is here that we start keying in the accounts that we are going to create budgets for.

And then we hit Create.

Just like our other Create we have a User interaction: Option -1 BUS101/B1

Now the interesting thing about this panel is that we have an Apply button and we need to enter in our values in to the rows of a ListView. The really interesting part is that we need to issue either 2 Nexts or F1 and then Next in order to continue.

So, I have entered some values below

Now if I hit Next

We can see we have the User interaction: Key ENTER BUS101/E1
And we can see that the panel hasn’t changed; everything though is to two decimal places (btw, ensuring the values are at two decimal places doesn’t remove the need for the double enter or Apply Enter combo).

Now we hit Next again.

We can see that another User interaction: Key ENTER BUS101/E1 occurred and now we are back to BUS101/B1 ready to enter our next account that we want a budget against.

For the sake of completeness – the Apply button is like the first Next, and the call chain is as follows:

Ok, so those are the events that we need to check for in our RequestCompleted event, and we now also know what command to issue for the Apply button in BUS101/E1. And our basic flow

1. Enter Budget Number and Budget Version (BUS100/B1)
2. Hit Create (Option -1)
3. Fill in the Description, Name, Start Period, Exchange Rate TP, No. Periods and tick the Upd bal file on BUS100/E
4. Hit Next (Key ENTER)
5. Loop through our accounts
   1. Enter Account and Department (BUS101/B1)
   2. Hit Create (Option -1)
   3. Loop through our Periods
      1. Fill in the “For curr amount” (BUS101/E1)
   4. Hit Apply (Key F1)
   5. Hit Next (Key ENTER)
6. Finished

Pretty straight forward really isn’t it?

It works perfectly when you run the script in the Script Tool, however when deployed to the server it doesn’t. Eventually I ended up added extensive logging in to the code so I could see what was going on. It turns out that when the script is deployed and we hit either the first Next (or Apply) our panel is changing from BUS101/E1 to BUS101/B1 instead of staying on BUS101/E1 and as such nothing ever gets saved – we just end up with nothing in BUS101/B1

Very interesting. Anyways, sample spreadsheet is below

Oh, before we go too far, I guess I should point out a couple of the niftier things in this code…

As we need to populate values in the TextBoxes inside the ListView on BUS100/B1 and BUS101/B1 I created a function which will do the heavy lifting for us called getListViewHeaderTextBox()

You’ll also see us calling System.IO.File.AppendAllText() to write data out to a text file.

And finally, we actually set a CheckBox in this code :-O

As I previously mentioned, adding an output to the debug.WriteLine() makes the script work when using the Scripting Tool, why I am not sure and of-course the debugging is disabled when we deploy. I tried to effectively simulate this by using the Sytem.IO.File.AppendAllText() function, but that didn’t help.

So, the path forward appears to be figure out how to simulate the debug.WriteLine() or to effectively create the object manually when running the script or look at WebServices to do the real work of the script.

Eitherway, there are a handy functions in this script, a few new techniques so I thought it would be worth publishing.

import System;
import System.Text;
import System.Windows;
import System.Windows.Controls;
import System.Windows.Media;
import System.Windows.Media.Media3D;

import System.Threading;
import System.TimeSpan;

import MForms;
import Mango.UI.Core;
import Mango.UI.Core.Util;
import Mango.UI.Services;
import Mango.Services;
import Mango.UI.Services.Lists;

import Excel;
import System.Reflection;

package MForms.JScript
{
    class BudgetImport_V059
    {
		var giicInstanceController : IInstanceController = null;    // this is where we will store the IInstanceController to make it available to the rest of the class
		var ggrdContentGrid : Grid = null;                          // this is the Grid that we get passed by the Init()
		var gexaApplication = null;                                 // Excel.Application object
		
		var gbtnImportFromExcel : Button = null;                    // this is the button that we will put on to the panel that will kick off the whole import
		var glvListView : ListView = null;                          // this is the ListView on the panel
		
		var gwbWorkbook = null;                                     // here we will store the Workbook object
		
		var giStartRow : int = 7;                                  // the starting row in the Spreadsheet
		var giMaxRow : int = 10000;                                 // the end row in the Spreadsheet
		var giCurrentRowBUS101_E1 : int = 7;                        // the current row in the Spreadsheet
        var giLVRowBUS101_E1 : int = 0;                             // ListView row
        var giCurrentRowBUS101_B1 : int = 7;
		var giLVRowBUS101_B1 : int = 0;                             // ListView row

		var gbLookForResponse = false;                              // should we be looking for a response?

        var gobjStatusBUS100_B1 = null;                                      // the statusbar
        var gobjStatusBUS101_B1 = null;
        var gobjStatusBUS101_E1 = null;

        var gbRequest : boolean = false;                            // the request event 

        var giNumberOfPeriods : int = 0;                            // this is the number of periods that we have

        var giBUS101_E1Count : int;                             // we need to press enter twice on BUS101_E1

        var gobjDebug;

        public function Init(element: Object, args: Object, controller : Object, debug : Object)
        {
            // lets make some of the controls and other
            // bits pieces available to other sections of our code
            gobjDebug = debug;
			ggrdContentGrid = controller.RenderEngine.Content;
			giicInstanceController = controller;
			glvListView = controller.RenderEngine.ListControl.ListView;
			
			try
			{
                // create the button for importing
				gbtnImportFromExcel = new Button();
				gbtnImportFromExcel.Content = "Import";

				Grid.SetColumnSpan(gbtnImportFromExcel, 10);
				Grid.SetColumn(gbtnImportFromExcel, 1);
				Grid.SetRow(gbtnImportFromExcel, 22);
				
                // finally add the control to the grid
				ggrdContentGrid.Children.Add(gbtnImportFromExcel);
				
				// ----- Events -----
				gbtnImportFromExcel.add_Click(OnImportFromExcelClicked);
				gbtnImportFromExcel.add_Unloaded(OnImportFromExcelUnloaded);

			}
			catch(exException)
			{
				MessageBox.Show("Error: " + exException.Message + Environment.NewLine + exException.StackTrace);
			}
        }



        // this is where we actually do the import
		private function OnImportFromExcelClicked(sender : Object, e : RoutedEventArgs)
		{
			try
			{
                // here we do some initialisation of Excel
				InitialiseExcel();

				var strFilename : String = retrieveImportFile();            // retrieve the filename of the Excel spreadsheet to open
				if((null != strFilename) && (null != gexaApplication))      // ensure that not only do we have a filename, but we also managed to initialise Excel
				{
					gwbWorkbook = gexaApplication.Workbooks.Open(strFilename);  // open the spreadsheet
					if(null != gwbWorkbook)
					{
				        giicInstanceController.add_RequestCompleted(OnRequestCompleted);    // subscribe to the RequestCompleted event
                        
                        gbRequest = true;

						gwbWorkbook.Saved = true;                               // get rid of those annoying save messages

                        // here I am saving some information for the C:\Temp\BUS101.txt file to diagnose what is going on
                        System.IO.File.AppendAllText("C:\\Temp\\BUS100.txt", DateTime.Now.ToString("yyyyMMdd HH:mm:ss") + " ******* OnImportFromExcelClicked: Start" + Environment.NewLine);

                        // no we actually kick off our process
                        handleBUS100_B1();
					}
					else MessageBox.Show("Failed to Open Workbook");
				}
				else MessageBox.Show("Filename or Excel doesn't exist: " + strFilename);
			}
			catch(exException)
			{
				MessageBox.Show("Error: " + exException.description);
			}
		}


		public function OnRequestCompleted(sender: Object, e: RequestEventArgs)
		{
            var strError : String = null;

            // log information to a file for later analysis
            System.IO.File.AppendAllText("C:\\Temp\\BUS100.txt", DateTime.Now.ToString("yyyyMMdd HH:mm:ss") + " OnRequestCompleted: Entering function" + Environment.NewLine);

            try
            {
                gobjDebug.WriteLine("OnRequestCompleted: giBUS101_E1Count = " + giBUS101_E1Count + " CommandType: " + e.CommandType + " CommandValue: " + e.CommandValue);  // we need this command for our script to work properly!

                System.IO.File.AppendAllText("C:\\Temp\\BUS100.txt", DateTime.Now.ToString("yyyyMMdd HH:mm:ss") + " OnRequestCompleted: giBUS101_E1Count = " + giBUS101_E1Count + " CommandType: " + e.CommandType + " CommandValue: " + e.CommandValue + " - giicInstanceController.RenderEngine.PanelHeader - " + giicInstanceController.RenderEngine.PanelHeader + Environment.NewLine);

			    if(e.CommandType == MNEProtocol.CommandTypeKey)     // we're looking for a key event
			    {
				    if(e.CommandValue == MNEProtocol.KeyEnter)      // specifically we're looking the enter key event
				    {
                        strError = checkForError();                 // check for any errors

                        if(true == String.IsNullOrEmpty(strError))
                        {
					        if(true == giicInstanceController.RenderEngine.PanelHeader.EndsWith("BUS100/B1"))   // are we on panel BUS100/B1?
					        {
                                System.IO.File.AppendAllText("C:\\Temp\\BUS100.txt", DateTime.Now.ToString("yyyyMMdd HH:mm:ss") + " OnRequestCompleted: about to call handleBUS100_B1" + Environment.NewLine);
						        handleBUS100_B1();     // handle panel E
                                System.IO.File.AppendAllText("C:\\Temp\\BUS100.txt", DateTime.Now.ToString("yyyyMMdd HH:mm:ss") + " OnRequestCompleted: returned from call handleBUS100_B1" + Environment.NewLine);
                                strError = checkForError();
					        }
                            else if(true == giicInstanceController.RenderEngine.PanelHeader.EndsWith("BUS101/B1")) // are we on panel BUS101/B1
                            {
                                strError = checkForError();

                                if(true != String.IsNullOrEmpty(strError))
                                {
                                    giCurrentRowBUS101_B1 = giMaxRow + 1;   // if there was an error we should effectively stop processing
                                }
                                else
                                {
                                    if(giCurrentRowBUS101_B1 <= giMaxRow)   // giMaxRow is the maximum row that we should go down to on the spreadsheet
                                    {
                                        giBUS101_E1Count = 0;       // this keeps track of how many 'enters' we have pressed on BUS101/E1 - one of my earlier attempts

                                        System.IO.File.AppendAllText("C:\\Temp\\BUS100.txt", DateTime.Now.ToString("yyyyMMdd HH:mm:ss") + " OnRequestCompleted: about to call handleBUS101_B1" + Environment.NewLine);
                                        handleBUS101_B1();    // handle panel j
                                        System.IO.File.AppendAllText("C:\\Temp\\BUS100.txt", DateTime.Now.ToString("yyyyMMdd HH:mm:ss") + " OnRequestCompleted: returned from call handleBUS101_B1" + Environment.NewLine);
                                    }
                                }
                            }
					        else if(true == giicInstanceController.RenderEngine.PanelHeader.EndsWith("BUS101/E1")) // are we on panel BUS101/E1
					        {
                                System.IO.File.AppendAllText("C:\\Temp\\BUS100.txt", DateTime.Now.ToString("yyyyMMdd HH:mm:ss") + " OnRequestCompleted: Found BUS101/E1 from an ENTER" + Environment.NewLine);
                                strError = checkForError();

                                if(0 == giBUS101_E1Count)   // if this is the first time we are on BUS101/E1 then we should fill in our values
                                {
                                    if(true != String.IsNullOrEmpty(strError))
                                    {
                                        giCurrentRowBUS101_E1 = giMaxRow + 1;   // if there is an error we should of-course stop looking through the spreadsheet and exit
                                    }
                                    else
                                    {
                                        System.IO.File.AppendAllText("C:\\Temp\\BUS100.txt", DateTime.Now.ToString("yyyyMMdd HH:mm:ss") + " OnRequestCompleted: about to call handleBUS101_E1" + Environment.NewLine);
                                        handleBUS101_E1();    // handle BUS101/E1 - populate our values
                                        System.IO.File.AppendAllText("C:\\Temp\\BUS100.txt", DateTime.Now.ToString("yyyyMMdd HH:mm:ss") + " OnRequestCompleted: returned from call handleBUS101_E1" + Environment.NewLine);
                                    }
                                }
                                else
                                {
                                    System.IO.File.AppendAllText("C:\\Temp\\BUS100.txt", DateTime.Now.ToString("yyyyMMdd HH:mm:ss") + " OnRequestCompleted: giBUS101_E1Count = " + giBUS101_E1Count + " about to press enter " + Environment.NewLine);
                                    
                                    giBUS101_E1Count = 0;   // we reset out count here as we are greater than 0
                                    giicInstanceController.PressKey("ENTER");   // this should be our 'second' enter
                                }
					        }
                        }
                        else
                        {
                            // setLineStatus(strError);
                        }
				    }
                    else if(0 == String.Compare("F1", e.CommandValue))  // if we are working on F1 rather than two enters, then when we hit F1 we should press ENTER
                    {
                        System.IO.File.AppendAllText("C:\\Temp\\BUS100.txt", DateTime.Now.ToString("yyyyMMdd HH:mm:ss") + " OnRequestCompleted: F1 was pressed" + Environment.NewLine);
                        // below was commented out so I could see the behaviour
//                        if(true == giicInstanceController.RenderEngine.PanelHeader.EndsWith("BUS101/E1"))
//                        {
                            System.IO.File.AppendAllText("C:\\Temp\\BUS100.txt", DateTime.Now.ToString("yyyyMMdd HH:mm:ss") + " OnRequestCompleted: Found BUS101/E1 from F1 about to press enter" + Environment.NewLine);
                            giicInstanceController.PressKey("ENTER");
                            System.IO.File.AppendAllText("C:\\Temp\\BUS100.txt", DateTime.Now.ToString("yyyyMMdd HH:mm:ss") + " OnRequestCompleted: Found BUS101/E1 from F1 after press enter" + Environment.NewLine);
//                        }
                    }
                    else
                    {
                        System.IO.File.AppendAllText("C:\\Temp\\BUS100.txt", DateTime.Now.ToString("yyyyMMdd HH:mm:ss") + " OnRequestCompleted: if(e.CommandValue == MNEProtocol.KeyEnter)" + Environment.NewLine);
                    }
			    }
                else if(e.CommandType == MNEProtocol.CommandTypeListOption)
                {
                    if(e.CommandValue == MNEProtocol.OptionCreate)
                    {
                        if(true == String.IsNullOrEmpty(strError))
                        {
					        if(true == giicInstanceController.RenderEngine.PanelHeader.EndsWith("BUS100/E"))   // are we on panel BUS100/E?
					        {
                                System.IO.File.AppendAllText("C:\\Temp\\BUS100.txt", DateTime.Now.ToString("yyyyMMdd HH:mm:ss") + " OnRequestCompleted: List about to call handleBUS100_E" + Environment.NewLine);
						        handleBUS100_E();     // handle panel E
                                System.IO.File.AppendAllText("C:\\Temp\\BUS100.txt", DateTime.Now.ToString("yyyyMMdd HH:mm:ss") + " OnRequestCompleted: List  returned from call handleBUS100_E" + Environment.NewLine);
                                strError = checkForError();
					        }
					        else if(true == giicInstanceController.RenderEngine.PanelHeader.EndsWith("BUS101/E1")) // are we on panel BUS101/E1
					        {
                                strError = checkForError();

                                if(true != String.IsNullOrEmpty(strError))
                                {
                                    giCurrentRowBUS101_E1 = giMaxRow + 1;
                                }
                                else
                                {
                                    System.IO.File.AppendAllText("C:\\Temp\\BUS100.txt", DateTime.Now.ToString("yyyyMMdd HH:mm:ss") + " OnRequestCompleted: List about to call handleBUS101_E1 " + giicInstanceController.RenderEngine.PanelHeader + Environment.NewLine);
                                    handleBUS101_E1();    // handle 
                                    System.IO.File.AppendAllText("C:\\Temp\\BUS100.txt", DateTime.Now.ToString("yyyyMMdd HH:mm:ss") + " OnRequestCompleted: List returned from call handleBUS101_E1 " + giicInstanceController.RenderEngine.PanelHeader + Environment.NewLine);
                                    //giicInstanceController.PressKey("ENTER");
                                    //System.IO.File.AppendAllText("C:\\Temp\\BUS100.txt", DateTime.Now.ToString("yyyyMMdd HH:mm:ss") + " OnRequestCompleted: List returned from call handleBUS101_E1 - pressing enter " + giicInstanceController.RenderEngine.PanelHeader + Environment.NewLine);
                                }
					        }
                        }
                        else
                        {
                            MessageBox.Show("There was an error");
                        }
                    }
                }
                if(null != giicInstanceController.Response)
                {
                    if(0 == String.Compare(giicInstanceController.Response.Request.RequestType.ToString(), "Panel"))    // we should look at doing a cleanup here as we are leaving the panel
                    {
                        if((MNEProtocol.CommandTypeKey == giicInstanceController.Response.Request.CommandType) && (MNEProtocol.KeyF03 == giicInstanceController.Response.Request.CommandValue))
                        {
                            System.IO.File.AppendAllText("C:\\Temp\\BUS100.txt", DateTime.Now.ToString("yyyyMMdd HH:mm:ss") + " OnRequestCompleted: About to CleanUp()" + Environment.NewLine);
                            CleanUp();
                            System.IO.File.AppendAllText("C:\\Temp\\BUS100.txt", DateTime.Now.ToString("yyyyMMdd HH:mm:ss") + " OnRequestCompleted: We've called CleanUp()" + Environment.NewLine);
                        }
                    }
                }
            }
            catch(ex)
            {
                MessageBox.Show(ex.message);
            }
            if(null != strError)
            {
                CleanUp();
            }
            System.IO.File.AppendAllText("C:\\Temp\\BUS100.txt", DateTime.Now.ToString("yyyyMMdd HH:mm:ss") + " OnRequestCompleted: Exiting function" + Environment.NewLine);
		}

        // This function will go out and actually enter our budget and the version
        private function handleBUS100_B1()
        {
            System.IO.File.AppendAllText("C:\\Temp\\BUS100.txt", DateTime.Now.ToString("yyyyMMdd HH:mm:ss") + " handleBUS100_B1: Entering function" + Environment.NewLine);
            // "W1BUNO" - Budget Number
            // "W1BVER" - Budget Version
            var w1buno : String = retrieveFromActiveSheet("B3");    // retrieve our Budget Number from the spreadsheet
            var w1bver : String = retrieveFromActiveSheet("D3");    // retrieve our Budget Version from the spreadsheet
            if(false == String.IsNullOrEmpty(w1buno))
            {
                if(false == String.IsNullOrEmpty(w1bver))
                {
                    var tbW1BUNO : TextBox = getListViewHeaderTextBox(glvListView, "W1BUNO");   // populate the ListViewHeader textbox with our Budget Number
                    if(null != tbW1BUNO)
                    {
                        tbW1BUNO.Text = w1buno;
                    }
                    else MessageBox.Show("W1BUNO not found!");

                    var tbW1BVER : TextBox = getListViewHeaderTextBox(glvListView, "W1BVER");   // populate the ListViewHeader textbox with our Budget Version
                    if(null != tbW1BVER)
                    {
                        tbW1BVER.Text = w1bver;
                    }
                    else MessageBox.Show("W1BVER not found!");

                    giicInstanceController.ListOption("-1");    // create the budget
                }
            }
            System.IO.File.AppendAllText("C:\\Temp\\BUS100.txt", DateTime.Now.ToString("yyyyMMdd HH:mm:ss") + " handleBUS100_B1: Exiting function" + Environment.NewLine);
        }

        // handleBUS100_E will enter the information specific to the budget - this is number of periods
        // starting period etc
        private function handleBUS100_E()
        {
            System.IO.File.AppendAllText("C:\\Temp\\BUS100.txt", DateTime.Now.ToString("yyyyMMdd HH:mm:ss") + " handleBUS100_E: Exiting function" + Environment.NewLine);
            // "WETX40" - Description
            // "WETX15" - Name
            // "WEBSPR" - Start Period
            // "WECRTP" - Exchange Rate
            // "WENPAM" - Number of Periods
            // "WEUPDB" - Update Balance File (Checkbox)

            var wetx40 : String = retrieveFromActiveSheet("B4");    // Budget Description
            var wetx15 : String = retrieveFromActiveSheet("B4");    // Budget Name
            var webspr : String = retrieveFromActiveSheet("D4");    // Start Period
            // var wecrtp : String = "1"; //retrieveFromActiveSheet("B3"); 
            var wecrtp : String = retrieveFromActiveSheet("K4");    // Exchange Rate
            var wenpam : String = retrieveFromActiveSheet("G4");    // Number of Periods
            // var weupdb : String = retrieveFromActiveSheet("B3");
            var weupdb : String = retrieveFromActiveSheet("I4");    // Update Balance File?
            var bUpdate : boolean = false;

            if(true == doWeHaveAValueFromSpreadsheet(weupdb))       // do some basic verification of the value from the spreadsheet
            {
                if(0 == String.Compare(weupdb, "true", true))       // this is our checkbox so we want a True/False value
                {
                    bUpdate = true;
                }
                else if(0 == String.Compare(weupdb, "yes", true))
                {
                    bUpdate = true;
                }
            }

            if(true == doWeHaveAValueFromSpreadsheet(wenpam))       // do some basic verification of the number value from the spreadsheet
            {
                giNumberOfPeriods = parseInt(wenpam);               // this is the number of periods, we were going to use this value for the looping purposes in BUS101/E1 but didn't get that far
            }
            

            setM3TextField("WETX40", wetx40);           // set the description
            setM3TextField("WETX15", wetx15);           // set the name
            setM3TextField("WEBSPR", webspr);           // set the start period
            setM3TextField("WECRTP", wecrtp);           // set the exchange rate
            setM3TextField("WENPAM", wenpam);           // set the number of periods
            setM3CheckboxField("WEUPDB", bUpdate);      // and set the Checkbox to update the balance file
            
            
            giicInstanceController.PressKey("ENTER");   // press the enter key
            System.IO.File.AppendAllText("C:\\Temp\\BUS100.txt", DateTime.Now.ToString("yyyyMMdd HH:mm:ss") + " handleBUS100_E: Exiting function" + Environment.NewLine);
        }

        // here we handle Panel BUS101/B1, where we record the gl account information that we will be applying the budget against
        private function handleBUS101_B1()
        {
            System.IO.File.AppendAllText("C:\\Temp\\BUS100.txt", DateTime.Now.ToString("yyyyMMdd HH:mm:ss") + " handleBUS101_B1: Exiting function" + Environment.NewLine);
            // "W1AIT1" - 
            // "W1AIT2" - 
            // "W1AIT3" - 
            // "W1AIT4" - 
            // "W1AIT5" - 
            // "W1AIT6" - 
            // "W1AIT7" - 
            // "W1CUCD" - NZD
            // "W1AMTN" - Curve

            // retrieve the records from the spreadsheet
            var w1ait1 : String = retrieveFromActiveSheet("A" + giCurrentRowBUS101_B1);
            var w1ait2 : String = retrieveFromActiveSheet("B" + giCurrentRowBUS101_B1);
            var w1ait3 : String = retrieveFromActiveSheet("C" + giCurrentRowBUS101_B1);
            var w1ait4 : String = retrieveFromActiveSheet("D" + giCurrentRowBUS101_B1);
            var w1ait5 : String = retrieveFromActiveSheet("E" + giCurrentRowBUS101_B1);
            var w1ait6 : String = retrieveFromActiveSheet("F" + giCurrentRowBUS101_B1);
            var w1ait7 : String = retrieveFromActiveSheet("G" + giCurrentRowBUS101_B1);
            var w1cucd : String = retrieveFromActiveSheet("H" + giCurrentRowBUS101_B1);
            var w1amtn : String = retrieveFromActiveSheet("I" + giCurrentRowBUS101_B1);

            if(true == doWeHaveAValueFromSpreadsheet(w1ait1))   // verify that we at least have the first dimension of the Account (the rest we don't need to have)
            {

                // set the ListView TextBoxes for each of the values
                setListViewHeaderTextBox(giicInstanceController.RenderEngine.ListControl.ListView, "W1AIT1", w1ait1); 
                setListViewHeaderTextBox(giicInstanceController.RenderEngine.ListControl.ListView, "W1AIT2", w1ait2);
                setListViewHeaderTextBox(giicInstanceController.RenderEngine.ListControl.ListView, "W1AIT3", w1ait3);
                setListViewHeaderTextBox(giicInstanceController.RenderEngine.ListControl.ListView, "W1AIT4", w1ait4);
                setListViewHeaderTextBox(giicInstanceController.RenderEngine.ListControl.ListView, "W1AIT5", w1ait5);
                setListViewHeaderTextBox(giicInstanceController.RenderEngine.ListControl.ListView, "W1AIT6", w1ait6);

                setListViewHeaderTextBox(giicInstanceController.RenderEngine.ListControl.ListView, "W1CUCD", w1cucd);
                setListViewHeaderTextBox(giicInstanceController.RenderEngine.ListControl.ListView, "W1AMTN", w1amtn);

                giCurrentRowBUS101_B1++;    // increment the spreadsheet row we are looking at
                giicInstanceController.ListOption("-1");    // create
            }
            System.IO.File.AppendAllText("C:\\Temp\\BUS100.txt", DateTime.Now.ToString("yyyyMMdd HH:mm:ss") + " handleBUS101_B1: Exiting function" + Environment.NewLine);
        }



        // actually populate the actual retrieval and imputting of the budget values themselves
        private function handleBUS101_E1()
        {
            System.IO.File.AppendAllText("C:\\Temp\\BUS100.txt", DateTime.Now.ToString("yyyyMMdd HH:mm:ss") + "handleBUS101_E1: Entering, giBUS101_E1Count = " + giBUS101_E1Count + Environment.NewLine);
            // below disabled purely for debugging purposes
//            if(giCurrentRowBUS101_E1 <= giMaxRow)
//            {
                // retrieve each of our periods from the spreadsheet
                var strPeriod1 : String = retrieveFromActiveSheet("J" + (giCurrentRowBUS101_B1 - 1));
                var strPeriod2 : String = retrieveFromActiveSheet("K" + (giCurrentRowBUS101_B1 - 1));
                var strPeriod3 : String = retrieveFromActiveSheet("L" + (giCurrentRowBUS101_B1 - 1));
                var strPeriod4 : String = retrieveFromActiveSheet("M" + (giCurrentRowBUS101_B1 - 1));
                var strPeriod5 : String = retrieveFromActiveSheet("N" + (giCurrentRowBUS101_B1 - 1));
                var strPeriod6 : String = retrieveFromActiveSheet("O" + (giCurrentRowBUS101_B1 - 1));
                var strPeriod7 : String = retrieveFromActiveSheet("P" + (giCurrentRowBUS101_B1 - 1));
                var strPeriod8 : String = retrieveFromActiveSheet("Q" + (giCurrentRowBUS101_B1 - 1));
                var strPeriod9 : String = retrieveFromActiveSheet("R" + (giCurrentRowBUS101_B1 - 1));
                var strPeriod10 : String = retrieveFromActiveSheet("S" + (giCurrentRowBUS101_B1 - 1));
                var strPeriod11 : String = retrieveFromActiveSheet("T" + (giCurrentRowBUS101_B1 - 1));
                var strPeriod12 : String = retrieveFromActiveSheet("U" + (giCurrentRowBUS101_B1 - 1));

                // ensure that we have the correct ListView from the current panel and give it the focus
                var lvListView : ListView = giicInstanceController.RenderEngine.ListControl.ListView;
                giicInstanceController.RenderEngine.ListControl.Focus();
               

                giCurrentRowBUS101_E1 = giCurrentRowBUS101_E1 + 1;

                 System.IO.File.AppendAllText("C:\\Temp\\BUS100.txt", DateTime.Now.ToString("yyyyMMdd HH:mm:ss") + "handleBUS101_E1: ListView Count = " + lvListView.Items.Count + " "  + giicInstanceController.RenderEngine.PanelHeader + Environment.NewLine);

                if(lvListView.Items.Count >= 1)
                {
                    if(0 == giBUS101_E1Count)   // if this is the first time we are called
                    {
                        for(var i : int = 0; i < lvListView.Items.Count; i++)   // loop through each of the periods adding a value to each period
                        {
                            var strValue : String = null;

                            if(0 == i)
                            {
                                strValue = strPeriod1;
                            }
                            else if(1 == i)
                            {
                                strValue = strPeriod2;
                            }
                            else if(2 == i)
                            {
                                strValue = strPeriod3;
                            }
                            else if(3 == i)
                            {
                                strValue = strPeriod4;
                            }
                            else if(4 == i)
                            {
                                strValue = strPeriod5;
                            }
                            else if(5 == i)
                            {
                                strValue = strPeriod6;
                            }
                            else if(6 == i)
                            {
                                strValue = strPeriod7;
                            }
                            else if(7 == i)
                            {
                                strValue = strPeriod8;
                            }
                            else if(8 == i)
                            {
                                strValue = strPeriod9;
                            }
                            else if(9 == i)
                            {
                                strValue = strPeriod10;
                            }
                            else if(10 == i)
                            {
                                strValue = strPeriod11;
                            }
                            else if(11 == i)
                            {
                                strValue = strPeriod12;
                            }

                            var lviItem : ListRow = lvListView.Items[i];
                            if(null != lviItem)
                            {
                                if(null != lviItem.Items)
                                {
                                    var ecEditableCell : EditableCell = lviItem.Items[2];   // we want the 3rd column
                                    if(null != ecEditableCell)
                                    {
                                        ecEditableCell.Text = convertTo2DecimalPlaces(strValue);    // populate the EditCell (or the box in the ListView) with a value to two decimal places
                                    }
                                }
                            }
                        }
                        giBUS101_E1Count = giBUS101_E1Count + 1;

                        System.IO.File.AppendAllText("C:\\Temp\\BUS100.txt", DateTime.Now.ToString("yyyyMMdd HH:mm:ss") + " handleBUS101_E1: About to Press " + MNEProtocol.KeyF1() + " - Panel Header: " + giicInstanceController.RenderEngine.PanelHeader + Environment.NewLine);
                        giicInstanceController.PressKey("F1");  // press F1 (or the Apply button)
                    }
                    else
                    {
                        System.IO.File.AppendAllText("C:\\Temp\\BUS100.txt", "Second enter pressed " + giBUS101_E1Count + Environment.NewLine);
                        giBUS101_E1Count = giBUS101_E1Count + 1;
                        giicInstanceController.PressKey("ENTER");   // press the enter key
                    }
                }
            System.IO.File.AppendAllText("C:\\Temp\\BUS100.txt", DateTime.Now.ToString("yyyyMMdd HH:mm:ss") + "   **** handleBUS101_E1: Exiting function" + Environment.NewLine);
        }

        // a little wee helper function that will search for a TextBox name
        // and set the TextBox value
		private function setM3TextField(astrName : String, astrValue : String)
		{
			var tbTextBox : TextBox = ScriptUtil.FindChild(ggrdContentGrid, astrName);
			if(null != tbTextBox)
			{
				tbTextBox.Text = astrValue;
			}
			else MessageBox.Show("Can't find: " + astrName);
		}

        // this helper function will find and then set the value of our CheckBox
		private function setM3CheckboxField(astrName : String, abValue : boolean)
		{
			var cbCheckBox : CheckBox = ScriptUtil.FindChild(ggrdContentGrid, astrName);
			if(null != cbCheckBox)
			{
                if(true == abValue)
                {
                    cbCheckBox.IsChecked = true;
                }
                else
                {
                    cbCheckBox.IsChecked = false;
                }
                
			}
			else MessageBox.Show("Can't find: " + astrName);
		}

        // retrieve some data from the active spreadsheet
        // at a specific location
		private function retrieveFromActiveSheet(astrPosition : String)
		{
			var strValue : String = gwbWorkbook.ActiveSheet.Range(astrPosition).Value;
			if(true == String.IsNullOrEmpty(strValue))
			{
				strValue = "";
			}
            else if(0 == String.Compare(strValue, "undefined"))
            {
                strValue = "";
            }

			return(strValue);
		}

        // check for errors
        // We will go out and look for the status control if we 
        // don't have it
        private function checkForError()
        {
            var strResult : String = null;
            var strStatusMessage : String = null;
            var objCurrentErrorBox = null;

            try
            {
                if(true == giicInstanceController.RenderEngine.PanelHeader.EndsWith("BUS100/B1"))
                {
                    if(null == gobjStatusBUS100_B1)              // have we found the status control, if not, try to find it
                    {
                        gobjStatusBUS100_B1 = findControl();     // do the actual finding of the control
                    }
                    if(null != gobjStatusBUS100_B1)
                    {
                        objCurrentErrorBox = gobjStatusBUS100_B1;
                    }
                    else
                    {
                        MessageBox.Show("Couldn't find the StatusBar");
                    }
                }
                else if(true == giicInstanceController.RenderEngine.PanelHeader.EndsWith("BUS101/B1"))
                {
                    if(null == gobjStatusBUS101_B1)              // have we found the status control, if not, try to find it
                    {
                        gobjStatusBUS101_B1 = findControl();     // do the actual finding of the control
                    }
                    if(null != gobjStatusBUS101_B1)
                    {
                        objCurrentErrorBox = gobjStatusBUS101_B1;
                    }
                    else
                    {
                        MessageBox.Show("Couldn't find the StatusBar");
                    }
                }
                else if(true == giicInstanceController.RenderEngine.PanelHeader.EndsWith("BUS101/E1"))
                {
                    if(null == gobjStatusBUS101_E1)              // have we found the status control, if not, try to find it
                    {
                        gobjStatusBUS101_E1 = findControl();     // do the actual finding of the control
                    }
                    if(null != gobjStatusBUS101_E1)
                    {
                        objCurrentErrorBox = gobjStatusBUS101_E1;
                    }
                    else
                    {
                        MessageBox.Show("Couldn't find the StatusBar");
                    }
                }

                if(null != objCurrentErrorBox)
                {
                    // now we should extract the status
                    strStatusMessage = objCurrentErrorBox.Content.ToString();
                    if(false == String.IsNullOrEmpty(strStatusMessage))
                    {
                        strResult = strStatusMessage;
                        MessageBox.Show("There was an error, cannot continue! '" + objCurrentErrorBox.Content.toString() + "'");
                    }
                }
            }
            catch(ex)
            {
                MessageBox.Show("checkForError() exception: " + ex.message);
            }

            return(strResult);
        }

        // display an OpenFileDialog box
        // and extract the result
		private function retrieveImportFile()
		{
			var result : String = null;
			var ofdFile = new System.Windows.Forms.OpenFileDialog();    // we have to use the forms OpenFileDialog unfortunately
			if(null != ofdFile)
			{
				ofdFile.Multiselect = false;
				ofdFile.Filter = "Excel Files (*.xls;*.xlsx)|*.xls;*.xlsx|All Files (*.*)|*.*"; // filter on xls or xlsx files only
				
				if(true == ofdFile.ShowDialog())
				{
					result = ofdFile.FileName;
				}
			}
			return(result);
		}

        private function CleanUp()
        {
            if(true == gbRequest)
            {
                giicInstanceController.remove_RequestCompleted(OnRequestCompleted);
        	    //giicInstanceController.remove_RequestCompleted(OnRequested);
            }
            gbRequest = false;
            CleanUpExcel();
        }

        // our Import button is being unloaded, now's a good time to clean
        // everything up
		private function OnImportFromExcelUnloaded(sender : Object, e : RoutedEventArgs)
		{
			if(null != gbtnImportFromExcel)
			{
				gbtnImportFromExcel.remove_Click(OnImportFromExcelClicked);
				gbtnImportFromExcel.remove_Unloaded(OnImportFromExcelUnloaded);
			}
		}

        // ==========================================
        // ============ Helper Functions ============
        // ==========================================

        // set the value of the text in a column header
        private function setListViewHeaderTextBox(alvListView : ListView, astrName : String, astrValue : String)
        {
            var tbTextBox : TextBox = getListViewHeaderTextBox(alvListView, astrName);

            if(null != tbTextBox)
            {
                if(null != astrName)
                {
                    tbTextBox.Text = astrValue;
                }
                else
                {
                    tbTextBox.Text = "";
                }
            }
            else MessageBox.Show("TextBox " + astrName + " not found!");
        }


        // this function will give us access to TextBoxes within the header of a ListView
        private function getListViewHeaderTextBox(alvListView : ListView, astrFieldName : String)
        {
            var tbResult : TextBox = null;
            if(null != alvListView)
            {
                var gvGridView : GridView = alvListView.View;       // get the GridView of the ListView
                if(null != gvGridView)
                {
                    for(var i : int = 0; i < gvGridView.Columns.Count; i++) // cycle through the columns
                    {
                        if(null != gvGridView.Columns[i].Header)            // check that the header isn't empty
                        {
                            try
                            {
                                if(0 == String.Compare(gvGridView.Columns[i].Header.GetType(), "System.Windows.Controls.GridViewColumnHeader"))
                                {
                                    if(0 == String.Compare(gvGridView.Columns[i].Header.Content.GetType(), "System.Windows.Controls.StackPanel"))   // verify that we have a StackPanel
                                    {
                                        if(gvGridView.Columns[i].Header.Content.Children.Count >= 2)    // if we have more than 1 child then we aren't just a label, but a label and a TextBox
                                        {
                                            if(0 == String.Compare(gvGridView.Columns[i].Header.Content.Children[1].GetType(), "System.Windows.Controls.TextBox"))  // verify the second child in the StackPanel is a TextBox
                                            {
                                                var tbTextBox : TextBox = gvGridView.Columns[i].Header.Content.Children[1]; // retrieve the TextBox
                                                if(null != tbTextBox)
                                                {
                                                    if(0 == String.Compare(tbTextBox.Name, astrFieldName, true))        // compare that TextBoxes name
                                                    {
                                                        tbResult = tbTextBox;   // return the textbox and break from the loop
                                                        break;
                                                    }
                                                }
                                            }
                                        }
                                    }
                                }
                            }
                            catch(ex)
                            {
                                MessageBox.Show("Exception on column " + i);
                            }

                        }
                    }
                }
            }
            else MessageBox.Show("No ListView found!");

            return(tbResult);
        }

        // convert a string value to 2 decimal places
        private function convertTo2DecimalPlaces(astrValue : String)
        {
            var strResult : String = "";

            if(false == String.IsNullOrEmpty(astrValue))
            {
                var dblTemp : double = astrValue;
                strResult = dblTemp.ToString("#.##");
            }

            return(strResult);
        }

        // check to ensure that we have a value when we extract from the 
        // spreadsheet
        private function doWeHaveAValueFromSpreadsheet(astrValue : String)
        {
            var bResult : boolean = false;
            if(false == String.IsNullOrEmpty(astrValue))
            {
                if(0 != String.Compare(astrValue, "undefined"))
                {
                    bResult = true;
                }
            }
            return(bResult);
        }

        private function CleanUpExcel()
        {
                // check to ensure we have a Workbook object
                // before we attempt to close the workbook
                if(null != gwbWorkbook)
                {
                    gwbWorkbook.Close();
                    gwbWorkbook = null;
                }
                // make sure we have actually created
                // the Excel Application object before
                // we Quit
                if(null != gexaApplication)
                {
                    gexaApplication.Quit();
                    gexaApplication = null;
                }
        }

		private function InitialiseExcel()
		{
			var result = null;
			try
			{
				gexaApplication = new ActiveXObject("Excel.Application");
				gexaApplication.Visible = true;
				
				result = gexaApplication;
			}
			catch(exException)
			{
				MessageBox.Show("Error: " + exException.Message + Environment.NewLine + exException.StackTrace);
			}
			return(result);
		}

        // find a specific control
        private function findControl()
        {
            var result = null;
            var objTopControl = goUp(ggrdContentGrid);      // go up the visual tree to somewhere useful

            if(null != objTopControl)   // we found 'somewhere useful' 😉
            {
                result = findControlInner(objTopControl, 0, "labelStatus"); // search for a control with a name "labelStatus"
            }
            else MessageBox.Show("Went up without success");

            return(result);
        }

        // this function goes down the tree looking for a specific object
        private function findControlInner(parent : Object, depth : int, astrControlName : String)
        {
            var objResult = null;

            try
            {
                if(null != parent)
                {
                    // get the type of our object, we do this
                    // so we can check if the object inherits
                    // from a DependencyObject
                    var parentobjType : Type = parent.GetType();
                    if(parentobjType.IsSubclassOf(DependencyObject) == true)
                    {
                        for(var i=0; i < VisualTreeHelper.GetChildrenCount(parent);i++)
                        {
                            // retrieve the child object
                            var current : Object = VisualTreeHelper.GetChild(parent,i);
                            if(null != current)
                            {
                                // here we shall deterine the type of the new object
                                var objType = current.GetType();

                                if(null != objType)
                                {
                                    // we're looking for the Name property, because
                                    // this is what I am interested in
                                    var objPropertyName = objType.GetProperty("Name");
                                    if(null != objPropertyName)
                                    {
                                        var strName = objPropertyName.GetValue(current);
                                        if(null != strName)
                                        {
                                            if(0 == String.Compare(strName, astrControlName))
                                            {
                                                objResult = current;

                                                break;
                                            }

                                            // does the current object have any children?
                                            if(VisualTreeHelper.GetChildrenCount(current) >= 1)
                                            {
                                                // recurse down
                                                objResult = findControlInner(current, depth+1, astrControlName);
                                                if(null != objResult)
                                                {
                                                    break;
                                                }
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }
            catch(ex)
            {
                //debug.WriteLine("!-! Exception: " + ex.Message + " " + ex.StackTrace);
            }

            return(objResult);
        }

        // go up the VisualTree until we get to PART_Window
        private function goUp(aContent : Object)
        {
            var parent : Object = aContent;
            var lastParent : Object = aContent;
            var result : Object = null;

            // here we will loop UP the VisualTree seeking the top
            while(null != (parent = VisualTreeHelper.GetParent(parent)))
            {
                lastParent = parent;

                var objType = lastParent.GetType();
                var objPropertyName = objType.GetProperty("Name");
                var strName : String = objPropertyName.GetValue(lastParent)

                if(null != strName)
                {
                    // PART_ContentPanel
                    if(String.Compare(strName,"PART_Window") == 0)
                    {
                        result = parent;

                        break;
                    }
                }
            }
            return(result);
        }
    }
}

Happy coding! 🙂