using HMI.Base;
|
using HMI.Core.Util;
|
using HMI.DataViewer.Entity;
|
using HMI.UI.Util;
|
using NPOI.SS.UserModel;
|
using NPOI.XSSF.UserModel;
|
using System;
|
using System.Collections.Generic;
|
using System.Collections.ObjectModel;
|
using System.Data;
|
using System.IO;
|
using System.Linq;
|
using System.Windows;
|
using System.Windows.Controls;
|
using System.Windows.Controls.Primitives;
|
using System.Windows.Forms;
|
using System.Windows.Media;
|
|
namespace HMI.DataViewer
|
{
|
/// <summary>
|
/// UserControl1.xaml 的交互逻辑
|
/// </summary>
|
public partial class DataViewer : PluginControl
|
{
|
public DataViewer()
|
{
|
DataContext = this;
|
InitializeComponent();
|
}
|
|
private string SourceFilePath;
|
|
private ObservableCollection<DataBase_Setting> settings = new ObservableCollection<DataBase_Setting>();
|
public ObservableCollection<DataBase_Setting> Settings
|
{
|
get => settings;
|
set { Set(ref settings, value); }
|
}
|
|
private ObservableCollection<QueryData> datas = new ObservableCollection<QueryData>();
|
public ObservableCollection<QueryData> Datas
|
{
|
get => datas;
|
set { Set(ref datas, value); }
|
}
|
|
private Dictionary<string, DataBase_Setting> Map = new Dictionary<string, DataBase_Setting>();
|
|
private void Button_Click(object sender, RoutedEventArgs e)
|
{
|
System.Windows.Controls.Button btn = sender as System.Windows.Controls.Button;
|
switch (btn.Name)
|
{
|
case "addBtn":
|
OpenSettingModal(-1, new DataBase_Setting());
|
break;
|
case "editBtn":
|
EditSettingModal();
|
break;
|
case "rfhBtn":
|
LoadData();
|
break;
|
case "delBtn":
|
DeleteDatasource();
|
break;
|
case "srhBtn":
|
SearchData();
|
break;
|
case "resetBtn":
|
ResetDataTable();
|
break;
|
case "exportBtn":
|
ExportData();
|
break;
|
default:
|
break;
|
}
|
}
|
|
private void DeleteDatasource()
|
{
|
try
|
{
|
var key = programInput.Text;
|
if (string.IsNullOrEmpty(key))
|
{
|
ToastHelper.Warning(Window.GetWindow(this), this.lang("Dv.SelectDatasourceFirst"));
|
return;
|
}
|
if (!Map.ContainsKey(key))
|
{
|
ToastHelper.Error(Window.GetWindow(this), this.lang("App.ParameterException"));
|
return;
|
}
|
DataBase_Setting setting = Map[key];
|
if (MsgBoxHelper.WarnMessage(Window.GetWindow(this), this.lang("HMI.MakeSureDeleteSingle", setting.Program))
|
== MessageBoxResult.OK)
|
{
|
var temp = XmlHelper.DeepClone(Settings);
|
temp.RemoveAt(Settings.IndexOf(setting));
|
XmlHelper.SerializeToXml(SourceFilePath, temp);
|
ToastHelper.Success(Window.GetWindow(this), this.lang("App.DeleteSuccess"));
|
LoadData();
|
}
|
}
|
catch (Exception ex)
|
{
|
log.Error("Delete Datasource Failed.", ex);
|
ToastHelper.Success(Window.GetWindow(this), $"{this.lang("App.DeleteFailed")}: {ex.Message}");
|
}
|
}
|
|
private void EditSettingModal()
|
{
|
var key = programInput.Text;
|
if (string.IsNullOrEmpty(key))
|
{
|
ToastHelper.Warning(Window.GetWindow(this), this.lang("Dv.SelectDatasourceFirst"));
|
return;
|
}
|
if (!Map.ContainsKey(key))
|
{
|
ToastHelper.Error(Window.GetWindow(this), this.lang("App.ParameterException"));
|
return;
|
}
|
DataBase_Setting setting = Map[key];
|
OpenSettingModal(Settings.IndexOf(setting), setting);
|
}
|
|
private void OpenSettingModal(int index, DataBase_Setting data)
|
{
|
DbSettingModal modal = new DbSettingModal(log)
|
{
|
Title = this.lang("Dv.AddDatasource"),
|
Owner = Window.GetWindow(this)
|
};
|
if (modal.Open(index, data, Settings, SourceFilePath) == true)
|
{
|
LoadData();
|
}
|
}
|
|
private void ResetDataTable()
|
{
|
startTimeInput.SelectedDateTime = null;
|
endTimeInput.SelectedDateTime = null;
|
dataTable.ItemsSource = null;
|
foreach (var data in Datas)
|
{
|
data.Value = null;
|
}
|
}
|
|
public override void SetBaseFolder(string folder)
|
{
|
base.SetBaseFolder(folder);
|
SourceFilePath = Path.Combine(BaseFolder, "SourceData.setting");
|
}
|
|
private void PluginControl_Loaded(object sender, RoutedEventArgs e)
|
{
|
LoadData();
|
Loaded -= PluginControl_Loaded;
|
}
|
|
private void LoadData()
|
{
|
ObservableCollection<DataBase_Setting> settings = new ObservableCollection<DataBase_Setting>();
|
if (File.Exists(SourceFilePath))
|
{
|
settings = XmlHelper.DeserializeFromXml<ObservableCollection<DataBase_Setting>>(SourceFilePath);
|
}
|
List<string> names = new List<string>();
|
|
Map.Clear();
|
foreach (var data in settings)
|
{
|
Map[data.Program] = data;
|
names.Add(data.Program);
|
}
|
programInput.ItemsSource = names;
|
//ResetInput();
|
UpdateAdvanced();
|
Settings = settings;
|
}
|
|
private void ResetInput()
|
{
|
programInput.Text = string.Empty;
|
Datas.Clear();
|
|
var temp = dataTable.Columns[0];
|
HMI.UI.DpUtil.DataGridHelper.SetSelectedColumn(dataTable, null);
|
dataTable.Columns.Clear();
|
dataTable.Columns.Add(temp);
|
currentColumns?.Clear();
|
}
|
|
private void UpdateAdvanced()
|
{
|
var key = programInput.Text;
|
if (!Map.ContainsKey(key))
|
{
|
ResetInput();
|
return;
|
}
|
DataBase_Setting setting = Map[key];
|
Datas = new ObservableCollection<QueryData>(
|
setting?.Condition?.Select(d => new QueryData { Key = d.Key, Name = d.Desc, Condition = "=" }).ToList());
|
|
var temp = dataTable.Columns[0];
|
HMI.UI.DpUtil.DataGridHelper.SetSelectedColumn(dataTable, null);
|
dataTable.Columns.Clear();
|
dataTable.Columns.Add(temp);
|
|
List<DataGridTextColumn> columns = new List<DataGridTextColumn>();
|
foreach (var data in Datas)
|
{
|
columns.Add(AddDataGridTextColumn(data.Name, data.Key));
|
}
|
currentColumns = columns;
|
}
|
|
private void programInput_SelectionChanged(object sender, SelectionChangedEventArgs e)
|
{
|
if (e.AddedItems == null || e.AddedItems.Count <= 0) return;
|
var key = e.AddedItems[0].ToString();
|
if (!Map.ContainsKey(key))
|
{
|
ToastHelper.Error(Window.GetWindow(this), this.lang("App.ParameterException"));
|
programInput.Text = string.Empty;
|
return;
|
}
|
DataBase_Setting setting = Map[key];
|
Datas = new ObservableCollection<QueryData>(
|
setting?.Condition?.Select(d => new QueryData { Key = d.Key, Name = d.Desc, Condition = "=" }).ToList());
|
|
var temp = dataTable.Columns[0];
|
HMI.UI.DpUtil.DataGridHelper.SetSelectedColumn(dataTable, null);
|
dataTable.Columns.Clear();
|
dataTable.Columns.Add(temp);
|
|
List<DataGridTextColumn> columns = new List<DataGridTextColumn>();
|
foreach (var data in Datas)
|
{
|
columns.Add(AddDataGridTextColumn(data.Name, data.Key));
|
}
|
currentColumns = columns;
|
}
|
|
private List<DataGridTextColumn> currentColumns;
|
|
private DataGridTextColumn AddDataGridTextColumn(string header, string bindingPath)
|
{
|
DataGridTextColumn textColumn = new DataGridTextColumn
|
{
|
Header = header,
|
Binding = new System.Windows.Data.Binding(bindingPath),
|
Width = new DataGridLength(1, DataGridLengthUnitType.Star)
|
};
|
dataTable.Columns.Add(textColumn);
|
return textColumn;
|
}
|
|
private void dataTable_PreviewMouseLeftButtonDown(object sender, System.Windows.Input.MouseButtonEventArgs e)
|
{
|
// 获取点击的元素
|
DependencyObject dep = (DependencyObject)e.OriginalSource;
|
|
// 遍历找到 DataGridColumnHeader
|
while (dep != null && !(dep is DataGridColumnHeader) && !(dep is DataGridRow))
|
{
|
dep = VisualTreeHelper.GetParent(dep);
|
}
|
HMI.UI.DpUtil.DataGridHelper.SetSelectedColumn(dataTable, null);
|
|
if (dep is DataGridColumnHeader columnHeader)
|
{
|
// 获取列索引
|
int columnIndex = columnHeader.Column.DisplayIndex;
|
|
// 清除已有选择
|
dataTable.SelectedCells.Clear();
|
|
// 遍历所有行，选中该列的所有单元格
|
foreach (var item in dataTable.Items)
|
{
|
DataGridCellInfo cellInfo = new DataGridCellInfo(item, columnHeader.Column);
|
dataTable.SelectedCells.Add(cellInfo);
|
}
|
HMI.UI.DpUtil.DataGridHelper.SetSelectedColumn(dataTable, columnHeader.Column);
|
|
e.Handled = true; // 防止默认行为
|
}
|
else if (dep is DataGridRow row)
|
{
|
// 清除已有选择
|
dataTable.SelectedCells.Clear();
|
dataTable.SelectedItem = row.Item;
|
e.Handled = true;
|
}
|
}
|
|
private DataTable DoSearchData(DataBase_Setting setting, List<QueryData> datas)
|
{
|
string conditon = "WHERE 1=1 ";
|
string fields = string.Join(",", Datas?.Select(d => $"[{d.Key}]").ToList());
|
if (datas != null && datas.Count > 0)
|
{
|
conditon = "WHERE " + string.Join(" AND ", datas.Select(d => $"[{d.Key}] {d.Condition} '{d.Value}'").ToList());
|
}
|
|
string timeCol = setting.TimeCol;
|
if (string.IsNullOrEmpty(timeCol))
|
{
|
timeCol = "time";
|
}
|
|
if (!string.IsNullOrEmpty(startTimeInput.Text))
|
{
|
conditon += $" AND {timeCol} >= '{startTimeInput.Text}'";
|
}
|
if (!string.IsNullOrEmpty(endTimeInput.Text))
|
{
|
conditon += $" AND {timeCol} < '{endTimeInput.Text}'";
|
}
|
|
SqliteHelper helper = null;
|
string sql = string.Empty;
|
try
|
{
|
helper = new SqliteHelper(setting.DBFilePath);
|
sql = $"SELECT {fields} FROM {setting.TableName} {conditon}";
|
DataTable dt = helper.GetDataTable(sql);
|
HMI.UI.DpUtil.DataGridHelper.SetSelectedColumn(dataTable, null);
|
dataTable.ItemsSource = dt.DefaultView;
|
return dt;
|
}
|
catch (Exception ex)
|
{
|
log.Error($"Select Data Failed. SQL: {sql}", ex);
|
throw ex;
|
}
|
finally
|
{
|
helper?.Close();
|
}
|
}
|
|
private void SearchData()
|
{
|
string program = programInput.Text;
|
if (!Map.ContainsKey(program))
|
{
|
ToastHelper.Error(Window.GetWindow(this), this.lang("App.ParameterException"));
|
return;
|
}
|
|
var datas = Datas?.Where(d => !string.IsNullOrEmpty(d.Value)).ToList();
|
|
if ((datas == null || datas.Count <= 0) &&
|
(string.IsNullOrEmpty(startTimeInput.Text) || string.IsNullOrEmpty(endTimeInput.Text)))
|
{
|
ToastHelper.Warning(Window.GetWindow(this), this.lang("Dv.QueryCantBeEmpty"));
|
return;
|
}
|
|
DataBase_Setting setting = Map[program];
|
|
try
|
{
|
DoSearchData(setting, datas);
|
}
|
catch (Exception ex)
|
{
|
ToastHelper.Error(Window.GetWindow(this), $"{this.lang("App.QueryFailed")}: {ex.Message}");
|
}
|
}
|
|
private void ExportData()
|
{
|
try
|
{
|
string program = programInput.Text;
|
if (string.IsNullOrEmpty(program))
|
{
|
ToastHelper.Warning(Window.GetWindow(this), this.lang("Dv.SelectDatasourceFirst"));
|
return;
|
}
|
|
if (!Map.ContainsKey(program))
|
{
|
ToastHelper.Error(Window.GetWindow(this), this.lang("App.ParameterException"));
|
return;
|
}
|
|
var datas = Datas?.Where(d => !string.IsNullOrEmpty(d.Value)).ToList();
|
if (datas == null || datas.Count <= 0)
|
{
|
ToastHelper.Warning(Window.GetWindow(this), this.lang("Dv.QueryCantBeEmpty"));
|
return;
|
}
|
|
DataBase_Setting setting = Map[program];
|
DataTable dt;
|
try
|
{
|
dt = DoSearchData(setting, datas);
|
}
|
catch (Exception ex)
|
{
|
ToastHelper.Error(Window.GetWindow(this), $"{this.lang("App.QueryFailed")}: {ex.Message}");
|
return;
|
}
|
|
if (currentColumns == null) return;
|
var dataView = dt?.DefaultView;
|
if (dataView == null) return;
|
|
string fname = string.Empty;
|
SaveFileDialog sfd = new SaveFileDialog();
|
|
sfd.Filter = "";
|
sfd.DefaultExt = "xls";
|
sfd.FileName = setting.Program;
|
if (sfd.ShowDialog() == DialogResult.OK)
|
{
|
fname = sfd.FileName;
|
}
|
|
/*导出当前dataGridView中的所有数据到xls文件
|
1.引入库文件,新建lib文件夹，复制相关文件
|
2.在项目中添加对这几个dll的引用
|
3.在内存中建立 excel表文件 */
|
XSSFWorkbook workbook = new XSSFWorkbook();
|
workbook.CreateSheet("sheet1");////新建一个工作表
|
XSSFSheet SheetOne = (XSSFSheet)workbook.GetSheet("Sheet1"); ////获取名称为Sheet1的工作表,创建标题头
|
IRow row1 = SheetOne.CreateRow(0);////获取Sheet1工作表的首行,设置表头，获取列
|
//XSSFCell[] SheetCell = new XSSFCell[currentColumns.Count];
|
for (int i = 0; i < currentColumns.Count; i++)
|
{
|
row1.CreateCell(i).SetCellValue(currentColumns[i].Header.ToString());
|
}
|
|
for (int i = 0; i < dataView.Count; i++)
|
{
|
IRow rowTemp = SheetOne.CreateRow(i + 1);
|
for (int j = 0; j < currentColumns.Count; j++)
|
{
|
string name = ((System.Windows.Data.Binding)currentColumns[j].Binding).Path.Path;
|
var value = dataView[i][name].ToString();
|
rowTemp.CreateCell(j).SetCellValue(value);
|
}
|
}
|
|
using (FileStream fs = new FileStream(fname, FileMode.Create))
|
{
|
workbook.Write(fs);
|
}
|
ToastHelper.Success(Window.GetWindow(this), this.lang("App.ExportSuccess"));
|
}
|
catch (Exception ex)
|
{
|
log.Error("Export Data Failed.", ex);
|
ToastHelper.Error(Window.GetWindow(this), $"{this.lang("App.ExportFailed")}: {ex.Message}");
|
}
|
}
|
|
private void MenuItem_Click(object sender, RoutedEventArgs e)
|
{
|
DataGridColumn column = HMI.UI.DpUtil.DataGridHelper.GetSelectedColumn(dataTable);
|
}
|
}
|
}
|
