0

We are developing a web site (ASP.Net MVC 5 with C#) for an existing application(Client/server) developed on PowerBuilder 12.5(Classic) + SQLServer 2014. We would like to use the same reports that are designed in our PB app, to save time on redesigning and future maintenance. We have read that it is possible to use .Net assemblies developed with PB.Net in C#. Just could not find the right resources for presenting a datawindow in an MVC website.

Trial#1: Tried exporting the html from the dw report, which does not render all fields properly. dw_1.Describe("DataWindow.data.html"). Changing the html table properties (in dw) did not make much of a difference other than the borders and text color. Background colors, conditionally visible fields, lines do not render properly, not even close. Maybe we are doing it wrong and somebody could enlighten us. This is the original report generated within PB

This is how it looks in html Trial#1End.

Another option we are working on, till we find a better solution is to run a WCF service, or an individual app which can be called from our website, to generate the reports in the desired format and allow user to download as a file.

Desperately seeking helpful resources and feedback.

Community
  • 1
  • 1
Arfath
  • 143
  • 7

3 Answers3

2

I've had some success with both method's you've indicated.

With the datawindow HTML method, I did not try it using a dataobject as complicated as the one you're trying, but by turning on the 'Generate CSS' property and some minor fiddling with the positions of various controls i was able to get a very close representation of the datawindow in HTML.

As for the download file method, what i did was to create a .net assembly using powerbuilder classic which would retrieve the report(datastore) and convert it to a PDF using ghostscript. Then I consumed this assembly in a c# web api and was able to download the file to a mobile app.

hope this helps.

dylan barone
  • 96
  • 2
  • Hi @dylan , I did find some articles suggesting your 2nd approach. Have started working on that. Coming from a PB background, generating a .net assembly from PB was easy. But now we are struggling on actually using them in our MVC website project. Could you please suggest some helpful resources that could help us get going? – Arfath May 04 '17 at 05:33
  • 1
    hi @Arfath. i don't recall finding many helpful resources online but i can try to help. when you generate the .net assembly, it creates a dll as well as pbd file(s). the pbd files contain the dataobjects. you add the dll as a reference to your project. when using pb12.5, the dll and pbd(s) are not linked so you need to manually copy them to your bin folder. – dylan barone May 05 '17 at 03:12
  • copied DLL along with all PBDs from the pb app's bin folder to my sample C# console app. most of the pb code works, except the part where i have to use a datawindow object. I'm using a datastore in my NVO's function, assigning a dataobject at runtime, depending on the passed parameter(reportType). Looks like the datawindow objects are not included in the pbd, or can't be rerenced. Any idea what we might be doing wrong? Has anybody faced this kind of a problem? – Arfath May 07 '17 at 10:45
  • 1
    here are some ideas to help troubleshoot. add a dummy dataobject to your pbl and compile. if the pbd file increased in size the dataobject is likely in there. create a test method that uses a datastore but without connecting to a db. something like, create a datastore, assign the dataobject, insert a blank row and have this method return the RowCount(), or use Describe() to return some property of the dataobject. if this works maybe its a db connection issue? try a simple embedded sql query and return the results to see if that works. – dylan barone May 07 '17 at 16:21
  • Can't thank you enough, for pointing me in the right direction. The rowcount() and describe() test failed, so I analysed my ConsoleApp(CA) using process monitor. Figured out that CA was searching for the PBD files in bin/debug folder, where i have copied the pbds into the bin folder. Copied the PBDs and now it works. thanks again. – Arfath May 08 '17 at 06:35
2

I have a function called "GuardarAExcel2()" which uses a step datawindow called "d_filafichero". With this function you generate an excel with the same visual aspect as your datawindow. I hope it helps you:

I did the translation of my comments, I hope you understand. I'm sorry for my English.

Example of use:

GuardarAExcel2( dw_1, "c:\Report.xls")
GuardarAExcel2( dw_1, "c:\Report.html")

enter image description here

enter image description here

// This function activates CSS in the datawindow to export in Excel format, 
// doing this will maintain the visual appearance of the dw in excel.

// ***********
// TO CONSIDER
// ***********
// - If there are overlapping column or compute type controls (one above the other), 
//   the SaveAs will only record the one above it. Use the setPosition() function if possible:
//     > Example: dw.setPosition( 'campo_t', 'header', true )
// - The objects must be in the Layer: Band
// - HELP to apply special formats:
//     > http://codesnipers.com/?q=excel-compatible-html
//     > https://stigmortenmyre.no/mso/html/excel/xlconformulas.htm

String ls_GenerateCSS
String ls_Border
String ls_NoWrap
String ls_Fila
String ls_Valor
String ls_Temp
String ls_CabeceraHTML
String ls_PieHTML
String ls_NumToStr

Long i
Long ll_TRow
Long ll_Pos
Long ll_PosIni
Long ll_PosFin
Long ll_Row
Long ll_Temp

Boolean lb_Actualizar

ls_CabeceraHTML = '<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40">'
ls_PieHTML = '</html>'
ls_NumToStr = 'x:str'

// Save current CSS datawindow format
ls_GenerateCSS = dwDatos.Describe("DataWindow.HTMLTable.GenerateCSS")
ls_Border = dwDatos.Describe("DataWindow.HTMLTable.border")
ls_NoWrap = dwDatos.Describe("DataWindow.HTMLTable.nowrap")

// Apply CSS format to datawindow
dwDatos.Modify("DataWindow.HTMLTable.GenerateCSS='1'")
dwDatos.Modify("DataWindow.HTMLTable.border='0'")
dwDatos.Modify("DataWindow.HTMLTable.nowrap='1'")

// Save the .xls file
dwDatos.SaveAs( spRuta, HTMLTable!, TRUE )

// Import the .xls file to "d_FilaFichero"
ds_datastore dsHTML
dsHTML = CREATE ds_datastore
dsHTML.DataObject = "d_FilaFichero"
dsHTML.ImportFile( Text!, spRuta )

// Process line by line for:
//   - Add in the header: <!DOCTYPE html>
//   - Replace "{;" by "{"
//   - If there is "visibility:hidden" replacing its value with spaces, excel does not support this and displays it
//          Example: Original: <TD NOWRAP CLASS=htmldw9C370 Style='visibility:hidden;'>0202002003</TD>
//                  Replaced: <TD NOWRAP CLASS=htmldw9C370 Style='visibility:hidden;'>          </TD>
//   - Number solution in parentheses, excel converts to negative number.
//   - Keep the leading zeros to the left, Excel them to convete numbers.
//   - Remove references to images that are not included:
//       Example: Original: <TD NOWRAP CLASS=htmldw152FC><IMG SRC="" border="0" CLASS=htmldw152FC  onClick="{return htmldw.itemClicked(0,-1,'compute_11',0,-1);}" ></TD>
//                Replaced: <TD NOWRAP CLASS=htmldw152FC></TD>
//   - Prevent text with / convert to date

dsHTML.InsertRow(1)
dsHTML.setItem( 1, 'fila', ls_CabeceraHTML )

dsHTML.InsertRow(0)
ll_TRow = dsHTML.RowCount()
dsHTML.setItem( ll_TRow, 'fila', ls_PieHTML )

For i = 1 to ll_TRow
    lb_Actualizar = False
    ls_Fila = dsHTML.getItemString( i, 'fila' )

    // Activation of CSS that is disabled by default
    If Pos( ls_Fila, '{;' ) > 0 Then
        ls_Fila = f_global_replace( ls_Fila, '{;', '{' )
        lb_Actualizar = True
    End If

    // Visibility
    If Pos( ls_Fila, 'visibility:hidden' ) > 0 or Pos( ls_Fila, 'visibility: hidden' ) > 0 Then
        ll_PosIni = Pos( ls_Fila, '>' )
        ll_PosFin = Pos( ls_Fila, '</' )
        If ll_PosIni > 0 and ll_PosFin > 0 Then
            ls_Valor = Mid( ls_Fila, ll_PosIni + 1, (ll_PosFin - ll_PosIni) - 1 )
            If Len( Trim( ls_Valor ) ) > 0 Then
                ls_Fila = f_global_replace( ls_Fila, ls_Valor, Space( Len( ls_Valor ) ) )
                lb_Actualizar = True
            End If
        End If
    End If

    // Number solution in parentheses, excel converts to negative number.
    ll_PosIni = Pos( ls_Fila, '>(' )
    ll_PosFin = Pos( ls_Fila, ')</' )
    If ll_PosIni > 0 and ll_PosFin > 0 Then
        ls_Valor = Mid( ls_Fila, ll_PosIni + 2, (ll_PosFin - ll_PosIni) - 2 )
        If isNumber( ls_Valor ) Then
            ls_Fila = f_global_replace( ls_Fila, ">(", ">&nbsp;(" )
            lb_Actualizar = True
        End If
    End If

    // Apply formatting to keep leading zeros
    ll_PosIni = Pos( ls_Fila, '>0' )
    ll_PosFin = Pos( ls_Fila, '</' )
    If ll_PosIni > 0 and ll_PosFin > 0 Then
        ls_Valor = Mid( ls_Fila, ll_PosIni + 1, (ll_PosFin - ll_PosIni) - 1 )
        If isNumber( ls_Valor ) Then
            If Dec( ls_Valor ) > 0 Then
                ls_Fila = f_global_replace( ls_Fila, ">0", " " + ls_NumToStr + ">0" )
                lb_Actualizar = True
            End If
        End If
    End If

    // Remove reference to images that are not included:
    ll_PosIni = Pos( ls_Fila, '<IMG SRC=""' )
    ll_PosFin = Pos( ls_Fila, '</' )
    If ll_PosIni > 0 and ll_PosFin > 0 Then
        ls_Valor = Mid( ls_Fila, ll_PosIni, (ll_PosFin - ll_PosIni) )
        If Len( Trim( ls_Valor ) ) > 0 Then
            ls_Fila = f_global_replace( ls_Fila, ls_Valor, "" )
            lb_Actualizar = True
        End If
    End If

    // Prevent text with / convert to date
    ll_PosIni = Pos( ls_Fila, '>' )
    ll_PosFin = Pos( ls_Fila, '</' )
    If ll_PosIni > 0 and ll_PosFin > 0 Then
        ls_Valor = Mid( ls_Fila, ll_PosIni + 1, (ll_PosFin - ll_PosIni) - 1 )
        If f_cuenta_char( ls_Valor, '/' ) = 1 Then
            ls_Fila = f_global_replace( ls_Fila, ">"+ls_Valor, " " + ls_NumToStr + ">"+ls_Valor )
            lb_Actualizar = True
        End If
    End If

    If lb_Actualizar Then
        dsHTML.setItem( i, 'fila', ls_Fila )
    End If
Next

// Save the "d_FilaFichero" as Txt with .xls extension
dsHTML.SaveAs( spRuta, Text!, FALSE )

// Restore the original CSS datawindow format
dwDatos.Modify("DataWindow.HTMLTable.GenerateCSS='" + ls_GenerateCSS + "'")
dwDatos.Modify("DataWindow.HTMLTable.border='" + ls_Border + "'")
dwDatos.Modify("DataWindow.HTMLTable.nowrap='" + ls_NoWrap + "'")

DESTROY dsHTML
Return
Eduardo G.
  • 341
  • 1
  • 7
  • Hi @eduardo-g , your function is a great piece of code. It opens up a lot of possibilities for manipulating html code generated from datawindows. though it doesn't answer my question, it did help in learning some manipulation ideas. surely, it would be more helpful to somebody out there looking out for a similar functionality. – Arfath May 04 '17 at 04:54
  • 1
    My code generates a file, but you decide the extension, it can be for example Excel or HTML. This generates many possibilities and visually has a very good result. – Eduardo G. May 04 '17 at 06:18
1

I would generate a PDF from the PowerBuilder code and display that in the browser.

Roland Smith
  • 957
  • 4
  • 7