Excel file export operation we often use, but to tell an Excel document import and display to the interface or the first time.
Below is an introduction to how to import an Excel file under C #.
First add two references
Using System.IO;
Using System.Data.OleDb;
Add Control OpenFileDialog
Then we need to configure the OLE DB connection string for Excel
<span style= "FONT-SIZE:14PX;" >public Const string oledbconnstring = "Provider = microsoft.jet.oledb.4.0; Data Source = {0}; Extended properties= ' Excel 8.0; Hdr=yes;imex=1, ' "; OLE DB connection string for Excel </span>
Select an Excel file
<summary> ///Select Excel//</summary> private void Btn_browserexcel_click (object sender, EventArgs e) { DialogResult dr = This.openFileDialog1.ShowDialog (); if (DialogResult.OK = = dr)//infer whether the file is selected { this.txtPath.Text = this.openFileDialog1.FileName; This.btn_Import.Enabled = true; } }
run the imported operation and bind the data source
<summary> ////Run import operation///</summary> private void Btn_import_click (object sender, EventArgs e) { string path = This.txtPath.Text.Trim (); if (string. IsNullOrEmpty (path) { MessageBox.Show ("Select the Excel file you want to import.) "," information "); return; } if (! File.exists (path) //Infer whether the file exists { MessageBox.Show ("info"), "the corresponding Excel file cannot be found, select again." "); This.btn_BrowserExcel.Focus (); return; } DataTable Exceltbl = this. Getexceltable (path); The call function gets the information in Excel if (exceltbl = = null) { return; } Dgvimport.datasource = exceltbl; }
The most core features are here:
<summary>////Get the information in the Excel file and save it in a DataTable////</summary>//<param name = "path" > File path </param>//<returns> returns the generated datatable</returns> private DataTable Getexcelta BLE (string path) {try {//Get Excel data DataTable dt1 = new Datata BLE ("exceltable"); String strconn = String. Format (oledbconnstring, path); OleDbConnection conn = new OleDbConnection (strconn); Conn. Open (); DataTable DT = conn. GetSchema ("Tables"); Infer the number of sheet pages in Excel, and query page 1th if (dt. Rows.Count > 0) {string selsqlstr = string. Format ("select * from [{0}]", dt. rows[0]["table_name"]); OleDbDataAdapter Oleda = new OleDbDataAdapter (SELSQLSTR, conn); Oleda.fill (DT1); } conn. Close (); return DT1; } catch (Exception ex) {MessageBox.Show ("Excel converted DataTable Error:" + ex.) Message); return null; } }
:
See here people will certainly have a sense of déjà vu, the top configuration connection string, here the Getexceltable method. Open the connection, query the statement, run the command, populate the table. Close the connection. Is it a copy of the process of querying data from a database?
Indeed, knowledge is interlinked.
In fact, this blog can be combined with a previous article, "Bulk save data List use", using the list<> generic collection to save the table data to the database.
??
C # Excel file import operation