Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 12 of 12
  1. #1
    New Coder
    Join Date
    Sep 2005
    Posts
    25
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Generate Excel file with multiple worksheets using ASP

    I am able to generate an Excel file with ASP, but with one worksheet only. How do I generate an Excel file with multiple worksheets ?

    I use the following code :

    Code:
    <%
    Response.ContentType = "application/vnd.ms-excel"
    Response.AddHeader "Content-Disposition", "attachment;filename=file01.xls"
    %>
    <html>
    <head>
      <title>File01</title>
    </head>
    <body>
    <table width="100%" border="1" cellpadding="0" cellspacing="0">
    <tr>
      <td>1</td>
      <td>2</td>
      <td>3</td>
    </tr>
    </table>
    </body>
    </html>

  • #2
    Regular Coder
    Join Date
    May 2007
    Location
    UK
    Posts
    180
    Thanks
    0
    Thanked 18 Times in 18 Posts
    If you save a multi-worksheet Excel workbook as a web page (.htm) then you will end up with more than one file. If you were to to do that in ASP then you would have to create the supporting files and reference them in the main page.

    If your target audience are using Excel 2003 or 2007 then you have some more options.

    A fairly simple one is to output your multi-worksheet workbook as XML. Microsoft have documented this somewhere (probably on MSDN) but I often find it easier to create a mockup of the results file in Excel and save it as XML to see what is required.

    Here is a fairly simple example:
    Code:
    <%
    Response.ContentType = "application/vnd.ms-excel"
    Response.AddHeader "Content-Disposition", "attachment;filename=file01.xls"
    %>
    <?xml version="1.0"?>
    <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
    xmlns:o="urn:schemas-microsoft-com:office:office"
    xmlns:x="urn:schemas-microsoft-com:office:excel"
    xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
    xmlns:html="http://www.w3.org/TR/REC-html40">
    <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
    <DownloadComponents/>
    <LocationOfComponents HRef="file:///\\"/>
    </OfficeDocumentSettings>
    <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
    <WindowHeight>12525</WindowHeight>
    <WindowWidth>15195</WindowWidth>
    <WindowTopX>480</WindowTopX>
    <WindowTopY>120</WindowTopY>
    <ActiveSheet>2</ActiveSheet>
    <ProtectStructure>False</ProtectStructure>
    <ProtectWindows>False</ProtectWindows>
    </ExcelWorkbook>
    <Styles>
    <Style ss:ID="Default" ss:Name="Normal">
    <Alignment ss:Vertical="Bottom"/>
    <Borders/>
    <Font/>
    <Interior/>
    <NumberFormat/>
    <Protection/>
    </Style>
    </Styles>
    <Worksheet ss:Name="Sheet1">
    <Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1"
    x:FullRows="1">
    <Row>
    <Cell><Data ss:Type="Number">1</Data></Cell>
    </Row>
    </Table>
    <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
    <ProtectObjects>False</ProtectObjects>
    <ProtectScenarios>False</ProtectScenarios>
    </WorksheetOptions>
    </Worksheet>
    <Worksheet ss:Name="Sheet2">
    <Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1"
    x:FullRows="1">
    <Row>
    <Cell><Data ss:Type="Number">2</Data></Cell>
    </Row>
    </Table>
    <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
    <ProtectObjects>False</ProtectObjects>
    <ProtectScenarios>False</ProtectScenarios>
    </WorksheetOptions>
    </Worksheet>
    <Worksheet ss:Name="Sheet3">
    <Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1"
    x:FullRows="1">
    <Row>
    <Cell><Data ss:Type="Number">3</Data></Cell>
    </Row>
    </Table>
    <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
    <Selected/>
    <Panes>
    <Pane>
    <Number>3</Number>
    <ActiveRow>1</ActiveRow>
    </Pane>
    </Panes>
    <ProtectObjects>False</ProtectObjects>
    <ProtectScenarios>False</ProtectScenarios>
    </WorksheetOptions>
    </Worksheet>
    </Workbook>

  • Users who have thanked SouthwaterDave for this post:

    Gyte (01-02-2009)

  • #3
    New Coder
    Join Date
    Sep 2005
    Posts
    25
    Thanks
    1
    Thanked 0 Times in 0 Posts
    The code works well, but when the amount of data is huge, Excel generates an error that there to much codes.
    The generated Excel file is about 1.5MB in size and it contains 5 different worksheets.

    Is there a limit to the maximum amount of data that can be generated in an Excel-file through XML? How can this problem be overcome ?
    Last edited by Gyte; 01-05-2009 at 04:34 PM.

  • #4
    Regular Coder
    Join Date
    May 2007
    Location
    UK
    Posts
    180
    Thanks
    0
    Thanked 18 Times in 18 Posts
    Excel 2003 only supports 65,535 lines. Server memory will probably be an issue at some stage too.

    The solution to your problem of too large an Excel file will depend on many factors.

    You may be able to get more data into an Excel 2007 .xlsx file format, because this format is really a zip file containing a number of xml files. One of these files is a strings file which enables additional space saving by storing duplicate strings in your workbook only once (your wookbook just contains multiple subscripts). Rename an Excel 2007 .xlsx workbook as .zip and you will see what I mean. Excel XP and Excel 2003 users can view and update Excel 2007 workbooks if they download and install the free Office 2007 compatibility pack from Microsoft.

    The other main issue to consider is why do you want to post large amounts of data on a web site? If your site is an intranet you might be able to generate large workbooks overnight in scheduled tasks rather than giving users fully up-to-date and online data. You may even be able to publish these workbooks on network shares rather than using a web server. Does all your data have to be in the workbook? Could you have summarised data in the workbook and provide detail files as appendices in other workbooks? You may even create an Access database for your users to download.

    These are all issues I have encountered at work within the last year or two. Let me know if you want more information on these subjects...

  • #5
    New to the CF scene
    Join Date
    Jul 2011
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Thanks

    Quote Originally Posted by SouthwaterDave View Post
    If you save a multi-worksheet Excel workbook as a web page (.htm) then you will end up with more than one file. If you were to to do that in ASP then you would have to create the supporting files and reference them in the main page.

    If your target audience are using Excel 2003 or 2007 then you have some more options.

    A fairly simple one is to output your multi-worksheet workbook as XML. Microsoft have documented this somewhere (probably on MSDN) but I often find it easier to create a mockup of the results file in Excel and save it as XML to see what is required.

    Here is a fairly simple example:
    Code:
    the code is here
    Thank you very much for this. I have been searching the web for 8 hours and final found this post. It solved all my nightmares thanks once again. Keep up the good help

  • #6
    New to the CF scene
    Join Date
    Jul 2012
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by SouthwaterDave View Post
    If you save a multi-worksheet Excel workbook as a web page (.htm) then you will end up with more than one file. If you were to to do that in ASP then you would have to create the supporting files and reference them in the main page.

    If your target audience are using Excel 2003 or 2007 then you have some more options.

    A fairly simple one is to output your multi-worksheet workbook as XML. Microsoft have documented this somewhere (probably on MSDN) but I often find it easier to create a mockup of the results file in Excel and save it as XML to see what is required.

    Here is a fairly simple example:
    Code:
    <%
    Response.ContentType = "application/vnd.ms-excel"
    Response.AddHeader "Content-Disposition", "attachment;filename=file01.xls"
    %>
    <?xml version="1.0"?>
    <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
    xmlns:o="urn:schemas-microsoft-com:office:office"
    xmlns:x="urn:schemas-microsoft-com:office:excel"
    xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
    xmlns:html="http://www.w3.org/TR/REC-html40">
    <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
    <DownloadComponents/>
    <LocationOfComponents HRef="file:///\\"/>
    </OfficeDocumentSettings>
    <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
    <WindowHeight>12525</WindowHeight>
    <WindowWidth>15195</WindowWidth>
    <WindowTopX>480</WindowTopX>
    <WindowTopY>120</WindowTopY>
    <ActiveSheet>2</ActiveSheet>
    <ProtectStructure>False</ProtectStructure>
    <ProtectWindows>False</ProtectWindows>
    </ExcelWorkbook>
    <Styles>
    <Style ss:ID="Default" ss:Name="Normal">
    <Alignment ss:Vertical="Bottom"/>
    <Borders/>
    <Font/>
    <Interior/>
    <NumberFormat/>
    <Protection/>
    </Style>
    </Styles>
    <Worksheet ss:Name="Sheet1">
    <Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1"
    x:FullRows="1">
    <Row>
    <Cell><Data ss:Type="Number">1</Data></Cell>
    </Row>
    </Table>
    <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
    <ProtectObjects>False</ProtectObjects>
    <ProtectScenarios>False</ProtectScenarios>
    </WorksheetOptions>
    </Worksheet>
    <Worksheet ss:Name="Sheet2">
    <Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1"
    x:FullRows="1">
    <Row>
    <Cell><Data ss:Type="Number">2</Data></Cell>
    </Row>
    </Table>
    <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
    <ProtectObjects>False</ProtectObjects>
    <ProtectScenarios>False</ProtectScenarios>
    </WorksheetOptions>
    </Worksheet>
    <Worksheet ss:Name="Sheet3">
    <Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1"
    x:FullRows="1">
    <Row>
    <Cell><Data ss:Type="Number">3</Data></Cell>
    </Row>
    </Table>
    <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
    <Selected/>
    <Panes>
    <Pane>
    <Number>3</Number>
    <ActiveRow>1</ActiveRow>
    </Pane>
    </Panes>
    <ProtectObjects>False</ProtectObjects>
    <ProtectScenarios>False</ProtectScenarios>
    </WorksheetOptions>
    </Worksheet>
    </Workbook>





    Hi All,
    I am new to ASP(Learninf now).
    can is use the above entire code in an XML file?
    When i saved the above code as a .xml file, its giving error with the symbol "<%" at Response.write line.

    How can i use this XML code.

  • #7
    New to the CF scene
    Join Date
    Aug 2012
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by naves8 View Post
    Hi All,
    I am new to ASP(Learninf now).
    can is use the above entire code in an XML file?
    When i saved the above code as a .xml file, its giving error with the symbol "<%" at Response.write line.

    How can i use this XML code.

    Hello!!

    The above code is for an ".asp" file, not ".xml".

  • #8
    Regular Coder
    Join Date
    Jul 2002
    Location
    UK
    Posts
    302
    Thanks
    16
    Thanked 0 Times in 0 Posts
    Thanks for post.

    What way do you do find it best to insert variable names into the XML. I had tried a few such <%="&WhatYouWant&"%>

    Mike
    Kind regards,
    Mike Hughes

  • #9
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,188
    Thanks
    80
    Thanked 4,560 Times in 4,524 Posts
    Quote Originally Posted by hughesmi View Post
    What way do you do find it best to insert variable names into the XML. I had tried a few such <%="&WhatYouWant&"%>
    That question doesn't seem to have anything to do with this thread.

    Perhaps you should show an example of what you are trying to do?
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #10
    Regular Coder
    Join Date
    Jul 2002
    Location
    UK
    Posts
    302
    Thanks
    16
    Thanked 0 Times in 0 Posts
    dont worry about this. just had a daft blonde momenet
    Kind regards,
    Mike Hughes

  • #11
    New to the CF scene
    Join Date
    Jun 2013
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by naves8 View Post
    Hi All,
    I am new to ASP(Learninf now).
    can is use the above entire code in an XML file?
    When i saved the above code as a .xml file, its giving error with the symbol "<%" at Response.write line.

    How can i use this XML code.
    This code is working brilliante the way I want but I've not be able to add extra rows and extra columns. It always give an error when I try to open the file.
    Could you tell me how do i do that?

    Thanks

  • #12
    Regular Coder
    Join Date
    Jul 2002
    Location
    UK
    Posts
    302
    Thanks
    16
    Thanked 0 Times in 0 Posts
    JoaoBeca, can you post your code please

    Mike
    Kind regards,
    Mike Hughes


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •