Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1008
  • Last Modified:

OWC - getting arrays into multiple spreadsheets

Now I need some hand holding per http://www.experts-exchange.com/Web/Web_Languages/ASP/Q_20919538.html

I am not printing out tables to IE, I am simply creating arrays that hold the values then spitting them out to Excel.  So my first question is how would I start using the XML stated in the link above.  Do I simply copy and paste the xml before my ASP or after?  In between the <% and %> or what?

Then how would I move the data in my array to each worksheet name and then what creates the file?  Currently, this is my code which creates an Excel spreadsheet and moves the data into it:

Sub CommentReport_Excel

      Dim p, i

      strSQL = "SELECT Email, Date, Comments FROM mytable"
      Set Query = objConnection.Execute(strSQL)

On Error Resume Next

dim objSpreadsheet
Set objSpreadsheet = Server.CreateObject("OWC.Spreadsheet")

p = 1

Do While NOT Query.EOF

            objSpreadsheet.Columns(p).Autofitcolumns
            'objSpreadsheet.Cells(p,1).font.size = "12"
            'objSpreadsheet.Cells(p,1).font.bold = True
            objSpreadsheet.Cells(p,1).Interior.Color = RGB(204,204,213)
            objSpreadsheet.Cells(p,1).Value = Query(0).Value
            objSpreadsheet.Cells(p,2).Interior.Color = RGB(214,216,211)
            objSpreadsheet.Cells(p,2).Value = Query(1).Value
            objSpreadsheet.Cells(p,3).Interior.Color = RGB(33,33,244)
            objSpreadsheet.Cells(p,3).Value = Query(2).Value

      p = p + 1

      Query.MoveNext

loop

Call objSpreadsheet.ActiveSheet.Export("C:\SurveyInfo.xls",0)

End Sub
0
dba123
Asked:
dba123
  • 18
  • 8
1 Solution
 
ap_sajithCommented:
If i read the post correctly, then the approach used is purely XML. It doesnt involve OWC.

You basically have to build the XML string as shown here..
http://www.experts-exchange.com/Web/Web_Languages/XML/Q_20919841.html

Cheers!!
0
 
dba123Author Commented:
forget that
0
 
dba123Author Commented:
I mean, is it for sure the only way to do so if using OWC?  There has got to be a way to do this through OWC rather than have to resort to XML.  First, I have spent 3 days coding my app to export to Excel using OWC, second, I have not a clue how to use XML, third, I don't intend to have to totally recode my Excel export automation in XML now that I have spent 3 days doing it through OWC.  This is crazy.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
ap_sajithCommented:
BTW.. thanks a mil for posting this Q,.. Learned something new today.. I wouldnt have come across this if not for you.

Try the sample code posted in the accepted answer at the link provided above.

Cheers!!
0
 
dba123Author Commented:
And the link you posted was the link I am showing up in my original post at the top here.
0
 
ap_sajithCommented:
The links are differnet ;o).. Both were asked by ftb.. but in different topic areas.

As for OWC, never really have got along worjing with OWC as MS recomends using it in webservers. The XML solution seems pretty neat to me though.

You really dont need much knowledge of XML, you are just creating a XML string.. just like you create a table string. The main points to note that it is case sensitive.

Cheers!!
0
 
dba123Author Commented:
Sub NewResponses_Excel2

      Dim array1, i, j, Q
      
      strSQL = "SELECT CustId, '' as empty1, Email, '' as empty2, CONVERT(CHAR(10), Date, 101), CONVERT(CHAR(10), Sent_Date, 101) FROM mytable where Date BETWEEN '" & Trim(Request.Form("Date1")) & "' AND '" & Trim(Request.Form("Date2")) & "'"

      Set Q = objConnection.Execute(strSQL)

      array1 = Q.GetRows


OpenVanDB

      For i=0 to Ubound(array1,2)
            array1(1,i) = GetCompanyName(array1(0,i))
            array1(4,i) = GetCSRName(array1(3,i))
      Next

CloseVanDB

<?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 Department</Author>
     <LastAuthor>IT Department</LastAuthor>
     <Created><%=Date()%></Created>
     <Company>Vitria</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>


