Add OpenFileDialog1 to select an Excel table
Using System.IO;
Using System.Data.OleDb;
Import Excel Table
private void Btninto_click (object sender, EventArgs e)
{
String resultfile = "";
OpenFileDialog openFileDialog1 = new OpenFileDialog ();
Openfiledialog1.filter = "Table file (*.xls,*.xlsx) |*.xls;*.xlsx";
Openfiledialog1.filterindex = 2;
Openfiledialog1.restoredirectory = true;
if (openfiledialog1.showdialog () = = DialogResult.OK)
{
Resultfile = Openfiledialog1.filename;
Getexcel (Resultfile);
}
}
<summary>
Get Excel Table Contents
</summary>
<param name= "fileName" >excel table name </param>
private void Getexcel (string fileName)
{
To create a link string from a table name
String excelstr = "provider= microsoft.ace.oledb.12.0;data source=" + FileName + "; Extended properties= ' Excel 12.0; Hdr=yes;imex=1 ' ";
String strsheetname = Getexcelfirsttablename (fileName);//Get First sheet name
if (strsheetname!=null)
{
DataTable dt = new DataTable ();
using (System.Data.OleDb.OleDbDataAdapter mycommand = new System.Data.OleDb.OleDbDataAdapter ("SELECT * from[" + strSheetName + "]", EXCELSTR))
{
Try
{
Mycommand.fill (DT);
}
catch (Exception ex)
{
MessageBox.Show ("operation failed" + ex.) Message);
}
}
Dgvshow.datasource = DT;
}
Else
{
MessageBox.Show ("This is an empty watch!") ");
}
}
<summary>
Get the first worksheet name of Excel
</summary>
<param name= "excelfilename" >excel table name </param>
<returns></returns>
public static string Getexcelfirsttablename (String excelfilename)
{
string tableName = null;
if (file.exists (excelfilename))
{
String excelstr = "provider= microsoft.ace.oledb.12.0;data source=" + Excelfilename + "; Extended properties= ' Excel 12.0; Hdr=yes;imex=1 ' ";
using (OleDbConnection conn = new OleDbConnection (EXCELSTR))
{
Conn. Open ();
DataTable DT = conn. GetOleDbSchemaTable (OleDbSchemaGuid.Tables, NULL);//Get table contents
tableName = dt. ROWS[0][2]. ToString (). Trim ();
}
}
return tableName;
}
C # read Excel import into database and read Excel worksheet as any table name method