Using ADO.Net to Create and Alter an Excel File...

  • Thread starter Thread starter JJDoherty
  • Start date Start date
J

JJDoherty

Hi All,

I've posted this before but unfortunately I have found a solution yet
so I am hoping that someone can help.

What I wish to do is take an excel file from an end user, copy and
rename it as "backup of...". Then I want to take the original file,
delete it and create a new blank copy of the file with the same
structure as the "backup of..." file with 2 to 3 additional columns.

Next I want to use DataReader to read the data from the "backup of..."
file, as the file may be to big to use DataSet, and "INSERT" the data
into the blank file using ADO along with additional data which will
occupy the new additional columns.

So how do I build the string I need to create the blank excel file
using only ADO? I cannot guarantee that excel is installed on the PC
running the program so this is the reason that
ADO is the prefered option. I would expect to use GetTableSchema or
similar and loop through this to get the field names, sizes and types
but is there something I am missing. I am using VB.Net for this as
well.

At this stage any help would be very, very muchly appreciated.
 
Hi there,

Theres an absolute ton of info is you just take a couple of seconds to
Google for it. Thats probably why no one has responded.
Use search terms like "ADO.Net excel interop" or "ADO.Net excel programming"
or "ADO.Net microsoft office".
Even Microsoft have screens of info about this topic. You basically just
want to use FileIo for the first block of questions.

You could use a code block as below to connect to the spreadsheet:

#Region "Function : OpenExcelConnection(ByVal fieldList As String, ByVal
sorting As String) As OleDb.OleDbCommand "
'
===============================================================================
' Purpose :
'
===============================================================================
Friend Function OpenExcelConnection(ByVal fieldList As String, ByVal sorting
As String) As OleDb.OleDbCommand
Dim stext As String = String.Format("SELECT {0} FROM [{1}$] {2}", fieldList,
_sheetName, sorting)
Dim cs As String = CONNSTR.Replace("@@@@", _filePath)
Dim cmd As New OleDb.OleDbCommand(stext, New OleDb.OleDbConnection(cs))
cmd.Connection.Open()
Return cmd
End Function
#End Region

where the connection string is something like:
Private Const CONNSTR As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=@@@@;Extended Properties=""Excel 8.0;HDR=YES;"""
and then creating and accessing excel files is all done through the Excel
object model about which you can find info here:

http://msdn2.microsoft.com/en-us/library/aa168292(office.11).aspx

Your problem has been solved a zillion times before so there is alot of info
out there on it... just Google it.

hth
Richard
 
Hi Richard,

Thanks for the reply. I am aware that there is plenty of information
regarding excel and using the object within VB.Net to perform what I
require however as you may have missed in my original post I am unable
to use the excel object as I have no way of knowing or indeed forcing
the user to install Excel on their PC. Therefore this must be an
ADO.net solution. I am able to open the excel file using the
Microsoft.Jet.OLEDB driver etc, but my question is how do I obtain the
structure of the excel file to create a new blank table which I will
then use to insert records one by one to, from the original file.

The excel object works very well as that is what has been used up to
this point, so it is a pure ADO.Net solution that I am seeking and
which, so far, I have been unable to find through a search through
Google or in reply to any questions that I have posted.

Regards.
 
¤ Hi All,
¤
¤ I've posted this before but unfortunately I have found a solution yet
¤ so I am hoping that someone can help.
¤
¤ What I wish to do is take an excel file from an end user, copy and
¤ rename it as "backup of...". Then I want to take the original file,
¤ delete it and create a new blank copy of the file with the same
¤ structure as the "backup of..." file with 2 to 3 additional columns.
¤
¤ Next I want to use DataReader to read the data from the "backup of..."
¤ file, as the file may be to big to use DataSet, and "INSERT" the data
¤ into the blank file using ADO along with additional data which will
¤ occupy the new additional columns.
¤
¤ So how do I build the string I need to create the blank excel file
¤ using only ADO? I cannot guarantee that excel is installed on the PC
¤ running the program so this is the reason that
¤ ADO is the prefered option. I would expect to use GetTableSchema or
¤ similar and loop through this to get the field names, sizes and types
¤ but is there something I am missing. I am using VB.Net for this as
¤ well.
¤
¤ At this stage any help would be very, very muchly appreciated.

Is it OK if the Excel Workbook contains one or more Worksheets?

Something to keep in mind, Excel doesn't support all the features of the more common database
products that are available so there are some limitations.


Paul
~~~~
Microsoft MVP (Visual Basic)
 
Hi Paul,

Yes it is possible that the excel file may contain more than one
worksheet, although it is expected that this will not be the case
however it is perfectly acceptable that the newly created exel file
only contains the one worksheet that is involved in the process. The
backup will hold the original data and any additional worksheets
anyway.

Thanks
 
¤ Hi Paul,
¤
¤ Yes it is possible that the excel file may contain more than one
¤ worksheet, although it is expected that this will not be the case
¤ however it is perfectly acceptable that the newly created exel file
¤ only contains the one worksheet that is involved in the process. The
¤ backup will hold the original data and any additional worksheets
¤ anyway.

The following method works for me, although you do need to create a Worksheet or the Workbook file
will not be created:

Dim ConnectionString As String

ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Test Files\ExcelWB.xls;Extended Properties=Excel 8.0"

Dim ExcelConnection As New System.Data.OleDb.OleDbConnection(ConnectionString)
ExcelConnection.Open()

Dim SQLDDLCommand As String = "CREATE TABLE tblCustomers " & _
"(CustomerID INTEGER, " & _
"[Last Name] TEXT(50), " & _
"[First Name] TEXT(50), " & _
"Phone TEXT(10), " & _
"Email TEXT(50))"

Dim ExcelCommand As New System.Data.OleDb.OleDbCommand(SQLDDLCommand, ExcelConnection)
ExcelCommand.ExecuteNonQuery()

ExcelConnection.Close()


Paul
~~~~
Microsoft MVP (Visual Basic)
 
Hi There,

I dont see how you can create an "blank" excel file if Excel is not
installed on the users system?
I thought Excel has a proprietary format that can only be programmatically
created via the object model?

Either way I dont quite understand how you are getting Excel files from a
user if they dont have Excel??
If they are in turn just sending them to you then the only requirement is
that you have Excel installed on your system?

Can you not just create a Csv? They are directly supported by Excel.

To extract the data, the function (OpenExcelConnection) i posted is just
ADO.Net. No object model in there
..
For table schema i just Googled the phrase "Get excel table schema using
ADO.net" and the top reply
was
http://www.simple-talk.com/dotnet/.net-framework/schema-and-metadata-retrieval-using-ado.net/
which has the following info:

=====================================================
Why use ADO.NET 2.0 to retrieve metadata?

How does using ADO.NET 2.0 to retrieve metadata differ from using SQL
Management Objects (SMO) or SQL Distributed Management Objects (SQL-DMO)?
The schema and metadata retrieval methods in the ADO.NET layer are used to
retrieve read-only information; they cannot be used to manipulate the
server. SMO or SQL-DMO, on the other hand, lets you manipulate the database
server at an administrative level.

One advantage of the schema retrieval methods in the ADO.NET layer is that
they are similar for other data sources as well. We can easily retrieve the
worksheets in an Excel workbook, for example, using the same schema
retrieval methods available in the OleDb data providers in ADO.NET:

C# Code:

System.Data.OleDb.OleDbConnection con = new
System.Data.OleDb.OleDbConnection(
@"Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=C:\Sample.xls; Extended Properties=""Excel 8.0;
HDR=Yes;IMEX=1""");con.Open();System.Data.DataTable tbl =
con.GetSchema(System.Data.OleDb.
OleDbMetaDataCollectionNames.Tables);con.Close();foreach
(System.Data.DataRow row in
tbl.Rows){Console.WriteLine(row["table_name"]);}======================================================



