Solved

Trying to get XML to Export into Excel Doc from ASP

Posted on 2004-04-30
69
1,026 Views
Last Modified: 2008-01-16
References:

Here is my code below.  The following is happening:

1) I get a save prompt but it states in the prompt:

FileName:  my current asp page name
From: my site.com

2) When I try to SAve the file, I get the error "Internet Explorer cannot download asp page name from my site.com.  Internet Explorer was not able to open this Internet site"

3) Why is it trying to download an asp page rather than export to Excel and prompt me to save an Excel file?  Or will it if I get this to stop erroring out?

My code

<%@Language="VBScript"%>
<%
     'Using the Northwind as an example:
     'works for Excel 2000 and Excel 2002

Response.AddHeader "Content-Disposition", "attachment;filename=invoice.xls"
Response.ContentType = "application/vnd.ms-excel"

     Function GetIsoDate()
          Dim datNow
          datNow = Now
          GetISODate = CStr(Year(datNow)) & "-" & PadItem(Month(datNow)) & "-" & PadItem(Day(datNow)) & "T" & _
           PadItem(Hour(datNow)) & ":" & PadItem(Minute(datNow)) & ":" & PadItem(Second(datNow)) & "Z"
     End Function

     Function PadItem(ByVal value)
          value = CStr(value)
          If Len(value) = 1 Then
               value = "0" & value
          End If
          PadItem = value
     End Function
%>

<?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">
<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
     <Author>dba123</Author>
     <LastAuthor>dba123</LastAuthor>
     <Created>1/1/2004</Created>
     <Company>Company Name</Company>
     <Version>10.2625</Version>
</DocumentProperties>
<OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
    <DownloadComponents/>
    <LocationOfComponents HRef="file:///\\OfficeXP\CD1\"/>
</OfficeDocumentSettings>
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
        <WindowHeight>6135</WindowHeight>
        <WindowWidth>8445</WindowWidth>
        <WindowTopX>240</WindowTopX>
        <WindowTopY>120</WindowTopY>
        <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>
<%
Dim oCon, Query, strSQL

oCon.ConnectionString "Provider=sqloledb;Data Source=somedatasource;Initial Catalog=cataolognamehere;User Id=sa;Password=passwordhere;"

oCon.Open

Set Query = Server.CreateObject("ADODB.Recordset")

strSQL = "SELECT TOP 15 * FROM mytable"

Set Query = oCon.Execute(strSQL)

Dim count
count = 0

While Not Query.EOF
count = count + 1
%>
<Worksheet ss:Name="<%=Query("Email_Date")%>">
<Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount="4" x:FullColumns="1" x:FullRows="1" ID="Table<%=count%>">
<Column ss:Width="150" />
<Column ss:Width="200" />
<Row>
     <Cell><Data ss:Type="String">CSR Id</Data></Cell>
     <Cell><Data ss:Type="String"><%=Query("CSR_Id")%></Data></Cell>
</Row>
<Row>
     <Cell><Data ss:Type="String">Email</Data></Cell>
     <Cell><Data ss:Type="String"><%=Query("Email")%></Data></Cell>
</Row>
</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<% if count = 1 then %>
<Selected />
<% end if %>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
<%
Query.MoveNext
Wend
oCon.Close
%>
</Workbook>
0
Comment
Question by:dba123
  • 41
  • 23
  • 5
69 Comments
 
LVL 1

Author Comment

by:dba123
Comment Utility
whoops, references: http://www.experts-exchange.com/Web/Web_Languages/XML/Q_20919841.html which I am modeling this attempt by
0
 
LVL 21

Expert Comment

by:ap_sajith
Comment Utility
Its working for me now..

Go to your IE and clear all offline content (Tools | Internet Option | Delet Files | Delete all offline content)

Also, try running this from a new browser window.

Cheers!!
0
 
LVL 1

Author Comment

by:dba123
Comment Utility
I don't know, it is just not working even after that.  Can you post your code?
0
 
LVL 1

Author Comment

by:dba123
Comment Utility
so is your filename showing an asp page also?  how is it working?  are you getting an excel file as output?
0
 
LVL 1

Author Comment

by:dba123
Comment Utility
I give up
0
 
LVL 21

Expert Comment

by:ap_sajith
Comment Utility
Mine clearly shows invoice.xls.. Here is the code.. Also try clearing history and deleting all the offline content. Also try rebooting your server/ pC. I think it is being served from the cache.

<%@Language="VBScript"%>
<%
     Response.AddHeader "Content-Disposition", "attachment;filename=invoice.xls"
       Response.ContentType = "application/vnd.ms-excel"
     Function GetIsoDate()
          Dim datNow
          datNow = Now
          GetISODate = CStr(Year(datNow)) & "-" & PadItem(Month(datNow)) & "-" & PadItem(Day(datNow)) & "T" & _
           PadItem(Hour(datNow)) & ":" & PadItem(Minute(datNow)) & ":" & PadItem(Second(datNow)) & "Z"
     End Function
     
     Function PadItem(ByVal value)
          value = CStr(value)
          If Len(value) = 1 Then
               value = "0" & value
          End If
          PadItem = value
     End Function
