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