將Excel的資料庫字典導到PDM中

Excel格式

匯入代碼

using System;using System.Collections.Generic;using System.ComponentModel;using System.Data;using System.Drawing;using System.Linq;using System.Text;using System.Windows.Forms;using System.Xml.Linq;using System.Xml;using System.Data.OleDb;namespace ExcelUnionPDM{    public partial class frmMain : Form    {        const string C_ExcelConnTmp = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= {0};Extended Properties='Excel 8.0;HDR=YES;IMEX=1';";        public string ExcelFile        {            get { return @"D:\workspace\doc\系統字典.xls"; }        }        public string Sheet        {            get            {                return "資料表$";            }        }        public string ExcelConnectString        {            get            {                return string.Format(C_ExcelConnTmp, ExcelFile);            }        }        public new List<TableInfo> Tables        {            get;            private set;        }        string C_PDMFile = @"D:\workspace\doc\JLMFG.pdm";        string C_PDMFile_New = @"D:\workspace\doc\JLMFG2.pdm";        public frmMain()        {            InitializeComponent();        }        private void btnImport_Click(object sender, EventArgs e)        {            LoadExcelTableInfo();            SyncData();            MessageBox.Show("OK!");        }        private void SyncData()        {            var doc = XDocument.Load(C_PDMFile);            var tables = doc.Descendants().Where(ent => ent.Name.LocalName == "Table" && ent.Attribute("Id") != null).ToList();            XNamespace aw = "attribute";            XNamespace ow = "object";            foreach (var table in tables)            {                var tableCode=table.Element(aw+"Code").Value.Trim();                var findTable= Tables.Find(ent => string.Compare(ent.Code, tableCode, true) == 0);                if (findTable == null) continue;                table.Element(aw + "Name").SetValue(findTable.Name);                var columns = table.Descendants(ow + "Column").ToList();                //掃描列                foreach (var column in columns.Where(ent => ent.Attribute("Id") != null))                {                    var columnCode = column.Element(aw + "Code").Value.ToString();                    var findColumn = findTable.Columns.Find(ent => string.Compare(ent.Code, columnCode, true) == 0);                    if (findColumn == null) continue;                    if (findColumn.NeedSync)                    {                        if (column.Element(aw + "Comment") == null)                        {                            column.Add(new XElement(aw + "Comment", findColumn.Comment));                        }                        else                        {                            column.Element(aw + "Comment").SetValue(findColumn.Comment);                        }                        column.Element(aw + "Name").SetValue(findColumn.Name);                    }                }            }                   doc.Save(C_PDMFile_New);        }        private void LoadExcelTableInfo()        {            Tables = new List<TableInfo>();            var ds = LoadExcelData();            TableInfo curTable=null;            for (int i = 1; i < ds.Tables[0].Rows.Count; i++)            {                DataRow dr=ds.Tables[0].Rows[i];                if (string.IsNullOrWhiteSpace(dr["新加"].ToString()))//空表示表資訊                {                     curTable = new TableInfo();                    curTable.Name = dr["F2"].ToString().Trim();                    curTable.Code = dr["p_bs"].ToString().Trim();                    Tables.Add(curTable);                    continue;                }                var column = new ColumnInfo();                column.Code = dr["新加"].ToString().Trim();                string tmp = dr["刪除"].ToString();                if (!string.IsNullOrWhiteSpace(tmp))                {                    column.Name = tmp.Trim().Split(" ".ToArray(), StringSplitOptions.RemoveEmptyEntries)[0];                    column.Comment = tmp.Trim();                    if (column.Name.Length >= 10)                    {                        column.Name = column.Name.Substring(0, 10);                    }                    column.NeedSync = true;                }                curTable.Columns.Add(column);            }        }        private DataSet LoadExcelData()        {            DataSet dsExcelSheet = new DataSet();            //開啟並讀取Excel表資訊            OleDbConnection connExecel = new OleDbConnection(ExcelConnectString);            OleDbDataAdapter dpQualityLevel = new OleDbDataAdapter("SELECT * FROM [" + Sheet + "]", connExecel);                 connExecel.Open();            dpQualityLevel.Fill(dsExcelSheet);            connExecel.Close();            return dsExcelSheet;        }    }    #region Class    public class TableInfo    {        public TableInfo()        {            Columns = new List<ColumnInfo>();        }        public string Name { get; set; }        public string Code { get; set; }        public List<ColumnInfo> Columns { get; set; }    }    public class ColumnInfo    {        public string Name { get; set; }        public string Code { get; set; }        public string Comment { get; set; }        /// <summary>        /// 是否需要同步        /// </summary>        public bool NeedSync { get; set; }    }    #endregion}