PB匯入Excel

 long ll_excelok,ll_rtn,ll_rowcount,ll_row

string ls_emp_no,ls_emp_name,ls_emp_sex,ls_emp_age,ls_emp_birthday

string ls_pathname,ls_filename

integer li_emp_age

date ld_emp_birthday

oleobject ExcelServer

pointer oldpointer

delete from EMP_M_COPY;

oldpointer = setpointer(hourglass!)

ExcelServer = create Oleobject

ll_excelok = ExcelServer.connecttonewobject("Excel.application")

if ll_excelok  <> 0 then

       messagebox("提示!","無法串連EXCEL,請確認本機是否安裝了EXCEL")

end if

ll_rtn = getfileopenname('開啟EXCEL檔案',ls_pathname,ls_filename,'XLS','EXCEL檔案(*.xls),*.xls')

if ls_filename = '' then return

ExcelServer.workbooks.open(ls_pathname,0,0)

ExcelServer.application.visible = false

//excelserver.workbooks.add()

//ExcelServer.application.workbooks(1).worksheets(1) //設定第一個工作表為啟用工作表

//ExcelServer.application.visible = true //顯示EXCEL視窗

//ExcelServer.Caption='pb轉excel' //更改標題

ll_rowcount = excelserver.activesheet.usedRange.rows.count

for ll_row = 1 to ll_rowcount
       ls_emp_no = string(excelserver.activesheet.cells(ll_row + 1,1).value)
       ls_emp_name = string(excelserver.activesheet.cells(ll_row + 1,2).value)
       ls_emp_sex = string(excelserver.activesheet.cells(ll_row + 1,3).value)
       ls_emp_age = string(excelserver.activesheet.cells(ll_row + 1,4).value)
//       ls_emp_birthday = string(excelserver.activesheet.cells(ll_row + 1,5).value,'yyyy/mm/dd')
       ls_emp_birthday = string(excelserver.activesheet.cells(ll_row + 1,15).value)
//       li_emp_age = integer(ls_emp_age)
//       ld_emp_birthday = date(ls_emp_birthday)

       st_1.text = "正在匯入第: " + string(ll_row) + "筆資料"
//messagebox( ls_emp_no,ls_emp_name + ls_emp_sex + ls_emp_age + ls_emp_birthday)
       insert into dele (a,b,c,d,e) values(:ls_emp_no,:ls_emp_name,:ls_emp_sex,:ls_emp_age,:ls_emp_birthday);

next

 

ExcelServer.quit //exit

SetPointer(oldpointer)

ExcelServer.DisconnectObject()

DESTROY ExcelServer

 
dw_1.settransobject(sqlca)
dw_1.retrieve()
 

if dw_1.rowcount() = 0 then

       rollback;

       messagebox("提示","匯入失敗")

       st_1.text ="匯入失敗!"

else

       commit;

       messagebox("提示","匯入成功")

       st_1.text ="匯入成功!"

end if