Advertisement
Guest User

Json way

a guest
Jun 19th, 2019
85
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
C# 3.88 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.ToString().Dump();
  7.  
  8.     doc.ToObject<Premium>().Dump();
  9. }
  10.  
  11. JObject ProcessBlock(Worksheet worksheet, int topRow, int leftColumn) {
  12.     Newtonsoft.Json.Linq.JObject root = new Newtonsoft.Json.Linq.JObject();
  13.  
  14.     int row = topRow;
  15.     while (true) {
  16.         JToken content = null;
  17.         string elementName = "";
  18.         int height = 0;
  19.         var value = worksheet[row, leftColumn].Value;
  20.  
  21.         if (value.IsEmpty) {
  22.             break;
  23.         } else {
  24.             elementName = value.TextValue.RemoveWhiteSpaces();
  25.             if (elementName.EndsWith(CollectionSuffix)) {
  26.                 (content, height) = ParseToCollection(worksheet, row, leftColumn);
  27.             } else if (elementName.EndsWith(TableSuffix)) {
  28.                 (content, height) = ParseToTable(worksheet, row, leftColumn);
  29.             } else {
  30.                 content = ParseToSingleElement(worksheet, row, leftColumn);
  31.                 height = 1;
  32.             }
  33.         }
  34.         root[elementName] = content;
  35.         row += height + 1; // +1 for the empty row under the collection
  36.     }
  37.     return root;
  38. }
  39.  
  40. JToken ParseToSingleElement(Worksheet worksheet, int row, int leftColumn) {
  41.     return worksheet[row, leftColumn + 1].Value.ToString();
  42.  
  43. }
  44.  
  45. (JObject table, int tableHeight) ParseToTable(Worksheet worksheet, int topRow, int leftColumn) {
  46.     var tableBodyRow = topRow + 1;
  47.  
  48.     //get labels from the area under table header
  49.     var labelsColumn = worksheet.Columns[leftColumn];
  50.     List<string> labels = GetColumnValuesUntilEmpty(labelsColumn, tableBodyRow);
  51.  
  52.     //get element properties from the column to the right of lables
  53.     var elementColumn = worksheet.Columns[leftColumn + 1];
  54.     var element = GetElement(elementColumn, tableBodyRow, labels);
  55.     var totalTableHeight = labels.Count + 1; // +1 for the header of the table
  56.  
  57.     return (element, totalTableHeight);
  58. }
  59.  
  60. (JArray collection, int collectionHeight) ParseToCollection(Worksheet worksheet, int topRow, int leftColumn) {
  61.     JArray array = new JArray();
  62.     // get collection elements from the area under the header of the collection
  63.     var (elements, elementsHeight) = GetCollectionElements(worksheet, topRow + 1, leftColumn);
  64.  
  65.     elements.ForEach(x => array.Add(x));
  66.     var totalCollectionHeight = elementsHeight + 1; // +1 for the header of the collection
  67.  
  68.     return (array, totalCollectionHeight);
  69. }
  70.  
  71. (List<JToken>, int elementsHeight) GetCollectionElements(Worksheet worksheet, int topRow, int leftColumn) {
  72.     List<JToken> elements = new List<JToken>();
  73.  
  74.     var labelsColumn = worksheet.Columns[leftColumn];
  75.     List<string> labels = GetColumnValuesUntilEmpty(labelsColumn, topRow);
  76.  
  77.     // get collection elements from the area to the right of labels column
  78.     for (int columnIndex = leftColumn + 1; ; columnIndex++) {
  79.         var elementColumn = worksheet.Columns[columnIndex];
  80.         var element = GetElement(elementColumn, topRow, labels);
  81.         if (element != null) {
  82.             elements.Add(element);
  83.         } else {
  84.             //right edge of the collection reached
  85.             break;
  86.         }
  87.     }
  88.     return (elements, labels.Count);
  89. }
  90.  
  91. JObject GetElement(DevExpress.Spreadsheet.Column elementColumn, int topRow, List<string> labels) {
  92.     JObject element = null;
  93.     var properties = GetColumnValuesUntilEmpty(elementColumn, topRow);
  94.     for (int i = 0; i < properties.Count; i++) {
  95.         element = element ?? new JObject();
  96.         element[labels[i]] = properties[i];
  97.     }
  98.     return element;
  99. }
  100.  
  101. List<string> GetColumnValuesUntilEmpty(DevExpress.Spreadsheet.Column column, int topRow) {
  102.     List<string> labels = new List<string>();
  103.  
  104.     for (int row = topRow; ; row++) {
  105.         var value = column[row].Value;
  106.         if (value.IsEmpty) {
  107.             //bottom reached
  108.             break;
  109.         } else {
  110.             labels.Add(value.ToString());
  111.         }
  112.     }
  113.     return labels;
  114. }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement