Working on RML Module..

1 files changed, 104 insertions, 0 deletions

diff --git a/Software/Visual_Studio/Tango.Documents/ExcelWriter.cs b/Software/Visual_Studio/Tango.Documents/ExcelWriter.cs
new file mode 100644
index 000000000..613bbd90f
--- /dev/null
+++ b/Software/Visual_Studio/Tango.Documents/ExcelWriter.cs
@@ -0,0 +1,104 @@
+using DocumentFormat.OpenXml.Packaging;
+using DocumentFormat.OpenXml.Spreadsheet;
+using System;
+using System.Collections.Generic;
+using System.IO;
+using System.Linq;
+using System.Reflection;
+using System.Text;
+using System.Text.RegularExpressions;
+using System.Threading.Tasks;
+
+namespace Tango.Documents
+{
+    public class ExcelWriter : IDisposable
+    {
+        private Stream _stream;
+        private SpreadsheetDocument _document;
+        private List<DefinedName> _column_names;
+
+        public ExcelWriter(Stream source)
+        {
+            _stream = source;
+            _document = SpreadsheetDocument.Open(_stream, true);
+            _column_names = new List<DefinedName>();
+
+            if (_document.WorkbookPart.Workbook.GetFirstChild<DefinedNames>() != null)
+            {
+                foreach (DefinedName name in _document.WorkbookPart.Workbook.GetFirstChild<DefinedNames>())
+                {
+                    _column_names.Add(name);
+                }
+            }
+        }
+
+        public ExcelWriter(String fileName) : this(new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite))
+        {
+
+        }
+
+        public void WriteData<T>(IEnumerable<T> data, String sheetName)
+        {
+            var props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance).ToList();
+
+            WorksheetPart work_sheet_part = GetWorkSheetPart(sheetName);
+            Worksheet workSheet = work_sheet_part.Worksheet;
+            SheetData sheetData = workSheet.GetFirstChild<SheetData>();
+            List<Row> rows = sheetData.Elements<Row>().ToList();
+
+            var list = data.ToList();
+
+            for (int i = 0; i < list.Count; i++)
+            {
+                var item = list[i];
+
+                Row newRow = new Row();
+
+                sheetData.InsertAfter(newRow, sheetData.Elements<Row>().Last());
+
+                foreach (var prop in props)
+                {
+                    var definedName = _column_names.SingleOrDefault(x => x.Name == prop.Name);
+
+                    if (definedName != null)
+                    {
+                        SetCellRow(newRow, GetCellReference(definedName, i + 2), prop.GetValue(item));
+                    }
+                }
+            }
+        }
+
+        private void SetCellRow(Row row, string cellReference, object value)
+        {
+            Cell cell = row.Descendants<Cell>().FirstOrDefault(c => c.CellReference == cellReference);
+            if (cell == null)
+            {
+                cell = new Cell();
+                cell.CellReference = cellReference;
+            }
+            cell.CellValue = new CellValue(value.ToString());
+            cell.DataType = CellValues.String;
+            row.Append(cell);
+        }
+
+        private WorksheetPart GetWorkSheetPart(String sheetName)
+        {
+            var sheet = _document.WorkbookPart.Workbook.Descendants<Sheet>().FirstOrDefault(x => x.Name.Value == sheetName);
+            string relId = sheet.Id;
+            return (WorksheetPart)_document.WorkbookPart.GetPartById(relId);
+        }
+
+        private string GetCellReference(DefinedName name, int rowIndex)
+        {
+            Regex rowPatern = new Regex("^.*\\!\\$(.*)\\$\\d*$");
+            Match match = rowPatern.Match(name.Text);
+            return match.Groups[1].Value + rowIndex.ToString();
+        }
+
+        public void Dispose()
+        {
+            _document.Save();
+            _stream.Dispose();
+        }
+    }
+}