Dynamically Created multiple sheet excel file in asp (idea might work for php too)

Discussion in 'Programming' started by joe, Oct 8, 2008.

to remove all ads.
  1. joe

    joe New Member

    Messages:
    82
    This is a way to do it in classic asp. If anyone wants to know, here's how you do it:

    Say you have 2 asp pages that dynamically create tables. You want these tables to be exported into excel on separate sheets. What you will need to do is create a new page call it export.asp (for example) and create two html files by using asp's file system object. See an example here:

    Code:
    '''''''''''''''''
    Set objFSO1 = CreateObject("Scripting.FileSystemObject")
    Set fs = objFSO1.CreateTextFile(Server.MapPath("pg1.html"))
    fs.WriteLine "<html>"
    fs.WriteLine "<head>"
    fs.WriteLine "</head>"
    fs.WriteLine "<table><tr><td>Col 1</td><td>Col 2</td></tr>"
    
    '''bunch of code to create table
    
    fs.WriteLine "</table></div></body></html>"
    
    ''''''''''''' repeat for as many sheets as needed
    
    In the same export.asp file after you've created the html files you will need to create your excel file.

    Code:
    <% 
    Response.Buffer = TRUE
    Response.ContentType = "application/vnd.ms-excel"
    Response.AddHeader "Content-Disposition", "attachment; filename=file_name.xls"
    
    
     %>
    
    <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 name="Excel Workbook Frameset">
    <meta http-equiv=Content-Type content="text/html; charset=windows-1252">
    <meta name=ProgId content=Excel.Sheet>
    <meta name=Generator content="Microsoft Excel 11">
    <link rel=File-List href="./filelist.xml">
    <xml>
      <o:OfficeDocumentSettings>
      <o:DownloadComponents/>
      <o:LocationOfComponents HRef="file:///C:\Program%20Files\selfheal\Off2k3RTM\"/>
     </o:OfficeDocumentSettings>
    </xml>
    <xml>
    <x:ExcelWorkbook>
      <x:ExcelWorksheets>
    ''''''''''''''
       <x:ExcelWorksheet>
        <x:Name>Name of Sheet</x:Name>
    	<x:WorksheetSource HRef="full url of html file"/>
       </x:ExcelWorksheet>
    '''''''''''''Repeat inside for other sheets
      </x:ExcelWorksheets>
      
     </x:ExcelWorkbook>
    </xml>
    </head>
    
    Voila, you have a dynamically created excel file with multiple sheets. I'm sure there might be another way to do this, but I have not found any way to do it online in the past several months. There are ways you can do it in .NET, but I don't have .NET so I can't use it.
     
  2. zkiller

    zkiller Super Moderator Staff Member

    Messages:
    1,639
    If the data is coming from a database you wouldn't need to create the two html files. You could just create the excel spreadsheets dynamically.

    Here's an example I just found using google: http://www.codetoad.com/asp_excel.asp
     
  3. rportokalos

    rportokalos New Member

    Messages:
    1
    IS IT when im generating the excel a pop up will be shown but it will close when the excel is opened? the problem the pop up can not close when i use ie 6? does any one has any idea to close that?
     

Share This Page