C#读取Excel,或者多个excel表，返回dataset

把excel 表作为一个数据源进行读取

  /// <summary>

         /// 读取Excel单个Sheet

         /// </summary>

         /// <param name="Path">Excel路径</param>

         /// <returns>返回dataset</returns>

         public DataSet ExcelToDS(string Path)

         {

             string filename = "sheet1";//可以指定excel  sheet

             string strConn = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'", Path);

             //string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";

             OleDbConnection conn = new OleDbConnection(strConn);

             conn.Open();

             string strExcel = "";

             OleDbDataAdapter myCommand = null;

             DataSet ds = null;

             strExcel = string.Format("select * from [{0}$]", filename);

             myCommand = new OleDbDataAdapter(strExcel, strConn);

             ds = new DataSet();

             myCommand.Fill(ds, "table1");

             return ds;

         }

代码

一个excel存在多表读取方法

 /// <summary>

        ///  读取Excel多个Sheet

        /// </summary>

        /// <param name="filePath">文件路径</param>

        /// <param name="fileName">文件名称</param>

        /// <returns></returns>

        public static DataSet ToDataTable(string filePath,string fileName)

        {

            string connStr = "";

            string fileType = System.IO.Path.GetExtension(fileName);

            if (string.IsNullOrEmpty(fileType)) return null;

            if (fileType == ".xls")

                connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filePath + ";" + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";

            else

                connStr = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + filePath + ";" + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\"";

            string sql_F = "Select * FROM [{0}]";

            OleDbConnection conn = null;

            OleDbDataAdapter da = null;

            DataTable dtSheetName = null;

            DataSet ds = new DataSet();

            try

            {

                // 初始化连接，并打开

                conn = new OleDbConnection(connStr);

                conn.Open();

                // 获取数据源的表定义元数据

                string SheetName = "";

                dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });

                dtSheetName.DefaultView.Sort = "TABLE_NAME";//针对excel进行排序，

                // 初始化适配器

                da = new OleDbDataAdapter();

                for (int i = ; i < dtSheetName.Rows.Count; i++)

                {

                    SheetName = (string)dtSheetName.Rows[i]["TABLE_NAME"];

                    if (SheetName.Contains("$") && !SheetName.Replace("'", "").EndsWith("$"))

                    {

                        continue;

                    }

                    da.SelectCommand = new OleDbCommand(String.Format(sql_F, SheetName), conn);

                    DataSet dsItem = new DataSet();

                    da.Fill(dsItem, SheetName);

                    ds.Tables.Add(dsItem.Tables[].Copy());

                }

            }

            catch (Exception ex)

            {

            }

            finally

            {

                // 关闭连接

                if (conn.State == ConnectionState.Open)

                {

                    conn.Close();

                    da.Dispose();

                    conn.Dispose();

                }

            }

            return ds;

        }

读取Excel多个Sheet

C#读取Excel,或者多个excel表，返回dataset的相关教程结束。