Overwriting existing data in an Excel worksheet using ADO.Net

[Joel Gordon]

Hi,

I'm wanting to write an in memory ADO.Net Datatable to a
worksheet
in an Excel spreadsheet. If the worksheet exists I would like to
overwrite all the existing data in the worksheet.

I've successfully used the Jet OLE DB Provider to create a new Excel
worksheet (using a CREATE TABLE) and populate it. But I haven't been
able to overwrite data in an existing worksheet.

In an old posting (Oct 2002) to microsoft.public.data.ado (Subject:
Delete records in Excel with ADO from VB) it was mentioned that Excel
automation is needed to delete empty rows cleanly. But it also
mentioned
that you can execute a DROP TABLE and start over.

I have tried using DROP TABLE which deletes all the data in a worksheet,
however it also seems to delete the column headers which means when I
use the InsertCommand on an OleDbDataAdapter to write the dataTable to
Excel no data gets written.

I was hoping DROP TABLE would actually delete the worksheet (rather than
just it's contents) so that I could then re-create the worksheet (using
CREATE TABLE).

Question :
------------
Am I doing something wrong with my InsertCommand, or is there some way
I can delete the worksheet itself or delete the contents of the
worksheet and still leave the column headers intact ?

I would like to avoid using Excel automation if possible, since if I
don't use it Excel does not need to be installed, versioning issues can
be avoided and I presume that using the Jet OLE DB Provider is faster
than using automation.

Thanks in advance,
Joel Gordon.

[Joel Gordon]

[Peter Huang [MSFT]]

Hi

