/// <summary> /// TODO: переписать реализацию /// </summary> public static void SetDynamicCellValue(IXLCell cell, object value) { switch (value) { case string stringValue: cell.SetValue(stringValue); cell.SetDataType(XLDataType.Text); break; case int intValue: cell.SetValue(intValue); cell.SetDataType(XLDataType.Number); break; case long longValue: cell.SetValue(longValue); cell.SetDataType(XLDataType.Number); break; case double doubleValue: cell.SetValue(doubleValue); cell.SetDataType(XLDataType.Number); break; default: throw new Exception($"Неизвестный тип: {value?.GetType().Name}"); } }
private static DateTime FormatDateCell(IXLCell cell) { cell.SetDataType(XLDataType.DateTime); var value = cell.GetDateTime().ToString(FormatoDataExcel, Execute.CulturaPtBr); return(DateTime.Parse(value)); }
private static void Main(string[] args) { string scriptDirectoryPath = AppDomain.CurrentDomain.BaseDirectory; if (!Path.IsPathRooted(scriptDirectoryPath)) { return; } var scriptDirectory = new DirectoryInfo(scriptDirectoryPath); DirectoryInfo excerptsDirectory = scriptDirectory.Parent; if (excerptsDirectory == null || !excerptsDirectory.Exists) { return; } const StringComparison comparison = StringComparison.InvariantCultureIgnoreCase; var estates = new List <Estate>(); foreach (FileInfo file in excerptsDirectory.EnumerateFiles("*.xml")) { var builder = new StringBuilder(); string[] lines = File.ReadAllLines(file.FullName); foreach (string line in lines) { if (!line.StartsWith("<?")) { if (!string.IsNullOrEmpty(line)) { builder.Append(line); builder.Append(Environment.NewLine); } } } string text = builder.ToString(); var document = new XmlDocument(); try { document.LoadXml(text); } catch (Exception e) { Console.WriteLine(file.FullName); Console.WriteLine(e); continue; } foreach (XmlNode kpoks in document.ChildNodes) { if (string.Equals(kpoks.Name, "KPOKS", comparison)) { foreach (XmlNode realty in kpoks.ChildNodes) { if (string.Equals(realty.Name, "Realty", comparison)) { foreach (XmlNode flat in realty.ChildNodes) { if (string.Equals(flat.Name, "Flat", comparison)) { var estate = new Estate(); estates.Add(estate); if (flat.Attributes != null) { foreach (XmlAttribute a in flat.Attributes) { if (string.Equals(a.Name, "CadastralNumber", comparison)) { estate.CadastralNumber = a.Value; } else if (string.Equals(a.Name, "DateCreated", comparison)) { estate.DateCreated = a.Value; } } } foreach (XmlNode flatChild in flat.ChildNodes) { if (string.Equals(flatChild.Name, "CadastralBlock", comparison)) { estate.CadastralBlock = flatChild.InnerText; } else if (string.Equals(flatChild.Name, "ObjectType", comparison)) { estate.ObjectType = flatChild.InnerText; } else if (string.Equals(flatChild.Name, "Area", comparison)) { estate.Area = flatChild.InnerText; } else if (string.Equals(flatChild.Name, "Address", comparison)) { foreach (XmlNode addressChild in flatChild.ChildNodes) { if (string.Equals(addressChild.Name, "adrs:OKATO", comparison)) { estate.Okato = addressChild.InnerText; } else if (string.Equals(addressChild.Name, "adrs:KLADR", comparison)) { estate.Kladr = addressChild.InnerText; } else if (string.Equals(addressChild.Name, "adrs:PostalCode", comparison)) { estate.PostalCode = addressChild.InnerText; } else if (string.Equals(addressChild.Name, "adrs:Region", comparison)) { estate.Region = addressChild.InnerText; } else if (string.Equals(addressChild.Name, "adrs:UrbanDistrict", comparison)) { string type = null; string name = null; if (addressChild.Attributes != null) { foreach (XmlAttribute a in addressChild.Attributes) { if (string.Equals(a.Name, "Type", comparison)) { type = a.Value; } else if (string.Equals(a.Name, "Name", comparison)) { name = a.Value; } } } estate.UrbanDistrict = $"{name} {type}"; } else if (string.Equals(addressChild.Name, "adrs:Street", comparison)) { string type = null; string name = null; if (addressChild.Attributes != null) { foreach (XmlAttribute a in addressChild.Attributes) { if (string.Equals(a.Name, "Type", comparison)) { type = a.Value; } else if (string.Equals(a.Name, "Name", comparison)) { name = a.Value; } } } estate.Street = $"{name} {type}"; } else if (string.Equals(addressChild.Name, "adrs:Level1", comparison)) { if (addressChild.Attributes != null) { foreach (XmlAttribute a in addressChild.Attributes) { if (string.Equals(a.Name, "Value", comparison)) { estate.Level1 = a.Value; } } } } else if (string.Equals(addressChild.Name, "adrs:Apartment", comparison)) { if (addressChild.Attributes != null) { foreach (XmlAttribute a in addressChild.Attributes) { if (string.Equals(a.Name, "Value", comparison)) { estate.Apartment = a.Value; } } } } } } else if (string.Equals(flatChild.Name, "CadastralCost", comparison)) { if (flatChild.Attributes != null) { foreach (XmlAttribute a in flatChild.Attributes) { if (string.Equals(a.Name, "Value", comparison)) { estate.CadastralCost = a.Value; } else if (string.Equals(a.Name, "Unit", comparison)) { estate.CadastralCostUnit = a.Value; } } } } else if (string.Equals(flatChild.Name, "Rights", comparison)) { foreach (XmlNode rightNode in flatChild.ChildNodes) { if (string.Equals(rightNode.Name, "Right", comparison)) { var right = new Right(); estate.Rights.Add(right); foreach (XmlNode rightChild in rightNode.ChildNodes) { if (string.Equals(rightChild.Name, "Name", comparison)) { right.Name = rightChild.InnerText; } else if (string.Equals(rightChild.Name, "Type", comparison)) { right.Type = rightChild.InnerText; } else if (string.Equals(rightChild.Name, "Owners", comparison)) { foreach (XmlNode ownerNode in rightChild.ChildNodes) { if (string.Equals(ownerNode.Name, "Owner", comparison)) { var owner = new Owner(); right.Owners.Add(owner); foreach (XmlNode person in ownerNode.ChildNodes) { if (string.Equals(person.Name, "Person", comparison)) { foreach (XmlNode child in person.ChildNodes) { if (string.Equals(child.Name, "FamilyName", comparison)) { owner.FamilyName = child.InnerText; } else if (string.Equals(child.Name, "FirstName", comparison)) { owner.FirstName = child.InnerText; } else if (string.Equals(child.Name, "Patronymic", comparison)) { owner.Patronymic = child.InnerText; } } } } } } } else if (string.Equals(rightChild.Name, "Registration", comparison)) { foreach (XmlNode child in rightChild.ChildNodes) { if (string.Equals(child.Name, "RegNumber", comparison)) { right.RegNumber = child.InnerText; } else if (string.Equals(child.Name, "RegDate", comparison)) { right.RegDate = child.InnerText; } } } } } } } } break; } } break; } } break; } } } var workbook = new XLWorkbook(); IXLWorksheet worksheet = workbook.Worksheets.Add("Flats"); var headers = new[] { "Квартира", "ОКАТО", "КЛАДР", "Индекс", "Регион", "Район", "Улица", "Дом", "Кадастровый номер", "Кадастровый блок", "Тип объекта", "Площадь", "Кадастровая стоимость", "Собственность", "Тип собственности", "Регистрационный номер", "Дата регистрации", "ФИО" }; char[] alphabet = "ABCDEFGHIJKLMNOPQRSTUVWXYZ".ToCharArray(); for (int i = 0; i < headers.Length; i++) { IXLCell a1 = worksheet.Cell($"{alphabet[i]}1"); a1.Value = headers[i]; a1.SetDataType(XLCellValues.Text); IXLStyle style = a1.Style; style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center); style.Font.Bold = true; } int row = 1; foreach (Estate estate in estates) { row++; IXLCell a = worksheet.Cell($"A{row}"); a.Value = estate.Apartment; a.SetDataType(XLCellValues.Text); IXLCell b = worksheet.Cell($"B{row}"); b.Value = estate.Okato; b.SetDataType(XLCellValues.Text); IXLCell c = worksheet.Cell($"C{row}"); c.Value = estate.Kladr; c.SetDataType(XLCellValues.Text); IXLCell d = worksheet.Cell($"D{row}"); d.Value = estate.PostalCode; d.SetDataType(XLCellValues.Text); IXLCell e = worksheet.Cell($"E{row}"); e.Value = estate.Region; e.SetDataType(XLCellValues.Text); IXLCell f = worksheet.Cell($"F{row}"); f.Value = estate.UrbanDistrict; f.SetDataType(XLCellValues.Text); IXLCell g = worksheet.Cell($"G{row}"); g.Value = estate.Street; g.SetDataType(XLCellValues.Text); IXLCell h = worksheet.Cell($"H{row}"); h.Value = estate.Level1; h.SetDataType(XLCellValues.Text); IXLCell i = worksheet.Cell($"I{row}"); i.Value = estate.CadastralNumber; i.SetDataType(XLCellValues.Text); IXLCell j = worksheet.Cell($"J{row}"); j.Value = estate.CadastralBlock; j.SetDataType(XLCellValues.Text); IXLCell k = worksheet.Cell($"K{row}"); k.Value = estate.ObjectType; k.SetDataType(XLCellValues.Text); IXLCell l = worksheet.Cell($"L{row}"); l.Value = estate.Area; IXLCell m = worksheet.Cell($"M{row}"); m.Value = estate.CadastralCost; foreach (Right right in estate.Rights) { row++; IXLCell n = worksheet.Cell($"N{row}"); n.Value = right.Name; n.SetDataType(XLCellValues.Text); IXLCell o = worksheet.Cell($"O{row}"); o.Value = right.Type; o.SetDataType(XLCellValues.Text); IXLCell p = worksheet.Cell($"P{row}"); p.Value = right.RegNumber; p.SetDataType(XLCellValues.Text); IXLCell q = worksheet.Cell($"Q{row}"); q.Value = right.RegDate; foreach (Owner owner in right.Owners) { row++; IXLCell r = worksheet.Cell($"R{row}"); r.Value = $"{owner.FamilyName} {owner.FirstName} {owner.Patronymic}"; r.SetDataType(XLCellValues.Text); } } row++; } worksheet.Columns(1, 18).AdjustToContents(); string xlsxPath = Path.Combine(excerptsDirectory.FullName, $"{DateTime.Now:yyyy-MM-dd HH-mm-ss}.xlsx"); try { workbook.SaveAs(xlsxPath); Process.Start(xlsxPath); } catch (Exception e) { Console.WriteLine(e); } Console.ReadLine(); }