SQL TEST

DataBase/Oracle 2013.09.02 14:12
<pre class="brush: sql">
/*
 * SyntaxHighlighter
 * 이와 같이 작성할 수 있습니다.
 */
SQL>SELECT *
      FROM DUAL;
</pre>


Posted by 뉴커리어

PB TO EXCEL

PowerBuilder 2010.02.22 23:41

Option Explicit
Private ii_page, li_maxline As Integer, li_line1h As Double, mpictdir As String, moledir As String, mdestdir As String
Private lole_pb As Object, lole_invoice As Object, li_totitem As Integer, lole_payment As Object
Private li_lastrow As Long, mdestfile As String, lole_destination As Object, ls_needdimession As String

'製作Proforma invoice主程式
Sub GetorderInfo()
    Dim li_rc, li_temp, li_this As Integer
    Dim li_ret As Integer, li_loop As Integer, lole_date_num As Object, li_datenum As Integer, mport As String, ls_payment As String
    Dim ls_ordno As String, ls_lastrow As String, lole_buyer_item As Object, mitem_id As String, ls_nation As String, ls_price_term As String
    Dim li_totgw As Double, li_totbox As Long, li_totmoney As Double, li_totcuft As Double, li_totqty As Long
    Dim ls_range As String, ls_port As String, mbuyer_id As String
    Dim ls_itemsort As String, lb_new_page As Boolean
 
    li_maxline = 33                                                 '每頁最大列數
    li_lastrow = 0                                                  '最後一筆資料所在row 位置
    Application.DisplayAlerts = False                               '讓Excel不要問東問西
    Application.ScreenUpdating = False                              '讓畫面不要閃
    Windows("proinvoice_Argentina.xls").Activate
    ls_ordno = Trim(Worksheets("invoice").Range("D7").Value)        '指定要處理的訂單號碼
   
    li_maxline = Val(Worksheets("invoice").Range("E10").Value)      '最大列數開放操作者在工作表設定
    moledir = Worksheets("invoice").Range("E11").Value              'OLE資料夾
    mdestdir = Worksheets("invoice").Range("E12").Value             '目標資料夾
    mdestfile = ls_ordno + "-sc.xls"                                '檔名產生
    ls_itemsort = InputBox("1.第一外文 2.第二外文 3.主品名", "選擇品名印出方式", "1") '品文列印選擇
    ls_needdimession = "0"  'InputBox("1:印出 其他:不印", "選擇印出外箱尺寸", "1")
   
    If Dir(Trim(mdestdir) + mdestfile) <> "" Then                   '避免目標檔還開著
       Workbooks.Open Filename:=Trim(mdestdir) + mdestfile
       Windows(mdestfile).Activate
       ActiveWorkbook.Close
    End If
   
   
    Windows("proinvoice_Argentina.xls").Activate                    '將樣本檔複製給目標檔
    Workbooks.Open Filename:="c:\bigbear_form\proinvoice_peruform.xls"
    Windows("proinvoice_peruform.xls").Activate
    ActiveWorkbook.SaveAs Filename:=mdestdir + mdestfile, FileFormat:= _
        xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
        , CreateBackup:=False
    Windows(mdestfile).Activate

    ActiveWorkbook.Close

    Workbooks.Open Filename:=mdestdir + mdestfile
    Windows("proinvoice_Argentina.xls").Activate

    Set lole_pb = CreateObject("PowerBuilder.Application")          '產生PB主物件
    li_totcuft = 0                       '合計材機
    li_totgw = 0                         '合計毛重
    li_totmoney = 0                      '合計金額
    li_totbox = 0                        '合計箱數
    ii_page = 1                          '總頁次
    If IsObject(lole_pb) Then
       lole_pb.LibraryList = Trim(moledir) + "ole.pbd"              '固定要的
       lole_pb.MachineCode = False                                  '固定要的
      
       Set lole_invoice = lole_pb.CreateObject("n_ds_order")        '產生取資料datastore物件
       li_rc = lole_invoice.of_connect                              'PB物件的funcion,連向資料庫
       If li_rc < 0 Then
          Exit Sub
       End If
       Set lole_destination = lole_pb.CreateObject("n_ds_destination") '取目的港用
       Set lole_payment = lole_pb.CreateObject("n_ds_payment")         '取付款條件用
      
       Set lole_buyer_item = lole_pb.CreateObject("n_ds_buyer_item")   '取客戶產品檔,此表主要是取成交價與包裝資料
       li_rc = lole_buyer_item.of_connect
      
       Set lole_date_num = lole_pb.CreateObject("n_ds_date_num")       '取訂單交貨日檔
       li_rc = lole_date_num.of_connect
       If li_rc < 0 Then
          Exit Sub
       End If
       mbuyer_id = "18888P"                                            '此表固定給此客戶編號使用
       li_totitem = lole_invoice.of_get_order_info(ls_ordno)           'PB物件取訂單資料的funcion,取出此單號所有訂單表頭與明細
       If li_totitem > 0 Then                                          '資料筆數
          mbuyer_id = lole_invoice.GetItemString(1, "buyer_id")
         
          Windows("proinvoice_Argentina.xls").Activate
          '*************第一頁表頭較高,先取表頭資料,取第一筆資料內容即可
          Sheets("title").Select
          Worksheets("title").Range("G12").Value = Format(Date, "mmmm dd,yyyy")  '列印日期=印表當天,格是為 September 26,2007
          mport = get_portname(lole_invoice.GetItemString(1, "loading_port"))    '裝貨港
          ls_price_term = getitem(lole_invoice.GetItemString(1, "price_term"))   '交易條件
          ls_nation = lole_invoice.GetItemString(1, "order_item_cargo_nation")   '產國
         
          ls_range = "A1:P28"                                          '考備表頭區與第一項位置產品給目標檔
          Range(ls_range).Select
          Selection.Copy
          Windows(mdestfile).Activate
          Sheets("Sheet1").Select
          ls_range = "A1"
          Range(ls_range).Select
          ActiveSheet.Paste
          li_lastrow = 28                                              '列指標停在28
         
          '填第一頁表頭
          Worksheets("Sheet1").Range("D13").Value = ls_ordno
          Worksheets("Sheet1").Range("D14").Value = lole_invoice.GetItemString(1, "buyer_ordno") '客戶訂單號
          Worksheets("Sheet1").Range("D16").Value = ls_nation                                     '產國
        
          Worksheets("Sheet1").Range("D15").Value = mport                                         '裝貨港
          ls_payment = get_payment(getitem(lole_invoice.GetItemString(1, "payment_term")))        '付款條件
          Worksheets("Sheet1").Range("D17").Value = ls_payment
          '逐一處理每筆訂單明細
          For li_this = 1 To li_totitem
              lb_new_page = False
              If ii_page > 1 And li_this > (3 + 19 * (ii_page - 1)) Then '第二頁起每頁19項產品
                 ii_page = ii_page + 1
                 lb_new_page = True
              End If
              If ii_page = 1 And li_this > 3 Then                        '第一頁只放3項就好
                  lb_new_page = True
                  ii_page = 2
              End If
             
              If lb_new_page Then                                        '如需換頁
                  Sheets("Sheet1").Select
                  ls_range = "A" + Trim(Str(li_lastrow + 1))             '加換頁碼
                  Range(ls_range).Select
                  ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell
             
                  Windows("proinvoice_Argentina.xls").Activate           '頁表頭自title2考備.title2的比較簡短
                  Sheets("title2").Select
                  ls_range = "A1:P14"
                  Range(ls_range).Select
                  Selection.Copy
                  Windows(mdestfile).Activate
                  Sheets("Sheet1").Select
                  ls_range = "A" + Trim(Str(li_lastrow + 1))
                  Range(ls_range).Select
                  ActiveSheet.Paste
                  li_lastrow = li_lastrow + 14                           '列指標加14列
              End If
         
              If li_this > 1 Then                                        '第二項開始都自title2考備單項品用樣品格
                 Windows("proinvoice_Argentina.xls").Activate            '考備後會連格線與儲存格公式都一起過來
                 Sheets("title2").Select
                 ls_range = "A14:P14"
                 Range(ls_range).Select
                 Selection.Copy
                 Windows(mdestfile).Activate
                 Sheets("Sheet1").Select
                 ls_range = "A" + Trim(Str(li_lastrow))
                 Range(ls_range).Select
                 ActiveSheet.Paste

              End If
              Windows(mdestfile).Activate
              ls_lastrow = Trim(Str(li_lastrow))
             
         
              mitem_id = lole_invoice.GetItemString(li_this, "item_id")                 '產品編號
              li_datenum = lole_date_num.of_date_num_info(ls_ordno, mitem_id)           '取交貨日名單,一張訂單可能有多個交貨日
              '**** 單一item處理不同交運日之數量明細*******************************
              If li_datenum = 1 Then
                 If li_this = 1 Then                                                    '取第一項的shipping window列到表頭
                     Worksheets("Sheet1").Range("D24").Value = lole_date_num.GetItemString(1, "order_etd_eta_shipwindow")
                 End If
                 Worksheets("Sheet1").Range("E" + ls_lastrow).Value = Format(lole_date_num.GetItemNumber(1, "order_item_num"), "######") '訂單數量
                 Worksheets("Sheet1").Range("M" + ls_lastrow).Value = Format(lole_date_num.GetItemNumber(1, "order_item_price"), "####0.000") '訂單單價
              End If
              li_ret = lole_buyer_item.of_buyer_item_info(mbuyer_id, mitem_id)  '至powerbuilder抓buyer_item客戶產品資料
              If li_ret = 1 Then
                  Worksheets("Sheet1").Range("B" + ls_lastrow).Value = getitem(lole_buyer_item.GetItemString(1, "e01"))  '客戶貨號
                  '列印品名,有三種列印方式
                  If ls_itemsort = "1" Then
                      Worksheets("Sheet1").Range("C" + ls_lastrow).Value = getitem(lole_buyer_item.GetItemString(1, "buyer_itemname")) '客戶產品檔紀錄的第一外文
                  ElseIf ls_itemsort = "2" Then
                      Worksheets("Sheet1").Range("C" + ls_lastrow).Value = getitem(lole_buyer_item.GetItemString(1, "buyer2itemname")) '客戶產品檔紀錄的第二外文
                  Else
                      Worksheets("Sheet1").Range("C" + ls_lastrow).Value = getitem(lole_buyer_item.GetItemString(1, "c10"))            '產品檔的品文
                  End If
                     
                  Worksheets("Sheet1").Range("F" + ls_lastrow).Value = getnum(lole_buyer_item.GetItemNumber(1, "e09"))  'iner qty內箱數量
                  Worksheets("Sheet1").Range("G" + ls_lastrow).Value = getnum(lole_buyer_item.GetItemNumber(1, "e17"))  ' qty  ctn外箱數量
                  Worksheets("Sheet1").Range("H" + ls_lastrow).Value = getnum(lole_buyer_item.GetItemNumber(1, "e15"))  ' gw  單箱毛重
                  Worksheets("Sheet1").Range("I" + ls_lastrow).Value = getnum(lole_buyer_item.GetItemNumber(1, "e16"))  ' cuft 單箱材積
                  '統計合計數,這些儲存格都有計算公式
                  li_totgw = li_totgw + Worksheets("Sheet1").Range("K" + ls_lastrow).Value       '箱數*單箱毛重
                  li_totcuft = li_totcuft + Worksheets("Sheet1").Range("L" + ls_lastrow).Value   '箱數*單箱材積
                  li_totmoney = li_totmoney + Worksheets("Sheet1").Range("N" + ls_lastrow).Value 'N格自有計算公式=單價*數量
                  li_totbox = li_totbox + Worksheets("Sheet1").Range("J" + ls_lastrow).Value     '箱數=訂單數/ctn外箱數量
                 
                  Worksheets("Sheet1").Range("O" + ls_lastrow).Value = getitem(lole_buyer_item.GetItemString(1, "e25"))  'Brand
                  Worksheets("Sheet1").Range("P" + ls_lastrow).Value = getitem(lole_buyer_item.GetItemString(1, "e21"))  'EAN碼
                  If Worksheets("Sheet1").Range("P" + ls_lastrow).Value = "" Then                '若EAN碼空白,則嘗試找UPC
                     Worksheets("Sheet1").Range("P" + ls_lastrow).Value = getitem(lole_buyer_item.GetItemString(1, "upc")) 'UPC碼
                  End If
                 
              End If
              Worksheets("Sheet1").Range("D" + ls_lastrow).Value = mitem_id
              li_lastrow = li_lastrow + 1
              
          Next
         
          '列出合計數,並處理其格式Format
          ls_lastrow = Trim(Str(li_lastrow))
          Worksheets("Sheet1").Range("J" + ls_lastrow).Value = li_totbox
          Worksheets("Sheet1").Range("K" + ls_lastrow).Value = li_totgw
         
          Worksheets("Sheet1").Range("L" + ls_lastrow).Value = li_totcuft
          Worksheets("Sheet1").Range("N" + ls_lastrow).Value = li_totmoney
          ls_range = "J" + ls_lastrow + ":L" + ls_lastrow
          Range(ls_range).Select
          Selection.NumberFormatLocal = "#,##0.00_ "
          With Selection.Font
            .Name = "Arial"
            .Size = 10
          End With
         
          ls_range = "N" + ls_lastrow + ":N" + ls_lastrow
          Range(ls_range).Select
          Selection.NumberFormatLocal = "#,##0.000_ "
          With Selection.Font
            .Name = "Arial"
            .Size = 10
          End With
         
          li_lastrow = li_lastrow + 3
          '結尾需要7列以上,剩餘不足7列須加跳頁碼
          If (li_this - (3 + 19 * (ii_page - 2))) > 7 Then  '判斷結尾前是否需要換頁
                ls_range = "A" + Trim(Str(li_lastrow))
                Range(ls_range).Select
                ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell
                li_lastrow = li_lastrow + 3
                ii_page = ii_page + 1
          End If
         
          '考備結尾樣本,並填入結尾資料
          Windows("proinvoice_Argentina.xls").Activate
          Sheets("title2").Select
          ls_range = "B19:L28"
          Range(ls_range).Select
          
          Selection.Copy
          Windows(mdestfile).Activate
          
          Sheets("Sheet1").Select
          ls_range = "B" + Trim(Str(li_lastrow))
          Range(ls_range).Select
          ActiveSheet.Paste
          ls_lastrow = Trim(Str(li_lastrow))
          Worksheets("Sheet1").Range("B" + ls_lastrow).Value = "Country of Origin: " + UCase(ls_nation) + " / Loading Port: " + mport
          '置換頁數字串,並設為粗體,每頁為33列,頁數在每頁第5列
          For li_this = 1 To ii_page
              Worksheets("Sheet1").Range("N" + Trim(Str(5 + 33 * (li_this - 1)))).Value = "Page : " & li_this & " of " & ii_page
              Range("N" + Trim(Str(5 + 33 * (li_this - 1)))).Select
              Selection.Font.Bold = True
          Next
          ls_lastrow = Trim(Str(li_lastrow + 1))
          Worksheets("Sheet1").Range("B" + ls_lastrow).Value = "Total amount :    " + Mid(lole_invoice.GetItemString(1, "currency"), 1, 2) + "$"
          Worksheets("Sheet1").Range("D" + ls_lastrow).Value = li_totmoney
          ls_lastrow = Trim(Str(li_lastrow + 2))
          Worksheets("Sheet1").Range("D" + ls_lastrow).Value = li_totgw
          ls_lastrow = Trim(Str(li_lastrow + 3))
          Worksheets("Sheet1").Range("D" + ls_lastrow).Value = li_totcuft
         
       Else
           MsgBox ("未讀取任何資料")
       End If
       '與資料庫離線
       lole_date_num.of_disconnect
       lole_buyer_item.of_disconnect
       lole_invoice.of_disconnect
      
    Else
       MsgBox ("cannot create powerbuilder object")
    End If
    '釋放物件記憶體
    Set lole_buyer_item = Nothing
    Set lole_date_num = Nothing
    Set lole_invoice = Nothing
    Set lole_payment = Nothing
    Set lole_pb = Nothing
   
    Windows(mdestfile).Activate
    '將焦點移到左上角
    Sheets("Sheet1").Select
    ActiveWindow.LargeScroll ToRight:=-1
    ActiveWindow.ScrollRow = 1
    Range("A1").Select
    ActiveWorkbook.Save
    Windows("proinvoice_Argentina.xls").Activate
