using System.IO;
|
|
namespace Yw.WinFrmUI
|
{
|
public class PhartExcelHelper
|
{
|
#region Export
|
|
public static void ExportUniversalTemplate(string file_path, Yw.Ahart.eCurveType curve_type)
|
{
|
var title = AxisTitleHelper.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);
|
|
}
|
|
#endregion
|
|
#region Parse
|
|
public static string ParseUniversalExcel(string file_path, out List<Yw.Geometry.Point2d> list)
|
{
|
list = new List<Geometry.Point2d>(); ;
|
if (!File.Exists(file_path))
|
return "文件不存在";
|
|
//初始化文件
|
NPOI.HSSF.UserModel.HSSFWorkbook theBook = null;
|
using (FileStream file = new FileStream(file_path, FileMode.Open, FileAccess.Read))
|
{
|
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));
|
}
|
|
return "";
|
}
|
|
public static List<(double Q, double H, double P, double E)> ParsePumpExcel(string fileName)
|
{
|
if (!File.Exists(fileName))
|
return default;
|
int line = 0;
|
|
//初始化文件
|
NPOI.HSSF.UserModel.HSSFWorkbook theBook = null;
|
using (FileStream file = new FileStream(fileName, FileMode.Open, FileAccess.Read))
|
{
|
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_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)
|
{
|
throw new Exception("第一行第一列不能空");
|
}
|
|
//开始读取的行
|
int start_line = title_line_index + 1;
|
var cell_0 = row_title.GetCell(0);
|
if (cell_0 == null)
|
{
|
throw new Exception("无法读取表头文件");
|
}
|
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%");
|
}
|
|
list.Add(new(flow, head, eff, power));
|
|
}
|
|
return list;
|
}
|
|
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
|
}
|
}
|
