pb 导入excel 关闭程序出错

[second_huang]

现象是可以从EXCEL导入数据到PB数据窗口，但是在退出整个应用程序的时候，会报错PB9.0.EXE 应用程序错误
代码如下：
oleobject excelserver
excelserver=create oleobject
excelok=excelserver.connecttonewobject("excel.application")

//检查返回值，以确保已成功地连接到了Excel
if excelok <> 0 then
messagebox("信息提示","连接EXCEL失败，请检查计算机中是否安装了EXCEL!")
return
end if
li_net = GetFileOpenName("选择文件", str_savename,named,"xls","Excel文件(*.xls),*.xls")
if li_net > 0 then
if str_savename = "" then return
dw_1.settransobject(sqlca)
dw_1.reset()
// excelserver.workbooks.open(str_savename)
excelserver.workbooks.add(str_savename)
excelserver.activesheet.cells.copy
li_return = messagebox("提示信息","导入的文件是否包含表头?",Question!,YesNo! ,1)
if li_return = 1 then
i = 2
else
i = 1
end if
li_count = dw_1.importclipboard(i) //导入数据
clipboard("")

excelserver.quit()
excelserver.disconnectobject()
destroy excelserver
else
messagebox('提示信息','没有指定导入文件!')
return
end if

[dsd999]

可能和你导入excel这段程序没关系。

[second_huang]

但是我点导入按钮的时候如果选择‘否’，退出整个程序就不会报错，选择是就报错，还有从数据窗口导出的时候判断EXCEL版本，如果存成.xlsx格式的告诉我无法打开文件

[dsd999]

没用过pb，你只能一点点试了。

先注释掉一些代码，找到具体引起问题的地方。

[second_huang]

就只留下if li_net > 0 then
else
end if就出错了

[second_huang]

我看了很多相关的帖子，但是不尽相同，无论导入导出EXCEL，在退出程序的时候都报错如题错误

[second_huang]

怎么没人遇到过么？

[ribut9225]

你不应该放到剪贴板里，PB数据窗口是区分列属性，如果你在一个int类型列中放入string数据，就会报错

一般是一个单元格一个单元的读取

long ll_RowCount,ll_ColCount //总行数和总列数
long ll_Row,ll_Col
string ls_temp	//临时变量，统一转换成字符串处理，需要数据窗口的各列也都是字符串类型。如果楼主有特殊需要，则要自己处理
long ll_NewRow

ll_Row = excelserver.activesheet.UsedRange.Rows.count //总行数
ll_ColCount = excelserver.activesheet.UsedRange.Column.Count	//总列数

for ll_Row = 1 to ll_RowCount
    ll_NewRow = dw_1.InsertRow(dw_1.RowCount()+1)	//数据窗口中新增一行
   for col = 1 to ll_ColCount
      ls_temp = string(excelserver.activesheet.Cells(ll_Row,ll_Col).Value)
      dw_1.SetItem(ll_NewRow,ll_Col,ls_temp)  //数据窗口中写入数据
   next
next

[second_huang]

感谢 ribut9225的回复

还有就是导出的问题，一个问题是比如本机安装的是office2007，我把导出文件名改为XLSX，提示我无法打开文件；另一个问题是在退出整个应用程序的时候，会报错PB9.0.EXE 应用程序错误

代码如下：

[second_huang]

string ls_docname, ls_named
integer li_value

li_value = GetFileSaveName("请选择输出到文件名:", ls_docname, ls_named, "XLS", "Excel Files (*.XLS), *.XLS" )
IF li_value = 1 THEN
SetPointer(HourGlass!)

string ls_sql,ls_err,ls_string
string ls_objects,ls_name,ls_objzw,ls_coltext,ls_coltextt
long ll_col,i,ll_pos,j,ll_colnum,ll_num
datastore lds_saveas //导出数据窗
datastore lds_sort //获得根据 object.x 排序的 (band = detail and visible = 1) 的 column/compute
boolean lb_return //返回值
string ls_pbver //pb 版本信息
environment env //环境变量

