**********************************************************/
//Function : 转换数据到 excel
//parm :
// 1. as_title : excel 的标题
//return : 1 is success and -1 is fail
//Author : hzh
//date : 2003.12.08
//Modifier :
// 1. 2003.12.10 by hzh
//Reason :
// 1. 增加对计算列的处理
/**********************************************************/
IF NOT FileExists(as_excelfile) THEN
MessageBox("数据读入","没有指定的文件")
RETURN 1
END IF
OLEObject xlapp
IF NOT IsValid(xlApp) THEN
xlApp = CREATE OLEObject
END IF
IF xlApp.ConnectToNewObject("Excel.Application" ) < 0 THEN
MessageBox(ga_app.dwmessagetitle,"不能连接 EXCEL 服务器,请检查你的计算机中是 ~r~n " +&
"否安装了MS EXCEL ? 假如安装,请与程序供应商联系 !",QuesTion!)
RETURN -1
END IF
//增加空文档 (EXCEL table)
xlApp.Workbooks.Open(as_excelfile)
//xlApp.Application.Visible = FALSE
xlApp.DisplayAlerts = false
//定位到第一格
Long l_cnt,l_rows,l_cols
Long l_row,l_i
Int i_cnt
l_rows = 30
l_cols = 23
Long l_cno
String s_cno,s_temp
Long l_newrow
//一次处理两行
DataStore ds_todb
IF NOT IsValid(ds_todb) THEN
ds_todb = CREATE DataStore
END IF
ds_todb.DataObject = "dw_getdatafromexcel"
ds_todb.SetTransObject(at_sqlca)
FOR l_cnt = 2 TO l_rows STEP 2
Yield()
l_row = l_cnt
s_cno = String(xlapp.activeworkbook.activesheet.cells[l_row - 1,1].value)
IF NOT IsNumber(s_cno) THEN CONTINUE
FOR l_row = l_cnt - 1 TO l_cnt
s_cno = xlapp.activeworkbook.activesheet.cells[l_row,1].FormulaR1C1
IF IsNull(s_cno) OR s_cno = "" THEN
//非新行
FOR l_i = 4 TO l_cols STEP 2
s_temp = String(Trim(xlapp.activeworkbook.activesheet.cells[l_row,l_i].FormulaR1C1))
IF IsNull(s_temp) OR s_temp = "" THEN CONTINUE
i_cnt++
ds_todb.SetItem(l_newrow,i_cnt,s_temp)
NEXT
ELSE
//新行开始,清空数组
IF NOT IsNumber(s_cno) THEN CONTINUE
l_newrow = ds_todb.InsertRow(0)
ds_todb.SetItem(l_newrow,1,s_cno)
//ds_todb.SetItem(l_newrow,2,gnvo_db.uf_todate(Long(xlapp.activeworkbook.activesheet.cells[l_row,2].FormulaR1C1)))
ds_todb.SetItem(l_newrow,3, Dec(xlapp.activeworkbook.activesheet.cells[l_row,3].FormulaR1C1))
i_cnt = 3
FOR l_i = 4 TO l_cols STEP 2
s_temp = String(Trim(xlapp.activeworkbook.activesheet.cells[l_row,l_i].FormulaR1C1))
IF IsNull(s_temp) OR s_temp = "" THEN CONTINUE
i_cnt++
ds_todb.SetItem(l_newrow,i_cnt,s_temp)
NEXT
END IF
NEXT
NEXT
xlApp.activeworkbook.close(false)
xlapp.Application.quit()
xlApp.DisConnectObject()
//xlApp.Application.Workbooks.quit()
IF IsValid(xlApp) THEN
DESTROY xlapp
END IF
//gnvo_db.uf_closewin("Microsoft excel")
IF ds_todb.Update() = 1 THEN
COMMIT USING at_sqlca;
MessageBox("导入数据","保存成功")
ELSE
ROLLBACK USING at_sqlca;
MessageBox("导入数据","保存失败")
END IF
IF IsValid(ds_todb) THEN
DESTROY ds_todb
END IF
//FileDelete(as_excelfile)
RETURN 1
Is_syspath=space(255)
GetCurrentDirectoryA(255,Is_syspath)
value=GetFileopenname("请选择要导入BOM的EXECL类型文件!",ls_path,ls_filename,'xls','EXECL 文件(*.xls),*.xls')
is_filename = mid(ls_filename,1,len(ls_filename) - 4)
if value <> 1 then 没取到、或者取消返回
Return false
end if
Top
7 楼workhand(我可憨了...)回复于 2004-04-16 08:21:24 得分 0 Is_syspath=space(255)
GetCurrentDirectoryA(255,Is_syspath)
value=GetFileopenname("请选择要导入BOM的EXECL类型文件!",ls_path,ls_filename,'xls','EXECL 文件(*.xls),*.xls')
is_filename = mid(ls_filename,1,len(ls_filename) - 4)
if value <> 1 then 没取到、或者取消返回
Return false
end if
第一种方法
OLEObject ExcelServer
long excelok
string str_savename
ExcelServer = CREATE OLEObject
ExcelOK = ExcelServer.ConnectToNewObject( "excel.application" )
if excelok < 0 then
messagebox("连接excel失败,检查你的系统是否安装了office",string(excelok))
return false
else
ExcelServer.Workbooks.Open(str_filename)
str_savename="c:/temp.txt"
excelserver.activeworkbook.saveas(str_savename,3)
excelserver.displayalerts=false
ExcelServer.quit()
ExcelServer.DisconnectObject()
DESTROY ExcelServer
dw_acton.ImportFile(str_savename)
filedelete(str_savename)
return true
end if
第二种方法
string ls_title, ls_str
OLEObject xl, xlApp, xlbook, xlsheet
xl = Create OLEObject
st_stat.text = '正在连接...'
int ret = 0
ret = xl.ConnectToObject(is_path, "Excel.Sheet" ) //打开一个已存在的工作表
if ret < 0 then
messagebox("提示","不能打开所选的文件,可能是已被别人打开!")
destroy xl
return
end if
xlApp = xl.Application
xlbook = xlapp.Workbooks[1]
xlsheet = xlbook.Worksheets[1]
//
//
以下是读数据部分, 首先判断有几行数据,前面几个都为空时则数据结束
long ll_i,ll_row,ll_ll
int li_start //从第几行开始是真正的数据
li_start = 4 //从第n行开始是真正的数据
//
do while ((ll_ll < 5) and (ll_i < 3000))
ll_i += 1
ls_str = xlsheet.cells[ ll_i,4 ].text //货物名称
ll_row = ll_i
if ls_str = "" then
ll_ll = 1
ls_str = xlsheet.cells[ ll_i,5 ].text
if ls_str = "" then
ll_ll += 1
ls_str = xlsheet.cells[ ll_i,6 ].text
if ls_str = "" then
ll_ll += 1
ls_str = xlsheet.cells[ ll_i+1,4 ].text
if ls_str = "" then
ll_ll += 1
ls_str = xlsheet.cells[ ll_i+2, 4 ].text
if ls_str = "" then
ll_ll += 1
end if
end if
end if
end if
end if
loop
//
if ll_row > 2800 then
messagebox("错误","行数太多, 不能导入!")
goto l_exit
end if
//一共有ll_row - start 行
//ls_title = xlsheet.cells[1,1].text //标题, 这里可能有比较
ls_str = xlsheet.cells[1,2].text //合同号
if ls_str <> is_contractcode then
messagebox( "导入出错","合同号不符,请检查[1,2]单元格" )
st_stat.text = "导入失败, 请退出重来!"
goto l_exit
end if
//ls_str = xlsheet.cells[2,2].text //合同名
//if ls_str <> is_contractname then
// messagebox( "导入出错","合同名不符,请检查[2,2]单元格" )
// st_stat.text = "导入失败, 请退出重来!"
// goto l_exit
//end if
开始导入真正的数据
string ls_temp,ls_find
long l_count,l_temp
if (ll_row - 1) = li_start then
messagebox( "提示","没有数据可导!请检查EXCEL表格后,重来!" )
return
end if
for ll_i = li_start to ll_row - 1
st_stat.text = "正在导入第 " + string( ll_i - li_start + 1 ) + " 行," + '共有'+ string( ll_row - li_start) + '行'
hpb_1.position = (( ll_i - li_start + 1 ) * 100/(ll_row - li_start))
l_count = dw_2.insertrow( 0 )
dw_2.setrow( l_count )
dw_2.object.f_id[ l_count ] = gf_get_max( 't_goodslist' ) //ID
dw_2.object.f_contractid[ l_count ] = il_contractid //合同ID
//从文件导入部分
dw_2.object.f_period[ l_count ] = xlsheet.cells[ ll_i, 1].text //期别
dw_2.object.f_mysys[ l_count ] = xlsheet.cells[ ll_i, 2].text //系统码
dw_2.object.f_goodscode[ l_count ] = xlsheet.cells[ ll_i, 3].text //货物编码
dw_2.object.f_goodsname[ l_count ] = xlsheet.cells[ ll_i, 4].text //货物名称
dw_2.object.f_specif[ l_count ] = xlsheet.cells[ ll_i, 5].text //货物规格
dw_2.object.f_parameter[ l_count] = xlsheet.cells[ ll_i, 6].text //主要技术参数
dw_2.object.f_factory[ l_count ] = xlsheet.cells[ ll_i, 7].text //制造厂
dw_2.object.f_goodstype[ l_count ] = xlsheet.cells[ ll_i, 8].text //货物类别
dw_2.object.f_unit[ l_count ] = xlsheet.cells[ ll_i, 9].text //单位
//数量
ls_str = xlsheet.cells[ ll_i, 10].text
if not isnumber( ls_str ) and ls_str <> '' then
messagebox("错误","第["+ string( ll_i) + ',10]单元格必须是数字!')
goto l_exit
end if
dw_2.object.f_quant[ l_count ] = dec(ls_str ) //数量
//出厂价
ls_str = xlsheet.cells[ ll_i, 11].text
if not isnumber( ls_str ) and ls_str<>'' then
messagebox("错误","第["+ string( ll_i) + ',11]单元格必须是数字!')
goto l_exit
end if
dw_2.object.f_outprice[ l_count ] = dec(ls_str ) //出厂价
//运输价
ls_str = xlsheet.cells[ ll_i, 12].text
if not isnumber( ls_str ) and ls_str<>'' then
messagebox("错误","第["+ string( ll_i) + ',12]单元格必须是数字!')
goto l_exit
end if
dw_2.object.f_transprice[ l_count ] = dec(ls_str ) //运输价
//保险价
ls_str = xlsheet.cells[ ll_i, 13].text
if not isnumber( ls_str ) and ls_str<>'' then
messagebox("错误","第["+ string( ll_i) + ',13]单元格必须是数字!')
goto l_exit
end if
dw_2.object.f_guaranteep[ l_count ] = dec(ls_str ) //保险价
//其它价
ls_str = xlsheet.cells[ ll_i, 14].text
if not isnumber( ls_str ) and ls_str<>'' then
messagebox("错误","第["+ string( ll_i) + ',14]单元格必须是数字!')
goto l_exit
end if
dw_2.object.f_otherprice[ l_count ] = dec(ls_str ) //其它价
//综合单价
ls_str = xlsheet.cells[ ll_i, 15].text
if not isnumber( ls_str ) and ls_str <> '' then
messagebox("错误","第["+ string( ll_i) + ',15]单元格必须是数字!')
goto l_exit
end if
dw_2.object.f_unitprice[ l_count ] = dec(ls_str ) //综合单价
//外币总价
ls_str = xlsheet.cells[ ll_i, 16].text
if not isnumber( ls_str ) and ls_str <> '' then
messagebox("错误","第["+ string( ll_i) + ',16]单元格必须是数字!')
goto l_exit
end if
dw_2.object.f_totalprice[ l_count ] = dec(ls_str ) //外币总价
dw_2.object.f_currency[ l_count ] = xlsheet.cells[ ll_i, 17].text //币种
//汇率
ls_str = xlsheet.cells[ ll_i, 18].text
if not isnumber( ls_str ) and ls_str <> '' then
messagebox("错误","第["+ string( ll_i) + ',18]单元格必须是数字!')
goto l_exit
end if
dw_2.object.f_exchange[ l_count ] = dec(ls_str ) //汇率
//汇率日期
ls_str = xlsheet.cells[ ll_i, 19].text
if not isdate( ls_str ) and ls_str <> '' then
messagebox("错误","第["+ string( ll_i) + ',19]单元格必须是日期!')
goto l_exit
end if
dw_2.object.f_exchangedate[ l_count ] = datetime(date(ls_str )) //汇率日期
//人民币单价
ls_str = xlsheet.cells[ ll_i, 20].text
if not isnumber( ls_str ) and ls_str <> '' then
messagebox("错误","第["+ string( ll_i) + ',20]单元格必须是数字!')
goto l_exit
end if
dw_2.object.f_rmbunitprice[ l_count ] = dec(ls_str ) //人民币单价
//人民币总价
ls_str = xlsheet.cells[ ll_i, 21].text
if not isnumber( ls_str ) and ls_str <> '' then
messagebox("错误","第["+ string( ll_i) + ',21]单元格必须是数字!')
goto l_exit
end if
dw_2.object.f_rmbtotalprice[ l_count ] = dec(ls_str ) //人民币总价
//开始日期
ls_str = xlsheet.cells[ ll_i, 22].text
if not isdate( ls_str ) and ls_str <> '' then
messagebox("错误","第["+ string( ll_i) + ',22]单元格必须是日期!')
goto l_exit
end if
dw_2.object.f_startdate[ l_count ] = datetime(date(ls_str )) //开始日期
//结束日期
ls_str = xlsheet.cells[ ll_i, 23].text
if not isdate( ls_str ) and ls_str <> ''then
messagebox("错误","第["+ string( ll_i) + ',23]单元格必须是日期!')
goto l_exit
end if
dw_2.object.f_enddate[ l_count ] = datetime(date(ls_str )) //结束日期
dw_2.object.f_station[l_count] = xlsheet.cells[ ll_i,24].text //站点
dw_2.object.f_place[l_count] = xlsheet.cells[ ll_i,25].text //安装位置
dw_2.object.f_gaisuan[l_count]= xlsheet.cells[ ll_i,26].text //概算码
dw_2.object.f_memo[l_count]= xlsheet.cells[ ll_i,27].text //备注
//其它信息
dw_2.object.f_stat[l_count] = 0
dw_2.object.f_compquant1[l_count] = 0
dw_2.object.f_compquant2[l_count] = 0
dw_2.object.f_inquant1[l_count] = 0
dw_2.object.f_inquant2[l_count] = 0
dw_2.object.f_payquant[ l_count ] = 0
dw_2.object.f_changestat[ l_count ] = 0
dw_2.object.f_changecount[ l_count ] = 0
next
st_stat.text = '导入成功!请选择[保存]或[取消]。 保存--此次导入数据将提交到数据库中,取消--则不提交!'
cb_ok.of_setenabled( true )
//有错退出, 主要是要释放内存
l_exit:
xl.close()
xl.DisConnectObject()
Destroy xl
//
//return true
//以上的程序是导入某一合同的供货清单其中某一列不能为空,判断文件是否结束的写法有点烦。