Creating Excel document from Dataset and XML ?

[Paul]

Hi all

Arggghhh...........
The problem.....I want the user to be able to create an excel document and
name particular cells in the document where they want the data to be placed
and then save this out of an XML file or Excel Template file.

Next I need to convert a dataset to xml and try and transform this data into
the users xml file..........i've seen a few things on this but havent had
much success...

basically

1. template file in xml with named cells
2. xml data payload
3. xsl style sheet to blend them all together

please if someone has an example then please respond. I know there a few
applications out there that does this, but hey it cant be that hard, just
not p to scracth with my xsl stuff.

thanks

[Ken Tucker [MVP]]

Hi,

Maybe this will help.
http://support.microsoft.com/default...b;en-us;307021

Ken
-----------
"Paul" <ma********@hotmail.com.nospam> wrote in message
news:eZ****************@TK2MSFTNGP11.phx.gbl...

Hi all

Arggghhh...........
The problem.....I want the user to be able to create an excel document and
name particular cells in the document where they want the data to be placed and then save this out of an XML file or Excel Template file.

Next I need to convert a dataset to xml and try and transform this data into the users xml file..........i've seen a few things on this but havent had
much success...

basically

1. template file in xml with named cells
2. xml data payload
3. xsl style sheet to blend them all together

please if someone has an example then please respond. I know there a few
applications out there that does this, but hey it cant be that hard, just
not p to scracth with my xsl stuff.

thanks

[Paul]

The example just goes through creating an excel sheet using a XSL sheet
which obviously at a user level they would have no idea of creating,
basicaly I want the user to populate an excel file with pointers to where
they wan the data and to embed the dataset into the excel sheet and save
this out.

Thanks
Paul

"Ken Tucker [MVP]" <vb***@bellsouth.net> wrote in message
news:%2****************@tk2msftngp13.phx.gbl...

Hi,

Maybe this will help.
http://support.microsoft.com/default...b;en-us;307021

Ken
-----------
"Paul" <ma********@hotmail.com.nospam> wrote in message
news:eZ****************@TK2MSFTNGP11.phx.gbl...

Hi all

Arggghhh...........
The problem.....I want the user to be able to create an excel document and name particular cells in the document where they want the data to be

placed

and then save this out of an XML file or Excel Template file.

Next I need to convert a dataset to xml and try and transform this data

into

the users xml file..........i've seen a few things on this but havent had much success...

basically

1. template file in xml with named cells
2. xml data payload
3. xsl style sheet to blend them all together

please if someone has an example then please respond. I know there a few
applications out there that does this, but hey it cant be that hard, just not p to scracth with my xsl stuff.

thanks

[Fergus Cooney]

Hi Paul,

!! ... hey it cant be that hard ...

Are you sure about that? What sort of Xml does Excel produce - nice clean
economical stuff or reams of God-know-what?

Another alternative that might be worth investigating is to use the
XmlDocument class and do it programmatically.

Regards,
Fergus

[Paul]

lol,

Well yeah the easy way is do it programmatically but I need the ability for
a user to create a template file, so that they can setup the font details,
page headers etc. We used ot do it by spawning excel but it just seems to
dangerous to do this on the web server.
"Fergus Cooney" <fi******@tesco.net> wrote in message
news:%2****************@TK2MSFTNGP12.phx.gbl...

Hi Paul,

!! ... hey it cant be that hard ...

Are you sure about that? What sort of Xml does Excel produce - nice clean economical stuff or reams of God-know-what?

Another alternative that might be worth investigating is to use the
XmlDocument class and do it programmatically.

Regards,
Fergus

[Dino Chiesa [Microsoft]]

An idea...
Excel is addressable as an OLEDB data source, so you can insert the data
from the dataset into Excel just by using the
System.Data.DataAdapter.Update() method, specifying the DataSet. Your
UpdateCommand should include a SQL statement that refers to a named range in
the Excel file (this is the thing the user sets up, presumably). This does
not utilize Excel automation and so should be acceptable from within a
server app.

You can create a new sheet this way, or open an existing sheet and append
data.
ps:
please do not cross-post.
--
Dino Chiesa
Microsoft Developer Division
d i n o c h @ o n l i n e . m i c r o s o f t . c o m
"Paul" <ma********@hotmail.com.nospam> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...

lol,

Well yeah the easy way is do it programmatically but I need the ability for a user to create a template file, so that they can setup the font details,
page headers etc. We used ot do it by spawning excel but it just seems to
dangerous to do this on the web server.
"Fergus Cooney" <fi******@tesco.net> wrote in message
news:%2****************@TK2MSFTNGP12.phx.gbl...