string ls_colname,ls_type,ls_zw
string ls_objtype,ls_band,ls_visible,ls_substring
long ll_x,ll_row,ll_subcol,h,ll_col1,d,s,row
string l_colname,ls_subcol,ls_value

long m,ll_return,ll_row1

getenvironment(env)
ls_pbver = string(env.pbmajorrevision)

//创建排序列 datastore
lds_sort = create datastore
ls_sql = 'column=(type=char(40) name = colname dbname="colname" )' + '~r~n' + &
'column=(type=number name = x dbname="x" )' + '~r~n' + &
'column=(type=char(40) name = coltype dbname="coltype" )' + '~r~n' + &
'column=(type=char(40) name = coltitle dbname="coltitle" )' + '~r~n' + &
'column=(type=char(40) name = objtype dbname="objtype" )' + '~r~n' + &
'column=(type=char(40) name = band dbname="band" )' + '~r~n' + &
'column=(type=char(40) name = vis dbname="vis" )' + '~r~n' + &
'column=(type=char(40) name = coltext dbname="coltext" )' + '~r~n' + &
'column=(type=char(40) name = coltextt dbname="coltextt" )' + '~r~n' + &
'column=(type=number name = num dbname="num" )' + '~r~n'

ls_sql = 'release ' + ls_pbver + ';~r~ntable(' + ls_sql + ')'
lds_sort.create(ls_sql, ls_err)
if len(ls_err) > 0 then
lb_return = false
else
// 记录 dw_1 中的对象数目
ls_objects = dw_1.Describe("DataWindow.Objects")
//将所有对象的属性记录在表中
do while len(ls_objects) > 0
ll_pos = pos(ls_objects,"~t")
if ll_pos <= 0 then ll_pos = len(ls_objects) + 1
ls_name = left(ls_objects,ll_pos - 1) // Object 名字
ls_objects = mid(ls_objects,ll_pos + 1,len(ls_objects))
ls_type = upper(dw_1.Describe(ls_name + ".coltype")) // Object 数据类型
if pos(ls_type, "(") > 0 then
ls_type = left(ls_type, pos(ls_type, "(") - 1 )
end if
ll_x = long(dw_1.Describe(ls_name + ".x")) // Object X 坐标
ls_band = upper(dw_1.Describe(ls_name + ".Band")) // Object 所属区域
ls_visible = dw_1.Describe(ls_name + ".visible") // Object 是否可见
ls_objtype = upper(dw_1.Describe(ls_name + ".Type")) // Object 类别
// Object 中文标头
ls_objzw = ls_name
ls_coltext = trim(dw_1.describe(ls_objzw + ".text"))
ls_objzw = ls_name + "_t"
ls_coltextt = trim(dw_1.describe(ls_objzw + ".text"))

i = lds_sort.insertrow(0)
lds_sort.setitem(i, "colname", ls_name )
lds_sort.setitem(i, "x", ll_x )
lds_sort.setitem(i, "coltype", ls_type )
lds_sort.setitem(i, "coltitle",ls_zw )
lds_sort.setitem(i, "objtype", ls_objtype )
lds_sort.setitem(i, "band",ls_band)
lds_sort.setitem(i,"vis",ls_visible)
lds_sort.setitem(i,"coltext",ls_coltext)
lds_sort.setitem(i,"coltextt",ls_coltextt)
lds_sort.setitem(i, "num", i )
loop

