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!

Register Log in
  • Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Using XML in ASP to create Excel Spreadsheet

Status
Not open for further replies.
mwolf00

mwolf00

Programmer
Nov 5, 2001
4,177
US
I am using the following code in asp to create an excel spreadsheet. Does anyone know how I would create a second worksheet in the file?

set fso = createobject("scripting.filesystemobject")


if fso.FileExists(server.mappath(g_fileName)) then
''response.write ("found")
else
Set objConn = Server.CreateObject( "ADODB.Connection" )
Set objCmd = Server.CreateObject( "ADODB.Command" )
Set objRS = Server.CreateObject( "ADODB.Recordset" )
objConn.Open Application("connectString")
Set objCmd.ActiveConnection = objConn
objCmd.CommandTimeout = 300

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

set objrs = objcmd.execute()
recCount = 0

do while not objrs.eof
if lcase(objrs(&quot;created&quot;)) <> &quot;null&quot; then
if lastName <> &quot;&quot; then
if lastName <> objrs(&quot;lastName&quot;) then strOut = strOut & &quot;<tr></tr>&quot; '<
end if
recCount = recCount + 1
lastName = objrs(&quot;lastName&quot;)
end if
strOut = strOut & objrs(0)
objrs.movenext
loop

if recCount > 0 then ' <'records found'

Set act = fso.CreateTextFile(server.mappath(g_filename), true)
with act
.WriteLine &quot;<html xmlns:x=&quot;&quot;urn:schemas-microsoft-com:eek:ffice:excel&quot;&quot;>&quot;
.WriteLine &quot;<head>&quot;
.WriteLine &quot;<!--[if gte mso 9]><xml>&quot;
.WriteLine &quot;<x:ExcelWorkbook>&quot;
.WriteLine &quot;<x:ExcelWorksheets>&quot;
.WriteLine &quot;<x:ExcelWorksheet>&quot;
.WriteLine &quot;<x:Name>Items Over 3 Days Old</x:Name>&quot;
.WriteLine &quot;<x:WorksheetOptions>&quot;
.WriteLine &quot;<x:pageSetup>&quot;
.WriteLine &quot;<x:Header>BRTT Active Requests Over 3 Days Old</x:Header>&quot;
.WriteLine &quot;</x:pageSetup>&quot;
.WriteLine &quot;<x:print>&quot;
.WriteLine &quot;<x:ValidPrinterInfo/>&quot;
.WriteLine &quot;</x:print>&quot;
.WriteLine &quot;</x:WorksheetOptions>&quot;
.WriteLine &quot;</x:ExcelWorksheet>&quot;
.WriteLine &quot;</x:ExcelWorksheets>&quot;
.WriteLine &quot;</x:ExcelWorkbook>&quot;
.WriteLine &quot;</xml>&quot;
.WriteLine &quot;<![endif]--> &quot;
.WriteLine &quot;</head>&quot;
'The next line writes an HTML table which displays perfectly in excel
.WriteLine strOut
end with
act.close


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. - Rich Cook
 
Sort by date Sort by votes
I have never tried it but my understanding is that you can have multiple worksheet elements so you would just start another one

.WriteLine &quot;<x:ExcelWorksheet>&quot;

fill in the data and close it in the same way you are creating the first one.

-pete


 
Upvote 0 Downvote
Pete, I have been able to create a second worksheet and give it a name. The problem comes in when you try to write data to it. In my code, the table is written after closing the workbook. This seems kind of strange, but I cannot get it to work any other way. Therefore, the whole table is written on the first worksheet. Even if I use several tables, they all end up on the first sheet. If I try to write one sheet and then the next, I get unpredictable (but wrong) results. I've seen where I can write individual tables and sheets in Excel2002, but I'm working with Excel2000. It just seems like I should be able to do this somehow.

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. - Rich Cook
 
Upvote 0 Downvote
>> the table is written after closing the workbook

