To get excel sheet NAMES, use the following - just pass in the filepath
of the excel spreadsheet:
<code>
public string[] ExcelSheetNames(String excelFile)
{
DataTable dt;
string connString = ExcelExtendedConn(excelFile);
using (OleDbConnection objConn = new
OleDbConnection(connString))
{
objConn.Open();
dt =
objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables , null);
if(dt == null)
{
return null;
}
string[] res = new string[dt.Rows.Count];
for (int i = 0; i < res.Length; i++)
{
string name = dt.Rows[i]["TABLE_NAME"].ToString();
if(name[0] == '\'')
{
//numeric sheetnames get single quotes around
them in the schema.
//remove them here
if(Regex.IsMatch(name, @"^'\d\w+\$'$"))
{
name = name.Substring(1, name.Length - 2);
}
}
res[i] = name;
}
return res;
}
}
</code>
To get the column names of the worksheet in the excel spreadsheet:
<code>
public DataTable columnNames(String excelFile, String sheetName)
{
string connString = ExcelExtendedConn(excelFile);
using (OleDbConnection conn = new
OleDbConnection(connString))
{
OleDbCommand odc = new
OleDbCommand(string.Format("Select * FROM [{0}$]", sheetName), conn);
conn.Open();
OleDbDataReader reader = odc.ExecuteReader();
DataTable sheetSchema = reader.GetSchemaTable();
return sheetSchema;
}
}
</code>
Using the methods above, you can then pull the data of the excel
spreadsheet:
<code>
public DataTable originalData(String _filename,String _sheet)
{
DataTable dt = new DataTable();
OdbcConnection conn = new OdbcConnection();
OdbcCommand cmd = new OdbcCommand();
conn.ConnectionString = string.Format(@"Driver={{Microsoft
Excel Driver (*.xls)}};DBQ={0};ReadOnly=0;", _filename);
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT * FROM [" + _sheet + "$]";
OdbcDataAdapter oda = new OdbcDataAdapter();
oda.SelectCommand = cmd;
oda.Fill(dt);
return dt;
}
</code>
I know this works because it's pulled directly from an application that
I've just written.
simida wrote:
If you want to read certain columns, you can use SELECT statements in
SQL language.
Such as,
"SELECT [Column1], [Column2] FROM [Root Levels$]"
OR, you operate it on the table which is contained in the DataSet
object.
Table.Select() method.
As far as I know, it is a easy way to read EXCEL file. :(
Hope a more elegant way to achieve it.
Sincerely,
simida
dba123 写道：
I looked at the link, seems more complex than needed to simply read an excel
file and figure out which sheets and columns to work with. what is BIFF?
There has to be an easy way to pinpoint specific worksheets in a workbook
and certain columns through .NET 2.0 in conjunction with reading and writing
to a DB table.
Yea, I could use XML and the .NET XML reader class but right now, timeis
of the essence.
--
dba123
"simida" wrote:
Another way, you can use EXCEL2003 open XLS file, and save as .XML
file. You will process XML file more easy in .NET 2.0.
>
You can also anaylse EXCEL file format to read data firstly. More
information about EXCEL file format, plz refer to this.
>
http://chicago.sourceforge.net/devel/docs/excel/
>
Sincerely,
simida
>
dba123 写道：
>
Thanks. What about reading the worksheet, how to pick out a certain column,
and pass that as a stream or something to a s tored procedure whichinserts
the data from the worksheet?
Here's my code so far:
// Insert Root Categories
string strConn;
strConn = "Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=C:\\test.xls; Extended Properties=Excel 8.0;";
//You must use the $ after the object you reference in the
spreadsheet
OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM
[Root Levels$]", strConn);
DataSet myDataSet = new DataSet();
myCommand.Fill(myDataSet, "ExcelInfo");
//Now, using stored proc, insert the data (Specific column in the dataset)
into database table.
Also, is there a better way or new way in .NET 2.0 to read an excelworksheet?
OR can I somehow create a stream then stream each row from the excel sheet
directly to my SQL Table?
--
dba123
"simida" wrote:
Maybe System.Data.OleDb or ODBC will help you. I used OleDb to connect
EXCEL Worksheet.
>
connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=\"Excel
5.0;HDR=yes\";Data Source=test.xls;";
>
You can also use MSSQL Tools about Import/Export data Wizard to import
your worksheet data into DB.
>
Sincerely,
simida
>
dba123 写道：
>
I need some help and direction on what classes and an example or two
(article) on how to read an Excel Worksheet and insert one column into a
database table column. I am using .NET 2.0 only. What namespaces and
classes should I use and how?
--
dba123
>
>
