. Net to get the content of the excel table,. net to get the excel

. Net to get the content of the excel table,. net to get the excel

Reference Components and namespaces first

Using Microsoft. Office. Interop. Excel;

Using System. Data. OleDb;

Then upload the excel file to the specified path.

File Upload method omitted

Finally, convert the uploaded excel file to Dataset (copy the following method to use)

Public DataSet seachExcel (string str) // The excel path

{

OleDbDataAdapter da = new OleDbDataAdapter ();

DataSet ds = new DataSet ();

DataTable dt = new DataTable ();

String NameTable = "";

String ConText = "";

Try

{

// Obtain the Excel file path

FileInfo info = new FileInfo (str );

// Obtain the file extension

String fileExt = info. Extension;

// Determine the connection method used

If (fileExt. ToLower () = ". xls ")

{

ConText = "Provider = Microsoft. jet. OLEDB.4.0; Data Source = "+ str +"; Extended Properties = 'excel 8.0; hdr = no; IMEX = 1'; Persist Security Info = false ";

}

Else if (fileExt. ToLower () = ". xlsx ")

{

ConText = "Provider = Microsoft. ACE. OLEDB.12.0; Data Source = "+ str +"; Extended Properties = 'excel 12.0 Xml; hdr = no; IMEX = 1'; Persist Security Info = False ";

}

// Connect to excel

OleDbConnection conn = new OleDbConnection (ConText );

// Open excel

Conn. Open ();

Dt = conn. GetOleDbSchemaTable (OleDbSchemaGuid. Tables, null );

If (dt! = Null & dt. Rows. Count> 0)

{

// Obtain the table name of the sheet1 form

NameTable = dt. Rows [0] ["TABLE_NAME"]. ToString ();

// Obtain the table name of the sheet2 form

// NameTable = dt. Rows [1] ["TABLE_NAME"]. ToString ();

}

String SQL = "select * from [" + NameTable + "]";

Da = new OleDbDataAdapter (SQL, conn );

Try

{

Da. Fill (ds, NameTable); // Fill data in Dataset

}

Catch

{}

Conn. Close ();

}

Catch

{

}

Return ds; // returns the Dataset.

}