欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2024-02-25 19:08:21
复制代码 代码如下:using system;using microsoft.sharepoint;using microsoft.sharepoint.webcontro...

复制代码 代码如下:

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