任我行/管家婆 导入EXCEL ACCESS数据
来源:互联网 发布:说明书制作软件 编辑:程序博客网 时间:2024/04/29 23:57
- <%
- Dim SqlName
- SqlName =request.form("db")
- if SqlName="" then SqlName="debnC"
- Dim SqlIp
- SqlIp =request.form("svr")
- if SqlIp="" then SqlIp="127.0.0.1"
- Dim SqlUser
- SqlUser =request.form("un")
- Dim SqlPass
- SqlPass =request.form("up")
- strPath =request.form("xls")
- ExcelTbl=request.form("tbl")
- if ExcelTbl="" then ExcelTbl="ptype"
- Dim Dx,DanType
- Dim G1,G2
- Dim objConn1,objConn
- Dim objRs1,objRs
- if request.form("daolu")="导入" and strPath<>"" then call Command1_Click:response.end
- if request.form("daocu")="导出" and strPath<>"" then call Command2_Click:response.end
- if request.form("daocuTxt")="导出" and strPath<>"" then call Command3_Click:response.end
- call ShowDiog
- Sub Command3_Click()
- on error resume next
-
-
- Set objConn1 = server.CreateObject("ADODB.Connection")
- objConn1.Provider = "Microsoft.Jet.OLEDB.4.0 "
- objConn1.ConnectionString = "Data Source=" & strPath & ";Persist Security Info=False;Jet OLEDB:Database Password=123"
- objConn1.Open
- if err.number<>0 then response.write "2"&err.description:response.end
-
- Set objRs1 = server.CreateObject("ADODB.Recordset")
- objRs1.Open "select * from ["&ExcelTbl"]", objConn1, 1, 1
- Set Rs = server.CreateObject("ADODB.Recordset")
- if err.number<>0 then response.write "3"&err.description:response.end
-
-
-
- Dx=(right("0000000000"&objrs1(12),5))
- if not isnumeric(Dx) then Dx=(left(right(objrs1(12),5),4))"01":err.clear
- response.write "管家婆辉煌版8.x"
- response.write vbcrlf
- call WriteTitle
- if err.number<>0 then response.write "4"&err.description:response.end
-
- while not objrs1.eof
- if objrs1(2)="合计" then
-
- if IsNumeric(objrs1(6)) then
- response.write dx&vbtab
- response.write "A"&vbtab
- response.write "101"&vbtab
- response.write "现 金"&vbtab
- response.write objrs1(6)&vbtab
-
- response.write vbcrlf
- end if
-
- objRs1.movenext
- if objRs1.eof then response.end
-
- Dx=(right(objrs1(12),5))
- if not isnumeric(Dx) then Dx=(left(right(objrs1(12),5),4))"01":err.clear
-
-
-
-
- call WriteTitle
- end if
-
- if objrs1(6)<>"" then call WriteRecord
- objRs1.movenext
- wend
- response.write ";导入完成!"
- End Sub
- sub WriteRecord()
- response.write dx&vbtab
- response.write "P1"&vbtab
- response.write ""&vbtab
- response.write objrs1(1)&vbtab
- response.write objrs1(2)&vbtab
- response.write objrs1(6)&vbtab
- response.write objrs1(8)&vbtab
- response.write objrs1(9)&vbtab
- response.write "1"&vbtab
- response.write objrs1(8)&vbtab
- response.write objrs1(9)&vbtab
- response.write "0"&vbtab
- response.write objrs1(8)&vbtab
- response.write "0"&vbtab
- response.write objrs1(11)
- response.write vbcrlf
- End Sub
- sub WriteTitle()
- response.write dx&vbtab
- response.write "P"&vbtab
- if instr(ExcelTbl,"进货") then
- DanType="JH"
- if objrs1(6)<0 then
- response.write "进货退货"&vbtab
- DanType="JHT"
- else
- response.write "进货单"&vbtab
- end if
- else
- DanType="DB"
- if objrs1(6)<0 then
- response.write "销售退货"&vbtab
- DanType="DBT"
- else
- response.write "销售单"&vbtab
- end if
- end if
- response.write "101"&vbtab
- response.write "现 金"&vbtab
- response.write ""&vbtab
- if objrs1(11)<>"" then
- response.write objrs1(11)&vbtab
- else
- response.write "德标"&vbtab
- end if
- response.write ""&vbtab
- if objrs1(16)<>"" then
- response.write objrs1(16)&vbtab
- else
- response.write "公司"&vbtab
- end if
- response.write "主仓库"&vbtab
- response.write "001"&vbtab
- response.write DanType&objrs1(12)&vbtab
- G1=split(""&objrs1(13),"-")
- G2=G1(0)"-"&right("00"&G1(1),2)"-"&right("00"&G1(2),2)
- response.write G2&vbtab
- response.write ""
- response.write vbcrlf
- End Sub
- Sub Command1_Click()
- Dim connStr
- on error resume next
- Set objConn = server.CreateObject("adodb.connection")
- objConn.ConnectionTimeout = 60
- objConn.CommandTimeout = 60
- objConn.CursorLocation = 3
- connStr="Provider=SQLOLEDB.1;Persist Security Info=False;User ID="&SqlUser";Password="&SqlPass";Initial Catalog=" & SqlName & ";Data Source=" & SqlIp & ""
- objConn.Open connStr
-
- if err.number<>0 then response.write "0"&err.description:response.end
-
- objConn.execute "delete from "&ExcelTbl" where 1=1 "
- Set objRs = server.CreateObject("ADODB.Recordset")
- objRs.Open "select * from "&ExcelTbl" where 0=1", objConn, 3, 3
- if err.number<>0 then response.write "1"&err.description:response.end
-
- Set objConn1 = server.CreateObject("ADODB.Connection")
- objConn1.Provider = "Microsoft.Jet.OLEDB.4.0 "
- objConn1.ConnectionString = "Data Source=" & strPath & ";" & "Extended Properties=Excel 8.0;"
- objConn1.Open
- if err.number<>0 then response.write "2"&err.description:response.end
-
- Set objRs1 = server.CreateObject("ADODB.Recordset")
- objRs1.Open "select * from ["&ExcelTbl"$]", objConn1, 1, 1
- if err.number<>0 then response.write "3"&err.description:response.end
- Set Rs = server.CreateObject("ADODB.Recordset")
-
-
-
- Dim Pid
- while not objrs1.eof
- objRs.addnew
- for fori=0 to objRs.fields.count-1
-
- if fori<>30 and fori<>31 then
- objRs(fori)=objRs1(fori)
- end if
- if fori=31 then
- rs.open "select [Rec] from "&ExcelTbl" where typeId='"&objRs1(1)"'",objConn, 1, 1
- objRs(31)=rs(0)
- rs.close
- end if
- next
- response.write "导入"&objRs(0)"完成!"
- objRs.update
- objRs.Requery
-
- objRs1.movenext
- wend
- response.write "导入完成!"
- End Sub
-
- Sub Command2_Click()
- on error resume next
- Set objConn = server.CreateObject("ADODB.Connection")
- objConn.Provider = "Microsoft.Jet.OLEDB.4.0 "
- objConn.ConnectionString = "Data Source=" & strPath & ";" & "Extended Properties=Excel 8.0;"
- objConn.Open
- if err.number<>0 then response.write "1"&err.description:response.end
-
- Set objConn1 = server.CreateObject("adodb.connection")
- objConn1.ConnectionTimeout = 60
- objConn1.CommandTimeout = 60
- objConn1.CursorLocation = 3
- objConn1.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID="&SqlUser";Password="&SqlPass";Initial Catalog=" & SqlName & ";Data Source=" & SqlIp & ""
- if err.number<>0 then response.write "2"&err.description:response.end
-
- Set objRs = server.CreateObject("ADODB.Recordset")
- objRs.Open "select * from ["&ExcelTbl"1$]", objConn, 3, 3
-
- Set objRs1 = server.CreateObject("ADODB.Recordset")
- objRs1.Open "select * from "&ExcelTbl"", objConn1, 1, 1
- if err.number<>0 then response.write "3"&err.description:response.end
-
- Dim i
- Dim strValue
- Dim strTitle
- Dim strSql
- objRs.addnew
- for fori=0 to objRs.fields.count-1
-
- objRs(fori)=objRs1(fori).name
- next
- response.write "导出题头"&objrs1(0)"完成!"
- objRs.update
-
-
- while not objrs1.eof
- objRs.addnew
- for fori=0 to objrs1.fields.count-1
-
-
- objRs(fori)=objRs1(fori)
-
- next
- response.write "导出"&objrs1(0)"完成!"
- objRs.update
-
- objRs1.movenext
-
- wend
- response.write "导出完成!"
- End Sub
- %>
- <%
- %>
- <%sub ShowDiog()%>
- <form action="" method="post" enctype="application/x-www-form-urlencoded" name="form1">
- <table width="98%" border="1">
- <tr>
- <td>EXCEL文件</td>
- <td><input name="xls" type="file" size="80" path="<%=strPath%>"></td>
- </tr>
- <tr>
- <td>SQL</td>
- <td>服务器
- <input name="svr" type="text" value="<%=SqlIp%>">
- 数据库
- <input name="db" type="text" value="<%=SqlName%>">
- 用户
- <input name="un" type="text" value="sa">
- 口令
- <input name="up" type="password" value="jiaguo">
- </td>
- </tr>
- <tr>
- <td> </td>
- <td>表名:
- <input name="tbl" type="text" id="tbl" value="<%=ExcelTbl%>">
- 本操作仅适用于本机导入,因管家婆2005辉煌版数据导入所需而做,用于其它地方可能会产生错误。</td>
- </tr>
- <tr>
- <td><a href="?">返回</a></td>
- <td align="center">EXCEL
- <input name="daolu" type="submit" id="daolu" value="导入">
- 到SQL
- <input name="daocu" type="submit" id="daocu" value="导出">
- 到EXCEL ACCESS
- <input name="daocuTxt" type="submit" id="daocuTxt" value="导出">
- 导出为文本</td>
- </tr>
- <tr>
- <td>说明</td>
- <td valign="middle"><p>1、商品类别,表名ptype。</p>
- <p>2、草稿单据,从ACCESS导出,有“进货”字样为进货,其它为出货。导出后,查看源代码另存为文本即可用于管家婆的单据导入,省却对表的直接操作。</p></td>
- </tr>
- </table>
- </form>
- <%End Sub%>