How to get the contents of the Excel table in the asp. net page

How to get the contents of the Excel table in the asp. net page is described as follows:

Reference components and namespaces first

using Microsoft.Office.Interop.Excel;
 using System.Data.OleDb;

Then upload excel to the specified path

Upload file method omitted

Finally, change the uploaded excel into Dataset (copy the following method can be used)

public DataSet seachExcel(string str) // Parameter is excel Path of 
  {
    OleDbDataAdapter da = new OleDbDataAdapter();
    DataSet ds = new DataSet();
    DataTable dt = new DataTable();
    string NameTable = "";
    string ConText = "";
    try
    {
      // Get Excel Path 
      FileInfo info = new FileInfo(str);
      // Get the extension of the file 
      string fileExt = info.Extension;
      // Determine which connection method to use 
      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 excel
      OleDbConnection conn = new OleDbConnection(ConText);
     // Open excel
      conn.Open();
      dt=conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,null );
        if(dt!=null && dt.Rows .Count >0)
        {
          // Get sheet1 The name of the form 
          NameTable = dt.Rows[0]["TABLE_NAME"].ToString();
          // Get sheet2 The name of the form 
          //NameTable = dt.Rows[1]["TABLE_NAME"].ToString();
        }
        string sql = "select * from [" + NameTable + "]";
        da = new OleDbDataAdapter(sql, conn);
        try
        {
          da.Fill(ds,NameTable); // Fill the data into the Dataset
        }  
        catch
        { }
        conn.Close();
    }
    catch
    {
    }
    return ds; // Reverse return Dataset
}

Method for asp. net to read excel table data

In fact, reading the data in Excel table is very similar to reading the data in database, because Excel table can be regarded as one data table to some extent. The main difference between the two is that they use different data engines.

In the program of this article, the following code is used to read Excel table data, as follows:

string strDataPathPhy = "c://1.xls";
string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " + strDataPathPhy + ";Extended Properties=Excel 8.0";
OleDbConnection myConn = new OleDbConnection(strCon);
string strCom = " SELECT * FROM [Sheet1$]";
myConn.Open();
OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn);
DataTable dtData = new DataTable();
myCommand.Fill(dtData);
myConn.Close();

Since Excel can be looked at one database on the left, the worksheet inside can look at each database table on the left, so the search results can also be filtered, for example:

strCom = " SELECT * FROM [Sheet1$] WHERE column1 <> '' ";

In this way, the data in dtData is all the data that column1 is not empty in the table [Sheet1 $]

The above is how to get the Excel table in the asp. net page introduced in this article, hoping to be helpful to everyone