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