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");
}
/// <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="filePath"></param>
/// <param name="listName"></param>
private void ImportExcel(List<string> configList, string filePath, string listGUID)
{
DataTable dt = ExcelToDS(filePath);
SPSite spSite = SPContext.Current.Site;
SPWeb spWeb = spSite.RootWeb;
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();
}
}
}
}
1
最新推荐文章于 2023-07-01 14:25:09 发布