Hi Paul,

!! ... hey it cant be that hard ...

Are you sure about that? What sort of Xml does Excel produce - nice

clean

economical stuff or reams of God-know-what?

Another alternative that might be worth investigating is to use the
XmlDocument class and do it programmatically.

Regards,
Fergus

[Paul]

HI.

That is what I am trying to use at the moment, however I have found a few
problems which i cannot seem to get a define answer on..........

User defines a named area and the excel sheet hen oldedb does a update
command on the namedtange it takes the top row as the field names however
when you insert the record the fieldname is still showing in the excel
document..for example you might want the text to say "First Name" where as
the field is actuall called "first_name". If I try and name a field within
named range it complains that the field does not exist. So what I end up
with is.

FirstName LastName
Fred Blogs

Where as I need

First Name Last Name
Fred Blogs

So its just the header bits that causing the problem at the moment.

Code is below
Dim objConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=" + _
"C:\inetpub\wwwroot\excel\exceldata3.xls;Exten ded Properties=Excel 8.0;")

objConn.Open()

Try

Dim objExcelCommand As New OleDbCommand

objExcelCommand.Connection = objConn

objExcelCommand.CommandType = CommandType.TableDirect

objExcelCommand.CommandText = "NameTable"

Dim objExcelAdapter As New OleDbDataAdapter(objExcelCommand)

Dim objExcelDB = New OleDbCommandBuilder(objExcelAdapter)

Dim objExcelSet As New DataSet

Dim objNewRow As DataRow

objExcelAdapter.FillSchema(objExcelSet, SchemaType.Source, "NameTable")

objNewRow = objExcelSet.Tables("NameTable").NewRow()

objNewRow("FirstName") = "Bryan"

objNewRow("LastName") = "Slatner"

objExcelSet.Tables("NameTable").Rows.Add(objNewRow )

objExcelAdapter.Update(objExcelSet, "NameTable")

objExcelSet.Dispose()

objExcelAdapter.Dispose()

Catch ex As Exception

Response.Write(ex.Message)

Finally

objConn.Close()

End Try
Thanks
Paul

"Dino Chiesa [Microsoft]" <di****@online.microsoft.com> wrote in message
news:eD*************@TK2MSFTNGP11.phx.gbl...

An idea...
Excel is addressable as an OLEDB data source, so you can insert the data
from the dataset into Excel just by using the
System.Data.DataAdapter.Update() method, specifying the DataSet. Your
UpdateCommand should include a SQL statement that refers to a named range in the Excel file (this is the thing the user sets up, presumably). This does not utilize Excel automation and so should be acceptable from within a
server app.

You can create a new sheet this way, or open an existing sheet and append data.
ps:
please do not cross-post.
--
Dino Chiesa
Microsoft Developer Division
d i n o c h @ o n l i n e . m i c r o s o f t . c o m
"Paul" <ma********@hotmail.com.nospam> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...

lol,

Well yeah the easy way is do it programmatically but I need the ability

for

a user to create a template file, so that they can setup the font details, page headers etc. We used ot do it by spawning excel but it just seems to dangerous to do this on the web server.
"Fergus Cooney" <fi******@tesco.net> wrote in message
news:%2****************@TK2MSFTNGP12.phx.gbl...

Hi Paul,

!! ... hey it cant be that hard ...

Are you sure about that? What sort of Xml does Excel produce - nice

clean

economical stuff or reams of God-know-what?

Another alternative that might be worth investigating is to use

the XmlDocument class and do it programmatically.

Regards,
Fergus

[Dino Chiesa [Microsoft]]

I think there is something wrong with the OLEDB driver for Excel that
disallows column names that include spaces.
"Paul" <ma********@hotmail.com.nospam> wrote in message
news:uX****************@TK2MSFTNGP11.phx.gbl...

HI.

That is what I am trying to use at the moment, however I have found a few
problems which i cannot seem to get a define answer on..........

User defines a named area and the excel sheet hen oldedb does a update
command on the namedtange it takes the top row as the field names however
when you insert the record the fieldname is still showing in the excel
document..for example you might want the text to say "First Name" where as
the field is actuall called "first_name". If I try and name a field within
named range it complains that the field does not exist. So what I end up
with is.

FirstName LastName
Fred Blogs

Where as I need

First Name Last Name
Fred Blogs

So its just the header bits that causing the problem at the moment.

