【Net】.Net读取Excel 返回DataTable实例代码-Excel-DataTable

欢迎您访问我爱IT技术网，今天小编为你分享的编程技术是：【.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实例代码的用户互动如下：

　　相关问题：

　　答： >>详细

　　相关问题：

　　答： >>详细

　　相关问题：

　　答： >>详细