using Microsoft.SharePoint;using System;using System.Collections.Generic;using System.ComponentModel;using System.Data;using System.Data.OleDb;using System.Linq;using System.Web.UI.WebControls.WebParts;using System.Xml.Linq;using System.Text;namespace MySharePointProject.UploadExcelVisualWebPart{    [ToolboxItemAttribute(false)]    public partial class UploadExcelVisualWebPart : WebPart    {        // 仅当使用检测方法对场解决方案进行性能分析时才取消注释以下 SecurityPermission        // 特性，然后在代码准备进行生产时移除 SecurityPermission 特性        // 特性。因为 SecurityPermission 特性会绕过针对您的构造函数的调用方的        // 安全检查，不建议将它用于生产。        // [System.Security.Permissions.SecurityPermission(System.Security.Permissions.SecurityAction.Assert, UnmanagedCode = true)]        public UploadExcelVisualWebPart()        {        }        protected override void OnInit(EventArgs e)        {            base.OnInit(e);            InitializeControl();        }        protected void Page_Load(object sender, EventArgs e)        {        }        protected void btnUpload_Click(object sender, EventArgs e)        {            //取得XML配置文件中的导入配置,第一项字符串所代表的是需要把Excel第一列导入到的SharePoint栏名            List<string> lis = getAppManager("123");            //把文件上传到本地            this.ExcelFileUpload.SaveAs(@"C:\a\test.xlsx");            ImportExcel(lis, @"C:\a\test.xlsx", "WageList", "");//  "/2014/1"        }        /// <summary>        /// 从配置文件XML取得列表        /// </summary>        /// <param name="strListId"></param>        /// <returns></returns>        private List<string> getAppManager(string strListId)        {            List<string> FieldList = new List<string>();            XElement root = XElement.Load(@"C:\XMLFile1.xml");//XElement.Load(System.Web.HttpContext.Current.Server.MapPath("XMLFile1.xml"));            IEnumerable<XElement> address = from el in root.Elements("listId")                                            where (string)el.Attribute("id") == strListId                                            select el;            foreach (XElement el in address)            {                foreach (XNode node in el.Nodes())                {                    XElement nod = (XElement)node;                    string FieldName = nod.Value;                    FieldList.Add(FieldName);                }            }            return FieldList;        }        /// <summary>        ///         /// </summary>        /// <param name="l">配置列表</param>        /// <param name="filePath">Excel 文件路径</param>        /// <param name="listName">列表名称</param>        /// <param name="folder">列表文件夹</param>        private void ImportExcel(List<string> l, string filePath, string listName, string folder)        {            DataTable dt = ExcelToDS(filePath);            SPSite spSite = SPContext.Current.Site;            SPWeb spWeb = spSite.RootWeb;            SPList list = spWeb.GetListFromUrl("/Lists/" + listName + "/AllItems.aspx");            foreach (DataRow row in dt.AsEnumerable())            {                //向列表中指定的文件夹中添加列表项                SPListItem spListItem = list.AddItem("/Lists/WageList" + folder, SPFileSystemObjectType.File);                for (int i = 0; i < l.Count; i++)                {                    spListItem[spListItem.Fields[l[i]].InternalName] = row[i];                }                //别忘了保存                spListItem.Update();            }        }        /// <summary>        /// 传入Excel的地址,得到Sheet1的DataTable        /// </summary>        /// <param name="Path"></param>        /// <returns></returns>        public DataTable ExcelToDS(string Path)        {            string strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "Data Source=" + Path + ";" + "Extended Properties='Excel 12.0;HDR=Yes;IMEX=1';";            OleDbConnection conn = new OleDbConnection(strConn);            try            {                DataTable dt = new DataTable();                if (conn.State != ConnectionState.Open)                    conn.Open();                string strExcel = "select * from [Sheet1$]";                OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, conn);                adapter.Fill(dt);                return dt;            }            catch (Exception ex)            {                throw new Exception(ex.Message);            }            finally            {                if (conn.State != ConnectionState.Closed)                    conn.Close();            }        }    }}