using System;
|
using System.Collections.Generic;
|
using System.IO;
|
|
namespace IStation.WinFrmUI.Monitor
|
{
|
/// <summary>
|
///
|
/// </summary>
|
public class PhartExcelHelper
|
{
|
|
|
#region Parse
|
|
public static string ParseUniversalExcel(string file_path, out List<Model.CurvePoint> list)
|
{
|
list = new List<Model.CurvePoint>();
|
try
|
{
|
if (!File.Exists(file_path))
|
return "文件不存在";
|
|
//初始化文件
|
NPOI.HSSF.UserModel.HSSFWorkbook theBook = null;
|
using (FileStream file = new FileStream(file_path, 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;
|
//x 列
|
int col_index_x = 1;
|
//y 列
|
int col_index_y = 2;
|
|
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 ("无法读取表头文件");
|
}
|
else if (cell_0.StringCellValue.Contains("序号"))
|
{
|
col_index_x = 1;
|
col_index_y = 2;
|
}
|
else if (cell_0.StringCellValue.Contains("流量"))
|
{
|
col_index_x = 0;
|
col_index_y = 1;
|
}
|
|
NPOI.SS.UserModel.IRow row_temp;
|
NPOI.SS.UserModel.ICell cell;
|
|
int line = 0;
|
for (line = start_line; line < 1000; line++)
|
{
|
row_temp = sheet1.GetRow(line);
|
if (row_temp == null)
|
break;
|
|
cell = row_temp.GetCell(col_index_x);
|
if (cell == null)
|
break;
|
if (!ParseCellValue(cell, out double x))
|
break;
|
|
cell = row_temp.GetCell(col_index_y);
|
if (cell == null)
|
break;
|
if (!ParseCellValue(cell, out double y))
|
break;
|
|
list.Add(new Model.CurvePoint(x, y));
|
}
|
}
|
catch (Exception)
|
{
|
return "文件异常!";
|
}
|
return "";
|
}
|
|
public static string ParsePumpExcel(string fileName, out List<Model.CurvePoint> qh, out List<Model.CurvePoint> qe, out List<Model.CurvePoint> qp)
|
{
|
qh = new List<Model.CurvePoint>();
|
qe = new List<Model.CurvePoint>();
|
qp = new List<Model.CurvePoint>();
|
try
|
{
|
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;
|
//流量列
|
int col_index_q = 1;
|
//扬程列
|
int col_index_h = 2;
|
//效率列
|
int col_index_e = 3;
|
//功率列
|
int col_index_p = 4;
|
|
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 ("无法读取表头文件");
|
}
|
else if (cell_0.StringCellValue.Contains("序号"))
|
{
|
col_index_q = 1;
|
col_index_h = 2;
|
col_index_e = 3;
|
col_index_p = 4;
|
}
|
else if (cell_0.StringCellValue.Contains("流量"))
|
{
|
col_index_q = 0;
|
col_index_h = 1;
|
col_index_e = 2;
|
col_index_p = 3;
|
}
|
|
|
NPOI.SS.UserModel.IRow row_temp = null;
|
NPOI.SS.UserModel.ICell cell;
|
|
var list = new List<(double Q, double H, double P, double E)>();
|
for (line = start_line; line < 1000; line++)
|
{
|
|
row_temp = sheet1.GetRow(line);
|
if (row_temp == null)
|
break;
|
|
cell = row_temp.GetCell(col_index_q);
|
if (cell == null)
|
break;
|
if (!ParseCellValue(cell, out double flow))
|
break;
|
if (flow < 0)
|
break;
|
|
cell = row_temp.GetCell(col_index_h);
|
if (cell == null)
|
break;
|
if (!ParseCellValue(cell, out double head))
|
break;
|
if (head < 0)
|
break;
|
|
cell = row_temp.GetCell(col_index_e);
|
if (cell == null)
|
break;
|
if (!ParseCellValue(cell, out double eff))
|
{
|
eff = -1;
|
}
|
|
|
cell = row_temp.GetCell(col_index_p);
|
if (cell == null)
|
break;
|
if (!ParseCellValue(cell, out double power))
|
{
|
power = -1;
|
}
|
|
if (eff > 0)
|
{
|
|
power = Model.CurveCalcuHelper.CalculateP(flow, head, eff);
|
}
|
else
|
{
|
eff = Model.CurveCalcuHelper.CalculateE(flow, head, power);
|
}
|
|
if (eff > 99)
|
{
|
throw new Exception("效率大于100%");
|
}
|
|
qh.Add(new Model.CurvePoint(flow, head));
|
qe.Add(new Model.CurvePoint(flow, eff));
|
qp.Add(new Model.CurvePoint(flow, power));
|
}
|
|
return "";
|
|
}
|
catch (Exception)
|
{
|
return "文件异常!";
|
}
|
}
|
|
public static List<Model.CurveAnalyzePoint> ParsePumpExcel(string fileName)
|
{
|
var pt_list = new List<Model.CurveAnalyzePoint>();
|
try
|
{
|
if (!File.Exists(fileName))
|
return default;
|
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)
|
throw new Exception("无Sheet数据");
|
}
|
|
//标题行
|
int title_line_index = 0;
|
//流量列
|
int col_index_t = 0;
|
//流量列
|
int col_index_q = 1;
|
//扬程列
|
int col_index_h = 2;
|
|
var row_title = sheet1.GetRow(title_line_index);
|
if (row_title == null)
|
{
|
throw new Exception("第一行第一列不能空");
|
}
|
|
//开始读取的行
|
int start_line = title_line_index + 1;
|
var cell_0 = row_title.GetCell(0);
|
if (cell_0 == null)
|
{
|
throw new Exception("无法读取表头文件");
|
}
|
|
|
|
NPOI.SS.UserModel.IRow row_temp = null;
|
NPOI.SS.UserModel.ICell cell;
|
|
var list = new List<(double Q, double H, double P, double E)>();
|
for (line = start_line; line < 1000; line++)
|
{
|
|
row_temp = sheet1.GetRow(line);
|
if (row_temp == null)
|
break;
|
|
cell = row_temp.GetCell(col_index_t);
|
if (cell == null)
|
break;
|
if (!ParseCellValue(cell, out DateTime time))
|
break;
|
if (time == DateTime.MinValue)
|
break;
|
|
cell = row_temp.GetCell(col_index_q);
|
if (cell == null)
|
break;
|
if (!ParseCellValue(cell, out double flow))
|
break;
|
if (flow < 0)
|
break;
|
|
cell = row_temp.GetCell(col_index_h);
|
if (cell == null)
|
break;
|
if (!ParseCellValue(cell, out double head))
|
break;
|
if (head < 0)
|
break;
|
|
//cell = row_temp.GetCell(col_index_e);
|
//if (cell == null)
|
// break;
|
//if (!ParseCellValue(cell, out double eff))
|
//{
|
// eff = -1;
|
//}
|
|
|
//cell = row_temp.GetCell(col_index_p);
|
//if (cell == null)
|
// break;
|
//if (!ParseCellValue(cell, out double power))
|
//{
|
// power = -1;
|
//}
|
|
//if (eff > 0)
|
//{
|
|
// power = Model.CurveCalcuHelper.CalculateP(flow, head, eff);
|
//}
|
//else
|
//{
|
// eff = Model.CurveCalcuHelper.CalculateE(flow, head, power);
|
//}
|
|
//if (eff > 99)
|
//{
|
// throw new Exception("效率大于100%");
|
//}
|
|
var pt = new Model.CurveAnalyzePoint();
|
pt.Time = time;
|
pt.Q = flow;
|
pt.P1 = 0;
|
pt.P2 = 0;
|
pt.H = head;
|
pt.P = 3;
|
pt.E = 99;
|
pt.N = 2900;
|
pt.HZ = 50;
|
pt.Status = 1;
|
|
pt_list.Add(pt);
|
}
|
|
return pt_list;
|
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
}
|
|
private static bool ParseCellValue(NPOI.SS.UserModel.ICell cell, out double cell_value)
|
{
|
cell_value = 0;
|
if (cell.CellType == NPOI.SS.UserModel.CellType.Numeric)
|
{
|
cell_value = cell.NumericCellValue;
|
return true;
|
}
|
|
if (cell.CellType == NPOI.SS.UserModel.CellType.String)
|
{
|
if (double.TryParse(cell.StringCellValue, out double value))
|
{
|
cell_value = value;
|
return true;
|
}
|
}
|
|
return false;
|
}
|
|
|
private static bool ParseCellValue(NPOI.SS.UserModel.ICell cell, out DateTime cell_value)
|
{
|
cell_value = DateTime.MinValue;
|
if (cell.CellType == NPOI.SS.UserModel.CellType.Numeric)
|
{
|
cell_value = cell.DateCellValue;
|
return true;
|
}
|
|
if (cell.CellType == NPOI.SS.UserModel.CellType.String)
|
{
|
if (DateTime.TryParse(cell.StringCellValue, out DateTime value))
|
{
|
cell_value = value;
|
return true;
|
}
|
}
|
|
return false;
|
}
|
|
#endregion
|
|
}
|
}
|
