SHARE
TWEET

Untitled

a guest Jun 18th, 2019 61 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. void Main() {
  2.     var wb = LoadWorkbook(@"D:\Downloads\EU.xlsx");
  3.     var tree1 = ProccessBlock<Premium>(wb.Worksheets["Output"], 50, 1);
  4.  
  5.     var tree2 = ProccessBlock<Premium>(wb.Worksheets["Validation"], 5, 1);
  6.  
  7.  
  8.     var a = new Premium();
  9.     tree1(wb.Worksheets["Output"], a);
  10.     a.Dump();
  11.     var b = new Premium();
  12.     tree2(wb.Worksheets["Validation"], b);
  13.     b.Dump();
  14. }
  15.  
  16. Action<Worksheet, T> ProccessBlock<T>(Worksheet templateWorksheet, int topRow, int leftColumn) {
  17.     List<Action<Worksheet, T>> actions = new List<Action<Worksheet, T>>();
  18.  
  19.     int row = topRow;
  20.     while (true) {
  21.         string elementName = "";
  22.         int height = 0;
  23.         var value = templateWorksheet[row, leftColumn].Value;
  24.  
  25.         if (value.IsEmpty) {
  26.             break;
  27.         } else {
  28.             elementName = value.TextValue.RemoveWhiteSpaces();
  29.             if (elementName.EndsWith(CollectionSuffix)) {
  30.                 var result = ParseToCollection<T>(templateWorksheet, elementName, row, leftColumn);
  31.                 height = result.Item2;
  32.                 actions.Add(result.Item1);
  33.             } else if (elementName.EndsWith(TableSuffix)) {
  34.                 var result = ParseToTable<T>(templateWorksheet, elementName, row, leftColumn);
  35.                 height = result.Item2;
  36.                 actions.Add(result.Item1);
  37.             } else {
  38.                 var transformation = ParseToSingleElement<T>(elementName, row, leftColumn);
  39.                 actions.Add(transformation);
  40.                 height = 1;
  41.             }
  42.         }
  43.         row += height + 1; // +1 for the empty row under the collection
  44.     }
  45.     return (worksheet, obj) => {
  46.         actions.ForEach(x => x(worksheet, obj));
  47.     };
  48. }
  49.  
  50. Action<Worksheet, T> ParseToSingleElement<T>(string elementName, int row, int leftColumn) {
  51.     var property = typeof(T).GetProperty(elementName);
  52.     return (worksheet, x) => property.SetValue(x, worksheet[row, leftColumn + 1].Value.ToString());
  53. }
  54.  
  55. (Action<Worksheet, T>, int) ParseToTable<T>(Worksheet templateWorksheet, string tableName, int topRow, int leftColumn) {
  56.     var tableBodyRow = topRow + 1;
  57.     var property = typeof(T).GetProperty(tableName);
  58.     var propertyType = property.PropertyType;
  59.  
  60.     //get labels from the area under table header
  61.     var labelsColumn = templateWorksheet.Columns[leftColumn];
  62.     List<CellValue> labels = GetColumnValuesUntilEmpty(labelsColumn, tableBodyRow);
  63.  
  64.     var totalTableHeight = labels.Count + 1; // +1 for the header of the table
  65.  
  66.     return ((worksheet, obj) => {
  67.         var propertyValue = Activator.CreateInstance(propertyType);
  68.  
  69.         //get element properties from the column to the right of lables
  70.         var elementColumn = worksheet.Columns[leftColumn + 1];
  71.         propertyValue = GetElement(propertyType, labels, elementColumn, tableBodyRow);
  72.  
  73.         property.SetValue(obj, propertyValue);
  74.     }
  75.     , totalTableHeight);
  76. }
  77.  
  78. (Action<Worksheet, T>, int) ParseToCollection<T>(Worksheet templateWorksheet, string collectionName, int topRow, int leftColumn) {
  79.     var property = typeof(T).GetProperty(collectionName);
  80.     var propertyType = property.PropertyType.GetGenericArguments()[0];
  81.  
  82.     Type listType = typeof(List<>).MakeGenericType(propertyType);
  83.  
  84.     var (transformation, elementsHeight) = GetCollectionElements(templateWorksheet, propertyType, topRow + 1, leftColumn);
  85.  
  86.     var totalCollectionHeight = elementsHeight + 1; // +1 for the header of the collection
  87.  
  88.     return ((worksheet, obj) => {
  89.         var list = (IList)Activator.CreateInstance(listType);
  90.  
  91.         // get collection elements from the area under the header of the collection
  92.  
  93.         transformation(worksheet, list);
  94.         property.SetValue(obj, list);
  95.     }
  96.     , totalCollectionHeight);
  97. }
  98.  
  99. (Action<Worksheet, IList>, int) GetCollectionElements(Worksheet templateWorksheet, Type type, int topRow, int leftColumn) {
  100.     var labelsColumn = templateWorksheet.Columns[leftColumn];
  101.     List<CellValue> labels = GetColumnValuesUntilEmpty(labelsColumn, topRow);
  102.  
  103.     return ((worksheet, list) => {
  104.         // get collection elements from the area to the right of labels column
  105.         for (int columnIndex = leftColumn + 1; ; columnIndex++) {
  106.  
  107.             var elementColumn = worksheet.Columns[columnIndex];
  108.             var element = GetElement(type, labels, elementColumn, topRow);
  109.             if (element != null) {
  110.                 list.Add(element);
  111.             } else {
  112.                 //right edge of the collection reached
  113.                 break;
  114.             }
  115.         }
  116.     }
  117.     , labels.Count);
  118. }
  119.  
  120. object GetElement(Type type, List<CellValue> labels, DevExpress.Spreadsheet.Column elementColumn, int topRow) {
  121.     var propertyValues = GetColumnValuesUntilEmpty(elementColumn, topRow);
  122.  
  123.     if (propertyValues.Count == 0) {
  124.         return null;
  125.     } else {
  126.         var element = Activator.CreateInstance(type);
  127.  
  128.         for (int i = 0; i < labels.Count; i++) {
  129.             var property = type.GetProperty(labels[i].TextValue);
  130.             var propertyType = property.PropertyType;
  131.  
  132.             var propertyValue = Convert.ChangeType(propertyValues[i].ToObject(), propertyType);
  133.             property.SetValue(element, propertyValue);
  134.         }
  135.  
  136.         return element;
  137.     }
  138. }
  139.  
  140. List<CellValue> GetColumnValuesUntilEmpty(DevExpress.Spreadsheet.Column column, int topRow) {
  141.     var values = new List<CellValue>();
  142.  
  143.     for (int row = topRow; ; row++) {
  144.         var value = column[row].Value;
  145.         if (value.IsEmpty) {
  146.             //bottom reached
  147.             break;
  148.         } else {
  149.             values.Add(value);
  150.         }
  151.     }
  152.     return values;
  153. }
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!
 
Top