ASP Classsic - 插入到Excel .XLS列或创建一个真正的Excel格式文件 [英] ASP Classsic - Insert into Excel .XLS columns or Create a Real Excel Format File

问题描述

我想我的MS SQL数据添加到下面的Excel的.xls文件。

I would like to add my MS SQL data into the Excel .XLS file below.

我试过如下：

    <% 
    Response.Clear
    Response.Buffer = False
    Response.ContentType = "application/vnd.ms-excel"
    Response.AddHeader "Content-Disposition", "attachment;filename=example.xls"

        ' --OPEN MS SQL DATABASE CODE--
        ' --OPEN RECORDSET CODE--
     %>
    <table>  
        <tr>
            <th>DEBNAME</th>
            <th>INV_ID</th>
            <th>INV_DATE</th>
            <th>PO_NO</th>
            <th>INVAMT</th>
        </tr>
    <% 'START OF: ROW LOOP 
      Do Until objRS.EOF
     %>
        <tr>
            <td><%=objRS("DEBNAME")%></td>
            <td><%=objRS("INV_ID")%></td>
            <td><%=objRS("INV_DATE")%></td>
            <td><%=objRS("PO_NO")%></td>
            <td><%=objRS("INVAMT")%></td>
        </tr>
    <%
      objRS.MoveNext
      Loop
      objRS.Close
    'END OF: ROW LOOP
     %>
</table>

然后当我尝试打开它，它给了我这个错误：

THEN WHEN I TRY TO OPEN IT, IT GIVES ME THIS ERROR:

我想这是因为该文件只有在它里面的HTML code（我通过记事本打开它检查出来）

I think this is because the file only has HTML code inside it (I opened it via Notepad to check it out)

如果我点击的是的数据将显示，但我想产生一个真正的.xls文件或使用empty.xls文件，克隆它，然后将数据插入到吧。

If I click on Yes the data will show but I would like to generate a real .xls file or use an empty.xls file, clone it, and then insert the data into it.

感谢您的帮助！的

THANKS FOR ANY HELP!

这是我如何empty.xls文件看起来

This is how my empty.xls file looks

推荐答案

使用一个空文件准备填补是最无痛的方法。

通过填写相关数据提供商表（S）后，可以服务于静态excel文件或流吧。

这里的演示： http://aspfiddle.com/ymuikl0id4/test.asp （将被删除几天后）

请确保您有权限创建新的文件。

希望它帮助。

Using an empty file that ready to fill is the most painless method.
After filling the sheet(s) via related data providers, you can serve the static excel file or stream it.
Here's the demo : http://aspfiddle.com/ymuikl0id4/test.asp (will be deleted a few days later)
Make sure that you have permission to create new files.
Hope it helps.

<%@Language=VBScript CodePage=65001%>
<%
If Request.Form.Count Then 'form handling
    Dim Fso
    Set Fso = Server.CreateObject("Scripting.FileSystemObject")

    Dim emptyXlsFileName
        emptyXlsFileName = Server.Mappath("test.xls") 'empty original file path

    Dim filledXlsFileName
        filledXlsFileName = Replace(Fso.GetTempName, ".tmp", ".xls") 'temp file will be created and filled

    Fso.CopyFile emptyXlsFileName, Server.Mappath(filledXlsFileName)

    Dim Connection
    Set Connection = Server.CreateObject("Adodb.Connection")
        Connection.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.Mappath(filledXlsFileName) & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=0"";"
        'Since Jet.OLEDB.4.0 is a 32-Bit only provider
        'if you need to run this application in a 64-bit environment
        'you have to install 64-bit version of Access Database Engine (http://www.microsoft.com/en-us/download/details.aspx?id=13255) to the server
        'then use the following connection string instead
        'Connection.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Server.Mappath(filledXlsFileName) & ";Extended Properties=""Excel 8.0;HDR=YES;IMEX=0"";"

    Dim Recordset
    Set Recordset = Server.CreateObject("Adodb.Recordset")
        Recordset.Open "[Sheet1$]", Connection, , 3
        Recordset.Addnew
        Recordset("DEBNAME").Value = Request.Form("DEBNAME")
        Recordset("INV_ID").Value = Request.Form("INV_ID")
        Recordset("INV_DATE").Value = Request.Form("INV_DATE")
        Recordset("PO_NO").Value = Request.Form("PO_NO")
        Recordset("INVAMT").Value = Request.Form("INVAMT")
        Recordset.Update
        Recordset.Close
    Set Recordset = Nothing

        Connection.Close
    Set Connection = Nothing

    Const BufferSize = 8192

    Response.ContentType = "application/vnd.ms-excel"
    Response.AddHeader "Content-Disposition", "attachment;filename=example.xls"
    Dim Stm
    Set Stm = Server.CreateObject("Adodb.Stream")
        Stm.Type = 1 'adTypeBinary
        Stm.Open
        Stm.LoadFromFile Server.Mappath(filledXlsFileName)
        Do While Not Stm.EOS
            Response.BinaryWrite Stm.Read(BufferSize)
            Response.Flush
            If Not Response.IsClientConnected Then Exit Do
        Loop
        Stm.Close
    Set Stm = Nothing
    Fso.DeleteFile Server.Mappath(filledXlsFileName)
    Response.End

    Set Fso = Nothing
End If
%><!doctype html>
<html lang="en">
<head>
    <title>Insert Into Excel File</title>
    <meta charset="utf-8">
</head>
<body>
    <form method="post" target="_blank">
        <table>
            <tr><td>DEBNAME</td><td><input type="text" name="DEBNAME" value="John Doe" /></td></tr>
            <tr><td>INV_ID</td><td><input type="text" name="INV_ID" value="123" /></td></tr>
            <tr><td>INV_DATE</td><td><input type="text" name="INV_DATE" value="24 Jun, 2014" /></td></tr>
            <tr><td>PO_NO</td><td><input type="text" name="PO_NO" value="321" /></td></tr>
            <tr><td>INVAMT</td><td><input type="text" name="INVAMT" value="etc" /></td></tr>
            <tr><td>&nbsp;</td><td><input type="submit" value="Add & Download" /></td></tr>
        </table>
    </form>
</body>
</html>

这篇关于ASP Classsic - 插入到Excel .XLS列或创建一个真正的Excel格式文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！