<Worksheet ss:Name="SurveyReports - <%Date()%>">
<Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount="4" x:FullColumns="1" x:FullRows="1" ID="Table">
<Column ss:Width="150" />
<Column ss:Width="200" />

<%       For b=0 to Ubound(array,1)
            For a=0 to Ubound(array,2) %>

<Row>
     <Cell><Data ss:Type="String"><%=array1(b,a)%></Data></Cell>
</Row>

<%             Next
         Next
%>

</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<Selected />
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
<%


%>
</Workbook>

End Sub



What do you think, will this work and what am I doing wrong here?
0
 
dba123Author Commented:
Where do you specify where to create the Excel file...how is it saving it to a location?
0
 
chisholmdCommented:
Looking at your original post in this thread what is not working? Is there an error message? Does the file get created but some formating or data is work?

Is this page accessed anonymously? Because if it is then the accoiunt used for security is your iusr account. You didn't give the iusr account permissions to this path did you?

C:\SurveyInfo.xls

Also, I am not totally familiar with OWC I usualy use excel app directly. Could there be permission issues with accessing this object itself?


0
 
dba123Author Commented:
My original post turned into having to use XML.  There is nothing wrong with my original post and I never said there was.  I posted code to show you how I was doing the writing to excel from ASP.  I wanted to know how to write to multiple spreadsheets in which all I have been told is that you must use XML at that point.  Now I'm trying my own stab at it via the link where Fritz used XML in his and I just now posted my latest code at my attempt to encorporate XML.

The only problem is, I must take my XML portion out of the <%  %> but I can't do this because my ASP page has multiple subs in it and each sub shows different forms.  I can't just throw out XML in the open.  Plus, I have no need to show the data in an XML table, I just need to take my array and use XML to shove it into multiple Excel spreadsheets.
0
 
dba123Author Commented:
And the example where Fritz is using XML, it appears that he is no longer using OWC.  Originally he tried OWC like I did but then came across the same stumbling block...how to move data from ASP to Excel in multiple sheets.  I see that Fritz had to start using this form of syntaxt

Response.ContentType = "application/vnd.ms-excel"

rather than OWC:

dim objSpreadsheet
Set objSpreadsheet = Server.CreateObject("OWC.Spreadsheet")

which brings me to another question besides if my new XML code works.  The question I have in addition is that if you useResponse.ContentType = "application/vnd.ms-excel", that simply throws it into Excel without any formatting.  I assume that you must use XML to then format the cell colors, format, etc. in Excel at that point where previously I was using OWC to do this? (e.g.           'objSpreadsheet.Cells(p,1).font.bold = True
          objSpreadsheet.Cells(p,1).Interior.Color = RGB(204,204,213))
0
 
dba123Author Commented:
0
 
dba123Author Commented:
There has got to be a way to do this through OWC without using 100% XML but maybe not.
0
 
ap_sajithCommented:
The URL you originally posted was http://www.experts-exchange.com/Web/Web_Languages/ASP/Q_20919538.html
 
And that is where you are getting the above code from. If you look at the accepted answer link from http://www.experts-exchange.com/Web/Web_Languages/XML/Q_20919841.html ,

You will notice a different approach. The entire code block has to be in a separate page, not in  a sub.. and you should either open the page in a new window or do a response.redirect to the new page.

This would prompt you whether you want to open it or save it.u can infact try out the code just by changing the connection string properties if you are using MSSQL

Cheers!!
0
 
dba123Author Commented:
Ok, the problem or better yet question is that before I decide to go to XML, I must be sure that I can format the excel cells as needed.  I don't want to go to a new way of doing all of this when I don't know if it can be done AND if I am unsure exactly how you are to format the Excel cells when using the XML way.  Quite frankly, it took me a long time to find any info on OWC formatting that I show in my initial post.
0
 
dba123Author Commented:
I tried pasting this into a new asp page and just trying the code as you suggested before I even attempt to go back and continue my earlier post which needs to be in a separate file as you stated.  

I tried to run that page but it is not finding the page..in other words something is not right in my code.  The connection string is in my functions.asp so that should be ok.

<!--#include virtual="/includes/header.asp"-->

<!--#include virtual="/includes/adovbs.inc" -->
<!--#include virtual="/includes/functions.asp" -->
Server.ScriptTimeout = 15

%>

<%
     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>CompanyName</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 strSQL, Query

      strSQL = "SELECT DISTINCT CSR_ID FROM mytable WHERE Date1 IS NOT NULL"
      Set Query = objConnection.Execute(strSQL)

While Not Query.EOF

%>
<Worksheet ss:Name="TEST1">
<Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount="4" x:FullColumns="1" x:FullRows="1" ID="Table">
<Column ss:Width="150" />
<Column ss:Width="200" />
<Row>
     <Cell><Data ss:Type="String">CE Id</Data></Cell>
     <Cell><Data ss:Type="String"><%=Query("CSR_ID")%></Data></Cell>
</Row>
</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<Selected />
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
<%
Query.MoveNext
Loop
Query.Close
%>
</Workbook>
0
 
ap_sajithCommented:
I was talking about the following piece of code..
Just change the parameters for

oCon.ConnectionString = "Provider=SQLOLEDB;Initial Catalog=NorthWind;Data Source=(local);User Id=user;Password=password;"

I assume that you are running on MS SQL and have the Northwind Database in SQL Server.

As for foramtting the cells... I am not quite sure how to go about it. Maybe there is a way.. because everything from last author to the owner seems to be configurable using this method.

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

<%@Language="VBScript"%>
<%
     'Using the Northwind as an example:
     'works for Excel 2000 and Excel 2002
   
     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=user;Password=password;"
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
 
dba123Author Commented:
ooops...yea, he is using northwind....thanks
0
 
dba123Author Commented:
Ok, tried it.  It prompted me to save but then wouldnt' allow me.  Is it tyring to save to Excel here?  It states that it is trying to save an asp page..I don't get it and of course since I can't accomplish this, I can't tell

Error after I click Save As

"Internet Explorer Cannot Downloand mypage.asp from mysite.com
Internet Explorer was not able to open this Internet site.  The site requested is either unavailable or cannot be found"

why is it trying to open a site rather than save an Excel file?
0
 
ap_sajithCommented:
Replace

Response.ContentType = "application/vnd.ms-excel"

With

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

Also, Make sure that you have provided the correct parametes for the db connection string.

I tried the code at my end.. it works like a dream...

Cheers!!
0
 
dba123Author Commented:
weird, checked my connections, still getting that error.  I have my code below and have renamed some stuff for privacy

<%@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>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, Query, strSQL

oCon.ConnectionString "Provider=sqloledb;Data Source=mydatasource;Initial Catalog=catalogname;User Id=sa;Password=somepassword;"

oCon.Open

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

strSQL = "SELECT TOP 15 * FROM mytable"

Dim count
count = 0

Set Query = objConnection.Execute(strSQL)

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">Date</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
Wend
oCon.Close
%>
</Workbook>
0
 
dba123Author Commented:
Why is the filename showing my asp page name and the file type blank when it prompts me to save?
0
 
dba123Author Commented:
and is this line looking on my hard drive?     <LocationOfComponents HRef="file:///\\OfficeXP\CD1\"/>
0
 
ap_sajithCommented:
How did you call this page?

Response.redirect?

Cheers!!
0
 
dba123Author Commented:
I didn't call it, I am just directly loading it via URL...typing in my URL, then this page
0
 
ap_sajithCommented:
>>and is this line looking on my hard drive?     <LocationOfComponents HRef="file:///\\OfficeXP\CD1\"/>

Not quite sure.. maybe the location as to where to find the files from the Office CD!

>>I didn't call it, I am just directly loading it via URL...typing in my URL, then this page

hmm.. mine also doesnt seem to work now.. I'll fix it and let you know...

Cheers!!
0
 
dba123Author Commented:
this post is too long and getting of subject from my original post.  I am going to start a new thread in ASP forum.
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

  • 18
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now