When rendering page as excel, can u name the tabs? 2

VBGuy2112 · Apr 23, 2003

When I write an html page as an excel speadsheet using the following:

Dim r As HttpResponse = GetResponse()
r.Buffer = True
r.ContentType = "application/vnd.ms-excel"
r.Write(obuilder.ToString)
r.Flush()

I get a nice excel speadsheet from the HTML table structures but the tab (or worksheet name) has a strange label that looks like part of the query string. Is there a way I can name this tab myself?

Also, is there a way to write a page as excel and have multiple worksheets/tabs? I promise I will give stars if someone can answer these.

mwolf00 · Apr 23, 2003

If you're using asp, you can create the excel file using xml like this....

<%
response.expires = 0

monNum = month(date())
dayNum = day(date())

if monNum < 10 then monNum = "0" & monNum
if dayNum < 10 then dayNum = "0" & dayNum

g_filename="report/ThreeDayReport"& "_" & year(date()) & monNum & dayNum &".xls"

set fso = createobject("scripting.filesystemobject&quot

if fso.FileExists(server.mappath(g_fileName)) then
''response.write ("found&quot

else
Set objConn = Server.CreateObject( "ADODB.Connection" )
Set objCmd = Server.CreateObject( "ADODB.Command" )
objConn.Open Application("connectString&quot

Set objCmd.ActiveConnection = objConn
objCmd.CommandTimeout = 300

with objCmd
.commandText = "br_sp_threeDayReport"
.CommandType = &H0004
End With

set objrs = objcmd.execute()
recCount = 0
'this routine is simply writing a HTML table'
do while not objrs.eof
if lcase(objrs("created&quot

) <> "null" then
if lastName <> "" then
if lastName <> objrs("lastName&quot

then strOut = strOut & "<tr></tr>" '<
end if
recCount = recCount + 1
lastName = objrs("lastName&quot

end if
strOut = strOut & "<td style='width: 100px'>" & objrs(0) & "</td>"
objrs.movenext
loop

if recCount > 0 then ' <'records found'

Set act = fso.CreateTextFile(server.mappath(g_filename), true)
with act
.WriteLine "<html xmlns:x=""urn:schemas-microsoft-com

ffice:excel"">"
.WriteLine "<head>"
.WriteLine "<!--[if gte mso 9]><xml>"
.WriteLine "<x:ExcelWorkbook>"
.WriteLine "<x:ExcelWorksheets>"
.WriteLine "<x:ExcelWorksheet>"
'the following line names the tab (you can make multiple tabs/sheets)
.WriteLine "<x:Name>Items Over 3 Days Old</x:Name>"
.WriteLine "<x:WorksheetOptions>"
.WriteLine "<x

ageSetup>"
'the following line writes the header for the worksheet
.WriteLine "<x:Header>BRTT Active Requests Over 3 Days Old</x:Header>"
.WriteLine "</x

ageSetup>"
.WriteLine "<x

rint>"
.WriteLine "<x:ValidPrinterInfo/>"
.WriteLine "</x

rint>"
.WriteLine "</x:WorksheetOptions>"
.WriteLine "</x:ExcelWorksheet>"
.WriteLine "</x:ExcelWorksheets>"
.WriteLine "</x:ExcelWorkbook>"
.WriteLine "</xml>"
.WriteLine "<![endif]--> "
.WriteLine "</head>"
'the following line writes the table to excel
.WriteLine strOut
end with
act.close
end if 'recCount > 0'
end if

Get the Best Answers! faq333-2924
Is this an asp FAQ? faq333-3048
Tek-Tips Best Practices: FAQ183-3179

VBGuy2112 · Apr 24, 2003

Thank you for your response. I'll try this out.

VBGuy2112 · Apr 24, 2003

Doesn't look like I can give stars. That must be left to the webmasters. I'm sorry. I thought I would be able to do that.

This is the best I can do:

***

VBGuy2112 · Apr 24, 2003

Guess I missed it before. You're now a star.

StuckInTheMiddle · Apr 25, 2003

mwolf00

I'm very intrigued by your solution, it has been most helpful so far. I am however having a problem trying to insert data into my 2nd worksheet.

I have attempted to follow your example, repeated the header 2 times to get 2 tabs, however repeating the
insert of the table 2 times (after the closing head tag) I end up with 2 tables on my first worksheet.

You wouldnt happen to know how I can get the 2nd table to appear on my 2nd worksheet?. Is another XML tag I'm not familiar with required here?

Thanks in advance,

S.

(The more ASP.NET I do, the more Javascript I find myself using)

OldJacques · Jul 11, 2003

At the

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnexcl2k2/html/odc_xmlss.asp

Microsoft Office Developers Site there is a reference list and

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnexcl2k2/html/odc_excelsqlxml.asp

Example page</a> that use multiple sheets. It seems, unfortunately, to be compatible only with Office/Excel 2002/XP, and not Excel2000.
The only alternative is the:
<x:ExcelWorksheets>
<x:ExcelWorksheet>
<x:WorksheetSource HRef="./test_file/sheet001.htm"/>
</x:ExcelWorksheet>
<x:ExcelWorksheet>
<x:WorksheetSource HRef="./test_file/sheet002.htm"/>
</x:ExcelWorksheet>
</x:ExcelWorksheets>
Structure, which calls external files which are separate.

mwolf00 · Jul 11, 2003

OldJacques -

You are right about Excel2000 vs 2002... 2002 is fully capable for XML while 2000 is VERY limited (damn Microsoft for making me need to upgrade).

You will find that your solution above does write both worksheets. HOWEVER, I've only been able to write data to one of them (unless, of course, I have Excel 2002)....

I haven't tried
<x:WorksheetSource HRef="./test_file/sheet002.htm"/>

have you tested on Excel2000????

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

When rendering page as excel, can u name the tabs? 2

VBGuy2112

Programmer

mwolf00

Programmer

VBGuy2112

Programmer

VBGuy2112

Programmer

VBGuy2112

Programmer

StuckInTheMiddle

Programmer

OldJacques

IS-IT--Management

mwolf00

Programmer

Similar threads

Part and Inventory Search

Sponsor