%><?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">
<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
     <Author>Fritz_the_Blank</Author>
     <LastAuthor>Fritz_the_Blank</LastAuthor>
     <Created><%=GetIsoDate()%></Created>
     <Company>ABC Inc</Company>
     <Version>10.2625</Version>
</DocumentProperties>
<OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
    <DownloadComponents/>
    <LocationOfComponents HRef="file:///\\OfficeXP\CD1\"/>
</OfficeDocumentSettings>
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
        <WindowHeight>6135</WindowHeight>
        <WindowWidth>8445</WindowWidth>
        <WindowTopX>240</WindowTopX>
        <WindowTopY>120</WindowTopY>
        <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>
<%
Dim oCon, oCmd, oRS, oXml, oXslt, oElement
Set oCon = Server.CreateObject("ADODB.Connection")
Set oCmd = Server.CreateObject("ADODB.Command")
Set oRS = Server.CreateObject("ADODB.Recordset")

oCon.ConnectionString = "Provider=SQLOLEDB;Initial Catalog=NorthWind;Data Source=(local);User Id=sa;Password=pass;"
oCon.Open
Set oCmd.ActiveConnection = oCon
oCmd.CommandText = "SELECT TOP 15 * FROM Orders"
Dim count
count = 0
Set oRS = oCmd.Execute
While Not oRS.EOF
count = count + 1
%>
<Worksheet ss:Name="<%=oRS("OrderId")%>">
<Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount="4" x:FullColumns="1" x:FullRows="1" ID="Table<%=count%>">
<Column ss:Width="150" />
<Column ss:Width="200" />
<Row>
     <Cell><Data ss:Type="String">Order Id</Data></Cell>
     <Cell><Data ss:Type="String"><%=oRS("OrderId")%></Data></Cell>
</Row>
<Row>
     <Cell><Data ss:Type="String">Order Date</Data></Cell>
     <Cell><Data ss:Type="String"><%=oRS("OrderDate")%></Data></Cell>
</Row>
<Row>
     <Cell><Data ss:Type="String">Required Date</Data></Cell>
     <Cell><Data ss:Type="String"><%=oRS("RequiredDate")%></Data></Cell>
</Row>
<Row>
     <Cell><Data ss:Type="String">Freight</Data></Cell>
     <Cell><Data ss:Type="Number"><%=(oRS("Freight"))%></Data></Cell>
</Row>
</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<% if count = 1 then %>
<Selected />
<% end if %>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
<%
oRS.MoveNext
Wend
oCon.Close
%>
</Workbook>

Cheers!!
0
 
LVL 1

Author Comment

by:dba123
Comment Utility
When you say shows an .xls, do you mean shows it in a browswer?  I am not wanting that, I want an actual phsyical Excel doc on my desktop or something, not a web page showing excel.  Is that what this all is doing?  

Thanks much for all the help so far.
0
 
LVL 21

Expert Comment

by:ap_sajith
Comment Utility
Try running the above code as is.. It should throw an error.. that way we can be sure that is is not being served from cache.
Then make the changes to the connection and try executing it from a new window.

Cheers!!
0
 
LVL 1

Author Comment

by:dba123
Comment Utility
The only difference I see is that you are using a local DB and I'm trying to access a DB on our server.  While I am able to edit and do stuff in ASP on my server Databases, maybe that is the problem but I don't know what needs to be tweaked on the server to prevent me from getting that error...if this is indeed the problem.
0
 
LVL 46

Expert Comment

by:fritz_the_blank
Comment Utility
As you know, I received help with that question. When it finally worked for me, the database was Access on a Windows 2000 server. No special components were on my computer.

The page displays as an Excel sheet in the browser, which could be saved locally via the file menu.

FtB
0
 
LVL 1

Author Comment

by:dba123
Comment Utility
tried your code, got an error.  Changed the damn connection...same errror.  I don't have Local registered in my SQL Server Enterprise Manager to try on either since i am using WinXP and when installing SQL Server, you get that messages stating that you cannot install the server portion on my pC, otherwise I'd try locally.
0
 
LVL 1

Author Comment

by:dba123
Comment Utility
Can you answer my question also which is a biggie for me:

When you say shows an .xls, do you mean shows it in a browswer?  I am not wanting that, I want an actual phsyical Excel doc on my desktop or something, not a web page showing excel.  Is that what this all is doing?  
0
 
LVL 21

Expert Comment

by:ap_sajith
Comment Utility
Try adding the code in a new page and try calling it. I dont think that the database being in a different machine is a problem. Also, make sure that there is a northwind database in your server.

I strongly believe it has something to do with caching. maybe the previous xml file that you generated is still in the cache or something. Otherwise the code should work perfectly fine.

Cheers!!
0
 
LVL 46

Expert Comment

by:fritz_the_blank
Comment Utility
When the page is called, a dialogue box appears offering you the choice to view the page in your browser, to save the file locally, or to cancel. There is no real way for this to write the file directly locally or else that would be a big security issue.

FtB
0
 
LVL 1

Author Comment

