pb将excel文件导入到数据库

$PBExportHeader$f_sc_xls2dw.srf
global type f_sc_xls2dw from function_object
end type

forward prototypes
global function integer f_sc_xls2dw (datawindow pdw, string pxls)
end prototypes

global function integer f_sc_xls2dw (datawindow pdw, string pxls);// 函数名称： f_sc_xls2dw
// 函数功能： EXCEL文件导入DATAWINDOW
// 2009-03-06 shupf// 2009-03-10 shupf 改变思路重写
IF NOT IsValid(pdw) THEN RETURN -1
IF IsNull(pxls) OR Upper(Right(pxls,4)) <> '.XLS' OR NOT FileExists ( pxls ) THEN RETURN -1
//----------------------------------------------------------------------------------------------
OLEObject lole_xls
Integer li_return
// 产生oleobject的实例
lole_xls = CREATE OLEObject
//连接ole对象
li_return = lole_xls.ConnectToNewObject( "excel.application" )
IF li_return < 0 THEN
MessageBox("连接失败!","连接到EXCEL失败,请确认您的系统是否已经安装EXCEL!~r~n错误代码:"+String(li_return))
RETURN -1
END IF
// 打开EXCEL文件
lole_xls.Application.Workbooks.Open(pxls)
long ll_xls_Rcount,ll_xls_Ccount
ll_xls_Rcount = lole_https://www.360docs.net/doc/b217390975.html,edRange.Rows.Count
ll_xls_Ccount = lole_https://www.360docs.net/doc/b217390975.html,edRange.Columns.Count
if ll_xls_Rcount < 2 or isnull(ll_xls_Rcount) then goto err
//列名匹配----------------------------------------------------------------------------------------------
String ls_Col[],ls_ColType[]
Long ll_Cell[]
String ls_colname,ls_temp,ls_coltext
Long ll_count, n , nn,i = 0
ll_count = Long(pdw.Describe("datawindow.column.count"))
IF IsNull(ll_count) OR ll_count < 1 THEN GOTO err
FOR n = 1 TO ll_count
ls_colname = pdw.Describe("#" + String(n) + ".Name")
ls_temp = pdw.Describe(ls_colname + ".visible")
IF ls_temp = "0" THEN CONTINUE //---------------------------
ls_coltext = Trim(pdw.Describe(ls_colname+"_t.text"))
IF IsNull(ls_coltext) OR Trim(ls_coltext) = '' THEN CONTINUE
FOR nn = 1 TO ll_xls_Ccount
ls_temp = Trim(String(lole_xls.ActiveSheet.Cells(1,nn).value))//第一行要求是列名
IF ls_temp = ls_coltext THEN EXIT //通过判断标题
NEXT
IF nn > ll_xls_Ccount OR nn < 1 THEN CONTINUE
i ++
ls_Col[i] = ls_colname//--------------------------------------------列名----------------------------
ls_ColType[i] = pdw.Describe("#" + String(n) + ".ColType")//--------类型----------------------------
ll_Cell[i] = nn//---------------------------------------------------EXCEL列序-----------------------
NEXT
ll_count = UpperBound(ls_Col[])
IF ll_count < 1 THEN GOTO err
//数据窗口赋值------------------------------------------------------------------------------------------
Long ll_row_xls = 2,ll_row_dw
String ls_data
Double ld_data
DO WHILE ll_row_xls <= ll_xls_Rcount
IF ll_row_xls = 2 THEN pdw.SelectRow(0,FALSE)
ll_row_dw = pdw.InsertRow(0)
pdw.SelectRow(ll_row_dw,TRUE)
FOR i = 1 TO

ll_count
ls_data = Trim(String(lole_xls.ActiveSheet.Cells(ll_row_xls,ll_Cell[i]).Value))
CHOOSE CASE Left(Upper(ls_ColType[i]),3)
CASE "DEC","NUM","REA","LON","INT","ULO"
ld_data = Double(ls_data)
pdw.SetItem(ll_row_dw,ls_Col[i],ld_data)
CASE ELSE
pdw.SetItem(ll_row_dw,ls_Col[i],ls_data)
END CHOOSE
NEXT
ll_row_xls ++
LOOP
//----------------------------------------------------------------------------------------------
lole_xls.Application.quit()
lole_xls.DisconnectObject()
DESTROY lole_xls
RETURN 1 //success
err:
lole_xls.Application.quit()
lole_xls.DisconnectObject()
DESTROY lole_xls
RETURN -1 //
end function