将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

}