using DevExpress.CodeParser;
|
using HStation.Assets;
|
using NPOI.HSSF.UserModel;
|
using NPOI.SS.UserModel;
|
using NPOI.XSSF.UserModel;
|
using System.IO;
|
|
namespace HStation.WinFrmUI
|
{
|
public class PumpExcelHelper
|
{
|
public static string ParsePumpExcel(string fileName, out List<HStation.Vmo.AssetsPumpMainVmo> list)
|
{
|
list = new List<Vmo.AssetsPumpMainVmo>();
|
if (!File.Exists(fileName))
|
return "文件不存在";
|
int line = 0;
|
|
IWorkbook workbook;
|
using (FileStream file = new(fileName, FileMode.Open, FileAccess.Read))
|
{
|
if (Path.GetExtension(fileName).ToLower() == ".xls")
|
{
|
workbook = new HSSFWorkbook(file);
|
}
|
else if (Path.GetExtension(fileName).ToLower() == ".xlsx")
|
{
|
workbook = new XSSFWorkbook(file);
|
}
|
else
|
{
|
return "不支持的文件格式";
|
}
|
}
|
|
ISheet sheet_ql = workbook.GetSheet("sheet1");
|
if (sheet_ql == null)
|
{
|
return "表格不符合格式";
|
}
|
//标题行
|
int title_line_index = 0;
|
//名称列
|
int col_index_name = 0;
|
//流量列
|
int col_index_flow = 1;
|
//扬程列
|
int col_index_head = 2;
|
//效率列
|
int col_index_efficiency = 3;
|
//功率列
|
int col_index_power = 4;
|
//转速列
|
int col_index_speed = 5;
|
//叶轮外径列
|
int col_index_d2 = 6;
|
//气蚀列
|
int col_index_erosion = 7;
|
//说明列
|
int col_index_descriptions = 8;
|
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;
|
|
for (line = start_line; line < 1000; line++)
|
{
|
row_temp = sheet_ql.GetRow(line);
|
if (row_temp == null)
|
break;
|
var name = row_temp.GetCell(col_index_name);
|
if (name == null)
|
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);
|
if (cell == null)
|
break;
|
if (!ParseCellValue(cell, out double? head))
|
break;
|
if (head < 0)
|
break;
|
|
cell = row_temp.GetCell(col_index_efficiency);
|
if (cell == null)
|
break;
|
if (!ParseCellValue(cell, out double? efficiency))
|
break;
|
if (efficiency < 0)
|
break;
|
|
cell = row_temp.GetCell(col_index_power);
|
if (cell == null)
|
break;
|
if (!ParseCellValue(cell, out double? power))
|
break;
|
if (power < 0)
|
break;
|
|
cell = row_temp.GetCell(col_index_speed);
|
if (cell == null)
|
break;
|
if (!ParseCellValue(cell, out double? speed))
|
break;
|
if (speed < 0)
|
break;
|
|
cell = row_temp.GetCell(col_index_d2);
|
if (cell == null)
|
break;
|
if (!ParseCellValue(cell, out double? d2))
|
break;
|
if (d2 < 0)
|
break;
|
|
/* cell = row_temp.GetCell(col_index_erosion);
|
if (cell == null)
|
break;
|
if (!ParseCellValue(cell, out double? erosion))
|
break;
|
if (erosion < 0)
|
break;*/
|
|
list.Add(new Vmo.AssetsPumpMainVmo
|
{
|
Name = name.StringCellValue,
|
RatedFlow = flow ?? 0, // 如果 flow 为 null，则使用默认值 0
|
RatedHead = head ?? 0, // 如果 head 为 null，则使用默认值 0
|
RatedEfficiency = efficiency,
|
RatedPower = power ?? 0, // 如果 power 为 null，则使用默认值 0
|
RatedSpeed = speed ?? 0, // 如果 speed 为 null，则使用默认值 0
|
D2 = d2,
|
// Erosion = erosion,
|
// Description = row_temp.GetCell(col_index_descriptions)?.StringCellValue,
|
});
|
}
|
|
if (list == null || !list.Any())
|
{
|
return ("表格不符合格式");
|
}
|
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;
|
}
|
|
//转换可以为空
|
private static bool ParseCellValue(NPOI.SS.UserModel.ICell cell, out double? cell_value)
|
{
|
cell_value = null;
|
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;
|
}
|
else if (cell.StringCellValue == null)
|
{
|
return true;
|
}
|
}
|
if (cell.CellType == NPOI.SS.UserModel.CellType.Blank)
|
{
|
return true;
|
}
|
|
return false;
|
}
|
|
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 rowTile1 = theSheet1.CreateRow(0);
|
rowTile1.CreateCell(0).SetCellValue("型号");
|
rowTile1.CreateCell(1).SetCellValue("额定流量(m³/h)");
|
rowTile1.CreateCell(2).SetCellValue("扬程(m)");
|
rowTile1.CreateCell(3).SetCellValue("额定效率");
|
rowTile1.CreateCell(4).SetCellValue("额定功率");
|
rowTile1.CreateCell(5).SetCellValue("额定转速");
|
rowTile1.CreateCell(6).SetCellValue("叶轮外径");
|
rowTile1.CreateCell(7).SetCellValue("气蚀");
|
rowTile1.CreateCell(8).SetCellValue("说明");
|
theSheet1.ForceFormulaRecalculation = true;
|
using FileStream fs = File.OpenWrite(file_path);
|
theBook.Write(fs);
|
}
|
}
|
}
|
