1. Peter Currier
  2. PowerBuilder
  3. Tuesday, 15 September 2020 18:40 PM UTC

I am trying to use Powerbuilder 2019 R2 to load the Office Interop .NET assembly so that I can format an existing Excel spreadsheet.   I have found some C# code that opens Excel and then instantiates the Excel Workbook by opening a file using the Excel Application class.   I am trying to duplicate that process in Powerbuilder.

I used the .NET DLL Importer and it created NVOs for the Application , Workbook, and Worksheet Classes from  Microsoft.Office.Interop.Excell.dll.

The Powerbuilder code below works and opens an EXCEL process.


Powerbuilder Code:

nvo_applicationclass lnvo_app
nvo_workbookclass lnvo_wb
nvo_worksheetclass lnvo_ws

dotnetassembly lcs_ass_excel
long li_return
string ls_text

lcs_ass_excel = CREATE dotnetassembly

lnvo_app = CREATE nvo_applicationclass
lnvo_wb = CREATE nvo_workbookclass
lnvo_ws = CREATE nvo_worksheetclass

li_return = lcs_ass_excel.LoadWithDotNetFramework("microsoft.office.interop.excel.dll")

if li_return < 0 then
    messagebox('Load .NET Failed', lcs_ass_excel.ErrorText)
    return
end if

li_return = lcs_ass_excel.CreateInstance ("microsoft.office.interop.excel.ApplicationClass", lnvo_app)

The next step would be to Open the XLSX file and instantiate the Workbook- similar to this C#code:

Excel.Application MyApp = new Excel.Application();
Excel.Workbook myWorkbook = MyApp.Workbooks.Open(FilePath,Type.Missing, Type.Missing, Type.Missing, Type.Missing,Type.Missing, Type.Missing, Type.Missing, Type.Missing,Type.Missing, Type.Missing, Type.Missing, Type.Missing,Type.Missing, Type.Missing);

Is this possible using the .NET tools in Powerbuilder 2019 R2?

 

Peter

Mark Lee @Appeon Accepted Answer Pending Moderation
  1. Wednesday, 16 September 2020 06:09 AM UTC
  2. PowerBuilder
  3. # 1

Hi Peter,

Currently, the Object-type arguments are not supported in .NET functions and the Interface also is not supported in .NET classes. 

You can refer to the following link for more details. 

https://docs.appeon.com/pb2019r2/application_techniques/ch20s01.html

I suggest that you create a .NET wrapper for these functions.

And here is an example on how to create such C# wrapper to work it around:https://community.appeon.com/index.php/qna/q-a/net-importer-tool#reply-20285 

Regards,

 

Comment
  1. Peter Currier
  2. Wednesday, 16 September 2020 13:39 PM UTC
Mark -



Thank you for your thorough explanation. This was very helpful.



Peter
  1. Helpful
There are no comments made yet.
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Wednesday, 16 September 2020 04:12 AM UTC
  2. PowerBuilder
  3. # 2

I think it would be possible as long as you don't do anything "visual" in .net, but to be true, you can just use OLE to do all the Excel stuff in powerbuilder (if you have Excel installed), no need for .Net

 

Maybe I don't really understand your question:

You say you've already imported the assembly with the DLL Importer tool, what stops you from trying?

 

regards.

Comment
  1. Peter Currier
  2. Wednesday, 16 September 2020 13:45 PM UTC
Hi Miguel,



We currently have quite a few Powerbuilder apps that use OLE Automation to do some sophisticated formatting of Excel worksheets. It appears that Microsoft does not support OLE Automation in any of the Click To Run versions of Office, so we are researching alternatives to OLE. One option is to go to a C# app/dll, but we were hoping to stay within Powerbuilder if that is possible.



Peter
  1. Helpful
  1. Miguel Leeuwe
  2. Wednesday, 16 September 2020 14:08 PM UTC
Ah I see, that will be a problem we will soon experience too, though I'm not sure if it was our .net programmer complaining about the Click to Run versions.

So yes, to avoid the problems of incompatible type that Mark told you about, you would have to write a DLL which has compatible in and output parameters for powerbuilder and does the function calls to the other DLL with the incompatible types.

I'll ask our .net programmer tomorrow what problems he had with clickToRun office, maybe it's also important for what you are doing.

regards
  1. Helpful
  1. Miguel Leeuwe
  2. Thursday, 17 September 2020 13:00 PM UTC
I just spoke with our .Net programmer. The problem he had was with some Excel Reports in which there's quite a lot of programming. The problem he had was with the Oracle OLEDB driver. He solved it by using Oracle ODBC.

So not really something to worry about.

regards
  1. Helpful
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.
We use cookies which are necessary for the proper functioning of our websites. We also use cookies to analyze our traffic, improve your experience and provide social media features. If you continue to use this site, you consent to our use of cookies.