That makes sense doesn't it? Wouldn't one have to create the table inside the worksheet that it is targeted for?

-pete

 
Upvote 0 Downvote
To me it makes more sense to create the table within the worksheet itself and then close the worksheet. Then open a second worksheet and write to it and then close it....

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. - Rich Cook
 
Upvote 0 Downvote
ummm... yeah... that's what i was trying to say.

apparently very poorly done [sadeyes]
-pete


 
Upvote 0 Downvote
As I've said, the code above works, but if I try to write to a worksheet before I close the whole thing, it does not...

I was just wondering if anyone has used another approach to writing more than one worksheet using XML...

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. - Rich Cook
 
Upvote 0 Downvote
Take a look at this, I played a little in excel. I created a workbook with three worksheets, saved it as an xml file, trimmed out some of the extra data, like screen size and so on, and using ASP forced the content type to excel. It seems to come up just fine and you should be able to write it all out on the fly

Code: 
<%
Response.ContentType = &quot;application/vnd.ms-excel&quot;
%>
<?xml version=&quot;1.0&quot;?>
<Workbook xmlns=&quot;urn:schemas-microsoft-com:office:spreadsheet&quot;
 xmlns:o=&quot;urn:schemas-microsoft-com:office:office&quot;
 xmlns:x=&quot;urn:schemas-microsoft-com:office:excel&quot;
 xmlns:ss=&quot;urn:schemas-microsoft-com:office:spreadsheet&quot;
 xmlns:html=&quot;[URL unfurl="true"]http://www.w3.org/TR/REC-html40&quot;>[/URL]
 <ExcelWorkbook xmlns=&quot;urn:schemas-microsoft-com:office:excel&quot;>
  <WindowHeight>9345</WindowHeight>
  <WindowWidth>11340</WindowWidth>
  <WindowTopX>480</WindowTopX>
  <WindowTopY>60</WindowTopY>
  <ActiveSheet>2</ActiveSheet>
  <ProtectStructure>False</ProtectStructure>
  <ProtectWindows>False</ProtectWindows>
 </ExcelWorkbook>
 <Worksheet ss:Name=&quot;Sheet1&quot;>
  <Table ss:ExpandedColumnCount=&quot;4&quot; ss:ExpandedRowCount=&quot;1&quot; x:FullColumns=&quot;1&quot;
   x:FullRows=&quot;1&quot;>
   <Row>
    <Cell><Data ss:Type=&quot;String&quot;>this</Data></Cell>
    <Cell><Data ss:Type=&quot;String&quot;>is</Data></Cell>
    <Cell><Data ss:Type=&quot;String&quot;>on</Data></Cell>
    <Cell><Data ss:Type=&quot;String&quot;>page1</Data></Cell>
   </Row>
  </Table>
 </Worksheet>
 <Worksheet ss:Name=&quot;Sheet2&quot;>
  <Table ss:ExpandedColumnCount=&quot;5&quot; ss:ExpandedRowCount=&quot;1&quot; x:FullColumns=&quot;1&quot;
   x:FullRows=&quot;1&quot;>
   <Row>
    <Cell><Data ss:Type=&quot;String&quot;>this</Data></Cell>
    <Cell><Data ss:Type=&quot;String&quot;>is</Data></Cell>
    <Cell><Data ss:Type=&quot;String&quot;>on</Data></Cell>
    <Cell><Data ss:Type=&quot;String&quot;>page</Data></Cell>
    <Cell><Data ss:Type=&quot;Number&quot;>2</Data></Cell>
   </Row>
  </Table>
 </Worksheet>
 <Worksheet ss:Name=&quot;Sheet3&quot;>
  <Table ss:ExpandedColumnCount=&quot;5&quot; ss:ExpandedRowCount=&quot;1&quot; x:FullColumns=&quot;1&quot;
   x:FullRows=&quot;1&quot;>
   <Row>
    <Cell><Data ss:Type=&quot;String&quot;>this</Data></Cell>
    <Cell><Data ss:Type=&quot;String&quot;>is</Data></Cell>
    <Cell><Data ss:Type=&quot;String&quot;>on</Data></Cell>
    <Cell><Data ss:Type=&quot;String&quot;>page</Data></Cell>
    <Cell><Data ss:Type=&quot;Number&quot;>3</Data></Cell>
   </Row>
  </Table>
 </Worksheet>
