Solved

Export power builder reports to Excel

Posted on 2002-03-29
14
1,708 Views
Last Modified: 2013-12-26
How to convert any type of power builder reports to excel or document file as it is?
Note : The report may include foreground objects and bitmap objects.
0
Comment
Question by:dhanush
14 Comments
 
LVL 4

Expert Comment

by:Bhatti
Comment Utility
Actually when you will save your report as excel-sheet then the data will be transfered to the excel but not the objects and bitmaps.

If you can write a macro to adjust the objects and bitmaps to put in the excel on openwindow event of the sheet may this help you.


May be there exist another trick but I tested to save report in excel with bitmaps but it transfred only data.
I used SaveAS() function.

Best regards

Bhatti
0
 

Author Comment

by:dhanush
Comment Utility
Hi Bhatti,

SaveAs() function will save only the data. But I want the exact format as preview the datawindow. If you find any coding, let me know.
1
 

Author Comment

by:dhanush
Comment Utility
Hi Bhatti,

SaveAs() function will save only the data. But I want the exact format as preview the datawindow. If you find any coding, let me know.
0
 

Author Comment

by:dhanush
Comment Utility
Hi Bhatti,

SaveAs() function will save only the data. But I want the exact format as preview the datawindow. If you find any coding, let me know.
0
 

Expert Comment

by:fazar
Comment Utility
If you are using PB v6.0 or higher so try the function  SAVEASASCII() instead of saveas() this will help you concerning the data format that you have in the report but concerning the bitmap and foreground it is not applicable.

regards
fazar
0
 

Author Comment

by:dhanush
Comment Utility
Thanks for your comments. I am looking for better answer.
0
 

Expert Comment

by:pjaishankar
Comment Utility
GIF or TIFF or PDF drivers will solve this requirement. Some of the trial version available in  the Internet. U can download and configure that as one printer. While printing the DW, select that driver as default printer. Your output will be saved as TIFF or PDF file. It will be exact look of your report.

Or save it as PSR file and open it in PSR viewer.

0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 4

Expert Comment

by:Bhatti
Comment Utility
Hello pjaishanker,

dhanush want to see his PB-Report in Excel not in pdf file or other. If it is possible to open a pdf file created from PB in Excel like PB-Report?

0
 

Author Comment

by:dhanush
Comment Utility
Hi Jaisankar,

Thanks for your comments.

Customer wants to do some amendments after preparing the report. I dont think it is not possible to edit the PDF file. Even it is possible, it will not be easier than excel file. I have already used that PDF driver.

I am trying to do a generalized program to export into excel file.

Waiting for comments.........


0
 

Expert Comment

by:sunpatil
Comment Utility
you have to buy excel converter which you can purhcase.These utilites are really useful.

regards

sunpatil
0
 
LVL 4

Expert Comment

by:Bhatti
Comment Utility
I find this script and solution in Internet.

Try to save you datawindow as HTMLTable! with SaveAs() function and then With OLE convert to Excel:

if dw_1.SaveAs(docname, HTMLTable!, True) = -1 then
    MessageBox("Warning", "Unable to export data. Error writing to file!", Exclamation!)
    return
end if


// Convert HTML file to Excel native format
OLEObject excel
excel = CREATE OLEObject
if excel.ConnectToObject(docname) = 0 then
    excel.application.DisplayAlerts = FALSE
    excel.application.workbooks(1).Parent.Windows(excel.application.workbooks(1).Name).Visible = True
excel.application.workbooks(1).saveas(docname, 39)
excel.application.workbooks(1).close()
end if

DESTROY excel
// done

May this help to solve.


Bhatti
0
 
LVL 1

Expert Comment

by:kathotia
Comment Utility
here is your answer to your question:

http://gethelp.devx.com/techtips/pb_pro/10min/10min1298.asp

you can visit this url and i hope you will be satisfied.
0
 
LVL 2

Expert Comment

