After spending years looking at the M3 APIs in disdain and grudgingly using them, it looks like I’ve finally been converted.

Over the past 12 months due to a variety of projects for different people I’ve relied upon them more and more and found them to be a lot better than my early encounters. And being able to call them directly within Smart Office 10.x without needing to authenticate has been a real boon.

Now those of you that have long memories will recall that I created a script which added a button to BUS100 which would read a spreadsheet and import the data in to BUS100 budgets, we do this as the manual entry is horrible, especially if you have different monthly amounts during the year. Due to the way the BUS100 worked I couldn’t use the same method I used for the GLS100 imports so I resorted to use the APIs wrapped in a webservice. It worked well for IFL…

Roll on 2014 and Upgrade-X (more of this in another post), and I needed to uplift the webservices. The webservices I originally created were pre-grid and were going to need some tweaking along with some other non-technical challenges I figured I’d spend the time converting the code to use the APIs instead. The structure of the script made it fairly trivial and now means that it’s easier for others to use and when further upgrades are done I don’t need to worry about messing around with webservices.

First off, here is the spreadsheet template, it should be fairly self-evident how to use it…

https://drive.google.com/file/d/0Bxi1gJ1tQyDRdDN4SE8zcExZOTQ/view?usp=sharing

And now the updated script…(I was facing time pressure so it’s not terribly pretty but it is functional)

//
// 20121214 v005    - changed the division so it is no longer statically set
// 20121214 v007    - the name and description of the budget were being ignored and set as TEST
// 20141122 v008	- convert to UpgradeX
//
import System;
import System.IO;
import System.Net;
import System.Windows;
import System.Windows.Controls;
import MForms;

import System.Web.Services.Protocols;
import System.Xml;

import Lawson.M3.MI;
import Mango.UI;

import System.Text;
import Excel;
import System.Reflection;

