优化一些东西；

alielie

2025-11-25 006c98e5650b530e5220f279c1e803fefe69d40d

 HMI.DataViewer/DataViewer.xaml.cs

@@ -2,6 +2,8 @@
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;
@@ -10,7 +12,9 @@
using System.Linq;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Controls.Primitives;
using System.Windows.Forms;
using System.Windows.Media;

namespace HMI.DataViewer
{
@@ -45,15 +49,20 @@

        private void Button_Click(object sender, RoutedEventArgs e)
        {
            Button btn = sender as Button;
            System.Windows.Controls.Button btn = sender as System.Windows.Controls.Button;
            switch (btn.Name)
            {
                case "addBtn":
                    OpenSettingModal(new DataBase_Setting());
                    OpenSettingModal(-1, new DataBase_Setting());
                    break;
                case "editBtn":
                    EditSettingModal();
                    break;
                case "rfhBtn":
                    LoadData();
                    break;
                case "delBtn":
                    DeleteDatasource();
                    break;
                case "srhBtn":
                    SearchData();
@@ -61,81 +70,74 @@
                case "resetBtn":
                    ResetDataTable();
                    break;
                case "exportBtn":
                    ExportData();
                    break;
                default:
                    break;
            }
        }


        private void OpenSettingModal(DataBase_Setting data)
        private void DeleteDatasource()
        {
            DbSettingModal modal = new DbSettingModal(log)
            {
                Title = "新增数据源",
                Owner = Window.GetWindow(this)
            };
            if (modal.Open(-1, data, Settings, SourceFilePath) == true)
            {
                LoadData();
            }
        }

        private void SearchData()
        {
            string program = programInput.Text;
            if (!Map.ContainsKey(program))
            {
                ToastHelper.Error(Window.GetWindow(this), "参数异常，请刷新重试");
                return;
            }

            var datas = Datas?.Where(d => !string.IsNullOrEmpty(d.Value)).ToList();
            if (datas == null || datas.Count <= 0)
            {
                ToastHelper.Warning(Window.GetWindow(this), "请设置查询条件值");
                return;
            }

            DataBase_Setting setting = Map[program];
            

            string fields = string.Join(",", Datas?.Select(d => $"[{d.Key}]").ToList());

            string conditon = 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}'";
            }
            conditon = string.IsNullOrEmpty(conditon) ? string.Empty : $" WHERE {conditon}";
            
            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);

                dataTable.ItemsSource = dt.DefaultView;
                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($"Select Data Failed. SQL: {sql}", ex);
                ToastHelper.Error(Window.GetWindow(this), $"查询失败！{ex.Message}");

                log.Error("Delete Datasource Failed.", ex);
                ToastHelper.Success(Window.GetWindow(this), $"{this.lang("App.DeleteFailed")}: {ex.Message}");
            }
            finally
        }

        private void EditSettingModal()
        {
            var key = programInput.Text;
            if (string.IsNullOrEmpty(key))
            {
                helper?.Close();
                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();
            }
        }

@@ -164,26 +166,43 @@

        private void LoadData()
        {
            ObservableCollection<DataBase_Setting> settings = new ObservableCollection<DataBase_Setting>();
            if (File.Exists(SourceFilePath))
            {
                List<string> names = new List<string>();
                Settings = XmlHelper.DeserializeFromXml<ObservableCollection<DataBase_Setting>>(SourceFilePath);
                Map.Clear();
                foreach (var data in Settings)
                {
                    Map.Add(data.Program, data);
                    names.Add(data.Program);
                }
                programInput.ItemsSource = names;
                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 programInput_SelectionChanged(object sender, SelectionChangedEventArgs e)
        private void ResetInput()
        {
            var key = e.AddedItems[0].ToString();
            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))
            {
                ToastHelper.Error(Window.GetWindow(this), "参数异常，请刷新重试");
                ResetInput();
                return;
            }
            DataBase_Setting setting = Map[key];
@@ -191,24 +210,266 @@
                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)
            {
                AddDataGridTextColumn(data.Name, data.Key);
                columns.Add(AddDataGridTextColumn(data.Name, data.Key));
            }
            currentColumns = columns;
        }

        private void AddDataGridTextColumn(string header, string bindingPath)
        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 Binding(bindingPath)
                ,
                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);
        }
    }
}