PB import 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 ("prompt! "," Cannot connect to EXCEL. Check whether EXCEL is installed on the local machine ")

End if

Ll_rtn = getfileopenname ('Open EXCEL file', ls_pathname, ls_filename, 'xls ', 'excel file (*. 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) // set the first worksheet to activate the worksheet

// ExcelServer. application. visible = true // display the EXCEL window

// ExcelServer. Caption = 'pb to excel '// change the title

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 = "importing" + string (ll_row) + "pen data"
// 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 ("prompt", "Import failed ")

St_1.text = "Import failed! "

Else

Commit;

Messagebox ("prompt", "imported successfully ")

St_1.text = "Import successful! "

End if