public DataSet Import(string path, StandardDef standard)
{
try
{
string domainName = Path.GetFileNameWithoutExtension(path);
DomainDef domain = standard.DomainContainer.Find(x => x.Code == domainName | x.LangStr == domainName);
DataSet ds = new DataSet(domainName);
IWorkbook wb = ReadWorkbook(path);
List <string> sheetNames = GetSheetNames(wb);
//sheetNames equal to objectName
foreach (string sheetName in sheetNames)
{
DGObjectDef objectDef = standard.GetDGObjectDefByName(sheetName);
DataTable dt = ReadSheet(wb.GetSheet(sheetName), objectDef);
if (dt != null)
{
ds.Tables.Add(dt);
}
}
return(ds);
}
catch (Exception)
{
System.Windows.MessageBox.Show("Check if the Standard adapt to data");
return(null);
}
}
public bool Insert(DGObjectDef objectDef, DataTable table)
{
try
{
for (int i = 0; i < table.Rows.Count; i++)
{
string sql = "INSERT INTO " + objectDef.Code;
string column = "s(";
string value = " VALUES(";
foreach (PropertyMeta meta in objectDef.PropertyContainer)
{
string dataCell = table.Rows[i][meta.PropertyName].ToString();
if (dataCell != null)
{
column += meta.PropertyName + ",";
value += "'" + dataCell + "',";
}
}
column = column.TrimEnd(',') + ") ";
value = value.TrimEnd(',') + ") ";
sql += column + value;
//db.Boreholes.SqlQuery(sql);
//db.Database.ExecuteSqlCommand(sql);
////ExecuteSqlCommand(sql);
//db.SaveChanges();
}
return(true);
}
catch (Exception e)
{
System.Windows.MessageBox.Show(e.Message);
return(false);
}
}
public void Data2DB(DataSet dataSet, DataStandardDef standardDef)
{
try
{
string domainName = dataSet.DataSetName;
DomainDef domain = standardDef.DomainContainer.Find(x => x.Code == domainName);
if (domain != null)
{
// get current assembly(程序集)
//Assembly assembly = Assembly.GetExecutingAssembly();
//create Entity for specific domain
//dynamic db = assembly.CreateInstance("iS3_DataManager.DataManager." + domainName + "DB_EF");
GeologyDB_EF db = new GeologyDB_EF();
foreach (DataTable table in dataSet.Tables)
{
DGObjectDef objectDef = domain.DGObjectContainer.Find(x => x.Code == table.TableName);
Insert(db, objectDef, table);
}
}
System.Windows.MessageBox.Show("数据导入成功");
}
catch (Exception e)
{
System.Windows.MessageBox.Show(e.Message);
}
}
/// <summary>
/// just for test
/// </summary>
/// <returns></returns>
public DataStandardDef GetSample()
{
//定义隧道数据标准和地质域
DataStandardDef dsDef = new DataStandardDef()
{
Code = "TunnelStandard",
Description = "This a Tunnel DataStandard",
};
DomainDef ddDef = new DomainDef()
{
Code = "Geology",
Desciption = "This a Geology Domain",
};
dsDef.DomainContainer.Add(ddDef);
//定义地质域内的数据结构
DGObjectDef dgDef = new DGObjectDef()
{
Code = "Borehole",
Desctiption = "This a Borehole DGObject"
};
ddDef.DGObjectContainer.Add(dgDef);
//定义钻孔中的属性内容
dgDef.PropertyContainer.Add(new PropertyMeta("ID", "Int", null, "这是编号字段", "['zh':'编号','en':'ID']", IsKey: true, regularExpression: @"\d"));
dgDef.PropertyContainer.Add(new PropertyMeta("BoreholeID", "string", null, "这是钻孔编号", "['zh':'钻孔编号','en':'BoreholeID']", true, regularExpression: @""));
dgDef.PropertyContainer.Add(new PropertyMeta("BoreholeTime", "dateTime", null, "这是钻孔时间", "['zh':'钻孔时间','en':'BoreholeTime']", true));
dgDef.PropertyContainer.Add(new PropertyMeta("BoreholeDepth", "double", "m", "这是钻孔深度", "['zh':'钻孔深度','en':'BoreholeDepth']", true));
return(dsDef);
}
public void Data2DataBase(DataSet dataSet, StandardDef standard)
{
try
{
foreach (DataTable table in dataSet.Tables)
{
DGObjectDef dGObject = standard.GetDGObjectDefByName(table.TableName);
string typeName = "iS3_DataManager.ObjectModels." + dGObject.Code;
Type type = Type.GetType(typeName);
var properties = type.GetProperties();
int count = properties.Length;
foreach (DataRow row in table.Rows)
{
object[] project = new object[35];
int index = 0;
foreach (PropertyMeta meta in dGObject.PropertyContainer)
{
project[index++] = row[meta.LangStr];
}
var instance = Activator.CreateInstance(type, project);
}
}
}
catch (Exception x)
{
throw x;
}
}
private DataTable CleanTable(DataTable table, DGObjectDef objectDef)
{
DataTable tmpTable = DeduplicateTable(table, objectDef);
tmpTable = RemoveError(tmpTable, objectDef);
return(tmpTable);
}
public DataSet Import(string path, StandardDef standard)
{
//try
//{
string domainName = Path.GetFileNameWithoutExtension(path);
// StageDef domain = standard.StageContainer.Find(x => x.Code == domainName | x.LangStr == domainName);
DataSet ds = new DataSet()
{
DataSetName = domainName
};
try
{
IWorkbook wb = ReadWorkbook(path);
List <string> sheetNames = GetSheetNames(wb);
//sheetNames equal to objectName
foreach (string sheetName in sheetNames)
{
DGObjectDef objectDef = standard.GetDGObjectDefByName(sheetName);
DataTable dt = ReadSheet(wb.GetSheet(sheetName), objectDef);
if (dt != null)
{
ds.Tables.Add(dt);
}
}
return(ds);
}
catch (Exception ex)
{
return(null);
}
}
private DataTable RemoveError(DataTable dataTable, DGObjectDef objectDef)
{
DataTable tmpTable = dataTable;
foreach (PropertyMeta meta in objectDef.PropertyContainer)
{
for (int j = 0; j < dataTable.Rows.Count; j++)
{
DataRow row = dataTable.Rows[j];
if ((meta.Nullable == false | meta.IsKey == true) & (row[meta.LangStr].ToString() == null | row[meta.LangStr].ToString() == ""))
{
tmpTable.Rows.RemoveAt(j);//Delete rows which lack of key values;
continue;
}
if (meta.RegularExp != null)
{
var data = row[meta.LangStr].ToString();
bool reult1 = (data != "" & data != null);
bool result = Regex.IsMatch(row[meta.LangStr].ToString(), meta.RegularExp);
if (reult1 & !result)
{
tmpTable.Rows.RemoveAt(j);//delete error imformation
}
}
}
dataTable = tmpTable;
}
return(tmpTable);
}
public bool Clean()
{
try
{
_dataSet = dataSet;
_dataTable = dataTable;
ThreadStart start = new ThreadStart(Save2Local);//async save data to local
Thread t = new Thread(start);
t.Start();
if (dataSet != null)
{
DataSet tmpDS = new DataSet(dataSet.DataSetName);
foreach (DataTable table in dataSet.Tables)
{
DGObjectDef objectDef = standardDef.GetDGObjectDefByName(table.TableName);
DataTable dt = CleanTable(table, objectDef);
tmpDS.Tables.Add(dt);
}
this.dataSet = tmpDS;
}
else if (dataTable != null)
{
DGObjectDef objectDef = standardDef.GetDGObjectDefByName(dataTable.TableName);
dataTable = CleanTable(dataTable, objectDef);
}
return(true);
}
catch (Exception e)
{
System.Windows.MessageBox.Show(e.Message);
return(false);
}
}
private void AddButton(int i, int j)
{
if (i < this.DataDG.Items.Count)
{
DataRowView drv = DataDG.Items[i] as DataRowView;
DataGridRow row = GetRow(DataDG, i);
DataGridCellsPresenter presenter = GetVisualChild <DataGridCellsPresenter>(row);
DataGridCell cell = (DataGridCell)presenter.ItemContainerGenerator.ContainerFromIndex(j);
DGObjectDef objectDef = standard.GetDGObjectDefByName(dataTable.TableName);
DataFileService dfs = new DataFileService(objectDef.Code, IDindex[i]);
StackPanel _stackpanel = new StackPanel();
_stackpanel.Orientation = Orientation.Horizontal;
MyButton _button1 = new MyButton();
_button1.Content = "下载附件";
_button1._dfs = dfs;
_button1.Click += _button1._dfs.Downloadfile;
MyButton _button2 = new MyButton();
_button2.Content = "上传附件";
_button2._dfs = dfs;
_button2.Click += _button2._dfs.Uploadfile;
_stackpanel.Children.Add(_button2);
_stackpanel.Children.Add(_button1);
cell.Content = _stackpanel;
}
}
private void GenerateNodes(Tunnel tunnel, StandardDef Standard)
{
List <TreeNode> nodes = new List <TreeNode>();
int index = 0;
foreach (Stage stage in tunnel.Stages)
{
TreeNode stageTreeNode = new TreeNode()
{
NodeID = index++, Level = 1, Context = stage.LangStr, isExpanded = true
};
foreach (Category category in stage.Categories)
{
TreeNode categoryTreeNode = new TreeNode()
{
NodeID = index++, Level = 2, Context = category.LangStr, isExpanded = true
};
foreach (string obj in category.objList)
{
DGObjectDef dGObject = Standard.GetDGObjectDefByCode(obj);
TreeNode objTreeNode = new TreeNode()
{
NodeID = index++, Level = 3, Context = dGObject.LangStr
};
categoryTreeNode.ChildNodes.Add(objTreeNode);
}
stageTreeNode.ChildNodes.Add(categoryTreeNode);
}
nodes.Add(stageTreeNode);
}
TreeNodes = nodes;
}
public bool Check()
{
try
{
if (dataSet != null)
{
StageDef domain = standardDef.StageContainer.Find(x => x.Code == dataSet.DataSetName);
foreach (DataTable table in dataSet.Tables)
{
DGObjectDef objectDef = domain.DGObjectContainer.Find(x => x.LangStr == table.TableName);
CheckRows(table, objectDef);
}
}
else if (dataTable != null)
{
DGObjectDef objectDef = standardDef.GetDGObjectDefByName(dataTable.TableName);
CheckRows(dataTable, objectDef);
}
return(true);
}
catch (Exception e)
{
System.Windows.MessageBox.Show(e.Message);
return(false);
}
}
public DataSet Import(string path, DataStandardDef standard)
{
try
{
string domainName = Path.GetFileNameWithoutExtension(path);
DomainDef domain = standard.DomainContainer.Find(x => x.Code == domainName);
DataSet ds = new DataSet(domainName);
IWorkbook wb = ReadWorkbook(path);
List <string> sheetNames = GetSheetNames(wb);
//sheetNames equal to objectName
foreach (string sheetName in sheetNames)
{
DGObjectDef objectDef = domain.DGObjectContainer.Find(x => x.Code == sheetName);
DataTable dt = ReadSheet(wb.GetSheet(sheetName), objectDef);
ds.Tables.Add(dt);
}
return(ds);
}
catch (Exception)
{
return(null);
}
}
void wrtieTitle(ISheet sheet, DGObjectDef item)
{
IRow row1 = sheet.CreateRow(1);
int i = 0;
foreach (PropertyMeta property in item.PropertyContainer)
{
row1.CreateCell(i++).SetCellValue(property.LangStr + property.DataType);
}
}
private DataTable DeduplicateTable(DataTable table, DGObjectDef objectDef)
{
string[] distinctcols = new string[(table.Columns.Count)];
foreach (DataColumn dataColumn in table.Columns)
{
distinctcols[dataColumn.Ordinal] = dataColumn.ColumnName;
}
DataTable DeduplicatedTable = new DataTable(table.TableName);
DataView mydataview = new DataView(table);
DeduplicatedTable = mydataview.ToTable(true, distinctcols);//去重复
return(DeduplicatedTable);
}
void writeDescription(ISheet sheet, DGObjectDef item)
{
IRow row0 = sheet.CreateRow(0);
row0.CreateCell(0).SetCellValue(item.Code + "表");
row0.CreateCell(1).SetCellValue(item.Desctiption);
row0.CreateCell(3).SetCellValue("请勿修改sheet名");
for (int i = 0; i < 20; i++)
{
sheet.SetColumnWidth(i, 20 * 175);
}
}
void writeDescription(ISheet sheet, DGObjectDef objectDef)
{
IRow row0 = sheet.CreateRow(0);
row0.CreateCell(0).SetCellValue(objectDef.Code + "表");
row0.CreateCell(1).SetCellValue(objectDef.LangStr);
row0.CreateCell(3).SetCellValue("请勿修改sheet名");
for (int i = 0; i < 20; i++)
{
sheet.SetColumnWidth(i, 20 * 150);
}
}
/// <summary>
/// sheet to object datatable
/// </summary>
/// <param name="sheet"></param>
/// <returns></returns>
DataTable ReadSheet(ISheet sheet, DGObjectDef objectDef)
{
if (sheet == null)
{
return(null);
}
else
{
DataTable dt = new DataTable()
{
TableName = objectDef.LangStr
};
foreach (PropertyMeta meta in objectDef.PropertyContainer)
{
dt.Columns.Add(meta.LangStr);
}
foreach (IRow row in sheet)
{
DataRow dr = dt.NewRow();
int i = 0;
foreach (PropertyMeta meta in objectDef.PropertyContainer)
{
ICell cell = row.GetCell(i);
if (cell != null)
{
if ((cell.CellType == CellType.Numeric) && (DateUtil.IsCellDateFormatted(cell)))
{
dr[meta.LangStr] = cell.DateCellValue.ToShortDateString();
}
else
{
dr[meta.LangStr] = cell.ToString();
}
i++;
}
else
{
i++;
dr[meta.LangStr] = null;
}
}
dt.Rows.Add(dr);
}
for (int i = 0; i < 3; i++)
{
dt.Rows.RemoveAt(0); //remove the decription line(first 3 linesS)
}
return(dt);
}
}
void wrtieTitle(ISheet sheet, DGObjectDef item, XSSFCellStyle s1, XSSFCellStyle s2)
{
IRow row1 = sheet.CreateRow(1);
IRow row2 = sheet.CreateRow(2);
int i = 0;
foreach (PropertyMeta property in item.PropertyContainer)
{
row1.CreateCell(i).SetCellValue(property.LangStr);
row1.Cells[i].CellStyle = s1;
row2.CreateCell(i).SetCellValue(property.DataType);
row2.Cells[i++].CellStyle = s2;
}
}
private void Filter2Standard(Category category, ref StandardDef standardDef, StandardDef dataStandard)
{
DomainDef domain = new DomainDef()
{
Code = category.CategoryName,
LangStr = category.LangStr
};
foreach (string obj in category.objList)
{
DGObjectDef objectDef = dataStandard.GetDGObjectDefByCode(obj);
domain.DGObjectContainer.Add(objectDef);
}
standardDef.DomainContainer.Add(domain);
}
private void Watchnewdata_Click(object sender, RoutedEventArgs e)
{
try
{
TreeNode node = LYTunnelTreeview.SelectedItem as TreeNode;
if ((node == null) || (node.Level != 2))
{
return;
}
DataImporter_Excel dataImporter = new DataImporter_Excel();
string path = Runtime.dataPath + "\\ProjectData\\LYTunnel.xls";
DataTable _newdatatable = dataImporter.Import(path, LYTunnelStandard, node.Context);
if (_newdatatable.Columns.Count == 0)
{
StageDef stage = LYTunnelStandard.StageContainer.Find(x => x.Code == node.Parent);
DGObjectDef dGObject = stage.DGObjectContainer.Find(x => x.Code == node.Code);
if (dGObject != null)
{
foreach (PropertyMeta meta in dGObject.PropertyContainer)
{
_newdatatable.Columns.Add(meta.LangStr);
}
}
}
string[] distinctcols = new string[(_newdatatable.Columns.Count)];
foreach (DataColumn dataColumn in _newdatatable.Columns)
{
distinctcols[dataColumn.Ordinal] = dataColumn.ColumnName;
}
DataView mydataview = new DataView(_newdatatable);
_newdatatable = mydataview.ToTable(true, distinctcols); //去重复
datatable_clean(_newdatatable); //去空白行
Comparetables(currenttable, _newdatatable);
DataView view = new DataView(_newdatatable);
DataDG.ItemsSource = view;
ChangeStyle style = new ChangeStyle(_newdatatable, ref DataDG, LYTunnelStandard);
style.RefreshStyle();
DataDG.ScrollIntoView(DataDG.Items[0]);
DataDG.UpdateLayout();
Statelabel.Content = "校核结果如上，如无误则点击“上传新数据”按钮";
newdatatable = _newdatatable;
}
catch (Exception ex)
{
MessageBox.Show("请保存Excel模板文件，并关闭它！");
}
}
void ShowProperty(string objName)
{
GridView gv = PropertyLV.View as GridView;
if (gv != null)
{
foreach (GridViewColumn gvc in gv.Columns)
{
gvc.Width = gvc.ActualWidth;
gvc.Width = Double.NaN;
}
}
DGObjectDef dGObjectDef = Standard.GetDGObjectDefByName(objName);
PropertyLV.ItemsSource = null;
PropertyLV.ItemsSource = dGObjectDef.PropertyContainer;
}
/// <summary>
/// generate template for only one DGobject
/// </summary>
void GenerateSingleTemplate(TreeNode treeNode)
{
try
{
DomainDef domain = new DomainDef()
{
Code = treeNode.Context, LangStr = treeNode.Context
};
DGObjectDef dG = Standard.GetDGObjectDefByName(treeNode.Context);
domain.DGObjectContainer.Add(dG);
new TemplateGenerator_Excel().Export(domain);
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// import Data
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private async void DataTemplateTreeview_SelectedItemChanged(object sender, RoutedEventArgs e)
{
try
{
TreeNode node = LYTunnelTreeview.SelectedItem as TreeNode;
if ((node == null) || (node.Level != 2))
{
return;
}
StageDef stage = LYTunnelStandard.StageContainer.Find(x => x.Code == node.Parent);
DGObjectDef dGObject = stage.DGObjectContainer.Find(x => x.Code == node.Code);
Project _prj = Globals.project;
Domain _domain = _prj.getDomain(stage.Code);
DGObjects objs = _domain.DGObjectsList.Where(x => x.definition.Type == dGObject.Code).FirstOrDefault();
await GetData(objs);
if (currenttable.Columns.Count == 0)
{
if (dGObject != null)
{
foreach (PropertyMeta meta in dGObject.PropertyContainer)
{
currenttable.Columns.Add(meta.LangStr);
}
}
}
DataView view = new DataView(currenttable);
DataDG.ItemsSource = view;
DataDG.UpdateLayout();
ChangeStyle style = new ChangeStyle(currenttable, ref DataDG, LYTunnelStandard);
style.IDindex = currenttable_IDindex;
style.Addattachment();
DataDG.UpdateLayout();
DataDG.ScrollIntoView(DataDG.Items[0]);
DataDG.UpdateLayout();
Statelabel.Content = "请查看" + currenttable.TableName + "的所有数据！";
}
catch (Exception ex)
{
MessageBox.Show("读取云端数据发生意外，读取失败！");
}
}
/// <summary>
/// 向entity中写入数据,实例形式
/// </summary>
/// <param name="db">entity实例</param>
/// <param name="data">数据对象</param>
/// <returns></returns>
public bool BoreholeInsert(GeologyDB_EF db, DGObjectDef objectDef, DataTable table)
{
for (int i = 3; i < table.Rows.Count; i++)
{
int j = 0;
List <object> data = new List <object>();
foreach (PropertyMeta property in objectDef.PropertyContainer)
{
data.Add(table.Rows[i][j.ToString()]);
}
Borehole borehole = new Borehole {
};
db.Boreholes.Add(borehole);
}
db.SaveChanges();
return(false);
}
/// <summary>
/// generate template for one category
/// </summary>
/// <param name="selectedNode"></param>
void GenerateCategoryTemplate(TreeNode selectedNode)
{
try
{
DomainDef domain = new DomainDef()
{
Code = Standard.Code, LangStr = selectedNode.Context
};
foreach (TreeNode childNode in selectedNode.ChildNodes)
{
DGObjectDef dG = Standard.GetDGObjectDefByName(childNode.Context);
domain.DGObjectContainer.Add(dG);
}
new TemplateGenerator_Excel().Export(domain);
}
catch (Exception a)
{
MessageBox.Show(a.Message);
}
}
void GenerateTempleteForOther(TreeNode treeNode)
{
try
{
DomainDef domain = new DomainDef()
{
Code = Standard.DomainContainer.Find(x => x.LangStr == treeNode.Context).Code, LangStr = treeNode.Context
};
foreach (var item in treeNode.ChildNodes)
{
DGObjectDef dG = Standard.GetDGObjectDefByName(item.Context);
domain.DGObjectContainer.Add(dG);
}
new TemplateGenerator_Excel().Export(domain);
}
catch (Exception)
{
throw;
}
}
public DataSet UpdateDataSet(DataSet oldSet, DataSet newDataSet, StageDef stage)
{
this.stage = stage;
path = localPath + @"\Data\Template\" + stage.Code + ".xls";
BackupExcel(path);
RebuildTemplate();
DataSet addedDataSet = new DataSet()
{
DataSetName = oldSet.DataSetName
};
int count = newDataSet.Tables.Count;
if (count > 5)
{
foreach (DataTable newTable in newDataSet.Tables)
{
if (newTable.Rows.Count < 1)
{
continue;
}
DataTable oldTable = oldSet.Tables[newTable.TableName];
DGObjectDef dG = stage.DGObjectContainer.Find(x => (x.LangStr == newTable.TableName || x.Code == newTable.TableName));
TableComparator comparator = new TableComparator(oldTable, newTable, dG);
comparator.Compare();
DataTable UpdatedTable = comparator.updatedTable;
updatedDataSet = newDataSet.Clone();
updatedDataSet.Tables.Remove(UpdatedTable.TableName);
updatedDataSet.Tables.Add(UpdatedTable);
foreach (DataRow row in UpdatedTable.Rows)
{
oldTable.ImportRow(row);
}
oldSet.Tables.Remove(oldTable.TableName);
oldSet.Tables.Add(oldTable);
}
}
return(oldSet);
}
public void Data2DB(DataSet ds, DataStandardDef standardDef)
{
try
{
string domainName = ds.DataSetName;
DomainDef domain = standardDef.DomainContainer.Find(x => x.Code == domainName);
if (domain != null)
{
foreach (DataTable table in ds.Tables)
{
DGObjectDef objectDef = domain.DGObjectContainer.Find(x => x.Code == table.TableName);
Insert(objectDef, table);
}
System.Windows.MessageBox.Show("数据导入成功");
}
}
catch (Exception e)
{
System.Windows.MessageBox.Show(e.ToString());
}
}
/// <summary>
/// sheet to object datatable
/// </summary>
/// <param name="sheet"></param>
/// <returns></returns>
DataTable ReadSheet(ISheet sheet, DGObjectDef objectDef)
{
if (sheet == null)
{
return(null);
}
else
{
DataTable dt = new DataTable(objectDef.LangStr);
foreach (PropertyMeta meta in objectDef.PropertyContainer)
{
dt.Columns.Add(meta.LangStr);
}
foreach (IRow row in sheet)
{
DataRow dr = dt.NewRow();
int i = 0;
foreach (PropertyMeta meta in objectDef.PropertyContainer)
{
if (row.GetCell(i) != null)
{
dr[meta.LangStr] = row.GetCell(i++);
}
else
{
i++;
dr[meta.LangStr] = null;
}
}
dt.Rows.Add(dr);
}
for (int i = 0; i < 3; i++)
{
dt.Rows.RemoveAt(0); //remove the decription line(first 3 linesS)
}
return(dt);
}
}