by:dba123
Comment Utility
nevermind, my connection was messed up.  It starts to go to excel after download then getting this error..looking into it

End tag 'Workbook' does not match the start tag 'Worksheet'
0
 
LVL 21

Expert Comment

by:ap_sajith
Comment Utility
phew... Atleast we have crossed the initial hurdle.

Cheers!!
0
 
LVL 46

Expert Comment

by:fritz_the_blank
Comment Utility
Okay, that should be easy to find--you just have a mismatched tag.

FtB
0
 
LVL 1

Author Comment

by:dba123
Comment Utility
1)  I am now getting the error after I try opening the Excel doc

"Problems came up in the following areas during load

Workbook setting"

I have looked up and down my code.  I don't see any problems in it.

2) What is Set oCmd = Server.CreateObject("ADODB.Command").  I took that out.

3) ap_sajith, are you getting your Excel file to open with data without any errors?

My latest code:

<%@Language="VBScript"%>
<%
Response.AddHeader "Content-Disposition", "attachment;filename=invoice.xls"
Response.ContentType = "application/vnd.ms-excel"
%>

<?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">
<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
     <Author>IT Dept</Author>
     <LastAuthor>IT Dept</LastAuthor>
     <Created>1/1/2004</Created>
     <Company>Company Name</Company>
     <Version>10.2625</Version>
</DocumentProperties>
<OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
    <DownloadComponents/>
    <LocationOfComponents HRef="file:///\\OfficeXP\CD1\"/>
</OfficeDocumentSettings>
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
        <WindowHeight>6135</WindowHeight>
        <WindowWidth>8445</WindowWidth>
        <WindowTopX>240</WindowTopX>
        <WindowTopY>120</WindowTopY>
        <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>

<%
Dim oCon, Query, strSQL, count

Set oCon = Server.CreateObject("ADODB.Connection")
oCon.Open "Provider=sqloledb;Data Source=somesource;Initial Catalog=somecatalog;User Id=sa;Password=somepassword;"

Set Query = Server.CreateObject("ADODB.Recordset")

strSQL = "SELECT TOP 15 * FROM my_table"

count = 0

Set Query = oCon.Execute(strSQL)

Do While Not Query.EOF
count = count + 1
%>


<Worksheet ss:Name="test">
<Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount="4" x:FullColumns="1" x:FullRows="1" ID="Table<%=count%>">
<Column ss:Width="150" />
<Column ss:Width="200" />
<Row>
     <Cell><Data ss:Type="String">Email</Data></Cell>
     <Cell><Data ss:Type="String"><%=Query("Email")%></Data></Cell>
</Row>
</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<% if count = 1 then %>
<Selected />
<% end if %>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
<%

Query.MoveNext
loop
Query.Close

%>
</Workbook>


0
 
LVL 21

Expert Comment

by:ap_sajith
Comment Utility
>>2) What is Set oCmd = Server.CreateObject("ADODB.Command").  I took that out.
You dont really need that.. what you have written is correct.

>>3) ap_sajith, are you getting your Excel file to open with data without any errors?

I got it working perfectly. Was it working fine before you made the modifications?

I am looking at your code for any possible errors.. Shall let you know if i come across any.

Cheers!!
0
 
LVL 1

Author Comment

by:dba123
Comment Utility
No, it hs never worked yet, this is my last error....THEN IT WILL WORK....ahhhh
0
 
LVL 1

Author Comment

by:dba123
Comment Utility
Thanks
0
 
LVL 46

Expert Comment

by:fritz_the_blank
Comment Utility
One problem that I frequently encountered was that if the sheet name was too long, let's say greater than 10 characters, the code threw an error.

FtB
0
 
LVL 1

Author Comment

by:dba123
Comment Utility
thanks, it is definitely not the name...I tried even a two character name for both the spreadsheet and worksheet.
0
 
LVL 21

Expert Comment

by:ap_sajith
Comment Utility
Try this...

<%@Language="VBScript"%>
<%
     Response.AddHeader "Content-Disposition", "attachment;filename=invoice.xls"
      Response.ContentType = "application/vnd.ms-excel"
     Function GetIsoDate()
          Dim datNow
          datNow = Now
          GetISODate = CStr(Year(datNow)) & "-" & PadItem(Month(datNow)) & "-" & PadItem(Day(datNow)) & "T" & _
           PadItem(Hour(datNow)) & ":" & PadItem(Minute(datNow)) & ":" & PadItem(Second(datNow)) & "Z"
     End Function
     
     Function PadItem(ByVal value)
          value = CStr(value)
          If Len(value) = 1 Then
               value = "0" & value
          End If
          PadItem = value
     End Function
%><?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">
<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
     <Author>Fritz_the_Blank</Author>
     <LastAuthor>Fritz_the_Blank</LastAuthor>
     <Created><%=GetIsoDate()%></Created>
     <Company>ABC Inc</Company>
     <Version>10.2625</Version>
</DocumentProperties>
<OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
    <DownloadComponents/>
    <LocationOfComponents HRef="file:///\\OfficeXP\CD1\"/>
