private void btn_loadsample_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
{
DataTable dt = null;
dt_TableAndField = new DataTable();
string sheetName = "物理表汇总";
string tablename = "T_WELL_SAMPLE";//采油井生产月报
//string DabaBasePath = "provider=microsoft.jet.oledb.4.0; Data Source=" + Application.StartupPath + "\\Database.mdb";
//string excelpath = Application.StartupPath + "\\ACCESS.xlsx";
string TableAndField = string.Format("select 列显示名称 AS name ,库字段名称 as ID, 默认单位名称 as UNIT from [{0}$] where (库表名称='" + tablename + "')", sheetName);
dt_TableAndField = OleDbHelper.ExcelToDataTable(sheetName, TableAndField);
try
{
string sSql = "select ";
for (int i = 0; i < dt_TableAndField.Rows.Count - 1; i++)
{
sSql = sSql + string.Format("{0} AS {1}, ", dt_TableAndField.Rows[i][1], dt_TableAndField.Rows[i][0]);
}
sSql = sSql + string.Format("{0} AS {1} From {2}", dt_TableAndField.Rows[dt_TableAndField.Rows.Count - 1][1], dt_TableAndField.Rows[dt_TableAndField.Rows.Count - 1][0], tablename);
dt = OleDbHelper.getTable(sSql, Globalname.DabaBasePath);
}
catch
{
}
}
private DataTable GetProductData(string wellid, string tablename = "T_OW_M")
{
DataTable dt = null;
try
{
//采油井生产月报
//string DabaBasePath = "provider=microsoft.jet.oledb.4.0; Data Source=" + Application.StartupPath + "\\Database.mdb";
//string excelpath = Application.StartupPath + "\\ACCESS.xlsx";
string[] dataname;
dt_TableAndField = new DataTable();
string sheetName = "物理表汇总";
string TableAndField = string.Format("select 列显示名称 AS name ,库字段名称 as ID, 默认单位名称 as UNIT from [{0}$] where (库表名称='" + tablename + "')", sheetName);
dt_TableAndField = OleDbHelper.ExcelToDataTable(sheetName, TableAndField);
// string sSql = "SELECT T_DM_UNIT_CUR_INFOR.DM_UNIT_ID AS ParentID, T_WELL_INFOR.WELL_ID as KeyID,T_WELL_INFOR.WELL_NAME as Name FROM T_WELL_INFOR ,T_DM_UNIT_CUR_INFOR where T_WELL_INFOR.DM_UNIT_ID=T_DM_UNIT_CUR_INFOR.DM_UNIT_ID " +
//"UNION SELECT PARENT_DM_UNIT_ID AS ParentID,DM_UNIT_ID as KeyID,DM_UNIT_NAME as Name FROM T_DM_UNIT_CUR_INFOR";where [{0}$].库表名称 + tablename
string sSql = "select ";
for (int i = 0; i < dt_TableAndField.Rows.Count - 1; i++)
{
sSql = sSql + string.Format("{0} AS {1}, ", dt_TableAndField.Rows[i][1], dt_TableAndField.Rows[i][0]);
}
sSql = sSql + string.Format("{0} AS {1} From {2} where WELL_ID='{3}'", dt_TableAndField.Rows[dt_TableAndField.Rows.Count - 1][1], dt_TableAndField.Rows[dt_TableAndField.Rows.Count - 1][0], tablename, wellid);
dt = OleDbHelper.getTable(sSql, Globalname.DabaBasePath);
return(dt);
}
catch
{
return(dt);
}
}
private void GetTatableAndFieldName()
{
DataTable dt_TableAndCate = new DataTable();
//string pathName = Application.StartupPath + "\\ACCESS.xlsx";
string sheetName = "数据表描述";
string columname = "数据表类型,表显示名称,库表名称 ";
string sSql = "SELECT T_DM_UNIT_CUR_INFOR.DM_UNIT_ID AS ParentID, T_WELL_INFOR.WELL_ID as KeyID,T_WELL_INFOR.WELL_NAME as Name FROM T_WELL_INFOR ,T_DM_UNIT_CUR_INFOR where T_WELL_INFOR.DM_UNIT_ID=T_DM_UNIT_CUR_INFOR.DM_UNIT_ID " +
"UNION SELECT PARENT_DM_UNIT_ID AS ParentID,DM_UNIT_ID as KeyID,DM_UNIT_NAME as Name FROM T_DM_UNIT_CUR_INFOR"; //where [{0}$].库表名称=[{1}$].库表名称
string TableAndCate = string.Format("select 数据表类型ID AS ParentID ,表显示名称 as Name,库表名称 as KeyID from [{0}$] UNION select 上级数据表类型 as ParentID, 数据表类型 as Name,数据表类型ID as KeyID from [{0}$]", sheetName);
dt_TableAndCate = OleDbHelper.ExcelToDataTable(sheetName, TableAndCate);
InitTreeView(treeList1);
treeList1.Nodes.Clear();
treeList1.DataSource = dt_TableAndCate;
treeList1.ParentFieldName = "ParentID";
treeList1.KeyFieldName = "KeyID";
treeList1.Columns["Name"].Caption = "表显示名称";
this.treeList1.Nodes[0].Expanded = true; // 只显示1级目录
}
private void GetDataFromAccess(string tablename)
{
DataTable dt = null;
//string excelpath = Application.StartupPath + "\\ACCESS.xlsx";
string[] dataname;
DataTable dt_TableAndField = new DataTable();
string sheetName = "物理表汇总";
string TableAndField = string.Format("select 库表名称 AS TABLE_ID, 表显示名称 AS 目的表名, 列显示名称 AS 目的字段 ,库字段名称 as ID, 默认单位名称 as 单位名称, 主键 as 是否必须 from [{0}$] where (库表名称='" + tablename + "')", sheetName);
dt_TableAndField = OleDbHelper.ExcelToDataTable(sheetName, TableAndField);
DataTable query = new DataTable();
dt_TableAndField.Columns.Add("源字段", typeof(string));
gridView3.OptionsView.ShowColumnHeaders = true; //因为有Band列了，所以把ColumnHeader隐藏
gridView3.ColumnPanelRowHeight = 50;
gridView3.OptionsView.AllowHtmlDrawHeaders = true;
//gridView3.Appearance.HeaderPanel.TextOptions.WordWrap = DevExpress.Utils.WordWrap.Wrap;
//表头及行内容居中显示
gridView3.Appearance.Row.TextOptions.HAlignment = DevExpress.Utils.HorzAlignment.Center;
gridView3.Appearance.HeaderPanel.TextOptions.HAlignment = DevExpress.Utils.HorzAlignment.Center;
//添加列标题
dt_TableAndField.Columns["源字段"].SetOrdinal(3);
dt_TableAndField.Columns.Add("源表格", typeof(string));
dt_TableAndField.Columns.Add("ID_字符类型", typeof(string));
string varTableName = dt_TableAndField.Rows[0]["TABLE_ID"].ToString();
dt_TableAndField = OleDbHelper.GetTableColumn(Globalname.DabaBasePath, varTableName, dt_TableAndField);
DataTable tr3 = treeList3.DataSource as DataTable;
if (tr3 != null)
{
for (int i = 0; i < dt_TableAndField.Rows.Count; i++)
{
dt_TableAndField.Rows[i]["源表格"] = treeList3.DataSource.ToString();
}
}
gridControl3.DataSource = dt_TableAndField; // dt;
gridControl3.MainView.PopulateColumns();
gridView3.Columns[0].Visible = false; //目的表ID
gridView3.Columns[4].Visible = false; //目的字段ID
gridView3.Columns[7].Visible = false; //原表格
if (lookup1 == null)
{
lookup1 = (RepositoryItemSearchLookUpEdit)gridControl1.RepositoryItems.Add("SearchLookUpEdit");
}
if (tr3 != null)
{
treeList3.Visible = true;
tr3.Columns[0].ColumnName = "源字段";
lookup1.ValueMember = tr3.Columns[0].ToString();
lookup1.DisplayMember = tr3.Columns[0].ToString();
lookup1.DataSource = tr3;
gridView3.Columns["源字段"].ColumnEdit = lookup1;
}
gridView3.OptionsView.ColumnAutoWidth = true;
gridView3.ScrollStyle = DevExpress.XtraGrid.Views.Grid.ScrollStyleFlags.LiveHorzScroll;
gridView3.HorzScrollVisibility = DevExpress.XtraGrid.Views.Base.ScrollVisibility.Always; // or ScrollVisibility.Auto doesn't work neither; or remove this line, doesn't work
gridView3.OptionsView.RowAutoHeight = true;
gridView3.OptionsCustomization.AllowFilter = true;
gridView3.BestFitColumns(true);
gridView3.OptionsCustomization.AllowColumnMoving = false;
gridView3.OptionsSelection.MultiSelect = true;
gridView3.OptionsSelection.MultiSelectMode = DevExpress.XtraGrid.Views.Grid.GridMultiSelectMode.RowSelect;
}
public Form6()
{
InitializeComponent();
//gridcontroltool gridcontroltool1 = new gridcontroltool();
//gridcontroltool1.InitView(gridView1);
//this.gridControl1.ContextMenuStrip = gridcontroltool1.tool_menu;
// adding a category row to the root level
vGridControl1.Rows.Add(rowMain);
vGridControl1.LayoutStyle = LayoutViewStyle.SingleRecordView;
vGridControl1.CellValueChanged += vGridControl1_CellValueChanged;
// creating and modifying the Trademark row
EditorRow datasmooth = new EditorRow("数据平滑");
datasmooth.Properties.Caption = "数据平滑";
RepositoryItemComboBox datasmoothedit = new RepositoryItemComboBox();
datasmoothedit.Items.AddRange(new string[] { "不处理", "移动窗口平滑", "五次三点平滑" });
// adding the Trademark row to the Main row's child collection
rowMain.ChildRows.Add(datasmooth);
vGridControl1.RepositoryItems.Add(datasmoothedit);
//Now you can define the repository item as an inplace editor of columns
// populating the combo box with data
datasmooth.Properties.Value = "不处理";
datasmooth.Properties.RowEdit = datasmoothedit;
for (int j = 1; j < 3; j++)
{
int i = 1;
// creating and modifying the Model row
DataTable dt_TableAndCate = new DataTable();
//string pathName = Application.StartupPath + "\\ACCESS.xlsx";
string sheetName = "数据表描述";
string columname = "数据表类型,表显示名称,库表名称 ";
string sSql = "SELECT T_DM_UNIT_CUR_INFOR.DM_UNIT_ID AS ParentID, T_WELL_INFOR.WELL_ID as KeyID,T_WELL_INFOR.WELL_NAME as Name FROM T_WELL_INFOR ,T_DM_UNIT_CUR_INFOR where T_WELL_INFOR.DM_UNIT_ID=T_DM_UNIT_CUR_INFOR.DM_UNIT_ID " +
"UNION SELECT PARENT_DM_UNIT_ID AS ParentID,DM_UNIT_ID as KeyID,DM_UNIT_NAME as Name FROM T_DM_UNIT_CUR_INFOR"; //where [{0}$].库表名称=[{1}$].库表名称
string TableAndCate = string.Format("select 数据表类型ID AS ParentID ,表显示名称 as Name,库表名称 as KeyID from [{0}$] UNION select 上级数据表类型 as ParentID, 数据表类型 as Name,数据表类型ID as KeyID from [{0}$]", sheetName);
dt_TableAndCate = OleDbHelper.ExcelToDataTable(sheetName, TableAndCate);
//UserControl1 fg=new UserControl1();
// fg.DataSource = dt_TableAndCate;
tgridControl11.SetGridDataView(dt_TableAndCate);
tgridControl11.gridViewoption();
tgridControl11.gridView1.OptionsCustomization.AllowColumnMoving = false;
//tgridControl11.gridView1.OptionsView.ColumnAutoWidth = false;
//gridView1.ScrollStyle = DevExpress.XtraGrid.Views.Grid.ScrollStyleFlags.LiveHorzScroll;
//gridView1.HorzScrollVisibility = DevExpress.XtraGrid.Views.Base.ScrollVisibility.Always; // or ScrollVisibility.Auto doesn't work neither; or remove this line, doesn't work
//gridView1.OptionsView.RowAutoHeight = true;
//gridView1.BestFitColumns(true);
//gridView1.OptionsCustomization.AllowColumnMoving = false;
//gridView1.OptionsSelection.MultiSelect = true;
//gridView1.OptionsSelection.MultiSelectMode = DevExpress.XtraGrid.Views.Grid.GridMultiSelectMode.RowSelect;
vGridControl1.ShowButtonMode = ShowButtonModeEnum.ShowAlways;
}
}