using NPOI.HSSF.UserModel;
|
using NPOI.SS.UserModel;
|
using System;
|
using System.Collections.Generic;
|
using System.IO;
|
|
namespace AStation.WinFrmUI.Chart
|
{
|
/// <summary>
|
/// ExcelFileHelper
|
/// </summary>
|
public class CurveExpressFileHelper
|
{
|
public static List<AStation.Curve.GroupPoint> ImportByExcel(string fileName, out string msg)
|
{
|
msg = string.Empty;
|
if (!File.Exists(fileName))
|
{
|
msg = "文件不存在!";
|
return default;
|
}
|
|
var list = new List<AStation.Curve.GroupPoint>();
|
int line = 0;
|
try
|
{
|
//初始化文件
|
HSSFWorkbook theBook = null;
|
using (FileStream file = new FileStream(fileName, FileMode.Open, FileAccess.Read))
|
{
|
theBook = new HSSFWorkbook(file);
|
}
|
|
//检查表格是否符合
|
NPOI.SS.UserModel.ISheet sheet1 = theBook.GetSheet("Sheet1");
|
if (sheet1 == null)
|
{
|
sheet1 = theBook.GetSheetAt(0);
|
if (sheet1 == null)
|
{
|
msg = "请将数据放在Sheet1中!";
|
return default;
|
}
|
}
|
|
//标题行
|
int titleLineIndex = 0;
|
//流量列
|
int colIndexQ = 1;
|
//扬程列
|
int colIndexH = 2;
|
//效率列
|
int colIndexE = 3;
|
//功率列
|
int colIndexP = 4;
|
|
var rowTitle = sheet1.GetRow(titleLineIndex);
|
if (rowTitle == null)
|
{
|
msg = "第一行第一列不能空!";
|
return default;
|
}
|
|
//开始读取的行
|
int startLine = titleLineIndex + 1;
|
var cell_0 = rowTitle.GetCell(0);
|
if (cell_0 == null)
|
{
|
msg = "无法读取表头文件";
|
return default;
|
}
|
else if (cell_0.StringCellValue.Contains("序号"))
|
{
|
colIndexQ = 1;
|
colIndexH = 2;
|
colIndexE = 3;
|
colIndexP = 4;
|
}
|
else if (cell_0.StringCellValue.Contains("流量"))
|
{
|
colIndexQ = 0;
|
colIndexH = 1;
|
colIndexE = 2;
|
colIndexP = 3;
|
}
|
|
double q, h, eta, power;
|
NPOI.SS.UserModel.IRow rowTemp = null;
|
NPOI.SS.UserModel.ICell cell;
|
|
var index = 1;
|
for (line = startLine; line < 1000; line++)
|
{
|
q = h = eta = power = -1;
|
rowTemp = sheet1.GetRow(line);
|
if (rowTemp == null)
|
break;
|
|
cell = rowTemp.GetCell(colIndexQ);
|
if (cell == null)
|
break;
|
if (cell.CellType == NPOI.SS.UserModel.CellType.Numeric)
|
q = cell.NumericCellValue;
|
else if (cell.CellType == NPOI.SS.UserModel.CellType.String)
|
q = Convert.ToDouble(cell.StringCellValue);
|
else
|
continue;
|
|
if (q < 0)
|
break;
|
|
cell = rowTemp.GetCell(colIndexH);
|
if (cell == null)
|
break;
|
if (cell.CellType == NPOI.SS.UserModel.CellType.Numeric)
|
h = cell.NumericCellValue;
|
else if (cell.CellType == NPOI.SS.UserModel.CellType.String)
|
h = Convert.ToDouble(cell.StringCellValue);
|
else if (cell.CellType == NPOI.SS.UserModel.CellType.Blank)
|
break;
|
if (h < 0)
|
break;
|
|
|
cell = rowTemp.GetCell(colIndexE);
|
if (cell == null)
|
break;
|
if (cell.CellType == NPOI.SS.UserModel.CellType.Numeric)
|
eta = cell.NumericCellValue;
|
else if (cell.CellType == NPOI.SS.UserModel.CellType.String)
|
eta = Convert.ToDouble(cell.StringCellValue);
|
else
|
eta = -1;
|
|
cell = rowTemp.GetCell(colIndexP);
|
if (cell == null)
|
break;
|
if (cell.CellType == NPOI.SS.UserModel.CellType.Numeric)
|
power = cell.NumericCellValue;
|
else if (cell.CellType == NPOI.SS.UserModel.CellType.String)
|
power = Convert.ToDouble(cell.StringCellValue);
|
else
|
power = -1;
|
|
if (eta > 0)
|
{
|
power = AStation.Curve.PumpCalculateHelper.CalculateP(q, h, eta);
|
}
|
else
|
{
|
eta = AStation.Curve.PumpCalculateHelper.CalculateE(q, h, power);
|
}
|
|
if (eta > 99)
|
{
|
throw new Exception("效率大于100%");
|
}
|
|
list.Add(new AStation.Curve.GroupPoint(index++, q, h, eta, power));
|
}
|
|
if (list.Count < 4)
|
{
|
msg = "导入点过少,请手动添加点,点数至少4个点";
|
return default;
|
}
|
}
|
catch (Exception err)
|
{
|
msg = string.Format("读取Excel出错!错误原因:{0},行号: {1}", err.Message, line);
|
}
|
return list;
|
}
|
|
public static Model.PumpCurveInfo ImportByCurveExpress(string fileName, out string msg)
|
{
|
msg = string.Empty;
|
if (!File.Exists(fileName))
|
{
|
msg = "文件不存在!";
|
return default;
|
}
|
var strInfo = File.ReadAllText(fileName);
|
if (string.IsNullOrEmpty(strInfo))
|
{
|
msg = "无数据!";
|
return default;
|
}
|
|
var express = Model.PumpCurveInfo.ToModel(strInfo);
|
if (express == null)
|
{
|
msg = "解析失败!";
|
return default;
|
}
|
return express;
|
}
|
|
|
public static bool ExportCurveExpress(string fileName, Model.PumpCurveInfo featCurveExpressGroup, out string msg)
|
{
|
msg = string.Empty;
|
if (featCurveExpressGroup == null)
|
{
|
msg = "表达式为空!";
|
return default;
|
}
|
try
|
{
|
var str = featCurveExpressGroup.ToJson();
|
File.WriteAllText(fileName, str);
|
}
|
catch (Exception ex)
|
{
|
msg = ex.Message;
|
}
|
return true;
|
}
|
|
|
|
public static bool Export2Excel(string fileName, Model.PumpCurveInfo featCurveExpressGroup, out string msg, int fitPointNumQH = 12)
|
{
|
msg = string.Empty;
|
if (featCurveExpressGroup == null)
|
{
|
msg = "曲线表达式为空!";
|
return default;
|
}
|
return Export2Excel(fileName, featCurveExpressGroup.CurveQH, featCurveExpressGroup.CurveQE, featCurveExpressGroup.CurveQP, out msg, fitPointNumQH);
|
}
|
public static bool Export2Excel(string fileName, AStation.Curve.CurveExpress curveExpressQH, AStation.Curve.CurveExpress curveExpressQE, AStation.Curve.CurveExpress curveExpressQP, out string msg, int fitPointNumQH = 12)
|
{
|
msg = string.Empty;
|
if (curveExpressQH == null)
|
{
|
msg = "流量扬程曲线为空!";
|
return default;
|
}
|
if (!File.Exists(fileName))
|
{
|
File.Create(fileName);
|
}
|
|
try
|
{
|
HSSFWorkbook theBook = null;
|
using (FileStream file = new FileStream(fileName, FileMode.Open, FileAccess.Read))
|
{
|
theBook = new HSSFWorkbook(file);
|
if (theBook == null)
|
{
|
msg = "Excel初始化失败!";
|
return default;
|
}
|
var dsi = NPOI.HPSF.PropertySetFactory.CreateDocumentSummaryInformation();
|
dsi.Company = "EvenTech";
|
theBook.DocumentSummaryInformation = dsi;
|
var si = NPOI.HPSF.PropertySetFactory.CreateSummaryInformation();
|
si.Subject = "yiwei";
|
theBook.SummaryInformation = si;
|
}
|
|
ISheet theSheet1 = theBook.CreateSheet("Sheet1");
|
theSheet1.ForceFormulaRecalculation = true;
|
|
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("功率");
|
|
|
//均匀插值点,计算点坐标
|
double rQmin = curveExpressQH.Min;
|
double rQmax = curveExpressQH.Max;
|
double space = (rQmax - rQmin) / (fitPointNumQH - 1);
|
double Q = rQmin;
|
double H, E, P;
|
for (int i = 0; i < fitPointNumQH; i++)
|
{
|
H = AStation.Curve.FitHelper.GetFitPointY(curveExpressQH, Q);
|
P = AStation.Curve.FitHelper.GetFitPointY(curveExpressQP, Q);
|
var eta = AStation.Curve.FitHelper.GetFitPointY(curveExpressQE, Q);
|
|
int col = 0;
|
IRow rowtemp = theSheet1.CreateRow(i + 1);
|
rowtemp.CreateCell(col).SetCellValue(i + 1);//序号
|
col++;
|
rowtemp.CreateCell(col).SetCellValue(DoubleTrans(Q));
|
col++;
|
rowtemp.CreateCell(col).SetCellValue(DoubleTrans(H));
|
col++;
|
|
if (curveExpressQE != null && Q <= curveExpressQE.Max && Q >= curveExpressQE.Min)
|
{
|
if (Q < 0.1)
|
E = 0;
|
else
|
E = AStation.Curve.FitHelper.GetFitPointY(curveExpressQE, Q);
|
rowtemp.CreateCell(col).SetCellValue(DoubleTrans(E));
|
col++;
|
}
|
|
if (curveExpressQP != null && Q <= curveExpressQP.Max && Q >= curveExpressQP.Min)
|
{
|
P = AStation.Curve.FitHelper.GetFitPointY(curveExpressQP, Q);
|
rowtemp.CreateCell(col).SetCellValue(DoubleTrans(P));
|
col++;
|
}
|
|
Q = Q + space;
|
if (Q > curveExpressQH.Max * 0.9999)
|
Q = curveExpressQH.Max * 0.9999;
|
}
|
using (FileStream fs = File.OpenWrite(fileName))
|
{
|
theBook.Write(fs);
|
}
|
}
|
catch (Exception ex)
|
{
|
msg = ex.Message;
|
}
|
return true;
|
}
|
|
|
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");
|
}
|
|
}
|
}
|
