C# Learning Records Read Excel with OleDB

The project requires me to read an Excel form. The way on the Internet is to use Excel as a data source and OleDB to read it.
As I understand it, OleDB is an interface for data processing. It is located in the upper ODBC layer and the lower application layer. It can be used to read and operate various data sources, including but not limited to the database.

The following are online approaches:
The EXCEL file is used as a data source to read data. Examples are as follows:

public DataSet ExcelToDS(string Path) 
{ 
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +"Data Source="+ Path +";"+"Extended Properties=Excel 8.0;"; 
OleDbConnection conn = new OleDbConnection(strConn); 
conn.Open();   
string strExcel = "";    
OleDbDataAdapter myCommand = null; 
DataSet ds = null; 
strExcel="select * from [sheet1$]"; 
myCommand = new OleDbDataAdapter(strExcel, strConn); 
ds = new DataSet(); 
myCommand.Fill(ds,"table1");    
return ds; 
}
//For the table in EXCEL, sheet([sheet1$]), if it is not fixed, you can use the following method to get it  

string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +"Data Source="+ Path +";"+"Extended Properties=Excel 8.0;"; 
OleDbConnection conn = new OleDbConnection(strConn); 
DataTable schemaTable = objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables,null); 
string tableName=schemaTable.Rows[0][2].ToString().Trim(); 
  • The return value of this method is DataSet, which is a data set that stores the read data, similar to a small independent database with tables like DataTable and the concept of columns and rows.
    Introduce the original post for reference https://blog.csdn.net/liucherish/article/details/52808173

  • For different. net data providers, ADO.NET Connection objects are used to connect databases.
    There are four Connection classes: SqlConnection, OleDbConnection, OdbcConnection and Oracle Connection.
    OleDb Connection connects databases that support OLE DB, such as Access
    For details see https://www.cnblogs.com/vaevvaev/p/6873367.html

  • Data access objects of ADO.Net include Connection, Command, DataReader and DataAdaper, etc.
    The namespace of OLEDB.NET data provider is System.Data.OleDb. Use it before using it.

    1. Using the object identifier of the OleDbConnection class to physically connect to a database, DataSource obtains the server name or file name of the data source, Provider obtains the name of the OLEDB provider specified in the "Provider =" clause of the connection string, and Extend Properties is an extended attribute.
    2. DataAdapter represents a set of SQL commands and a database connection, which are used to populate DataSet and update data sources, and Fill method to populate DataSet

In addition, it can also be written to EXCEL file, as follows:

public void DSToExcel(string Path,DataSet oldds) 
{ 
//Getting the DataSet that summarizes EXCEL first is to get the structure of EXCEL in DataSet. 
string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source ="+path1+";Extended Properties=Excel 8.0" ; 
OleDbConnection myConn = new OleDbConnection(strCon) ; 
string strCom="select * from [Sheet1$]"; 
myConn.Open ( ) ; 
OleDbDataAdapter myCommand = new OleDbDataAdapter ( strCom, myConn ) ; 
ystem.Data.OleDb.OleDbCommandBuilder builder=new OleDbCommandBuilder(myCommand); 
//QuotePrefix and QuoteSuffix are mainly used when the builder generates InsertComment commands. 
builder.QuotePrefix="[";     //Gets the reserved character (starting position) in the insert statement 
builder.QuoteSuffix="]"; //Gets the reserved character (end position) in the insert statement 
DataSet newds=new DataSet(); 
myCommand.Fill(newds ,"Table1") ; 
for(int i=0;i<oldds.Tables[0].Rows.Count;i++) 
{ 
//The ImportRow method cannot be used here to import a row into news because ImportRow will retain all the settings of the original DataRow (the DataRowState state remains unchanged).
   //New DS has value after using ImportRow, but cannot be updated to Excel because all imported rows are DataRowState!=Added 
DataRow nrow=aDataSet.Tables["Table1"].NewRow(); 
for(int j=0;j<newds.Tables[0].Columns.Count;j++) 
{ 
   nrow[j]=oldds.Tables[0].Rows[i][j]; 
} 
newds.Tables["Table1"].Rows.Add(nrow); 
} 
myCommand.Update(newds,"Table1"); 
myConn.Close(); 
} 

Tags: Programming Excel Database odbc Oracle

Posted on Mon, 29 Jul 2019 03:10:41 -0400 by BuckeyeTheDog