XSharp

For the 4th example we will not use a standard example from Visual Objects but will create a new sample app in Visual Objects to remote control Excel and to write data to an Excel Sheet.

I found the following example in the Comp.Lang.Clipper.Visual-Objects newsgroup (which I changed a little bit for this example).

•Create a new terminal application in Visual Objects and copy and paste the code below into the app.

•Open the application Properties and add the Ole Library.

•Also 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 one source file (Start.prg). The source is almost identical with 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 not correct. 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, so X# thinks 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 2 errors remain. These indicate that X# does not know how to convert the array index from string to int. This is on the line with the range assignment.
The code above uses 'Late Binding', so the types are not known at compile time. X# does not know if the Range property from the Worksheet object is an indexed property or if it 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

 

and 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

And run the example again. You will see that both functions are called. Before the save the path and name are not set properly, 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

 

 

 

That is because these events are called on a separate thread. 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