/// <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> /// 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); }
/// <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); }