Ok, so I posted some code about submitting importing budgets last week. And now we have some improvements.
Since last week I have had some time to think about the script and some of the improvements. I didn’t really like the fact that the username and password were stored in the spreadsheet, so I’ve added code which will create a WPF Window which will prompt the user for a username and password.
Over and above this I now have a method that will determine the number of rows that we need to go down to rather than using a keyword such as End.
And last but not least we will write an OK or an exception per line that we submit in volume V on the spreadsheet so the user can easily determine which submissions haven’t worked correctly.
So to start with…
// construct and display a window to ask a user for a username and password
// which we will use for the web services
private function passwordPrompt() : boolean
{
var bResult : boolean = false;
wndPasswordPrompt = new Window();
if(null != wndPasswordPrompt)
{
var spVertical : StackPanel = new StackPanel();
var spHorizontal1 : StackPanel = new StackPanel();
var spHorizontal2 : StackPanel = new StackPanel();
var spHorizontal3 : StackPanel = new StackPanel();
var lbUsername : Label = new Label();
var lbPassword : Label = new Label();
var tbUsername : TextBox = new TextBox();
var pbPassword : PasswordBox = new PasswordBox();
var btnOk : Button = new Button();
var btnCancel : Button = new Button();
wndPasswordPrompt.Title = "Please enter you username and password";
wndPasswordPrompt.Width = 280;
wndPasswordPrompt.Height = 150;
lbUsername.Content = "Username:";
lbPassword.Content = "Password:";
lbUsername.Width = 80;
lbPassword.Width = 80;
tbUsername.Width = 150;
pbPassword.Width = 150;
tbUsername.Margin = new Thickness(2);
pbPassword.Margin = new Thickness(2);
tbUsername.Name = "tbUsername";
pbPassword.Name = "pbPassword";
btnOk.Content = "Ok";
btnCancel.Content = "Cancel";
btnOk.IsDefault = true;
btnOk.Margin = new Thickness(10);
btnCancel.IsCancel = true;
btnCancel.Margin = new Thickness(10);
spHorizontal1.Children.Add(lbUsername);
spHorizontal1.Children.Add(tbUsername);
spHorizontal2.Children.Add(lbPassword);
spHorizontal2.Children.Add(pbPassword);
spHorizontal3.Children.Add(btnOk);
spHorizontal3.Children.Add(btnCancel);
spHorizontal3.HorizontalAlignment = System.Windows.HorizontalAlignment.Stretch;
spVertical.Margin = new Thickness(5);
spVertical.Orientation = System.Windows.Controls.Orientation.Vertical;
spHorizontal1.Orientation = System.Windows.Controls.Orientation.Horizontal;
spHorizontal2.Orientation = System.Windows.Controls.Orientation.Horizontal;
spHorizontal3.Orientation = System.Windows.Controls.Orientation.Horizontal;
wndPasswordPrompt.Content = spVertical;
spVertical.Children.Add(spHorizontal1);
spVertical.Children.Add(spHorizontal2);
spVertical.Children.Add(spHorizontal3);
btnOk.add_Click(OnbtnDialogOKClick);
btnCancel.add_Click(OnbtnDialogCancelClick);
if(true == (bResult = wndPasswordPrompt.ShowDialog()))
{
gstrUsername = tbUsername.Text;
gstrPassword = pbPassword.Password;
}
else
{
gstrUsername = null;
gstrPassword = null;
}
// remove the OK and cancel events
btnOk.remove_Click(OnbtnDialogOKClick);
btnCancel.remove_Click(OnbtnDialogCancelClick);
wndPasswordPrompt = null;
}
return(bResult);
}
public function OnbtnDialogOKClick(sender: Object, e: RoutedEventArgs)
{
wndPasswordPrompt.DialogResult = true;
wndPasswordPrompt.Close();
}
public function OnbtnDialogCancelClick(sender: Object, e: RoutedEventArgs)
{
wndPasswordPrompt.Close();
}
The passwordPrompt() method will create a Window, in that window we add a vertical StackPanel which has three horizontal StackPanels, we then have a label and TextBox, then a label and PasswordBox before finally adding an OK and Cancel button. We need to also had the events for the OK and Cancel.
Then we display the window.
Determining the last row of the spreadsheet is the next enhancement.
giMaxRow = gwbWorkbook.ActiveSheet.Cells(gwbWorkbook.ActiveSheet.Rows.Count,1).End(-4162).Row;
This will basically go to the bottom most cell in column A and then go up until it encounters a non-empty cell and we’ll record that bottom cell. We will then only scan down to that cell. We use this method rather than using the End keyword.
Anyways, the code…
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 System.Text;
import Excel;
import System.Reflection;
package MForms.JScript
{
class BUS100_BudgetImport_002
{
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 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("B3"); // budget number
var w1bver : String = retrieveFromActiveSheet("D3"); // version number
gstrCompany = "100"; // statically set our company for us
gstrDivision = "IFL"; // statically set out division
gstrBudgetNumber = w1buno;
gstrBudgetVersion = w1bver;
var wetx40 : String = retrieveFromActiveSheet("B4"); // description
var wetx15 : String = retrieveFromActiveSheet("B4"); // name
var webspr : String = retrieveFromActiveSheet("D4"); // start period
// var wecrtp : String = "1"; //retrieveFromActiveSheet("B3");
var wecrtp : String = retrieveFromActiveSheet("K4"); // exchange rate tp
var wenpam : String = retrieveFromActiveSheet("G4"); // number of periods
// var weupdb : String = retrieveFromActiveSheet("B3");
var weupdb : String = retrieveFromActiveSheet("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";
}
}
doRequest(addBudgetHeader(wetx40, wetx15, webspr, wecrtp, wenpam, strUpdate)); // send the request
handleLines();
}
// this function will go through the spreadsheet looking for values to submit
// to the SOAP interface
private function handleLines()
{
while(giCurrentRowBUS101_B1 <= giMaxRow) // we will loop through until we hit MaxRows
{
var w1ait1 : String = retrieveFromActiveSheet("A" + giCurrentRowBUS101_B1); // retrieve the first dimension from the spreadsheet
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("B" + giCurrentRowBUS101_B1); // dimension 1
var w1ait3 : String = retrieveFromActiveSheet("C" + giCurrentRowBUS101_B1); // dimension 2
var w1ait4 : String = retrieveFromActiveSheet("D" + giCurrentRowBUS101_B1); // dimension 4
var w1ait5 : String = retrieveFromActiveSheet("E" + giCurrentRowBUS101_B1); // dimension 5
var w1ait6 : String = retrieveFromActiveSheet("F" + giCurrentRowBUS101_B1); // dimension 6
var w1ait7 : String = retrieveFromActiveSheet("G" + giCurrentRowBUS101_B1); // dimension 7
var w1cucd : String = retrieveFromActiveSheet("H" + giCurrentRowBUS101_B1); //
var w1amtn : String = retrieveFromActiveSheet("I" + giCurrentRowBUS101_B1); //
var strPeriod1 : String = retrieveFromActiveSheet("J" + giCurrentRowBUS101_B1); // value for period 1
var strPeriod2 : String = retrieveFromActiveSheet("K" + giCurrentRowBUS101_B1); // value for period 2
var strPeriod3 : String = retrieveFromActiveSheet("L" + giCurrentRowBUS101_B1); // value for period 3
var strPeriod4 : String = retrieveFromActiveSheet("M" + giCurrentRowBUS101_B1); // value for period 4
var strPeriod5 : String = retrieveFromActiveSheet("N" + giCurrentRowBUS101_B1); // value for period 5
var strPeriod6 : String = retrieveFromActiveSheet("O" + giCurrentRowBUS101_B1); // value for period 6
var strPeriod7 : String = retrieveFromActiveSheet("P" + giCurrentRowBUS101_B1); // value for period 7
var strPeriod8 : String = retrieveFromActiveSheet("Q" + giCurrentRowBUS101_B1); // value for period 8
var strPeriod9 : String = retrieveFromActiveSheet("R" + giCurrentRowBUS101_B1); // value for period 9
var strPeriod10 : String = retrieveFromActiveSheet("S" + giCurrentRowBUS101_B1); // value for period 10
var strPeriod11 : String = retrieveFromActiveSheet("T" + giCurrentRowBUS101_B1); // value for period 11
var strPeriod12 : String = retrieveFromActiveSheet("U" + giCurrentRowBUS101_B1); // value for period 12
var sbSOAPString : StringBuilder = new StringBuilder(5000); // we use a string builder which we will add to as we process our data
if(null != sbSOAPString)
{
sbSOAPString.Append('<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:mws2="http://mws.intentia.net/mws2" xmlns:add="http://www.indfish.co.nz/BUS100/AddBudgetLines"><soapenv:Header><mws2:mws><mws2:user>' + gstrUsername + '</mws2:user><mws2:password>' + gstrPassword + '</mws2:password><mws2:company>' + gstrCompany + '</mws2:company><mws2:division>' + gstrDivision + '</mws2:division></mws2:mws></soapenv:Header><soapenv:Body><add:AddBudgetLines maxRecords="100"><!--Zero or more repetitions:--><add:AddBudgetLinesItem>');
buildSOAP(sbSOAPString, "add:Company", gstrCompany);
buildSOAP(sbSOAPString, "add:Division", gstrDivision);
buildSOAP(sbSOAPString, "add:BudgetNumber", gstrBudgetNumber);
buildSOAP(sbSOAPString, "add:BudgetVersion", gstrBudgetVersion);
buildSOAP(sbSOAPString, "add:AccountingDimension1", w1ait1);
buildSOAP(sbSOAPString, "add:AccountingDimension2", w1ait2);
buildSOAP(sbSOAPString, "add:AccountingDimension3", w1ait3);
buildSOAP(sbSOAPString, "add:AccountingDimension4", w1ait4);
buildSOAP(sbSOAPString, "add:AccountingDimension5", w1ait5);
buildSOAP(sbSOAPString, "add:AccountingDimension6", w1ait6);
buildSOAP(sbSOAPString, "add:AccountingDimension7", w1ait7);
buildSOAP(sbSOAPString, "add:Currency", w1cucd);
buildSOAP(sbSOAPString, "add:Curve", null);
buildSOAP(sbSOAPString, "add:TotalForeignCurrencyAmountBudget", null);
buildSOAP(sbSOAPString, "add:TotalBudgetQuantity", null);
buildSOAP(sbSOAPString, "add:ForeignCurrencyAmountBudget1", convertTo2DecimalPlaces(strPeriod1));
buildSOAP(sbSOAPString, "add:ForeignCurrencyAmountBudget2", convertTo2DecimalPlaces(strPeriod2));
buildSOAP(sbSOAPString, "add:ForeignCurrencyAmountBudget3", convertTo2DecimalPlaces(strPeriod3));
buildSOAP(sbSOAPString, "add:ForeignCurrencyAmountBudget4", convertTo2DecimalPlaces(strPeriod4));
buildSOAP(sbSOAPString, "add:ForeignCurrencyAmountBudget5", convertTo2DecimalPlaces(strPeriod5));
buildSOAP(sbSOAPString, "add:ForeignCurrencyAmountBudget6", convertTo2DecimalPlaces(strPeriod6));
buildSOAP(sbSOAPString, "add:ForeignCurrencyAmountBudget7", convertTo2DecimalPlaces(strPeriod7));
buildSOAP(sbSOAPString, "add:ForeignCurrencyAmountBudget8", convertTo2DecimalPlaces(strPeriod8));
buildSOAP(sbSOAPString, "add:ForeignCurrencyAmountBudget9", convertTo2DecimalPlaces(strPeriod9));
buildSOAP(sbSOAPString, "add:ForeignCurrencyAmountBudget10", convertTo2DecimalPlaces(strPeriod10));
buildSOAP(sbSOAPString, "add:ForeignCurrencyAmountBudget11", convertTo2DecimalPlaces(strPeriod11));
buildSOAP(sbSOAPString, "add:ForeignCurrencyAmountBudget12", convertTo2DecimalPlaces(strPeriod12));
buildSOAP(sbSOAPString, "add:ForeignCurrencyAmountBudget13", null);
buildSOAP(sbSOAPString, "add:ForeignCurrencyAmountBudget14", null);
buildSOAP(sbSOAPString, "add:ForeignCurrencyAmountBudget15", null);
buildSOAP(sbSOAPString, "add:BudgetQuantity1", null);
buildSOAP(sbSOAPString, "add:BudgetQuantity2", null);
buildSOAP(sbSOAPString, "add:BudgetQuantity3", null);
buildSOAP(sbSOAPString, "add:BudgetQuantity4", null);
buildSOAP(sbSOAPString, "add:BudgetQuantity5", null);
buildSOAP(sbSOAPString, "add:BudgetQuantity6", null);
buildSOAP(sbSOAPString, "add:BudgetQuantity7", null);
buildSOAP(sbSOAPString, "add:BudgetQuantity8", null);
buildSOAP(sbSOAPString, "add:BudgetQuantity9", null);
buildSOAP(sbSOAPString, "add:BudgetQuantity10", null);
buildSOAP(sbSOAPString, "add:BudgetQuantity11", null);
buildSOAP(sbSOAPString, "add:BudgetQuantity12", null);
buildSOAP(sbSOAPString, "add:BudgetQuantity13", null);
buildSOAP(sbSOAPString, "add:BudgetQuantity14", null);
buildSOAP(sbSOAPString, "add:BudgetQuantity15", null);
sbSOAPString.Append('</add:AddBudgetLinesItem></add:AddBudgetLines></soapenv:Body></soapenv:Envelope>');
doRequest(sbSOAPString.ToString());
}
else
{
return(null);
}
}
giCurrentRowBUS101_B1++; // increment our spreadsheet position
}
}
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
{
if(true == passwordPrompt())
{
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
MessageBox.Show("Finished Import"); // provide some feedback that we are finished
}
else MessageBox.Show("Failed to Open Workbook");
}
}
else MessageBox.Show("Filename or Excel doesn't exist: " + strFilename);
}
catch(exException)
{
MessageBox.Show("Error: " + exException.description);
}
CleanUp();
}
// send the WebService Request
public function doRequest(astrXMLRequest : String)
{
// we are going to use the HttpWebRequest object
// http://msdn.microsoft.com/en-us/library/system.net.httpwebrequest.aspx
// and we want to connect to the ItemFreeCaps2 service
var hwrRequest : HttpWebRequest = WebRequest.Create("http://wlmx02.indfish.co.nz:12000/lwstest/services/BUS100");
// ensure we actually managed to create something
if(null != hwrRequest)
{
// here we're defining our actions and content types
hwrRequest.Headers.Add("SOAPAction","\"\"");
hwrRequest.ContentType = "text/xml;charset=\"utf-8\"";
hwrRequest.Method = "POST";
hwrRequest.Accept = "text/xml";
hwrRequest.Proxy = GlobalProxySelection.GetEmptyWebProxy();
// we are going to use a stream to write out our request (and also read it later)
var strmStream : Stream = hwrRequest.GetRequestStream();
if(null != strmStream)
{
// SOAP is basically just xml, so we are going to use the XML framework
// to make our lives easier.
// Create an XML Document
var xmdDocument : XmlDocument = new XmlDocument();
if(null != xmdDocument)
{
// we then add the String to our XML document
xmdDocument.LoadXml(astrXMLRequest);
// the save of the document to our stream actually sends the request
// to the server
xmdDocument.Save(strmStream);
// close our stream
strmStream.Close();
// this section is wrapped in a try .. catch()
// block because I had a lot of problems getting
// this running initially.
try
{
// now we want to get a response
var wresponse : WebResponse = hwrRequest.GetResponse();
if(null != wresponse)
{
// we like using streams, so get the stream
// connection
strmStream = wresponse.GetResponseStream();
if(null != strmStream)
{
// create a streamreader to retrieve the data
var srStreamReader : StreamReader = new StreamReader(strmStream);
if(null != srStreamReader)
{
// and finally we read the data
var strXML : String = srStreamReader.ReadToEnd();
// close the response
wresponse.Close();
// close the stream reader
srStreamReader.Close();
// lets load the xml we read
xmdDocument.LoadXml(strXML);
var xel = xmdDocument.DocumentElement;
gwbWorkbook.ActiveSheet.Range("V" + giCurrentRowBUS101_B1).Value = "OK"; // write an ok to the spreadsheet
}
}
}
else
{
gdebug.WriteLine("No Response was returned");
}
}
catch(e)
{
gdebug.WriteLine("Exception: " + e.message);
gwbWorkbook.ActiveSheet.Range("V" + giCurrentRowBUS101_B1).Value = "Exception - " + e.message; // write an exception to the spreadsheet
}
}
}
}
else
{
gdebug.WriteLine("doRequest() unable to create");
}
}
// findNode will recurse through the XmlNodes until if finds
// and Element with astrElementName as a name, it will
// then return that node.
public function findNode(axmnNode : XmlNode, astrElementName : String)
{
var result : XmlNode = null;
if(null != axmnNode)
{
if(String.Compare(astrElementName, axmnNode.Name, true) == 0)
{
result = axmnNode;
}
else
{
if(true == axmnNode.HasChildNodes)
{
result = findNode(axmnNode.FirstChild, astrElementName);
}
if(null != axmnNode.NextSibling)
{
result = findNode(axmnNode.NextSibling, astrElementName);
}
}
}
return result;
}
// here we will create the budget header SOAP request
private function addBudgetHeader(astrDescription : String, astrName : String, astrStartPeriod : String, astrExchangeRate : String, astrNumberPeriods : String, astrUpdateBudget : String) : String
{
// because we have to build the request on the fly we will use a StringBuilder
// it helps prevent memory fragmentation
var sbSOAPString : StringBuilder = new StringBuilder(5000);
if(null != sbSOAPString)
{
sbSOAPString.Append('<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:mws2="http://mws.intentia.net/mws2" xmlns:add="http://www.indfish.co.nz/BUS100/AddBudgetHeader"><soapenv:Header><mws2:mws><mws2:user>' + gstrUsername + '</mws2:user><mws2:password>' + gstrPassword + '</mws2:password><mws2:company>' + gstrCompany + '</mws2:company><mws2:division>' + gstrDivision + '</mws2:division></mws2:mws></soapenv:Header><soapenv:Body><add:AddBudgetHeader maxRecords="100"><add:AddBudgetHeaderItem>');
buildSOAP(sbSOAPString, "add:Company", gstrCompany);
buildSOAP(sbSOAPString, "add:Division", gstrDivision);
buildSOAP(sbSOAPString, "add:BudgetNumber", gstrBudgetNumber);
buildSOAP(sbSOAPString, "add:BudgetVersion", gstrBudgetVersion);
buildSOAP(sbSOAPString, "add:Description", "Test");
buildSOAP(sbSOAPString, "add:Name", "Test");
buildSOAP(sbSOAPString, "add:StartPeriodBudget", astrStartPeriod);
buildSOAP(sbSOAPString, "add:ExchangeRateType", astrExchangeRate);
buildSOAP(sbSOAPString, "add:NumberOfPeriods", astrNumberPeriods);
buildSOAP(sbSOAPString, "add:RoundingoffCategory", null);
buildSOAP(sbSOAPString, "add:UpdateBalanceFile", astrUpdateBudget);
buildSOAP(sbSOAPString, "add:AllocationTemplate", null);
buildSOAP(sbSOAPString, "add:ObjectAccessGroup", null);
sbSOAPString.Append('</add:AddBudgetHeaderItem></add:AddBudgetHeader></soapenv:Body></soapenv:Envelope>');
return(sbSOAPString.ToString());
}
else
{
return(null);
}
}
// this is a little function to encapsulate a value within an XML element
// if we don't have a value then the element isn't added - if we add the element with the
// empty value we will get an exception back from the webservice
private function buildSOAP(atbFinalString : StringBuilder, astrElement : String, astrValue : String)
{
if(false == String.IsNullOrEmpty(astrValue))
{
atbFinalString.Append("<" + astrElement + ">" + astrValue + "</" + astrElement + ">");
}
}
// 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);
}
// 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)
{
MessageBox.Show("Error: " + exException.Message + Environment.NewLine + exException.StackTrace);
}
return(result);
}
// construct and display a window to ask a user for a username and password
// which we will use for the web services
private function passwordPrompt() : boolean
{
var bResult : boolean = false;
wndPasswordPrompt = new Window();
if(null != wndPasswordPrompt)
{
var spVertical : StackPanel = new StackPanel();
var spHorizontal1 : StackPanel = new StackPanel();
var spHorizontal2 : StackPanel = new StackPanel();
var spHorizontal3 : StackPanel = new StackPanel();
var lbUsername : Label = new Label();
var lbPassword : Label = new Label();
var tbUsername : TextBox = new TextBox();
var pbPassword : PasswordBox = new PasswordBox();
var btnOk : Button = new Button();
var btnCancel : Button = new Button();
wndPasswordPrompt.Title = "Please enter you username and password";
wndPasswordPrompt.Width = 280;
wndPasswordPrompt.Height = 150;
lbUsername.Content = "Username:";
lbPassword.Content = "Password:";
lbUsername.Width = 80;
lbPassword.Width = 80;
tbUsername.Width = 150;
pbPassword.Width = 150;
tbUsername.Margin = new Thickness(2);
pbPassword.Margin = new Thickness(2);
tbUsername.Name = "tbUsername";
pbPassword.Name = "pbPassword";
btnOk.Content = "Ok";
btnCancel.Content = "Cancel";
btnOk.IsDefault = true;
btnOk.Margin = new Thickness(10);
btnCancel.IsCancel = true;
btnCancel.Margin = new Thickness(10);
spHorizontal1.Children.Add(lbUsername);
spHorizontal1.Children.Add(tbUsername);
spHorizontal2.Children.Add(lbPassword);
spHorizontal2.Children.Add(pbPassword);
spHorizontal3.Children.Add(btnOk);
spHorizontal3.Children.Add(btnCancel);
spHorizontal3.HorizontalAlignment = System.Windows.HorizontalAlignment.Stretch;
spVertical.Margin = new Thickness(5);
spVertical.Orientation = System.Windows.Controls.Orientation.Vertical;
spHorizontal1.Orientation = System.Windows.Controls.Orientation.Horizontal;
spHorizontal2.Orientation = System.Windows.Controls.Orientation.Horizontal;
spHorizontal3.Orientation = System.Windows.Controls.Orientation.Horizontal;
wndPasswordPrompt.Content = spVertical;
spVertical.Children.Add(spHorizontal1);
spVertical.Children.Add(spHorizontal2);
spVertical.Children.Add(spHorizontal3);
btnOk.add_Click(OnbtnDialogOKClick);
btnCancel.add_Click(OnbtnDialogCancelClick);
if(true == (bResult = wndPasswordPrompt.ShowDialog()))
{
gstrUsername = tbUsername.Text;
gstrPassword = pbPassword.Password;
}
else
{
gstrUsername = null;
gstrPassword = null;
}
// remove the OK and cancel events
btnOk.remove_Click(OnbtnDialogOKClick);
btnCancel.remove_Click(OnbtnDialogCancelClick);
wndPasswordPrompt = null;
}
return(bResult);
}
public function OnbtnDialogOKClick(sender: Object, e: RoutedEventArgs)
{
wndPasswordPrompt.DialogResult = true;
wndPasswordPrompt.Close();
}
public function OnbtnDialogCancelClick(sender: Object, e: RoutedEventArgs)
{
wndPasswordPrompt.Close();
}
}
}
Potato IT 