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(); } } } }
SharePoint2013导入Excel到列表,布布扣,bubuko.com
原文:http://blog.csdn.net/u014316433/article/details/28595297