WYSIWYG Excel production in Appeon

[Former Member]

Hi All,

Currently stumbling through a PB migration using Appeon at the moment and just trying to figure out the best solution for exporting complex DW Reports into Excel Files.  A lot of what I am doing is proof of concept to see how much of our App we want to deploy, etc.

In our desktop app we use pb2xls DLL & dw2xls PBL for exporting our DW reports to excel.  We allow the users to set options to determine the level of 'WYSIWYGness' of the file, i.e. let pb2xls split and merge cells based on visual layout.  They can also disable this so that the Excel file is more usable by reducing the number of merges and splits.

To cut a long story short, I am just wondering if anyone else has used pb2xls/dw2xls in an Appeon deployment or should I look to some alternative method?

One thought I had was maybe encapsulating the functionality in a webservice, but unsure how to pass the populated DW through to it.

Any thoughts or comments appreciated, as always.

Michael

[Former Member · Accepted solution]

Well after a lot of trial and error AND reading of community posts, I have made little progress!

Firstly, Windows 10 and PB are just trying to frustrate me.  Following reading posts by Chris Pollach about PB being tightly tied to specific .NET versions I have gone and downloaded multiple versions of SDKs to install.  Windows 10 comes with 4.5 & 4.6 installed, neither of which are of any use to me.

So I installed 2.0 for PB 12.1, 3.5 for PB 12.5 and 4.0 for a trial of PB 12.6.

I have now managed to deploy a very simple Web Service using PB 12.6 on the 1st go.  When I initially tried the same with PB 12.5 it said it deployed fine, but only created an empty folder in wwwroot.  I eventually found the cause of this was because I was running PB with Admin privileges because Appeon requires PB to be run 'as Administrator'.  Changed shortcut back to run as normal and PB 12.5 deployed fine.

Now to PB 12.1!!  Removed the Admin privilege from PB 12.1 and the web service deployment crashes giving the following error.  I have not been able to work around this yet, but at least I can create a Web Service in PB 12.5...

SO, now I have gone back to PB 12.5, created my basic Web Service, which will take in a Blob to recreate a DW/DS.  This will then be used to create the Excel File using the dw2xls library and PB2XLS.dll on the server side...or so I thought!

On first deployment I get an error about Private function and inheritance, which I can sort out easily enough.  Now I get the following error and am stumped!  Found a single post with the same error from a number of years ago, but no followup so no answer.

---------- Error messages:    (15:53:42)

Error when compiling generated C# code:

n_dwr_band.of_add_field(ISLRCn_dwr_field.)(88,78): error CS0030: Cannot convert type 'T2' to 'c__n_dwr_rect'

---------- Finished    (15:53:42)

Has anybody seen this type of error before or found a solution.  Don't even know where to begin.

Why dost thou mock me Windows and PB!!

[Former Member · Accepted solution]

Hi Michael;

1) PB 12.1 works great for me on W10 with Web Services. However, note that you must ensure that only .Net 2.0 entities are present at run time. I accomplish this by setting my .Net Run-Time and SDKs dynamically by using Environment Variables to create a pure setting for PB 12.1, as follows:

  What I do then is to rename the .Net run-time & SDK's by adding a "_XX" suffix to each non-2.0 reference when I want to take them out of the picture. My System Path uses the above as follows:

%SystemRoot%;%SystemRoot%\System32;%SystemRoot%\System32\Wbem;%systemroot%\System32\WindowsPowerShell\v1.0\;%DotnetSDK4.0%;%DotnetSDK2.0%;%Dotnet4.0%;%Dotnet3.5%;%Dotnet2.0%;%PB12.6%;%PB12.5%;%PB12.0%;%JavaPB%; ...

  Using this technique - I can switch between PB 12.1, 12.5.1 and 12.6 by either stepping up the .Net level or reversing it moving down the PB version scale in seconds.  

2) FWIW: I always run my PB IDE's in Admin mode and have no such Appeon requirement or deployment issues with IIs in 12.5.1.

3) Now I get the following error and am stumped!

  How & where are you getting this (native PB test app)?

Regards .. Chris

[Former Member · Accepted solution]

Hi Chris,

The Environment variables are a great idea, I must give it go.  I wonder is the Run as Admin issue a security thing on my wwwroot folder, but its a minor bugbear.

The Error is coming from a test web service that I am trying to deploy.  I have a project with 2 libraries, one if the Service Objects, etc. the 2nd is the PB2XLS pbl.  When I try to deploy the service I get the error relating to something in the PB2XLS pbl.

Test Web Service PB12.5.rar - Google Drive

The link above is to the project/source.

Michael

[Former Member · Accepted solution]

Ahhh .. yes, I see that they use lots of external function calls to dynamically create Window classes and other SDK calls to manipulate visual object classes. These of course, are not allowed in a Web Service .. only non-visual classes & operations. My guess is that the WS compiler is balking at these.