</OfficeDocumentSettings>
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
        <WindowHeight>6135</WindowHeight>
        <WindowWidth>8445</WindowWidth>
        <WindowTopX>240</WindowTopX>
        <WindowTopY>120</WindowTopY>
        <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>

Cheers!!
0
 
LVL 1

Author Comment

by:dba123
Comment Utility
sorry, same error
0
 
LVL 21

Expert Comment

by:ap_sajith
Comment Utility
Atleast i've managed to replicate the error at my end.. Let me try and fix it.

Cheers!!
0
 
LVL 1

Author Comment

by:dba123
Comment Utility
man, I hope so...thanks
0
 
LVL 1

Author Comment

by:dba123
Comment Utility
I have been spending all day on this damn thing
0
 
LVL 1

Author Comment

by:dba123
Comment Utility
Did you change your code to produce that error or did it just suddenly come up?
0
 
LVL 21

Expert Comment

by:ap_sajith
Comment Utility
I changed the code to retrieve data from one of my databases and got the worksheet error while trying to open it.

Cheers!!
0
 
LVL 1

Author Comment

by:dba123
Comment Utility
Well, I can tell you one thing, I changed my query to select All files instead of Top 15.  I can see that Excel is downloading the data because the processing bar takes about 5 seconds.  At the end then of course that error.
0
 
LVL 1

Author Comment

by:dba123
Comment Utility
Ok, I tried running it on my server and the Excel file opens fine.
0
 
LVL 21

Expert Comment

by:ap_sajith
Comment Utility
Me too... Just got it working and was about to post the code here..

Glad that it worked finally.

Cheers!!
0
 
LVL 1

Author Comment

by:dba123
Comment Utility
yea, but this still poses a major problem wtih the end user.  It is useless if they can't go to the page and download the document.
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 1

Author Comment

by:dba123
Comment Utility
Fritz

>>When the page is called, a dialogue box appears offering you the choice to view the page in your browser, to save the file locally, or to cancel. There is no real way for this to write the file directly locally or else that would be a big security issue

So basically you are saying I'm getting this worksheet error because I am trying to save it locally?  If so, then how in the hell do we get it so that the end user can download the excel doc to their desktop....or a work around to where they can call the asp page, it downloads it to the server and I provide a link back to the created excel doc?  I don't see how the user can produce the excel file even on the server if this error occurs every time.
0
 
LVL 21

Expert Comment

by:ap_sajith
Comment Utility
I tried it from a different machine on the server. It is saving the file properly and opening it and displaying the data perfectly.

Try clearing the cache on your local PC and try executing the code again.

Cheers!!
0
 
LVL 1

Author Comment

by:dba123
Comment Utility
Furthermore, there are 2 major problems:

1) If I try this, it still is putting everything in one row in Excel

<Row>
     <Cell><Data ss:Type="String">Resolution</Data></Cell>
     <Cell><Data ss:Type="String">Email To</Data></Cell>

</Row>
<Row>

     <Cell><Data ss:Type="Number"><%=Query("Resolution")%></Data></Cell>
     <Cell><Data ss:Type="String"><%=Query("Email")%></Data></Cell>
</Row>

which shows like this in Excel

2) How in the heck can we then format the Excel spreadsheet cell background, font, etc. when going through this using XML?  I assume I have to use XML to format the Excel cells somehow?  and if so where the heck would I start?  I have to color code rows in the Excel spreadsheet.
0
 
LVL 21

Expert Comment

by:ap_sajith
Comment Utility
Try adding this at the top of your page to disable caching..

Response.Buffer=True
        Response.AddHeader "Content-Disposition", "attachment;filename=invoice.xls"
      Response.ContentType = "application/vnd.ms-excel"
        ' ** DISABLE CACHING **

            Response.Expires=-1
            Response.AddHeader "Pragma", "no-cache"
            Response.CacheControl="no-cache"
            Response.CacheControl = "Private"


Cheers!!
0
 
LVL 21

Expert Comment

by:ap_sajith
Comment Utility
1) If I try this, it still is putting everything in one row in Excel

<Row>
     <Cell><Data ss:Type="String">Resolution</Data></Cell>
     <Cell><Data ss:Type="String">Email To</Data></Cell>

</Row>
<Row>

     <Cell><Data ss:Type="Number"><%=Query("Resolution")%></Data></Cell>
     <Cell><Data ss:Type="String"><%=Query("Email")%></Data></Cell>
</Row>


Well.. It shous the data in one row.. isnt that what you want?

As for the second question, i'll have to look around..

Cheers!!
0
 
LVL 1

Author Comment

by:dba123
Comment Utility
No, I want the data to be like this

Label   Label2
value   Value 2

I am creating a matrix-like report, not a one liner!...that doesn't make sense having label value lable 2 value2

0
 
LVL 1

Author Comment

by:dba123
Comment Utility
actually it is inserting the data like this in excel with those rows

label label 2 value value2

so I have 2 labels followed by 2 values....that is just plain stupid
0
 
LVL 1

Author Comment

