noam
7/17/2002 7:01:00 PM
I am using odbc.net to read in data from several different external file
types, including Excel (2000).
When I try to load excel data I supply the name of the worksheet as the
table name, and fill the dataset based on that worksheet. (See the code
snippet below.) If I know that name of the worksheet - say "Sheet1" then I
affix a "$" to signify that it's a worksheet and everything works well.
However, if I don't know the name of the worksheet - and 99% of the time it
most definitely is NOT "Sheet1$" - I have a problem. I'm not using
automation, so I can't reference the worksheet by requesting Worksheets[0],
for example. I tried also using CurrentRegion and CurrentRange, but it
seems that they are properties accessible only through automation.
1. Assuming there is only 1 worksheet, with only 1 region (or range), does
anyone know how I can access that single worksheet using ODBC.NET (or ODBC
at all for that matter) without knowing the worksheet's user-defined name?
2. Does the excel driver support some pre-defined naming convention for the
worksheets based on index?
3. Is it possible, alternatively, to query for a list of worksheets in a
spreadsheet file?
4. Is my assumption that CurrentRegion and CurrentRange are properties
available onlt with automation correct, or can I use these somehow with
odbc?
The following code snippet works correctly:
//---------------------------------------------------------------------
dataDriver = "{Microsoft Excel Driver (*.xls)}";
dataDataBase = fi.FullName; // file name of the requested file
dataTableName = "Sheet1$"; // PROBLEM IS HERE
OdbcConnection conn = new OdbcConnection( getConnStr() );
string select = "SELECT * FROM [" + dataTableName + "]";
OdbcCommand cmd = new OdbcCommand( select, conn);
OdbcDataAdapter da = new OdbcDataAdapter();
da.SelectCommand = cmd;
DataSet ds = new DataSet();
DataTable tbl = ds.Tables.Add();
da.Fill( tbl );
//---------------------------------------------------------------------
ANY help is greatly appreciated!
I have a very tight deadline, resting partly on the solution to this
problem.
Thanks in advance,
Noam
noamr@britannica-ks.com