Excel Conversion

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I currently have a prototype application that uses Excel spreadsheets that
are used as data entry forms to load a MS Access database. I would like to
know how to perform the following: 1. Create a process that would save the
Excel spreadsheet data in a XML format that would then be read by a middle
tier XML service that would then load the data into the MS Access database.
2. Convert the Excel spreadsheets into Web pages and then: 2a. Be bound to
the MS Access database via ADO.NET. or 2b. Use a XML service to view and
update the MS Access database. 3. Create a process that will re-convert
changes to the Excel spreadsheets to Web pages and if necessary rebind the
fields to the MS Access database.
 
If you open it with the Jet drivers and query it using Jet-SQL with ADO.Net
then you'll have a Dataset which is your XML.

Excel is an ISAM so you can work with it using Jet as middleware just like
you can do with xBase or Lotus or Text or HTML ISAMs.

Do what you want with the XML Dataset including connecting to an actaul Jet
file ("Access" data file) and iterating through the Dataset to create
Inserts for the Jet file (or dynamically link the Excel sheet right to the
Jet file and use Jet-SQL Insert Into statements for a fast table creation).

Back and forth, vise versa.

Example to consider, this loads the sheet values into a grid ... because the
values are in a Dataset (XML)

The HDR=Yes in the connection string tells the provider that the first row
of the sheet has values that are used as "Column Names", if your sheet
doesn't have that then don't say Yes :)

Imports System.Data.Oledb
......

Dim cn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\mytests\book1.xls;" & _
"Extended Properties=""Excel 8.0;HDR=Yes""")

Dim ds As New DataSet

Dim da As New OleDbDataAdapter("Select * from [Sheet1$]", cn)
cn.Open()
Try
da.Fill(ds)
MsgBox(ds.Tables.Count)
MsgBox(ds.Tables(0).Rows.Count)

grid1.DataSource = ds.Tables(0)

Catch ex As Exception
MsgBox(ex.ToString)
End Try


Note the special bracket-$ syntax for sheetnames. Named ranges can also be
called with slightly different syntax and you can query using specific
"column names" sortings, pivots and most all other options that are
supported by Jet-SQL.
Look to Office Developer Help and related resources for more information on
using ISAMs via Jet.

Hope that helps.

Robert Smith
Kirkland, WA
www.smithvoice.com
 
Back
Top