Solved

ASP Export into Excel with Multiple Worksheets

Posted on 2006-11-13
3
11,519 Views
Last Modified: 2010-05-18
Please accept my apologies in advance for this additional posting and also for any help you can offer.  I've searched around, found several examples of solutions, but I can't get any of them to work completely.

To be clear, I'm trying to export data through an ASP page, ideally .NET, to render in Excel 2000.  My current station is a Windows 2000 station.  I have an ASP Classic version that works, but it won't allow me to create multiple sheets.

This article will be a good starting point as far as the research I've done: http://www.experts-exchange.com/Web/Web_Languages/ASP/Q_20919538.html

I tried the version with <x:ExcelWorksheet> in combination with the table tags provided by Fritz the Blank, and it sets up/names multiple sheets for me, but the data appears on a single sheet only.  (I've at least been able to duplicate Fritz's initial state and prove I'm able to at least create  multiple sheets.)

I followed the complete XML example provided by sybe, but it wouldn't create multiple sheets, and the data all showed up on one sheet, all data in one cell per row.

If the sybe example is supposed to work as Fritz indicates, and I have every confidence it does, then the issue is somewhere else.  Station configuration, maybe?  Can anyone offer a suggestion as to how I should proceed?

Thank you again for taking the time to read this and also for any assistance you can provide.

0
Comment
Question by:jeristotle
3 Comments
 
LVL 6

Accepted Solution

by:
gete earned 500 total points
ID: 17937657
I thought this would only need a few tweak here and there but boy I was wrong :) But, before the answer:
I tried sybe's XML example as is, it worked fine. Btw I'm using Excel 2007 but it's supposed to work in earlier version.

As for the hybrid HTML/XML (fritz's initial idea), the following are my references:

Can I Get That As a Spreadsheet?
http://www.15seconds.com/issue/031007.htm

Microsoft Office HTML and XML Reference
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnoffxml/html/ofxml2k.asp
(download the .exe file and just extract the .chm if you have WinZip or other compression tool)

From the 15seconds article, you can have a good tutorial about the setup. And from Microsoft documentation, it's confirmed that in order to have multiple worksheets, you need to separate the data/table in different files. The good thing is, Excel support MIME HTML (.mht/.mhtml) file (at least in Office 2007, haven't tried it in earlier ver). With MHTML, you only need to produce 1 output file.

Here's my initial setup:

*** books.xls ***
<html xmlns:x="urn:schemas-microsoft-com:office:excel">
<head>
<meta name="Excel Workbook Frameset">
<xml>
      <x:ExcelWorkbook>
            <x:ExcelWorksheets>
                  <x:ExcelWorksheet>
                        <x:Name>First Sheet</x:Name>
                        <x:WorksheetSource HRef="sheet1.htm"/>
                  </x:ExcelWorksheet>
                  <x:ExcelWorksheet>
                        <x:Name>Second Sheet</x:Name>
                        <x:WorksheetSource HRef="sheet2.htm"/>
                  </x:ExcelWorksheet>
            </x:ExcelWorksheets>
      </x:ExcelWorkbook>
</xml>
</head>
</html>

*** sheet1.htm ***
<table>
      <tr>
            <td>1asdf</td>
            <td>2</td>
            <td>3</td>
      </tr>
      <tr>
            <td>4</td>
            <td>5</td>
            <td>6</td>
      </tr>
</table>

*** sheet2.htm ***
<table>
      <tr>
            <td>a</td>
            <td>b</td>
            <td>c</td>
      </tr>
      <tr>
            <td>d</td>
            <td>e</td>
            <td>f</td>
      </tr>
</table>

Regardless of what 15seconds article stated, I find the above setup works fine without needing to create filelist.xml. So, basically the steps are:
1) Create the worksheets container/frameset with references to individual worksheet files. This is books.xls (which is just an HTML/XML file)
2) Create individual worksheet file as a normal HTML file. These are sheet1.htm and sheet2.htm. Note: the above examples is a simple <table>. You can use <body bgcolor=...> etc. as per normal HTML.

Now, the next step is to make a multipart MIME HTML from those 3 files:

*** booksmultipart.xls ***
MIME-Version: 1.0
X-Document-Type: Workbook
Content-Type: multipart/related; boundary=3D"----=_NextPart_ExcelWorkbook"

------=_NextPart_ExcelWorkbook
Content-Location: books.xls
Content-Transfer-Encoding: quoted-printable
Content-Type: text/html; charset=3D"us-ascii"

<html xmlns:x=3D"urn:schemas-microsoft-com:office:excel">
<head>
<meta name=3D"Excel Workbook Frameset">
<xml>
      <x:ExcelWorkbook>
            <x:ExcelWorksheets>
                  <x:ExcelWorksheet>
                        <x:Name>First Sheet</x:Name>
                        <x:WorksheetSource HRef=3D"sheet1.htm"/>
                  </x:ExcelWorksheet>
                  <x:ExcelWorksheet>
                        <x:Name>Second Sheet</x:Name>
                        <x:WorksheetSource HRef=3D"sheet2.htm"/>
                  </x:ExcelWorksheet>
            </x:ExcelWorksheets>
      </x:ExcelWorkbook>
</xml>
</head>
</html>

------=_NextPart_ExcelWorkbook
Content-Location: sheet1.htm
Content-Transfer-Encoding: quoted-printable
Content-Type: text/html; charset=3D"us-ascii"

<table>
      <tr>
            <td>1</td>
            <td>2</td>
            <td>3</td>
      </tr>
      <tr>
            <td>4</td>
            <td>5</td>
            <td>6</td>
      </tr>
</table>

------=_NextPart_ExcelWorkbook
Content-Location: sheet2.htm
Content-Transfer-Encoding: quoted-printable
Content-Type: text/html; charset=3D"us-ascii"

<table>
      <tr>
            <td>a</td>
            <td>b</td>
            <td>c</td>
      </tr>
      <tr>
            <td>d</td>
            <td>e</td>
            <td>f</td>
      </tr>
</table>
------=_NextPart_ExcelWorkbook--

Basically booksmultipart.xls is just a text file combining the 3 files. Just take note you need to make the contents quoted-printable, e.g. you can do Replace(contents, "=", "=3D") for that.
0
 

Author Comment

by:jeristotle
ID: 18170137
I apologize for leaving this open.  I tried the sample, seemed to work OK for what I needed.  Thank you for your help!
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Send to Print Preview database record with one click 3 61
Select distinct 25 115
Auto Submit on dropdown box 3 79
html Uncheck Checkbox 2 25
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question