sharepoint学习笔记汇总
http://blog.csdn.net/qq873113580/article/details/20390149
using System;
using System.Collections.Generic;
using Microsoft.SharePoint;
using Microsoft.SharePoint.WebControls;
using NuctechProject.DTO.Bll;
using NuctechProject.DTO;
using System.Text;
using Maticsoft.DAL;
using System.Linq;
using Maticsoft.Model;
using System.Web.UI.WebControls;
using System.IO;
using Microsoft.Office.Interop.Excel;
using System.Web;
namespace NuctechProject.Layouts.Manage
{
public partial class Compare : LayoutsPageBase
{
private MuchProjectBll bll = new MuchProjectBll();
private aspnet_ProjectTaskDal taskDal = new aspnet_ProjectTaskDal();
protected void Page_Load(object sender, EventArgs e)
{
string script = "_spOriginalFormAction = document.forms[0].action;\n_spSuppressFormOnSubmitWrapper = true;";
this.ClientScript.RegisterClientScriptBlock(this.GetType(), "script", script, true);
if (!IsPostBack)
{
if (bll.GetByCreateID(SPContext.Current.Web.CurrentUser.ID).Count == 0)
{
this.innerHtml.Text = "您还没有添加对比项目,请在项目列表中添加需要对比的项目。";
}
else
{
Bind();
BindRP();
}
}
}
private void Bind()
{
//获取需要对比的项目ID
string idList = "";
#region 获取所选的ID,返回(1,2,3)这样的字符串
List<MuchProject> mpList = bll.GetByCreateID(SPContext.Current.Web.CurrentUser.ID);
for (int i = 0; i < mpList.Count; i++)
{
string id = "(" + mpList[i].ProjectID + ")";
if (taskDal.GetInProjectID(id).Count == 0)
{
this.innerHtml.Text = "项目[" + mpList[i].ProjectName + "]没有可对比的数据,请移除。";
return;
}
if (i + 1 == mpList.Count)
{
idList += mpList[i].ProjectID;
}
else
{
idList += mpList[i].ProjectID + ",";
}
}
if (string.IsNullOrEmpty(idList))
{
this.innerHtml.Text = "您还没有添加对比项目,请在项目列表中添加需要对比的项目。";
return;
}
idList = "(" + idList + ")";
#endregion
//根据项目ID找到对应的数据源
List<aspnet_ProjectTask> dataSource = taskDal.GetInProjectID(idList);
//检查项目
//检查数据源的对比数据列是否一致
#region 检查数据源的对比数据列是否一致
var result = dataSource.Where(x => x.TaskName.Contains("#") == true && x.PID == 0);
if (result.Count() == 0)
{
this.innerHtml.Text = "所选项目中的数据格式不正确,无法生产对比信息。接收格式:一级里程碑的任务名如(生产编码#任务名字)。";
return;
}
int taskID = 0;
int count = -1;
foreach (var item in result)
{
if (count == -1)
{
taskID = item.TaskID;
count = dataSource.Where(x => x.PID == item.TaskID).Count();
}
else if (count != dataSource.Where(x => x.PID == item.TaskID).Count())
{
count = -1;
break;
}
}
if (count == -1)
{
this.innerHtml.Text = "所选项目中的数据格式不正确,无法生产对比信息。原因:一级里程碑的直接下一级任务数不一致,不能对比。如:001#xxx的直接下一级任务为(任务1,任务2);002#xxx的直接下一级任务有为(任务1,任务2,任务3)这样无法产生对比。";
return;
}
#endregion
//获取所有列名字集合
List<string> colsName = dataSource.Where(x => x.PID == taskID).Select(x => x.TaskName).OrderBy(x => x).ToList<string>();
#region 判断值是否相同
bool b = true;
foreach (var item in result)
{
var childs = dataSource.Where(x => x.PID == item.TaskID);
foreach (var chidItem in childs)
{
if (colsName.Contains(chidItem.TaskName) == false)
{
b = false;
break;
}
}
}
if (b == false)
{
this.innerHtml.Text = "所选项目中的数据格式不正确,无法生产对比信息。原因:一级里程碑的直接下一级任务名字不相同,不能对比。如:001#xxx的直接下一级任务为(任务1,任务3);002#xxx的直接下一级任务有为(任务1,任务2)这样无法产生对比。";
return;
}
#endregion
lbtnToExcel.Visible = true;
StringBuilder html = new StringBuilder();
html.Append("<table id='content_table' border='0' cellpadding='0' cellspacing='0'>");
#region 第一行
html.Append("<tr style='background-color:#D3E2F3;'><td rowspan='2' style='text-algin:center;width:50px;'>序号</td>");
html.Append("<td rowspan='2' style='text-algin:left;width:200px;'>项目名称</td>");
html.Append("<td rowspan='2' style='text-algin:left;width:200px;'>生产编号</td>");
foreach (string colName in colsName)
{
html.Append("<td colspan='3' style='text-algin:center;'>" + colName + "</td>");
}
html.Append("</tr>");
#endregion
#region 第二行
html.Append("<tr style='background-color:#D3E2F3;'>");
foreach (string colName in colsName)
{
html.Append("<td style='text-algin:center;width:100px;'>责任人</td>");
html.Append("<td style='text-algin:center;width:180px;'>计划开始时间</td>");
html.Append("<td style='text-algin:center;width:180px;'>计划结束时间</td>");
}
html.Append("</tr>");
#endregion
//循环项目行
for (int i = 0; i < mpList.Count; i++)
{
//一个项目需要夸几行
List<aspnet_ProjectTask> rowspan = dataSource.Where(x => x.ProjectId == mpList[i].ProjectID && x.TaskName.Contains("#") && x.PID == 0).ToList();
//项目名称
string projectName = dataSource.First(x => x.ProjectId == mpList[i].ProjectID).ProjectName;
for (int j = 0; j < rowspan.Count; j++)
{
if (j == 0)
{
html.Append("<tr>");
html.Append("<td rowspan='" + rowspan.Count() + "' style='text-algin:center;width:50px;'>" + (i + 1) + "</td>");
html.Append("<td rowspan='" + rowspan.Count() + "' style='text-algin:center;width:200px;'>" + projectName + "</td>");
}
else
{
html.Append("<tr>");
}
html.Append("<td style='text-algin:center;width:200px;'>" + rowspan[j].TaskName + "</td>");
List<aspnet_ProjectTask> taskList = dataSource.Where(x => x.PID == rowspan[j].TaskID).OrderBy(x => x.TaskName).ToList();
for (int k = 0; k < taskList.Count; k++)
{
html.Append("<td style='text-algin:center;width:100px;'>" + taskList[k].AssignedTo + "</td>");
html.Append("<td style='text-algin:center;width:180px;'>" + taskList[k].StartDate.ToString("yyyy-MM-dd") + "</td>");
html.Append("<td style='text-algin:center;width:180px;'>" + taskList[k].DueDate.ToString("yyyy-MM-dd") + "</td>");
}
html.Append("</tr>");
}
}
this.innerHtml.Text = html.ToString();
}
protected void lbtnProjectList_Click(object sender, EventArgs e)
{
Response.Redirect(Common.rootUrl + "/_layouts/15/Manage/CompareProject.aspx?aid=a_229");
}
protected void lbtnProjectSee_Click(object sender, EventArgs e)
{
Response.Redirect(Common.rootUrl + "/_layouts/15/Manage/Compare.aspx?aid=a_229");
}
private void BindRP()
{
rpList.DataSource = new MuchProjectBll().GetByCreateID(SPContext.Current.Web.CurrentUser.ID);
rpList.DataBind();
}
protected void rpList_ItemCommand(object source, RepeaterCommandEventArgs e)
{
if (e.CommandName == "del")
{
int id = Convert.ToInt32(e.CommandArgument);
new MuchProjectBll().DeleteByID(id);
BindRP();
Bind();
}
}
protected void lbtnClrear_Click(object sender, EventArgs e)
{
new MuchProjectBll().DeleteByCreateID(SPContext.Current.Web.CurrentUser.ID);
BindRP();
Bind();
}
protected void lbtnToExcel_Click(object sender, EventArgs e)
{
ProjectToExcel();
}
public void ProjectToExcel()
{
string userName = SPContext.Current.Web.CurrentUser.Name;
SPSecurity.RunWithElevatedPrivileges(delegate
{
#region 删除以username为开头的文件
string folderPath = Server.MapPath("~/_layouts/15/ProjectToExcel/" + userName + "/");
string templatePath = Server.MapPath("~/_layouts/15/ProjectToExcel/" + userName + "/" + userName + "项目对比文件.xlsx");
if (Directory.Exists(folderPath)) //根目录
{
//判断上传目录是否存在(删除遗留文件夹)
Directory.Delete(folderPath, true);
}
if (!Directory.Exists(folderPath))
{
Directory.CreateDirectory(folderPath);
}
DirectoryInfo directory = new DirectoryInfo(folderPath);
FileInfo[] fileInfoArray = directory.GetFiles();
foreach (FileInfo file in fileInfoArray)
{
if (file.Name.Contains(userName))
{
file.Delete();
}
}
#endregion
#region Excel基本设置
Microsoft.Office.Interop.Excel.ApplicationClass MyExcel = new Microsoft.Office.Interop.Excel.ApplicationClass();
MyExcel.Visible = false;//excel是否可见
MyExcel.DisplayAlerts = false;//屏蔽一些弹出窗口
Microsoft.Office.Interop.Excel.Workbooks MyWorkBooks = MyExcel.Workbooks;
Microsoft.Office.Interop.Excel.Workbook MyWorkBook = MyWorkBooks.Add(System.Type.Missing);
Microsoft.Office.Interop.Excel.Worksheet MyWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)MyWorkBook.Worksheets[1];
#endregion
//获取需要对比的项目ID
string idList = "";
#region 获取所选的ID,返回(1,2,3)这样的字符串
List<MuchProject> mpList = bll.GetByCreateID(SPContext.Current.Web.CurrentUser.ID);
for (int i = 0; i < mpList.Count; i++)
{
string id = "(" + mpList[i].ProjectID + ")";
if (taskDal.GetInProjectID(id).Count == 0)
{
this.innerHtml.Text = "项目[" + mpList[i].ProjectName + "]没有可对比的数据,请移除。";
return;
}
if (i + 1 == mpList.Count)
{
idList += mpList[i].ProjectID;
}
else
{
idList += mpList[i].ProjectID + ",";
}
}
if (string.IsNullOrEmpty(idList))
{
this.innerHtml.Text = "您还没有添加对比项目,请在项目列表中添加需要对比的项目。";
return;
}
idList = "(" + idList + ")";
#endregion
//根据项目ID找到对应的数据源
List<aspnet_ProjectTask> dataSource = taskDal.GetInProjectID(idList);
var result = dataSource.Where(x => x.TaskName.Contains("#") == true && x.PID == 0).ToList();
int taskID = result[0].TaskID;
//获取所有列名字集合
List<string> colsName = dataSource.Where(x => x.PID == taskID).Select(x => x.TaskName).OrderBy(x => x).ToList<string>();
//总列数
int allCols = colsName.Count * 3 + 3;
#region 第一二行
MyWorkSheet.Cells[1, 1] = "序号";
Merge(MyExcel, MyWorkSheet, 1, 1, 2, 1);
MyWorkSheet.Cells[1, 2] = "项目名称";
Merge(MyExcel, MyWorkSheet, 1, 2, 2, 2);
MyWorkSheet.Cells[1, 3] = "生产编号";
Merge(MyExcel, MyWorkSheet, 1, 3, 2, 3);
for (int i = 0; i < colsName.Count; i++)
{
int y = 4 + i + (2 * i);
MyWorkSheet.Cells[1, y] = colsName[i];
Merge(MyExcel, MyWorkSheet, 1, y, 1, (y + 2));
MyWorkSheet.Cells[2, y] = "责任人";
Merge(MyExcel, MyWorkSheet, 2, y, 2, y);
MyWorkSheet.Cells[2, y + 1] = "计划开始时间";
Merge(MyExcel, MyWorkSheet, 2, y + 1, 2, y + 1);
MyWorkSheet.Cells[2, y + 2] = "计划结束时间";
Merge(MyExcel, MyWorkSheet, 2, y + 2, 2, y + 2);
}
#endregion
//总行数
int rowCount = 3;
//循环项目行
for (int i = 0; i < mpList.Count; i++)
{
//一个项目需要夸几行
List<aspnet_ProjectTask> rowspan = dataSource.Where(x => x.ProjectId == mpList[i].ProjectID && x.TaskName.Contains("#") && x.PID == 0).ToList();
//项目名称
string projectName = dataSource.First(x => x.ProjectId == mpList[i].ProjectID).ProjectName;
MyWorkSheet.Cells[rowCount, 1] = i + 1;
Merge(MyExcel, MyWorkSheet, rowCount, 1, rowCount - 1 + (rowspan.Count * 2), 1);
MyWorkSheet.Cells[rowCount, 2] = projectName;
Merge(MyExcel, MyWorkSheet, rowCount, 2, rowCount - 1 + (rowspan.Count * 2), 2);
int rowLine = rowCount;
for (int j = 0; j < rowspan.Count; j++)
{
MyWorkSheet.Cells[rowLine, 3] = rowspan[j].TaskName;
Merge(MyExcel, MyWorkSheet, rowLine, 3, rowLine + 1, 3);
List<aspnet_ProjectTask> taskList = dataSource.Where(x => x.PID == rowspan[j].TaskID).OrderBy(x => x.TaskName).ToList();
for (int k = 0; k < taskList.Count; k++)
{
int y = 4 + k + (2 * k);
MyWorkSheet.Cells[rowLine, y] = taskList[k].AssignedTo;
Merge(MyExcel, MyWorkSheet, rowLine, y, rowLine, y);
MyWorkSheet.Cells[rowLine, y + 1] = taskList[k].StartDate.ToString("yyyy-MM-dd");
Merge(MyExcel, MyWorkSheet, rowLine, y + 1, rowLine, y + 1);
MyWorkSheet.Cells[rowLine, y + 2] = taskList[k].DueDate.ToString("yyyy-MM-dd");
Merge(MyExcel, MyWorkSheet, rowLine, y + 2, rowLine, y + 2);
}
rowLine += 2;
}
rowCount = rowCount + (rowspan.Count * 2);
}
rowCount--;
//给单元格加边框
Range Range1 = MyWorkSheet.get_Range((Range)MyWorkSheet.Cells[1, 1], (Range)MyWorkSheet.Cells[rowCount, allCols]);
Range1.Font.Name = "微软雅黑";
Range1.EntireColumn.AutoFit();
Range1.BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, null);
Range1.Borders.get_Item(XlBordersIndex.xlInsideHorizontal).LineStyle = XlLineStyle.xlContinuous;//块内竖线
Range1.Borders.get_Item(XlBordersIndex.xlInsideVertical).LineStyle = XlLineStyle.xlContinuous;//块内横线
SPSecurity.RunWithElevatedPrivileges(delegate { MyWorkBook.SaveCopyAs(templatePath); });
Context.Response.ContentType = "application/ms-excel";
Context.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(userName + "项目对比文件.xlsx", Encoding.UTF8));
Response.BinaryWrite(File.ReadAllBytes(templatePath));
Context.Response.End();
// 删除副本
File.Delete(templatePath);
MyWorkBook.Close();
MyWorkBooks.Close();
MyExcel.Quit();
GC.Collect();
});
}
//合并单元格
private void Merge(Microsoft.Office.Interop.Excel.ApplicationClass MyExcel, Microsoft.Office.Interop.Excel.Worksheet MyWorkSheet, int startX, int startY, int endX, int endY)
{
MyExcel.Application.DisplayAlerts = false;
Range RangeTitle = MyWorkSheet.get_Range((Range)MyWorkSheet.Cells[startX, startY], (Range)MyWorkSheet.Cells[endX, endY]);
RangeTitle.Merge(false);
RangeTitle.EntireColumn.AutoFit();
RangeTitle.HorizontalAlignment = XlHAlign.xlHAlignCenter;
MyExcel.Application.DisplayAlerts = true;
}
}
}