Modified Tech_IOS according to Avidan spec.

Added Tango.Documents library.
Implemented Ports List Generator.

1 files changed, 427 insertions, 0 deletions

diff --git a/Software/Visual_Studio/Tango.Documents/ExcelReader.cs b/Software/Visual_Studio/Tango.Documents/ExcelReader.cs
new file mode 100644
index 000000000..b212678ae
--- /dev/null
+++ b/Software/Visual_Studio/Tango.Documents/ExcelReader.cs
@@ -0,0 +1,427 @@
+using DocumentFormat.OpenXml.Packaging;
+using DocumentFormat.OpenXml.Spreadsheet;
+using System;
+using System.Collections.Generic;
+using System.Diagnostics;
+using System.Globalization;
+using System.IO;
+using System.Linq;
+using System.Reflection;
+using System.Text.RegularExpressions;
+
+namespace Tango.Documents
+{
+    /// <summary>
+    /// Represents a class for reading excel document rows by the specified model.
+    /// </summary>
+    public class ExcelReader : IDisposable
+    {
+        private SpreadsheetDocument document; //Will contain the Excel document.
+        private List<DefinedName> names; //Will contain the collection of Excel defined names.
+        private bool throwError; //Determines whether to throw exceptions while parsing the Excel file. 
+        private string sheetN; //Will contain the specified Excel sheet name.
+        private Stream fileStream; //Will contain the Excel file stream.
+
+        #region Constructors
+
+        /// <summary>
+        /// Initializes a new instance of the ExcelReader.
+        /// </summary>
+        /// <param name="source">Document Stream.</param>
+        /// <param name="throwOnError">Throw exceptions if encountering an error while parsing the document.</param>
+        public ExcelReader(Stream source, bool throwOnError = true)
+        {
+            sheetN = String.Empty;
+            fileStream = source;
+            document = SpreadsheetDocument.Open(source, false);
+            names = new List<DefinedName>();
+            if (document.WorkbookPart.Workbook.GetFirstChild<DefinedNames>() != null)
+            {
+                foreach (DefinedName name in document.WorkbookPart.Workbook.GetFirstChild<DefinedNames>())
+                    names.Add(name);
+            }
+            throwError = throwOnError;
+        }
+
+        /// <summary>
+        /// Initializes a new instance of ExcelReader.
+        /// </summary>
+        /// <param name="templatePath">File path to the Excel document.</param>
+        /// <param name="throwOnError">Throw exceptions if encountering an error while parsing the document.</param>
+        public ExcelReader(string templatePath, bool throwOnError = true)
+            : this(File.OpenRead(templatePath), throwOnError) { }
+
+
+        #endregion
+
+        #region Public Methods
+
+        public List<T> GetDataByIndex<T>(String sheetName, int firstRowIndex)
+        {
+            sheetN = sheetName;
+
+            List<T> results = new List<T>();
+
+            Worksheet currWorksheet = GetWorkSheetPart().Worksheet;
+            int rowCount = currWorksheet.Descendants<Row>().Count();
+
+            PropertyInfo[] properties = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
+
+            for (int rowIndex = firstRowIndex; rowIndex < rowCount; rowIndex++)
+            {
+                Row row = GetRow(currWorksheet, rowIndex);
+                if (row == null)
+                    break;
+
+                T newObj = (T)Activator.CreateInstance<T>();
+
+                List<Cell> cells = GetRowCells(row).ToList();
+
+                for (int cellIndex = 0; cellIndex < Math.Min(properties.Count(), cells.Count); cellIndex++)
+                {
+                    Cell cell = cells[cellIndex] as Cell;
+                    String value = CellValue(cell);
+
+                    properties[cellIndex].SetValue(newObj, value);
+                }
+
+                results.Add(newObj);
+            }
+
+            return results;
+        }
+
+        /// <summary>
+        /// Parse the document and populate the specified model collection using reflections.
+        /// </summary>
+        /// <typeparam name="T">Type of model.</typeparam>
+        /// <param name="sheetName">Document Sheet name.</param>
+        /// <returns>Collection of models reflecting the parsed rows.</returns>
+        public List<T> GetData<T>(String sheetName)
+        {
+            sheetN = sheetName;
+
+            List<T> ret = new List<T>();
+
+            PropertyInfo[] PropColl = typeof(T).GetProperties();
+            names = (from pr in PropColl
+                     join n in names
+                    on pr.Name equals n.Name.ToString()
+                     select n).ToList();
+
+            if (this.names.Any())
+            {
+                int Rowindex = GetFirstRowNumber() + 1;
+                Worksheet currWorksheet = GetWorkSheetPart().Worksheet;
+                int rowCount = currWorksheet.Descendants<Row>().Count();
+                while (Rowindex <= rowCount)
+                {
+                    string a = "";
+                    try
+                    {
+                        Row curr = GetRow(currWorksheet, Rowindex);
+                        if (curr == null)
+                            break;
+
+                        bool isEmptyRow = true;
+                        T newObj = (T)Activator.CreateInstance<T>();
+
+                        foreach (DefinedName name in names)
+                        {
+                            Stopwatch watchCol = new Stopwatch();
+                            watchCol.Start();
+
+
+                            var t = curr.ElementAtOrDefault(12);
+
+                            Cell theCell =
+                                curr.Descendants<Cell>().Where(c => c.CellReference == GetColumn(name) + Rowindex.ToString()).FirstOrDefault();
+                            PropertyInfo Prop = PropColl.FirstOrDefault(pr => pr.Name == name.Name);
+
+                            a = CellValue(theCell);
+                            if (a != null)
+                            {
+                                isEmptyRow = false;
+                                SetPropValue(a, newObj, Prop);
+                            }
+                        }
+                        if (isEmptyRow)
+                            break;
+                        ret.Add(newObj);
+                    }
+                    catch (Exception ex)
+                    {
+                        if (throwError)
+                        {
+                            throw ex;
+                        }
+                    }
+                    Rowindex++;
+                }
+            }
+            return ret.ToList();
+        }
+
+        /// <summary>
+        /// Disposes the document stream.
+        /// </summary>
+        public void Close()
+        {
+            fileStream.Close();
+            fileStream.Dispose();
+        }
+
+        #endregion
+
+        #region Private Methods
+
+        ///<summary>returns an empty cell when a blank cell is encountered
+        ///</summary>
+        public static IEnumerable<Cell> GetRowCells(Row row)
+        {
+            int currentCount = 0;
+
+            foreach (Cell cell in row.Descendants<Cell>())
+            {
+                string columnName = GetColumnName(cell.CellReference);
+
+                int currentColumnIndex = ConvertColumnNameToNumber(columnName);
+
+                for (; currentCount < currentColumnIndex; currentCount++)
+                {
+                    yield return new DocumentFormat.OpenXml.Spreadsheet.Cell();
+                }
+
+                yield return cell;
+                currentCount++;
+            }
+        }
+
+        /// <summary>
+        /// Given a cell name, parses the specified cell to get the column name.
+        /// </summary>
+        /// <param name="cellReference">Address of the cell (ie. B2)</param>
+        /// <returns>Column Name (ie. B)</returns>
+        public static string GetColumnName(string cellReference)
+        {
+            // Match the column name portion of the cell name.
+            var regex = new System.Text.RegularExpressions.Regex("[A-Za-z]+");
+            var match = regex.Match(cellReference);
+
+            return match.Value;
+        }
+
+        /// <summary>
+        /// Given just the column name (no row index),
+        /// it will return the zero based column index.
+        /// </summary>
+        /// <param name="columnName">Column Name (ie. A or AB)</param>
+        /// <returns>Zero based index if the conversion was successful</returns>
+        /// <exception cref="ArgumentException">thrown if the given string
+        /// contains characters other than uppercase letters</exception>
+        public static int ConvertColumnNameToNumber(string columnName)
+        {
+            var alpha = new System.Text.RegularExpressions.Regex("^[A-Z]+$");
+            if (!alpha.IsMatch(columnName)) throw new ArgumentException();
+
+            char[] colLetters = columnName.ToCharArray();
+            Array.Reverse(colLetters);
+
+            int convertedValue = 0;
+            for (int i = 0; i < colLetters.Length; i++)
+            {
+                char letter = colLetters[i];
+                int current = i == 0 ? letter - 65 : letter - 64; // ASCII 'A' = 65
+                convertedValue += current * (int)Math.Pow(26, i);
+            }
+
+            return convertedValue;
+        }
+
+        /// <summary>
+        /// Cells the value.
+        /// </summary>
+        /// <param name="cell">The cell.</param>
+        /// <returns></returns>
+        private string CellValue(Cell cell)
+        {
+            if (cell == null) return null;
+            string value = (cell.CellFormula == null) ? cell.InnerText : cell.CellValue.InnerText;
+            if (String.IsNullOrEmpty(value)) return null;
+
+            if (cell.DataType != null)
+            {
+                switch (cell.DataType.Value)
+                {
+                    case CellValues.SharedString:
+                        var stringTable = document.WorkbookPart.SharedStringTablePart;
+                        if (stringTable != null)
+                        {
+                            value = stringTable.SharedStringTable.
+                                ElementAt(int.Parse(value)).InnerText;
+                        }
+                        break;
+
+                    case CellValues.Boolean:
+                        switch (value)
+                        {
+                            case "0":
+                                value = "FALSE";
+                                break;
+                            default:
+                                value = "TRUE";
+                                break;
+                        }
+                        break;
+                }
+            }
+            return value;
+        }
+
+        /// <summary>
+        /// Determines whether [is empty row] [the specified row].
+        /// </summary>
+        /// <param name="row">The row.</param>
+        /// <returns></returns>
+        private bool IsEmptyRow(Row row)
+        {
+            Worksheet currWorksheet = GetWorkSheetPart().Worksheet;
+            foreach (DefinedName name in names)
+            {
+                Cell theCell = row.Descendants<Cell>().
+                    Where(c => c.CellReference == GetColumn(name) + row.RowIndex.ToString()).FirstOrDefault();
+
+                if (theCell == null || theCell.CellValue == null)
+                    continue;
+                if (!string.IsNullOrEmpty(theCell.CellValue.Text))
+                    return false;
+            }
+            return true;
+        }
+
+        /// <summary>
+        /// Gets the first row number.
+        /// </summary>
+        /// <returns></returns>
+        private int GetFirstRowNumber()
+        {
+            Regex rowPatern = new Regex("^.*\\!\\$.*\\$(\\d*)$");
+            Match match = rowPatern.Match(names[0].Text);
+            return Convert.ToInt32(match.Groups[1].Value);
+        }
+
+        /// <summary>
+        /// Gets the column.
+        /// </summary>
+        /// <param name="name">The name.</param>
+        /// <returns></returns>
+        private string GetColumn(DefinedName name)
+        {
+            Regex rowPatern = new Regex("^.*\\!\\$(.*)\\$\\d*$");
+            Match match = rowPatern.Match(name.Text);
+            return match.Groups[1].Value;
+        }
+
+        /// <summary>
+        /// Gets the row.
+        /// </summary>
+        /// <param name="worksheet">The worksheet.</param>
+        /// <param name="rowIndex">Index of the row.</param>
+        /// <returns></returns>
+        private Row GetRow(Worksheet worksheet, int rowIndex)
+        {
+            var row = worksheet.GetFirstChild<SheetData>().
+              Elements<Row>().Where(r => r.RowIndex == rowIndex).FirstOrDefault();
+            return row;
+        }
+
+        /// <summary>
+        /// Gets the work sheet part.
+        /// </summary>
+        /// <returns></returns>
+        private WorksheetPart GetWorkSheetPart()
+        {
+            //get worksheet based on defined name 
+            var sheet = document.WorkbookPart.Workbook.Descendants<Sheet>().FirstOrDefault(x => x.Name.Value == sheetN);
+            string relId = sheet.Id;
+
+            return (WorksheetPart)document.WorkbookPart.GetPartById(relId);
+        }
+
+
+        /// <summary>
+        /// Gets the name of the sheet.
+        /// </summary>
+        /// <returns></returns>
+        private string GetSheetName()
+        {
+            Regex rowPatern = new Regex("^(.*)\\!\\$.*\\$\\d*$");
+            Match match = rowPatern.Match(names[0].Text);
+            return match.Groups[1].Value;
+        }
+
+        /// <summary>
+        /// Sets the property value.
+        /// </summary>
+        /// <typeparam name="T"></typeparam>
+        /// <param name="val">The value.</param>
+        /// <param name="obj">The object.</param>
+        /// <param name="prop">The property.</param>
+        private static void SetPropValue<T>(object val, T obj, PropertyInfo prop)
+        {
+            try
+            {
+                Type PType = prop.PropertyType;
+                if (val == null)
+                    prop.SetValue(obj, null, null);
+                else if (PType == typeof(int) || PType == typeof(int?))
+                    prop.SetValue(obj, Convert.ToInt32(val), null);
+                else if (PType == typeof(Int16) || PType == typeof(Int16?))
+                    prop.SetValue(obj, Convert.ToInt16(val), null);
+                else if (PType == typeof(Int64) || PType == typeof(Int64?))
+                    prop.SetValue(obj, Convert.ToInt64(val), null);
+                else if (PType == typeof(Double) || PType == typeof(Double?))
+                {
+                    double? tDouble = null;
+                    if (val.ToString().ToLower().Contains("e"))
+                        tDouble = double.Parse(val.ToString(), NumberStyles.Any);
+                    else
+                        tDouble = Convert.ToDouble(val);
+                    prop.SetValue(obj, tDouble, null);
+                }
+                else if (PType == typeof(decimal) || PType == typeof(decimal?))
+                {
+                    decimal? tDecimal = null;
+                    if (val.ToString().ToLower().Contains("e"))
+                        tDecimal = decimal.Parse(val.ToString(), NumberStyles.Any);
+                    else
+                        tDecimal = Convert.ToDecimal(val);
+                    prop.SetValue(obj, tDecimal, null);
+                }
+                else if (PType == typeof(DateTime) || PType == typeof(DateTime?))
+                    prop.SetValue(obj, Convert.ToDateTime(val), null);
+                else if (PType == typeof(bool) || PType == typeof(bool?))
+                    prop.SetValue(obj, Convert.ToBoolean(val), null);
+                else if (PType == typeof(byte) || PType == typeof(byte?))
+                    prop.SetValue(obj, Convert.ToByte(val), null);
+                else
+                    prop.SetValue(obj, val, null);
+            }
+            catch (Exception ex)
+            {
+                throw ex;
+            }
+        }
+
+        #endregion
+
+        #region IDisposable
+
+        public void Dispose()
+        {
+            Close();
+        }
+
+        #endregion
+    }
+}
\ No newline at end of file