"210" reads and writes Excel data to a DataTable through OLE DB

Reference: C # is displayed through OLE DB read-write Excel2013 to the DataGrid control, modifies the dataset, and updates the excel2013

Directory:

1. System.Data.OleDb namespaces

• OleDbConnection class
• OleDbDataAdapter class
• Dateset class
• DataTable class

2. Code implementation

System.Data.OleDb namespaces

• OleDbConnection class: Represents an open connection to a data source.
  • OleDbConnection (String): Initializes a new instance of the OleDbConnection class with the specified connection string.
  • ConnectionString: Gets or sets the string used to open the database. (String of the above constructor)
    

    String Strcon = "Provider=microsoft.ace.oledb.12.0;data source=" + @ "d:/score table 2013.xlsx" + "; Extended properties= ' Excel 12.0; Hdr=yes;imex=0 ' ";

  • Open: Opens the database connection using the property settings specified by ConnectionString.
  • Close: Closes the connection to the data source.
• OleDbDataAdapter class: Represents a set of data commands and a database connection that is used to populate the dataset and update the data source.
  • OleDbDataAdapter (): Initializes a new instance of the OleDbDataAdapter class.
  • OleDbDataAdapter (OleDbCommand): Initializes a new instance of the OleDbDataAdapter class with the specified OleDbCommand as the SelectCommand property.
  • OleDbDataAdapter (String, OleDbConnection): Initializes a new instance of the OleDbDataAdapter class with SelectCommand.
  • OleDbDataAdapter (String, String): Initializes a new instance of the OleDbDataAdapter class with SelectCommand.
  • Fill (DataSet, String): Adds or refreshes rows in the dataset to match rows in the data source using the dataset and DataTable names.
    

    OleDbConnection myconn = new OleDbConnection (Strcon); string strcom = "SELECT * FROM [sheet1$]"; MyConn.Open (); O Ledbdataadapter MyDataAdapter = new OleDbDataAdapter (strcom, myconn);D ataset mydataset = new DataSet (); Mydataadapter.fill (myDataSet, "[sheet1$]"); Myconn.close ();

• Dateset class: An in-memory cache that represents data. (suitable for small amounts of data, put all memory in one time)
• DataTable class: represents an in-memory data table

Code implementation: (By default, the first row is set to the title, the table content is calculated from the second row)

The extension is XLS error, xlsx will not error string str_filename = @ "D:\01-Working\ integrated business platform data \ Buoy \fb.xls"; string Strcon = String.Format (" Provider=microsoft.ace.oledb.12.0;data source={0}; Extended properties= ' Excel 12.0; Hdr=yes;imex=0 ' ", str_filename);//New Connection
OleDbConnection myconn = new OleDbConnection (Strcon);
MyConn.Open ();//New SQL statement, get SHEET1 all data string strcom = "SELECT * FROM [sheet1$]";
Used to add data to the dataset OleDbDataAdapter MyDataAdapter = new OleDbDataAdapter (strcom, myconn);D ataset mydataset = new DataSet (); Mydataadapter.fill (myDataSet, "[sheet1$]"); Myconn.close ();

"210" reads and writes Excel data to a DataTable through OLE DB