Differences VO->Excel automation and X#->Excel Interop

[ic2]

We use a VO VO->Excel automation OLE library on which may Excel manipulations are based. Such an OLE lib can not be converted and the idea was that you could just use the Excel Interop include.

However, today I went with Frank through a substantial number of errors which required to change our code while it was not obvious why in most cases. Here are 3 samples, for number 1 + 2 we have not yet found a solution:

1 This works in VO:oRange:= oWorksheet:Range["A1","B2"]. But the converted X# code gives:

XS9059: Can not convert array index from string to integer

How can I set a range to (in this case) this 4 cells if the Range does no longer accept strings?

2 SELF:nExcLang:=SELF:oApplication:International[1] returns Excel's language in VO, e.g. 1 for English or 31 for Dutch. However, in X# this is an object which does not seem to have a language property. How do we get the Excel language?

3 We had code in VO like this .HorizontalAlignment = xlLeft but this says that xlLeft is not defined. After trying every logical "using" we found it should be:

.HorizontalAlignment = XlHAlign.xlHAlignLeft

For all 3 (and some more) of these changes I wonder why the generated VO OLE code is so different from what has to be used from the Excel Interop X# solution?

Dick

[robert]

Dick,
Since you are not showing all the code, this means that we have to guess about the cause of problem.
How is oWorksheet declared ? I expect is is not typed as Worksheet
I tried the code below and it works as expected.

And the various values for the numeric constants are defined in Enums in this type library. VO did not know the concept of enums so all the names of the possible values for these enums were generated as defines. In the example below I am casting the counter in the loop to the appropriate Enum, so you can also see the name of the setting.
And indeed the HorizontalAlignment property of the style object is of type XlHAlign.
The X# compiler by the way is smart enough to also allow you to use the underlying numeric value for a property instead of the define as you can see in the example below. The International property can be called with both the enum value as well as an integer,
Robert

Code: Select all

USING Microsoft.Office.Interop.Excel

FUNCTION Start() AS VOID STRICT
	LOCAL App AS Application
	LOCAL ws AS Worksheet
	LOCAL wb AS Workbook
	LOCAL e AS XlApplicationInternational
	app := ApplicationClass{}

	? app:International
	FOR VAR i := 1 TO 10
		e := (XlApplicationInternational) i
		? i, e:ToString(), app:International[e], app:International[i]
	NEXT

	wb := app:Workbooks:Add()
	ws := wb:Worksheets[1]
	? "Rows", ws:Range["a1","b1"]:Rows:Count
	? "Columns", ws:Range["a1","b1"]:Columns:Count
   WAIT
   RETURN

[ic2]

Hello Robert,

Thanks very much for the sample!

We did indeed try to pass the values (as we did in VO) where a iEnumerable is expected and this did not compile (Frank uses a 2.8 version). I will ask Frank tomorrow to test this sample app and see if he can implement it in our current code, and if not, why not.

To be complete, this is how we define oWorksheet in VO.

So: to be continued...

SELF:oWorkbooks := SELF:oApplication:Workbooks
IF lAddWorkBook
SELF:oWorkbook:=SELF:oWorkbooks:Add(1)
SELF:oSheets := SELF:oWorkbook:WorkSheets
SELF:oWorksheet := oSheets:[Item,1]
SELF:oWorksheet := SELF:oApplication:ActiveSheet
SELF:oWorksheet:Activate()
SELF:oPagesetup := SELF:oWorkSheet:PageSetup
ENDIF


Dick

[robert]

Dick,

This is still not complete enough.
You are not showing how oWorkbook is declared. I guess that it is declared AS USUAL or AS OBJECT.
And for
SELF:oWorksheet := oSheets:[Item,1]
you can now simply write
SELF:oWorksheet := oSheets[1]

Robert

Robert

[ic2]

Hello Robert,

Indeed, oWorksheet is an OBJECT and I have even added the reason in the comment why I changed it to an object.

EXPORT oRange AS ExcelRange
EXPORT oWorkbooks AS ExcelWorkbooks
EXPORT oWorksheet AS OBJECT // Excel_Worksheet // since 2753 used to be Excel_Worksheet but as oWorksheet:=oWorkBook:ActiveSheet gives WRONG CLASS changed to object 9-9-2007
EXPORT oWorkbook AS Excel_Workbook
EXPORT oSheets AS ExcelSheets
EXPORT oPageSetup AS ExcelPageSetup

Anyhow, let's see what Frank finds Thursday.

Dick

[wriedmann]

Hi Dick,
please let me add something: sometimes in X# things change, so you could write code like this in VO:

Code: Select all

#ifdef __XSHARP__
EXPORT oWorksheet AS Excel_Worksheet
#else
EXPORT oWorksheet AS OBJECT
#endif

And then it would work in both VO and X#.
Wolfgang

[ic2]

Hello Wolfgang, Robert,

The program does not work. With Local ws As Excel_Worksheet

we get the following error the definition:


Error XS0246 The type or namespace name 'Excel_Worksheet' could not be found (are you missing a using directive or an assembly reference?)

and with Local ws As Worksheet we get the following error:


Error XS0266 Cannot implicitly convert type 'object' to 'Microsoft.Office.Interop.Excel.Worksheet'. An explicit conversion exists (are you missing a cast?)

at
ws := wb:Worksheets[1]

The program is then 100% the same as Robert posted it and Microsoft.Office.Interop.Excel. has been included and set as USING.

Dick

[robert]

Dick,
The reason why you have a different result is most likely compiler options.
But since you do not include your complete code this is hard to see.
Try to add the following line before the Start() function (or enable Late Binding in the Project Properties)

Code: Select all

#pragma options("lb", ON)

I am not sure if the #pragma works in the version of X# that you are using (you are not using the latest version IIRC).
In that case you will HAVE to enable Late Binding.
Alternatively you will have to cast the object returned by the Worksheets collection to a Worksheet:

Code: Select all

ws := (Worksheet) wb:Worksheets[1]

I am not sure why Ms declared the return value of the WorkSheets:Item property as OBJECT.
Most likely because you can also store something else in this collection.

Robert

[ic2]

Hello Robert,

That's again lightning fast!

The complete program was the program you posted yourself.....

But to summarize, the #pragma nor Late Binding made it work, but the casting (ws := (Worksheet) wb:Worksheets[1]) did..

With this working Frank can compare this with his converted code, so hopefully he gets a compiling conversion.

Dick