Access转Excel AND Excel导入Access代码.docx
- 文档编号:30178087
- 上传时间:2023-08-05
- 格式:DOCX
- 页数:13
- 大小:18.16KB
Access转Excel AND Excel导入Access代码.docx
《Access转Excel AND Excel导入Access代码.docx》由会员分享,可在线阅读,更多相关《Access转Excel AND Excel导入Access代码.docx(13页珍藏版)》请在冰豆网上搜索。
Access转ExcelANDExcel导入Access代码
Access转ExcelANDExcel导入Access代码
JaAcc_Excel.Asp
--#includefile=common.asp-->
<%
'**********************************************
'CodebyASP导出EXCEL通用
'修改引用By子言(JaStudio)
'**********************************************
Dimxibua
Dimmysql
xibua=Request.QueryString("ids")
ifxibua="all"Then
mysql="select*fromsingup"
Else
mysql="select*fromsingupwhere[系部]='"&xibua&"'"
EndIf
server.scripttimeout=100000'处理时间较长,设置值应大一点
OnErrorResumeNext
setobjExcelApp=CreateObject("Excel.Application")
objExcelApp.DisplayAlerts=false
objExcelApp.Application.Visible=false
objExcelApp.WorkBooks.add
setobjExcelBook=objExcelApp.ActiveWorkBook
setobjExcelSheets=objExcelBook.Worksheets
setobjSpreadsheet=objExcelBook.Sheets
(1)
DimobjRS
SetobjRS=Server.CreateObject("ADODB.Recordset")
objRS.Openmysql,conn,1,3
IfobjRS.EOFthen
response.write("Error")
respose.end
Endif
DimobjField,iCol,iRow
iCol=1'取得列号
iRow=1'取得行号
objSpreadsheet.Cells(iRow,iCol).Value=""&xibua&"部的报名情况"'单元格插入数据
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+1
ForEachobjFieldinobjRS.Fields
'objSpreadsheet.Columns(iCol).ShrinkToFit=true
objSpreadsheet.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
'Displayallofthedata
DoWhileNotobjRS.EOF
iRow=iRow+1
iCol=1
ForEachobjFieldinobjRS.Fields
IfIsNull(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=2
objSpreadsheet.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=2
objSpreadsheet.Cells(iRow,iCol).ParagraphFormat.Alignment=1
EndIf
iCol=iCol+1
Next'objField
objRS.MoveNext
Loop
DimSaveName
SaveName=xibua
DimobjExcel
DimExcelPath
ExcelPath=""&SaveName&".xls"
objExcelBook.SaveAsserver.mappath(ExcelPath)
Response.Write"
response.Write"
Response.Write" white> response.write(" history.back()>返回上一页" Response.Write"" "
objExcelApp.Quit
setobjExcelApp=Nothing
%>
JaExcel_Acc.Asp
td,input,select,textarea,body{font-size:
9pt}
a{color:
blue}
a:
hover{color:
green}
<%
ifsession("xibu")="administrator"then
'**********************************************
'Codeby子言(JaStudio)
'作用:
ASP操作Excel导入ACCESS
'编写时间:
2005.03.13历时:
4小时文件JaExcel.AspJaAcc_Save.Asp
'难点:
Excel文件无确定字段的数据处理
'解决方法:
循环输出所有,分开处理写进ACCESS
'**********************************************
IfRequest.QueryString("action")="do"Then
Dimconn
DimStrConn
DimRs
DimSql
Dimi
DimExName
ExName=Request.Form("ExName")
ExTName=Request.Form("ExTName")
Setconn=Server.CreateObject("ADODB.Connection")
StrConn="Driver={MicrosoftExcelDriver(*.xls)};DBQ="&Server.MapPath("Excel/"&ExName)
conn.OpenStrConn
Setrs=Server.CreateObject("ADODB.Recordset")
Sql="select*from["&ExTName&"$]"
rs.OpenSql,conn,1,1
%>
action=exit>注销| backup()>备份数据库|
white> <% fori=0tors.Fields.Count-1 %> <% Next Response.Write"
Response.Write"
Dima
a=0
dowhilenotrs.eof
fori=0tors.Fields.Count-1
ifimodrs.Fields.Count=0then
Response.Write"
"
Endif
%>
<%
a=a+1
next
rs.MoveNext
Loop
Response.Write"
Response.Write""
rs.close
setrs=nothing
conn.close
setStrConn=nothing
Response.End
Endif
%>
td,input,select,textarea,body{font-size:
9pt}
a{color:
blue}
a:
hover{color:
green}
functionchk()
{
if(document.form1.ExName.value=="")
{
alert("Excel文件名称不能为空!
");
document.form1.ExName.focus();
returnfalse;
}
if(document.form1.ExTname.value=="")
{
alert("Excel数据表文件名称不能为空!
");
document.form1.ExTname.focus();
returnfalse;
}
}
action=exit>注销| backup()>备份数据库|
whitealign=center> 导入数据注意事项 1: 请确保你清楚Excel文件内容字段与导入数据库的字段相同 2: 请确保你清楚Excel文件的表名正确如Sheet1 3: 请确保服务器上有该Excel文件存在于Excel文件夹里,如没有,请上传并记下文件名称 4: 如有不明白请参考Excel文件夹里的[副本学生信息资料.xls],如填写: [Excel地址: 副本学生信息资料.xls][Excel导入数据表名: 学生信息] 5: 如有不明白可以直接联系我获得技术支持: QQ23638564Email: kpggdf@
whitealign=center>