by:AFI
Comment Utility
Hello, my solution is similar to Bhatti.
What I recoment is a little more complicate but interesting, make a template in Excel of the report you want, using or not a Macros sending the information you retrieve from your DB... I'll show you an example in Excel I recently made sorry but I don't have time to edit the script to show you more simply. Where you saw o_excel.Run("") I was calling a macro. FormulaR1C1 is the function us to write in Excel cells.

OLEObject       o_excel
Long                  ll_status, linea
Double            ld_thora
Integer            SheetCount, li_i, li_FileNum      
String            NewSheet, LastSheet, Dia, pb_find, &
                        ls_doc, ls_hora
DateTime            Hoy


Select SYSDATE Into :Hoy From DUAL ;

o_excel = CREATE OLEObject

If Not gf_find_app("Microsoft Excel", This) Then
      ll_status = o_excel.ConnectToNewObject ("EXCEL.Application")
Else
      ll_status = o_excel.ConnectToObject("", "EXCEL.Application")
End If

If ll_status <> 0 Then
      MessageBox("Error", ll_status)
      Return
Else            
      CHOOSE CASE a_generar
            CASE "semanal"
                  String      ls_codigo, ls_tipo, ls_desc, ls_semana
                  Date            lunes, viernes, domingo
                  DateTime ldt_fecha
                  Double      ld_hora
                  Integer      li_pos
                  
                  
                  ld_thora = 0
                  
                  ls_doc = g_parm.xsres + g_parm.xnres + g_consultor.inicial + ".xls"
                  If Not FileExists(ls_doc) Then //Resumen Semanal
                        If Not FileExists(g_parm.xores) Then //Resumen Semanal Plantilla
                              MessageBox("Falta Plantilla", "El archivo plantilla del resumen semanal no se ha encontrado en :" + g_parm.xores)
                              GoTo Fuera
                        End If
                        o_excel.Workbooks.Open(g_parm.xores)
                        o_excel.Run("pb_saveas", ls_doc)
                  Else
                        li_FileNum = FileOpen(ls_doc, StreamMode!, Read!, LockRead!, Append!)                        
                        FileClose(li_FileNum)
                        
                        If li_FileNum = -1 Then
                              msg_tray("Informacisn", "El archivo " + ls_doc + " se encuentra abierto, " + &
                                                "cierrelo y vuelva a ejecutar la operacisn.", "info.bmp", false, 0)
//                              MessageBox(, )
                              GoTo Fuera
                        End If
                              
                        o_excel.Workbooks.Open(ls_doc)
                  End If
                        
//-------o_excel.Workbooks.Add
                  o_excel.Visible = 1
                  
                  ls_semana = String( o_excel.Run("pb_week_select", String(Hoy, "mm/yyyy")) )
                  /****** Descomposicisn de la semana ******/
                  ls_semana = Mid(ls_semana, Pos(ls_semana, "del") + 4)
                  li_pos = Pos(ls_semana, "al")
                  lunes = Date(Left(ls_semana, li_pos - 2))
                  viernes = Date(Mid(ls_semana, li_pos + 3))
                  domingo = RelativeDate(viernes, 2)
                  ls_semana = "Semana: " + String(lunes, "dd-mmm") + " al " + String(viernes, "dd-mmm")

                  /***************/
                  o_excel.Run("pb_asistente", "escribir")
                  /*************************      ENCABEZADO      *************************/
                  o_excel.Range("G8").Select // Dma actual
                  o_excel.ActiveCell.FormulaR1C1 = String(Hoy, "dd-mmm-yy")
                  o_excel.Range("B12").Select // Inicio y fin de la semana
                  o_excel.ActiveCell.FormulaR1C1 = ls_semana
                  o_excel.Range("B14").Select
                  o_excel.ActiveCell.FormulaR1C1 = g_consultor.nombre + " (" + g_consultor.inicial + ")"
                  /*************************      ENCABEZADO      *************************/

                  // Inicio Recupero la informacisn de las Actividades de la semana seleccionada.
                   DECLARE cur_actividad CURSOR FOR  
                    SELECT "TAREA"."CODIGO",  
                                    "TAREA"."TIPO",  
                                    "TAREA"."DESC",  
                                    "REGISTRO"."FECHA",  
                                    "REGISTRO"."HORAS"  
                         FROM "REGISTRO",  
                                    "ASIGNACION",  
                                    "TAREA"  
                        WHERE ( "REGISTRO"."COD_ASIG" = "ASIGNACION"."COD_ASIG" ) and  
                                    ( "REGISTRO"."CONSULTOR" = "ASIGNACION"."CONSULTOR" ) and  
                                    ( "ASIGNACION"."TAREA" = "TAREA"."CODIGO" ) and  
                                    ( ( "REGISTRO"."CONSULTOR" = :g_consultor.cod_nt ) AND  
                                    ( "REGISTRO"."FECHA" between :lunes and :domingo ) )
                        ORDER BY "REGISTRO"."FECHA" ASC ;

                  Open cur_actividad ;
                  
                  Do While sqlca.sqlcode = 0
                        Fetch cur_actividad into :ls_codigo, :ls_tipo, :ls_desc, :ldt_fecha, :ld_hora ;
                        If sqlca.sqlcode = 100 Then Exit
                        
                        