'    ActiveWorkbook.Close '報表產生完畢後,關閉VBA程式檔
   

End Sub


출처] http://www.idon.com.tw/vba/proinvoice_Argentina_vba.htm
Posted by 뉴커리어

PB TO XLS

PowerBuilder 2010.02.22 23:37

Posted by 뉴커리어

질문)
여러개의 DW를 하나의 엑셀로 저장하고 싶은데 예를 들어 하나의 엑셀파일에서 시트별로..
DW_1 은 SHEET1
DW_2 는 SHEET2
DW_3 은 SHEET3
이렇게 만들고 싶은데 방법 좀 가르쳐 주십시오.


답변)
직접 엑셀 쉬트에 값을 넣는 방법을 사용하셔야 할것같습니다. 아래스크립터를 참고하시기 바랍니다.
1. Global External Function 선언
FUNCTION UInt FindWindowA( Ulong className, string winName )  LIBRARY "user32.dll"
FUNCTION UInt SetFocus( int winHand )  LIBRARY "user32.dll"
2. 해당 스크립트 작성
OleObject     myOleObject
int           i_Result,rc
String        excel_title
UInt          excel_handle
myOleObject = Create OleObject //ole 오브젝트 생성
i_Result = myOleObject.ConnectToNewObject( "excel.application" )
// 엑셀에 연결
excel_title = myOleObject.Application.Caption
myOleObject.Application.Visible = True
excel_handle = FindWindowA( 0, excel_title )
SetFocus( excel_handle )
myOleObject.WorkBooks.Open("c:\mmm.xls")
// c:\mmm.xls 을 불러온다.
myOleObject.WindowState = 2
// 엑셀윈도우의 상태 지정 1-normal, 2-min, 3-max
//sheet1의 A1셀의 데이터를 불러올때
double a // 엑셀 데이터타입에 맞는 형을 선언
a = myOleobject.application.workbooks(1).worksheets(1).Range( "A1" ).Value
//만약 sheet2에 접근하려면 아래처럼 합니다.
//a = myOleobject.application.workbooks(1).worksheets(2).Range( "A1" ).Value
//sheet1의 A4에 'Park'이라는 값을 넣으려면 아래처럼
myOleobject.application.workbooks(1).worksheets(1).Range( "A4" ).Value = 'Park'
myoleobject.application.workbooks(1).Save() //엑셀저장
myoleobject.application.workbooks(1).SaveAs("c:\mmm3.xls")
//mmm3.xls로 새이름으로 저장하는 방법
//다른 엑셀명령도 위처럼 사용가능하지 않을까 싶네요 (여러가지 해보지를 못해서..)
myoleobject.DisConnectObject() //연결종료
Destroy myoleobject //오브젝트 제거