Because Excel workbook did not have primary key, so the DELETE command is
not support.
Based on my test, after we call the drop table to delete the content from
the worksheet, we still can call the create table to recreate table.
private void button1_Click(object sender, System.EventArgs e)
{
// Establish a connection to the data source.
System.Data.OleDb.OleDbConnection objConn = new
System.Data.OleDb.OleDbConnection(
@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Book1.xls;Extended
Properties=Excel 8.0;Persist Security Info=False");
objConn.Open();

// Add two records to the table named 'MyTable'.
System.Data.OleDb.OleDbCommand objCmd = new
System.Data.OleDb.OleDbCommand();
objCmd.Connection = objConn;

objCmd.CommandText = "CREATE TABLE Test(x INT,y varchar(50))";
objCmd.ExecuteNonQuery();
objCmd.CommandText = "INSERT INTO Test VALUES (2,'Hello')";
objCmd.ExecuteNonQuery();
objCmd.CommandText = "INSERT INTO Test VALUES (3,'Test')";
objCmd.ExecuteNonQuery();

objCmd.CommandText = "drop table Test";
objCmd.ExecuteNonQuery();

objCmd.CommandText = "CREATE TABLE Test(x INT,y varchar(50))";
objCmd.ExecuteNonQuery();
objCmd.CommandText = "INSERT INTO Test VALUES (4,'Hello')";
objCmd.ExecuteNonQuery();
objCmd.CommandText = "INSERT INTO Test VALUES (5,'Test')";
objCmd.ExecuteNonQuery();

// Close the connection.
objConn.Close();
}

You may take a look and let me know the result.

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

[Peter Huang [MSFT]]

Hi

I reviewed the thread and find that there is a similar issue in the
newsgroup below.Now I have replied to you, you may go and take a look.
Subject: Overwriting existing data in an Excel worksheet using ADO.Net
Newsgroups: microsoft.public.excel.programming

[Jamie Collins]

"Joel Gordon" <joel....@forestresearch.co.nz> wrote ...

> I've successfully used the Jet OLE DB Provider to create a new Excel
> worksheet (using a CREATE TABLE)

Not quite correct. You cannot directly create a worksheet using the
OLE DB provider or Excel ODBC driver e.g. (the $ suffix indicates the
Excel 'table' is a worksheet):

CREATE TABLE [Sheet99$] (Col1 FLOAT);

will generate an error, ' 'Sheet99$' is not a valid name because the $
character is illegal in the name for a 'named range'.

You can only directly create a 'named range' (technically a workbook
level defined Name that is defined using a simple formula that returns
a Range object). Try a legal name e.g.

CREATE TABLE Sheet99 (Col1 FLOAT);

and it works without error assuming the a 'named range' does not
already exist. The provider/driver will create a new worksheet to host
the new 'named range', unless a worksheet of the same name already
exists and is unused. If the sheet exists and is 'used' the new sheet
will be given a name derived from the 'named range' name e.g.
Sheet991.

So you can create a worksheet using the provider/driver but only as a
by-product of creating a 'named range' and then only if the
circumstances are favorable.

You *can* use the DROP syntax with a worksheet e.g.

DROP TABLE [Sheet99$];

This will clear the worksheet including the column headers but it will
not remove the worksheet nor any 'named range' of the same name - the
latter will be redefined to refer to the range in the top left corner
e.g. =Sheet99!$A$1:$A$1. Because the 'named range' is retained, a
subsequent CREATE TABLE using the same table name will fail.

You can also use the DROP syntax with a 'named range' e.g.

DROP TABLE [Sheet99];

this will do the same as DROP on the worksheet name but additionally
remove the defined Name, allowing a subsequent CREATE TABLE to work
and be created on the same worksheet.

Jamie.

--

[Jamie Collins]

v-ph...@online.microsoft.com ("Peter Huang" [MSFT]) wrote ...

> Based on my test, after we call the drop table to delete the content from
> the worksheet, we still can call the create table to recreate table

> objCmd.CommandText = "CREATE TABLE Test(x INT,y varchar(50))";
> objCmd.ExecuteNonQuery();
> objCmd.CommandText = "INSERT INTO Test VALUES (2,'Hello')";
> objCmd.ExecuteNonQuery();
> objCmd.CommandText = "INSERT INTO Test VALUES (3,'Test')";
> objCmd.ExecuteNonQuery();
>
> objCmd.CommandText = "drop table Test";
> objCmd.ExecuteNonQuery();
>
> objCmd.CommandText = "CREATE TABLE Test(x INT,y varchar(50))";
> objCmd.ExecuteNonQuery();
> objCmd.CommandText = "INSERT INTO Test VALUES (4,'Hello')";
> objCmd.ExecuteNonQuery();
> objCmd.CommandText = "INSERT INTO Test VALUES (5,'Test')";
> objCmd.ExecuteNonQuery();

The OP is referring to a *worksheet* as a table e.g.

SELECT * FROM [Sheet1$];

The Excel table you are creating with your code is not a worksheet,
rather you are specifying a 'named range' (workbook level defined Name
objected defined using a simple formula that returns a Range object)
e.g.

SELECT * FROM Sheet1;

This is not the same as a worksheet.

Try you code using the name MyTable$ (the $ character is used to
signify a worksheet name to distinguish it from a defined Name of the
same name) and you will find your CREATE TABLE does not work.

> CREATE TABLE Test(x INT,y varchar(50))";

Excel does not support Jet's INTEGER data type. The only 'true'
numeric data type (as distinct from CURRENCY and DATETIME) that Excel
supports is Double (FLOAT). Examine the schema of your Excel table
created using your code and you will see its data type has been mapped
to FLOAT.

> Because Excel workbook did not have primary key, so the DELETE command is
> not support.

That is not the reason. I can use DELETE on a Jet (MS Access) table
with no primary key e.g.

CREATE TABLE NoPK (x INT,y varchar(50));
INSERT INTO NoPK VALUES (2,'Hello');
INSERT INTO NoPK VALUES (3,'Test');
DELETE FROM NoPK WHERE x = 3;

The above works for a Jet (MS Access) table with no primary key.

DELETE is not supported in Excel for other reasons. Unlike a regular
Jet database table, relative row order and absolute row position
matters a lot in Excel e.g. to calculation chains of dependent cell
formulas. How would you propose DELETE could work with Excel,
specifically deleting a row mid table? Shift up the rows below and
break the calculation chains? Null the values in the row, leaving gaps
in the table that cannot be reused?

Jamie.

--

[Joel Gordon]

Jamie Collins wrote:

> "Joel Gordon" <joel....@forestresearch.co.nz> wrote ...
>
> > I've successfully used the Jet OLE DB Provider to create a new Excel
> > worksheet (using a CREATE TABLE)
>
> Not quite correct. You cannot directly create a worksheet using the
> OLE DB provider or Excel ODBC driver e.g. (the $ suffix indicates the
> Excel 'table' is a worksheet):
>
> CREATE TABLE [Sheet99$] (Col1 FLOAT);
>
> will generate an error, ' 'Sheet99$' is not a valid name because the $
> character is illegal in the name for a 'named range'.
>
> You can only directly create a 'named range' (technically a workbook
> level defined Name that is defined using a simple formula that returns
> a Range object). Try a legal name e.g.
>
> CREATE TABLE Sheet99 (Col1 FLOAT);
>
> and it works without error assuming the a 'named range' does not
> already exist. The provider/driver will create a new worksheet to host
> the new 'named range', unless a worksheet of the same name already
> exists and is unused. If the sheet exists and is 'used' the new sheet
> will be given a name derived from the 'named range' name e.g.
> Sheet991.
>
> So you can create a worksheet using the provider/driver but only as a
> by-product of creating a 'named range' and then only if the
> circumstances are favorable.
>

> You can use the DROP syntax with a worksheet e.g.

>
> DROP TABLE [Sheet99$];
>
> This will clear the worksheet including the column headers but it will
> not remove the worksheet nor any 'named range' of the same name - the
> latter will be redefined to refer to the range in the top left corner
> e.g. =Sheet99!$A$1:$A$1. Because the 'named range' is retained, a
> subsequent CREATE TABLE using the same table name will fail.
>
> You can also use the DROP syntax with a 'named range' e.g.
>
> DROP TABLE [Sheet99];
>
> this will do the same as DROP on the worksheet name but additionally
> remove the defined Name, allowing a subsequent CREATE TABLE to work
> and be created on the same worksheet.
>
> Jamie.
>
> --

Hi Jamie,

Thanks for the reply and for clarifying worksheets and named ranges
- I certainly had not understood the differences.

I agree with how you have described the behaviour regarding creating
tables and how this relates to the creation of worksheets and named
ranges BUT...

When I try to drop the table with a 'named range' it does NOT seem to
remove the defined name, hence a subsequent CREATE TABLE does NOT work
- I get an OleDbException stating that the table already exists.

Following is the code I am using. The first time the code is run (and
the excel file does not exist) it runs fine. If it is run again then
the drop table leaves the named range alone and hence the create table
fails.

I am using Microsoft Office Excel 2003 (11.6355.6360).

Am I doing something wrong ?

Thanks,
Joel.

internal static void WriteToExcelFile( DataTable dataTable,
string excelFileName ) {
OleDbConnection connection =
new OleDbConnection( "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + excelFileName + ";" +
"Extended Properties=\"Excel 8.0;HDR=Yes\"" );
if ( File.Exists( excelFileName ) ) {
connection.Open();
if ( TableExists( connection, dataTable.TableName ) ) {
DeleteTable( connection, dataTable.TableName );
}
connection.Close();
}
WriteDataTable( dataTable, connection, excelFileName );
}

private static bool TableExists( OleDbConnection connection,
string tableName ) {
DataTable table =
connection.GetOleDbSchemaTable( OleDbSchemaGuid.Tables,
new object[] {null, null, null, "TABLE"} );
foreach( DataRow dataRow in table.Rows ) {
if ( ((string)dataRow["TABLE_NAME"]) == tableName ) {
return true;
}
}
return false;
}

private static void DeleteTable( OleDbConnection connection,
string tableName ) {
OleDbCommand command =
new OleDbCommand( "DROP TABLE [" + tableName + "]",
connection, null );
command.ExecuteNonQuery();
}

private static void WriteDataTable( DataTable dataTable,
OleDbConnection connection,
string fileName ) {
connection.Open();
try {
// Create workbook with appropriate column headers
OleDbCommand command = new OleDbCommand(
GetCreateString( dataTable ), connection, null );
command.ExecuteNonQuery();

// Populate worksheet with all data from the dataTable
OleDbDataAdapter adapter =
new OleDbDataAdapter("SELECT * FROM
["+dataTable.TableName+"]",
connection );
OleDbCommandBuilder builder = new OleDbCommandBuilder(adapter);
builder.QuotePrefix = "[";
builder.QuoteSuffix = "]";
adapter.Update( dataTable );
} finally {
connection.Close();
}
}

private static string GetCreateString( DataTable table ) {
string createString = "CREATE TABLE [" + table.TableName + "](\n";
foreach( DataColumn column in table.Columns ){
createString += "[" + column.ColumnName + "] ";
switch( column.DataType.ToString() ){
case "System.String":
createString += "TEXT";
break;
case "System.Int32":
createString += "INT";
break;
case "System.Double":
case "System.Single":
createString += "FLOAT";
break;
case "System.DateTime":
createString += "DATETIME";
break;
default:
throw new ApplicationException( "Unknown type." );
createString += ",\n";
}
createString = createString.TrimEnd( new char[]{'\n',','} );
createString += ");";
return createString;
}

[[MSFT]]

Yes, Delete is not supported in JET for Excel file and Text file. They are
different from Access database file for storage. As you said, row
position/order matter this

For the original question, I think the best pratice is to drop the table
and create it again.

Luke

[Jamie Collins]

"Joel Gordon" <joel....@forestresearch.co.nz> wrote ...

> The first time the code is run (and

> the excel file does not exist) it runs fine. If it is run again then
> the drop table leaves the named range alone and hence the create table
> fails.

I don't think that is correct. I think your code is failing to call
its DeleteTable method on the second pass because it wrongly assumes
the table doesn't exist.

You are using GetOleDbSchemaTable to detect whether the table exists
and only try to drop the table if TableExists returns true. However, a
'named range' that contains no data rows does not show up as a table
in the schema (non-.NET readers should note that the same behaviour
results when using GetOpenSchema in ADO 'classic'). Regardless, the
table may still be used and so, of course, may be dropped.

The approach I usually take is to try to DROP the table anyhow: if it
exists then it gets dropped; if it doesn't exist I ignore the error.

Jamie.

--

[Joel Gordon]

Jamie Collins wrote:

Thanks Jamie - you are correct. I have changed my code to always attempt to
DROP the table. Unfortunately when I attempt to write the data table to a
workbook which has had a successful DROP table done then I get the exception :

System.InvalidOperationException: Dynamic SQL generation for the UpdateCommand
is not supported against a SelectCommand that does not return any key column
information.

A complete example of my code follows. Creation of a new excel file works fine
but attempting to overwrite existing data fails with the exception above.

What am I doing wrong?

Thanks again,
Joel.

using System;
using System.IO;
using System.Data;
using System.Data.OleDb;

public class TestWriteToExcel {

const string EXCEL_FILENAME = @"C:\TestWorkBook.xls";

public static void Main() {
try {
if ( File.Exists( EXCEL_FILENAME ) ) {
File.Delete( EXCEL_FILENAME );
}
DataTable dataTable = CreateDataTable();
dataTable.TableName = "TestWorkSheet";
WriteToExcelFile( dataTable, EXCEL_FILENAME );
dataTable.Rows[0][0] = 999;
WriteToExcelFile( dataTable, EXCEL_FILENAME );
} catch ( Exception e ) {
Console.WriteLine( e );
}
}

private static DataTable CreateDataTable() {
DataTable dataTable = new DataTable();
dataTable.Columns.Add( "IntColumn", typeof(Int32) );
for( int i=1; i<4; i++ ) {
DataRow row = dataTable.NewRow();
row[0] = i;
dataTable.Rows.Add( row );
}
return dataTable;

}

static void WriteToExcelFile( DataTable dataTable,
string excelFileName ) {
OleDbConnection connection =
new OleDbConnection( "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + excelFileName + ";" +
"Extended Properties=\"Excel 8.0;HDR=Yes\"" );

connection.Open();
try {
// Clear worksheet
DeleteTable( connection, dataTable.TableName );

// Create worksheet
OleDbCommand command = new OleDbCommand( GetCreateString(dataTable),
connection, null );
command.ExecuteNonQuery();

// Populate worksheet all data from dataTable

OleDbDataAdapter adapter = new OleDbDataAdapter(

"SELECT * FROM [" + dataTable.TableName + "]", connection );

OleDbCommandBuilder builder = new OleDbCommandBuilder( adapter );
builder.QuotePrefix = "[";
builder.QuoteSuffix = "]";
adapter.Update( dataTable );
} finally {
connection.Close();
}
}

static void DeleteTable( OleDbConnection connection, string tableName ) {
try {

OleDbCommand command =
new OleDbCommand( "DROP TABLE [" + tableName + "]",
connection, null );
command.ExecuteNonQuery();

Console.WriteLine( "Table " + tableName + " dropped." );
} catch (Exception) {
;

[onedaywhen]

Joel Gordon wrote:

> I have changed my code to always attempt to
> DROP the table. Unfortunately when I attempt to write the data table
to a
> workbook which has had a successful DROP table done then I get the
exception :
>
> System.InvalidOperationException: Dynamic SQL generation for the
UpdateCommand
> is not supported against a SelectCommand that does not return any key
column
> information.

See:

PRB: Error Occurs When You Use ADO.NET OLEDbDataAdapter to Modify Excel
Workbook
http://support.microsoft.com/default.aspx?scid=kb;en-us;316756

Another 'resolution' to the problem us to write the dynamic SQL
yourself e.g. using Jet's INSERT INTO..SELECT syntax, perhaps using an
ODBC connection to the data source. Old technology yes, but then so is
Excel <g>.

Jamie.

--