【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]; //转换成表格输出 }