Sub ImportCourse(intCourseID,strFilePath)
Dim Exceldb,Excelrs,strExcelSql Dim aryOption(9) Set Exceldb = Server.CreateObject("ADODB.Connection") Exceldb.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;';Data Source=" & Server.MapPath(strFilePath) '表名一定要以"[表名$]"的格式 strExcelSql="Select * From [Sheet1$]" Set Excelrs=Exceldb.Execute(strExcelSql) If Not Excelrs.EOF And Not Excelrs.BOF Then Call DBConnBegin() Do While Not Excelrs.EOF sAnswer = ChkString(Excelrs("答案"),"") strQuestion = ChkString(Excelrs("题目"),"") aryOption(0) = ChkString(Excelrs("选项1"),"") aryOption(1) = ChkString(Excelrs("选项2"),"") aryOption(2) = ChkString(Excelrs("选项3"),"") aryOption(3) = ChkString(Excelrs("选项4"),"") aryOption(4) = ChkString(Excelrs("选项5"),"") aryOption(5) = ChkString(Excelrs("选项6"),"") aryOption(6) = ChkString(Excelrs("选项7"),"") aryOption(7) = ChkString(Excelrs("选项8"),"") aryOption(8) = ChkString(Excelrs("选项9"),"") aryOption(9) = ChkString(Excelrs("选项10"),"") aryAnswer = Split(sAnswer,"|") Dim strAnswer : strAnswer = "" If strQuestion <> "" Then Application.Lock() '=================================================================================== strSql = "Insert Into [QuestionInfos](QuestionName,CourseID,OptionID) Values('"&strQuestion&"',"&intCourseID&",'')" objConn.Execute(strSql) intQueID = ReadMaxID("QuestionInfos") For i=0 To 9 If aryOption(i)<>"" Then strExecSql = "Insert Into [OptionInfos](Qid,Options) Values("&intQueID&",'"&aryOption(i)&"')" objConn.Execute(strExecSql) If Ubound(aryAnswer)<>-1 Then For j=0 To Ubound(aryAnswer) If int(aryAnswer(j)) = (i+1) Then intMaxID = ReadMaxID("OptionInfos") If strAnswer = "" Then strAnswer = intMaxID Else strAnswer = strAnswer & "|" & intMaxID End If End If Next End If End If Next If strAnswer <> "" Then strSql = "Update [QuestionInfos] Set OptionID='"&strAnswer&"' Where ID = " & intQueID objConn.Execute(strSql) End If '=================================================================================== Application.UnLock() End If Excelrs.MoveNext Loop Call DBConnEnd() End If Excelrs.Close Set Excelrs=nothing Exceldb.Close Set Exceldb=Nothing End Sub |
转载于:https://blog.51cto.com/zjland/122852