//셀병합

myole***.application.workbooks(1).worksheets(1).Range("D6:E6").MergeCells = True
Posted by 뉴커리어


The sample copies Datawindow Row 1 ,Column 1 to MS Excel Row 1 , Column 1 etc... 

The script can be modified to copy only the desired columns/rows.  A template xls file could be opened and saved to a different xls filename, preserving the template.   The sample pbl provided uses the EAS Demo DB's Employee table.


The following script code is located in the "Copy DW Data to Excel using OLE Automation"
button , on the only window in the pbl. The example pbl is currently setup to open the file "c:\file1.xls"

Modify the path/filename or create file1.xls in the c:\ directory. The file can be an empty xls file.   Uncomment the Save or SaveAs lines accordingly.
 

long numcols , numrows , c, r
OLEObject xlapp , xlsub
int ret

// Set the # of columns and rows to process
// Currently Set to copy the entire DW

numcols = long(dw_1.Object.DataWindow.Column.Count)
numrows = dw_1.RowCount()

// Create the oleobject variable xlapp
xlApp = Create OLEObject

// Connect to Excel and check the return code
ret = xlApp.ConnectToNewObject( "Excel.Sheet" )
if ret < 0 then
MessageBox("Connect to Excel Failed !",string(ret))
return
end if

// Open a particular Excel file
xlApp.Application.Workbooks.Open("c:\file1.xls") //,false,true
// Make Excel visible
xlApp.Application.Visible = true

// Resolve the Excel reference once
// This technique shortens the script and improves performance
xlsub = xlapp.Application.ActiveWorkbook.Worksheets[1]

// Loop thru the Datawindow and Excel sheet
// The for/next loop copies all rows for each column
For c = 1 to numcols
For r = 1 to numrows
xlsub.cells[r,c] = dw_1.object.data[r,c]

Next
Next

// Save opened file
//xlApp.Application.Activeworkbook.Save()

// SaveAs a different filename
//xlApp.Application.Activeworkbook.SaveAs("c:\file2.xls")

// clean up
xlApp.DisConnectObject()
Destroy xlapp

Posted by 뉴커리어