MOSS层级结构Excel导入SPList
等等
using System;
using Microsoft.SharePoint;
using Microsoft.SharePoint.WebControls;
using Microsoft.Office.Interop.Excel;
using System.Web;
using System.Data;
using System.Collections;
namespace NuctechProject.Layouts.Settings
{
public partial class Introducte : LayoutsPageBase
{
private static readonly string _rootUrl = HttpContext.Current.Request.Url.Scheme + "://" +
HttpContext.Current.Request.Url.Host;
private static readonly string weburl = _rootUrl + "/";
private readonly string StrUrl = weburl + "newTemplate/"; //公告站点地址
string excelPath = @"C:\Users\Administrator\Desktop\s\strTemplateFileName.xlsx";
public static int cengMax = 1;
public static System.Data.DataTable dt;
protected void Page_Load(object sender, EventArgs e)
{
SPSecurity.RunWithElevatedPrivileges(delegate
{
using (var site = new SPSite(StrUrl))
{
using (SPWeb web = site.OpenWeb())
{
web.AllowUnsafeUpdates = true;
dt = GetDataFromExcel(excelPath, true, 1, "dd", 1);
GridView1.DataSource = dt;
GridView1.DataBind();
cengMax = GetCengMax(dt, "级任务");
SPList spList = web.Lists["TastTemp"];
for (int i = spList.ItemCount - 1; i >= 0; i--)
{
spList.Items[i].Delete();
}
AddChildsToSpList(-1, 0, 0.0, spList, web);
}
}
});
}
/// <summary>
/// Excel导入DataTable
/// </summary>
/// <param name="strFileName">文件名称</param>
/// <param name="isHead">是否包含表头</param>
/// <param name="iSheet">Sheet</param>
/// <param name="strErrorMessage">错误信息</param>
/// <param name="iRowsIndex">导入的Excel的开始行</param>
/// <returns></returns>
public static System.Data.DataTable GetDataFromExcel(string strFileName, bool isHead, int iSheet,
string strErrorMessage, int iRowsIndex)
{
if (!strFileName.ToUpper().EndsWith(".XLSX"))
{
strErrorMessage = "文件类型与系统设定不一致，请核对！";
return null;
}
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook workbookData;
Microsoft.Office.Interop.Excel.Worksheet worksheetData;
workbookData = xlApp.Workbooks.Open(strFileName, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value,
System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
worksheetData = (Microsoft.Office.Interop.Excel.Worksheet)workbookData.Sheets[iSheet];
Microsoft.Office.Interop.Excel.Range xlRang = null;
int iRowCount = worksheetData.UsedRange.Cells.Rows.Count;
int iParstedRow = 0, iCurrSize = 0;
int iEachSize = 1000; // each time you
int iColumnAccount = worksheetData.UsedRange.Cells.Columns.Count;
int iHead = iRowsIndex;
if (isHead)
iHead = iRowsIndex + 1;
System.Data.DataTable dt = new System.Data.DataTable();
for (int i = 1; i <= iColumnAccount; i++)
{
if (isHead)
dt.Columns.Add(xlApp.Cells[iRowsIndex, i].FormulaLocal);
else
dt.Columns.Add("Columns" + i.ToString());
}
object[,] objVal = new object[iEachSize, iColumnAccount];
try
{
iCurrSize = iEachSize;
while (iParstedRow < iRowCount)
{
if ((iRowCount - iParstedRow) < iEachSize)
iCurrSize = iRowCount - iParstedRow;
xlRang = worksheetData.get_Range("A" + ((int)(iParstedRow + iHead)).ToString(), ((char)('A' + iColumnAccount - 1)).ToString()
+ (((int)(iParstedRow + iCurrSize + 1)).ToString()));
objVal = (object[,])xlRang.Value2;
int iLength = objVal.Length / iColumnAccount;
for (int i = 1; i < iLength; i++)
{
DataRow dr = dt.NewRow();
for (int j = 1; j <= iColumnAccount; j++)
{
if (objVal[i, j] != null)
{
dr[j - 1] = objVal[i, j].ToString();
}
}
dt.Rows.Add(dr);
}
iParstedRow = iParstedRow + iCurrSize;
}
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlRang);
xlRang = null;
}
catch (Exception ex)
{
xlApp.Quit();
strErrorMessage = ex.Message;
return null;
}
xlApp.Quit();
return dt;
}
/// <summary>
/// 获取是否有下一级
/// </summary>
/// <param name="rowNum">当前行</param>
/// <param name="columnNum">当前列</param>
/// <param name="cengMax">最大层级数</param>
/// <param name="dt">datatable</param>
/// <returns>YesOrNo</returns>
public static bool HasFollowSon(int rowNum, int columnNum, int cengMax, System.Data.DataTable dt)
{
if (columnNum < cengMax - 1)
{
if (rowNum >= dt.Rows.Count - 1)//当最后一行时不去判断下一行，否则出错
{
return false;
}
if (dt.Rows[rowNum + 1][columnNum + 1].ToString() != "")
{
return true;
}
else
{
return false;
}
}
else
{
return false;
}
return true;
}
/// <summary>
/// 判断是否有紧跟的同级
/// </summary>
/// <param name="rowNum">当前行</param>
/// <param name="columnNum">当前列</param>
/// <param name="dt">DataTable</param>
/// <returns>YesOrNo</returns>
public static bool HasFollowBrother(int rowNum, int columnNum, System.Data.DataTable dt)
{
if (rowNum >= dt.Rows.Count - 1)//当最后一行时不去判断下一行，否则出错
{
return false;
}
if (dt.Rows[rowNum + 1][columnNum].ToString() == "")
{
return false;
}
else
{
return true;
}
}
/// <summary>
/// 获取总体层级
/// </summary>
/// <param name="dt">DataTable</param>
/// <param name="keyWords">关键字，如？"级任务"</param>
/// <returns></returns>
public static int GetCengMax(System.Data.DataTable dt, string keyWords)
{
int columnCount = dt.Columns.Count;
for (int i = 2; i < columnCount; i++)//最小为两极
{
if (!dt.Columns[i].ColumnName.ToString().Contains(keyWords))
{
return i;
}
}
//TODU：如何报错
return 5;
}
/// <summary>
/// 获取DataTable中子任务的集合
/// 当行为0时输出根节点
/// </summary>
/// <param name="rowNum"></param>
/// <param name="columnNum"></param>
/// <param name="dt"></param>
/// <returns></returns>
public static DataRowCollection GetChildCollection(int rowNum, int columnNum, System.Data.DataTable dt)
{
System.Data.DataTable dtClone = dt.Clone();
if (rowNum == -1)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
if (dt.Rows[i][0].ToString() != "")
{
dtClone.ImportRow(dt.Rows[i]);
}
}
}
else
{
if (HasFollowSon(rowNum, columnNum, cengMax, dt))
{
rowNum++;
columnNum++;
do
{
dtClone.ImportRow(dt.Rows[rowNum]);
rowNum++;
while (HasFarBrother(rowNum - 1, columnNum, dt))
{
dtClone.ImportRow(GetFarBrother(rowNum - 1, columnNum, dt));
rowNum = GetFarBrotherRowIndex(rowNum - 1, columnNum, dt) + 1;
}
} while (HasFollowBrother(rowNum - 1, columnNum, dt));
}
}
return dtClone.Rows;
}
/// <summary>
/// 获取一行数据中的任务名
/// 读取方法为读取第一个值
/// </summary>
/// <param name="dr"></param>
/// <returns></returns>
public static string GetTaskName(DataRow dr)
{
for (int i = 0; i < dr.Table.Columns.Count; i++)
{
if (dr[i].ToString() != "")
{
return dr[i].ToString();
}
}
return "任务名读取错误";
}
public static int GetCeng(DataRow dr)
{
for (int i = 0; i < dr.Table.Columns.Count; i++)
{
if (dr[i].ToString() != "")
{
return i;
}
}
return 1;//不知如何报错
}
/// <summary>
///
/// </summary>
/// <param name="rowNum"></param>
/// <param name="columnNum"></param>
/// <param name="taskId">用于PID</param>
/// <param name="spList"></param>
/// <param name="spWeb"></param>
public static void AddChildsToSpList(int rowNum, int columnNum, double taskId, SPList spList, SPWeb spWeb)
{
DataRowCollection drC = GetChildCollection(rowNum, columnNum, dt);
double ID;
for (int i = 0; i < drC.Count; i++)
{
if (rowNum == -1)
{
SPListItem spi = spList.AddItem();
spi["TaskName"] = GetTaskName(drC[i]);
spi["PID"] = 0;
//可以同上一并判断，但鉴于需求中不清楚相同时如何修改。。。。。。
if (!SpListHasThisItem(spList, GetTaskName(drC[i]), 0))
{
spi.Update();
ID = spi.ID;
}
else
{
ID = GetTaskID(spList, GetTaskName(drC[i]));//做相应的处理
}
if (HasFollowSon(GetRowNumRoot(dt, GetTaskName(drC[i])), GetColumnNumFC(drC[i]), cengMax, dt))
{
AddChildsToSpList(GetRowNumRoot(dt, GetTaskName(drC[i])), GetColumnNumFC(drC[i]), ID, spList, spWeb);//获取根目录的行
}
}
else
{
SPListItem spi = spList.AddItem();
spi["TaskName"] = GetTaskName(drC[i]);
spi["PID"] = taskId;
if (SpListHasThisItem(spList, GetTaskName(drC[i]), taskId))
{
ID = GetTaskID(spList, GetTaskName(drC[i]));//做相应的处理
}
else
{
spi.Update();
ID = spi.ID;
}
if (HasFollowSon(GetRowNumFC(drC[i], dt), GetColumnNumFC(drC[i]), cengMax, dt))
{
AddChildsToSpList(GetRowNumFC(drC[i], dt), GetColumnNumFC(drC[i]), ID, spList, spWeb);//获取根目录的行
}
}
}
}
/// <summary>
/// 判断某一任务的子任务中是否有某一名称的任务
/// </summary>
/// <param name="splist"></param>
/// <param name="taskName"></param>
/// <param name="PID">父任务Id</param>
/// <returns></returns>
public static bool SpListHasThisItem(SPList splist, string taskName, double PID)
{
SPQuery query = new SPQuery();
query.Query = @"<Where>
<Eq>
<FieldRef Name='TaskName' />
<Value Type='Text'>" + taskName + @"</Value>
</Eq>
</Where>";
SPListItemCollection items = splist.GetItems(query);
foreach (SPListItem item in items)
{
if (item["PID"].Equals(PID))
{
return true;
}
}
return false;
}
/// <summary>
/// 获取根目录的行数
/// </summary>
/// <param name="dt"></param>
/// <param name="taskName"></param>
/// <returns></returns>
public static int GetRowNumRoot(System.Data.DataTable dt, string taskName)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
if (dt.Rows[i][0].ToString() == taskName)
{
return i;
}
}
return 1;//如何报错。。。
}
/// <summary>
/// 根据任务名找到Id
/// </summary>
/// <param name="splist"></param>
/// <param name="taskName"></param>
/// <returns></returns>
public static int GetTaskID(SPList splist, string taskName)
{
for (int i = 0; i < splist.Items.Count; i++)
{
if (splist.Items[i]["TaskName"].ToString() == taskName)
{
return splist.Items[i].ID;
}
}
return 2;
}
public static int GetColumnNumFC(DataRow dr)
{
for (int i = 0; i < dr.Table.Columns.Count; i++)
{
if (dr[i].ToString().Length >= 1)
{
return i;
}
}
return 3;
}
public static int GetRowNumFC(DataRow dr, System.Data.DataTable dt)
{
int columnNum = GetColumnNumFC(dr);
string taskName = GetTaskName(dr);
for (int i = 0; i < dt.Rows.Count; i++)
{
if (dt.Rows[i][columnNum].ToString() == taskName)
{
return i;
}
}
return 2;
}
public static bool HasFarBrother(int rowNum, int columnNum, System.Data.DataTable dt)
{
int rowCount = dt.Rows.Count;
for (int i = rowNum + 2; i < rowCount; i++)//加2是因为暂时要把有相邻弟兄和不相邻弟兄分开
{
if (dt.Rows[i][columnNum].ToString() != "")
{
if (dt.Rows[i - 1][columnNum-1].ToString() != "")
{
return false;
}
else
{
return true;
}
}
}
return false;
}
public static int GetFarBrotherRowIndex(int rowNum, int columnNum, System.Data.DataTable dt)
{
int rowCount = dt.Rows.Count;
for (int i = rowNum + 2; i < rowCount; i++)
{
if (dt.Rows[i][columnNum].ToString() != "")
{
if (dt.Rows[i - 1][columnNum].ToString() != "")
{
return i;
}
else
{
return i;
}
}
}
return 77;
}
public static DataRow GetFarBrother(int rowNum, int columnNum, System.Data.DataTable dt)
{
int rowCount = dt.Rows.Count;
for (int i = rowNum + 2; i < rowCount; i++)
{
if (dt.Rows[i][columnNum].ToString() != "")
{
if (dt.Rows[i - 1][columnNum].ToString() != "")
{
return dt.Rows[i];
}
else
{
return dt.Rows[i];
}
}
}
//报错
return dt.Rows[1];
}
}
}