Code is below
Dim objConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=" + _
"C:\inetpub\wwwroot\excel\exceldata3.xls;Exten ded Properties=Excel 8.0;")

objConn.Open()

Try

Dim objExcelCommand As New OleDbCommand

objExcelCommand.Connection = objConn

objExcelCommand.CommandType = CommandType.TableDirect

objExcelCommand.CommandText = "NameTable"

Dim objExcelAdapter As New OleDbDataAdapter(objExcelCommand)

Dim objExcelDB = New OleDbCommandBuilder(objExcelAdapter)

Dim objExcelSet As New DataSet

Dim objNewRow As DataRow

objExcelAdapter.FillSchema(objExcelSet, SchemaType.Source, "NameTable")

objNewRow = objExcelSet.Tables("NameTable").NewRow()

objNewRow("FirstName") = "Bryan"

objNewRow("LastName") = "Slatner"

objExcelSet.Tables("NameTable").Rows.Add(objNewRow )

objExcelAdapter.Update(objExcelSet, "NameTable")

objExcelSet.Dispose()

objExcelAdapter.Dispose()

Catch ex As Exception

Response.Write(ex.Message)

Finally

objConn.Close()

End Try
Thanks
Paul

"Dino Chiesa [Microsoft]" <di****@online.microsoft.com> wrote in message
news:eD*************@TK2MSFTNGP11.phx.gbl...

An idea...
Excel is addressable as an OLEDB data source, so you can insert the data

from the dataset into Excel just by using the
System.Data.DataAdapter.Update() method, specifying the DataSet. Your
UpdateCommand should include a SQL statement that refers to a named range

in

the Excel file (this is the thing the user sets up, presumably). This

does

not utilize Excel automation and so should be acceptable from within a
server app.

You can create a new sheet this way, or open an existing sheet and

append

data.
ps:
please do not cross-post.
--
Dino Chiesa
Microsoft Developer Division
d i n o c h @ o n l i n e . m i c r o s o f t . c o m
"Paul" <ma********@hotmail.com.nospam> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...

lol,

Well yeah the easy way is do it programmatically but I need the

ability for

a user to create a template file, so that they can setup the font

details, page headers etc. We used ot do it by spawning excel but it just seems to dangerous to do this on the web server.
"Fergus Cooney" <fi******@tesco.net> wrote in message
news:%2****************@TK2MSFTNGP12.phx.gbl...
> Hi Paul,
>
> !! ... hey it cant be that hard ...
>
> Are you sure about that? What sort of Xml does Excel produce - nice clean
> economical stuff or reams of God-know-what?
>
> Another alternative that might be worth investigating is to use the > XmlDocument class and do it programmatically.
>
> Regards,
> Fergus
>
>

[Paul]

Yes, but what i need is the ability to name the cells without having to
create column headings as such, I guess the only real way is to get a clever
xsl sheet or butcher the saved Excel XML source, somehow look through the
namedcells collection and then try to append the new rows/cells , do a sort
then save this out ?
"Dino Chiesa [Microsoft]" <di****@online.microsoft.com> wrote in message
news:ej**************@TK2MSFTNGP09.phx.gbl...

I think there is something wrong with the OLEDB driver for Excel that
disallows column names that include spaces.
"Paul" <ma********@hotmail.com.nospam> wrote in message
news:uX****************@TK2MSFTNGP11.phx.gbl...

HI.

That is what I am trying to use at the moment, however I have found a few

problems which i cannot seem to get a define answer on..........

User defines a named area and the excel sheet hen oldedb does a update
command on the namedtange it takes the top row as the field names however when you insert the record the fieldname is still showing in the excel
document..for example you might want the text to say "First Name" where as the field is actuall called "first_name". If I try and name a field within named range it complains that the field does not exist. So what I end up
with is.

FirstName LastName
Fred Blogs

Where as I need

First Name Last Name
Fred Blogs

So its just the header bits that causing the problem at the moment.

Code is below
Dim objConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + _
"C:\inetpub\wwwroot\excel\exceldata3.xls;Exten ded Properties=Excel 8.0;")
objConn.Open()

Try

Dim objExcelCommand As New OleDbCommand

objExcelCommand.Connection = objConn

objExcelCommand.CommandType = CommandType.TableDirect

objExcelCommand.CommandText = "NameTable"

Dim objExcelAdapter As New OleDbDataAdapter(objExcelCommand)

Dim objExcelDB = New OleDbCommandBuilder(objExcelAdapter)

Dim objExcelSet As New DataSet

