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