.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>文件路径</param>
        /// <param>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>文件路径</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;
        }
    }
}