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