PowerBuilder学习笔记之导入Excel数据

原文地址：http://blog.chinaunix.net/uid-20586802-id-3235549.html

/*****************简单的导入功能，涉及到数据类型判断*****************/

string ls_path,ls_name,ls_filter
integer li_ret
long ll_rows,ll_columns
string a[1000,100]  //导入数据行数最大1000行，列数最大100列
ls_filter = "Excel文件(*.xls),*.xls"
li_ret = GetFileOpenName("请选择后缀名为XLS的文件",ls_path,ls_name,"xls",ls_filter)
if li_ret <> 1 then return 

//建立OLE对象
OLEObject ExcelServer
ExcelServer = create OLEObject
integer li_excel
li_excel = ExcelServer.ConnectToNewObject("excel.application")
if li_excel < 0 then 
    MessageBox("错误提示","连接excel失败,检查你的系统是否安装了office!")
    return 
end if

ExcelServer.Workbooks.Open(ls_path,0,False)
ExcelServer.Application.DisplayAlerts = False
ll_rows = ExcelServer.ActiveSheet.UsedRange.Rows.Count            //取得总行数
ll_columns= ExcelServer.ActiveSheet.UsedRange.columns.Count    //取得总行数

dw_grid.reset()
if ll_rows = 0 then
    messagebox("提示!","没有Excel数据可导，请检查Excel表格后重来!")
    ExcelServer.Workbooks.close()
    ExcelServer.DisConnectObject()
    Destroy ExcelServer
    return 
end if

//导入的excel文件行数是否超过1000 行
if ll_rows >1000 then
    messagebox("错误提示!","导入的excel文件列数过大，无法导入!")
    ExcelServer.Workbooks.close()
    ExcelServer.DisConnectObject()
    Destroy ExcelServer
    return 
end if

//导入的excel文件列数与所对应的数据窗口列数不一致，则提示
integer li_columncount    //得到数据窗口的总列数,总共14列
li_columncount = integer(dw_grid.describe("datawindow.column.count"))
if ll_columns <> li_columncount - 2 then
    messagebox("提示错误!","导入的excel文件列数与所对应的数据窗口类数不一致!")
    ExcelServer.Workbooks.close()
    ExcelServer.DisConnectObject()
    Destroy ExcelServer
    return 
end if

//从excel文件导入到数据窗口,没有空行,字段,计算列字段都为字符串型
//long m,n
//for m = 1 to ll_rows
//     dw_grid.insertrow(0)
//     for n = 1 to ll_columns
//          a[m,n] = string(ExcelServer.ActiveSheet.Cells[m,n].value)
//          dw_grid.Setitem(m,n,a[m,n])
//    next
//next

//ll_null为空行数
long m,amount,ll_null
amount = 0
for m = 1 to ll_rows
    a[m,1] = String(ExcelServer.ActiveSheet.Cells[m,1].value)
    if  len(a[m,1]) <> 0 then
        amount = amount+1
    end if
next
ll_null = ll_rows - amount

long i,li_count
string ls_text
for i=(ll_null+1+1) to ll_rows   //+1+1即表中列名称算一行，从+1行读起
    li_count = dw_grid.insertrow(0)
    dw_grid.setrow(li_count)
    
    //简称
    dw_grid.object.clientcode_1[li_count] = ExcelServer.ActiveSheet.Cells[i,2].text 
    //客户编码 
    dw_grid.object.clientcode[li_count] =  ExcelServer.ActiveSheet.Cells[i,1].text 
    //数期
    ls_text = ExcelServer.ActiveSheet.Cells[i,3].text
    if not isnumber( ls_text ) and ls_text <> '' then
        messagebox("错误","第["+ string(i) + ',3]单元格必须是数字！')
        return
    else
        dw_grid.object.yyprd_bas_client_sq[li_count] = Long(ls_text)
    end if
    //信用额
    if not isnumber(ExcelServer.ActiveSheet.Cells[i,4].text) and ExcelServer.ActiveSheet.Cells[i,4].text <> '' then
        messagebox("错误","第["+ string(i) + ',4]单元格必须是数字！')
        return
    else
        dw_grid.object.yyprd_bas_client_creditamount[li_count] = Dec(ExcelServer.ActiveSheet.Cells[i,4].text)
    end if
    //所属月份
    if not isdate(ExcelServer.ActiveSheet.Cells[i,5].text) and ExcelServer.ActiveSheet.Cells[i,5].text <> '' then
        messagebox("错误","第["+ string(i) + ',5]单元格必须是日期！')
        return
    else
        dw_grid.object.months[li_count] = datetime(ExcelServer.ActiveSheet.Cells[i,5].text)
    end if
    //货币
    dw_grid.object.currency[li_count] = String(ExcelServer.ActiveSheet.Cells[i,6].text)  
    //汇率
    if not isnumber(ExcelServer.ActiveSheet.Cells[i,7].text) and ExcelServer.ActiveSheet.Cells[i,7].text <> '' then
        messagebox("错误","第["+ string(i) + ',7]单元格必须是数字！')
        return
    else
        dw_grid.object.currencyrate[li_count] = dec(ExcelServer.ActiveSheet.Cells[i,7].text)
    end if
     //销售金额
    if not isnumber(ExcelServer.ActiveSheet.Cells[i,8].text) and ExcelServer.ActiveSheet.Cells[i,8].text <> '' then
        messagebox("错误","第["+ string(i) + ',8]单元格必须是数字！')
        return
    else
        dw_grid.object.salesamount[li_count] = Dec(ExcelServer.ActiveSheet.Cells[i,8].text)
    end if
     //备注
    dw_grid.object.yyprd_cdc_sales_remarks[li_count] = String(ExcelServer.ActiveSheet.Cells[i,9].text)   
    //建立日期
    ls_text = ExcelServer.ActiveSheet.Cells[i,10].text
    if  isnumber(ls_text) and ls_text <> '' then
        messagebox("错误","第["+ string(i) + ',10]单元格必须是日期！')
        return
    else
        dw_grid.object.created_date[li_count] = datetime(ExcelServer.ActiveSheet.Cells[i,10].text)
    end if
next

messagebox("提示","导入数据成功，请点击保存按钮提交到数据库!")
ExcelServer.Workbooks.close()
ExcelServer.DisConnectObject()
Destroy ExcelServer