.net获取excel表的内容（OleDB方法)

首先引用组件和命名空间

using Microsoft.Office.Interop.Excel;

 using System.Data.OleDb;

然后把excel上传到指定路径

上传文件方法省略

最后把上传的excel变成Dataset  （复制下面的方法就可以用了)

public DataSet seachExcel(string str)  //参数为excel的路径

    {

        OleDbDataAdapter da = new OleDbDataAdapter();

        DataSet ds = new DataSet();

        DataTable dt = new DataTable();

        string NameTable = "";

        string ConText = "";

        try

        {

            //获取Excel路径

            FileInfo info = new FileInfo(str);

            //获取文件的扩展名

            string fileExt = info.Extension;

            //判断用哪种连接方式

            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";

            }

           //连接excel

            OleDbConnection conn = new OleDbConnection(ConText);

          //打开excel

            conn.Open();

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

                if(dt!=null && dt.Rows .Count >0)

                {

                    //获取sheet1表单的表名

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

                    //获取sheet2表单的表名

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

                }

                string sql = "select * from [" + NameTable + "]";

                da = new OleDbDataAdapter(sql, conn);

                try

                {

                    da.Fill(ds,NameTable); //把数据填充到Dataset

                }    

                catch

                { }

                conn.Close();

        }

        catch

        {

       }

        return ds; //反回Dataset

}

 

转载于:https://www.cnblogs.com/wei325/p/4725027.html