db2excel_out.asp

物流进销存系统 全功能,MSSQL 2000数据库. 可做物流公司信息系统开发的源程序

				strLine = strLine & outw_date& chr(9) & outw_sra & chr(9) & dc_code& chr(9) & dc_name& chr(9) & guest_user& chr(9) & guest_address& chr(9) & guest_tel& chr(9) & guest_fax& chr(9)  & guest_takecode& chr(9)& info_type& chr(9)& type_name & chr(9) & prod_no& chr(9) & prod_pinming& chr(9) & prod_type& chr(9) & prod_color& chr(9) & detail_no& chr(9) & elink_odd & chr(9) & air_take_no & chr(9) & air_line_no & chr(9) & air_etd & chr(9) & air_eta & chr(9) & " " & chr(9) & " " & chr(9) & rec_idcard & chr(9) & info_yuji_time & chr(9) & " " & chr(9) & " " 
				'--将表的数据写入EXCEL
				myfile.writeline strLine
		
			rs_2.MoveNext
			loop 
		else
			tt = 0
			response.Write "无记录!"
			response.Write "<a href=""javascript:history.go(-1)""><font size=+2> 后 退 </font></a>"
		end if
        rs_2.Close
        set rs_2 = nothing
  
	
  else
 
    Set fs = server.CreateObject("scripting.filesystemobject")
  '--假设你想让生成的EXCEL文件做如下的存放
  'filename = "D:\mir_20020418\companyadmin\"&Session("companymanager")&"_online.xls"
  filename =  server.MapPath(""&User&"_online_out.xls")
  filename2 = "operate\"&User&"_online_out.xls"
  '--如果原来的EXCEL文件存在的话删除它
  if fs.FileExists(filename) then
    fs.DeleteFile(filename)
  end if
  '--创建EXCEL文件
  set myfile = fs.CreateTextFile(filename,true)
  
  
    dim strLine,responsestr
    strLine=""
      'For each x in rs.fields
      '  strLine= strLine & x.name & chr(9)
      'Next
		 strline = strline & "填写日期" & chr(9) & "SRA" & chr(9) & "DC编号" & chr(9) & "城市" & chr(9) & "客户名称" & chr(9) & "客户地址" & chr(9) & "联系电话" & chr(9) & "传真" & chr(9)  & "收货代码" & chr(9) & "客服出库方式" & chr(9) & "产品类别" & chr(9) & "产品编号" & chr(9) & "品名" & chr(9) & "产品型号" & chr(9) & "产品颜色" & chr(9) & "数量" & chr(9) & "易联单号" & chr(9) & "航空单号" & chr(9) & "航班号" & chr(9) & "预计出发时间" & chr(9) & "预计到达时间" & chr(9) & "签收时间" & chr(9)  & "签收人" & chr(9) & "身份证" & chr(9) & "预计到达时间" & chr(9) & "实际重量" & chr(9) & "记费重量" & chr(9)  & "调度出库方式" & chr(9)     
      '--将表的列名先写入EXCEL
      myfile.writeline strLine
      Do while Not rs.EOF
      strLine=""
      'for each x in rs.Fields
      '  strLine= strLine & x.value & chr(9)
      'next
      outw_sra = trim(rs("outw_sra"))
		if isnumeric(outw_sra) = true then
			outw_sra = outw_sra & "'"
		end if
      elink_odd = trim(rs("elink_odd"))
		if isnumeric(elink_odd) = true then
			elink_odd = elink_odd & "'"
		end if
	  rec_idcard = trim(rs("rec_idcard"))
		if isnumeric(rec_idcard) = true then
			rec_idcard = rec_idcard & "'"
		end if
      database_outw_id = rs("detail_outw_id")
      database_guest_id = rs("detail_guest_id")
      transport_type = rs("transport_type")
      if transport_type = 1 then
			transport_type_no = "汽运"
	  elseif transport_type = 2 then
			transport_type_no = "空运"
	  elseif transport_type = 3 then
			transport_type_no = "铁路"
	  elseif transport_type = 4 then
			transport_type_no = "EMS"
	  else
			transport_type_no = " "
	  end if
			
      
      sql = "select * from detail_info where info_detail_id = (select min(detail_id) as detail_id from outward_detail where detail_outw_id = "&database_outw_id&" and detail_guest_id = "&database_guest_id&") "
      set rst = conn.execute(sql)
      if not rst.eof then
		info_type = rst("info_type")
		info_takeman_id = rst("info_takeman_id")
		info_yuji_time = rst("info_yuji_time")
	  else
		info_type = " "
		info_takeman_id = " "
		info_yuji_time = " "
	  end if
      rst.close
      set rst = nothing
      
      transport_type = rs("transport_type")
      if transport_type = 2 then
			sql = "select * from air where air_elink_id = "&rs("elink_id")&""
			set rs_air = conn.execute(sql)
			if not rs_air.eof then
				air_take_no = rs_air("air_take_no")
				air_line_no = rs_air("air_line_no")
				air_etd = rs_air("air_etd")
				air_eta = rs_air("air_eta")
			else
				air_take_no = " "
				air_line_no = " "
				air_etd = " "
				air_eta = " "
			end if
			rs_air.close
			set rs_air = nothing
      else
				air_take_no = " "
				air_line_no = " "
				air_etd = " "
				air_eta = " "
      end if
      
      strLine = strLine & rs("outw_date")& chr(9) & outw_sra & chr(9) & rs("dc_code")& chr(9) & rs("dc_name")& chr(9) & rs("guest_user")& chr(9) & rs("guest_address")& chr(9) & rs("guest_tel")& chr(9) & rs("guest_fax")& chr(9)  & rs("guest_takecode")& chr(9)& info_type& chr(9)& rs("type_name") & chr(9)& rs("prod_no")& chr(9) & rs("prod_pinming")& chr(9) & rs("prod_type")& chr(9) & rs("prod_color")& chr(9) & rs("detail_no")& chr(9) & elink_odd & chr(9) & air_take_no & chr(9) & air_line_no & chr(9) & air_etd & chr(9) & air_eta & chr(9) & rs("rec_date")& chr(9) & rs("rec_man")& chr(9) & rec_idcard & chr(9) & info_yuji_time & chr(9) & rs("fact_weight") & chr(9) & rs("tally_weight")& chr(9) & transport_type_no 
      '--将表的数据写入EXCEL
      myfile.writeline strLine

      rs.MoveNext
      loop
      
       '写入未生成易联单号的所有记录到EXCEL表中
       sql = "select * from outward "
	   sql = sql + " right outer join outward_detail on outward.outw_id = outward_detail.detail_outw_id "
	   sql = sql + " join guest on outward_detail.detail_guest_id = guest.guest_id "
	   sql = sql + " join dc on outward.outw_dc_id = dc.dc_id "
	   '2002-9-17修改为左连接
	   'sql = sql + " join detail_info on outward.outw_id = detail_info.info_outw_id "
	   'sql = sql + " left outer join detail_info on detail_info.info_detail_id = outward_detail.detail_id "
	   sql = sql + " join product on outward_detail.detail_prod_id = product.prod_id "
	   sql = sql + " join prod_type on product.prod_flag = prod_type.id "
		sql = sql + " where outward.outw_date >= '"&start_date&"' and outward.outw_date <= '"&end_date&"' "
	   sql = sql + " and outward.outw_companyid = "&newcompanyid&" "
		if smallinput <> 0 then
			sql = sql + " and outward.outw_dc_id = "&smallinput&" "
		else
			if flag > 3 then
				sql = sql + " and outward.outw_dc_id in ("&dcservice&") "
			end if
		end if
		if cstr(wood_type) = "1" then
			sql = sql + " and product.prod_dir_flag = 0 "
		elseif cstr(wood_type) = "2" then
			sql = sql + " and product.prod_dir_flag = 1 "
		end if
		
		if clng(oddsia) = 1 then
			if smalllocation <> 0 then
				sql = sql + " and outward_detail.detail_prod_id = "&smalllocation&" "
			end if
	    elseif clng(oddsia) = 2 then
			if prod_type2 <> "" then
				sql = sql + " and product.prod_type = '"&prod_type2&"' "
			end if
			if prod_color <> "" then
				sql	= sql + " and product.prod_color = '"&prod_color&"' "
			end if
	    end if
	    sql = sql + " and outward_detail.detail_elink_id = 0 "
		sql = sql + " order by outward_detail.detail_id asc "
		'response.Write sql 
		'response.End 
		set rs_2 = server.CreateObject("adodb.recordset")
		rs_2.Open sql,conn,1,1
		if not rs_2.EOF then
			do while not rs_2.EOF
				strLine=""
				'for each x in rs.Fields
				'  strLine= strLine & x.value & chr(9)
				'next
				outw_date = rs_2("outw_date")
				outw_sra = trim(rs_2("outw_sra"))
					if isnumeric(outw_sra) = true then
						outw_sra = outw_sra & "'"
					end if
				dc_code =  rs_2("dc_code")
				dc_name =  rs_2("dc_name")
				guest_user = rs_2("guest_user")
				guest_address = rs_2("guest_address")
				guest_tel =  rs_2("guest_tel")
				guest_fax = rs_2("guest_fax")
				guest_takecode = rs_2("guest_takecode")
				'info_type = rs_2("info_type")
				type_name = rs_2("type_name")
				prod_no =  rs_2("prod_no")
				prod_pinming = rs_2("prod_pinming")
				prod_type = rs_2("prod_type")
				prod_color = rs_2("prod_color")
				detail_no = rs_2("detail_no")
				elink_odd = " "
				rec_idcard = " "
				air_take_no = " "
				air_line_no = " "
				air_etd = " "
				air_eta = " "
				'info_yuji_time = rs_2("info_yuji_time")
				database_outw_id = rs_2("detail_outw_id")
				database_guest_id = rs_2("detail_guest_id")
				sql = "select * from detail_info where info_detail_id = (select min(detail_id) as detail_id from outward_detail where detail_outw_id = "&database_outw_id&" and detail_guest_id = "&database_guest_id&") "
				set rst = conn.execute(sql)
				if not rst.eof then
					info_type = rst("info_type")
					info_takeman_id = rst("info_takeman_id")
					info_yuji_time = rst("info_yuji_time")
				else
					info_type = " "
					info_takeman_id = " "
					info_yuji_time = " "
				end if
				rst.close
				set rst = nothing
            
      
      
				strLine = strLine & outw_date& chr(9) & outw_sra & chr(9) & dc_code& chr(9) & dc_name& chr(9) & guest_user& chr(9) & guest_address& chr(9) & guest_tel& chr(9) & guest_fax& chr(9)  & guest_takecode& chr(9)& info_type& chr(9)& type_name &chr(9)& prod_no& chr(9) & prod_pinming& chr(9) & prod_type& chr(9) & prod_color& chr(9) & detail_no& chr(9) & elink_odd & chr(9) & air_take_no & chr(9) & air_line_no & chr(9) & air_etd & chr(9) & air_eta & chr(9) & " " & chr(9) & " " & chr(9) & rec_idcard & chr(9) & info_yuji_time & chr(9) & " " & chr(9) & " " 
				'--将表的数据写入EXCEL
				myfile.writeline strLine
		
			rs_2.MoveNext
			loop 
		end if
        rs_2.Close
        set rs_2 = nothing
      
      
      
      
      
      
      
  end if


set myfile = nothing
Set fs=Nothing
if tt <> 0 then
	link="<A HREF=../"&filename2&">请用鼠标右键点击,然后选择<目标另存为></a>"
	Response.write link
end if
rs.Close
set rs = nothing
call closedatabase
%>
<input class=buttonface type="button" name="ok" value="返回"  onclick="javascript:history.go(-1)">
</body>
</html>