Welcome, Guest
Username: Password: Remember me
  • Page:
  • 1

TOPIC:

Differences VO->Excel automation and X#->Excel Interop 13 Oct 2021 17:01 #19904

  • ic2


  • Posts: 1122
  • 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

    Please Log in or Create an account to join the conversation.

    Differences VO->Excel automation and X#->Excel Interop 13 Oct 2021 18:12 #19907

    • robert
    • robert's Avatar


  • Posts: 2371
  • 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
    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
    XSharp Development Team
    The Netherlands

    Please Log in or Create an account to join the conversation.

    Differences VO->Excel automation and X#->Excel Interop 13 Oct 2021 18:21 #19908

    • ic2


  • Posts: 1122
  • 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

    Please Log in or Create an account to join the conversation.

    Differences VO->Excel automation and X#->Excel Interop 13 Oct 2021 18:26 #19909

    • robert
    • robert's Avatar


  • Posts: 2371
  • 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
    XSharp Development Team
    The Netherlands

    Please Log in or Create an account to join the conversation.

    Differences VO->Excel automation and X#->Excel Interop 13 Oct 2021 21:18 #19910

    • ic2


  • Posts: 1122
  • 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

    Please Log in or Create an account to join the conversation.

    Differences VO->Excel automation and X#->Excel Interop 14 Oct 2021 07:08 #19911

    • wriedmann
    • wriedmann's Avatar


  • Posts: 2785
  • Hi Dick,
    please let me add something: sometimes in X# things change, so you could write code like this in VO:
    #ifdef __XSHARP__
    EXPORT oWorksheet AS Excel_Worksheet
    #else
    EXPORT oWorksheet AS OBJECT
    #endif
    And then it would work in both VO and X#.
    Wolfgang
    Wolfgang Riedmann
    Meran, South Tyrol, Italy

    www.riedmann.it - docs.xsharp.it

    Please Log in or Create an account to join the conversation.

    Differences VO->Excel automation and X#->Excel Interop 14 Oct 2021 12:41 #19914

    • ic2


  • Posts: 1122
  • 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

    Please Log in or Create an account to join the conversation.

    Differences VO->Excel automation and X#->Excel Interop 14 Oct 2021 12:50 #19915

    • robert
    • robert's Avatar


  • Posts: 2371
  • 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)
    #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:
    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
    XSharp Development Team
    The Netherlands

    Please Log in or Create an account to join the conversation.

    Last edit: by robert.

    Differences VO->Excel automation and X#->Excel Interop 14 Oct 2021 13:27 #19917

    • ic2


  • Posts: 1122
  • Hello Robert,

    That's again lightning fast!

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

    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

    Please Log in or Create an account to join the conversation.

    • Page:
    • 1