【Dongle】【Web】 Excel导入DataTable

using ReaderBLL;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.IO;
using System.Web.UI.HtmlControls;

       /// <summary>
        /// 验证文件的是否是excel文件
        /// </summary>
        /// <param name="ExcelPath">文件全路径</param>
        /// <returns></returns>
        private string GetConStr(string ExcelPath)
        {
            string path = ExcelPath;
            //检查文件是否存在
            if (!File.Exists(path))
            {
                Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('请选择要导入的Excel文件！');</script>");
                return null;
            }
            //文件存在，获取文件扩展名
            string str2 = Path.GetExtension(path).ToLower();
            if ((str2 != ".xls") && (str2 != ".xlsx"))
            {
                Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('请选择“.xls”或“.xlsx”文件！');</script>");
                return null;
            }
            //如果文件后缀为.xls，则连接oledb4.0
            string str3 = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source =" + path + "; Extended Properties=Excel 8.0";

            //如果文件后缀为.xlsx，则连接oledb12.0
            if (str2 == ".xlsx")
            {
                str3 = "Provider = Microsoft.ACE.OLEDB.12.0; Data Source=" + path + "; Extended Properties=Excel 12.0";
            }
            return str3;
        }
        /// <summary>
        /// 执行Excel转换成DataTable方法
        /// </summary>
        /// <param name="ExcelPath"></param>
        /// <returns></returns>
        public DataTable ExcelToDataTable(string ExcelPath)
        {
            return ExcelToDataTable(ExcelPath, null);
        }
        /// <summary>
        /// 获取Excel数据，并导入到dataTable中,具体
        /// </summary>
        /// <param name="ExcelPath">文件路径</param>
        /// <param name="SheetName">工作簿</param>
        /// <returns></returns>
        public DataTable ExcelToDataTable(string ExcelPath, string SheetName)
        {
            string conStr = GetConStr(ExcelPath);
            //判断文件是否为空
            if (string.IsNullOrEmpty(conStr))
            {
                return null;
            }
            //连接数据源，即Excel数据源
            OleDbConnection connection = new OleDbConnection(conStr);
            connection.Open();//打开数据源
            //判断是否存在对应名称的工作表
            if (string.IsNullOrEmpty(SheetName))
            {
                SheetName = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString();
            }
            else if (!SheetName.Contains("$"))
            {
                SheetName = SheetName + "$";
            }
            //定义dataadapter，用于查找数据
            OleDbDataAdapter adapter = new OleDbDataAdapter("select * from [" + SheetName + "]", conStr);
            //实例dataset，用于接收数据的容器
            DataSet dataSet = new DataSet();
            adapter.Fill(dataSet, "[" + SheetName + "$]");//填充dataset
            connection.Close();//关闭连接
            return dataSet.Tables[0];  //转换成表格输出
        }