/// <summary> /// Complements the AsList(this List<XmlDataObject>) extension method - forces single /// elements into a List so we can make the assumption that we're always dealing with lists. /// </summary> public static List <XmlDataObject> AsList(XmlDataObject Subject) { return(new List <XmlDataObject>() { Subject }); }
/// <summary> /// Takes sequence (or solitary member) of Elements and dumps the values into string List /// </summary> public static List <string> ElementValuesToStringList(dynamic ChildElements) { List <XmlDataObject> ListOfElements = XmlDataObject.AsList(ChildElements); List <string> retval = new List <string>(); foreach (dynamic Element in XmlDataObject.AsList(ChildElements)) { retval.Add(Element.Value); } return(retval.Cast <string>().ToList <string>()); }
/// <summary> /// Translates the Element and all of its children into a tree structure /// using nested instances of XmlDataObject's. /// </summary> public static dynamic EmitDataObject(XElement Element) { // Remove all of the namespaces - overwrite local reference to original Element Element = XmlDataObjectBuilder.RemoveAllNamespaces(Element); // Initialize the return value XmlDataObject DataObject = new XmlDataObject(Element.Name.ToString()); // Step 1 - process all of the attributes foreach (XAttribute Attribute in Element.Attributes()) { DataObject.TrySetMember(Attribute.Name.ToString(), Attribute.Value); } // Step 2 - is this an Element without children if (Element.Elements().Count <XElement>() == 0) { DataObject.TrySetMember("Value", Element.Value); return(DataObject); } // Step 3 - there are child Elements, so we'll cycle through each child Element and process foreach (string ElementName in UniqueElementNames(Element)) { // Is this Element one among many child Elements with the same name? List <XElement> ChildElementList = Element.Elements().Where <XElement>(x => x.Name.ToString() == ElementName).ToList <XElement>(); if (ChildElementList.Count > 1) { // There are child Elements with the same name. So, we'll return a List for the Elements. List <XmlDataObject> ChildElementToObjectList = new List <XmlDataObject>(); foreach (XElement ChildElement in ChildElementList) { // Recursively call EmitDataObject on Child Elements having same name ChildElementToObjectList.Add(EmitDataObject(ChildElement)); } DataObject.TrySetMember(ElementName, ChildElementToObjectList); } else { // Unique Element name. Therefore, recursively call EmitDataObject on just this Element. DataObject.TrySetMember(ElementName, EmitDataObject(Element.Elements().Single <XElement>(x => x.Name == ElementName))); } } return(DataObject); }
/// <summary> /// Method for finding an immediate child element that has an attribute with a certain value. /// </summary> /// <param name="ChildElements">Either an XmlDataObject or a List of XmlDataObjects</param> /// <param name="AttributeName">Name of Xml attribute we're querying</param> /// <param name="AttributeValue">Expect value of the Xml attribute</param> /// <returns>XmlDataObject Element</returns> public static dynamic FindFirstElementByAttribute(dynamic ChildElements, string AttributeName, string AttributeValue) { List <XmlDataObject> ListOfElements = XmlDataObject.AsList(ChildElements); foreach (XmlDataObject Element in ListOfElements) { if (Element.GetMember(AttributeName) as string == AttributeValue) { return(Element); } } // If we have a value type i.e. a leaf, then it obviously won't have attributes return(null); }
/// <summary> /// Complements the AsList(this List<XmlDataObject>) extension method - forces single /// elements into a List so we can make the assumption that we're always dealing with lists. /// </summary> public static List<XmlDataObject> AsList(XmlDataObject Subject) { return new List<XmlDataObject>() { Subject }; }
/// <summary> /// Translates the Element and all of its children into a tree structure /// using nested instances of XmlDataObject's. /// </summary> public static dynamic EmitDataObject(XElement Element) { // Remove all of the namespaces - overwrite local reference to original Element Element = XmlDataObjectBuilder.RemoveAllNamespaces(Element); // Initialize the return value XmlDataObject DataObject = new XmlDataObject(Element.Name.ToString()); // Step 1 - process all of the attributes foreach (XAttribute Attribute in Element.Attributes()) { DataObject.TrySetMember(Attribute.Name.ToString(), Attribute.Value); } // Step 2 - is this an Element without children if (Element.Elements().Count<XElement>() == 0) { DataObject.TrySetMember("Value", Element.Value); return DataObject; } // Step 3 - there are child Elements, so we'll cycle through each child Element and process foreach (string ElementName in UniqueElementNames(Element)) { // Is this Element one among many child Elements with the same name? List<XElement> ChildElementList = Element.Elements().Where<XElement>(x => x.Name.ToString() == ElementName).ToList<XElement>(); if (ChildElementList.Count > 1) { // There are child Elements with the same name. So, we'll return a List for the Elements. List<XmlDataObject> ChildElementToObjectList = new List<XmlDataObject>(); foreach (XElement ChildElement in ChildElementList) { // Recursively call EmitDataObject on Child Elements having same name ChildElementToObjectList.Add(EmitDataObject(ChildElement)); } DataObject.TrySetMember(ElementName, ChildElementToObjectList); } else { // Unique Element name. Therefore, recursively call EmitDataObject on just this Element. DataObject.TrySetMember(ElementName, EmitDataObject(Element.Elements().Single<XElement>(x => x.Name == ElementName))); } } return DataObject; }
/// <param name="XlsxFilePath">Location of Excel 2007 or newer file</param> /// <param name="WorksheetName">Worksheet to be used as Data Source</param> /// <returns>DataTable loaded with Excel data</returns> public static Ado::DataTable Build(string XlsxFilePath, string WorksheetName) { // Unzip the files string UnzipDirectory = TargetUnzipDirectory(XlsxFilePath); CleanDirectory(UnzipDirectory); UnzipFile(XlsxFilePath, UnzipDirectory); // Create the file paths string SharedStringFilePath = UnzipDirectory + @"\xl\sharedStrings.xml"; string WorkbookFilePath = UnzipDirectory + @"\xl\workbook.xml"; string WorkSheetFilePath; // Load the Workbook and get the sheet name dynamic Workbook = XmlDataObjectBuilder.EmitDataObject(WorkbookFilePath); dynamic sheet = XmlDataObjectUtility.FindFirstElementByAttribute(Workbook.sheets.sheet, "name", WorksheetName); if (sheet == null) { throw new ArgumentException("Unable to locate Worksheet: " + WorksheetName); } else { WorkSheetFilePath = UnzipDirectory + @"\xl\worksheets\sheet" + sheet.id.Replace("rId", "") + ".xml"; } // Load the Worksheet into an XmlDataObject dynamic Worksheet = XmlDataObjectBuilder.EmitDataObject(WorkSheetFilePath); // Build the Shared String Table dynamic StringTable = XmlDataObjectBuilder.EmitDataObject(SharedStringFilePath); List <string> StringTableList = new List <string>(); foreach (dynamic si in StringTable.si) { StringTableList.Add(si.t.Value); } // Create Data Table and load Columns from first row Ado::DataTable OutputDataTable = new Ado::DataTable(); dynamic WorksheetRows = XmlDataObject.AsList(Worksheet.sheetData.row); foreach (dynamic ColumnName in ExtractWorksheetRow(StringTableList, WorksheetRows[0])) { OutputDataTable.Columns.Add(ColumnName); } // Load the Rows for (int WorksheetRowIndex = 1; WorksheetRowIndex < WorksheetRows.Count; WorksheetRowIndex++) { Ado::DataRow NewRow = OutputDataTable.NewRow(); List <string> WorksheetRow = ExtractWorksheetRow(StringTableList, WorksheetRows[WorksheetRowIndex]); for (int Column = 0; Column < WorksheetRow.Count; Column++) { NewRow[Column] = WorksheetRow[Column]; } OutputDataTable.Rows.Add(NewRow); } // Clean up CleanDirectory(UnzipDirectory); return(OutputDataTable); }