package MForms.JScript
{
	class BUS100_BudgetImport_008
	{
		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 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_B1 : int = 7;        // the row we will start from

		var gstrCompany : String = null;
		var gstrDivision : String = null;
		var gstrBudgetNumber : String = null;
		var gstrBudgetVersion : String = null;

		//var gstrUsername : String = null;           // the username that we will use to log in to the WebServices
		//var gstrPassword : String = null;           // the password that we will use to log in to the WebServices
		
		//var gstrBaseURI : String = null;			// this is the URI to the webservice

		var btnImportBudget : Button = null;

		var wndPasswordPrompt : Window = null;      // this is the window we will display requesting the username and password

		var gdebug;

		public function Init(element: Object, args: Object, controller : Object, debug : Object)
		{
			gdebug = debug;
			var content : Object = controller.RenderEngine.Content;

			// add a button to retrieve the free caps of an item
			btnImportBudget = new Button();
			// the button will display "FreeCap"
			btnImportBudget.Content = "Import";

			// set the position of the button
			Grid.SetColumnSpan(btnImportBudget, 10);
			Grid.SetColumn(btnImportBudget, 11);
			Grid.SetRow(btnImportBudget, 0);

			// actually add the button to the panel
			content.Children.Add(btnImportBudget);

			// we want to know about the click and unloaded events, so we register our interest here
			btnImportBudget.add_Click(OnbtnImportBudgetClick);
			btnImportBudget.add_Unloaded(OnbtnImportBudgetClickUnloaded);
		}


		public function OnbtnImportBudgetClickUnloaded(sender: Object, e: RoutedEventArgs)
		{
			// remove the events that we are subscribed to
			btnImportBudget.remove_Click(OnbtnImportBudgetClick);
			btnImportBudget.remove_Unloaded(OnbtnImportBudgetClickUnloaded);
		}

		// this will submit the header to the webservice
		// we statically set the Company & Division in our case
		private function handleHeader()
		{
			// "WETX40" - Description
			// "WETX15" - Name
			// "WEBSPR" - Start Period
			// "WECRTP" - Exchange Rate
			// "WENPAM" - Number of Periods
			// "WEUPDB" - Update Balance File (Checkbox)
			//gstrUsername = retrieveFromActiveSheet("B1");           // retrieve the username from the spreadsheet
			//gstrPassword = retrieveFromActiveSheet("B2");           // retrieve the password from the spreadsheet

			var w1buno : String = retrieveFromActiveSheet(gwbWorkbook, "B3");    // budget number
			var w1bver : String = retrieveFromActiveSheet(gwbWorkbook, "D3");    // version number

			gstrCompany = "100";                                    // statically set our company for us
			// gstrDivision = "IFL";                                   // statically set out division
			gstrDivision = retrieveFromActiveSheet(gwbWorkbook, "F3");    // Division
			gstrBudgetNumber = w1buno;
			gstrBudgetVersion = w1bver;


			var wetx40 : String = retrieveFromActiveSheet(gwbWorkbook, "B4");    // description
			var wetx15 : String = retrieveFromActiveSheet(gwbWorkbook, "B4");    // name
			var webspr : String = retrieveFromActiveSheet(gwbWorkbook, "D4");    // start period
			// var wecrtp : String = "1"; //retrieveFromActiveSheet("B3");
			var wecrtp : String = retrieveFromActiveSheet(gwbWorkbook, "K4");    // exchange rate tp
			var wenpam : String = retrieveFromActiveSheet(gwbWorkbook, "G4");    // number of periods
			// var weupdb : String = retrieveFromActiveSheet("B3");
			var weupdb : String = retrieveFromActiveSheet(gwbWorkbook, "I4");    // should we update the budget
			var strUpdate : String = "1";

			// check to ensure a value for the update budget
			if(true == doWeHaveAValueFromSpreadsheet(weupdb))
			{
				if(0 == String.Compare(weupdb, "true", true))
				{
					strUpdate = "1";
				}
				else if(0 == String.Compare(weupdb, "yes", true))
				{
					strUpdate = "0";
				}
			}
			
			if(wetx15.length > 15)
			{
				ConfirmDialog.ShowWarningDialog("Warning", "Budget name is > 15 characters - it will be truncated");
				wetx15 = wetx15.substring(0,15);
			}
			
			
			var mirRequest = new MIRequest();
			// we only want to filter on the company
			var mirInRecord = new MIRecord();
			mirInRecord["CONO"] = gstrCompany;
			mirInRecord["DIVI"] = gstrDivision;
			mirInRecord["BUNO"] = gstrBudgetNumber;	// budget number
			mirInRecord["BVER"] = gstrBudgetVersion;	// budget version
			mirInRecord["TX40"] = wetx40;	// Description
			mirInRecord["TX15"] = wetx15;	// Name
			mirInRecord["BSPR"] = webspr;	// start period budget
			mirInRecord["CRTP"] = wecrtp;	// exchange rate type
			mirInRecord["NPAM"] = wenpam;	// number periods
			//mirInRecord["ROPP"] = UserContext.CurrentCompany;	// rounding off category
			mirInRecord["UPDB"] = strUpdate;	// Update Balance File
			//mirInRecord["DTMP"] = UserContext.CurrentCompany;	// Allocaton Template
			//mirInRecord["ACGR"] = UserContext.CurrentCompany;	// Object Access Group

			mirRequest.Program = "BUS100MI";
			mirRequest.Record = mirInRecord;
			mirRequest.Transaction = "AddBudgetHeader";
			mirRequest.Tag = gwbWorkbook;
			
			MIWorker.Run(mirRequest, addBudgetHeader_OnComplete);
			
			// handleLines();
		}

		
		private function addBudgetHeader_OnComplete(amirResponse : MIResponse)
		{
			gdebug.Debug("addBudgetHeader_OnComplete start");
			if((null != amirResponse) && (false == amirResponse.HasError))
			{
				handleLines(amirResponse.Tag);
			}
			else
			{
				if(null == amirResponse)
				{	
					ConfirmDialog.ShowErrorDialog("Error", "No response from the APIs");
				}
				else
				{
					ConfirmDialog.ShowErrorDialog("Error", "Error returned from the APIs: " + amirResponse.ErrorCode + " " + amirResponse.ErrorMessage);
				}
				CleanUp();
			}
			gdebug.Debug("addBudgetHeader_OnComplete end");
		}
		
		// this function will go through the spreadsheet looking for values to submit
		// to the SOAP interface
		private function handleLines(workbook)
		{
			gdebug.Debug("handleLines start");
			
			var argumentArray = new Array();
			var iArrayPosition = 0;
			gdebug.Debug("handleLines MIMultWorker created");
			
			while(giCurrentRowBUS101_B1 <= giMaxRow)    // we will loop through until we hit MaxRows
			{
				gdebug.Debug("Dimension 1, " + "A" + giCurrentRowBUS101_B1);
				var w1ait1 : String = retrieveFromActiveSheet(workbook, "A" + giCurrentRowBUS101_B1);     // retrieve the first dimension from the spreadsheet
				gdebug.Debug("Dimension 1, " + "A" + giCurrentRowBUS101_B1 + " = '" + w1ait1 + "'");

				if(true == doWeHaveAValueFromSpreadsheet(w1ait1))   // if the dimension doesn't exist then there isn't any point retrieving any other dimensions
				{
					if(0 == String.Compare("End", w1ait1, true))    // if we come across End we should break from our line loop
					{
						break;
					}

					var w1ait2 : String = retrieveFromActiveSheet(workbook, "B" + giCurrentRowBUS101_B1);     // dimension 1
					var w1ait3 : String = retrieveFromActiveSheet(workbook, "C" + giCurrentRowBUS101_B1);     // dimension 2
					var w1ait4 : String = retrieveFromActiveSheet(workbook, "D" + giCurrentRowBUS101_B1);     // dimension 4
					var w1ait5 : String = retrieveFromActiveSheet(workbook, "E" + giCurrentRowBUS101_B1);     // dimension 5
					var w1ait6 : String = retrieveFromActiveSheet(workbook, "F" + giCurrentRowBUS101_B1);     // dimension 6
					var w1ait7 : String = retrieveFromActiveSheet(workbook, "G" + giCurrentRowBUS101_B1);     // dimension 7
					var w1cucd : String = retrieveFromActiveSheet(workbook, "H" + giCurrentRowBUS101_B1);     // 
					var w1amtn : String = retrieveFromActiveSheet(workbook, "I" + giCurrentRowBUS101_B1);     // 

					var strPeriod1 : String = retrieveFromActiveSheet(workbook, "J" + giCurrentRowBUS101_B1);     // value for period 1
					var strPeriod2 : String = retrieveFromActiveSheet(workbook, "K" + giCurrentRowBUS101_B1);     // value for period 2
					var strPeriod3 : String = retrieveFromActiveSheet(workbook, "L" + giCurrentRowBUS101_B1);     // value for period 3
					var strPeriod4 : String = retrieveFromActiveSheet(workbook, "M" + giCurrentRowBUS101_B1);     // value for period 4
					var strPeriod5 : String = retrieveFromActiveSheet(workbook, "N" + giCurrentRowBUS101_B1);     // value for period 5
					var strPeriod6 : String = retrieveFromActiveSheet(workbook, "O" + giCurrentRowBUS101_B1);     // value for period 6
					var strPeriod7 : String = retrieveFromActiveSheet(workbook, "P" + giCurrentRowBUS101_B1);     // value for period 7
					var strPeriod8 : String = retrieveFromActiveSheet(workbook, "Q" + giCurrentRowBUS101_B1);     // value for period 8
					var strPeriod9 : String = retrieveFromActiveSheet(workbook, "R" + giCurrentRowBUS101_B1);     // value for period 9
					var strPeriod10 : String = retrieveFromActiveSheet(workbook, "S" + giCurrentRowBUS101_B1);    // value for period 10
					var strPeriod11 : String = retrieveFromActiveSheet(workbook, "T" + giCurrentRowBUS101_B1);    // value for period 11
					var strPeriod12 : String = retrieveFromActiveSheet(workbook, "U" + giCurrentRowBUS101_B1);    // value for period 12

					
					var mirRequest : MIRequest = new MIRequest();
					var mirInRecord : MIRecord = new MIRecord();
					mirInRecord["CONO"] = gstrCompany;
					mirInRecord["DIVI"] = gstrDivision;
					mirInRecord["BUNO"] = gstrBudgetNumber;
					mirInRecord["BVER"] = gstrBudgetVersion;
					mirInRecord["AIT1"] = w1ait1;
					mirInRecord["AIT2"] = w1ait2;
					mirInRecord["AIT3"] = w1ait3;
					mirInRecord["AIT4"] = w1ait4;
					mirInRecord["AIT5"] = w1ait5;
					mirInRecord["AIT6"] = w1ait6;
					mirInRecord["AIT7"] = w1ait7;
					mirInRecord["CUCD"] = w1cucd;
					mirInRecord["BCU1"] = convertTo2DecimalPlaces(strPeriod1);
					mirInRecord["BCU2"] = convertTo2DecimalPlaces(strPeriod2);
					mirInRecord["BCU3"] = convertTo2DecimalPlaces(strPeriod3);
					mirInRecord["BCU4"] = convertTo2DecimalPlaces(strPeriod4);
					mirInRecord["BCU5"] = convertTo2DecimalPlaces(strPeriod5);
					mirInRecord["BCU6"] = convertTo2DecimalPlaces(strPeriod6);
					mirInRecord["BCU7"] = convertTo2DecimalPlaces(strPeriod7);
					mirInRecord["BCU8"] = convertTo2DecimalPlaces(strPeriod8);
					mirInRecord["BCU9"] = convertTo2DecimalPlaces(strPeriod9);
					mirInRecord["BC10"] = convertTo2DecimalPlaces(strPeriod10);
					mirInRecord["BC11"] = convertTo2DecimalPlaces(strPeriod11);
					mirInRecord["BC12"] = convertTo2DecimalPlaces(strPeriod12);

					mirRequest.Program = "BUS100MI";
					mirRequest.Record = mirInRecord;
					mirRequest.Transaction = "AddBudgetLines";
					// mirRequest.Tag = workbook;
					argumentArray[iArrayPosition] = mirRequest;
					iArrayPosition++;
				}

				giCurrentRowBUS101_B1++;        // increment our spreadsheet position
			}
			
			gdebug.Debug("iArrayPosition = " + iArrayPosition);
			gdebug.Debug("giCurrentRowBUS101_B1 = " + giCurrentRowBUS101_B1);
			
			if(argumentArray.length > 0)
			{
				gdebug.Debug("Argument Array length = " + argumentArray.length);
				
				var mirRequestArray = new MIRequest[argumentArray.length];
				for(var i = 0; i < argumentArray.length; i++)
				{
					mirRequestArray[i] = argumentArray[i];
				}
				addBudgetLines(mirRequestArray);
				//mwWorker.RunWorkerAsync(req, addBudgetLines_OnComplete);
			}
			
			gdebug.Debug("handleLines end");
			
		}

		private function addBudgetLines(req)
		{
			if(null != req)
			{
				gdebug.Debug("req count: " + req.length);
				var mwWorker = new MIMultiWorker();
				mwWorker.RunWorkerAsync(req, addBudgetLines_OnComplete);
			}
		}
		
		private function addBudgetLines_OnComplete(amirResponse : MIMultiResult)
		{
			try
			{
				if(null != amirResponse && amirResponse.HasError == false)
				{
					gdebug.Debug("No errors");
					ConfirmDialog.ShowInformationDialogNeverHidden("Complete", "Completed without any errors");
				}
				else
				{
					gdebug.Debug("Errors!");
					if(null != amirResponse.ResponseList && amirResponse.ResponseList.Count > 0)
					{
						var strErrors : String = null;
						for(var i = 0; i < amirResponse.ResponseList.Count; i++)
						{
							var reponse : MIResponse = amirResponse.ResponseList[i];
							if(true == reponse.HasError)
							{
								if(false == String.IsNullOrEmpty(strErrors))
								{
									strErrors += Environment.NewLine;
								}
								strErrors += "Response[" + i + "]: " + reponse.Error;
							}
						}
						if(false == String.IsNullOrEmpty(strErrors))
						{
							ConfirmDialog.ShowErrorDialog("Error", "The following errors were encountered" + Environment.NewLine + strErrors);
						}
						else
						{
							ConfirmDialog.ShowErrorDialog("Error", "Undetermined errors occured creating the budget lines, please check the Smart Office logs");
						}
					}
					else
					{
						ConfirmDialog.ShowErrorDialog("Error", "Undetermined errors occured creating the budget lines");
					}
				}
			
				try
				{
					gwbWorkbook.Save();                     // save the spreadsheet
				}
				catch(ext)
				{
				}
				
				CleanUp();

			}
			catch(ex)
			{
				ConfirmDialog.ShowErrorDialog("Exception", "An Exception occurred, please check the import" + Environment.NewLine + ex);
			}
		}
		
		public function OnbtnImportBudgetClick(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)
					{
						gwbWorkbook.Saved = true;               // get rid of those annoying save messages

						giMaxRow = gwbWorkbook.ActiveSheet.Cells(gwbWorkbook.ActiveSheet.Rows.Count,1).End(-4162).Row;      // get the end row
						
						handleHeader();                         // kick off the creation of the header, this in turn will create the lines
						//gwbWorkbook.Save();                     // save the spreadsheet

					}
					else ConfirmDialog.ShowErrorDialog("Error", "Failed to Open Workbook '" + strFilename + "'");
				}
				else ConfirmDialog.ShowErrorDialog("Error", "Filename or Excel doesn't exist: " + strFilename);
			}
			catch(exException)
			{
				ConfirmDialog.ShowErrorDialog("Error", "Error: " + exException.description);
			}
			
		}




		// retrieve some data from the active spreadsheet
		// at a specific location
		private function retrieveFromActiveSheet(awbWorkBook, astrPosition)
		{
			//gdebug.Debug("retrieveFromActiveSheet() start: " + astrPosition);
			var result = "";
			
			if(null != awbWorkBook)
			{
				if(null != awbWorkBook.ActiveSheet)
				{
					result = awbWorkBook.ActiveSheet.Range(astrPosition).Value;
					if(true == String.IsNullOrEmpty(result))
					{
						result = "";
					}
					else if(0 == String.Compare(result, "undefined"))
					{
						result = "";
					}
				}
				else
				{
					gdebug.Error("No active worksheet");
				}
			}
			else
			{
				gdebug.Error("Workbook is null");
			}
			

			return(result);
		}

		// 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);
		}

		// our central cleanup function
		private function CleanUp()
		{
			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 != btnImportBudget)
			{
				btnImportBudget.remove_Click(OnbtnImportBudgetClick);
				btnImportBudget.remove_Unloaded(OnbtnImportBudgetClickUnloaded);
			}
		}

		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);
		}

		// close Excel and any workbooks
		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;
				}
		}

		// Initialise Excel, essentially start the Excel Application and set it to visible
		private function InitialiseExcel()
		{
			var result = null;
			try
			{
				gexaApplication = new ActiveXObject("Excel.Application");
				gexaApplication.Visible = true;
				
				result = gexaApplication;
			}
			catch(exException)
			{
				ConfirmDialog.ShowErrorDialog("Error", "Error: " + exException.Message + Environment.NewLine + exException.StackTrace);
			}
			return(result);
		}

		
		
	}
}

Enjoy! 🙂