(C#) Method to connect to Excel using oleDbConnection

(C#) Connect to Excel using oleDbConnection

"Provider=.4.0;Data Source=D:/;Extended Properties= "Excel 8.0;HDR=Yes;IMEX=1"

For if the string is connected The relevant attributes are explained as follows："HDR=Yes;”Indicates that the first row contains the column name，Not data，"IMEX=1;”Notification drivers will always“Mix each other”Data columns are read as text。 Excel 8.0 is for Excel 2000 and above, and Excel 5.0 is for Excel 97.

using ;
using ;

String sConnectionString = "Provider=.4.0;" +
"Data Source=c:/;" +
"Extended Properties=Excel 8.0;";
OleDbConnection objConn = new OleDbConnection(sConnectionString);
();
OleDbCommand objCmdSelect =new OleDbCommand("SELECT * FROM [sheet1]", objConn);
OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
 = objCmdSelect;
DataSet objDataset1 = new DataSet();
//Fill data in Excel into the data set(objDataset1, "XLData");
();

As can be seen from the above, using Excel can be used as a normal database and using SQL statements to operate.

By getting the Sheet names of Excel files, you can use metadata:

String sConnectionString = "Provider=.4.0;" +
"Data Source=c:/;" +
"Extended Properties=Excel 8.0;";
OleDbConnection cn = new OleDbConnection(sConnectionString);
();
DataTable tb = (, null);
foreach (DataRow row in )
{
//Transfer the names of each Sheet that pop up(row["TABLE_NAME"]);
}

Regarding the use of creating and writing Excel files is very similar to normal database operations, see the following code:

String sConnectionString = "Provider=.4.0;" +
"Data Source=c:/;" +
"Extended Properties=Excel 8.0;";
OleDbConnection cn = new OleDbConnection(sConnectionString);
string sqlCreate = "CREATE TABLE TestSheet ([ID] INTEGER,[Username] VarChar,[UserPwd] VarChar)";
OleDbCommand cmd = new OleDbCommand(sqlCreate, cn);
//Create Excel file: C:/();
//Create a TestSheet worksheet();
//Add data = "INSERT INTO TestSheet VALUES(1,'elmer','password')";
();
//Close the connection();

The above is all the content of this article. I hope it will be helpful to everyone's study and I hope everyone will support me more.