private List <FMELWEntity> ReadFile(string filePath, List <FMELWEntity> listFMELW)
{
List <FMELWEntity> list = new List <FMELWEntity>();
if (!File.Exists(filePath))
{
string msg = string.Format("the file [{0}] is not exist.", filePath);
Logger.Log(msg, Logger.LogType.Error);
return(null);
}
try
{
using (ExcelApp eApp = new ExcelApp(false, false))
{
Workbook wBook = ExcelUtil.CreateOrOpenExcelFile(eApp, filePath);
Worksheet wSheet = wBook.Worksheets[1] as Worksheet;
if (wSheet == null)
{
string msg = "Worksheet could not be created. Check that your office installation and project reference are correct!";
Logger.Log(msg, Logger.LogType.Error);
return(null);
}
//wSheet.Name = "FM";
int lastUsedRow = wSheet.UsedRange.Row + wSheet.UsedRange.Rows.Count - 1;//193=1+193-1
using (ExcelLineWriter reader = new ExcelLineWriter(wSheet, 2, 2, ExcelLineWriter.Direction.Right))
{
while (reader.Row <= lastUsedRow)
{
FMELWEntity fm = new FMELWEntity();
fm.ISIN = reader.ReadLineCellText();//web.StandardCongenial
reader.PlaceNext(reader.Row, 4);
fm.IssuingAuthority = reader.ReadLineValue2();
reader.PlaceNext(reader.Row + 1, 2);
if (!string.IsNullOrEmpty(fm.ISIN.Trim()) && !string.IsNullOrEmpty(fm.IssuingAuthority.Trim()))
{
list.Add(fm);
}
}
}
}
return(list);
}
catch (Exception ex)
{
string msg = string.Format("read xls file error :{0}", ex.ToString());
Logger.Log(msg);
return(null);
}
}
private void FillInListTemplate(List <IssueAssetAddTemplate> listIAATemplate)
{
using (ExcelApp app = new ExcelApp(false, false))
{
var workbook = ExcelUtil.CreateOrOpenExcelFile(app, hkQAAddFileName);
Worksheet worksheet = workbook.Worksheets[1] as Worksheet;
int lastUsedRow = worksheet.UsedRange.Row + worksheet.UsedRange.Rows.Count - 1;
using (ExcelLineWriter reader = new ExcelLineWriter(worksheet, 2, 1, ExcelLineWriter.Direction.Right))
{
string ric = string.Empty;
string warrantIssueQuantity = string.Empty; //18
string tranche = string.Empty; //17
string trancheListingAate = string.Empty; //16
for (int i = 1; i <= lastUsedRow; i++)
{
ric = reader.ReadLineCellText();
if (ric == null || ric.Trim() == "")
{
continue;
}
foreach (var item in listIAATemplate)
{
if (!(item.HongKongCode.Trim() + ".HK").Equals(ric.Trim()))
{
continue;
}
reader.PlaceNext(reader.Row, 16);
trancheListingAate = DateTime.Parse(reader.ReadLineCellText()).ToString("dd-MMM-yyyy");
reader.PlaceNext(reader.Row, 17);
tranche = reader.ReadLineCellText();
reader.PlaceNext(reader.Row, 18);
warrantIssueQuantity = reader.ReadLineValue2();
if (trancheListingAate == null || tranche == null || warrantIssueQuantity == null)
{
string msg = String.Format("value (row,clo)=({0},{1}) is null!", reader.Row, reader.Col);
Logger.Log(msg, Logger.LogType.Error);
MessageBox.Show(msg);
continue;
}
item.TrancheListingDate = trancheListingAate;
item.TranchePrice = tranche;
item.WarrantIssueQuantity = warrantIssueQuantity;
}
reader.PlaceNext(reader.Row + 1, 1);
}
}
}
}
