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); } } } }