Richard
 
Hi Paul,

Thanks very much for the help. I am also able to create such a file.
The problem I have is creating the string assigned to the
SQLDLCommand. I wish to create this string from the structure of an
existing excel file. I have previously attempted to use GetTableSchema
to locate this information but the results returned have not contained
the correct file types.

I am starting to believe that this sort of information gathering and
manipulation of an excel file is simply not possible using ADO.Net.

Once again, thank you for your help.
 
¤ Hi Paul,
¤
¤ Thanks very much for the help. I am also able to create such a file.
¤ The problem I have is creating the string assigned to the
¤ SQLDLCommand. I wish to create this string from the structure of an
¤ existing excel file. I have previously attempted to use GetTableSchema
¤ to locate this information but the results returned have not contained
¤ the correct file types.
¤
¤ I am starting to believe that this sort of information gathering and
¤ manipulation of an excel file is simply not possible using ADO.Net.

How about using GetOleDbSchemaTable instead of GetTableSchema?

The only problem I see here is that you will probably need to perform some mapping between the data
type values returned and the DDL. GetOleDbSchemaTable returns values for the DATA_TYPE that
correspond to the System.Data.OleDb.OleDbType enumerated constants.


Paul
~~~~
Microsoft MVP (Visual Basic)
 
Paul,

Thanks once again for all your help. Thinking that I will research the
OleDBTableSchema approach a bit more but otherwise I will go with the
Excel object if ultimately it proves to be the easiest way forward for
this and not having unlimited time either. Forcing the user to have an
installed version of Excel isn't ideal by any mean but again if it is
the easiest way forward then I suppose, if I can't get an ADO.net
solution, I have no choice.

If I do manage to find a way forward using ADO.net I will of course
post that solution. Once again thank you.
 
I couldn't figure out how to do it with OleDb, but here's the Odbc
solution:

"Driver={Microsoft Excel Driver
(*.xls)};CREATE_DB=filepath.xls;DBQ=filepath.xls;DSN='';FIRSTROWHASNAMES=1;READONLY=FALSE"

On Windows Server 2003 this will create an XLS file that comes pre-
loaded with Sheet1, Sheet2, and Sheet3 - which isn't necessarily
desired. I've just run new code using this on my Vista box and it only
had a sheet for the table that I created (using CREATE TABLE...)
 
Back
Top