using DevExpress.Mvvm.POCO; using DevExpress.Utils; using DevExpress.XtraCharts; using DevExpress.XtraGrid.Columns; using DevExpress.XtraGrid.Views.Grid; using System.Data; using System.IO; using Yw.WinFrmUI; namespace PBS.WinFrmUI.DataAnalysis { public partial class EnergyAnalysis : DocumentPage { public EnergyAnalysis() { InitializeComponent(); InitialChart(); } private void barButtonItem1_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e) { //导入文件 var dlg = new System.Windows.Forms.OpenFileDialog(); dlg.Filter = "EXCEL 文件(*.xls)|*.xls"; dlg.CheckFileExists = true; if (dlg.ShowDialog() != System.Windows.Forms.DialogResult.OK) return; var datalist = new List(); var datatable = new DataTable(); ParseExcel(dlg.FileName, out datalist, out datatable); if (datalist.Any()) { SetBindingData(datalist, datatable); } } #region Private Variable private XYDiagram _diagram; private XYDiagramDefaultPane _default_pane; private AxisX _axis_x_flow; private AxisY _axis_y_head; private SecondaryAxisY _axis_y_eff; #endregion /// /// 初始化图表 /// private void InitialChart() { this.chartControl1.RuntimeHitTesting = true; this.chartControl1.Legend.Visibility = DefaultBoolean.True; _diagram = (XYDiagram)this.chartControl1.Diagram; _diagram.EnableAxisXScrolling = true; _diagram.EnableAxisYScrolling = true; _diagram.EnableAxisXZooming = true; _diagram.EnableAxisYZooming = true; _default_pane = _diagram.DefaultPane; _diagram.AxisX.DateTimeScaleOptions.MeasureUnit = DateTimeMeasureUnit.Second; _diagram.AxisX.DateTimeScaleOptions.ScaleMode = ScaleMode.Continuous; _axis_x_flow = _diagram.AxisX; _axis_y_head = _diagram.AxisY; _axis_y_eff = _diagram.SecondaryAxesY.GetAxisByName("AxisYEff"); _axis_y_eff.Alignment = AxisAlignment.Far; _axis_x_flow.GridLines.Visible = false; _axis_y_head.GridLines.Visible = false; _axis_y_eff.GridLines.Visible = false; this.chartControl1.CrosshairEnabled = DefaultBoolean.True; } /// /// 设置图表 /// public void SetBindingData(List vm_list, DataTable dataTable) { var view = gridControl1.MainView as GridView; view.Columns.Clear(); this.chartControl1.BeginInit(); this.chartControl1.Series.Clear(); for (int i = this.chartControl1.AnnotationRepository.Count - 1; i > 0; i--) { if (i == 0) break; this.chartControl1.AnnotationRepository.RemoveAt(i); } this.chartControl1.Legend.CustomItems.Clear(); if (IsInvalidData()) { this.chartControl1.EndInit(); return; } AxisXBase axis_x = null; AxisYBase axis_y = null; XYDiagramPaneBase pane = null; var colTime = new GridColumn(); colTime.FieldName = "时间"; colTime.Caption = "时间"; colTime.Visible = true; view.Columns.Add(colTime); var totalFlow = new List(); foreach (var vm in vm_list) { if (!vm.Name.Contains("压力")) { axis_x = _axis_x_flow; axis_y = _axis_y_head; pane = _default_pane; totalFlow = vm.Datas.Select(c => c.Y).ToList(); } else { axis_x = _axis_x_flow; axis_y = _axis_y_eff; pane = _default_pane; } var col = new GridColumn(); col.FieldName = vm.Name; col.Caption = vm.Name; col.Visible = true; view.Columns.Add(col); AddLineSeries(vm.Name, axis_x, axis_y, pane, vm.Datas); } double totalEl = 0; double totalOldEl = 0; double totalFy = 0; var tempdatas = vm_list.FirstOrDefault(c => c.Name.Contains("压力")); var tempflow = vm_list.FirstOrDefault(c => c.Name.Contains("流量")); foreach (var item in tempdatas.Datas) { var flow = tempflow.Datas.FirstOrDefault(c => c.Time == item.Time); if (flow != null) { var oldpressM = Yw.Pump.CalculationHelper.Mpa2M(Press); var newpressM = Yw.Pump.CalculationHelper.Mpa2M(item.Y); var oldel = Yw.Pump.CalculationHelper.CalcuP(flow.Y, oldpressM, Efficiency); var newel = Yw.Pump.CalculationHelper.CalcuP(flow.Y, newpressM, Efficiency); var el = oldel - newel; totalOldEl += oldel; totalEl += el; var Fy = ElecPriceHelper.CalcuShysElectricityFees(DateTime.Parse(item.Time), el); totalFy += Fy; } item.Y = Press;//Yw.Pump.CalculationHelper.CalcuP(); } AddLineSeries("原泵后压力", axis_x, _axis_y_eff, _default_pane, tempdatas.Datas); var min_x = vm_list.SelectMany(x => x.Datas).Min(x => DateTime.Parse(x.Time)); var max_x = vm_list.SelectMany(x => x.Datas).Max(x => DateTime.Parse(x.Time)); _axis_x_flow.NumericScaleOptions.AutoGrid = false; _axis_x_flow.WholeRange.SideMarginsValue = 0; _axis_x_flow.VisualRange.SideMarginsValue = 0; _axis_x_flow.WholeRange.SetMinMaxValues(min_x, max_x); _axis_x_flow.VisualRange.SetMinMaxValues(min_x, max_x); var diff_days = max_x - min_x; textEdit1.Text = Math.Round(diff_days.TotalDays).ToString(); textEdit2.Text = Math.Round(totalFlow.Sum() / 60).ToString(); textEdit3.Text = Math.Round(totalEl).ToString(); textEdit4.Text = Math.Round(totalEl / totalOldEl * 100).ToString(); textEdit5.Text = Math.Round(totalFy).ToString(); gridControl1.DataSource = dataTable; this.chartControl1.EndInit(); } //是否是无效数据 private bool IsInvalidData() { return false; } private void AddLineSeries(string id, AxisXBase axis_x, AxisYBase axis_y, XYDiagramPaneBase pane, List pt_list, DevExpress.XtraCharts.DashStyle dash = DevExpress.XtraCharts.DashStyle.Solid) { if (pt_list == null || !pt_list.Any()) return; var view = new DevExpress.XtraCharts.LineSeriesView(); view.LineStyle.DashStyle = dash; view.LineStyle.LineJoin = System.Drawing.Drawing2D.LineJoin.Round; view.LineStyle.Thickness = 2; if (dash != DashStyle.Solid) { view.LineStyle.Thickness = 3; } view.EnableAntialiasing = DefaultBoolean.True; view.MarkerVisibility = DefaultBoolean.False; view.AxisX = axis_x; view.AxisY = axis_y; view.EmptyPointOptions.Color = Color.Transparent; view.Pane = pane; var series_pt_list = pt_list.Select(x => new SeriesPoint(DateTime.Parse(x.Time), x.Y)).ToArray(); var series = new DevExpress.XtraCharts.Series(); series.Tag = id; //series.ShowInLegend = false; series.Name = id; series.ArgumentScaleType = DevExpress.XtraCharts.ScaleType.DateTime; series.DateTimeSummaryOptions.MeasureUnit = DateTimeMeasureUnit.Second; series.LabelsVisibility = DevExpress.Utils.DefaultBoolean.False; series.CrosshairEnabled = DefaultBoolean.True; series.CrosshairLabelPattern = id + ":{V:N2} {A:yyyy-MM-dd HH:mm:ss}"; series.ToolTipEnabled = DefaultBoolean.False; series.SeriesPointsSorting = SortingMode.None; series.Visible = true; series.View = view; series.Points.AddRange(series_pt_list); this.chartControl1.Series.Add(series); } public static string ParseExcel(string fileName, out List datalist, out DataTable dataTable) { datalist = new List(); dataTable = new DataTable(); if (!File.Exists(fileName)) return "文件不存在"; int line = 0; //初始化文件 NPOI.HSSF.UserModel.HSSFWorkbook theBook = null; using (FileStream file = new FileStream(fileName, FileMode.Open, FileAccess.ReadWrite)) { theBook = new NPOI.HSSF.UserModel.HSSFWorkbook(file); } //检查表格是否符合 NPOI.SS.UserModel.ISheet sheet1 = theBook.GetSheet("Sheet1"); if (sheet1 == null) { sheet1 = theBook.GetSheetAt(0); if (sheet1 == null) return ("无Sheet数据"); } //标题行 int title_line_index = 0; var row_title = sheet1.GetRow(title_line_index); if (row_title == null) { return ("第一行第一列不能空"); } //开始读取的行 int start_line = title_line_index + 1; var cell_0 = row_title.GetCell(0); if (cell_0 == null) { return ("无法读取表头文件"); } var totalcell = row_title.Cells.Count; for (int i = 0; i < totalcell; i++) { var row_cell = row_title.GetCell(i); if (i > 0) { if (row_cell != null) { datalist.Add(new FlowPressChartViewModel() { Name = row_cell.StringCellValue, Datas = new List() }); } dataTable.Columns.Add(row_cell.StringCellValue, typeof(double)); } else { dataTable.Columns.Add(row_cell.StringCellValue, typeof(string)); } } NPOI.SS.UserModel.IRow row_temp = null; NPOI.SS.UserModel.ICell cell; for (line = start_line; line < 10000; line++) { row_temp = sheet1.GetRow(line); if (row_temp == null) break; var rowData = new object[totalcell]; for (int i = 0; i < totalcell; i++) { try { if (i > 0) { var cell_temp = row_temp.GetCell(i); double yvalue = cell_temp.NumericCellValue; var timevalue = row_temp.GetCell(0).StringCellValue; datalist[i - 1].Datas.Add(new TimeChartViewModel() { Time = timevalue, //DateTime.FromOADate(timevalue).ToString("YYYY-MM-dd HH:mm:ss"), Y = yvalue, }); rowData[i] = yvalue; } else { var timevalue = row_temp.GetCell(0).StringCellValue; rowData[i] = timevalue;//DateTime.FromOADate(timevalue).ToString("YYYY-MM-dd HH:mm:ss"); } } catch (Exception ex) { //var a = ex.Message; } } dataTable.Rows.Add(rowData); } return ""; } double Press = 0.6; double Efficiency = 75; SetPressForm dlg = new SetPressForm(); private void barButtonItem2_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e) { if (dlg.ShowDialog() == DialogResult.OK) { Press = dlg.Press; Efficiency = dlg.Efficiency; } } } }