/// <summary>
/// 导入Excel到库A，并返回原油的oilInfoID
/// </summary>
/// <param name="path">excel的路径</param>
/// <returns></returns>
public static OilInfoEntity importExcel(string fileName)
{
OilInfoEntity oilA = null;
try
{
DataSet ds = ExcelTool.ExcelToDataSet(fileName);
#region "原油信息表的处理"
List <string> oilInfoItemCode = OilTableRowBll._OilTableRow.Where(d => d.oilTableTypeID == (int)EnumTableType.Info).Select(d => d.itemCode).Distinct().ToList();
oilA = findOilInfoTalbe(ds, oilInfoItemCode); //首先要找到原油信息表，没有原油信息表 则没必要继续下去
if (oilA.ID < 0)
{
return(oilA);
}
#endregion
List <string> allItemCode = OilTableRowBll._OilTableRow.Where(d => new int[] { (int)EnumTableType.Whole, (int)EnumTableType.Light, (int)EnumTableType.Narrow, (int)EnumTableType.Wide, (int)EnumTableType.Residue }.Contains(d.oilTableTypeID)).Select(d => d.itemCode).Distinct().ToList();
List <WCol> tableColList = new List <WCol>(); //记录所有窄馏分、宽馏分、渣油 数据列集合
foreach (DataTable table in ds.Tables) //找到原油信息表后，遍历每个表
{
tableColList.AddRange(getColListFromTable(table, allItemCode));
}
#region "对宽馏分数据处理,如果出现ICP，ECP相同的重复列则合并。"
var items = tableColList.Where(d => d.TableType == EnumTableType.Wide).GroupBy(x => new { x.ICP, x.ECP }).Select(d => new { keys = d.Key, Count = d.Count() });
foreach (var i in items)
{
if (i.Count > 1)
{
List <WCol> mergeList = tableColList.Where(d => d.TableType == EnumTableType.Wide && d.ICP == i.keys.ICP && d.ECP == i.keys.ECP).ToList();
WCol newCol = new WCol();
newCol.ECP = i.keys.ECP;
newCol.ICP = i.keys.ICP;
newCol.TableType = EnumTableType.Wide;
foreach (var mergeCol in mergeList)
{
newCol.Cells.AddRange(mergeCol.Cells);
tableColList.Remove(mergeCol);
}
tableColList.Add(newCol);
}
}
#endregion
//排序，找出宽馏分的，根据iep排序，渣油也排序
List <WCol> wideData = tableColList.Where(d => d.TableType == EnumTableType.Wide).OrderBy(d => d.MCP).ToList();
WriteToOilA(ref oilA, wideData, EnumTableType.Wide);
wideData = tableColList.Where(d => d.TableType == EnumTableType.Whole).ToList();
WriteToOilA(ref oilA, wideData, EnumTableType.Whole);
wideData = tableColList.Where(d => d.TableType == EnumTableType.Narrow).ToList();
WriteToOilA(ref oilA, wideData, EnumTableType.Narrow);
wideData = tableColList.Where(d => d.TableType == EnumTableType.Residue).OrderBy(d => d.MCP).ToList();
WriteToOilA(ref oilA, wideData, EnumTableType.Residue);
wideData = tableColList.Where(d => d.TableType == EnumTableType.Light).ToList();
WriteToOilA(ref oilA, wideData, EnumTableType.Light);
}
catch (Exception ex)
{
Log.Error("数据管理,镇海导入Excel到库原始库:" + ex);
return(null);
}
return(oilA);
}
/// <summary>
/// 取出一个表中的数据列集合
/// </summary>
/// <param name="table"></param>
/// <param name="firstCode"></param>
/// <param name="itemCodeList"></param>
/// <returns></returns>
private static List <WCol> getColListFromTable(DataTable table, List <string> itemCodeList)
{
List <WCol> dataColList = new List <WCol>();
int[] firstCodeLocation = findFirstItemCodeLocation(table, itemCodeList);//存放第一个代码的行和列，找出存放代码的列和行
if (firstCodeLocation[0] == -1 || firstCodeLocation[1] == -1)
{
return(dataColList);//此表格没有ItemCode，继续下一个表格
}
EnumTableType tableType = EnumTableType.None;
Dictionary <string, string[]> codeUnitConvert = new Dictionary <string, string[]>();
//记录IEP的行
int IEPRow = -1;
int ICPRow = -1;
int ECPRow = -1;
#region "表内数据的行循环,找出由于单位不同需要转换的数据行"
for (int row = firstCodeLocation[0]; row < table.Rows.Count; row++)
{
string rowContent = table.Rows[row][firstCodeLocation[1]].ToString().Trim();
rowContent = Units.ToDBC(rowContent);
if (rowContent.Equals("G00"))
{
tableType = EnumTableType.Light;
}
while (itemCodeList.Count < row)//补充前面空行，使itemcodelist顺序与行号一致
{
itemCodeList.Add("");
}
//记录下此表的行的itemcode顺序
itemCodeList.Add(rowContent);
//记录下温度行
if (rowContent.Contains("IEP"))
{
IEPRow = row;
}
if (rowContent.Contains("ICP"))
{
ICPRow = row;
}
if (rowContent.Contains("ECP"))
{
ECPRow = row;
}
if (tableType != EnumTableType.Light)
{
#region 单位转换
//先读取行前面的 单位，确定哪一行如何单位转换
int colIndex = firstCodeLocation[1];//取出列位置
#region 获取代码列前面的值
string frontContent = "";
if (colIndex >= 1)
{
frontContent = table.Rows[row][colIndex - 1].ToString().Trim();
if (colIndex >= 2)
{
frontContent += table.Rows[row][colIndex - 2].ToString().Trim();
}
if (colIndex >= 3)
{
frontContent += table.Rows[row][colIndex - 3].ToString().Trim();
}
//D20单位转换要先记录下所有需要转换的行，等wcol填满后，再计算需要转换的行
}
else
{
//throw
}
frontContent = Units.ToDBC(frontContent);
#endregion
string matchCode = "";
foreach (var item in Units.UnitList)
{
if (frontContent.Contains(item))
{
if (item.Length > matchCode.Length) //有可能多个匹配、部分匹配，记录下最长的
{
matchCode = item;
}
}
}
//包含可能转换的单位，matchCode是excel中的单位,为空则表示无单位，去查看这个代码在程序中的单位
//除去IEP
string tUnit = "";
if (!rowContent.Contains("IEP"))
{
try
{
var rowEntity = OilTableRowBll._OilTableRow.Where(d => new int[] { 2, 6, 7, 8 }.Contains(d.oilTableTypeID) && d.itemCode == rowContent).FirstOrDefault();
if (rowEntity != null)
{
tUnit = rowEntity.itemUnit;
}
}
catch
{
MessageBox.Show("未找到itemunit" + rowContent);
}
}
//tUnit = _rowCache[rowContent, EnumTableType.Wide].itemUnit;
if (!string.IsNullOrWhiteSpace(matchCode) && !string.IsNullOrWhiteSpace(tUnit) && !tUnit.Equals(matchCode))
{
codeUnitConvert.Add(row.ToString(), new string[] { matchCode, tUnit });
}
#endregion
}
}
#endregion
#region "表内数据的列循环"
for (int col = firstCodeLocation[1] + 1; col < table.Columns.Count; col++)
{
WCol colData = new WCol();//获取列数据
if (tableType != EnumTableType.Light)
{
#region 读取IEP行
if (IEPRow != -1)
{
string colContent = table.Rows[IEPRow][col].ToString().Trim();
colContent = Units.ToDBC(colContent);
if (colContent.Contains("原油"))
{
colData.TableType = EnumTableType.Whole;
}
else
{
List <int> IEPNum = GetIEPNum(colContent);
#region ""
//有数，个数小于两个
if (IEPNum.Count <= 2 && IEPNum.Count > 0)
{
if (IEPNum.Count == 2)
{
if (IEPNum[0] < IEPNum[1])
{
//这一行是
if (tableType != EnumTableType.Narrow)
{
colData.TableType = EnumTableType.Wide;
}
else
{
colData.TableType = EnumTableType.Narrow;
}
colData.ICP = IEPNum[0];
colData.ECP = IEPNum[1];
}
else
{
continue;
}
}
else
{
//只得到一个值，有可能是
bool isICP = true;
if (colContent.Contains(">"))
{//这个值是ECP
isICP = false;
}
else if (colContent.Contains("<"))
{
isICP = true;
}
else if (colContent.Contains("~"))
{
int signIndex = colContent.IndexOf("~");
//--------------------------获得一个 为数字的值
Regex regex = new Regex(@"\d");
int numIndex = regex.Match(colContent).Index;
if (signIndex < numIndex)
{
isICP = false;
}
else
{
isICP = true;
}
}
else if (colContent.Contains("-"))
{
int signIndex = colContent.IndexOf("-");
//--------------------------获得一个 为数字的值
Regex regex = new Regex(@"\d");
int numIndex = regex.Match(colContent).Index;
if (signIndex < numIndex)
{
isICP = false;
}
else
{
isICP = true;
}
}
if (tableType != EnumTableType.Narrow)
{
if (colContent.Contains("+") || colContent.Contains(">"))
{
//渣油
colData.TableType = EnumTableType.Residue;
colData.ICP = IEPNum[0];
}
else
{
colData.TableType = EnumTableType.Wide;
if (isICP == true)
{
colData.ICP = IEPNum[0];
}
else
{
colData.ECP = IEPNum[0];
}
}
}
else
{
colData.TableType = EnumTableType.Narrow;
if (isICP == true)
{
colData.ICP = IEPNum[0];
}
else
{
colData.ECP = IEPNum[0];
}
}
}
}
else
{
continue;
}
#endregion
}
}
#endregion
#region 查ICP 和 ECP 的数据
if (ICPRow != -1 && colData.TableType == EnumTableType.None)
{
string ICPContent = table.Rows[ICPRow][col].ToString().Trim();
string ECPContent = "";
if (ECPRow != -1)
{
ECPContent = table.Rows[ECPRow][col].ToString().Trim();
}
ICPContent = Units.ToDBC(ICPContent);
ECPContent = Units.ToDBC(ECPContent);
if (IsInt(ICPContent) && IsInt(ECPContent))
{
if (int.Parse(ICPContent) < int.Parse(ECPContent))
{
if (tableType != EnumTableType.Narrow)
{
colData.TableType = EnumTableType.Wide;
}
else
{
colData.TableType = EnumTableType.Narrow;
}
colData.ICP = int.Parse(ICPContent);
colData.ECP = int.Parse(ECPContent);
}
else
{
continue;
}
}
else if (IsInt(ICPContent))
{
if (tableType != EnumTableType.Narrow)//渣油
{
colData.TableType = EnumTableType.Residue;
}
else
{
colData.TableType = EnumTableType.Narrow;
}
colData.ICP = int.Parse(ICPContent);
}
else if (IsInt(ECPContent))
{
if (tableType != EnumTableType.Narrow)
{
colData.TableType = EnumTableType.Wide;
}
else
{
colData.TableType = EnumTableType.Narrow;
}
colData.ECP = int.Parse(ECPContent);
}
else
{
continue;
}
}
#endregion
}
else
{
colData.TableType = EnumTableType.Light;
}
List <string> lightItemtCode = new List <string>(); //记录轻端itemcode
for (int row = firstCodeLocation[0]; row < table.Rows.Count; row++) //for(int row = ) 一列一列循环单元格，放入List<wcol>中
{
if (row == IEPRow || row == ECPRow || row == ICPRow)
{
continue;
}
string content = table.Rows[row][col].ToString().Trim();
content = Units.ToDBC(content);
if (tableType == EnumTableType.Light)
{
if (!string.IsNullOrWhiteSpace(itemCodeList[row]))
{
if (lightItemtCode.Contains(itemCodeList[row]))
{
if (colData.Cells.Count() != 0)
{
dataColList.Add(colData);
}
lightItemtCode.Clear();
lightItemtCode.Add(itemCodeList[row]);
colData = new WCol();
colData.TableType = EnumTableType.Light;
}
else
{
lightItemtCode.Add(itemCodeList[row]);
}
}
}
if (string.IsNullOrWhiteSpace(content) || string.IsNullOrWhiteSpace(itemCodeList[row]))
{
continue;
}
WCell cell = new WCell();
cell.ItemCode = itemCodeList[row];//itemcodelist中存有空值，根据content不为空，则存入coldata中的都不为空
//需要单位转换的
if (codeUnitConvert.ContainsKey(row.ToString()))
{
string[] tempstr = codeUnitConvert[row.ToString()];
double dContent = double.Parse(content);
Units.UnitConvert(ref dContent, tempstr[0], tempstr[1]);
content = dContent.ToString();
}
cell.LabData = content;
colData.Add(cell);
}
if (colData.Cells.Count() != 0)
{
dataColList.Add(colData);
}
}
#endregion
return(dataColList);
}