//按不同的位置信息建立存储表
lds_sort.setsort("x A")
lds_sort.sort()
ls_string = "$"
ls_sql = ""
ll_col = 0
for i = 1 to lds_sort.rowcount()
ll_colnum = lds_sort.getitemnumber(i,"x")
if ll_colnum <> ll_num then
ll_col++
ls_string = ls_string + string(ll_col) + "." + string(ll_colnum) + "$"
ls_sql = ls_sql + 'column=(type=char(60) name = A' + string(ll_col) + ' dbname="A' + string(ll_col) + '")' + '~r~n'
end if
ll_num = ll_colnum
next
lds_saveas = create datastore
ls_sql = 'release ' + ls_pbver + ';~r~ntable(' + ls_sql + ')'
lds_saveas.create(ls_sql, ls_err)
if len(ls_err) > 0 then
lb_return = false
else

lds_sort.setsort("num A")
lds_sort.sort()

//向 lds_saveas 中写数据
long n
h = 1
d = 1
s = 1
n = 1
lds_saveas.settransobject(sqlca)

[second_huang]

for j = 1 to lds_sort.rowcount()
yield()//释放消息队列, 如果数据量较大, 可以使用这个函数
ls_colname = lds_sort.getitemstring(j, "colname")
ll_x = lds_sort.getitemnumber(j, "x")
ls_type = lds_sort.getitemstring(j, "coltype")
ls_zw = lds_sort.getitemstring(j, "coltitle")
ls_objtype = lds_sort.getitemstring(j, "objtype")
ls_band = lds_sort.getitemstring(j, "band")
ls_visible = lds_sort.getitemstring(j,"vis")
ls_coltext = lds_sort.getitemstring(j,"coltext")
ls_coltextt = lds_sort.getitemstring(j,"coltextt")
ll_row = lds_sort.getitemnumber(j, "num" )


if ls_band = "HEADER" and ls_visible = "1" then
if h = 1 then
h++
row = lds_saveas.insertrow(0)
end if
ll_col = pos(ls_string,string(ll_x))
if ll_col <= 5 then
ll_col1 = 1
else
ll_col1 = ll_col - 5
end if

ll_col1 = pos(ls_string,"$",ll_col1)
ls_subcol = mid(ls_string,(ll_col1 + 1),(ll_col - ll_col1 - 2))
l_colname = "A" + trim(ls_subcol)
lds_saveas.setitem(row, l_colname, ls_coltext)
elseif ls_band = "DETAIL" and ls_visible = "1" and (ls_objtype = "COLUMN" or ls_objtype = "COMPUTE" )then
for i = 1 to dw_1.rowcount()
if d = 1 then
lds_saveas.insertrow(0)
end if
row = i + h - 1
if ls_objtype = "COLUMN" then
ls_value = dw_1.Describe("Evaluate('LookupDisplay(" + ls_colname + ")'," + string(i) + ")")
elseif ls_objtype = "COMPUTE" then
// 对于 Compute 对象，利用前述方法会出错
choose case ls_type
case "CHAR"
ls_value = dw_1.GetItemString(i, ls_colname )
case "DATE"
ls_value = string( dw_1.GetItemDate(i, ls_colname) , "yyyy-mm-dd")
case "DATETIME"
ls_value = string( dw_1.GetItemDateTime(i, ls_colname) , "yyyy-mm-dd")
case "INT","LONG", "NUMBER", "REAL", "UNLONG", "DECIMAL"
ls_value = string( dw_1.GetItemNumber(i, ls_colname) )
case "TIME", "TIMESTAMP"
ls_value = string( dw_1.GetItemTime(i, ls_colname) )
end choose
end if
ll_col = pos(ls_string,string(ll_x))
//ls_substring = mid(ls_string,ll_col - 5)
if ll_col <= 5 then
ll_col1 = 1
else
ll_col1 = ll_col - 5
end if

ll_col1 = pos(ls_string,"$",ll_col1)
ls_subcol = mid(ls_string,(ll_col1 + 1),(ll_col - ll_col1 - 2))
l_colname = "A" + trim(ls_subcol)
lds_saveas.setitem(row, l_colname, ls_value)
next
d++
//====================
elseif LEFT(ls_band,7) = "TRAILER" and ls_visible = "1" then
m = 0

