namespace Yw.WinFrmUI
|
{
|
/// <summary>
|
///
|
/// </summary>
|
public class PhartExcelHelper
|
{
|
|
#region Export
|
|
public static void ExportUniversalTemplate(string file_path, Yw.Ahart.eCurveType curve_type)
|
{
|
var title = PhartAxisTitleHelper.Get(curve_type);
|
string col_x_title = title.AxisXTile;
|
string col_y_title = title.AxisYTitle;
|
|
NPOI.HSSF.UserModel.HSSFWorkbook theBook = new NPOI.HSSF.UserModel.HSSFWorkbook();
|
var theSheet1 = theBook.CreateSheet("Sheet1");
|
|
NPOI.SS.UserModel.IRow rowTile = theSheet1.CreateRow(0);
|
rowTile.CreateCell(0).SetCellValue(col_x_title);
|
rowTile.CreateCell(1).SetCellValue(col_y_title);
|
|
|
//强制Excel在打开时重新计算所有公式
|
theSheet1.ForceFormulaRecalculation = true;
|
using FileStream fs = File.OpenWrite(file_path);
|
theBook.Write(fs);
|
|
}
|
|
public static void ExportPumpTemplate(string file_path)
|
{
|
NPOI.HSSF.UserModel.HSSFWorkbook theBook = new NPOI.HSSF.UserModel.HSSFWorkbook();
|
var theSheet1 = theBook.CreateSheet("Sheet1");
|
|
NPOI.SS.UserModel.IRow rowTile = theSheet1.CreateRow(0);
|
rowTile.CreateCell(0).SetCellValue("流量");
|
rowTile.CreateCell(1).SetCellValue("扬程");
|
rowTile.CreateCell(2).SetCellValue("效率");
|
rowTile.CreateCell(3).SetCellValue("功率");
|
|
|
theSheet1.ForceFormulaRecalculation = true;
|
using FileStream fs = File.OpenWrite(file_path);
|
theBook.Write(fs);
|
|
}
|
|
public static void ExportValveTemplate(string file_path)
|
{
|
NPOI.HSSF.UserModel.HSSFWorkbook theBook = new NPOI.HSSF.UserModel.HSSFWorkbook();
|
|
var theSheet1 = theBook.CreateSheet("流量损失");
|
NPOI.SS.UserModel.IRow rowTile1 = theSheet1.CreateRow(0);
|
rowTile1.CreateCell(0).SetCellValue("开度 %");
|
rowTile1.CreateCell(1).SetCellValue("流量 m³/h");
|
rowTile1.CreateCell(2).SetCellValue("水头损失 m");
|
|
var theSheet2 = theBook.CreateSheet("开度损失");
|
NPOI.SS.UserModel.IRow rowTile2 = theSheet2.CreateRow(0);
|
rowTile2.CreateCell(0).SetCellValue("开度 %");
|
rowTile2.CreateCell(1).SetCellValue("损失系数 k");
|
|
|
theSheet1.ForceFormulaRecalculation = true;
|
theSheet2.ForceFormulaRecalculation = true;
|
|
using FileStream fs = File.OpenWrite(file_path);
|
theBook.Write(fs);
|
}
|
|
|
#endregion
|
|
#region Parse
|
|
public static string ParseUniversalExcel(string file_path, out List<Yw.Geometry.Point2d> list)
|
{
|
list = new List<Geometry.Point2d>();
|
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(x, y));
|
}
|
}
|
catch (Exception ex)
|
{
|
return "文件异常!";
|
}
|
return "";
|
}
|
|
public static string ParsePumpExcel(string fileName, out List<Yw.Geometry.Point2d> qh, out List<Yw.Geometry.Point2d> qe, out List<Yw.Geometry.Point2d> qp)
|
{
|
qh = new List<Geometry.Point2d>();
|
qe = new List<Geometry.Point2d>();
|
qp = new List<Geometry.Point2d>();
|
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 = Yw.Pump.CalculationHelper.CalcuP(flow, head, eff);
|
}
|
else
|
{
|
eff = Yw.Pump.CalculationHelper.CalcuE(flow, head, power);
|
}
|
|
if (eff > 99)
|
{
|
throw new Exception("效率大于100%");
|
}
|
|
qh.Add(new Geometry.Point2d(flow, head));
|
qe.Add(new Geometry.Point2d(flow, eff));
|
qp.Add(new Geometry.Point2d(flow, power));
|
}
|
|
return "";
|
|
}
|
catch (Exception ex)
|
{
|
return "文件异常!";
|
}
|
}
|
|
public static string ParseValveExcel(string fileName, out List<(Yw.Ahart.eCurveType CurveType, List<Yw.Geometry.Point2d> DefPointList, int Opening)> list)
|
{
|
list = new List<(Ahart.eCurveType CurveType, List<Geometry.Point2d> DefPointList, int Opening)>();
|
try
|
{
|
if (!File.Exists(fileName))
|
return "文件不存在";
|
int line = 0;
|
|
//初始化文件
|
NPOI.HSSF.UserModel.HSSFWorkbook theBook = null;
|
using (FileStream file = new(fileName, FileMode.Open, FileAccess.ReadWrite))
|
theBook = new NPOI.HSSF.UserModel.HSSFWorkbook(file);
|
|
|
//检查表格是否符合
|
NPOI.SS.UserModel.ISheet sheet_ql = theBook.GetSheet("流量损失");
|
if (sheet_ql != null)
|
{
|
//标题行
|
int title_line_index = 0;
|
//开度列
|
int col_index_opening = 0;
|
//流量列
|
int col_index_flow = 1;
|
//水头损失列
|
int col_index_head_loss = 2;
|
|
var row_title = sheet_ql.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 ("无法读取表头文件");
|
}
|
|
|
|
NPOI.SS.UserModel.IRow row_temp = null;
|
NPOI.SS.UserModel.ICell cell;
|
|
var cell_value_list = new List<(double Opening, double Flow, double HeadLoss)>();
|
for (line = start_line; line < 1000; line++)
|
{
|
row_temp = sheet_ql.GetRow(line);
|
if (row_temp == null)
|
break;
|
|
cell = row_temp.GetCell(col_index_opening);
|
if (cell == null)
|
break;
|
if (!ParseCellValue(cell, out double opening))
|
break;
|
if (opening < 0 || opening > 100)
|
break;
|
|
cell = row_temp.GetCell(col_index_flow);
|
if (cell == null)
|
break;
|
if (!ParseCellValue(cell, out double flow))
|
break;
|
if (flow < 0)
|
break;
|
|
cell = row_temp.GetCell(col_index_head_loss);
|
if (cell == null)
|
break;
|
if (!ParseCellValue(cell, out double head_loss))
|
break;
|
if (head_loss < 0)
|
break;
|
|
cell_value_list.Add((opening, flow, head_loss));
|
}
|
|
var opening_group = cell_value_list.GroupBy(x => x.Opening);
|
foreach (var item in opening_group)
|
{
|
var pt_list = item.Select(x => new Yw.Geometry.Point2d(x.Flow, x.HeadLoss));
|
list.Add((Yw.Ahart.eCurveType.QL, pt_list.ToList(), (int)item.Key));
|
}
|
}
|
|
|
//检查表格是否符合
|
NPOI.SS.UserModel.ISheet sheet_ol = theBook.GetSheet("开度损失");
|
if (sheet_ol != null)
|
{
|
//标题行
|
int title_line_index = 0;
|
//开度列
|
int col_index_opening = 0;
|
//损失系数列
|
int col_index_k = 1;
|
|
var row_title = sheet_ol.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 ("无法读取表头文件");
|
}
|
|
|
|
NPOI.SS.UserModel.IRow row_temp = null;
|
NPOI.SS.UserModel.ICell cell;
|
|
var pt_list = new List<Yw.Geometry.Point2d>();
|
for (line = start_line; line < 1000; line++)
|
{
|
row_temp = sheet_ol.GetRow(line);
|
if (row_temp == null)
|
break;
|
|
cell = row_temp.GetCell(col_index_opening);
|
if (cell == null)
|
break;
|
if (!ParseCellValue(cell, out double opening))
|
break;
|
if (opening < 0 || opening > 100)
|
break;
|
|
cell = row_temp.GetCell(col_index_k);
|
if (cell == null)
|
break;
|
if (!ParseCellValue(cell, out double k))
|
break;
|
if (k < 0)
|
break;
|
|
pt_list.Add(new Geometry.Point2d(opening, k));
|
}
|
|
if (pt_list.Any())
|
list.Add((Yw.Ahart.eCurveType.OL, pt_list.ToList(), 100));
|
}
|
|
if (list == null || !list.Any())
|
{
|
return ("表格不符合格式");
|
}
|
|
return "";
|
|
|
}
|
catch (Exception ex)
|
{
|
return "文件异常!";
|
}
|
}
|
|
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;
|
}
|
|
#endregion
|
|
}
|
}
|
