asked on

ASP Export to Excel with multiple pages

Hello and thank you for reading this question.

I am exporting data from a database to an Excel spreadsheet via ADO in ASP using the following method:

<%response.ContentType="application/vnd.ms-excel"%>

I simply iterate through my recordset placing the results in a table, and all works fine.

What I would like to be able to do, however, is to create a new sheet (or tab if you prefer) for each record, but I am not sure that it is possible to do so with ASP classic (although there is a solution with XML and ADO.net).

Does anybody have an idea how to do this in ASP classic?

I would appreciate any help,

Fritz the Blank

sybe

It is possible for higher Excel versions (those that support XML)
I don't have the exact code at hand now, but the way to go is through XML. If you save a multiple page Excel file to XML (hit the Save As), and then look through the generated XML with a text editor, you see what i mean.

jitganguly

Patrick,
I remember I helped someone with this. Youc ant do it with contrent type, but generate excel on server side

See here

https://www.experts-exchange.com/questions/20727830/ASP-to-EXCEL-multiple-worksheets-and-worksheet-names.html?query=multiple+excel+sheets&searchType=topic

sybe

Copying the XML from the question mentioned above:

<xml>
<x:ExcelWorkbook>
<x:ExcelWorksheets>
<x:ExcelWorksheet>
<x:Name>Sample Workbook</x:Name>
<x:WorksheetOptions>
<x:Print>
<x:ValidPrinterInfo/>
</x:Print>
</x:WorksheetOptions>
</x:ExcelWorksheet>

<x:ExcelWorksheet>
<x:Name>Sample Workbook 2</x:Name>
<x:WorksheetOptions>
<x:Print>
<x:ValidPrinterInfo/>
</x:Print>
</x:WorksheetOptions>
</x:ExcelWorksheet>
</x:ExcelWorksheets>
</x:ExcelWorkbook>
</xml>

then creating multiple pages is a matter of adding a <x:ExcelWorksheet></x:ExcelWorksheet> node for each extra page.

fritz_the_blank

ASKER

I could use a little hand holding; how would I modify the following?

<%Response.Buffer = true%>
<%
dim strDataPath, strConnectString
dim objConnection, objRS

'set connection strings for entire application
strDataPath = server.MapPath("SomeDatabase.mdb")
strConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;"_
                  + " Data Source= " & strDataPath & ";"_
                  + " Mode=Share Deny None;User Id=admin;PASSWORD=;"

if not IsObject("ojbConnection") then
      set objConnection=Server.CreateObject("ADODB.Connection")
      objConnection.ConnectionTimeout = 15
      objConnection.CommandTimeout = 10
      objConnection.Mode = 3 'adModeReadWrite
      if objConnection.state = 0 then
            objConnection.Open strConnectString
      end if
end if

set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open "SELECT fld1, fld2,fld3 FROM tblSomeTable", objConnection,3,3

%>
<html>
<body>
<%Response.Clear%>
<%response.ContentType="application/vnd.ms-excel"%>

<%
Response.Write("<Table Border=1 cellpadding=2 cellspacing=2>")
Response.Write("<TR>")
do while not objRS.EOF
      Response.Write("<TR>")
            Response.Write("<TD>" & objRS("fld1")& "</TD>")
            Response.Write("<TD>" & objRS("fld3")& "</TD>")
            Response.Write("<TD>" & objRS("fld1")& "</TD>")
      Response.Write("</TR>")
      objRS.MoveNext
loop
%>
</Table>
</body>
</html>
<%
if objRS.State <> 0 then
      objRS.Close
end if
set objRS = Nothing
if objConnection.state <> 0 then
      objConnection.Close
end if
set objConnection = Nothing
Response.End
%>

jitganguly

http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q271/5/72.ASP&NoWebContent=1

This link should help you from my earlier response.

fritz_the_blank

ASKER

Thanks Jit, but I am not sure that I understand how to specify a new sheet for each row. It looks like it might have something to do with @page but I am not sure how to put this all together.

Fritz the Blank

fritz_the_blank

ASKER

Okay, if I do this, I can get two worksheets, but I still don't know how to place the content of each table on a separate worksheet:

<%@ Language=VBScript %>
<%Response.Buffer=true%>
<%
Response.ContentType = "application/vnd.ms-excel"
%>

<HTML xmlns:x="urn:schemas-microsoft-com:office:excel">
<HEAD>
<style>

</style>

</HEAD>
<BODY>
<TABLE>
      <TR>
            <TD>1</TD>
            <TD>2</TD>
            <TD>3</TD>
      </TR>
      <TR>
            <TD>4</TD>
            <TD>5</TD>
            <TD>6</TD>
      </TR>
      <TR>
            <TD>7</TD>
            <TD>8</TD>
            <TD>9</TD>
      </TR>
</TABLE>
<TABLE>
      <TR>
            <TD>10</TD>
            <TD>11</TD>
            <TD>12</TD>
      </TR>
      <TR>
            <TD>13</TD>
            <TD>14</TD>
            <TD>15</TD>
      </TR>
      <TR>
            <TD>16</TD>
            <TD>17</TD>
            <TD>18</TD>
      </TR>
</TABLE>

</BODY>
</HTML>

dan_neal

Why not just use vbscript?

fritz_the_blank

ASKER

Dan--

This will be part of an asp-based web application where the user will be able to submit a parameter, and the data will be generated from the database. So, using client-side VBScript won't work here. Moreover, I want to avoid using Office Automation as MS recommends against it (and having played around with it, I agree).

Apparently, script similar to that above will work. I just need the little bit of syntax that directs each table to the appropriate works sheet, and all will be well.

Fritz the Blank

ASKER CERTIFIED SOLUTION

sybe

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

fritz_the_blank

ASKER

Found the answer here at the same time!

https://www.experts-exchange.com/questions/20919841/Pseudo-XML-HTML-Excel-Export.html

fritz_the_blank

ASKER

Upping points before accepting answer