Advertisement
Guest User

Reflection way

a guest
Jun 19th, 2019
162
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
C# 4.24 KB | None | 0 0
  1. void Main() {
  2.     var wb = LoadWorkbook(@"D:\Downloads\EU.xlsx");
  3.     var a = ProccessBlock<Premium>(wb.Worksheets["Output"], 50, 1);
  4.     a.Dump();
  5. }
  6.  
  7. T ProccessBlock<T>(Worksheet worksheet, int topRow, int leftColumn) {
  8.     T root = (T)Activator.CreateInstance(typeof(T));
  9.  
  10.     int row = topRow;
  11.     while (true) {
  12.         string elementName = "";
  13.         int height = 0;
  14.         var value = worksheet[row, leftColumn].Value;
  15.  
  16.         if (value.IsEmpty) {
  17.             break;
  18.         } else {
  19.             elementName = value.TextValue.RemoveWhiteSpaces();
  20.             if (elementName.EndsWith(CollectionSuffix)) {
  21.                 height = ParseToCollection(root, elementName, worksheet, row, leftColumn);
  22.             } else if (elementName.EndsWith(TableSuffix)) {
  23.                 height = ParseToTable(root, elementName, worksheet, row, leftColumn);
  24.             } else {
  25.                 ParseToSingleElement(root, elementName, worksheet, row, leftColumn);
  26.                 height = 1;
  27.             }
  28.         }      
  29.         row += height + 1; // +1 for the empty row under the collection
  30.     }
  31.     return root;
  32. }
  33.  
  34. void ParseToSingleElement<T>(T obj, string elementName, Worksheet worksheet, int row, int leftColumn) {
  35.     var property = typeof(T).GetProperty(elementName);
  36.     property.SetValue(obj, worksheet[row, leftColumn + 1].Value.ToString());
  37. }
  38.  
  39. int ParseToTable<T>(T obj, string tableName, Worksheet worksheet, int topRow, int leftColumn) {
  40.     var tableBodyRow = topRow + 1;
  41.     var property = typeof(T).GetProperty(tableName);
  42.     var propertyType = property.PropertyType;
  43.    
  44.     var propertyValue = Activator.CreateInstance(propertyType);
  45.  
  46.     //get labels from the area under table header
  47.     var labelsColumn = worksheet.Columns[leftColumn];
  48.     List<CellValue> labels = GetColumnValuesUntilEmpty(labelsColumn, tableBodyRow);
  49.  
  50.     //get element properties from the column to the right of lables
  51.     var elementColumn = worksheet.Columns[leftColumn + 1];
  52.     propertyValue = GetElement(propertyType, labels, elementColumn, tableBodyRow);
  53.     property.SetValue(obj, propertyValue);
  54.    
  55.     var totalTableHeight = labels.Count + 1; // +1 for the header of the table
  56.     return totalTableHeight;
  57. }
  58.  
  59. int ParseToCollection<T>(T obj, string collectionName, Worksheet worksheet, int topRow, int leftColumn) {
  60.     var property = typeof(T).GetProperty(collectionName);
  61.     var propertyType = property.PropertyType.GetGenericArguments()[0];
  62.        
  63.     Type listType = typeof(List<>).MakeGenericType(propertyType);
  64.     var list = (IList)Activator.CreateInstance(listType);  
  65.    
  66.     // get collection elements from the area under the header of the collection
  67.     var elementsHeight = GetCollectionElements(list, propertyType, worksheet, topRow + 1, leftColumn);
  68.  
  69.     property.SetValue(obj, list);
  70.    
  71.     var totalCollectionHeight = elementsHeight + 1; // +1 for the header of the collection
  72.     return totalCollectionHeight;
  73. }
  74.  
  75. int GetCollectionElements(IList list, Type type, Worksheet worksheet, int topRow, int leftColumn) {
  76.     var labelsColumn = worksheet.Columns[leftColumn];
  77.     List<CellValue> labels = GetColumnValuesUntilEmpty(labelsColumn, topRow);
  78.  
  79.     // get collection elements from the area to the right of labels column
  80.     for (int columnIndex = leftColumn + 1; ; columnIndex++) {      
  81.        
  82.         var elementColumn = worksheet.Columns[columnIndex];
  83.         var element = GetElement(type, labels, elementColumn, topRow);
  84.         if (element != null) {
  85.             list.Add(element);
  86.         } else {
  87.             //right edge of the collection reached
  88.             break;
  89.         }
  90.     }
  91.     return labels.Count;
  92. }
  93.  
  94. object GetElement(Type type, List<CellValue> labels, DevExpress.Spreadsheet.Column elementColumn, int topRow) {
  95.     var propertyValues = GetColumnValuesUntilEmpty(elementColumn, topRow);
  96.    
  97.     if(propertyValues.Count == 0){
  98.         return null;
  99.     } else {
  100.         var element = Activator.CreateInstance(type);
  101.        
  102.         for (int i = 0; i < labels.Count; i++) {
  103.             var property = type.GetProperty(labels[i].TextValue);
  104.             var propertyType = property.PropertyType;
  105.  
  106.             var propertyValue = Convert.ChangeType(propertyValues[i].ToObject(), propertyType);
  107.             property.SetValue(element, propertyValue);
  108.         }
  109.  
  110.         return element;
  111.     }
  112. }
  113.  
  114. List<CellValue> GetColumnValuesUntilEmpty(DevExpress.Spreadsheet.Column column, int topRow) {
  115.     var values = new List<CellValue>();
  116.  
  117.     for (int row = topRow; ; row++) {
  118.         var value = column[row].Value;
  119.         if (value.IsEmpty) {
  120.             //bottom reached
  121.             break;
  122.         } else {
  123.             values.Add(value);
  124.         }
  125.     }
  126.     return values;
  127. }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement