So, I was wondering if it was possible to read values off an Excel spreadsheet and use those values to create an order and add an order line. In short – but of-course! 🙂

Though Smart Office provides functionality natively through mforms::ExcelUtilities, I thought it would be interesting to use a more traditional approach to illustrate how you can use other external programs without Lawson providing direct functionality – for example, you could use Microsoft Word to record all the values for an item in MMS001 for the purposes of documentation.

Though I haven’t played around with it, I’m assuming that this would be a good time to use automation – but I haven’t read that far through the pdf 😉

So the basic premise of what I am going to demonstrate is

1. From OIS100
2. Open a Spreadsheet
   1. Read some of the values
3. Subscribe to the RequestCompleted event
4. Do a PressKey
5. The PressKey will fire the RequestCompleted event once we move on the next panel
   1. PressKey until we get to the Order Line (which will generate a new RequestCompleted event)
6. Write some of the values to the order lines that we extracted from the Spreadsheet

We use the RequestCompleted, as we need to wait until the first event is complete before moving on to the next event. Doing the PressKeys one after the other won’t achieve the results that we want.

The sample spreadsheet which I saved as D:\Test.xlsx, you’ll see that in the A column I have two field names, I have done this purely for the purposes of demonstrating that we can extract the field name and the values from the spreadsheet and it made it easier to muck around using different fields without changing the scripts 🙂

The script below doesn’t add a button, it just runs and creates an order for customer 165, with an order type of D01. We have a panel sequence of EFGHB1, and we have an extra confirm on panel A.

We then set the Item Number and the quantity before entering.

Anyways, lots of comments in the code – hopefully it all makes sense and still works when you deploy it 😀

import System;
import System.Windows;
import System.Windows.Controls;
import MForms;

import Excel;
import System.Reflection;

package MForms.JScript
{
   class ExcelTest
   {
   		// cache some of the variables
   		var gcontroller;
   		var gdebug;
   		var gcontent;
   		
   		var giStep;

		// where we will store the Item Number
   		var gITNO;
   		// Quantity of the order
   		var gQty;
   		
   		// has the RequestCompleted event been removed?
   		var bRemoved;
   		
      public function Init(element: Object, args: Object, controller : Object, debug : Object)
      {
         var content : Object = controller.RenderEngine.Content;

         gdebug = debug;
         gcontroller = controller;
         gcontent = content;
         
         // subscribe to the RequestCompleted event
         controller.add_RequestCompleted(OnRequestCompleted);
			bRemoved = false;
         try
         {
         		// we want to create an Excel App
	         var exExcel = new ActiveXObject("Excel.Application");
	         if(null != exExcel)
	         {
	         		// for the purposes of demonstrating, we'll show
	         		// Excel - we do get better performance if it is
	         		// hidden
	         		exExcel.Visible = true;
	         		
	         		// load the spreadsheet which we will use to submit
	         		// the information
	         		var wbBook = exExcel.Workbooks.Open("D:\Test.xlsx");
	
	         		if(null != wbBook)
	         		{
	         			// Cell B1 - this is the customer
	         			var strCUNO = wbBook.Worksheets(1).Cells(1,2).Value;
	         			// Cell B2 - this is the order type
	         			var strORTP = wbBook.Worksheets(1).Cells(2,2).Value;

						// Just for the purposes of because we can...
						// we are storing the field names in column A1 and A2
						// CUNO, Customer Number field name
	         			var strFNCUNO = wbBook.Worksheets(1).Cells(1,1).Value;
	         			// ORTP, Order Type field name
	         			var strFNORTP = wbBook.Worksheets(1).Cells(2,1).Value;
	         			
	         			// go out and retrieve the Customer Number TextBox control
	         			var tbCUNO = ScriptUtil.FindChild(content, strFNCUNO);
	         			// go out and retrieve the Order Type TextBox control
	         			var tbORTP = ScriptUtil.FindChild(content, strFNORTP);
	         			
	         			if(null != tbCUNO)
	         			{
	         				// set the TextBox Customer Number value
	         				tbCUNO.Text = strCUNO;
	         			}
	         			
	         			if(null != tbORTP)
	         			{
	         				// set the order type text box
	         				tbORTP.Text = strORTP;
	         			}
	         			
	         			// we're also going to demonstrate adding an order line, only one so
	         			// we'll cache it globally
	         			// get the Item Number
	         			gITNO = wbBook.Worksheets(1).Cells(4,1).Value;
	         			// get the Quantity
	         			gQty = wbBook.Worksheets(1).Cells(4,2).Value;
	         			
	         			// now we are going to press enter
	         			controller.PressKey("ENTER");
	         			giStep = 0;

	         			// close the Workbook
	         			wbBook.Close();
	         		}
	         		// Quit Excel
	         		exExcel.Quit();
	         }
	      }
         catch(ex)
         {
         }
      }
      
      public function OnRequestCompleted(sender: Object, e: RequestEventArgs)
      {
      		if((e.CommandType == MNEProtocol.CommandTypeKey) && (e.CommandValue == MNEProtocol.KeyEnter))
      		{
      			// we already know the panel sequence, so we won't bother checking it
      			// if it is likely to change you should look it up on the A panel
      			
				if(true == gcontroller.RenderEngine.PanelHeader.EndsWith("A"))
				{
					// if panel a, then we just want to enter through it, we have nothing useful to add
					gcontroller.PressKey("ENTER");
				}
      			else if(true == gcontroller.RenderEngine.PanelHeader.EndsWith("E"))
      			{
      				// if panel E, then we just want to enter through it, we have nothing useful to add
      				gcontroller.PressKey("ENTER");
      			}
      			else if(true == gcontroller.RenderEngine.PanelHeader.EndsWith("F"))
      			{
      				// if panel F, then we just want to enter through it, we have nothing useful to add
      				gcontroller.PressKey("ENTER");
      			}
      			else if(true == gcontroller.RenderEngine.PanelHeader.EndsWith("G"))
      			{
      				// if panel G, then we just want to enter through it, we have nothing useful to add
      				gcontroller.PressKey("ENTER");
      			}
      			else if(true == gcontroller.RenderEngine.PanelHeader.EndsWith("H"))
      			{
      				// if panel H, then we just want to enter through it, we have nothing useful to add
      				gcontroller.PressKey("ENTER");
      			}
      			else if(true == gcontroller.RenderEngine.PanelHeader.EndsWith("B1"))
      			{
      				// if panel B1, this where we enter the order lines
      				if(giStep == 0)	// we'll just make sure we only execute it once
      				{
      					// from the order line, we will enter the Item Number and Quantity
      					// so we retrieve the TextBoxes from the panel
	         			var tbITNO = ScriptUtil.FindChild(gcontent, "WBITNO");
	         			var tbQty = ScriptUtil.FindChild(gcontent, "WBORQA");
	         			
	         			// Fill in the Item Number TextBox
	         			tbITNO.Text = gITNO;
	         			// Fill in the Quantity TextBox
	         			tbQty.Text = gQty;
	         			
	         			// Press Enter to add the line
	         			gcontroller.PressKey("ENTER");
	         			// we change the giStep so we don't loop through this
	         			giStep = 1;
         			}
         			// now we'll remove the RequestCompleted Event
         			gcontroller.remove_RequestCompleted(OnRequestCompleted);
         			bRemoved = true;
      			}
      		}
      		if(true != bRemoved)
      		{
      			gcontroller.remove_RequestCompleted(OnRequestCompleted);
      		}
      }
   }
}

Have fun!