//                        o_excel.Run("pb_escribir_semana", ls_codigo, ls_tipo, ls_desc, String(Date(ldt_fecha)), String(ld_hora))
                        
                        // Encuentro el registro del dma de la semana y obtengo su posision
                        Dia = gf_dayname(Date(ldt_fecha))
                        
                        If Dia <> is_day_line Then
                              pb_find = String(o_excel.Run("pb_find", Dia) )
                              li_i = Pos(pb_find, "$")
                              If Lower(Dia) = Lower(Left(pb_find, li_i - 1)) Then // Estoy en el lugar correcto se asume columna 'B'
                                    li_i = Pos(pb_find, "$", li_i + 1)
                                    linea = Long(Mid(pb_find, li_i + 1))
                              End If                  
                        End If            
                        
                        ii_day_line ++
                        
                        If ii_day_line > 6 Then
                              o_excel.Range("C" + String(linea + 1)).Select
                              o_excel.Selection.EntireRow.Insert                              
                        End If
                        is_day_line = Dia
                        
                        o_excel.Range("C" + String(linea)).Select //Actividad
                        o_excel.ActiveCell.FormulaR1C1 = ls_codigo
                        
                        o_excel.Range("D" + String(linea)).Select //Tipo
                        o_excel.ActiveCell.FormulaR1C1 = ls_tipo
                        
                        o_excel.Range("E" + String(linea)).Select //Descripcisn
                        o_excel.ActiveCell.FormulaR1C1 = ls_desc
                        
                        o_excel.Range("F" + String(linea)).Select //Horas                        
                        ls_hora = wf_calc_hora(ld_hora)                        
                        o_excel.ActiveCell.FormulaR1C1 = ls_hora
                        
                        linea++
                        ld_thora += ld_hora
                  Loop
                  
                  Close cur_actividad ;                  
            
                  If linea > 0 Then
                        o_excel.Range("B" + String(linea)).Select
                        pb_find = String(o_excel.Run("pb_find", "Actividad") )
                        li_i = Pos(pb_find, "$")
                        If "actividad" = Lower(Left(pb_find, li_i - 1)) Then // Estoy en el lugar correcto se asume columna 'B'
                              li_i = Pos(pb_find, "$", li_i + 1)
                              linea = Long(Mid(pb_find, li_i + 1))
                              o_excel.Range("C" + String(linea)).Select //Nota - Actividad
                              o_excel.ActiveCell.FormulaR1C1 = "Csdigo de Actividad (p.e. " + String(ls_codigo) + ")"
                              
                              o_excel.Range("F" + String(linea - 2)).Select //Total
                              ls_hora = wf_calc_hora(ld_thora)                        
                              o_excel.ActiveCell.FormulaR1C1 = ls_hora
                        End If                        
                        
                        o_excel.Run("pb_att_msg", "Resumen Semanal", "Hola " + &
                                                Left(g_consultor.nombre, Pos(g_consultor.nombre, " ") - 1) + &
                                                ", espero haberte facilitado un poco la tarea, azn asi " + &
                                                "revisa que todo este bien.")
                        o_excel.Run("pb_asistente", "correcto")
                  Else
                        o_excel.Run("pb_asistente", "att_menor")
                        o_excel.Run("pb_att_msg", "Resumen Semanal", &
                                                Left(g_consultor.nombre, Pos(g_consultor.nombre, " ") - 1) + &
                                                ", no tienes registradas tus actividades para esta fecha en la " + &
                                                "Base de Datos.  Verifica que esto sea correcto.")
                  End If
                  