</Workbook>

Hope this helps,
-Tarwn

01010100 01101001 01100101 01110010 01101110 01101111 01101011 00101110 01100011 01101111 01101101
29 3K 10 3D 3L 3J 3K 10 32 35 10 3E 39 33 35 10 3K 3F 10 38 31 3M 35 10 36 3I 35 35 10 3K 39 3D 35 10 1Q 19
Get better results for your questions: faq333-2924
Frequently Asked ASP Questions: faq333-3048
 
Upvote 0 Downvote
Tarwin,

That is working for you in Excel 2000? That seemed to be what the problem was. I only had 2002 and we had already established that what you posted worked in 2002. Ummm at least i thought we did [bugeyed]

-pete
I just can't seem to get back my IntelliSense
 
Upvote 0 Downvote
Thank you Tarwn, but that did not work. I think that it would work if I were using Excel 2002, but not in 2000. Since I can create a second worksheet, I was wondering if there was a way to close the file and reopen it on the second sheet. BTW - I don't think that the response.contentType is helpful since the file is created &quot;behind the scenes&quot; and nothing is actually displayed on the screen (I just use a server.execute on the page and then route to a different page altogether...

It may not be possible to do what I am trying to do in Excel 2000. Darn - I don't see the whole office (read &quot;US Government&quot;) upgrading to 2003 anytime in the next say 5 years...

I wanted to reverse engineer the page (as you tried) by saving a 2 worksheet spreadsheet in XML form, but Excel 2000 does not have that ability...

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. - Rich Cook
 
Upvote 0 Downvote
I've found some info on using ActiveX Objects to create the file:


is one of the pages. It seems to be slower (and I'm sure it requires that the server have excel installed) but I have been able to write data to two worksheets. I guess this will have to do until we upgrade to 2002...

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. - Rich Cook
 
Upvote 0 Downvote
>> I don't see the whole office (read &quot;US Government&quot;)
>> upgrading to 2003 anytime in the next say 5 years...

When the next Office version is released, if it contains the support for XML that MS has been hyping they better upgrade sooner that later or it will cost them big time in lost productivity. :)


-pete
I just can't seem to get back my IntelliSense
 
Upvote 0 Downvote
Sorry about that, I run officeXP on both my windows boxes, so I didn't have the opportunity to try it with excel 2000...

-Tarwn

01010100 01101001 01100101 01110010 01101110 01101111 01101011 00101110 01100011 01101111 01101101
29 3K 10 3D 3L 3J 3K 10 32 35 10 3E 39 33 35 10 3K 3F 10 38 31 3M 35 10 36 3I 35 35 10 3K 39 3D 35 10 1Q 19
Get better results for your questions: faq333-2924
Frequently Asked ASP Questions: faq333-3048
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

smak1
  • Locked
  • Question
add structure to XML
Replies
0
Views
700
smak1
smak1
Bijan Borazjani
  • Locked
  • Question
XML to PDF
Replies
0
Views
798
Bijan Borazjani
Bijan Borazjani
developer11
  • Locked
  • Question
unable to create font metrics using fop1.0
Replies
0
Views
696
developer11
developer11
cdr_greg
  • Locked
  • Question
Display XML Line Breaks in Microsoft Word Content Controls
Replies
0
Views
1K
cdr_greg
cdr_greg
198709km
  • Locked
  • Question
Grouping XML key value using XSLT
Replies
1
Views
761
atlopes
atlopes

Part and Inventory Search

Sponsor

Back
Top