Excel导入层级

 

 

 

程序显示

 

代码

using System;
using Microsoft.SharePoint;
using Microsoft.SharePoint.WebControls;
using System.Data;
using System.IO;
using System.Linq;
using System.Web;
using System.Collections;
using System.Data.OleDb;
using NuctechProject.DTO.Bll;
using System.Collections.Generic;

namespace NuctechProject.Layouts.Project
{
    public partial class IntroductionPlan : LayoutsPageBase
    {
        ProjectBll proBll = new ProjectBll();
        string url = Common.rootUrl;
        private string _strConn; //导入excel时的连接
        string pmurl = Common.proUrl;
        private UserBLL bll = new UserBLL();
        protected void Page_Load(object sender, EventArgs e)
        {
            hidProid.Value = Request.QueryString["proid"];
        }

        protected void BtnOK_Click(object sender, EventArgs e)
        {
            DataTable excelTable = null;

            SPSecurity.RunWithElevatedPrivileges(delegate
            {
                if (BaseInfoTemplateFile.HasFile)
                {

                    List<string> noInput = new List<string>();
                    string strLoginName = HttpContext.Current.User.Identity.Name; //获取用户名
                    string folderTemp = strLoginName.Substring(strLoginName.LastIndexOf('\\') + 1);
                    try
                    {
                        string extension = Path.GetExtension(BaseInfoTemplateFile.FileName); //获取文件的后缀
                        if (extension != null)
                        {
                            string fileException = extension.ToLower();
                            if (fileException == ".xlsx" || fileException == ".xls")
                            {
                                #region 读取Excel
                                string fileFolder = Server.MapPath("~/_layouts/15/images/" + folderTemp + "Upfile/");
                                if (!Directory.Exists(fileFolder)) //根目录
                                {
                                    Directory.CreateDirectory(fileFolder); //判断上传目录是否存在     自动创建
                                }
                                BaseInfoTemplateFile.SaveAs(Server.MapPath("~/_layouts/15/images/" + folderTemp + "Upfile/" + BaseInfoTemplateFile.FileName));
                                string strFilepathNmae = Server.MapPath("~/_layouts/15/images/" + folderTemp + "Upfile/" + BaseInfoTemplateFile.FileName);
                                string strExcel = ExcelSheetName(strFilepathNmae)[0].ToString();
                                excelTable = ExcelDataSource(strFilepathNmae, strExcel).Tables[0];
                                #endregion
                                //data是excel的数据
                                DataTable data = ExcelDataSource(strFilepathNmae, strExcel).Tables[0];
                                if (data != null)
                                {
                                    //try
                                    //{
                                    proBll.DeleteProject(Convert.ToInt32(this.hidProid.Value));
                                    using (SPWeb taskWeb = new SPSite(pmurl).OpenWeb())
                                    {
                                        SPList taskList = taskWeb.GetList(pmurl + "/Lists/" + this.hidProid.Value + "/AllItems.aspx");
                                        //总层级
                                        int levelCount = GetLevel(data, data.Rows[0]);
                                        //去掉前两行
                                        bool isFirstRow = true;
                                        //排序
                                        int orderID = 0;
                                        //上一个是第几层
                                        int beforLevel = 0;
                                        int beforPid = 0;
                                        int id = 0;
                                        List<Temp> tempList = new List<Temp>();
                                        foreach (DataRow row in data.Rows)
                                        {
                                            if (isFirstRow)
                                            {
                                                isFirstRow = false;
                                                continue;
                                            }
                                            int leve = 0;
                                            orderID++;
                                            taskWeb.AllowUnsafeUpdates = true;
                                            SPListItem item = taskList.AddItem();
                                            string taskName = row[0].ToString();
                                            //如果里程碑为空，就遭下面几级
                                            if (string.IsNullOrEmpty(taskName))
                                            {
                                                for (int i = 1; i <= levelCount; i++)
                                                {
                                                    taskName = row[i].ToString();
                                                    if (!string.IsNullOrEmpty(taskName))
                                                    {
                                                        leve = i;
                                                        break;
                                                    }
                                                }
                                            }
                                            #region MyRegion
                                            item["TaskName"] = taskName;
                                            if (leve == 0)
                                            {
                                                item["ImportantNode"] = true;
                                            }
                                            else
                                            {
                                                item["ImportantNode"] = false;
                                            }
                                            item["FileID"] = row[levelCount + 1].ToString();//文件编号

                                            SPFieldUserValue spuservale = bll.GetUserByName(row[levelCount + 2].ToString());
                                            if (spuservale != null)
                                            {
                                                item["AssignedTo"] = spuservale;//负责人
                                            }
                                            if (string.IsNullOrEmpty(row[levelCount + 3].ToString()) == false)
                                            {
                                                item["StartDate"] = Convert.ToDateTime(row[levelCount + 3]);
                                            }

                                            if (string.IsNullOrEmpty(row[levelCount + 4].ToString()) == false)
                                            {
                                                item["DueDate"] = Convert.ToDateTime(row[levelCount + 4]);
                                            }
                                            if (string.IsNullOrEmpty(row[levelCount + 5].ToString()) == false)
                                            {
                                                item["ActuaStartDate"] = Convert.ToDateTime(row[levelCount + 5]);
                                            }

                                            if (string.IsNullOrEmpty(row[levelCount + 6].ToString()) == false)
                                            {
                                                item["ActuaDueDate"] = Convert.ToDateTime(row[levelCount + 6]);
                                            }
                                            if (string.IsNullOrEmpty(row[levelCount + 7].ToString()) == false)
                                            {
                                                item["OutputFile"] = row[levelCount + 7].ToString();
                                            }
                                            if (string.IsNullOrEmpty(row[levelCount + 8].ToString()) == false)
                                            {
                                                item["Status"] = row[levelCount + 8].ToString();
                                            }
                                            spuservale = bll.GetUserByName(row[levelCount + 9].ToString());
                                            if (spuservale != null)
                                            {
                                                item["CloseUser"] = spuservale;
                                            }
                                            if (string.IsNullOrEmpty(row[levelCount + 10].ToString()) == false)
                                            {
                                                item["CloseDate"] = Convert.ToDateTime(row[levelCount + 10]);
                                            }

                                            if (string.IsNullOrEmpty(row[levelCount + 11].ToString()) == false)
                                            {
                                                item["Body"] = row[levelCount + 11].ToString();
                                            }
                                            #endregion
                                            if (leve == 0)
                                            {
                                                item["PID"] = 0;
                                                tempList.Clear();
                                            }
                                            else if (leve == beforLevel)
                                            {
                                                item["PID"] = beforPid;
                                            }
                                            else
                                            {
                                                if (leve > beforLevel)
                                                {
                                                    item["PID"] = id;
                                                }
                                                if (leve < beforLevel)
                                                {
                                                    Temp t = tempList.FirstOrDefault<Temp>(x=>x.Levle==leve);
                                                    item["PID"] = t.Pid;
                                                }
                                            }
                                            item["VersionState"] = "草稿";
                                            item["Version"] = proBll.GetLastVasionNumber(Convert.ToInt32(this.hidProid.Value));
                                            item["OrderID"] = orderID;
                                            item.Update();
                                            id = item.ID;
                                            beforPid = Convert.ToInt32(item["PID"]);
                                            beforLevel = leve;
                                            if (tempList.Count(x => x.Levle == leve) == 0)
                                            {
                                                tempList.Add(new Temp { ID = id, Levle = leve, Pid = beforPid });
                                            }
                                        }

                                    }
                                }
                                //}
                                //catch (Exception)
                                //{
                                //    Page.ClientScript.RegisterStartupScript(Page.ClientScript.GetType(), "myscript", "<script  type='text/javascript'>$.ligerDialog.closeWaitting();alert('Excel表列名与系统不符合，请检查Excel表列名！');</script>");
                                //    return;
                                //}
                            }

                            else
                            {
                                Page.ClientScript.RegisterStartupScript(Page.ClientScript.GetType(), "myscript", "<script  type='text/javascript'>$.ligerDialog.closeWaitting();alert('您选择的文件不是Excel格式！');</script>");
                                return;
                            }
                        }

                    }
                    finally //最终要把临时存储的文件删除
                    {
                        string strFileFolder = Server.MapPath("~/_layouts/15/images/" + folderTemp + "Upfile/");
                        if (Directory.Exists(strFileFolder)) //根目录
                        {
                            //Directory.CreateDirectory(strFileFolder);//判断上传目录是否存在     自动创建
                            Directory.Delete(strFileFolder, true);
                        }

                        Page.ClientScript.RegisterStartupScript(Page.ClientScript.GetType(), "myscript", "<script  type='text/javascript'>ReturnPageValue();</script>");

                    }
                }
                else
                {
                    Page.ClientScript.RegisterStartupScript(Page.ClientScript.GetType(), "myscript", "<script  type='text/javascript'>$.ligerDialog.closeWaitting();alert('请选择导入文件！');</script>");
                    return;
                }
            });
        }
        /// <summary>
        /// 获取总层级
        /// </summary>
        /// <param name="dr"></param>
        /// <returns></returns>
        public int GetLevel(DataTable dt, DataRow row)
        {
            int count = 0;
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                if (row[i].ToString().Contains("文件编号"))
                {
                    count = i - 1;
                    break;
                }
            }
            return count;
        }
        protected void BtnClose_Click(object sender, EventArgs e)
        {
            Page.ClientScript.RegisterStartupScript(Page.ClientScript.GetType(), "myscript", "<script  type='text/javascript'>ReturnPageValue();</script>");
        }
        /// <summary>
        /// 连接到Excel
        /// </summary>
        /// <param name="filepath">文件路径</param>
        /// <param name="sheetname">sheet名字</param>
        /// <returns></returns>
        public DataSet ExcelDataSource(string filepath, string sheetname)
        {
            _strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filepath +
                       ";Extended Properties='Excel 12.0;HDR=YES'";
            new OleDbConnection(_strConn);
            var oada = new OleDbDataAdapter("select * from [" + sheetname + "]", _strConn);
            var ds = new DataSet();
            oada.Fill(ds);
            return ds;
        }
        /// <summary>
        /// 获得Excel中的所有sheetname
        /// </summary>
        /// <param name="filepath">文件路径</param>
        /// <returns></returns>
        public ArrayList ExcelSheetName(string filepath)
        {
            _strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filepath +
                       ";Extended Properties='Excel 12.0;HDR=YES'";
            var al = new ArrayList();
            var conn = new OleDbConnection(_strConn);
            conn.Open();
            DataTable sheetNames = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
                new object[] { null, null, null, "TABLE" });
            conn.Close();
            if (sheetNames != null)
            {
                foreach (DataRow dr in sheetNames.Rows)
                {
                    al.Add(dr[2]);
                }
            }
            return al;
        }
    }

    public class Temp
    {
        public int Levle { get; set; }
        public int Pid { get; set; }
        public int ID { get; set; }
    }
}