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
}