private void ParseRests(FileInfo fi)
{
if (fi == null)
{
return;
}
using (var eh = new ExcelHelper())
{
var r = new ExRange(eh, eh.OpenWorkbook(fi, "", false), 1, 1);
for (var row = 2; row < 10000 && !string.IsNullOrWhiteSpace(r.Move(row, 3).Value("")); row++)
{
try
{
var edrpou = r.Move(row, 2).Value("");
var sum = r.Move(row, 3).Value(0d);
var number = r.Move(row, 4).Value("");
var date = r.Move(row, 5).Value(DateTime.MinValue);
this.payManager_.AddPayinOrRest(edrpou, number, date, sum);
}
catch (Exception e)
{
AppLogAndEventHelper.Instance.RaiseError(e);
AppLogAndEventHelper.Instance.RaiseError($"Считывание {fi.Name} в {row} строке ошибка {e.Message}");
r.Range.Interior.Color = XlRgbColor.rgbLightPink;
}
}
}
}
private void ParsePayouts(FileInfo fi, DBConnectionData.ConnectionData connection_data)
{
using (var eh = new ExcelHelper(true))
{
var r = new ExRange(eh, eh.OpenWorkbook(fi, "", false), 1, 1);
var connectionString =
$"Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST={connection_data.Server})(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME={connection_data.DataBase})));User Id={connection_data.Login};Password={connection_data.Password};";
var factory = DbProviderFactories.GetFactory("Oracle.ManagedDataAccess.Client");
using (var connection = factory.CreateConnection())
{
if (connection == null)
{
throw new Exception($"{nameof(connection)} = null");
}
connection.ConnectionString = connectionString;
connection.Open();
for (var row = 2; row < 10000 && !string.IsNullOrWhiteSpace(r.Move(row, 3).Value("")); row++)
{
try
{
var reciever = this.GetSubjectPayData(connection, factory,
r.Move(row, 3).Value(""),
r.Move(row, 4).Value(""));
var payin = new PayManager.Payin(
r.Move(row, 7).Value(""),
r.Move(row, 5).Value(""),
r.Move(row, 6).Value(DateTime.MinValue)
);
var sum = r.Move(row, 10).Value(0d);
var purpose = $"{r.Move(row, 9).Value("noname")} за {r.Move(row, 8).Value("noisin")} по {r.Move(row, 1).Value("nonumber")} від {r.Move(row, 2).Value(DateTime.MinValue)}";
this.payManager_.AddPayout(payin, ndu_, reciever, "", DateTime.Now, sum, purpose);
}
catch (Exception e)
{
AppLogAndEventHelper.Instance.RaiseError(e);
AppLogAndEventHelper.Instance.RaiseError($"Считывание {fi.Name} в {row} строке ошибка {e.Message}");
r.Range.Interior.Color = XlRgbColor.rgbLightPink;
}
}
}
}
}
private void ParsePayins(FileInfo fi)
{
using (var eh = new ExcelHelper())
{
var r = new ExRange(eh, eh.OpenWorkbook(fi), 1, 1);
var purpose_parsers = new List <PurposeParser> {
PurposeParser1, PurposeParser2
};
for (var row = 5; row < 10000 && !r.Move(row, 5).Value("").Contains("Всього оборотів"); row++)
{
if (!r.Move(row, 42).Value("").Contains("ОКПО"))
{
continue;
}
try
{
var edrpou = Regex.Match(r.Move(row, 42).Value(""), @"[0-9]+").Value;
var sum = r.Move(row - 4, 35).Value(0d);
var purpose = r.Move(row + 1, 10).Value("");
Tuple <string, DateTime> t = null;
foreach (var pp in purpose_parsers)
{
t = pp.Invoke(purpose);
if (t != null)
{
break;
}
}
if (t == null)
{
throw new Exception("Не удалось распарсить поле 'Призначен'");
}
this.payManager_.AddPayinOrRest(edrpou, t.Item1, t.Item2, sum);
}
catch (Exception e)
{
AppLogAndEventHelper.Instance.RaiseError(e);
AppLogAndEventHelper.Instance.RaiseError($"Считывание {fi.Name} в {row - 4}-{row + 1} строках ошибка {e.Message}");
r.Range.Interior.Color = XlRgbColor.rgbLightPink;
}
}
}
}
private static void JoinPayouts(List <FileInfo> fis, FileInfo file)
{
using (var eh = new ExcelHelper(true))
{
var saveSheet = eh.CreateWorkbook(file);
var save_row = 0;
foreach (var fi in fis)
{
var sheet = eh.OpenWorkbook(fi);
var r = new ExRange(eh, eh.OpenWorkbook(fi), 1, 1);
for (var row = 2; row < 10000 && !string.IsNullOrWhiteSpace(r.Move(row, 3).Value("")); row++)
{
try
{
if (save_row == 0)
{
eh.CopyTo(sheet, 1, 1, 2, 20, saveSheet, 1, 1); //с заголовками
save_row = 3;
}
else
{
eh.CopyTo(sheet, row, 1, row, 20, saveSheet, save_row, 1);
save_row += 1;
}
}
catch (Exception e)
{
AppLogAndEventHelper.Instance.RaiseError(e);
AppLogAndEventHelper.Instance.RaiseError($"Считывание {fi.Name} в {row} строке ошибка {e.Message}");
}
}
eh.CloseWorkbook(sheet.Parent as Workbook);
}
(saveSheet.Parent as Workbook)?.Save();
}
}
private static void WriteToExcel(IReadOnlyCollection <Data> datas, FileInfo fi)
{
var text = new string[datas.Count, 11];
//0 2 Депозитарний код рахунку у ЦП
//1 3 Найменування / ПІБ акціонера
//2 4 Код ІПН(ДРФО)
//3 5 Код ЄДРПОУ
//4 6 Серія та номер паспорту
//5 7 Орган, який видав
//6 8 Дата видачі
//7 9 Місцезнаходження
//8 10 Кількість ЦП
//9 11 -
//10 12 Ошибки
var i = -1;
foreach (var data in datas)
{
try
{
i++;
text[i, 0] = data.Account;
text[i, 1] = data.Name;
text[i, 7] = data.Address;
text[i, 8] = data.Quantity.ToString();
text[i, 9] = string.Empty;
text[i, 10] = data.PassportData.MistakeRate > 0 ? data.PassportData.Text : string.Empty;
if (data.OwnerCode == OwnerCode.LegalEntity)
{
text[i, 3] = data.Code;
}
else
{
text[i, 2] = data.Code;
text[i, 4] = data.PassportData.Seria + data.PassportData.Number;
text[i, 5] = data.PassportData.IssuedBy;
text[i, 6] = data.PassportData.Date.ToString("dd.MM.yyyy");
}
}
catch (Exception ex)
{
AppLogAndEventHelper.Instance.RaiseDebugInfo($"Строка = {i} Ошибка = {ex.Message}");
AppLogAndEventHelper.Instance.RaiseError(ex);
}
}
var f = Path.Combine(Path.GetDirectoryName(fi.FullName), Path.GetFileNameWithoutExtension(fi.FullName));
fi = excelTemplate_.CopyTo($"{f}_{DateTime.Now:yyMMdd_HHmmss}.xlsx");
const int START_ROW = 6;
using (var eh = new ExcelHelper())
{
var r = new ExRange(eh, eh.OpenWorkbook(fi, "", false), 1, 1);
r.Move(START_ROW, 2, START_ROW + i + 1, 12).Range.Value2 = text;
r.Book.Save();
}
}