for i = 1 to dw_1.rowcount()

ll_return = dw_1.FindGroupChange(i,long(trim(right(ls_band,len(ls_band) - 8))))
// messagebox(string(i),string(ll_return))
if ll_return = 1 then
continue
elseif ll_return = 0 then
i = dw_1.rowcount()
row = ll_return + dw_1.rowcount() + m + h
ll_row1 = ll_return + dw_1.rowcount()
m++
else
i = ll_return
row = ll_return + m + h - 1
ll_row1 = ll_return - 1
m++
end if
if n = 1 then
lds_saveas.insertrow(row)
end if


if ls_objtype = "TEXT" then
ls_value = ls_coltext
elseif ls_objtype = "COMPUTE" then
// 对于 Compute 对象，利用前述方法会出错
// messagebox(ls_type,ll_row1)
choose case ls_type
case "CHAR"
ls_value = dw_1.GetItemString(ll_row1, ls_colname )
case "DATE"
ls_value = string( dw_1.GetItemDate(ll_row1, ls_colname) , "yyyy-mm-dd")
case "DATETIME"
ls_value = string( dw_1.GetItemDateTime(ll_row1, ls_colname) , "yyyy-mm-dd")
case "INT","LONG", "NUMBER", "REAL", "UNLONG", "DECIMAL"
// messagebox('',ls_colname)
ls_value = string( dw_1.GetItemNumber(ll_row1, ls_colname) )
case "TIME", "TIMESTAMP"
ls_value = string( dw_1.GetItemTime(ll_row1, ls_colname) )
end choose
end if
ll_col = pos(ls_string,string(ll_x))

if ll_col <= 5 then
ll_col1 = 1
else
ll_col1 = ll_col - 5
end if

ll_col1 = pos(ls_string,"$",ll_col1)
ls_subcol = mid(ls_string,(ll_col1 + 1),(ll_col - ll_col1 - 2))
l_colname = "A" + trim(ls_subcol)
lds_saveas.setitem(row, l_colname, ls_value)
next
n++
//====================
elseif ls_band = "SUMMARY" and ls_visible = "1" then
if s = 1 then
s++
row = lds_saveas.insertrow(0)
end if
if ls_objtype = "TEXT" then
ls_value = ls_coltext
elseif ls_objtype = "COMPUTE" then
// 对于 Compute 对象，利用前述方法会出错
choose case ls_type
case "CHAR"
ls_value = dw_1.GetItemString(1, ls_colname )
case "DATE"
ls_value = string( dw_1.GetItemDate(1, ls_colname) , "yyyy-mm-dd")
case "DATETIME"
ls_value = string( dw_1.GetItemDateTime(1, ls_colname) , "yyyy-mm-dd")
case "INT","LONG", "NUMBER", "REAL", "UNLONG", "DECIMAL"
ls_value = string( dw_1.GetItemNumber(1, ls_colname) )
case "TIME", "TIMESTAMP"
ls_value = string( dw_1.GetItemTime(1, ls_colname) )
end choose
end if
ll_col = pos(ls_string,string(ll_x))
//ls_substring = mid(ls_string,ll_col - 5)
if ll_col <= 5 then
ll_col1 = 1
else
ll_col1 = ll_col - 5
end if

ll_col1 = pos(ls_string,"$",ll_col1)
ls_subcol = mid(ls_string,(ll_col1 + 1),(ll_col - ll_col1 - 2))
l_colname = "A" + trim(ls_subcol)
lds_saveas.setitem(row, l_colname, ls_value)
end if
next
end if
//saveas datawindow
lb_return = (lds_saveas.saveas(ls_named, excel!, false) = 1)
end if

destroy lds_sort
destroy lds_saveas
SetPointer(Arrow!)

if lb_return then
messagebox("","保存成功!")
else
messagebox("","保存失败!")
end if
end if



return lb_return