Advertisement
Guest User

Xml way

a guest
Jun 19th, 2019
101
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
C# 5.08 KB | None | 0 0
  1. void Main() {
  2.     var wb = LoadWorkbook(@"D:\Downloads\EU.xlsx");
  3.     //  wb.DocumentSettings.Calculation.Mode = DevExpress.Spreadsheet.CalculationMode.Manual;
  4.     //  wb.Worksheets["Output"].Range["A49:E70"].ExistingCells.Select(x => $"{Column.FromNumber(x.ColumnIndex + 1)}{x.RowIndex + 1} {x.Value.TextValue}".Dump()).ToArray();
  5.     var doc = ProcessBlock(wb.Worksheets["Output"], 50, 1);
  6.     doc.Dump();
  7.  
  8.     XmlReader reader = new XmlNodeReader(doc);
  9.     var serializer = new XmlSerializer(typeof(Premium));
  10.     Premium result = (Premium)serializer.Deserialize(reader);
  11.     result.Dump();
  12.     reader.Dispose();
  13. }
  14.  
  15. XmlDocument ProcessBlock(Worksheet worksheet, int topRow, int leftColumn) {
  16.     XmlDocument root = new XmlDocument();
  17.     Func<String, XmlElement> createElement = elementName => root.CreateElement(elementName)
  18.     var premium = root.AppendChild(createElement("Premium"));
  19.  
  20.     int row = topRow;
  21.     while (true) {
  22.         XmlElement element;
  23.         int height;
  24.         var value = worksheet[row, leftColumn].Value;
  25.  
  26.         if (value.IsEmpty) {
  27.             break;
  28.         } else {
  29.             var text = value.TextValue.RemoveWhiteSpaces();
  30.             if (text.EndsWith(CollectionSuffix)) {
  31.                 (element, height) = ParseToCollection(text, worksheet, row, leftColumn, createElement);
  32.             } else if (text.EndsWith(TableSuffix)) {
  33.                 (element, height) = ParseToTable(text, worksheet, row, leftColumn, createElement);
  34.             } else {
  35.                 element = ParseToSingleElement(worksheet, text, row, leftColumn, createElement);
  36.                 height = 1;
  37.             }
  38.         }
  39.         premium.AppendChild(element);
  40.         row += height + 1; // +1 for the empty row under the collection
  41.     }
  42.     return root;
  43. }
  44.  
  45. XmlElement ParseToSingleElement(Worksheet worksheet, string elementName, int row, int leftColumn, Func<string, XmlElement> createElement) {
  46.     var element = createElement(elementName);
  47.     element.InnerText = worksheet[row, leftColumn + 1].Value.ToString();
  48.     return element;
  49. }
  50.  
  51. (XmlElement table, int tableHeight) ParseToTable(string tableName, Worksheet worksheet, int topRow, int leftColumn, Func<string, XmlElement> createElement) {
  52.     var tableBodyRow = topRow + 1;
  53.     var element = createElement(tableName);
  54.  
  55.     //get labels from the area under table header
  56.     var labelsColumn = worksheet.Columns[leftColumn];
  57.     List<string> labels = GetColumnValuesUntilEmpty(labelsColumn, tableBodyRow);
  58.  
  59.     //get element properties from the column to the right of lables
  60.     var elementColumn = worksheet.Columns[leftColumn + 1];
  61.     var properties = GetElementProperties(elementColumn, tableBodyRow, i => createElement(labels[i]));
  62.     properties.ForEach(x => element.AppendChild(x));
  63.     var totalTableHeight = properties.Count + 1; // +1 for the header of the table
  64.  
  65.     return (element, totalTableHeight);
  66. }
  67.  
  68. (XmlElement collection, int collectionHeight) ParseToCollection(string collectionName, Worksheet worksheet, int topRow, int leftColumn, Func<string, XmlElement> createElement) {
  69.     var elementNameLenght = collectionName.Length - CollectionSuffix.Length;
  70.     var elementName = collectionName.Substring(0, elementNameLenght);
  71.  
  72.     // get collection elements from the area under the header of the collection
  73.     var (elements, elementsHeight) = GetCollectionElements(elementName, worksheet, topRow + 1, leftColumn, createElement);
  74.  
  75.     var collectionRoot = createElement(collectionName);
  76.     elements.ForEach(x => collectionRoot.AppendChild(x));
  77.     var totalCollectionHeight = elementsHeight + 1; // +1 for the header of the collection
  78.  
  79.     return (collectionRoot, totalCollectionHeight);
  80. }
  81.  
  82. (List<XmlElement>, int elementsHeight) GetCollectionElements(string elementName, Worksheet worksheet, int topRow, int leftColumn, Func<string, XmlElement> createElement) {
  83.     List<XmlElement> elements = new List<XmlElement>();
  84.  
  85.     var labelsColumn = worksheet.Columns[leftColumn];
  86.     List<string> labels = GetColumnValuesUntilEmpty(labelsColumn, topRow);
  87.  
  88.     // get collection elements from the area to the right of labels column
  89.     for (int columnIndex = leftColumn + 1; ; columnIndex++) {
  90.         var elementColumn = worksheet.Columns[columnIndex];
  91.  
  92.         var element = createElement(elementName);
  93.  
  94.         var properties = GetElementProperties(elementColumn, topRow, i => createElement(labels[i]));
  95.         if (properties.Count != 0) {
  96.             properties.ForEach(x => element.AppendChild(x));
  97.             elements.Add(element);
  98.         } else {
  99.             //right edge of the collection reached
  100.             break;
  101.         }
  102.     }
  103.     return (elements, labels.Count);
  104. }
  105.  
  106. List<XmlElement> GetElementProperties(DevExpress.Spreadsheet.Column column, int topRow, Func<int, XmlElement> createElement) {
  107.     List<XmlElement> properties = new List<XmlElement>();
  108.  
  109.     var columnValues = GetColumnValuesUntilEmpty(column, topRow);  
  110.     for (int row = 0; row < columnValues.Count; row++) {
  111.         var property = createElement(row);
  112.         property.InnerText = columnValues[row].ToString();
  113.         properties.Add(property);
  114.     }
  115.     return properties;
  116. }
  117.  
  118. List<string> GetColumnValuesUntilEmpty(DevExpress.Spreadsheet.Column column, int topRow) {
  119.     List<string> labels = new List<string>();
  120.  
  121.     for (int row = topRow; ; row++) {
  122.         var value = column[row].Value;
  123.         if (value.IsEmpty) {
  124.             //bottom reached
  125.             break;
  126.         } else {
  127.             labels.Add(value.ToString());
  128.         }
  129.     }
  130.     return labels;
  131. }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement