Powerbuilder - Extract Menu To Datawindow

1. Create Window, Datawindow and controls

2. Create A sample menu

3. Create window function

 public function boolean of_extract (menu am_menu, integer ai_from, integer ai_level, string as_parent);string     ls_text, ls_code  
 integer li_loop, li_count  
 Long ll_row  
 li_count = UpperBound(am_menu.Item[])  
 for li_loop = ai_from to li_count  
      ls_code = as_parent + string(li_loop,"00")  
      ls_text = am_menu.item[li_loop].Text  
      ll_row = dw_menu.InsertRow(0)  
      dw_menu.SetItem(ll_row,"menu_id", ls_code)  
      dw_menu.SetItem(ll_row,"label_menu", ls_text)  
      dw_menu.SetItem(ll_row,"level_id", ai_level)  
      dw_menu.SetItem(ll_row,"parent_id", as_parent)  
      of_extract(am_menu.Item[li_loop],1, ai_level + 1, ls_code)  
 next  
 return true  
 end function  

4. cb_extract Event Clicked()

 menu lm_main  
 if ddlb_1.text = 'm_main' then  
  lm_main = create m_main  
 else  
  lm_main = create m_sample  
 end if  
 dw_menu.reset()  
 of_extract(lm_main,1,0,'')   
 destroy lm_main  

5.See them in action

6. Cheers..



DOWNLOAD source code from 4Shared

Export Datawindow to Excel

Export Datawindow to excel files without dw2xls
First, create a global function

f_excel(datawindow idw)
int li_rtn
string xlsname,nama, ls_alamat
//Check for row(s) in datawindow
if idw.rowcount( ) <= 0 then
        messagebox(' Export Excel','No rows to export!')
        return
end if
//excel file save directory
li_rtn   =   GetFileSaveName( "Select   File",   &  
            xlsname,   nama,   "",   &  
            "Excel 97-2003 Workbook(*.xls) ,*.*,")
if idw.saveas( xlsname+'.xls' , htmltable!, TRUE ) = -1 then
    MessageBox(" Warning", "error!",Exclamation! )
    return ;
end if ;
oleobject excel ;
excel = create oleobject ;
if excel.connecttoobject(xlsname+ '.xls' ) = 0 then
   Excel.Application. DisplayAlerts = false ;
   Excel.Application. Workbooks( 1 ).Parent.Windows
   Excel.Application. workbooks( 1 ).Name ).Visible true ;
   Excel.Application. Workbooks( 1 ).SaveAs(xlsname+ '.xls', 39 ) ;
   Excel.Application. Workbooks( 1 ).close() ;
   messagebox(' Export Excel','datawindow exported succesfully!')
        run("c:\windows\ explorer. exe "+xlsname+'. xls')
end if ;
destroy excel

Sample usage : f_excel(dw_customer)

Cheers..

Import excel into datawindow

Tested in Microsoft Ofice Excel 2003

oleobject excel
long ll_hasil,ll_return,ll_count
boolean lb_cek
excel = create oleobject;
//connect to office application
ll_return = excel.connecttonewobject("excel.application")
if ll_return <> 0 then
messagebox('Peringatan','Tidak dapat mengeksekusi MS.Excel!',exclamation!)
destroy excel
return 0
end if
//open file excel (you can make this string as variable)
excel.workbooks.open( "c:\xls\contoh file excel.xls" )
excel.application.visible = false
excel.windowstate = 2
//cek rows in excel sheet with return value copy
lb_cek = excel.worksheets(3).activate
excel.worksheets(3).range("A2:Z5000").Copy //column range in excel files
ll_count = dw_1.importclipboard()
if ll_count <= 0 then
messagebox('Informasi','Data tidak ditemukan!')
end if
excel.worksheets(3).range("A10000:A10000").Copy
excel.application.quit
excel.disconnectobject()
destroy excel;

How it works?
This script open excel file, read cell and copy to clipboard. And then insert into datawindow.
You also import multiple sheet or multiple excel file.
Cheers..