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.

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]) 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.

--

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