Dim objNewRow As DataRow

objExcelAdapter.FillSchema(objExcelSet, SchemaType.Source, "NameTable")

objNewRow = objExcelSet.Tables("NameTable").NewRow()

objNewRow("FirstName") = "Bryan"

objNewRow("LastName") = "Slatner"

objExcelSet.Tables("NameTable").Rows.Add(objNewRow )

objExcelAdapter.Update(objExcelSet, "NameTable")

objExcelSet.Dispose()

objExcelAdapter.Dispose()

Catch ex As Exception

Response.Write(ex.Message)

Finally

objConn.Close()

End Try
Thanks
Paul

"Dino Chiesa [Microsoft]" <di****@online.microsoft.com> wrote in message
news:eD*************@TK2MSFTNGP11.phx.gbl...

An idea...
Excel is addressable as an OLEDB data source, so you can insert the data from the dataset into Excel just by using the
System.Data.DataAdapter.Update() method, specifying the DataSet. Your
UpdateCommand should include a SQL statement that refers to a named range

in

the Excel file (this is the thing the user sets up, presumably). This

does

not utilize Excel automation and so should be acceptable from within a
server app.

You can create a new sheet this way, or open an existing sheet and

append

data.
ps:
please do not cross-post.
--
Dino Chiesa
Microsoft Developer Division
d i n o c h @ o n l i n e . m i c r o s o f t . c o m
"Paul" <ma********@hotmail.com.nospam> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...
> lol,
>
> Well yeah the easy way is do it programmatically but I need the

ability for
> a user to create a template file, so that they can setup the font

details,

> page headers etc. We used ot do it by spawning excel but it just

seems to

> dangerous to do this on the web server.
>
>
> "Fergus Cooney" <fi******@tesco.net> wrote in message
> news:%2****************@TK2MSFTNGP12.phx.gbl...
> > Hi Paul,
> >
> > !! ... hey it cant be that hard ...
> >
> > Are you sure about that? What sort of Xml does Excel produce -

nice

> clean
> > economical stuff or reams of God-know-what?
> >
> > Another alternative that might be worth investigating is to

use the

> > XmlDocument class and do it programmatically.
> >
> > Regards,
> > Fergus
> >
> >
>
>

[Dino Chiesa [Microsoft]]

I'm not clear on what you want
I thought you wanted to support column headers that included spaces.
But now it seems you want no column headers whatsoever.

Have you checked the hdr=NO extended attribute on the Excel OLEDB driver? It
allows you to query the fields in an Excel sheet by F1, F2, F3 (no column
headers necessary) .
http://www.google.com/search?num=30&...no+oledb+excel

Or see enclosed?

-Dino
===================
'compile with:
' vbc.exe /t:exe /debug+ /R:System.dll /R:System.Data.dll
/R:System.Xml.dll /out:.\Paul2.exe Paul2.vb
'
Imports System
Imports System.Data
Imports System.Data.OleDb

Namespace Ionic
Public Class Paul

private conn As OleDbConnection
Public shared sub Main(args as string())
Dim p as New Paul
Dim f as new string("Paul.xls")

if (args.Length<>0)
f= args(0)
end if
p.Run(f)
end sub
public sub Run(XlsFile as string)

if (XlsFile = nothing)
XlsFile= "Paul.xls"
end if

conn= New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=" & XlsFile & _
";Extended Properties=""Excel 8.0;Hdr=No;""")

conn.Open()

Dim cb as OleDbCommandBuilder
Dim da As OleDbDataAdapter
Dim ds As DataSet
Dim r As DataRow
Dim cmd As New OleDbCommand

Dim SheetName as string

Try
cmd.Connection = conn

Dim SheetList As System.Data.DataTable =
conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSc hemaGuid.Tables, New
object() { Nothing, Nothing, Nothing, "TABLE"})
' no need for a named range, we can use the sheet name.
' here, we get the name of the first (0th) sheet
SheetName= "[" & SheetList.Rows(0)("TABLE_NAME").ToString()
& "]"
System.Console.WriteLine("SheetName: " & SheetName)

' can get all fields this way:
'cmd.CommandText = "SELECT * FROM " & SheetName

' or, using Hdr=No, we can get fields by name (Fn) where
n=1,2,3,...
cmd.CommandText = "SELECT F1, F2 FROM " & SheetName
da= New OleDbDataAdapter(cmd)

ds= New DataSet
da.Fill(ds)

ds.WriteXml(System.Console.Out)

ds.Dispose()
da.Dispose()

