XSharp

For the fourth example, instead of using a standard example from Visual Objects, we will create a new sample application in VO to remote control Excel and write data to an Excel Sheet.

I adapted a script found in the Comp.Lang.Clipper.Visual-Objects newsgroup to fit our example.

•Start by creating a new terminal application in Visual Objects, then copy and paste the code below into the application.

•Open the application Properties and add the Ole Library.

•Rename the app to "ExcelTest".

•Now, open the start module and copy the code.

•Compile and run, and you will find an xls file in the C:\ExcelTest folder.

 

FUNCTION Start()
LOCAL oExcel AS OBJECT
LOCAL oWorkBooks AS OBJECT
LOCAL oWorksheet AS OBJECT
LOCAL oRange AS OBJECT
LOCAL cFile AS STRING
cFile := "C:\ExcelTest\example.xls"
DirMake("C:\ExcelTest")
oExcel:=OLEAutoObject{"Excel.Application"}
oExcel:Visible:=FALSE // Don't show the EXCEL execute
oExcel:DisplayAlerts:=FALSE // Don't show messages
oWorkBooks:=oExcel:Workbooks
oWorkBooks:add() //open a new worksheet
oWorkSheet:=oExcel:ActiveSheet // active the first sheet
oRange:=oWorkSheet:[Range,"A1","A1"] // A1 cell
oRange:SELECT()
oRange:FormulaR1C1:="Hello my text"
oExcel:ActiveWorkBook:SaveAs(cFile,56,"","",;
     FALSE,FALSE) //"56" save the file in work book of EXCEL 97-2003 (Excel 8)
 
oWorkBooks:Close()
oExcel:Quit()  
WAIT
 
RETURN NIL  

 

•Export the AEF to "C:\ExcelTest\ExcelTest.AEF"

•Run the VOExporter and export the code.

•After opening the solution inside Visual Studio, you will also get an application with a single source file (Start.prg). The source is almost identical except for one difference. The line :

oRange:=oWorkSheet:[Range,"A1","A1"] // A1 cell

 

has been changed to

oRange:=oWorkSheet:Range["A1","A1"] // A1 cell

 

•Note that the name Range is now in front of the Square brackets.
Range is a so-called "Indexed property" of the worksheet. Visual Objects uses a "funny" syntax for this.
X# uses the same syntax that most other languages do. The property looks like an array (the square brackets) .

•Now, compile the app in X#. You will get the following errors:

Let's look at these errors:

•The last errors in the list indicate that the Start method is incorrect. This is because the Start function in .Net has to either be a VOID function or a function that returns an INT. In this case, no return type has been declared, making X# think that you want to create a function that returns a USUAL. Change the prototype of the start function to:

FUNCTION Start() AS VOID

and remove the NIL return value from the RETURN statement.
 

•Now, two errors remain. These indicate that X# does not know how to convert the array index from string to int. This is in line with the range assignment.
The code above uses 'Late Binding', so the types are not known at compile time. X# does not know whether the Range property from the Worksheet object is an indexed property or returns an array. It assumes that it returns an array and wants to specify the array index which is numeric (and subtract one because Visual Objects uses 1 based arrays where .Net uses 0 based arrays).

•The best way to fix this is to use strong typing and use the Generated Class wrappers for Excel.
In Visual Objects you would use the 'Automation Server' tool to generate such a class wrapper. In .Net there is a similar tool. The easiest way to achieve this is to add a reference to Excel to the references of the application:

•Right Click on "References" in the solution Explorer and Choose "Add Reference"

•In the 'Add Reference' dialog, choose the COM tabpage

•Locate the "Microsoft Excel nn.m Object Library". On my machine, that is Excel 16.0.

•Click Ok.

•This will add an entry to your References list with the name "Microsoft.Office.Interop.Excel". This is a generated type library that contains class definitions for all types inside Excel.

•Now go into your code, add a Using statement for the namespace of the Excel classes, and change "AS OBJECT" to the right types:

  USING Microsoft.Office.Interop.Excel
  FUNCTION Start() AS VOID
  LOCAL oExcel AS Application
  LOCAL oWorkBooks AS Workbooks
  LOCAL oWorksheet AS Worksheet
  LOCAL oRange AS Range

 

•Also, change the call to create the Main Excel object:

 oExcel:=ApplicationClass{}
 

•Your code is now strongly typed, so you should also get intellisense if you try to select a member from one of these objects, such as oExcel:Workbooks.

•Compile and run the code. It works as expected.

•You may want to change the value 56 in the SaveAs line to the appropriate enum value: xlFileFormat.xlExcel8

•Now run the app again and everything works as expected.

•If you look in the folder where the EXE is generated you will see both ExcelTest.Exe and Microsoft.Office.Interop.Excel.dll, the type library.

Note 1

If you are wondering why we declare oExcel as Application but use ApplicationClass to instantiate the object, here is the reason:

Application is the interface, ApplicationClass is the actual class that implements the Application interface. This is a model that you will see for most Automation Servers.

Note 2

Many people have asked for a way to implement OLE Events. With the X# code and the generated type library this is very easy.

Add the following code to the start method to define a BeforeSave and AfterSave event:

oExcel:WorkbookBeforeSave += OnBeforeSave
oExcel:WorkbookAfterSave += OnAfterSave

 

Then, add the following functions:

FUNCTION OnBeforeSave (oWb AS Workbook, SaveAsUI AS LOGIC, Cancel REF Logic) AS VOID
? "OnBeforeSave", oWb:Path, oWb:Name, SaveAsUI
RETURN
 
FUNCTION OnAfterSave (oWb AS Workbook, Success AS LOGIC) AS VOID
? "OnAfterSave", oWb:Path, oWb:Name, Success
RETURN

If you then run the example again, you will see that both functions are called. Before the save, the path and name are not set properly, while afterwards, they are set to the values specified in the code
Some versions of Excel do not support the WorkbookAfterSave event. In that case you will get a compile time error

 

 

 

The reason is that these events are called on a separate threat. If you look at the Threads window in VS (Debug - Windows - Threads) you will see that:

 

Note 3

If you look closely in the Add References dialog, you may also find other occurrences of the Excel library (on the .Net tab). On my machine these are:

 

 

These are so called "Primary Interop Assemblies" (PIAs), pre-compiled assemblies, for different Excel versions. You can use these as well. These assemblies are installed with the Office Developers Tools for Visual Studio. On my machine, they are located in subfolders of "c:\Program Files (x86)\Microsoft Visual Studio 14.0\Visual Studio Tools for Office\PIA" .

 

You will find the "code before" and "code after" in the XSharp Examples folder.