Its too bad that they implemented PB/DW2XLS this way.  

[Former Member · Accepted solution]

Thanks Chris,

I had not thought about the Visual User Object side of things.

Guess I will park this and wait to see what APPEON 2015 R2 will bring, as Armeen indicated.

In the meantime, I think I will tidy up reports as I go, and use the native Excel or XML export.

Michael

[Former Member · Accepted solution]

   I would also suggest adding this as an enhancement request to the PB/DW2XLS product vendor ... to make the DW2XLS product web service compliant.  

[Former Member · Accepted solution]

I purchased the latest version of dw2xls back in April, just prior to visiting Charlotte for the PB conference.  To that point there had been no development on the website or dw2xls since 2012.

Upon checking their website now, there seems to be a new version released in the last number of months...a 3 year gap...which they are selling with Appeon support.

Have contacted them for more information and will post back with their reply.

[Former Member]

I haven't tried using pb2xls on appeon, but  why are you not just including the dll in the appeon download and using it as you did before rather than try to build a web service for this?   There is a spot where you tell appeon what dlls etc to download for you.

[Former Member]

or are you using appeon mobile?  in that case you will need to do this externally via a service.

[Former Member]

Hi Mike,

I have done that and somewhere within it is failing to create the Excel file.

I have even updated the excel service uo to make sure it is finding the dll in the correct plugin folder on the server, but still does not work.  Could be hours/days of work trying to pick through every function call to find the exact point of failure.

Will try a move to the Web Service as Chris suggests, hopefully can get that to work.

[Former Member]

Appeon Web Deployment at the moment.  Not even thinking about the mobile side yet.  Chances are we will redevelop smaller modules as specific user apps.

[former_member185283]

Hi Michael,

DW2XLS relies on the OLE Storage/OLE Stream features of PowerBuilder.  We are currently in the process of adding support for these features to Appeon 2015 R2, which is planned to be released in January 2016.  As such, DW2XLS should work fine with the upcoming Appeon 2015 R2.  Here is a Webcast that explains other important features of this release: Appeon in 2015 & Beyond - YouTube

As far as alternatives to DW2XLS, there has been mentions on SCN about an open-source library trying to do similar thing to DW2XLS for PB but using a different approach than DW2XLS: https://github.com/2ruslan/wrx  Appeon has not evaluated this so we cannot explain what feature differences between the two or whether this is 100% compliant for Appeon Web deployment.

I hope this is helpful.

Best regards,

Armeen Mazda

CEO, Appeon

[Former Member]

Thanks Armeen,

I had previously seen the webcast and certainly some of the new features expected in R2 are very exciting.  There was also mention of Mac OS support at which will be of great interest going forward.

I will continue with my development of a web service, but look forward to seeing native ole support in R2 once it is released.

Thanks Again,

Michael

[Former Member]

Don't get your hopes up on that ruslan/wrx project.  I quickly looked at it and it doesn't do a lot. It dates back to PB5 days.  Has nowhere the sophistication of dw2xls.

[Former Member]

Hi Michael;

  Although I have not tied it - the Web Service route with DW2XLS should work well. However, the major problem you will encounter is replicating a complex Nested or Composite DW from the client to the WS as these DWO's have multiple buffers due to their many child DW nature.

  One way around the problem is to use a WS exclusively for generating / running the report DW's. Thus all the data buffers are present on the server to then use DW2XLS to finish the Excel rendering. This approach may require extensive refactoring of parts of your existing applications though. You may also have to rethink the client side presentation layer. Maybe passing back PDF report renderings instead.

  Another suggestion might be to look at other alternative products like LibXL for example.

Regards ... Chris

[Former Member]

Thanks Chris,

I will give the webservice a go.  I think we have disabled the export for overly complicated report, with nested reports, etc.

Most are single level reports, but can have a lot of additional formatting.

We have already purchased the latest version of PB2XLS, so not really an option to go and purchase another.

Just a quickie, how would you suggest passing the DW to the webservice?  I am thinking that I could pass the complete DW syntax through to the Webservice as a string and recreate the DW on the server side.  Then that can be used to generate the Excel file.  Does that sound feasible?

[Former Member]

Hi Michael;

  FWIW: I would pass the DWO + its datum as a Blob data type using the GetFullState ( ) method to assist you with the complete packaging. Then of course on the Web Service side, using the SetFullState ( ) method to help reconstitute the DWO plus its datum.

HTH

Regards ... Chris

[Former Member]

Thanks Chris,

Forgot that the Datawindow.Syntax will only give me the shell and not the DATA.

I will give the GetFullState a go and see how I get on, but looks like it could be promising.  Work for tomorrow...5pm here and home time!