by:dba123
Comment Utility
DISABLE CACHING still produces a worksheet error.  You're telling me that you can go to another client computer and download and open the excel file fine?  And you are actually opening the Excel file with no errors?

I tried on another computer as well but still got the error.
0
 
LVL 21

Accepted Solution

by:
ap_sajith earned 500 total points
Comment Utility
Adding colours to the columns is easier than i expected. Basically you have to define Styles and assign the style to the cells..

Eg:

<%@Language="VBScript"%>
<%
      Response.Buffer=True
      Response.AddHeader "Content-Disposition", "attachment;filename=invoice.xls"
      Response.ContentType = "application/vnd.ms-excel"
        ' ** DISABLE CACHING **

            Response.Expires=-1
            Response.AddHeader "Pragma", "no-cache"
            Response.CacheControl="no-cache"
            Response.CacheControl = "Private"

     Function GetIsoDate()
          Dim datNow
          datNow = Now
          GetISODate = CStr(Year(datNow)) & "-" & PadItem(Month(datNow)) & "-" & PadItem(Day(datNow)) & "T" & _
           PadItem(Hour(datNow)) & ":" & PadItem(Minute(datNow)) & ":" & PadItem(Second(datNow)) & "Z"
     End Function
     
     Function PadItem(ByVal value)
          value = CStr(value)
          If Len(value) = 1 Then
               value = "0" & value
          End If
          PadItem = value
     End Function
%><?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">
<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
     <Author>Fritz_the_Blank</Author>
     <LastAuthor>Fritz_the_Blank</LastAuthor>
     <Created><%=GetIsoDate()%></Created>
     <Company>ABC Inc</Company>
     <Version>10.2625</Version>
</DocumentProperties>
<OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
    <DownloadComponents/>
    <LocationOfComponents HRef="file:///\\OfficeXP\CD1\"/>
</OfficeDocumentSettings>
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
        <WindowHeight>6135</WindowHeight>
        <WindowWidth>8445</WindowWidth>
        <WindowTopX>240</WindowTopX>
        <WindowTopY>120</WindowTopY>
        <ProtectStructure>False</ProtectStructure>
        <ProtectWindows>False</ProtectWindows>
</ExcelWorkbook>
<Styles>
     <ss:Style ss:ID="Default">
          <ss:Font ss:Size="14" ss:FontName="Times"/>
     </ss:Style>
     <ss:Style ss:ID="A">
          <ss:Font ss:Color="Red"/>
     </ss:Style>
     <ss:Style ss:ID="B" ss:Parent="Default">
          <ss:Font ss:FontName="Tahoma"/>
     </ss:Style>
     <ss:Style ss:ID="C" ss:Parent="A">
          <ss:Font ss:FontName="Courier"/>
     </ss:Style>
       <ss:Style ss:ID="E">
          <ss:Font ss:Color="Blue"/>
     </ss:Style>
       <ss:Style ss:ID="F">
          <ss:Font ss:Color="Green"/>
     </ss:Style>
</Styles>

<%
Dim oCon, oCmd, oRS, oXml, oXslt, oElement
Set oCon = Server.CreateObject("ADODB.Connection")
Set oCmd = Server.CreateObject("ADODB.Command")
Set oRS = Server.CreateObject("ADODB.Recordset")

oCon.Open "Provider=sqloledb;Data Source=XXXXX;Initial Catalog=XXXXX;UID=sa;PWD=XXXXX"
Set oCmd.ActiveConnection = oCon
oCmd.CommandText = "SELECT * FROM tbl_Users"
Dim count
count = 0
Set oRS = oCmd.Execute
While Not oRS.EOF
count = count + 1
%>


<Worksheet ss:Name="<%=oRS("User_ID")%>">
<Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount="4" x:FullColumns="1" x:FullRows="1" ID="Table<%=count%>">
<Column ss:Width="150" />
<Column ss:Width="200" />
<Row>
     <Cell ss:StyleID="A"><><Data ss:Type="String">User Id</Data></Cell>
     <Cell ss:StyleID="A"><><Data ss:Type="String">Email</Data></Cell>
</Row>
<Row>
     <Cell ss:StyleID="E"><Data ss:Type="String"><ss:Font ss:Bold="1"/><%=oRS("User_ID")%></Data></Cell>
     <Cell ss:StyleID="F"><Data ss:Type="String"><%=oRS("User_ID")%></Data></Cell>
</Row>
</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<% if count = 1 then %>
<Selected />
<% end if %>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
<%

oRS.MoveNext
wend
oRS.Close

%>
</Workbook>

Cheers!!
0
 
LVL 46

Expert Comment

by:fritz_the_blank
Comment Utility
>>So basically you are saying I'm getting this worksheet error because I am trying to save it locally?  If so, then how in the hell do we get it so that the end user can download the excel doc to their desktop....or a work around to where they can call the asp page, it downloads it to the server and I provide a link back to the created excel doc?  I don't see how the user can produce the excel file even on the server if this error occurs every time.<<

No, I was answering an earlier post in the thread about how to get the file to the client machine. Once the page renders, a dialog box appears giving the user the choice of viewing it in the browser or to download it to the users machine. Either way it does not stay on the server.

