473,242 Members | 1,604 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,242 software developers and data experts.

question about opening SQL results in Excel from ASP.NET via XML

>From an asp.net web page I want the user to open the results of a SQL
query in Excel, as automatically as possible (ie not having to loop
through columns, rows, in code).

For this, dataset.writexml works great
(got the code from
http://forums.devx.com/archive/index.php/t-57273.html )

The only question I have is, when Excel opens up, it isn't the view I
would prefer. It opens as a read-only workbook, I would prefer as an
xml list.

If I look at the workbooks.open method

exc.Workbooks.Open(filename:=Server.MapPath("") &
"\my_sql_results.xml")

there are additional parameters
Open (Filename As String, [UpdateLinks As Object], [ReadOnly As
Object], [Password As Object], etc...

that I haven't found any documentation on, but might allow me to
specify the "xml list" format. I am guessing the "format" parameter
would be it, but I don't know what to pass in. Can anyone help?

Thanks...
PS If I try opening the XML file locally from Excel, it prompts:

Open XML
Please select how you would like to open this file:
(x) As an XML list
( ) As a read-only workbook
( ) Use the XML Source task pane
[OK] [Cancel] [Help]

I choose the first option and click OK and Excel then prompts:

Microsoft Office Excel
The specified XML source does not refer to a schema. Excel will create
a
schema based on the XML source data.
[ ] In the future, do not show this message.
[OK] [Help]

I click OK and it opens in Excel in the format I want.

PPS Here is the full code from
http://forums.devx.com/archive/index.php/t-57273.html

01-15-2003, 04:04 PM
Here's an example. The variable "ds1" in the code below is a DataSet
filled
with a single DataTable (I used the SQL Server sample pubs database
Authors
table to test this) . I haven't tested it with multiple DataTables.

You need to add a COM reference to the Microsoft Excel Object Library.
This
sample used the Microsoft Excel 10.0 Object Library -- a version which
can
read XML files. As Constance noted, you may need to write some other
file
type to import the data into earlier versions of Excel.

' create a DataSet
Dim ds As New ds1()

' choose a file name for the output
Dim filename As String = "c:\authors_data.xml"

' open the connection and fill the DataSet
' Sample used "SELECT * FROM Authors" as the query
Me.SqlConnection1.Open()
Me.SqlDataAdapter1.Fill(ds)

' delete any existing file
If File.Exists(filename) Then
File.Delete(filename)
End If

' save the DataSet in its default XML format
ds.WriteXml(filename)

' clean up
Me.SqlConnection1.Close()
Me.SqlDataAdapter1.Dispose()
Me.SqlConnection1.Dispose()

' create an Excel Application object and make it visible
Dim exc As New Excel.Application()
exc.Visible = True

' open the saved file
exc.Workbooks.Open(filename:=filename)

' show it
exc.ActiveWindow.Visible = True

This uses Excel's defaults for the column headings, etc. and because of
the
way the DataSet persists itself in XML, the name of the DataSet shows
up in
the spreadsheet. You could easily get rid of the unwanted values or
change
column names by either processing the saved XML before loading it into
Excel, or by using Excel's object model to delete and modify the data
after
loading the XML file. Finally, you could accomplish this same result
without
going through an intermediate file by iterating through the DataSet and
stuffing the data directly into Excel worksheet cells.

Nov 17 '06 #1
2 3092
Hi,

I would not automate excel from a website. This is probably the best
method

http://support.microsoft.com/kb/319180

Ken
------------------------------------------

"Mad Scientist Jr" wrote:
From an asp.net web page I want the user to open the results of a SQL
query in Excel, as automatically as possible (ie not having to loop
through columns, rows, in code).

For this, dataset.writexml works great
(got the code from
http://forums.devx.com/archive/index.php/t-57273.html )

The only question I have is, when Excel opens up, it isn't the view I
would prefer. It opens as a read-only workbook, I would prefer as an
xml list.

If I look at the workbooks.open method

exc.Workbooks.Open(filename:=Server.MapPath("") &
"\my_sql_results.xml")

there are additional parameters
Open (Filename As String, [UpdateLinks As Object], [ReadOnly As
Object], [Password As Object], etc...

that I haven't found any documentation on, but might allow me to
specify the "xml list" format. I am guessing the "format" parameter
would be it, but I don't know what to pass in. Can anyone help?

Thanks...
PS If I try opening the XML file locally from Excel, it prompts:

Open XML
Please select how you would like to open this file:
(x) As an XML list
( ) As a read-only workbook
( ) Use the XML Source task pane
[OK] [Cancel] [Help]

I choose the first option and click OK and Excel then prompts:

Microsoft Office Excel
The specified XML source does not refer to a schema. Excel will create
a
schema based on the XML source data.
[ ] In the future, do not show this message.
[OK] [Help]

I click OK and it opens in Excel in the format I want.

PPS Here is the full code from
http://forums.devx.com/archive/index.php/t-57273.html

01-15-2003, 04:04 PM
Here's an example. The variable "ds1" in the code below is a DataSet
filled
with a single DataTable (I used the SQL Server sample pubs database
Authors
table to test this) . I haven't tested it with multiple DataTables.

You need to add a COM reference to the Microsoft Excel Object Library.
This
sample used the Microsoft Excel 10.0 Object Library -- a version which
can
read XML files. As Constance noted, you may need to write some other
file
type to import the data into earlier versions of Excel.

' create a DataSet
Dim ds As New ds1()

' choose a file name for the output
Dim filename As String = "c:\authors_data.xml"

' open the connection and fill the DataSet
' Sample used "SELECT * FROM Authors" as the query
Me.SqlConnection1.Open()
Me.SqlDataAdapter1.Fill(ds)

' delete any existing file
If File.Exists(filename) Then
File.Delete(filename)
End If

' save the DataSet in its default XML format
ds.WriteXml(filename)

' clean up
Me.SqlConnection1.Close()
Me.SqlDataAdapter1.Dispose()
Me.SqlConnection1.Dispose()

' create an Excel Application object and make it visible
Dim exc As New Excel.Application()
exc.Visible = True

' open the saved file
exc.Workbooks.Open(filename:=filename)

' show it
exc.ActiveWindow.Visible = True

This uses Excel's defaults for the column headings, etc. and because of
the
way the DataSet persists itself in XML, the name of the DataSet shows
up in
the spreadsheet. You could easily get rid of the unwanted values or
change
column names by either processing the saved XML before loading it into
Excel, or by using Excel's object model to delete and modify the data
after
loading the XML file. Finally, you could accomplish this same result
without
going through an intermediate file by iterating through the DataSet and
stuffing the data directly into Excel worksheet cells.

Nov 18 '06 #2
Thanks for your reply. That's a lot of code, and it involves
hard-coding an XSLT file specific to your dataset. My application is an
Intranet-only app that runs ad-hoc queries. I suppose I could modify it
to iterate through the result set columns and build the XSLT on the
fly, but would automating Excel from a Web site on a secure Intranet be
bad? I would really prefer just specifying the correct format parameter
in the workbooks.open method...

Ken wrote:
Hi,

I would not automate excel from a website. This is probably the best
method

http://support.microsoft.com/kb/319180

Ken
------------------------------------------

"Mad Scientist Jr" wrote:
>From an asp.net web page I want the user to open the results of a SQL
query in Excel, as automatically as possible (ie not having to loop
through columns, rows, in code).

For this, dataset.writexml works great
(got the code from
http://forums.devx.com/archive/index.php/t-57273.html )

The only question I have is, when Excel opens up, it isn't the view I
would prefer. It opens as a read-only workbook, I would prefer as an
xml list.

If I look at the workbooks.open method

exc.Workbooks.Open(filename:=Server.MapPath("") &
"\my_sql_results.xml")

there are additional parameters
Open (Filename As String, [UpdateLinks As Object], [ReadOnly As
Object], [Password As Object], etc...

that I haven't found any documentation on, but might allow me to
specify the "xml list" format. I am guessing the "format" parameter
would be it, but I don't know what to pass in. Can anyone help?

Thanks...
PS If I try opening the XML file locally from Excel, it prompts:

Open XML
Please select how you would like to open this file:
(x) As an XML list
( ) As a read-only workbook
( ) Use the XML Source task pane
[OK] [Cancel] [Help]

I choose the first option and click OK and Excel then prompts:

Microsoft Office Excel
The specified XML source does not refer to a schema. Excel will create
a
schema based on the XML source data.
[ ] In the future, do not show this message.
[OK] [Help]

I click OK and it opens in Excel in the format I want.

PPS Here is the full code from
http://forums.devx.com/archive/index.php/t-57273.html

01-15-2003, 04:04 PM
Here's an example. The variable "ds1" in the code below is a DataSet
filled
with a single DataTable (I used the SQL Server sample pubs database
Authors
table to test this) . I haven't tested it with multiple DataTables.

You need to add a COM reference to the Microsoft Excel Object Library.
This
sample used the Microsoft Excel 10.0 Object Library -- a version which
can
read XML files. As Constance noted, you may need to write some other
file
type to import the data into earlier versions of Excel.

' create a DataSet
Dim ds As New ds1()

' choose a file name for the output
Dim filename As String = "c:\authors_data.xml"

' open the connection and fill the DataSet
' Sample used "SELECT * FROM Authors" as the query
Me.SqlConnection1.Open()
Me.SqlDataAdapter1.Fill(ds)

' delete any existing file
If File.Exists(filename) Then
File.Delete(filename)
End If

' save the DataSet in its default XML format
ds.WriteXml(filename)

' clean up
Me.SqlConnection1.Close()
Me.SqlDataAdapter1.Dispose()
Me.SqlConnection1.Dispose()

' create an Excel Application object and make it visible
Dim exc As New Excel.Application()
exc.Visible = True

' open the saved file
exc.Workbooks.Open(filename:=filename)

' show it
exc.ActiveWindow.Visible = True

This uses Excel's defaults for the column headings, etc. and because of
the
way the DataSet persists itself in XML, the name of the DataSet shows
up in
the spreadsheet. You could easily get rid of the unwanted values or
change
column names by either processing the saved XML before loading it into
Excel, or by using Excel's object model to delete and modify the data
after
loading the XML file. Finally, you could accomplish this same result
without
going through an intermediate file by iterating through the DataSet and
stuffing the data directly into Excel worksheet cells.
Nov 20 '06 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Jawahar Rajan | last post by:
All, I am using the ASP code below to save some data from a SQL Server database via ADO as an Excel spreadsheet strReportName = Request.QueryString("ReportName") If len(strReportName) > 0 then...
4
by: Aliza Klein | last post by:
I am new to ASP so please pardon me if this is crazy. I have data that I want to allow the user to be able to save in a format that will then enable the user to open the data in Excel. (I have...
6
by: Terrell Miller | last post by:
I am developing an Access/SQL2k app for a science lab. One of the lab folks asked me this, and I don't know the answer: Is it possible to do simple, straight-line trends somehow in Access...
18
by: Andre Laplume via AccessMonster.com | last post by:
I have inherited a bunch of dbs which are are shared among a small group in my dept. We typically use the dbs to write queries to extract data, usually dumping it into Excel. Most dbs originated...
7
by: aacool | last post by:
Hi, I've been trying to open an Excel connection through OLEDB with the code below: string ConnectString =@"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + fileName.Text +...
2
by: xhenxhe | last post by:
I don't some Excel automation. I've created a program that opens and Excel template and inputs information to it. It runs great on my machine. When I build and deploy I have a user that keep...
9
by: CGW | last post by:
I asked the question yesterday, but know better how to ask it, today: I'm trying to use the File.Copy method to copy a file from a client to server (.Net web app under IIS ). It looks to me that...
6
by: Gunawan | last post by:
Dear All, I have create an excel (COM Object) using this code Excel.Application xls = new Excel.Application(); but I can not remove it from memory although I have using close and quit ...
16
by: Phil Stanton | last post by:
I have a form with a button which is supposed to open an Excel file (With lots of Macros /VBA) in it. The Excel file gets it's data from the Access program Here is the code Private Sub...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.