How can I do this "select * from [sheet1$] where column is L" in Excel using OLEDB for C#?
I had asked about this question before but since the requirements changed, I am going to seek for answers again.
I am trying to get all of the contents within the L column of Excel or anything under the DocumentNo heading, which by t开发者_如何学Che way is placed 7 cells below the first row. So the DocumentNo data is at L:7. It is followed by a blank cell, then cells of document numbers. I want to get all of the document numbers and place it in an array to be used in succeeding functions.
Help on this please. Thanks.
You can do this using a range modifier to tell it where to go
For example if you want just L column in your example
SELECT DocumentNo
FROM [sheet1$L7:Ll40]
This is assuming that the document numbers end at L140.
try
{
//Create a OLEDB connection for Excel file
string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + "d:\\data.xls" + ";" +
"Extended Properties=Excel 8.0;";
OleDbConnection objConn = new OleDbConnection(connectionString);
objConn.Open();
// Creating a command object to read the values from Excel file
OleDbCommand ObjCommand = new OleDbCommand("SELECT DocumentNo FROM [Sheet1$]", objConn);
// Creating a Read object
OleDbDataReader objReader = ObjCommand.ExecuteReader();
// Looping through the values and displaying
//if (objReader.
while (objReader.Read())
{
object obj = objReader["DocumentNo"];
}
//Disposing the objects
objReader.Dispose();
ObjCommand.Dispose();
objConn.Dispose();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
精彩评论