FtB
0
 
LVL 21

Expert Comment

by:ap_sajith
Comment Utility
>>actually it is inserting the data like this in excel with those rows

label label 2 value value2

so I have 2 labels followed by 2 values....that is just plain stupid<<

Actually i am able to display the cells like
Label1    Label2
Value 1  Value2

The above code does that for me.

>>I tried on another computer as well but still got the error.<<

Do the machines have Excel200 or above installed?.

I tried on two different machines besides the server and both seems to work fine.

Cheers!!
0
 
LVL 1

Author Comment

by:dba123
Comment Utility
we are using Excel 2002
0
 
LVL 1

Author Comment

by:dba123
Comment Utility
well, I don't see how yours is possible working on the client and I don't see why my server would format the rows differently than you are saying, that just doesn't seem possible.
0
 
LVL 1

Author Comment

by:dba123
Comment Utility
what version are  you using may I ask of Excel?
0
 
LVL 21

Expert Comment

by:ap_sajith
Comment Utility
Try modifying the code that i last postd. just modify the connection string, the select query and the database column names and the cell headings.

Also, make sure that you are not using windows authentication to log into SQL server.

Otherwise i dont quite see how it works on the server and not on the clients??..
Repeating what i said earlier.. try clearing the cache and execute the code from a new browser window.

Cheers!!
0
 
LVL 21

Expert Comment

by:ap_sajith
Comment Utility
Excel 2002.. But i dont think thats an issue.

Cheers!!
0
 
LVL 1

Author Comment

by:dba123
Comment Utility
clearing the cache and execute the code from a new browser window.

tried that a million times on 2 computers
0
 
LVL 21

Expert Comment

by:ap_sajith
Comment Utility
So do you mean to say that it is working only from the server?.

Think about it.. The code is executed on the server and only the output is sent to the browser irrespective of the fact that the browser resides on the server or not.

BTW.. Do you have authentication enabled on the server ie, is anonymous access disabled on the server?

Cheers!!
0
 
LVL 1

Author Comment

by:dba123
Comment Utility
I uninstalled and reinstalled office.  Now I am finally getting the XMLError.log where before I was getting some string log like 23ENNOK.log in which didn't exist.

the log which is now showing up after I reinstalled Office (XMLerr.log)is stating that I have to many tages

XML ERROR in Workbook Setting
REASON:      Too many tags
FILE:      C:\Documents and Settings\myname\Local Settings\Temporary Internet Files\Content.IE5\TTWQWZLJ\invoice[1].xls
GROUP:      Workbook
TAG:      Worksheet
ATTRIB:      Name
VALUE:      t
0
 
LVL 1

Author Comment

by:dba123
Comment Utility
>>BTW.. Do you have authentication enabled on the server ie, is anonymous access disabled on the server?

Are you talking about NTFS or SQL Server Enterprise Manager here. Please be specific.
0
 
LVL 1

Author Comment

by:dba123
Comment Utility
I have had no problems hitting this database from other asp pages in the same directory so how would access be a problem here?  I have never had any access problems and I can see that before I get the error on my client, that it is downloading data for sure because I can see Excel processing (bar) moving slower if I select all records.
0
 
LVL 21

Expert Comment

by:ap_sajith
Comment Utility
Hmm... Wish i was ther to checkout things first hand.

I was talking about IIS Authentication and the error log looks greek to me ;o)

The last thing you could try out is to try changing the filename form invoice.xls to something else in your code. Also, try creating a new page, copy paste the same working code and try accessing it from the clients.

Also, try retrieving the top 10 records instead of the whole bunch.

Cheers!!
0
 
LVL 1

Author Comment

by:dba123
Comment Utility
Ok, I took out my recordset and tried hard coded values again as I did earlier.  Only this time it worked great.  Now what is it about my recordset?

Without Recordset

<%@Language="VBScript"%>
<%
     Response.AddHeader "Content-Disposition", "attachment;filename=testing.xls"
      Response.ContentType = "application/vnd.ms-excel"

       ' ** DISABLE CACHING **

          Response.Expires=-1
          Response.AddHeader "Pragma", "no-cache"
          Response.CacheControl="no-cache"
          Response.CacheControl = "Private"

     Function GetIsoDate()
          Dim datNow
          datNow = Now
          GetISODate = CStr(Year(datNow)) & "-" & PadItem(Month(datNow)) & "-" & PadItem(Day(datNow)) & "T" & _
           PadItem(Hour(datNow)) & ":" & PadItem(Minute(datNow)) & ":" & PadItem(Second(datNow)) & "Z"
     End Function

     Function PadItem(ByVal value)
          value = CStr(value)
          If Len(value) = 1 Then
               value = "0" & value
          End If
          PadItem = value
     End Function
%>

<?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"
        >
<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
     <Author>ITDept</Author>
     <LastAuthor>ITDept</LastAuthor>
     <Created></Created>
     <Company></Company>
     <Version>10.2614.6626</Version>
</DocumentProperties>
<OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
    <DownloadComponents/>
    <LocationOfComponents HRef="file:///\\OfficeXP\CD1\"/>
