.Net怎么讀取Excel返回DataTable

這篇文章主要介紹“.Net怎么讀取Excel返回DataTable”，在日常操作中，相信很多人在.Net怎么讀取Excel返回DataTable問題上存在疑惑，小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”.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;        }    }}

到此，關于“.Net怎么讀取Excel返回DataTable”的學習就結束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學習，快去試試吧！若想繼續學習更多相關知識，請繼續關注億速云網站，小編會繼續努力為大家帶來更多實用的文章！