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