BOM表格的数据库导入--一种树形结构

BOM表的数据大体样式

设计的mysql 模型

要求:1.BOM表数据大概有1W~2W的量,导入效率优先级最高;2.代号存入物号表,前端有A0018是不能有重复数据的;

begin:基本思路就是:

1.划分BOM树的层级,一级的数据存入dictionary[0],二级的数据存入dictionary[1].....依次类推;

2.保存父级数据,如dictionary[0],(这里避免频繁打开数据库操作,用的StringBuilder sql = new StringBuilder();sql.Append(),拼成一个完成的MySql 语句存入数据库,StringBuilder 比 String 执行效率要快很多);

3.保存第二层级(子集时)

 Hashtable table =
                            Plist.Where(p => p["oldNO"].ToString() == pidstr).First();
                    int pid = Convert.ToInt32(table["BOMID"] + "");

遍历 第二层级的数据,用linq 语句查找每个的父级,对应上;

4.这里获取父级的ID,思路是 先一起保存,然后再一起查询出来,将需要的数据存入list<>集合中;

List<Hashtable> Tlist = new List<Hashtable>();
　　　　　　if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                for (int j = 0; j < ds.Tables[0].Rows.Count; j++)
                {
                    DataRow drRows = ds.Tables[0].Rows[j];
                    #region 存入集合
                    Hashtable table = new Hashtable();
                    table.Add("BOMID", drRows["ID"]);
                    table.Add("oldNO", drRows["OldID"]);
                    Tlist.Add(table);
                    #endregion
                }
            }
return Tlist;

5.尽量减少循环里面套循环,一个循环,能对数据进行处理完,最好都放在同一个循环里面处理.

完整代码:

using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.Web;
using System.IO;
using System.Linq;
using NPOI.HSSF.UserModel;
using System.Text;
using System.Web.UI.WebControls;
using System.Xml.Schema;


namespace Md.Api.Web
{
    /// <summary>
    /// ExcelFileUploadHQL 的摘要说明
    /// </summary>
    public class ExcelFileUploadHQL : IHttpHandler
    {
        private static int CompID { get; set; }
        private static int CreateUser { get; set; }
        private static int ProjectID { get; set; }
        private static string ProjectCode { get; set; }

        private string Path
        {
            get
            {
                string path = ConfigurationManager.AppSettings["filepath"];

                try
                {
                    path = HttpContext.Current.Server.MapPath(path);
                }
                catch (Exception)
                {
                }
                return path;
            }
        }

        /// <summary>
        /// 接收请求
        /// </summary>
        /// <param name="context"></param>
        public void ProcessRequest(HttpContext context)
        {
            string result = "";
            context.Response.ContentType = "text/plain";
            HttpPostedFile postFile = context.Request.Files["Filedata"];
            CompID = Convert.ToInt32(context.Request["CompID"] + "");
            CreateUser = Convert.ToInt32(context.Request["CreateUser"] + "");
            ProjectID = Convert.ToInt32(context.Request["ProjectID"] + "");
            ProjectCode = context.Request["ProjectCode"] + "";

            string excelName = postFile.FileName;
            string savePath = "";

            try
            {

                /*文件是否有内容*/
                if (postFile != null && postFile.ContentLength > 0)
                {
                    if (string.IsNullOrEmpty(excelName))
                    {
                        excelName = Guid.NewGuid().ToString("N") + System.IO.Path.GetExtension(postFile.FileName);
                    }

                    if (!File.Exists(Path))
                    {
                        System.IO.Directory.CreateDirectory(Path);
                    }

                    string Mess = "";
                    savePath = Path + excelName;
                    postFile.SaveAs(savePath);

                    if (Mess != "")
                    {
                        //错误输出
                        context.Response.Write("false@" + Mess);
                    }
                    
                    Mess = DealExcelForHangQiLun(LoadDataFromExcel(savePath));
                    if (string.IsNullOrEmpty(Mess))
                    {
                        Mess = "导入成功!" + "@true";
                    }
                    result = "true@" + Mess;
                }
            }
            catch (Exception ex)
            {
                result = "false@上传失败!" + ex.ToString();
            }
            context.Response.Write(result);
        }

