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