using NPOI.HSSF.UserModel;
|
using NPOI.SS.UserModel;
|
using System;
|
using System.Collections.Generic;
|
using System.IO;
|
using System.Linq;
|
|
namespace IStation.WinFrmUI
|
{
|
/// <summary>
|
/// 导出 WaterDesk 所需数据
|
/// </summary>
|
public partial class ExportTestHelper
|
{
|
|
public class TimeValue
|
{
|
public TimeValue() { }
|
public TimeValue(DateTime t,double v)
|
{
|
this.Time= t;
|
this.Value= v;
|
}
|
public DateTime Time { get; set; }
|
public double Value { get; set; }
|
|
}
|
|
public class DataColumn
|
{
|
public string Title { get; set; }
|
public int Index { get; set; }
|
public List<TimeValue> TimeValueList { get; set; }
|
}
|
|
static BLL.StationSignalRecordPacket _packet = new BLL.StationSignalRecordPacket();
|
|
|
/// <summary>
|
/// 导出全部
|
/// </summary>
|
/// <param name="monitorDataSourcesId"></param>
|
/// <param name="stationId"></param>
|
/// <param name="year"></param>
|
/// <param name="month"></param>
|
/// <param name="timeStep"></param>
|
/// <returns></returns>
|
public static bool Export(long monitorDataSourcesId, List<long> monitor_ids, int year, int month, int timeStep = 300)
|
{
|
|
|
var bllMonitorPoint = new BLL.MonitorPoint();
|
var bllMonitorDataSet = new BLL.MonitorDataSet();
|
|
var dataColumnDict = new Dictionary<string, List<TimeValue>>();
|
var stationMonitorPointList = new List<Model.MonitorPointExSignalWithSignalType>();
|
var signalIds = new List<long>();
|
|
var monitorPoints_station = bllMonitorPoint.GetExSignalWithSignalTypeByIds(monitor_ids);
|
//monitorPoints_station = monitorPoints_station?.Where(x => !string.IsNullOrEmpty(x.TagName)).ToList();
|
//stationMonitorPointList = monitorPoints_station?.Where(x => x.Flags != null && x.Flags.Contains(IStation.Flags.总站)).ToList();
|
stationMonitorPointList = monitorPoints_station;
|
if (stationMonitorPointList != null && stationMonitorPointList.Any())
|
{
|
signalIds.AddRange(stationMonitorPointList.Select(x => x.SignalID));
|
foreach (var item in stationMonitorPointList)
|
{
|
dataColumnDict[item.Name] = new List<TimeValue>();
|
}
|
}
|
|
signalIds = signalIds.Distinct().ToList();
|
|
var bllSummary = new BLL.MonitorDataSetSummary();
|
var summaries = bllSummary.GetAllByDate(monitorDataSourcesId, year, month);
|
if (summaries == null || !summaries.Any())
|
return default;
|
|
var minTime = summaries.Min(x => x.MinTime);
|
var maxTime = summaries.Max(x => x.MaxTime);
|
var totalSeconds = (int)(maxTime - minTime).TotalSeconds;
|
|
var startDay = minTime.AddDays(1);
|
var days = (maxTime - minTime).Days;
|
var capacity = _packet.CalcuCapacity(days, timeStep);// 每个月的数据量
|
|
|
var findIndexDict = new Dictionary<long, int>();// 查找数据下标
|
var recordDict = new Dictionary<long, double>();// 数据字典
|
signalIds.ForEach(x =>
|
{
|
findIndexDict.Add(x, 0);
|
recordDict.Add(x, IStation.Error.Default);
|
});
|
|
var signalRecordPackets = bllMonitorDataSet.GetSignalRecordPacket(monitorDataSourcesId, signalIds, year, month);
|
if (signalRecordPackets == null || !signalRecordPackets.Any())
|
return default;
|
|
|
for (int i = 0; i <= days; i++) //按天整理数据
|
{
|
var day = startDay.AddDays(i);
|
for (int seconds = 0; seconds < _packet.TotalSecondsOfDay; seconds += timeStep) //00点的数据不要
|
{
|
var realTime = day.AddSeconds(seconds);
|
_packet.GetRecordDict(realTime, signalRecordPackets, findIndexDict, recordDict, timeStep, out bool existRecord);
|
if (!existRecord)
|
continue;
|
|
if (stationMonitorPointList != null && stationMonitorPointList.Any())
|
{
|
foreach (var stationMonitorPoint in stationMonitorPointList)
|
{
|
var time = realTime;
|
var value = recordDict[stationMonitorPoint.SignalID];
|
//if (stationMonitorPoint.SignalType == IStation.SignalType.压力)
|
//{
|
// value = Model.CurveCalcuHelper.Mpa2M(value);
|
//}
|
//else if (stationMonitorPoint.SignalType == IStation.SignalType.瞬时流量)
|
//{
|
// value = value < 0 ? 0 : value;
|
//}
|
var timeValue = new TimeValue(time, value);
|
dataColumnDict[stationMonitorPoint.Name].Add(timeValue);
|
}
|
}
|
|
}
|
}
|
|
var mode_excel_data_column_list = new List<DataColumn>();
|
var pressure_scada_data_column_list = new List<DataColumn>();
|
var flow_scada_data_column_list = new List<DataColumn>();
|
var index = 1;
|
foreach (var item in dataColumnDict)
|
{
|
var dataColumn = new DataColumn();
|
dataColumn.Title = item.Key;
|
dataColumn.Index = index;
|
dataColumn.TimeValueList = item.Value;
|
mode_excel_data_column_list.Add(dataColumn);
|
index++;
|
}
|
|
var root_folder = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "问题排查");
|
if (!Directory.Exists(root_folder))
|
{
|
Directory.CreateDirectory(root_folder);
|
}
|
var mode_excel_file_name = root_folder + "\\" + $"陈行数据.xls";
|
Export2Excel(mode_excel_file_name, mode_excel_data_column_list);
|
return true;
|
}
|
|
|
public static bool Export2Excel(string fileName, List<DataColumn> dataColumnList)
|
{
|
//try
|
//{
|
HSSFWorkbook theBook = new HSSFWorkbook();
|
var theSheet1 = theBook.CreateSheet("Sheet1");
|
|
IRow rowTile = theSheet1.CreateRow(0);
|
foreach (var info in dataColumnList)
|
{
|
rowTile.CreateCell(info.Index).SetCellValue(info.Title);
|
}
|
|
foreach (var item in dataColumnList)
|
{
|
for (int i = 1; i < item.TimeValueList.Count; i++)
|
{
|
var record = item.TimeValueList[i - 1];
|
|
IRow row;
|
if (theSheet1.LastRowNum < i)
|
{
|
row = theSheet1.CreateRow(i);
|
row.CreateCell(0).SetCellValue(record.Time.ToString("G"));
|
}
|
else
|
{
|
row = theSheet1.GetRow(i);
|
}
|
|
row.CreateCell(item.Index).SetCellValue(record.Value == IStation.Error.Default ? 0 : record.Value);
|
}
|
}
|
|
//强制Excel在打开时重新计算所有公式
|
theSheet1.ForceFormulaRecalculation = true;
|
using (FileStream fs = File.OpenWrite(fileName))
|
{
|
theBook.Write(fs);
|
}
|
|
return true;
|
}
|
|
|
public static void ExportCSV(string fileName, List<DataColumn> dataColumnList)
|
{
|
using (var fs = new FileStream(fileName, System.IO.FileMode.Create, System.IO.FileAccess.Write, FileShare.ReadWrite))
|
using (var sw = new StreamWriter(fs, System.Text.Encoding.UTF8))
|
{
|
sw.WriteLine("ScadaID,Time,Value");
|
foreach (var item in dataColumnList)
|
{
|
for (int i = 0; i < item.TimeValueList.Count; i++)
|
{
|
var r = item.TimeValueList[i];
|
var v = r.Value == IStation.Error.Default ? 0 : r.Value;
|
sw.WriteLine($"{item.Title},{r.Time:yyyy-MM-dd HH:mm:ss},{v}");
|
}
|
}
|
}
|
}
|
|
}
|
}
|
