ADO.NET - Excel and Column header..

  • Thread starter Thread starter poochi
  • Start date Start date
P

poochi

I am trying to create an excel file using ADO.NET (Can not use Excel
COM interop classes). Everything seems to be working fine except the
column header of the table sheet1 is also included in the output file.
Setting the HDR=NO doesn't seem to fix the problem. Could some one
please tell me how to turn off the column header creation (created in
the first row of the sheet)?

Here is the sample code..

==================================================
OleDbConnection objConn = null;
try
{
string sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=D:\\temp\\Book7.xls;Extended Properties=\"Excel
8.0;HDR=NO\"";

objConn = new OleDbConnection(sConnectionString);
objConn.Open();
// Create worksheet
OleDbCommand objCmd = new OleDbCommand();
objCmd.Connection = objConn;
// ======================== here is the problem
// Whatever i put in the column name unloaded in the first row of the
Excel file
// Is there a way to turn it off?
objCmd.CommandText = "Create Table Sheet1 ([A$] char(255), [B$]
char(255))";
objCmd.ExecuteNonQuery();
objCmd.CommandText = "Insert into Sheet1 " +
" values ('Bill', 'Brown')";
objCmd.ExecuteNonQuery();
objCmd.CommandText = "Insert into Sheet1 " +
" values ('Joe', 'Thomas')";
objCmd.ExecuteNonQuery();

}
catch(Exception e)
{
System.Console.WriteLine(e.Message);
}
finally
{
//Close the connection.
if (objConn != null)
objConn.Close();
}
=======================================================

Could someone please help me solve the problem?

Thanks,
poochi
 
¤ I am trying to create an excel file using ADO.NET (Can not use Excel
¤ COM interop classes). Everything seems to be working fine except the
¤ column header of the table sheet1 is also included in the output file.
¤ Setting the HDR=NO doesn't seem to fix the problem. Could some one
¤ please tell me how to turn off the column header creation (created in
¤ the first row of the sheet)?
¤

AFAIK there is no way to avoid adding the column headers when creating the Worksheet using the ISAM
or ODBC driver. You would need to use automation to do this or start out with an existing blank
Worksheet.


Paul
~~~~
Microsoft MVP (Visual Basic)
 
Thank you somuch for the reply Paul. hmm.. I wanted to avoid COM
interop. It seems that is best option available to me. Thanks again..
 
Thank you somuch for the reply Paul. hmm.. I wanted to avoid COM
interop. It seems that is best option available to me right now. The
problem is the user may not have Excel installed in their machine. Is
there any other way to create Excel? if not, could someone please tell
me what are the minimum files needed to use Excel interop in client
machine (if the user does not have excel in their machine) besides
Excel interop dll?

Thanks again.
 
¤ Thank you somuch for the reply Paul. hmm.. I wanted to avoid COM
¤ interop. It seems that is best option available to me right now. The
¤ problem is the user may not have Excel installed in their machine. Is
¤ there any other way to create Excel? if not, could someone please tell
¤ me what are the minimum files needed to use Excel interop in client
¤ machine (if the user does not have excel in their machine) besides
¤ Excel interop dll?
¤
¤ Thanks again.

For automation/interop with Excel the application needs to be installed. I'm not aware of any way
around this.


Paul
~~~~
Microsoft MVP (Visual Basic)
 
Back
Top