Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- void Main() {
- var wb = LoadWorkbook(@"D:\Downloads\EU.xlsx");
- // wb.DocumentSettings.Calculation.Mode = DevExpress.Spreadsheet.CalculationMode.Manual;
- // wb.Worksheets["Output"].Range["A49:E70"].ExistingCells.Select(x => $"{Column.FromNumber(x.ColumnIndex + 1)}{x.RowIndex + 1} {x.Value.TextValue}".Dump()).ToArray();
- var doc = ProcessBlock(wb.Worksheets["Output"], 50, 1);
- doc.ToString().Dump();
- doc.ToObject<Premium>().Dump();
- }
- JObject ProcessBlock(Worksheet worksheet, int topRow, int leftColumn) {
- Newtonsoft.Json.Linq.JObject root = new Newtonsoft.Json.Linq.JObject();
- int row = topRow;
- while (true) {
- JToken content = null;
- string elementName = "";
- int height = 0;
- var value = worksheet[row, leftColumn].Value;
- if (value.IsEmpty) {
- break;
- } else {
- elementName = value.TextValue.RemoveWhiteSpaces();
- if (elementName.EndsWith(CollectionSuffix)) {
- (content, height) = ParseToCollection(worksheet, row, leftColumn);
- } else if (elementName.EndsWith(TableSuffix)) {
- (content, height) = ParseToTable(worksheet, row, leftColumn);
- } else {
- content = ParseToSingleElement(worksheet, row, leftColumn);
- height = 1;
- }
- }
- root[elementName] = content;
- row += height + 1; // +1 for the empty row under the collection
- }
- return root;
- }
- JToken ParseToSingleElement(Worksheet worksheet, int row, int leftColumn) {
- return worksheet[row, leftColumn + 1].Value.ToString();
- }
- (JObject table, int tableHeight) ParseToTable(Worksheet worksheet, int topRow, int leftColumn) {
- var tableBodyRow = topRow + 1;
- //get labels from the area under table header
- var labelsColumn = worksheet.Columns[leftColumn];
- List<string> labels = GetColumnValuesUntilEmpty(labelsColumn, tableBodyRow);
- //get element properties from the column to the right of lables
- var elementColumn = worksheet.Columns[leftColumn + 1];
- var element = GetElement(elementColumn, tableBodyRow, labels);
- var totalTableHeight = labels.Count + 1; // +1 for the header of the table
- return (element, totalTableHeight);
- }
- (JArray collection, int collectionHeight) ParseToCollection(Worksheet worksheet, int topRow, int leftColumn) {
- JArray array = new JArray();
- // get collection elements from the area under the header of the collection
- var (elements, elementsHeight) = GetCollectionElements(worksheet, topRow + 1, leftColumn);
- elements.ForEach(x => array.Add(x));
- var totalCollectionHeight = elementsHeight + 1; // +1 for the header of the collection
- return (array, totalCollectionHeight);
- }
- (List<JToken>, int elementsHeight) GetCollectionElements(Worksheet worksheet, int topRow, int leftColumn) {
- List<JToken> elements = new List<JToken>();
- var labelsColumn = worksheet.Columns[leftColumn];
- List<string> labels = GetColumnValuesUntilEmpty(labelsColumn, topRow);
- // get collection elements from the area to the right of labels column
- for (int columnIndex = leftColumn + 1; ; columnIndex++) {
- var elementColumn = worksheet.Columns[columnIndex];
- var element = GetElement(elementColumn, topRow, labels);
- if (element != null) {
- elements.Add(element);
- } else {
- //right edge of the collection reached
- break;
- }
- }
- return (elements, labels.Count);
- }
- JObject GetElement(DevExpress.Spreadsheet.Column elementColumn, int topRow, List<string> labels) {
- JObject element = null;
- var properties = GetColumnValuesUntilEmpty(elementColumn, topRow);
- for (int i = 0; i < properties.Count; i++) {
- element = element ?? new JObject();
- element[labels[i]] = properties[i];
- }
- return element;
- }
- List<string> GetColumnValuesUntilEmpty(DevExpress.Spreadsheet.Column column, int topRow) {
- List<string> labels = new List<string>();
- for (int row = topRow; ; row++) {
- var value = column[row].Value;
- if (value.IsEmpty) {
- //bottom reached
- break;
- } else {
- labels.Add(value.ToString());
- }
- }
- return labels;
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement