Hold on to your seats, today I am going to talk about something that actually has a use.
So our Accounting Manager worked in a company that had modified MoveX extensively and they had some nice functionality. One of these nice things that they could do was import journals from a spreadsheet. Something that he wanted to help streamline some of the functions of his job but without modifications. 😉
So jscripts to the rescue.
The basic premise is that we add a button to GLS100, it is this button that will kick off the import itself. We load the Excel spreadsheet, the Excel spreadsheet determines the FAM Function, the data that gets entered in to the E panel before moving on to GLS120/J1, once we are in GLS120/J1 we loop through the spreadsheet extracting lines and entering it in to Smart Office.
There were a few challenges that needed to be addressed – how does one handle errors? How do we actually present the user with a File Dialog, strangely the WPF dialog doesn’t seem to play well (something I need to investigate a little further). And there were some issues around the handling of dates from Excel and empty cells.
How do we know if there was an error entering a line? I had a good look but couldn’t find any events that I could subscribe to nor any properties, so roll on a nasty hack. You’ll notice at the bottom of the window in the status bar we get the notification of errors. This in turn means that we can take a look at the notification message itself – we just have to find the control. So I needed to enlist some of the code that I wrote a while ago to run through the Visual Tree and find the name of the control. Then at runtime we need to find that control, again, I took some existing code.
So the Status notification control was called labelStatus and it is a Label control. Now that we know what it is called we need to go up the Visual Tree far enough that we can start moving down until we get to the label.
To this end, I have a little function called goUp(), I typically only go up as far as the control PART_Window
// 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);
}
Then we need to go down until we find the control.
// 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);
}
Then after we submit a journal line we check to see if there was an error against the labelStatus, if there is an error we stop any further submissions of lines.
The file prompting dialog was another issue, for some reason the WPF OpenFileDialog didn’t want to present from the jscript, so I had to resort to the System.Windows.Forms.OpenFileDialog()
The next issue to overcome was the way that Excel stores the dates in an odd format – so I couldn’t use the .Value, instead I needed to use Value2 property and then convert it with the DateTime.FromOADate()
So, the spreadsheet in question is as you see below:
Cell L5 is the FAM Function we select from GLS100/B
Cell R5 is the Accounting Date used in GLS100/E
Cell E7 is the Voucher Text used in GLS100/E
Rows 15 to 42 relate to the lines in GLS120/J1.
The code will issue a SELECT when we find the appropriate FAM Function.
We then set the Accounting Date and Voucher Text
And finally we enter each line.
And all of the code to do it…
import System;
import System.Text;
import System.Windows;
import System.Windows.Controls;
import System.Windows.Media;
import System.Windows.Media.Media3D;
import MForms;
import Mango.UI.Core;
import Mango.UI.Core.Util;
import Mango.UI.Services;
import Mango.Services;
import Excel;
import System.Reflection;
package MForms.JScript
{
class GLS100_JournalImport_V00
{
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 = 15; // the starting row in the Spreadsheet
var giMaxRow : int = 42; // the end row in the Spreadsheet
var giCurrentRow : int = 15; // the current row in the Spreadsheet
var gbLookForResponse = false; // should we be looking for a response?
var gobjStatus = null; // the statusbar
var gbRequest : boolean = false; // the request event
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
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);
giicInstanceController.add_RequestCompleted(OnRequestCompleted);
giicInstanceController.add_RequestCompleted(OnRequested);
gbRequest = true;
}
catch(exException)
{
MessageBox.Show("Error: " + exException.Message + Environment.NewLine + exException.StackTrace);
}
}
// 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);
}
// 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);
}
// 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)
{
gwbWorkbook.Saved = true; // get rid of those annoying save messages
var strVoucherType : String = retrieveVoucherType(); // we want to get the voucher type from the spreadsheet (the GLS100 voucher)
if(!String.IsNullOrEmpty(strVoucherType)) // we need to ensure that we have a voucher type
{
giicInstanceController.RenderEngine.SetFocusOnList();
selectFAMFunction(strVoucherType); // now we need to go out and select the function
// from where on out, we start using the events
}
}
else MessageBox.Show("Failed to Open Workbook");
}
else MessageBox.Show("Filename or Excel doesn't exist: " + strFilename);
}
catch(exException)
{
MessageBox.Show("Error: " + exException.description);
}
}
// set the VoucherText within GLS100/E
private function setM3VoucherText(astrVoucherText : String)
{
var tbVoucherText : TextBox = ScriptUtil.FindChild(ggrdContentGrid, "WWGVTX");
if(null != tbVoucherText)
{
tbVoucherText.Text = astrVoucherText;
}
else MessageBox.Show("setM3VoucherText() - Child not found");
}
// set the accounting date within GLS100/E
private function setM3AccountingDate(astrAccountingDate : String)
{
var tbITNO = ScriptUtil.FindChild(ggrdContentGrid, "WWACDT");
if(null != tbITNO)
{
try
{
var dtValue : DateTime = DateTime.FromOADate(Convert.ToDouble(astrAccountingDate));
tbITNO.Value = dtValue;
}
catch(ex)
{
MessageBox.Show(ex.description);
}
}
else MessageBox.Show("Accounting Date not found");
}
// retrieve the voucher text from the spreadsheet
private function retrieveVoucherText()
{
return(gwbWorkbook.ActiveSheet.Range("E7").Value);
}
// retrieve the accounting date from the spreadsheet
// we need to use Value2 in this instance to get
// a value that we can actually use
private function retrieveAccountingDate()
{
return(gwbWorkbook.ActiveSheet.Range("R5").Value2);
}
// retrieve the voucher type from the Spreadsheet
private function retrieveVoucherType()
{
return(gwbWorkbook.ActiveSheet.Range("K5").Value);
}
// GLS100/B set the FAM Function
private function selectFAMFunction(astrFAMFunction : String)
{
var bFound : boolean = false;
if(!String.IsNullOrEmpty(astrFAMFunction))
{
// search through the ListView for the FAM function
for(var iCount : int = 0; iCount < glvListView.Items.Count; iCount++)
{
var itmCurrentItem = glvListView.Items[iCount];
if(null != itmCurrentItem)
{
if(!String.IsNullOrEmpty(itmCurrentItem[0]))
{
var strCurrentString = itmCurrentItem[0].ToString();
if(0 == String.Compare(strCurrentString, astrFAMFunction))
{
glvListView.SelectedItem = itmCurrentItem;
bFound = true;
break;
}
}
}
}
}
if(true == bFound)
{
// ok, we've found the FAM Function on the ListView
// now we need to SELECT it
giicInstanceController.ListOption("1"); // SELECT
}
}
// 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);
}
}
public function OnRequested(sender: Object, e: RequestEventArgs)
{
// we don't really use this at all at the moment
}
public function OnRequestCompleted(sender: Object, e: RequestEventArgs)
{
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
{
if(true == giicInstanceController.RenderEngine.PanelHeader.EndsWith("E")) // are we on panel E?
{
handleEPanel(); // handle panel E
}
else if(true == giicInstanceController.RenderEngine.PanelHeader.EndsWith("J1")) // are we on panel G1 (this should be GLS120/G1)
{
if(null == gobjStatus) // have we found the status control, if not, try to find it
{
gobjStatus = findControl(); // do the actual finding of the control
}
if(null != gobjStatus)
{
// now we should extract the status
var strStatusMessage : String = gobjStatus.Content.ToString();
if(false == String.IsNullOrEmpty(strStatusMessage))
{
giCurrentRow = giMaxRow + 1;
MessageBox.Show("There was an error, cannot continue! '" + gobjStatus.Content.toString() + "'");
}
}
else
{
MessageBox.Show("Couldn't find the StatusBar");
}
handleJ1Panel(); // handle panel j
}
}
}
else if(e.CommandType == MNEProtocol.CommandTypeListOption)
{
if(e.CommandValue == MNEProtocol.OptionSelect)
{
handleEPanel();
}
}
}
// this is where we do the actual handling of the J1 Panel
private function handleJ1Panel()
{
if(giCurrentRow <= giMaxRow) // the spreadsheet has a limited number of rows...
{
// extract the lines from the spreadsheet
var strWXAIT1 : String = retrieveFromActiveSheet("B" + giCurrentRow);
var strWXAIT2 : String = retrieveFromActiveSheet("C" + giCurrentRow);
var strWXAIT3 : String = retrieveFromActiveSheet("D" + giCurrentRow);
var strWXAIT4 : String = retrieveFromActiveSheet("E" + giCurrentRow);
var strWXAIT5 : String = retrieveFromActiveSheet("F" + giCurrentRow);
var strWXAIT6 : String = retrieveFromActiveSheet("G" + giCurrentRow);
var strWWCUAMDebit : String = retrieveFromActiveSheet("I" + giCurrentRow);
var strWWCUAMCredit : String = retrieveFromActiveSheet("L" + giCurrentRow);
var strWWVTXT : String = retrieveFromActiveSheet("O" + giCurrentRow);
var strWWVTCD : String = retrieveFromActiveSheet("N" + giCurrentRow);
// this is the current row
giCurrentRow = giCurrentRow + 1;
if(!String.IsNullOrEmpty(strWXAIT1))
{
if(0 != String.Compare(strWXAIT1,"undefined")) // verify that we actually have content
{
setM3TextField("WXAIT1", strWXAIT1); // account
setM3TextField("WXAIT2", strWXAIT2); // Dept
setM3TextField("WXAIT3", strWXAIT3); // Dim3
setM3TextField("WXAIT4", strWXAIT4); // Dim4
setM3TextField("WXAIT5", strWXAIT5); // Dim5
setM3TextField("WXAIT6", strWXAIT6); // Dim6
//setM3TextField("", retrieveFromActiveSheet("H" + giCurrentRow)); // Division
if(!String.IsNullOrEmpty(strWWCUAMDebit))
{
setM3TextField("WWCUAM", "-" + strWWCUAMDebit); // Value
}
else if(!String.IsNullOrEmpty(strWWCUAMCredit))
{
setM3TextField("WWCUAM", strWWCUAMCredit); // Value
}
setM3TextField("WWVTXT", strWWVTXT); // Voucher Text
setM3TextField("WWVTCD", strWWVTCD); // VAT type
giicInstanceController.PressKey("ENTER"); // press the enter key
}
}
}
else
{
if(true == giicInstanceController.RenderEngine.PanelHeader.EndsWith("J1"))
{
giicInstanceController.remove_RequestCompleted(OnRequestCompleted);
giicInstanceController.remove_RequestCompleted(OnRequested);
gbRequest = false;
}
// check to ensure we have a Workbook object
// before we attempt to close the workbook
if(null != gwbWorkbook)
{
gwbWorkbook.Close(false);
gwbWorkbook = null;
}
// make sure we have actually created
// the Excel Application object before
// we Quit
if(null != gexaApplication)
{
gexaApplication.Quit();
gexaApplication = null;
}
}
}
// 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: " + tbTextBox.Text);
}
// 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);
}
// handle the E Panel
private function handleEPanel()
{
var strAccountingDate : String = retrieveAccountingDate(); // retrieve the accounting date from the Spreadsheet
var strVoucherText : String = retrieveVoucherText(); // retroeve the voucher text from the Spreadsheet
if((!String.IsNullOrEmpty(strAccountingDate)) && (!String.IsNullOrEmpty(strVoucherText)))
{
setM3AccountingDate(strAccountingDate); // now we actually set the accounting date in the TextBox
setM3VoucherText(strVoucherText); // and the Voucher TextBox
giicInstanceController.PressKey("ENTER"); // now we press enter - this will fire off a Request event and should take us to GLS120/G1
}
else MessageBox.Show("We require an Account Date and Voucher Text");
}
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);
}
//
}
}
Have fun coding!
Potato IT 