using DevExpress.XtraEditors;
|
using NPOI.HSSF.UserModel;
|
using NPOI.SS.UserModel;
|
using System;
|
using System.IO;
|
using System.Linq;
|
using System.Windows.Forms;
|
|
namespace IStation.WinFrmUI.Monitor
|
{
|
public class ExportValidatedDataHelper
|
{
|
//验证数据
|
public static void Export(long monitorDataSourcesId, long stationId, DateTime date)
|
{
|
var packet = new BLL.StationSignalRecordPacket().Get(monitorDataSourcesId, stationId, date.Year, date.Month);
|
var list = packet?.StationSignalRecords?.ToList();
|
list = list?.Where(x => x.Time.Day == date.Day && x.Time.Minute == 0 && x.Time.Second == 0).ToList();
|
if (list == null || !list.Any())
|
{
|
XtraMessageBox.Show("无数据！");
|
return;
|
}
|
|
try
|
{
|
var dlg = new SaveFileDialog();
|
dlg.Filter = "EXCEL 文件(*.xls)|*.xls";
|
dlg.FileName = date.ToString("D");
|
if (dlg.ShowDialog() != DialogResult.OK)
|
return;
|
HSSFWorkbook theBook = new HSSFWorkbook();
|
var theSheet1 = theBook.CreateSheet("Sheet1");
|
|
IRow rowTile = theSheet1.CreateRow(0);
|
rowTile.CreateCell(0).SetCellValue("时间");
|
rowTile.CreateCell(1).SetCellValue("总流量");
|
rowTile.CreateCell(2).SetCellValue("总扬程");
|
rowTile.CreateCell(3).SetCellValue("总功率");
|
rowTile.CreateCell(4).SetCellValue("单泵转速字典");
|
rowTile.CreateCell(5).SetCellValue("单泵流量字典");
|
rowTile.CreateCell(6).SetCellValue("单泵扬程字典");
|
rowTile.CreateCell(7).SetCellValue("单泵功率字典");
|
|
int rowIndex = 1;
|
DateTime current = DateTime.Now;
|
double totalHead, totalPower;
|
foreach (var record in list)
|
{
|
current = record.Time;
|
totalHead = record.PumpSignalRecords.Sum(x => x.Head) / record.PumpRunCount;
|
totalPower = record.PumpSignalRecords.Sum(x => x.InstantaneousPower);
|
|
var flag_rpm_dict = record.PumpSignalRecords.ToDictionary(x => x.Flag, x => x.Rpm);
|
var flag_flow_dict = record.PumpSignalRecords.ToDictionary(x => x.Flag, x => x.FlowRate);
|
var flag_head_dict = record.PumpSignalRecords.ToDictionary(x => x.Flag, x => x.Head);
|
var flag_power_dict = record.PumpSignalRecords.ToDictionary(x => x.Flag, x => x.InstantaneousPower);
|
|
|
var row = theSheet1.CreateRow(rowIndex);
|
rowIndex++;
|
|
int col = 0;
|
row.CreateCell(col).SetCellValue(current.ToString("G"));
|
col++;
|
|
|
row.CreateCell(col).SetCellValue(Trans(totalHead));
|
col++;
|
|
row.CreateCell(col).SetCellValue(Trans(totalPower));
|
col++;
|
|
row.CreateCell(col).SetCellValue(JsonHelper.Object2Json(flag_rpm_dict));
|
col++;
|
|
row.CreateCell(col).SetCellValue(JsonHelper.Object2Json(flag_flow_dict));
|
col++;
|
|
row.CreateCell(col).SetCellValue(JsonHelper.Object2Json(flag_head_dict));
|
col++;
|
|
row.CreateCell(col).SetCellValue(JsonHelper.Object2Json(flag_power_dict));
|
col++;
|
}
|
|
|
//强制Excel在打开时重新计算所有公式
|
theSheet1.ForceFormulaRecalculation = true;
|
using (FileStream fs = File.OpenWrite(dlg.FileName))
|
{
|
theBook.Write(fs);
|
}
|
}
|
catch (Exception ex)
|
{
|
XtraMessageBox.Show(ex.ToString());
|
return;
|
}
|
XtraMessageBox.Show("导出成功");
|
|
}
|
|
|
/// <summary>
|
/// 转换
|
/// </summary>
|
private static string DoubleTrans(double doubleValue)
|
{
|
if (doubleValue < 1)
|
return doubleValue.ToString("0.0000");
|
else if (doubleValue < 10)
|
return doubleValue.ToString("0.000");
|
else if (doubleValue < 100)
|
return doubleValue.ToString("0.00");
|
else
|
return doubleValue.ToString("0.0");
|
}
|
|
/// <summary>
|
/// 转换
|
/// </summary>
|
private static double Trans(double doubleValue)
|
{
|
if (doubleValue < 1)
|
return Math.Round(doubleValue, 4);
|
else if (doubleValue < 10)
|
return Math.Round(doubleValue, 3);
|
else if (doubleValue < 100)
|
return Math.Round(doubleValue, 2);
|
else
|
return Math.Round(doubleValue, 1);
|
}
|
|
|
}
|
}
|
