override public IEnumerable <XlRow> ReadXlsx() { // Get the file we are going to process var existingFile = new FileInfo(filePath); // Open and read the XlSX file. using (var package = new ExcelPackage(existingFile)) { // Get the work book in the file var workBook = package.Workbook; if (workBook != null) { if (workBook.Worksheets.Count > 0) { // Get the 'sheetName' worksheet var currentWorksheet = workBook.Worksheets[sheetName]; if (currentWorksheet != null) { // read data from file var start = currentWorksheet.Dimension.Start; var end = currentWorksheet.Dimension.End; for (int i = start.Row + 5; i <= end.Row; i++) { var rw = new XlRow(); rw.Dt = (DateTime)(currentWorksheet.Cells[3, 2].Value); rw.Acc = (currentWorksheet.Cells[i, 4].Value == null ? string.Empty : currentWorksheet.Cells[i, 4].Value.ToString()); rw.OstRub = Helpers.ParseToDouble(currentWorksheet.Cells[i, 5].Value == null ? "0.00" : currentWorksheet.Cells[i, 5].Value.ToString()); rw.ClientName = (currentWorksheet.Cells[i, 2].Value == null ? string.Empty : currentWorksheet.Cells[i, 2].Value.ToString().Replace("''", "\"")); rw.Inn = (currentWorksheet.Cells[i, 1].Value == null ? string.Empty : currentWorksheet.Cells[i, 1].Value.ToString().Trim()); rw.DealNumber = (currentWorksheet.Cells[i, 3].Value == null ? string.Empty : currentWorksheet.Cells[i, 3].Value.ToString()); rw.Cq = int.Parse(currentWorksheet.Cells[i, 6].Value == null ? "0" : currentWorksheet.Cells[i, 6].Value.ToString()); rw.Norm = Helpers.ParseToFloat(currentWorksheet.Cells[i, 7].Value == null ? "-0.01" : currentWorksheet.Cells[i, 7].Value.ToString()); rw.Reserv = Helpers.ParseToDouble(currentWorksheet.Cells[i, 8].Value == null ? "0.00" : currentWorksheet.Cells[i, 8].Value.ToString()); if (currentWorksheet.Cells[1, 1].Value.ToString() == "Ссудная задолженность СЗРЦ и филиалов СЗРЦ") { rw.Type = "РВПС"; rw.Ocr = "СЗРЦ_" + (currentWorksheet.Cells[i, 9].Value == null ? string.Empty : currentWorksheet.Cells[i, 9].Value.ToString()); rw.ResAcc = ""; } else { rw.Type = "РВП"; rw.ResAcc = (currentWorksheet.Cells[i, 9].Value == null ? string.Empty : currentWorksheet.Cells[i, 9].Value.ToString()); rw.Ocr = "СЗРЦ_" + (currentWorksheet.Cells[i, 10].Value == null ? string.Empty : currentWorksheet.Cells[i, 10].Value.ToString()); } yield return(rw); } } else { throw new Exception(string.Format("В файле СЗРЦ Нет листа '{0}'", sheetName)); } } } } }
public override IEnumerable <XlRow> ReadXlsx() { // Get the file we are going to process var existingFile = new FileInfo(filePath); // Open and read the XlSX file. using (var package = new ExcelPackage(existingFile)) { // Get the work book in the file var workBook = package.Workbook; if (workBook != null) { if (workBook.Worksheets.Count > 0) { // Get the 'sheetName' worksheet var currentWorksheet = workBook.Worksheets[sheetName]; if (currentWorksheet != null) { // read data from file var start = currentWorksheet.Dimension.Start; var end = currentWorksheet.Dimension.End; for (int i = start.Row + 5; i <= end.Row; i++) { var rw = new XlRow(); rw.Dt = (DateTime)(currentWorksheet.Cells[3, 3].Value); rw.Type = sheetName; rw.Acc = (currentWorksheet.Cells[i, 2].Value == null ? string.Empty : currentWorksheet.Cells[i, 2].Value.ToString().Trim()); rw.OstRub = Helpers.ParseToDoubleEx((currentWorksheet.Cells[i, 3].Value == null ? "0.00" : currentWorksheet.Cells[i, 3].Value.ToString().Trim()), sheetName, i); rw.ClientName = (currentWorksheet.Cells[i, 4].Value == null ? string.Empty : currentWorksheet.Cells[i, 4].Value.ToString().Trim()); rw.Inn = (currentWorksheet.Cells[i, 5].Value == null ? string.Empty : currentWorksheet.Cells[i, 5].Value.ToString().Trim()); rw.DealNumber = (currentWorksheet.Cells[i, 6].Value == null ? string.Empty : currentWorksheet.Cells[i, 6].Value.ToString().Trim()); rw.Cq = Helpers.CqConverter(currentWorksheet.Cells[i, 7].Value == null ? "0" : currentWorksheet.Cells[i, 7].Value.ToString().Trim()); rw.Norm = Helpers.ParseToFloat(currentWorksheet.Cells[i, 8].Value == null ? "-0.01" : currentWorksheet.Cells[i, 8].Value.ToString().Trim()); rw.ResAcc = (currentWorksheet.Cells[i, 9].Value == null ? string.Empty : currentWorksheet.Cells[i, 9].Value.ToString().Trim()); rw.Reserv = Helpers.ParseToDoubleEx((currentWorksheet.Cells[i, 10].Value == null ? "0.00" : currentWorksheet.Cells[i, 10].Value.ToString().Trim()), sheetName, i); rw.Ob1 = Helpers.ParseToDoubleEx((currentWorksheet.Cells[i, 11].Value == null ? "0.00" : currentWorksheet.Cells[i, 11].Value.ToString().Trim()), sheetName, i); rw.Ob2 = Helpers.ParseToDoubleEx((currentWorksheet.Cells[i, 12].Value == null ? "0.00" : currentWorksheet.Cells[i, 12].Value.ToString().Trim()), sheetName, i); rw.Ocr = (currentWorksheet.Cells[1, 5].Value == null ? "Ошибка в файле" : currentWorksheet.Cells[1, 5].Value.ToString().Trim()); yield return(rw); } } else { throw new Exception(string.Format("В файле КПФ Нет листа '{0}'", sheetName)); } } } } }
override public IEnumerable <XlRow> ReadXlsx() { // Get the file we are going to process var existingFile = new FileInfo(filePath); // Open and read the XlSX file. using (var package = new ExcelPackage(existingFile)) { // Get the work book in the file var workBook = package.Workbook; if (workBook != null) { if (workBook.Worksheets.Count > 0) { // Get the 'sheetName' worksheet var currentWorksheet = workBook.Worksheets[sheetName]; if (currentWorksheet != null) { // read data from file var start = currentWorksheet.Dimension.Start; var end = currentWorksheet.Dimension.End; for (int i = start.Row + 2; i <= end.Row; i++) { var rw = new XlRow(); rw.Dt = (DateTime)(currentWorksheet.Cells[i, 1].Value); rw.Type = (currentWorksheet.Cells[i, 8].Value.ToString().Substring(0, 1) == "9" ? "РВП" : "РВПС"); rw.Acc = (currentWorksheet.Cells[i, 8].Value == null ? string.Empty : currentWorksheet.Cells[i, 8].Value.ToString()); rw.OstRub = (currentWorksheet.Cells[i, 12].Value == null ? 0.00 : (double)currentWorksheet.Cells[i, 12].Value); rw.ClientName = (currentWorksheet.Cells[i, 30].Value == null ? string.Empty : currentWorksheet.Cells[i, 30].Value.ToString()); rw.Inn = (currentWorksheet.Cells[i, 33].Value == null ? string.Empty : currentWorksheet.Cells[i, 33].Value.ToString().Trim()); rw.DealNumber = (currentWorksheet.Cells[i, 17].Value == null ? string.Empty : currentWorksheet.Cells[i, 17].Value.ToString()); rw.Cq = int.Parse(currentWorksheet.Cells[i, 49].Value == null ? "0" : currentWorksheet.Cells[i, 49].Value.ToString()); rw.Norm = Helpers.ParseToFloat(currentWorksheet.Cells[i, 47].Value == null ? "-0.01" : currentWorksheet.Cells[i, 47].Value.ToString()); rw.Reserv = (currentWorksheet.Cells[i, 48].Value == null ? 0.00 : (double)currentWorksheet.Cells[i, 48].Value); rw.Ocr = "Mbank_" + (currentWorksheet.Cells[i, 3].Value == null ? string.Empty : currentWorksheet.Cells[i, 3].Value.ToString()); rw.Pos = (currentWorksheet.Cells[i, 45].Value == null ? string.Empty : currentWorksheet.Cells[i, 45].Value.ToString()); yield return(rw); } } else { throw new Exception(string.Format("В файле МБанка Нет листа '{0}'", sheetName)); } } } } }