/*                  o_excel.pb_saveas(path)*/
                  // Fin
/******************************************************************************************/                  
            CASE "quincenal"
                  MessageBox("quincenal", "Azn no generado")
/******************************************************************************************/
            CASE "hconsultor"
                  Int            li_mes
                  String      ls_mesano, ls_pmia, ls_pdesc, ls_ptarea, ls_hdia
                  Double      ld_hhora
                  Long            ll_pasign
                  Boolean      lb_hay = false
               
                  
                  ls_doc = g_parm.xsinc + g_parm.xninc + g_consultor.inicial + ".xls"
                  If Not FileExists(ls_doc) Then //Informe de Horas Consultor
                        If Not FileExists(g_parm.xoinc) Then //Informe de Horas Consultor Plantilla
                              MessageBox("Falta Plantilla", "El archivo plantilla del resumen semanal no se ha encontrado en :" + g_parm.xoinc)
                              GoTo Fuera
                        End If
                        o_excel.Workbooks.Open(g_parm.xoinc)
                        o_excel.Run("pb_saveas", ls_doc)
                  Else      
                        li_FileNum = FileOpen(ls_doc, StreamMode!, Read!, LockRead!, Append!)                        
                        FileClose(li_FileNum)
                        
                        If li_FileNum = -1 Then
                              msg_tray("Informacisn", "El archivo " + ls_doc + " se encuentra abierto, " + &
                                                "cierrelo y vuelva a ejecutar la operacisn.", "info.bmp", false, 0)
//                              MessageBox(, )
                              GoTo Fuera
                        End If                        
                        
                        o_excel.Workbooks.Open(ls_doc)
                  End If
                  
                  o_excel.Range("B3").Select // Consultor
                  o_excel.ActiveCell.FormulaR1C1 = g_consultor.nombre                  
                  o_excel.Visible = 1
                  
                  o_excel.Run("pb_asistente", "comparar")
                  
                  li_mes = Int(o_excel.Run("pb_month_select", g_parm.xanno))
                  /*************** AGRUPO LOS PROYECTO ******************/
                  ls_mesano = string(li_mes, "00") + g_parm.xanno
                  
                  DEClARE cur_proy CURSOR FOR
                    SELECT DISTINCT      "PROYECTO"."COD_PROY",
                                    "PROYECTO"."DESC",
                                    "TAREA"."CODIGO",
                                    "ASIGNACION"."COD_ASIG"
                         FROM "PROYECTO",  
                                    "REGISTRO",  
                                    "TAREA",  
                                    "ASIGNACION"  
                        WHERE ( "TAREA"."COD_PROY" = "PROYECTO"."COD_PROY" ) and  
                                    ( "ASIGNACION"."TAREA" = "TAREA"."CODIGO" ) and  
                                    ( "REGISTRO"."COD_ASIG" = "ASIGNACION"."COD_ASIG" ) and  
                                    ( "REGISTRO"."CONSULTOR" = "ASIGNACION"."CONSULTOR" ) and
                                    ( ( TO_CHAR("REGISTRO"."FECHA", 'mmyyyy') = :ls_mesano ) and  
                                    ( "REGISTRO"."CONSULTOR" = :g_consultor.cod_nt ) )             ;
                  Open cur_proy ;
                  
                  Do While sqlca.sqlcode = 0
                        Fetch cur_proy into :ls_pmia, :ls_pdesc, :ls_ptarea, :ll_pasign ;
                        If sqlca.sqlcode = 100 Then Exit
                        
                        ld_thora = 0                  
                  
                        /*************** HORAS POR DIA Y PROYECTO ******************/
                        DEClARE cur_hora CURSOR FOR
                          SELECT TO_CHAR("REGISTRO"."FECHA", 'dd'),
                                            "REGISTRO"."HORAS"
                               FROM "REGISTRO"
                              WHERE ( "REGISTRO"."COD_ASIG" = :ll_pasign ) and
                                          ( "REGISTRO"."CONSULTOR" = :g_consultor.cod_nt ) and
                                          ( TO_CHAR("REGISTRO"."FECHA", 'mmyyyy') = :ls_mesano ) ;
                        Open cur_hora ;
                  
                        Do While sqlca.sqlcode = 0
                              Fetch cur_hora into :ls_hdia, :ld_hhora ;
                              If sqlca.sqlcode = 100 Then Exit
                              
                              lb_hay = true
                              
                              ls_hora = wf_calc_hora(ld_hhora)
                              
                              If Left(ls_hdia, 1) = "0" Then ls_hdia = Right(ls_hdia, 1)
                              
                              o_excel.Run("pb_escribir_hora", ls_pmia + " " + ls_pdesc + "," + ls_ptarea + "," + ls_hdia + "," + ls_hora)
                              
//                              ld_thora += ld_hhora
                        Loop
                        Close cur_hora ;
                        
//                        ls_hora = wf_calc_hora(ld_thora)                        
//                        o_excel.ActiveCell.FormulaR1C1 = ls_hora
                        
                  Loop
                  Close cur_proy ;
                  
                  o_excel.Run("pb_totales")
                  
                  If lb_hay = true Then
                        o_excel.Run("pb_att_msg", "{ul 1} Informe MIA por Consultor {ul 0}", "Hola " + &
                                                Left(g_consultor.nombre, Pos(g_consultor.nombre, " ") - 1) + &
                                                ", espero haberte facilitado un poco la tarea, azn asi " + &
                                                "revisa que todo este bien.")
                        o_excel.Run("pb_asistente", "correcto")
                  Else
                        o_excel.Run("pb_asistente", "att_menor")
                        o_excel.Run("pb_att_msg", "{ul 1} Informe MIA por Consultor {ul 0}", &
                                                Left(g_consultor.nombre, Pos(g_consultor.nombre, " ") - 1) + &
                                                ", no tienes registradas tus actividades para esta fecha en la " + &
                                                "Base de Datos.  Verifica que esto sea correcto.")
                  End If
                  
                  o_excel.Run("pb_asistente", "")
      END CHOOSE      
End If

Fuera:

o_excel.DisconnectObject()
Destroy o_excel

//I recomend you to install the Visual Basic help for Excell or MSOffice.
0
 

Accepted Solution

by:
SpideyMod earned 0 total points
Comment Utility
PAQ'd and points refunded

SpideyMod
Community Support Moderator @Experts Exchange
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Sync project in eclipse locally 2 113
eclipse ide not starting 16 75
How to debug CodeIgniter (PHP) 7 173
Netbeans Open project initial dir 5 32
Jaspersoft Studio is a plugin for Eclipse that lets you create reports from a datasource.  In this article, we'll go over creating a report from a default template and setting up a datasource that connects to your database.
How to install Selenium IDE and loops for quick automated testing. Get Selenium IDE from http://seleniumhq.org (http://seleniumhq.org) Go to that link and select download selenium in the right hand columnThat will then direct you to their downlo…
The viewer will learn how to use NetBeans IDE 8.0 for Windows to connect to a MySQL database. Open Services Panel: Create a new connection using New Connection Wizard: Create a test database called eetutorial: Create a new test tabel called ee…
THe viewer will learn how to use NetBeans IDE 8.0 for Windows to perform CRUD operations on a MySql database.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now