当前位置： 移动技术网 > IT编程>开发语言>.net > .Net读取Excel 返回DataTable实例代码

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

2017年12月12日  | 移动技术网IT编程  | 我要评论

屯溪一中,非诚勿扰徐子婷,凤瑶小说网高辣文

复制代码 代码如下:

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;
        }
    }
}

您可能感兴趣的文章:

如对本文有疑问，请在下面进行留言讨论，广大热心网友会与你互动！！ 点击进行留言回复

相关文章:

验证码：
最近更新的文章
大家感兴趣的文章
移动技术网