public RowModel(ExcelWorksheet sheet, int row, AccountColumnMap map)
{
RowNum = row;
Address = GetStringValue(sheet, row, map.AddressCol);
PremiseNum = GetStringValue(sheet, row, map.PremiseNumCol);
LivingOrNot = GetStringValue(sheet, row, map.LivingOrNotCol);
LivingRoomNum = GetStringValue(sheet, row, map.LivingRoomNumCol);
Number = GetStringValue(sheet, row, map.NumberCol);
LastName = GetStringValue(sheet, row, map.LastNameCol);
FirstName = GetStringValue(sheet, row, map.FirstNameCol);
SecondName = ToLettersOnly(GetStringValue(sheet, row, map.SecondNameCol));
TotalSquare = GetStringValue(sheet, row, map.TotalSquareCol);
ResidentialSquare = GetStringValue(sheet, row, map.ResidentialSquareCol);
HeatedSquare = GetStringValue(sheet, row, map.HeatedSquareCol);
LivingPersons = GetStringValue(sheet, row, map.LivingPersonsCol);
IsRenter = GetStringValue(sheet, row, map.IsRenterCol);
}
private static List <RowModel> GetAccountModels(AccountColumnMap map, ExcelWorksheet sheet)
{
var models = new List <RowModel>();
for (int i = 2; sheet.Cells[i, 1].Value != null; i++)
{
var resultCell = sheet.Cells[i, map.ResultCol];
if (resultCell.Value != null &&
(resultCell.Value.ToString().Contains("успешно") || resultCell.Value.ToString().Contains("Уже есть в ГИС")))
{
continue;
}
models.Add(new RowModel(sheet, i, map));
}
return(models);
}
static void Main(string[] args)
{
List <RowModel> accounts;
List <OgfModel> ogfs;
string path = @"C:\Users\van\Documents\ExcelParsing\норильск\Шаблон последний для РК ЗАПОЛНЕН.xlsx";
using (var pack = new ExcelPackage(new FileInfo(path)))
{
var sheet = pack.Workbook.Worksheets[1];
var map = new AccountColumnMap(sheet);
accounts = GetAccountModels(map, sheet);
}
string pathOgf = @"C:\Users\van\Documents\ExcelParsing\норильск\норильск_ожф.xlsx";
using (var pack = new ExcelPackage(new FileInfo(pathOgf)))
{
var sheet = pack.Workbook.Worksheets[1];
var map = new OgfMap(sheet);
ogfs = GetOgfModels(map, sheet);
}
foreach (var acc in accounts)
{
var adrParts = acc.Address.Split(',');
foreach (var ogf in ogfs)
{
var ogfParts = ogf.Address.Split(',');
if (adrParts[2] == ogfParts[2] && adrParts[3] == ogfParts[3] && adrParts[4] == ogfParts[4] &&
acc.PremiseNum == ogf.PremiseNum)
{
acc.UniquePremiseNum = ogf.UniquePremiseNum;
acc.FullAddress = ogf.Address;
break;
}
}
}
var accsToUpload = accounts.Where(x => x.UniquePremiseNum == null).Select(x => x.Number).ToList();
using (var pack = new ExcelPackage(new FileInfo(path)))
{
var sheet = pack.Workbook.Worksheets[1];
for (int i = 1; sheet.Cells[i, 5].Value != null; i++)
{
if (!accsToUpload.Contains(sheet.Cells[i, 5].Text.Trim()))
{
sheet.Cells[i, 1].Value = null;
}
}
pack.SaveAs(new FileInfo(@"C:\Users\van\Documents\ExcelParsing\норильск\остатки.xlsx"));
}
// for (int i = 13; i <= 14; i++)
// {
// var accs = accsToUpload.Skip((i - 13) * 1000).Take(1000);
// using(var pack = new ExcelPackage(new FileInfo(@"C:\Users\van\Documents\ExcelParsing\норильск\template.xlsx")))
// {
// var sheet1 = pack.Workbook.Worksheets[1];
// var sheet2 = pack.Workbook.Worksheets[2];
// int count = 0;
// foreach (var acc in accs)
// {
// count++;
// sheet1.Cells[count + 2, 1].Value = count;
// sheet1.Cells[count + 2, 2].Value = acc.Number;
// sheet1.Cells[count + 2, 4].Value = "ЛС УО";
// sheet1.Cells[count + 2, 5].Value = acc.IsRenter;
// sheet1.Cells[count + 2, 6].Value = "Нет";
// sheet1.Cells[count + 2, 7].Value = acc.LastName;
// sheet1.Cells[count + 2, 8].Value = acc.FirstName;
// sheet1.Cells[count + 2, 9].Value = acc.SecondName;
// sheet1.Cells[count + 2, 18].Value = acc.TotalSquare;
// sheet1.Cells[count + 2, 19].Value = acc.ResidentialSquare;
// sheet1.Cells[count + 2, 21].Value = acc.LivingPersons;
// sheet2.Cells[count + 2, 1].Value = count;
// sheet2.Cells[count + 2, 2].Value = acc.FullAddress;
// sheet2.Cells[count + 2, 7].Value = acc.UniquePremiseNum;
// sheet2.Cells[count + 2, 8].Value = 100;
// }
// pack.SaveAs(new FileInfo($@"C:\Users\van\Documents\ExcelParsing\норильск\4gis\{i}.xlsx"));
// }
// }
}
