将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

 

}