Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- void Main() {
- var wb = LoadWorkbook(@"D:\Downloads\EU.xlsx");
- var a = ProccessBlock<Premium>(wb.Worksheets["Output"], 50, 1);
- a.Dump();
- }
- T ProccessBlock<T>(Worksheet worksheet, int topRow, int leftColumn) {
- T root = (T)Activator.CreateInstance(typeof(T));
- int row = topRow;
- while (true) {
- string elementName = "";
- int height = 0;
- var value = worksheet[row, leftColumn].Value;
- if (value.IsEmpty) {
- break;
- } else {
- elementName = value.TextValue.RemoveWhiteSpaces();
- if (elementName.EndsWith(CollectionSuffix)) {
- height = ParseToCollection(root, elementName, worksheet, row, leftColumn);
- } else if (elementName.EndsWith(TableSuffix)) {
- height = ParseToTable(root, elementName, worksheet, row, leftColumn);
- } else {
- ParseToSingleElement(root, elementName, worksheet, row, leftColumn);
- height = 1;
- }
- }
- row += height + 1; // +1 for the empty row under the collection
- }
- return root;
- }
- void ParseToSingleElement<T>(T obj, string elementName, Worksheet worksheet, int row, int leftColumn) {
- var property = typeof(T).GetProperty(elementName);
- property.SetValue(obj, worksheet[row, leftColumn + 1].Value.ToString());
- }
- int ParseToTable<T>(T obj, string tableName, Worksheet worksheet, int topRow, int leftColumn) {
- var tableBodyRow = topRow + 1;
- var property = typeof(T).GetProperty(tableName);
- var propertyType = property.PropertyType;
- var propertyValue = Activator.CreateInstance(propertyType);
- //get labels from the area under table header
- var labelsColumn = worksheet.Columns[leftColumn];
- List<CellValue> labels = GetColumnValuesUntilEmpty(labelsColumn, tableBodyRow);
- //get element properties from the column to the right of lables
- var elementColumn = worksheet.Columns[leftColumn + 1];
- propertyValue = GetElement(propertyType, labels, elementColumn, tableBodyRow);
- property.SetValue(obj, propertyValue);
- var totalTableHeight = labels.Count + 1; // +1 for the header of the table
- return totalTableHeight;
- }
- int ParseToCollection<T>(T obj, string collectionName, Worksheet worksheet, int topRow, int leftColumn) {
- var property = typeof(T).GetProperty(collectionName);
- var propertyType = property.PropertyType.GetGenericArguments()[0];
- Type listType = typeof(List<>).MakeGenericType(propertyType);
- var list = (IList)Activator.CreateInstance(listType);
- // get collection elements from the area under the header of the collection
- var elementsHeight = GetCollectionElements(list, propertyType, worksheet, topRow + 1, leftColumn);
- property.SetValue(obj, list);
- var totalCollectionHeight = elementsHeight + 1; // +1 for the header of the collection
- return totalCollectionHeight;
- }
- int GetCollectionElements(IList list, Type type, Worksheet worksheet, int topRow, int leftColumn) {
- var labelsColumn = worksheet.Columns[leftColumn];
- List<CellValue> 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(type, labels, elementColumn, topRow);
- if (element != null) {
- list.Add(element);
- } else {
- //right edge of the collection reached
- break;
- }
- }
- return labels.Count;
- }
- object GetElement(Type type, List<CellValue> labels, DevExpress.Spreadsheet.Column elementColumn, int topRow) {
- var propertyValues = GetColumnValuesUntilEmpty(elementColumn, topRow);
- if(propertyValues.Count == 0){
- return null;
- } else {
- var element = Activator.CreateInstance(type);
- for (int i = 0; i < labels.Count; i++) {
- var property = type.GetProperty(labels[i].TextValue);
- var propertyType = property.PropertyType;
- var propertyValue = Convert.ChangeType(propertyValues[i].ToObject(), propertyType);
- property.SetValue(element, propertyValue);
- }
- return element;
- }
- }
- List<CellValue> GetColumnValuesUntilEmpty(DevExpress.Spreadsheet.Column column, int topRow) {
- var values = new List<CellValue>();
- for (int row = topRow; ; row++) {
- var value = column[row].Value;
- if (value.IsEmpty) {
- //bottom reached
- break;
- } else {
- values.Add(value);
- }
- }
- return values;
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement