M
Mad Scientist Jr
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.
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.