</OfficeDocumentSettings>
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
        <WindowHeight>6135</WindowHeight>
        <WindowWidth>8445</WindowWidth>
        <WindowTopX>240</WindowTopX>
        <WindowTopY>120</WindowTopY>
        <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="testing">
<Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount="4" x:FullColumns="1" x:FullRows="1" ID="Table<%=count%>">
<Column ss:Width="150" />
<Column ss:Width="200" />
<Row>
     <Cell><Data ss:Type="String">Resolution</Data></Cell>
     <Cell><Data ss:Type="String">Email To</Data></Cell>
</Row>
<Row>
     <Cell><Data ss:Type="Number">2</Data></Cell>
     <Cell><Data ss:Type="String">test2</Data></Cell>
</Row>
</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<% if count = 1 then %>
<Selected />
<% end if %>
</WorksheetOptions>
</Worksheet>

</Workbook>




with recordset


<%@Language="VBScript"%>
<%
     Response.AddHeader "Content-Disposition", "attachment;filename=testing.xls"
      Response.ContentType = "application/vnd.ms-excel"

       ' ** DISABLE CACHING **

          Response.Expires=-1
          Response.AddHeader "Pragma", "no-cache"
          Response.CacheControl="no-cache"
          Response.CacheControl = "Private"

     Function GetIsoDate()
          Dim datNow
          datNow = Now
          GetISODate = CStr(Year(datNow)) & "-" & PadItem(Month(datNow)) & "-" & PadItem(Day(datNow)) & "T" & _
           PadItem(Hour(datNow)) & ":" & PadItem(Minute(datNow)) & ":" & PadItem(Second(datNow)) & "Z"
     End Function

     Function PadItem(ByVal value)
          value = CStr(value)
          If Len(value) = 1 Then
               value = "0" & value
          End If
          PadItem = value
     End Function
%>

<?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"
        >
<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
     <Author>ITDept</Author>
     <LastAuthor>ITDept</LastAuthor>
     <Created></Created>
     <Company></Company>
     <Version>10.2614.6626</Version>
</DocumentProperties>
<OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
    <DownloadComponents/>
    <LocationOfComponents HRef="file:///\\OfficeXP\CD1\"/>
</OfficeDocumentSettings>
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
        <WindowHeight>6135</WindowHeight>
        <WindowWidth>8445</WindowWidth>
        <WindowTopX>240</WindowTopX>
        <WindowTopY>120</WindowTopY>
        <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>


<%
Dim oCon, Query, strSQL, count

Set oCon = Server.CreateObject("ADODB.Connection")
oCon.Open "Provider=sqloledb;Data Source=somesource;Initial Catalog=somecatalog;User Id=sa;Password=somepassword;"

Set Query = Server.CreateObject("ADODB.Recordset")

strSQL = "SELECT TOP 100 * FROM mytable"

count = 0

Set Query = oCon.Execute(strSQL)

Do While Not Query.EOF
count = count + 1
%>

<Worksheet ss:Name="testing">
<Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount="4" x:FullColumns="1" x:FullRows="1" ID="Table<%=count%>">
<Column ss:Width="150" />
<Column ss:Width="200" />
<Row>
     <Cell><Data ss:Type="String">Resolution</Data></Cell>
     <Cell><Data ss:Type="String">Email</Data></Cell>
</Row>
<Row>
     <Cell><Data ss:Type="Number"><%=Query("Resolution")%></Data></Cell>
     <Cell><Data ss:Type="String"><%=Query("Email")%></Data></Cell>
</Row>
</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<% if count = 1 then %>
<Selected />
<% end if %>
</WorksheetOptions>
</Worksheet>
<%

Query.MoveNext
loop
Query.Close

%>
</Workbook>




Note: my connection string have valid values below, I just filled them in with fake values for privacy purposes
oCon.Open "Provider=sqloledb;Data Source=somesource;Initial Catalog=somecatalog;User Id=sa;Password=somepassword;"
0
 
LVL 21

Expert Comment

by:ap_sajith
Comment Utility
What type of data does your Resolution column contain?. Maybe you have data with singlequotes / double quotes / commas... or non XML supported characters. Try using email in both the fields.. or try hard coding the value for now in one column.

Cheers!!
0
 
LVL 1

Author Comment

by:dba123
Comment Utility
Resolution = int

I tried using email in both..no luck. I also tried to create a local DSN to that source then change my connection string to reference the DSN name on my PC but the connection would not work with the DSN I created.  Even though the DSN shows successfull after I tested it duing creation.
0
 
LVL 1

Author Comment

by:dba123
Comment Utility
yes, I said hard coding works if I were to replace the <%=Query("Email_To")%> with a hard coded string  ...it is just when I use a recordset
0
 
LVL 21

Expert Comment

by:ap_sajith
Comment Utility
>>I tried using email in both..no luck. I also tried to create a local DSN to that source then change my connection string to reference the DSN name on my PC but the connection would not work with the DSN I created.  Even though the DSN shows successfull after I tested it duing creation.

Wouldnt work because your code resides on the server and the DSN should thus reside on the server.

