/// <summary>
/// Returns an IEnumerable which exposes an Enumerator for XmlDataObjects
/// for each specified Element in its XPath
/// </summary>
/// <param name="ElementList">Location of the Xml file</param>
public static IEnumerable <dynamic> SingleDocument(List <XElement> ElementList)
{
// Cycle thru child elements and return a populated Data Object
foreach (XElement xelement in ElementList)
{
yield return(XmlDataObjectBuilder.EmitDataObject(xelement));
}
}
/// <summary>
/// Returns interface to an Enumerator which traverses the Cartesian Product of one-to-many lists of XElements.
/// Each array of data contains a row of dynamic objects with one XElement from each of the lists of XElements.
/// </summary>
/// <param name="ListOfListsOfNodes">The List of Lists of Elements</param>
public static IEnumerable <dynamic[]> MultipleDocuments(List <List <XElement> > ListOfListsOfNodes)
{
IEnumerable <XElement[]> cartesianProduct = MultipleListEnumerable.CartesianYield <XElement>(ListOfListsOfNodes);
foreach (XElement[] output in cartesianProduct)
{
yield return(output.Select <XElement, dynamic>(x => XmlDataObjectBuilder.EmitDataObject(x)).ToArray());
}
}
/// <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);
}
