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 TranslationExcelHelper
|
{
|
public static string ParseTranslationExcel(string fileName, out List<HStation.Vmo.AssetsTranslationMainVmo> list)
|
{
|
list = new List<Vmo.AssetsTranslationMainVmo>();
|
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_type = 1;
|
//材料列
|
int col_index_materials = 2;
|
//系数列
|
int col_index_coefficient = 3;
|
//上游直径列
|
int col_index_start_diameter = 4;
|
|
//下游直径列
|
int col_index_end_diameter = 5;
|
|
//备注列
|
int col_index_descriptions = 6;
|
|
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;
|
var type = row_temp.GetCell(col_index_type);
|
eTranslationType? eTranslationType = null;
|
switch (type.StringCellValue)
|
{
|
case "渐缩变径":
|
eTranslationType = HStation.Assets.eTranslationType.GradualContraction;
|
break;
|
|
case "偏心渐缩":
|
eTranslationType = HStation.Assets.eTranslationType.EccentricContraction;
|
break;
|
|
case "偏心渐扩":
|
eTranslationType = HStation.Assets.eTranslationType.EccentricExpansion;
|
break;
|
|
case "渐扩变径":
|
eTranslationType = HStation.Assets.eTranslationType.GradualExpansion;
|
break;
|
}
|
|
var material = row_temp.GetCell(col_index_materials);
|
if (material == null)
|
break;
|
cell = row_temp.GetCell(col_index_coefficient);
|
if (cell == null)
|
break;
|
if (!ParseCellValue(cell, out double coefficient))
|
break;
|
if (coefficient < 0)
|
break;
|
cell = row_temp.GetCell(col_index_start_diameter);
|
if (cell == null)
|
break;
|
if (!ParseCellValue(cell, out double start_diameter))
|
break;
|
if (start_diameter < 0)
|
break;
|
cell = row_temp.GetCell(col_index_end_diameter);
|
if (cell == null)
|
break;
|
if (!ParseCellValue(cell, out double end_diameter))
|
break;
|
if (end_diameter < 0)
|
break;
|
list.Add(new Vmo.AssetsTranslationMainVmo
|
{
|
Name = name.StringCellValue,
|
TranslationType = eTranslationType,
|
Material = material.StringCellValue,
|
MinorLoss = coefficient,
|
StartDiameter = start_diameter,
|
EndDiameter = end_diameter,
|
Roughness = 120,
|
Description = row_temp.GetCell(col_index_descriptions).StringCellValue,//?.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;
|
}
|
|
private static bool ParseCellValue(NPOI.SS.UserModel.ICell cell, out int cell_value)
|
{
|
cell_value = 0;
|
if (cell.CellType == NPOI.SS.UserModel.CellType.Numeric)
|
{
|
cell_value = (int)cell.NumericCellValue;
|
return true;
|
}
|
|
if (cell.CellType == NPOI.SS.UserModel.CellType.String)
|
{
|
if (int.TryParse(cell.StringCellValue, out int value))
|
{
|
cell_value = value;
|
return true;
|
}
|
}
|
|
return false;
|
}
|
|
public static void ExportTranslationTemplate(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("类型");
|
rowTile1.CreateCell(2).SetCellValue("材料");
|
rowTile1.CreateCell(3).SetCellValue("局阻系数");
|
rowTile1.CreateCell(4).SetCellValue("上游直径");
|
rowTile1.CreateCell(5).SetCellValue("下游直径");
|
rowTile1.CreateCell(6).SetCellValue("备注");
|
theSheet1.ForceFormulaRecalculation = true;
|
|
using FileStream fs = File.OpenWrite(file_path);
|
theBook.Write(fs);
|
}
|
}
|
}
|
