一：ASP连结EXECEL【SQL 操作EXCEL】
<%dim connset conn=server.createobject("adodb.connection")conn.open "driver={Microsoft Excel Driver (*.xls)};DBQ="&server.mappath("execl.xls")
dim rsset rs=conn.execute("select * from [Sheet1$]")%>
数据表后面要加$号才行
EXECL里第一行就是字段名,用法用ACCESS差不多
二 :ASP: EXCEL导入到ACCESS
原始EXECL表中的信息导到WEB里~~
下面连接到student.xls表sheet1$ '表后面要加个$的,我以前试用,一定要加这个符号Set conn = Server.CreateObject("ADODB.Connection")conn.Open "Driver={Microsoft Excel Driver (*.xls)};ReadOnly=0;DBQ=" & Server.MapPath("student.xls")SQL1="select * from [sheet1$]" Set rs = Server.CreateObject("ADODB.Recordset")rs.Open SQL1, conn, 3, 3'下面连接到data.mdb表tablecurDir = Server.MapPath("../data.mdb") Set conn1 = Server.CreateObject("ADODB.Connection")conn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & curDirSet rs1 = Server.CreateObject("ADODB.Recordset")Set rs1.ActiveConnection = conn1rs1.Source = "select * from table"rs1.CursorType = 3 ' adOpenKeysetrs1.LockType = 3 'adLockOptimisticrs1.OpenDo While Not rs.Eofrs1.AddNewrs1(0)=jfor i=0 to rs.Fields.Count-1 rs1(i)=Trim(rs(i))Nextrs1.Updaters.MoveNextj=j+1Looprs.Closers1.Closeconn.Closeconn1.CloseSet rs=nothingSet conn=nothing
如果导出数据结构不复杂,直接用TABLE,在页面里加入<%response.ContentType ="application/vnd.ms-excel"%>即可!
server.scripttimeout=100000 '处理时间较长，设置值应大一点On Error Resume Next set objExcelApp = createObject("Excel.Application")objExcelApp.DisplayAlerts = falseobjExcelApp.Application.Visible = falseobjExcelApp.WorkBooks.addset objExcelBook = objExcelApp.ActiveWorkBookset objExcelSheets = objExcelBook.Worksheetsset objSpreadsheet = objExcelBook.Sheets(1)Dim ConnDim ConnstrDim DBDB="weste.mdb" '这里选择数据库Set conn = Server.createObject("ADODB.Connection")Connstr="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath(DB)Conn.Open ConnstrDim objRSSet objRS = Server.createObject("ADODB.Recordset") objRS.Open "select * FROM FriendLink",conn,1,3 '这里用sql语句查询需要导出的内容If objRS.EOF then response.write("Error") respose.endEnd ifDim objField, iCol, iRow iCol = 1 '取得列号iRow = 1 '取得行号objSpreadsheet.Cells(iRow, iCol).Value = "用ASP将Access中的数据导入到Excel文件——西部e网" '单元格插入数据objSpreadsheet.Columns(iCol).ShrinkToFit=true '设定是否自动适应表格单元大小(单元格宽不变）'设置Excel表里的字体 objSpreadsheet.Cells(iRow, iCol).Font.Bold = True '单元格字体加粗objSpreadsheet.Cells(iRow, iCol).Font.Italic = False '单元格字体倾斜objSpreadsheet.Cells(iRow, iCol).Font.Size = 20 '设置单元格字号objSpreadsheet.Cells(iRow, iCol).ParagraphFormat.Alignment=1 '设置单元格对齐格式:居中objspreadsheet.Cells(iRow,iCol).font.name="宋体" '设置单元格字体objspreadsheet.Cells(iRow,iCol).font.ColorIndex=2 '设置单元格文字的颜色，颜色可以查询，2为白色objSpreadsheet.Range("A1:F1").merge '合并单元格(单元区域)objSpreadsheet.Range("A1:F1").Interior.ColorIndex = 1 '设计单元络背景色'objSpreadsheet.Range("A2:F2").WrapText=true '设置字符回卷（自动换行）iRow=iRow+1For Each objField in objRS.Fields 'objSpreadsheet.Columns(iCol).ShrinkToFit=trueobjSpreadsheet.Cells(iRow, iCol).Value = objField.Name'设置Excel表里的字体 objSpreadsheet.Cells(iRow, iCol).Font.Bold = True objSpreadsheet.Cells(iRow, iCol).Font.Italic = False objSpreadsheet.Cells(iRow, iCol).Font.Size = 20 objSpreadsheet.Cells(iRow, iCol).Halignment = 2 '居中 iCol = iCol + 1 Next 'objField 'Display all of the data Do While Not objRS.EOF iRow = iRow + 1 iCol = 1 For Each objField in objRS.Fields If IsNull(objField.Value) then objSpreadsheet.Cells(iRow, iCol).Value = "" Else objSpreadsheet.Columns(iCol).ShrinkToFit=true objSpreadsheet.Cells(iRow, iCol).Value = objField.Value objSpreadsheet.Cells(iRow, iCol).Halignment = 2objSpreadsheet.Cells(iRow, iCol).Font.Bold = False objSpreadsheet.Cells(iRow, iCol).Font.Italic = False objSpreadsheet.Cells(iRow, iCol).Font.Size = 10 'objSpreadsheet.Cells(iRow, iCol).Halignment = 2objSpreadsheet.Cells(iRow, iCol).ParagraphFormat.Alignment=1End If iCol = iCol + 1 Next 'objField objRS.MoveNext Loop Dim SaveName SaveName="temp1"Dim objExcel Dim ExcelPath ExcelPath = "" & SaveName & ".xls" objExcelBook.SaveAs server.mappath(ExcelPath)response.write("<a href='" & server.URLEncode(ExcelPath) & "'>下载</a>") objExcelApp.Quitset objExcelApp = Nothing
