Question

pb怎样导入excel收藏 详细�0�3

Answer 1

假如安装，请与程序供应商联系 !",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 = '导入成功！请选择[保存]或[取消]。 保存--此次导入数据将提交到数据库中，取消--则不提交！