>>yes, I said hard coding works if I were to replace the <%=Query("Email_To")%> with a hard coded string  ...it is just when I use a recordset

I said try hardcoding either of the two values and see the result.. Not both.

Also, for the number column, use something like

<Data ss:Type="Number"><%=(oRS("Freight"))%></Data>

Cheers!!
0
 
LVL 1

Author Comment

by:dba123
Comment Utility
I tried that, using one hard coded and one recordset value....didn't work.  There has got to be something here that is pretty obvious, there just has to be. Because like I said, if I don't encorporate my recordset with this whole thing and hard code all values, it works which is very frustrating...we're almost there but what the hell could it be?  

Are you using a recorset from your server or just a local database on your C drive that is setup via Enterprise Manager locally?  that is a big question I have since you haven't stated that yet.

0
 
LVL 1

Author Comment

by:dba123
Comment Utility
My latest code

<%@Language="VBScript"%>
<%
     Response.AddHeader "Content-Disposition", "attachment;filename=testing.xls"
      Response.ContentType = "application/vnd.ms-excel"

       ' ** DISABLE CACHING **

          Response.Expires=-1
          Response.AddHeader "Pragma", "no-cache"
          Response.CacheControl="no-cache"
          Response.CacheControl = "Private"

     Function GetIsoDate()
          Dim datNow
          datNow = Now
          GetISODate = CStr(Year(datNow)) & "-" & PadItem(Month(datNow)) & "-" & PadItem(Day(datNow)) & "T" & _
           PadItem(Hour(datNow)) & ":" & PadItem(Minute(datNow)) & ":" & PadItem(Second(datNow)) & "Z"
     End Function

     Function PadItem(ByVal value)
          value = CStr(value)
          If Len(value) = 1 Then
               value = "0" & value
          End If
          PadItem = value
     End Function
%>

<?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"
        >
<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
     <Author>ITDept</Author>
     <LastAuthor>ITDept</LastAuthor>
     <Created></Created>
     <Company></Company>
     <Version>10.2614.6626</Version>
</DocumentProperties>
<OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
    <DownloadComponents/>
    <LocationOfComponents HRef="file:///\\OfficeXP\CD1\"/>
</OfficeDocumentSettings>
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
        <WindowHeight>6135</WindowHeight>
        <WindowWidth>8445</WindowWidth>
        <WindowTopX>240</WindowTopX>
        <WindowTopY>120</WindowTopY>
        <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>


<%

Dim oCon, Query, strSQL, count

Set oCon = Server.CreateObject("ADODB.Connection")
oCon.Open "Provider=sqloledb;Data Source=somesource;Initial Catalog=somecatalog;User Id=sa;Password=somepassword;"


Set Query = Server.CreateObject("ADODB.Recordset")

strSQL = "SELECT TOP 100 * FROM mytable"

count = 0

Set Query = oCon.Execute(strSQL)

Do While Not Query.EOF
count = count + 1

%>

<Worksheet ss:Name="testing">
<Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount="4" x:FullColumns="1" x:FullRows="1" ID="Table<%=count%>">
<Column ss:Width="150" />
<Column ss:Width="200" />
<Row>
     <Cell><Data ss:Type="String">Status</Data></Cell>

</Row>
<Row>
     <Cell><Data ss:Type="String"><%=Query("status_name")%></Data></Cell>

</Row>
</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<% if count = 1 then %>
<Selected />
<% end if %>
</WorksheetOptions>
</Worksheet>
<%

Query.MoveNext
loop
Query.Close

%>
</Workbook>
0
 
LVL 1

Author Comment

by:dba123
Comment Utility
ap_sajith

you won't believe this.  I posted this problem in the XML forum and the response was:

<Worksheet ss:Name="test">
must be
<Worksheet ss:Name="test<%=count%>">

IT WORKS!   but I don't see why I had to do this...I'll find out and post here.
0
 
LVL 1

Author Comment

by:dba123
Comment Utility
    <ss:Style ss:ID="A">
          <ss:Font ss:Color="Red"/>
     </ss:Style>
is this CSS?  or XML style sheets?  I wonder how to get the cell background color to be different
0
 
LVL 21

Expert Comment

by:ap_sajith
Comment Utility
Shall i continue in the new thread?.

>><Worksheet ss:Name="test">
must be
<Worksheet ss:Name="test<%=count%>">

I had noticed that and had mentioned that in one of my earlier comments.

>>is this CSS?  or XML style sheets?  I wonder how to get the cell background color to be different

I am not quite sure. .But i dont think it would be too difficult to figure out.

Thanks for the points.

Cheers!!
0
 
LVL 1

Author Comment

by:dba123
Comment Utility
I have a new thread out on a new problem with this when trying to move the recordset looping to just write to one worksheet....thanks a lot.
0
 
LVL 21

Expert Comment

by:ap_sajith
Comment Utility
Did you delete the new thread?. I was working on it.

Cheers!!
0
 
LVL 1

Author Comment

by:dba123
Comment Utility
sorry, yes, I figured it out
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
This video discusses moving either the default database or any database to a new volume.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now