這篇文章主要介紹了.Net讀取Excel 返回DataTable實例代碼，有需要的朋友可以參考一下
代碼如下: using System; using Microsoft.SharePoint; using Microsoft.SharePoint.WebControls; using System.Data; using System.IO; using System.Linq; using System.Web; using System.Collections; using System.Data.OleDb; using NuctechProject.DTO.Bll; using System.Collections.Generic; namespace NuctechProject.Layouts.Project { public partial class IntroductionPlan : LayoutsPageBase { string url = Common.rootUrl; private string _strConn; //導入excel時的連接 string pmurl = Common.proUrl; private UserBLL bll = new UserBLL(); protected void Page_Load(object sender, EventArgs e) { hidProid.Value = Request.QueryString["proid"]; } protected void BtnOK_Click(object sender, EventArgs e) { DataTable excelTable = null; SPSecurity.RunWithElevatedPrivileges(delegate { if (BaseInfoTemplateFile.HasFile) { List<string> noInput = new List<string>(); string strLoginName = HttpContext.Current.User.Identity.Name; //獲取用戶名 string folderTemp = strLoginName.Substring(strLoginName.LastIndexOf('') + 1); try { string extension = Path.GetExtension(BaseInfoTemplateFile.FileName); //獲取文件的後綴 if (extension != null) { string fileException = extension.ToLower(); if (fileException == ".xlsx" || fileException == ".xls") { #region 讀取Excel string fileFolder = Server.MapPath("~/_layouts/15/images/" + folderTemp + "Upfile/"); if (!Directory.Exists(fileFolder)) //根目錄 { Directory.CreateDirectory(fileFolder); //判斷上傳目錄是否存在 自動創建 } BaseInfoTemplateFile.SaveAs(Server.MapPath("~/_layouts/15/images/" + folderTemp + "Upfile/" + BaseInfoTemplateFile.FileName)); string strFilepathNmae = Server.MapPath("~/_layouts/15/images/" + folderTemp + "Upfile/" + BaseInfoTemplateFile.FileName); string strExcel = ExcelSheetName(strFilepathNmae)[0].ToString(); excelTable = ExcelDataSource(strFilepathNmae, strExcel).Tables[0]; #endregion //data是excel的數據 DataTable data = ExcelDataSource(strFilepathNmae, strExcel).Tables[0]; //try //{ if (data != null) { foreach (DataRow row in data.Rows) { //讀取 } } //} //catch (Exception) //{ // Page.ClientScript.RegisterStartupScript(Page.ClientScript.GetType(), "myscript", "<script type='text/javascript'>$.ligerDialog.closeWaitting();alert('Excel表列名與系統不符合，請檢查Excel表列名！');</script>"); // return; //} } else { Page.ClientScript.RegisterStartupScript(Page.ClientScript.GetType(), "myscript", "<script type='text/javascript'>$.ligerDialog.closeWaitting();alert('您選擇的文件不是Excel格式！');</script>"); return; } } } finally //最終要把臨時存儲的文件刪除 { string strFileFolder = Server.MapPath("~/_layouts/15/images/" + folderTemp + "Upfile/"); if (Directory.Exists(strFileFolder)) //根目錄 { //Directory.CreateDirectory(strFileFolder);//判斷上傳目錄是否存在 自動創建 Directory.Delete(strFileFolder, true); } else { Page.ClientScript.RegisterStartupScript(Page.ClientScript.GetType(), "myscript", "<script type='text/javascript'>ReturnPageValue();</script>"); } } } else { Page.ClientScript.RegisterStartupScript(Page.ClientScript.GetType(), "myscript", "<script type='text/javascript'>$.ligerDialog.closeWaitting();alert('請選擇導入文件！');</script>"); return; } }); } protected void BtnClose_Click(object sender, EventArgs e) { Page.ClientScript.RegisterStartupScript(Page.ClientScript.GetType(), "myscript", "<script type='text/javascript'>ReturnPageValue();</script>"); } /// <summary> /// 連接到Excel /// </summary> /// <param name="filepath">文件路徑</param> /// <param name="sheetname">sheet名字</param> /// <returns></returns> public DataSet ExcelDataSource(string filepath, string sheetname) { _strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filepath + ";Extended Properties='Excel 12.0;HDR=YES'"; new OleDbConnection(_strConn); var oada = new OleDbDataAdapter("select * from [" + sheetname + "]", _strConn); var ds = new DataSet(); oada.Fill(ds); return ds; } /// <summary> /// 獲得Excel中的所有sheetname /// </summary> /// <param name="filepath">文件路徑</param> /// <returns></returns> public ArrayList ExcelSheetName(string filepath) { _strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filepath + ";Extended Properties='Excel 12.0;HDR=YES'"; var al = new ArrayList(); var conn = new OleDbConnection(_strConn); conn.Open(); DataTable sheetNames = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" }); conn.Close(); if (sheetNames != null) foreach (DataRow dr in sheetNames.Rows) { al.Add(dr[2]); } return al; } } }