Catch ex As Exception
System.Console.WriteLine("Exception: " & ex.ToString())
Finally
conn.Close()
End Try

End Sub

End Class

End Namespace


"Paul" <ma********@hotmail.com.nospam> wrote in message
news:uf**************@TK2MSFTNGP09.phx.gbl...

Yes, but what i need is the ability to name the cells without having to
create column headings as such, I guess the only real way is to get a clever xsl sheet or butcher the saved Excel XML source, somehow look through the
namedcells collection and then try to append the new rows/cells , do a sort then save this out ?
"Dino Chiesa [Microsoft]" <di****@online.microsoft.com> wrote in message
news:ej**************@TK2MSFTNGP09.phx.gbl...

I think there is something wrong with the OLEDB driver for Excel that
disallows column names that include spaces.
"Paul" <ma********@hotmail.com.nospam> wrote in message
news:uX****************@TK2MSFTNGP11.phx.gbl...

HI.

That is what I am trying to use at the moment, however I have found a few problems which i cannot seem to get a define answer on..........

User defines a named area and the excel sheet hen oldedb does a update
command on the namedtange it takes the top row as the field names however when you insert the record the fieldname is still showing in the excel
document..for example you might want the text to say "First Name" where

as

the field is actuall called "first_name". If I try and name a field within named range it complains that the field does not exist. So what I end

up with is.

FirstName LastName
Fred Blogs

Where as I need

First Name Last Name
Fred Blogs

So its just the header bits that causing the problem at the moment.

Code is below
Dim objConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + _
"C:\inetpub\wwwroot\excel\exceldata3.xls;Exten ded Properties=Excel 8.0;")
objConn.Open()

Try

Dim objExcelCommand As New OleDbCommand

objExcelCommand.Connection = objConn

objExcelCommand.CommandType = CommandType.TableDirect

objExcelCommand.CommandText = "NameTable"

Dim objExcelAdapter As New OleDbDataAdapter(objExcelCommand)

Dim objExcelDB = New OleDbCommandBuilder(objExcelAdapter)

Dim objExcelSet As New DataSet

Dim objNewRow As DataRow

objExcelAdapter.FillSchema(objExcelSet, SchemaType.Source, "NameTable")
objNewRow = objExcelSet.Tables("NameTable").NewRow()

objNewRow("FirstName") = "Bryan"

objNewRow("LastName") = "Slatner"

objExcelSet.Tables("NameTable").Rows.Add(objNewRow )

objExcelAdapter.Update(objExcelSet, "NameTable")

objExcelSet.Dispose()

objExcelAdapter.Dispose()

Catch ex As Exception

Response.Write(ex.Message)

Finally

objConn.Close()

End Try
Thanks
Paul

"Dino Chiesa [Microsoft]" <di****@online.microsoft.com> wrote in message news:eD*************@TK2MSFTNGP11.phx.gbl...
> An idea...
> Excel is addressable as an OLEDB data source, so you can insert the data

> from the dataset into Excel just by using the
> System.Data.DataAdapter.Update() method, specifying the DataSet.

Your > UpdateCommand should include a SQL statement that refers to a named

range

in
> the Excel file (this is the thing the user sets up, presumably). This does
> not utilize Excel automation and so should be acceptable from within a > server app.
>
> You can create a new sheet this way, or open an existing sheet and
append
> data.
>
>
> ps:
> please do not cross-post.
>
>
> --
> Dino Chiesa
> Microsoft Developer Division
> d i n o c h @ o n l i n e . m i c r o s o f t . c o m
>
>
> "Paul" <ma********@hotmail.com.nospam> wrote in message
> news:%2****************@TK2MSFTNGP09.phx.gbl...
> > lol,
> >
> > Well yeah the easy way is do it programmatically but I need the

ability

> for
> > a user to create a template file, so that they can setup the font
details,
> > page headers etc. We used ot do it by spawning excel but it just

seems to
> > dangerous to do this on the web server.
> >
> >
> > "Fergus Cooney" <fi******@tesco.net> wrote in message
> > news:%2****************@TK2MSFTNGP12.phx.gbl...
> > > Hi Paul,
> > >
> > > !! ... hey it cant be that hard ...
> > >
> > > Are you sure about that? What sort of Xml does Excel produce - nice
> > clean
> > > economical stuff or reams of God-know-what?
> > >
> > > Another alternative that might be worth investigating is to use the
> > > XmlDocument class and do it programmatically.
> > >
> > > Regards,
> > > Fergus
> > >
> > >
> >
> >
>
>