• ASP下MDB转xls

    post by Holmesian / 2008-11-3 12:47 Monday
    网上的代码

    有几个不错

    今天做asp导出excel的时候，发现如果数值很大的话，excel会自动科学计数，手动变为文本后，excel自动把后面的尾数全部换成0000，这样的话在实际中就有很多问题。网上查了一下解决方式。记录之：

        
    点击这里生成excel

      
          
    <%   
if request("act")="" then     
'response.write ("生成excel文件")     
else   
'dim conn,strconn     
'strconn=driver=;server=wen;uid=sa;pwd=;database=db_test     
'set  conn=server.createobject("adodb.connection")     
'conn.open strconn   
dim  rs,sql,filename,fs,myfile,x   
set fs=server.createobject("scripting.filesystemobject")     
'--假设你想让生成的excel文件做如下的存放     
filename = server.mappath("data.xls")     
'--如果原来的excel文件存在的话删除它     
if fs.fileexists(filename) then   
fs.deletefile(filename)    
'response.write "dele"    
end if   
'--创建excel文件   
set myfile = fs.createtextfile(filename,true)   
set rs = server.createobject("adodb.recordset")   
'--从数据库中把你想放到excel中的数据查出来     
sql = "Select mid(zgbm_no,1,6) as dzdm,dwmc as x01,xmmc as x02,frdm as a05,jsdz_bm as a03,v4 as x04,v5 as x05,v6 as x06,v7 as x07,v8 as x08,v9 as x09,v10_1 as x10,v11_1 as x11,v12 as x12,v13 as x13,v14 as x14,v15 as x15,v16 as x16,v17 as x17,v18 as x18,v19 as x19,dwfzr as xdw,tjfzr as xtj,tbr as xtb,rq as xrq FROM xmzb where zgbm_no like '"+session("zgbm_no")+"%' order by id desc"  
'response.write sql   
rs.open sql,conn,1,1   
if rs.eof and rs.bof then   
else   
dim strline,responsestr     
strline=""  
for each x in  rs.fields   
strline = strline &amp; x.name &amp; chr(9)   
next   
'--将表的列名先写入excel     
myfile.writeline strline   
do while not rs.eof     
strline=""  
for each x in  rs.fields    
  strline= strline &amp; "="""&amp;x.value """"&amp; chr(9)    
'if x<>"" then   
'strline = strline &amp; cstr(x.value) &amp; chr(9)     
'else   
'strline = strline &amp; x.value &amp; chr(9)   
'end if   
next   
'--将表的数据写入excel     
myfile.writeline strline   
rs.movenext   
loop   
end if   
response.write "生成成功！请  "+""+"点击这里下载"+""  
rs.close     
set  rs  =  nothing   
  end if   
%>

            
            
        
      
      
    其中：strline= strline & "="""&x.value """"& chr(9)  就是把数值转换成文本文件。

    其中top文件是数据库联接文件。内容如下：

     

    <%   
ConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &amp; Server.MapPath("access.mdb")   
Set conn = Server.CreateObject("ADODB.Connection")   
conn.open ConnectString   
%>  


      set   objExcel=CreateObject("Excel.Application")   
  objExcel.Workbooks.Open(server.mappath("\test")&"\test.xlt")   '打开Excel模板     
  objExcel.Sheets(1).select   '选中工作页     
  set   sheetActive=objExcel.ActiveWorkbook.ActiveSheet       
  sheetactive.cells(2,3)="success"   
  objExcel.ActiveWorkbook.saveas   filePos&"test.xls"   
  objExcel.quit     
  set   objExcel=Nothing

      我已经试过了，可以实现





    下面这个不错  如果不是需要二次处理导入成其他数据库形式的话   这种处理方法是可视化最好的


    常要把各种各样的查询结果输出到EXCEL中，所以做了下面这段小程序，用于把一个SQL的SELECT查询出的结果输出为EXCEL格式文件，这个程序你只要设好用于取得一个记录集的SQL的SELECT查询语句和一个文件名，程序就能输出EXCEL格式文件了，这个程序一共由三个文件构成，第一个文件的文件名为：toexcel.asp是主文件，内容如下：

    <%

'前面是和来链接到数据库,请自行书写相关语句，此处略过

sql=session("toexcelsql")  '这里是要输出EXCEL的查询语句，如 "SESECT * FORM CAI WHERE 性别='女'"
filename="excel.xls"   ' 要输出的EXCEL文件的文件名, 你只要改以上两句就行了,其它的都不要改.

'你只要修改以上两变量就行了.其它的我都做好了.

call toexcel(FILENAME,sql)
set conn=nothing

function ReadText(FileName)  '这是一个用于读出文件的函数
set adf=server.CreateObject("Adodb.Stream")
with adf
  .Type=2
  .LineSeparator=10
  .Open
  .LoadFromFile (server.MapPath(FileName))
  .Charset="GB2312"
  .Position=2
  ReadText=.ReadText
  .Cancel()
  .Close()
end with
set ads=nothing
end function

sub SaveText(FileName,Data)  '这是一个用于写文件的函数
set fs= createobject("scripting.filesystemobject") 
    set ts=fs.createtextfile(server.MapPath(FileName),true)
    ts.writeline(data)
    ts.close
    set ts=nothing
    set fs=nothing
end sub

sub toexcel(filename,sql)  '这是一个根据SQL语句和FILENAME生成EXCEL文件
  Set rs=Server.CreateObject("ADODB.RecordSet") 
  rs.Open sql,conn,1,3
  TOEXCELLR="<table width='100%'><tr >"
  set myfield=rs.fields
  dim fieldname(50)
  for i=0 to myfield.count-1
     toexcellr=toexcellr&amp;"<td class=xl24>"&amp;MYFIELD(I).NAME&amp;"</td>"
     fieldname(i)=myfield(i).name
     if myfield(i).type=135 then datename=datename&amp;myfield(i).name&amp;","
  next
  toexcellr=toexcellr&amp;"</tr>"
  do while not rs.eof
    toexcellr=toexcellr&amp;"<tr>"
    for i=0 to myfield.count-1 
      if instr(datename,fieldname(i)&amp;",")<>0 then
        if not isnull(rs(fieldname(i))) then
          TOEXCELLR=TOEXCELLR&amp;"<td  class=xl25 ><p align='left'>"&amp;formatdatetime(rs(fieldname(i)),2)&amp;"</p></td>"
        else
           TOEXCELLR=TOEXCELLR&amp;"<td  class=xl25 ><p align='left'>　</p></td>"
        end if
      else
         TOEXCELLR=TOEXCELLR&amp;"<td class=xl24 >"&amp;rs(fieldname(i))&amp;"</td>"
      end if
    next
    toexcellr=toexcellr&amp;"</tr>"
    rs.movenext
  loop
  toexcellr=toexcellr&amp;"</table>"
  tou=readtext("tou.txt")
  di=readtext("di.txt")
  toexcellr=tou&amp;toexcellr&amp;di
  call savetext(filename,toexcellr)
end sub
%>
<html>
<head>
<meta http-equiv="refresh" content="3;URL=<%=filename%>"> 
<meta http-equiv="Content-Language" content="en-us">
<meta http-equiv="Content-Type" content="text/html; charset=gb2312">
<title>正在生成EXLCE文件</title>
</head>
<BODY>
正在生成EXLCE文件....
</BODY>
</HTML>


    ＊＊＊＊＊＊＊＊＊＊＊＊＊＊第二个文件名为：di.txt 内容如下：

    <table x:str border=0 cellpadding=0 cellspacing=0 width=288 style='border-collapse:
collapse;table-layout:fixed;width:216pt'>
<![if supportMisalignedColumns]>
<tr height=0 style='display:none'>
  <td width=72 style='width:54pt'></td>
  <td width=72 style='width:54pt'></td>
  <td width=72 style='width:54pt'></td>
  <td width=72 style='width:54pt'></td>
</tr>
<![endif]>
</table>


    ＊＊＊＊＊＊＊＊＊＊＊＊第三个文件的文件名为：tou.TXT　内容如下：

    <html xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns="http://www.w3.org/TR/REC-html40">

<head>
<meta http-equiv=Content-Type content="text/html; charset=GB2312">
<meta name=ProgId content=Excel.Sheet>
<meta name=Generator content="Microsoft Excel 9">
<link rel=File-List href="./222.files/filelist.xml">
<link rel=Edit-Time-Data href="./222.files/editdata.mso">
<link rel=OLE-Object-Data href="./222.files/oledata.mso">
<!--[if gte mso 9]><xml>
<o:DocumentProperties>
  <o:Author>xky</o:Author>
  <o:LastAuthor>xky</o:LastAuthor>
  <o:Created>2002-05-27T17:51:00Z</o:Created>
  <o:LastSaved>2002-06-22T10:03:03Z</o:LastSaved>
  <o:Company>zydn</o:Company>
  <o:Version>9.2812</o:Version>
</o:DocumentProperties>
<o:OfficeDocumentSettings>
  <o:DownloadComponents/>
  <o:LocationOfComponents HRef="file:///E:/msowc.cab"/>
</o:OfficeDocumentSettings>
</xml><![endif]-->
<style>
<!--table
{mso-displayed-decimal-separator:"&amp;#92;.";
mso-displayed-thousand-separator:"&amp;#92;,";}
@page
{margin:1.0in .75in 1.0in .75in;
mso-header-margin:.5in;
mso-footer-margin:.5in;}
tr
{mso-height-source:auto;
mso-ruby-visibility:none;}
col
{mso-width-source:auto;
mso-ruby-visibility:none;}
br
{mso-data-placement:same-cell;}
.style0
{mso-number-format:General;
text-align:general;
vertical-align:bottom;
white-space:nowrap;
mso-rotate:0;
mso-background-source:auto;
mso-pattern:auto;
color:windowtext;
font-size:9.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:宋体;
mso-generic-font-family:auto;
mso-font-charset:134;
border:none;
mso-protection:locked visible;
mso-style-name:常规;
mso-style-id:0;}
td
{mso-style-parent:style0;
padding-top:1px;
padding-right:1px;
padding-left:1px;
mso-ignore:padding;
color:windowtext;
font-size:9.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:宋体;
mso-generic-font-family:auto;
mso-font-charset:134;
mso-number-format:General;
text-align:general;
vertical-align:bottom;
border:none;
mso-background-source:auto;
mso-pattern:auto;
mso-protection:locked visible;
white-space:nowrap;
mso-rotate:0;}
.xl24
{mso-style-parent:style0;
border:.5pt solid windowtext;}
.xl25
{mso-style-parent:style0;
mso-number-format:"Long Date";
text-align:left;
border:.5pt solid windowtext;}
ruby
{ruby-align:left;}
rt
{color:windowtext;
font-size:9.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:宋体;
mso-generic-font-family:auto;
mso-font-charset:134;
mso-char-type:none;
display:none;}
-->
</style>
<!--[if gte mso 9]><xml>
<x:ExcelWorkbook>
  <x:ExcelWorksheets>
   <x:ExcelWorksheet>
    <x:Name>Sheet1</x:Name>
    <x:WorksheetOptions>
     <x:DefaultRowHeight>225</x:DefaultRowHeight>
     <x:Print>
      <x:ValidPrinterInfo/>
      <x:PaperSizeIndex>9</x:PaperSizeIndex>
      <x:HorizontalResolution>-3</x:HorizontalResolution>
      <x:VerticalResolution>0</x:VerticalResolution>
     </x:Print>
     <x:Selected/>
     <x:Panes>
      <x:Pane>
       <x:Number>3</x:Number>
       <x:ActiveRow>24</x:ActiveRow>
       <x:ActiveCol>5</x:ActiveCol>
      </x:Pane>
     </x:Panes>
     <x:ProtectContents>False</x:ProtectContents>
     <x:ProtectObjects>False</x:ProtectObjects>
     <x:ProtectScenarios>False</x:ProtectScenarios>
    </x:WorksheetOptions>
   </x:ExcelWorksheet>
   <x:ExcelWorksheet>
    <x:Name>Sheet2</x:Name>
    <x:WorksheetOptions>
     <x:DefaultRowHeight>225</x:DefaultRowHeight>
     <x:ProtectContents>False</x:ProtectContents>
     <x:ProtectObjects>False</x:ProtectObjects>
     <x:ProtectScenarios>False</x:ProtectScenarios>
    </x:WorksheetOptions>
   </x:ExcelWorksheet>
   <x:ExcelWorksheet>
    <x:Name>Sheet3</x:Name>
    <x:WorksheetOptions>
     <x:DefaultRowHeight>225</x:DefaultRowHeight>
     <x:ProtectContents>False</x:ProtectContents>
     <x:ProtectObjects>False</x:ProtectObjects>
     <x:ProtectScenarios>False</x:ProtectScenarios>
    </x:WorksheetOptions>
   </x:ExcelWorksheet>
  </x:ExcelWorksheets>
  <x:WindowHeight>6600</x:WindowHeight>
  <x:WindowWidth>12000</x:WindowWidth>
  <x:WindowTopX>0</x:WindowTopX>
  <x:WindowTopY>1395</x:WindowTopY>
  <x:ProtectStructure>False</x:ProtectStructure>
  <x:ProtectWindows>False</x:ProtectWindows>
</x:ExcelWorkbook>
</xml><![endif]-->
</head>

<body  link=blue vlink=purple>


    标签: 资源 解决方法 asp excel mdb
    « 强制页面从服务器更新 | ASP实现Excel数据导入ACCESS»

    发表评论：