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
> > >
> > >
> >
> >
>
>