        public static string DealExcelForHangQiLun(DataSet ds)
        {
            try
            {
                string errorRow = "";
                string repeatstr = "";
                int clevel = 0;
                Dictionary<string, ExcelModel> goodsdictionary = new Dictionary<string, ExcelModel>();

                if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                {
                    //获取Excel 最大层级并分级
                    Dictionary<int, List<ExcelModel>> dictionary = GetExcelDataByClevel(ds, out clevel, out errorRow, out repeatstr, out goodsdictionary);
                    
                    if (string.IsNullOrEmpty(errorRow) && string.IsNullOrEmpty(repeatstr))
                    {
                        if (clevel > 0)
                        {
                            //根据项目编号删除BOM
                            new Md.HangQiLun().DeleteListByColunm("ProjectID", ProjectID.ToString());
                            //根据项目编号删除物号信息表(HasProjectCode=0)
                            new Md.HangQiLun().DeleteGoodsList(ProjectID, CompID);
                            //批量存储物号信息
                            Dictionary<string, int> goodsdic = SaveGoodsInfo(goodsdictionary);
                            //keeplist = new List<List<Hashtable>>();
                            if (dictionary.Count > 0)
                            {
                                RecursiveSave(dictionary, goodsdic);
                            }
                        }
                    }
                    else
                    {
                        
                        //排除自身重复的数字
                        if (!string.IsNullOrEmpty(errorRow))
                        {
                            string errorstr = DeduplicationData(errorRow.TrimEnd(','));
                            errorRow = "此Excel表格中," + "第 " + errorstr + " 行的序号或者代号不能为空";
                        }
                        if (!string.IsNullOrEmpty(errorRow) && !string.IsNullOrEmpty(repeatstr))
                        {
                            errorRow += ",且";
                        }
                        if (!string.IsNullOrEmpty(repeatstr))
                        {
                            errorRow += "此Excel表格中," + repeatstr;
                        }
                        errorRow += ",请检查修正后,重新导入!@false";
                    }
                }
                else
                {
                    errorRow = "此Excel表格没有任何数据!" + "@false";
                }
                return errorRow;
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        /// <summary>
        /// 处理重复的数据,返回错误行数
        /// </summary>
        /// <param name="errorRow">excel表格</param>.
        private static string DeduplicationData(string errorRow)
        {
            //1.把字符串转换成数组
            string[] str = errorRow.Split(',');

            //2.定义ArrayList,判断数组中重复字段不添加到 ArrayList对象中
            ArrayList array = new ArrayList();
            for (int i = 0; i < str.Length; i++)
            {
                //判断是否已经存在
                if (array.Contains(str[i]) == false)
                {
                    array.Add(str[i]);
                }
            }

            //3.将arrayList转换成数组
            str = new string[array.Count];
            str = (string[])array.ToArray(typeof(string));

            //4.字节数组转换成字符串
            string mess = "";
            for (int i = 0; i < str.Length; i++)
            {
                mess += str[i] + ",";
            }

            return mess.TrimEnd(',');
        }

        /// <summary>
        /// 根据层级将Excel表的分级处理
        /// </summary>
        /// <param name="ds">excel表格</param>.
        /// <param name="clevel">层级</param>
        /// <param name="errorRow">序号/代号不能为空</param>
        /// <param name="repeatstr">不能有重复的序号</param>
        /// <param name="goodsdictionary">物号集合</param>
        private static Dictionary<int, List<ExcelModel>> GetExcelDataByClevel(DataSet ds, out int clevel, out string errorRow, out string repeatstr, out Dictionary<string, ExcelModel> goodsdictionary)
        {
            #region 定义变量
            errorRow = "";
            repeatstr = "";
            clevel = 0;
            string str = string.Empty;
            ArrayList array = new ArrayList();
            goodsdictionary = new Dictionary<string, ExcelModel>();
            Dictionary<int, List<ExcelModel>> dictionary = new Dictionary<int, List<ExcelModel>>();
            int dsRows = ds.Tables[0].Rows.Count;
            #endregion

            for (int i = 0; i < dsRows; i++)
            {
                #region model赋值
                DataRow item = ds.Tables[0].Rows[i];
                string cols1 = item[0].ToString();
                string cols2 = item[1].ToString();
                ExcelModel excelmodel = new ExcelModel();
                excelmodel.No = cols1;
                excelmodel.GNumCode = cols2;
                excelmodel.GnumName = item[2].ToString();
                excelmodel.DocNo = item[3].ToString();
                excelmodel.Num = Convert.ToInt32(item[4].ToString());
                excelmodel.Stuff = item[5].ToString();
                excelmodel.Weight = Convert.ToDouble(item[6]);
                excelmodel.AllWeight = Convert.ToDouble(item[7]);
                excelmodel.Level = item[0].ToString().Split('.').Length;
                #endregion

                #region 数据处理
                //遍历excel 序号和代号,不能为空
                if (string.IsNullOrEmpty(cols1) || string.IsNullOrEmpty(cols2))
                {
                    errorRow += (i + 3) + ",";
                }
                //遍历excel 序号不能有重复的
                if (array.Contains(cols1) == false)
                {
                    array.Add(cols1);
                }
                //获取最大层级
                if (str.Length < item[0].ToString().Length)
                {
                    str = item[0].ToString();
                }
                #endregion

                #region 物号(代号)数据处理
                if (goodsdictionary.Keys.Contains(cols2) == false)
                {
                    goodsdictionary.Add(cols2, excelmodel);
                }
                #endregion

                #region 数据分级处理
                List<ExcelModel> listModel = new List<ExcelModel>();
                if (dictionary.Keys.Contains(excelmodel.Level) == false)
                {
                    listModel.Add(excelmodel);
                    dictionary.Add(excelmodel.Level, listModel);
                }
                else
                {
                    listModel = dictionary[excelmodel.Level];
                    listModel.Add(excelmodel);
                }
                #endregion
            }
            #region 返回值
            if (dsRows != array.Count)
            {
                repeatstr = "存在重复的序号";
            }
            if (!string.IsNullOrEmpty(str))
            {
                clevel = str.Split('.').Length;
            }
            return dictionary;
            #endregion
        }
       
        /// <summary>
        /// 保存并获取物号信息
        /// </summary>
        /// <param name="goodsdictionary"></param>
        /// <returns></returns>
        private static Dictionary<string, int> SaveGoodsInfo(Dictionary<string, ExcelModel> goodsdictionary)
        {
            Dictionary<string, int> goodsdic = new Dictionary<string, int>();
            StringBuilder sql = new StringBuilder();
            foreach (var item in goodsdictionary)
            {
                ExcelModel model = item.Value;
                string projectcode = model.GNumCode.Substring(0, model.GNumCode.IndexOf("-"));//项目编号
                string hasprojectcode = "";
                if (ProjectCode == projectcode)
                {
                    hasprojectcode = "0";//物号中是否拼接项目编号 0 拼接
                }
                else
                {
                    hasprojectcode = "1";//物号中是否拼接项目编号 1不拼接
                }
                int isexist = 0;
                if (hasprojectcode == "1")
                {
                    //查询该不拼接的物号是否已存在数据库中
                    isexist = new Md.HangQiLun().GetGoodsList(CompID, model.GNumCode);
                }
                if (isexist > 0)
                {
                    if (goodsdic.Keys.Contains(model.GNumCode) == false)
                    {
                        goodsdic.Add(model.GNumCode, isexist);
                    }
                }
                else
                {
                    sql.Append("('" + model.GnumName + "', '" + model.GNumCode + "', " + ProjectID + ", '" + hasprojectcode + "', " + CompID + "),");
                }
            }
            //存入库 -取数据
            if (sql.Length > 0)
            {
                sql.Remove(sql.Length - 1, 1);
                DataSet ds = new Md.HangQiLun().BatchSave(sql.ToString(), CompID, ProjectID);
                if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                {
                    for (int j = 0; j < ds.Tables[0].Rows.Count; j++)
                    {
                        DataRow drRows = ds.Tables[0].Rows[j];
                        string gnumcode = drRows["GNumCode"].ToString();
                        int gnumid = Convert.ToInt32(drRows["ID"].ToString());
                        if (goodsdic.Keys.Contains(gnumcode) == false)
                        {
                            goodsdic.Add(gnumcode, gnumid);
                        }
                    }
                }
            }
            return goodsdic;
        }


        /// <summary>
        /// 递归遍历
        /// </summary>
        /// <param name="ds">excel表格</param>.
        /// <param name="Tlist">父级集合</param>
        /// <param name="clevel">层级</param>
        /// <param name="x">从第几个循环</param>
        /// 
        public static void RecursiveSave(Dictionary<int, List<ExcelModel>> dictionary, Dictionary<string, int> goodsdic, int level = 1, List<Hashtable> Tlist = null, int SortID = 999)
        {
            try
            {
                if (dictionary.Keys.Contains(level))
                {
                    if (level == 1)
                    {
                        int sorid = 999;
                        List<Hashtable> list = SaveHangQiLun(dictionary[1], goodsdic, out sorid);
                        RecursiveSave(dictionary, goodsdic, 2, list, sorid);
                    }
                    else
                    {
                        int sorid = 999;
                        List<Hashtable> list = SaveHangQiLun(dictionary[level], goodsdic, out sorid, Tlist);
                        level++;
                        RecursiveSave(dictionary, goodsdic, level, list);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        /// <summary>
        /// 保存同级别的数据
        /// </summary>
        /// <param name="dictionary"></param>
        /// <param name="goodsdic">物号信息</param>
        /// <param name="sorid">返回排序数</param>
        /// <param name="Plist">父级</param>
        /// <param name="SortID">排序数</param>
        /// <returns></returns>
        private static List<Hashtable> SaveHangQiLun(List<ExcelModel> dictionary, Dictionary<string, int> goodsdic, out int sorid, List<Hashtable> Plist=null, int SortID = 999)
        {
            List<Hashtable> Tlist = new List<Hashtable>();
            StringBuilder sql = new StringBuilder();

            for (int i = 0; i < dictionary.Count; i++)
            {
                ExcelModel model = dictionary[i];

                #region 保存BOM表
                int goodsID = 0;
                if (goodsdic.Keys.Contains(model.GNumCode) == true)
                {
                    goodsID = goodsdic[model.GNumCode];
                }
                
                if (Plist == null)
                {
                    model.PID = 0;
                }
                else
                {
                    string pidstr = model.No.Substring(0, model.No.LastIndexOf('.'));
                    Hashtable table =
                            Plist.Where(p => p["oldNO"].ToString() == pidstr).First();
                    int pid = Convert.ToInt32(table["BOMID"] + "");
                    model.PID = pid;
                }
                SortID++;
                sql.Append("(" + model.PID + "," + model.Num + "," + goodsID + ",'" + model.Stuff + "'," + model.Weight + "," + model.AllWeight + "," + CreateUser + ",'" + DateTime.Now + "'," + ProjectID + ",'0'," + SortID + ",'" + model.No + "'," + CompID + "),");
                #endregion
            }
            sorid = SortID;
            //存入le_bom表,并取数据
            sql.Remove(sql.Length - 1, 1);
            DataSet ds = new Md.HangQiLun().BatchSaveBom(sql.ToString(), CompID, ProjectID);
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                for (int j = 0; j < ds.Tables[0].Rows.Count; j++)
                {
                    DataRow drRows = ds.Tables[0].Rows[j];
                    #region 存入集合
                    Hashtable table = new Hashtable();
                    table.Add("BOMID", drRows["ID"]);
                    table.Add("oldNO", drRows["OldID"]);
                    Tlist.Add(table);
                    #endregion
                }
            }
            return Tlist;
        }


        /// <summary>
        /// Excel解读至DataSet
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        public static DataSet LoadDataFromExcel(string filePath)
        {
            try
            {
                string strConn;
                strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath +
                          ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1;'";
                OleDbConnection OleConn = new OleDbConnection(strConn);
                OleConn.Open();

                string tbName = OleConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0][2].ToString().Trim();
                String sql = "SELECT * FROM  [" + tbName + "A2:H65535" + "]"; //可是更改Sheet名称，比如sheet2，等等   
                OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn);
                DataSet OleDsExcle = new DataSet();
                OleDaExcel.Fill(OleDsExcle, tbName);
                OleConn.Close();
                return OleDsExcle;
            }
            catch (Exception ex)
            {
                return null; //"数据绑定Excel失败!失败原因：" + err.Message + "提示信息";
            }
        }

        /// <summary>
        /// 定义class 类
        /// </summary>
        /// <returns></returns>
        public class ExcelModel
        {
            /// <summary>
            /// 序号
            /// </summary>
            public string No { get; set; }
            /// <summary>
            /// 物号编号
            /// </summary>
            public string GNumCode { get; set; }
            /// <summary>
            /// 物号名称
            /// </summary>
            public string GnumName { get; set; }
            /// <summary>
            /// 资料号
            /// </summary>
            public string DocNo { get; set; }
            /// <summary>
            /// 物号数量
            /// </summary>
            public int Num { get; set; }
            /// <summary>
            /// 材料
            /// </summary>
            public string Stuff { get; set; }
            /// <summary>
            /// 单物重量
            /// </summary>
            public Double Weight { get; set; }
            /// <summary>
            /// 总重量
            /// </summary>
            public Double AllWeight { get; set; }
            /// <summary>
            /// 层级
            /// </summary>
            public int Level { get; set; }
            /// <summary>
            /// 父级ID
            /// </summary>
            public int PID { get; set; }
        }
        public bool IsReusable
        {
            get
            {
                return false;
            }
        }
    }
}