/// <summary>
/// 把Excel 資料轉換成 Exhibit7Model
/// </summary>
/// <param name="pathType">string</param>
/// <param name="stream">Stream</param>
/// <returns>Exhibit7Model</returns>
public List <Exhibit7Model> getExcel(string pathType, Stream stream)
{
DataSet resultData = new DataSet();
List <Exhibit7Model> dataModel = new List <Exhibit7Model>();
try
{
IExcelDataReader reader = null;
switch (pathType) //判斷型別
{
case "xls":
reader = ExcelReaderFactory.CreateBinaryReader(stream);
break;
case "xlsx":
reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
break;
}
reader.IsFirstRowAsColumnNames = true;
resultData = reader.AsDataSet();
reader.Close();
if (resultData.Tables[0].Rows.Count > 5) //判斷有無資料
{
string Data_Year = resultData.Tables[0].Rows[2][3].ToString();
dataModel = (from q in resultData.Tables[0].AsEnumerable()
.Skip(3).Take(9)
select getExhibit7Models(q, Data_Year)).ToList();
//skip(4) 為排除Excel 前4行(參數可調)
if (dataModel.Count() == 9)
{
//add Sr. Secured Bond && (Recovery_Rate & LGD = (1st Lien Bond + 2nd Lien Bond )/2)
dataModel.Add(
new Exhibit7Model()
{
Data_Year = Data_Year,
Lien_Position = "Sr. Secured Bond",
Recovery_Rate =
((TypeTransfer.stringToDouble(dataModel[3].Recovery_Rate)
+
TypeTransfer.stringToDouble(dataModel[4].Recovery_Rate)) / 2).ToString(),
LGD =
((TypeTransfer.stringToDouble(dataModel[3].LGD)
+
TypeTransfer.stringToDouble(dataModel[4].LGD)) / 2).ToString(),
});
}
}
}
catch
{ }
return(dataModel);
}