2

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;
using System.Web.UI.WebControls;

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");
            //把文件上传到本地            
            ImportExcel(lis,"WageList");
        }

        /// <summary>
        /// 将文件上载到文档库中，并实现对字段的赋值
        /// </summary>
        /// <param name="web"></param>
        /// <param name="docLibName"></param>
        /// <param name="fUpload"></param>
        /// <param name="itemId"></param>
        public string UploadFileToDocLib(SPWeb web, string docLibName, FileUpload fUpload)
        {
            SPList list = web.Lists.TryGetList(docLibName);
            SPDocumentLibrary docLib = (SPDocumentLibrary)list;
            if (fUpload.HasFile)
            {
                string name = new Guid().ToString()+".xlsx";
                //string fn = System.IO.Path.GetFileName(fUpload.PostedFile.FileName);
                System.IO.Stream stm = fUpload.PostedFile.InputStream;
                int iLength = (int)stm.Length;
                if (iLength > 0)
                {
                    
                    SPFolder rootFolder = docLib.RootFolder;
                    Byte[] filecontent = new byte[iLength];
                    stm.Read(filecontent, 0, iLength);
                    SPFile f = rootFolder.Files.Add(name, filecontent);
                    SPListItem item = f.Item;
                    
                    item.SystemUpdate();
                    stm.Close();
                    return name;
                }
            }
            return null;
        }

        /// <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="configList"></param>
        /// <param name="listName"></param>
        private void ImportExcel(List<string> configList,string listGUID)
        {
            SPSite spSite = SPContext.Current.Site;
            SPWeb spWeb = spSite.RootWeb;
            string name = UploadFileToDocLib(spWeb,"Temp",this.ExcelFileUpload);

            DataTable dt = ExcelToDS(@"http://win-001/Temp/"+name);

            string listName = listGUID;//spWeb.Lists.GetList(new Guid(listGUID), true).Title;

            SPList list = spWeb.GetListFromUrl("/Lists/" + listName + "/AllItems.aspx");
            foreach (DataRow row in dt.AsEnumerable())
            {
                //确定需要添加到的文件夹
                DateTime date = Convert.ToDateTime(row[configList.FindIndex(IsDateColumn)]);
                string folder = "/" + date.Year + "/" + date.Month;
                //判断文件夹是否存在
                //年
                bool has = false;
                foreach (SPFolder spFolder in list.RootFolder.SubFolders)
                {
                    if (spFolder.Name == date.Year.ToString())
                    {
                        has = true;
                    }
                }
                if (!has)
                {
                    SPListItem spYearFolder = list.AddItem("/Lists/" + listName, SPFileSystemObjectType.Folder);
                    spYearFolder["Title"] = date.Year.ToString();
                    spYearFolder.Update();
                }
                //月
                has = false;
                foreach (SPFolder spFolder in list.RootFolder.SubFolders[date.Year.ToString()].SubFolders)
                {
                    if (spFolder.Name == date.Month.ToString())
                    {
                        has = true;
                    }
                }
                if (!has)
                {
                    SPListItem spMonthFolder = list.AddItem("/Lists/" + listName + "/" + date.Year.ToString(), 

SPFileSystemObjectType.Folder);
                    spMonthFolder["Title"] = date.Month.ToString();
                    spMonthFolder.Update();
                }


                //向列表中指定的文件夹中添加列表项
                SPListItem spListItem = list.AddItem("/Lists/" + listName + folder, SPFileSystemObjectType.File);
                for (int i = 0; i < configList.Count; i++)
                {
                    spListItem[spListItem.Fields[configList[i]].InternalName] = row[i];
                }

                //别忘了保存
                spListItem.Update();
            }
        }

        /// <summary>
        /// 判断是非是时间列
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        private bool IsDateColumn(string obj)
        {
            //如果列名是Date
            if (obj == "Date")
            {
                return true;
            }
            return false;
        }

        /// <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();
            }
        }

    }
}