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.Dump();
- XmlReader reader = new XmlNodeReader(doc);
- var serializer = new XmlSerializer(typeof(Premium));
- Premium result = (Premium)serializer.Deserialize(reader);
- result.Dump();
- reader.Dispose();
- }
- XmlDocument ProcessBlock(Worksheet worksheet, int topRow, int leftColumn) {
- XmlDocument root = new XmlDocument();
- Func<String, XmlElement> createElement = elementName => root.CreateElement(elementName)
- var premium = root.AppendChild(createElement("Premium"));
- int row = topRow;
- while (true) {
- XmlElement element;
- int height;
- var value = worksheet[row, leftColumn].Value;
- if (value.IsEmpty) {
- break;
- } else {
- var text = value.TextValue.RemoveWhiteSpaces();
- if (text.EndsWith(CollectionSuffix)) {
- (element, height) = ParseToCollection(text, worksheet, row, leftColumn, createElement);
- } else if (text.EndsWith(TableSuffix)) {
- (element, height) = ParseToTable(text, worksheet, row, leftColumn, createElement);
- } else {
- element = ParseToSingleElement(worksheet, text, row, leftColumn, createElement);
- height = 1;
- }
- }
- premium.AppendChild(element);
- row += height + 1; // +1 for the empty row under the collection
- }
- return root;
- }
- XmlElement ParseToSingleElement(Worksheet worksheet, string elementName, int row, int leftColumn, Func<string, XmlElement> createElement) {
- var element = createElement(elementName);
- element.InnerText = worksheet[row, leftColumn + 1].Value.ToString();
- return element;
- }
- (XmlElement table, int tableHeight) ParseToTable(string tableName, Worksheet worksheet, int topRow, int leftColumn, Func<string, XmlElement> createElement) {
- var tableBodyRow = topRow + 1;
- var element = createElement(tableName);
- //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 properties = GetElementProperties(elementColumn, tableBodyRow, i => createElement(labels[i]));
- properties.ForEach(x => element.AppendChild(x));
- var totalTableHeight = properties.Count + 1; // +1 for the header of the table
- return (element, totalTableHeight);
- }
- (XmlElement collection, int collectionHeight) ParseToCollection(string collectionName, Worksheet worksheet, int topRow, int leftColumn, Func<string, XmlElement> createElement) {
- var elementNameLenght = collectionName.Length - CollectionSuffix.Length;
- var elementName = collectionName.Substring(0, elementNameLenght);
- // get collection elements from the area under the header of the collection
- var (elements, elementsHeight) = GetCollectionElements(elementName, worksheet, topRow + 1, leftColumn, createElement);
- var collectionRoot = createElement(collectionName);
- elements.ForEach(x => collectionRoot.AppendChild(x));
- var totalCollectionHeight = elementsHeight + 1; // +1 for the header of the collection
- return (collectionRoot, totalCollectionHeight);
- }
- (List<XmlElement>, int elementsHeight) GetCollectionElements(string elementName, Worksheet worksheet, int topRow, int leftColumn, Func<string, XmlElement> createElement) {
- List<XmlElement> elements = new List<XmlElement>();
- 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 = createElement(elementName);
- var properties = GetElementProperties(elementColumn, topRow, i => createElement(labels[i]));
- if (properties.Count != 0) {
- properties.ForEach(x => element.AppendChild(x));
- elements.Add(element);
- } else {
- //right edge of the collection reached
- break;
- }
- }
- return (elements, labels.Count);
- }
- List<XmlElement> GetElementProperties(DevExpress.Spreadsheet.Column column, int topRow, Func<int, XmlElement> createElement) {
- List<XmlElement> properties = new List<XmlElement>();
- var columnValues = GetColumnValuesUntilEmpty(column, topRow);
- for (int row = 0; row < columnValues.Count; row++) {
- var property = createElement(row);
- property.InnerText = columnValues[row].ToString();
- properties.Add(property);
- }
- return properties;
- }
- 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