pb怎样导入excel收藏.docx
《pb怎样导入excel收藏.docx》由会员分享,可在线阅读,更多相关《pb怎样导入excel收藏.docx(13页珍藏版)》请在冰豆网上搜索。
![pb怎样导入excel收藏.docx](https://file1.bdocx.com/fileroot1/2023-1/23/0acb8e8c-be36-476a-aeff-c7d84624407d/0acb8e8c-be36-476a-aeff-c7d84624407d1.gif)
pb怎样导入excel收藏
pb怎样导入excel收藏
/**********************************************************/
//Function:
转换数据到excel
//parm:
//1.as_title:
excel的标题
//return:
1issuccessand-1isfail
//Author:
hzh
//date:
2003.12.08
//Modifier:
//1.2003.12.10byhzh
//Reason:
//1.增加对计算列的处理
/**********************************************************/
IFNOTFileExists(as_excelfile)THEN
MessageBox("数据读入","没有指定的文件")
RETURN1
ENDIF
OLEObjectxlapp
IFNOTIsValid(xlApp)THEN
xlApp=CREATEOLEObject
ENDIF
IFxlApp.ConnectToNewObject("Excel.Application")<0THEN
MessageBox(ga_app.dwmessagetitle,"不能连接EXCEL服务器,请检查你的计算机中是~r~n"+&
"否安装了MSEXCEL?
假如安装,请与程序供应商联系!
",QuesTion!
)
RETURN-1
ENDIF
//增加空文档(EXCELtable)
xlApp.Workbooks.Open(as_excelfile)
//xlApp.Application.Visible=FALSE
xlApp.DisplayAlerts=false
//定位到第一格
Longl_cnt,l_rows,l_cols
Longl_row,l_i
Inti_cnt
l_rows=30
l_cols=23
Longl_cno
Strings_cno,s_temp
Longl_newrow
//一次处理两行
DataStoreds_todb
IFNOTIsValid(ds_todb)THEN
ds_todb=CREATEDataStore
ENDIF
ds_todb.DataObject="dw_getdatafromexcel"
ds_todb.SetTransObject(at_sqlca)
FORl_cnt=2TOl_rowsSTEP2
Yield()
l_row=l_cnt
s_cno=String(xlapp.activeworkbook.activesheet.cells[l_row-1,1].value)
IFNOTIsNumber(s_cno)THENCONTINUE
FORl_row=l_cnt-1TOl_cnt
s_cno=xlapp.activeworkbook.activesheet.cells[l_row,1].FormulaR1C1
IFIsNull(s_cno)ORs_cno=""THEN
//非新行
FORl_i=4TOl_colsSTEP2
s_temp=String(Trim(xlapp.activeworkbook.activesheet.cells[l_row,l_i].FormulaR1C1))
IFIsNull(s_temp)ORs_temp=""THENCONTINUE
i_cnt++
ds_todb.SetItem(l_newrow,i_cnt,s_temp)
NEXT
ELSE
//新行开始,清空数组
IFNOTIsNumber(s_cno)THENCONTINUE
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
FORl_i=4TOl_colsSTEP2
s_temp=String(Trim(xlapp.activeworkbook.activesheet.cells[l_row,l_i].FormulaR1C1))
IFIsNull(s_temp)ORs_temp=""THENCONTINUE
i_cnt++
ds_todb.SetItem(l_newrow,i_cnt,s_temp)
NEXT
ENDIF
NEXT
NEXT
xlApp.activeworkbook.close(false)
xlapp.Application.quit()
xlApp.DisConnectObject()
//xlApp.Application.Workbooks.quit()
IFIsValid(xlApp)THEN
DESTROYxlapp
ENDIF
//gnvo_db.uf_closewin("Microsoftexcel")
IFds_todb.Update()=1THEN
COMMITUSINGat_sqlca;
MessageBox("导入数据","保存成功")
ELSE
ROLLBACKUSINGat_sqlca;
MessageBox("导入数据","保存失败")
ENDIF
IFIsValid(ds_todb)THEN
DESTROYds_todb
ENDIF
//FileDelete(as_excelfile)
RETURN1
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)
ifvalue<>1then////没取到、或者取消返回
Returnfalse
endif
Top
7楼workhand(我可憨了...)回复于2004-04-1608:
21:
24得分0Is_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)
ifvalue<>1then////没取到、或者取消返回
Returnfalse
endif
第一种方法
OLEObjectExcelServer
longexcelok
stringstr_savename
ExcelServer=CREATEOLEObject
ExcelOK=ExcelServer.ConnectToNewObject("excel.application")
ifexcelok<0then
messagebox("连接excel失败,检查你的系统是否安装了office",string(excelok))
returnfalse
else
ExcelServer.Workbooks.Open(str_filename)
str_savename="c:
\temp.txt"
excelserver.activeworkbook.saveas(str_savename,3)
excelserver.displayalerts=false
ExcelServer.quit()
ExcelServer.DisconnectObject()
DESTROYExcelServer
dw_acton.ImportFile(str_savename)
filedelete(str_savename)
returntrue
endif
第二种方法
stringls_title,ls_str
OLEObjectxl,xlApp,xlbook,xlsheet
xl=CreateOLEObject
st_stat.text='正在连接...'
intret=0
ret=xl.ConnectToObject(is_path,"Excel.Sheet")//打开一个已存在的工作表
ifret<0then
messagebox("提示","不能打开所选的文件,可能是已被别人打开!
")
destroyxl
return
endif
xlApp=xl.Application
xlbook=xlapp.Workbooks[1]
xlsheet=xlbook.Worksheets[1]
////////
//////
//////
////////以下是读数据部分,首先判断有几行数据,前面几个都为空时则数据结束
longll_i,ll_row,ll_ll
intli_start//从第几行开始是真正的数据
li_start=4//从第n行开始是真正的数据
//
dowhile((ll_ll<5)and(ll_i<3000))
ll_i+=1
ls_str=xlsheet.cells[ll_i,4].text//货物名称
ll_row=ll_i
ifls_str=""then
ll_ll=1
ls_str=xlsheet.cells[ll_i,5].text
ifls_str=""then
ll_ll+=1
ls_str=xlsheet.cells[ll_i,6].text
ifls_str=""then
ll_ll+=1
ls_str=xlsheet.cells[ll_i+1,4].text
ifls_str=""then
ll_ll+=1
ls_str=xlsheet.cells[ll_i+2,4].text
ifls_str=""then
ll_ll+=1
endif
endif
endif
endif
endif
loop
//
ifll_row>2800then
messagebox("错误","行数太多,不能导入!
")
gotol_exit
endif
//一共有ll_row-start行
////
//ls_title=xlsheet.cells[1,1].text//标题,这里可能有比较
////
ls_str=xlsheet.cells[1,2].text//合同号
ifls_str<>is_contractcodethen
messagebox("导入出错","合同号不符,请检查[1,2]单元格")
st_stat.text="导入失败,请退出重来!
"
gotol_exit
endif
////
//ls_str=xlsheet.cells[2,2].text//合同名
//ifls_str<>is_contractnamethen
//messagebox("导入出错","合同名不符,请检查[2,2]单元格")
//st_stat.text="导入失败,请退出重来!
"
//gotol_exit
//endif
////
////开始导入真正的数据
stringls_temp,ls_find
longl_count,l_temp
if(ll_row-1)=li_startthen
messagebox("提示","没有数据可导!
请检查EXCEL表格后,重来!
")
return
endif
forll_i=li_starttoll_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
ifnotisnumber(ls_str)andls_str<>''then
messagebox("错误","第["+string(ll_i)+',10]单元格必须是数字!
')
gotol_exit
endif
dw_2.object.f_quant[l_count]=dec(ls_str)//数量
//出厂价
ls_str=xlsheet.cells[ll_i,11].text
ifnotisnumber(ls_str)andls_str<>''then
messagebox("错误","第["+string(ll_i)+',11]单元格必须是数字!
')
gotol_exit
endif
dw_2.object.f_outprice[l_count]=dec(ls_str)//出厂价
//运输价
ls_str=xlsheet.cells[ll_i,12].text
ifnotisnumber(ls_str)andls_str<>''then
messagebox("错误","第["+string(ll_i)+',12]单元格必须是数字!
')
gotol_exit
endif
dw_2.object.f_transprice[l_count]=dec(ls_str)//运输价
//保险价
ls_str=xlsheet.cells[ll_i,13].text
ifnotisnumber(ls_str)andls_str<>''then
messagebox("错误","第["+string(ll_i)+',13]单元格必须是数字!
')
gotol_exit
endif
dw_2.object.f_guaranteep[l_count]=dec(ls_str)//保险价
//其它价
ls_str=xlsheet.cells[ll_i,14].text
ifnotisnumber(ls_str)andls_str<>''then
messagebox("错误","第["+string(ll_i)+',14]单元格必须是数字!
')
gotol_exit
endif
dw_2.object.f_otherprice[l_count]=dec(ls_str)//其它价
//综合单价
ls_str=xlsheet.cells[ll_i,15].text
ifnotisnumber(ls_str)andls_str<>''then
messagebox("错误","第["+string(ll_i)+',15]单元格必须是数字!
')
gotol_exit
endif
dw_2.object.f_unitprice[l_count]=dec(ls_str)//综合单价
//外币总价
ls_str=xlsheet.cells[ll_i,16].text
ifnotisnumber(ls_str)andls_str<>''then
messagebox("错误","第["+string(ll_i)+',16]单元格必须是数字!
')
gotol_exit
endif
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
ifnotisnumber(ls_str)andls_str<>''then
messagebox("错误","第["+string(ll_i)+',18]单元格必须是数字!
')
gotol_exit
endif
dw_2.object.f_exchange[l_count]=dec(ls_str)//汇率
//汇率日期
ls_str=xlsheet.cells[ll_i,19].text
ifnotisdate(ls_str)andls_str<>''then
messagebox("错误","第["+string(ll_i)+',19]单元格必须是日期!
')
gotol_exit
endif
dw_2.object.f_exchangedate[l_count]=datetime(date(ls_str))//汇率日期
//人民币单价
ls_str=xlsheet.cells[ll_i,20].text
ifnotisnumber(ls_str)andls_str<>''then
messagebox("错误","第["+string(ll_i)+',20]单元格必须是数字!
')
gotol_exit
endif
dw_2.object.f_rmbunitprice[l_count]=dec(ls_str)//人民币单价
//人民币总价
ls_str=xlsheet.cells[ll_i,21].text
ifnotisnumber(ls_str)andls_str<>''then
messagebox("错误","第["+string(ll_i)+',21]单元格必须是数字!
')
gotol_exit
endif
dw_2.object.f_rmbtotalprice[l_count]=dec(ls_str)//人民币总价
//开始日期
ls_str=xlsheet.cells[ll_i,22].text
ifnotisdate(ls_str)andls_str<>''then
messagebox("错误","第["+string(ll_i)+',22]单元格必须是日期!
')
gotol_exit
endif
dw_2.object.f_startdate[l_count]=datetime(date(ls_str))//开始日期
//结束日期
ls_str=xlsheet.cells[ll_i,23].text
ifnotisdate(ls_str)andls_str<>''then
messagebox("错误","第["+string(ll_i)+',23]单元格必须是日期!
')
gotol_exit
endif
dw_2