/// <summary> /// Converts a worksheet to a simple DataTable. /// </summary> /// <param name="doc"></param> /// <param name="wsp"></param> /// <returns></returns> protected DataTable WorksheetToDatatable(SpreadsheetDocument doc, WorksheetPart wsp) { //Read the first Sheet from Excel file. // Sheet sheet = doc.WorkbookPart.Workbook.Sheets.GetFirstChild<Sheet>(); //Get the Worksheet instance. //Worksheet worksheet = (doc.WorkbookPart.GetPartById(sheet.Id.Value) as WorksheetPart).Worksheet; Worksheet worksheet = wsp.Worksheet; //Fetch all the rows present in the Worksheet. IEnumerable <Row> rows = worksheet.GetFirstChild <SheetData>().Descendants <Row>(); //Create a new DataTable. DataTable dt = new DataTable(); //Loop through the Worksheet rows. // NOTE: This loop is the longest part of the import process by far. var rowCount = 0; foreach (Row row in rows) { rowCount++; // When we run out of content rows, get out. // There are blank rows within the first 12 rows, hence the 75 threshold. if (rowCount > 75 && row.InnerText == "") { break; } var newRow = dt.Rows.Add(); int colIndex = 0; for (int j = 0; j < row.Descendants <Cell>().Count(); j++) { var cell = row.Descendants <Cell>().ElementAt(j); // lazily add column on the fly, if necessary if (dt.Columns.Count < j + 1) { dt.Columns.Add(ImportManagerAwwa.ColumnIndexToColumnLetter(j + 1)); } newRow[colIndex] = ImportManagerAwwa.GetValue(doc, cell); colIndex++; } } return(dt); }
/// <summary> /// Converts a worksheet to a simple DataTable. /// </summary> /// <param name="doc"></param> /// <param name="wsp"></param> /// <returns></returns> protected DataTable WorksheetToDatatable(SpreadsheetDocument doc, WorksheetPart wsp) { //Read the first Sheet from Excel file. // Sheet sheet = doc.WorkbookPart.Workbook.Sheets.GetFirstChild<Sheet>(); //Get the Worksheet instance. //Worksheet worksheet = (doc.WorkbookPart.GetPartById(sheet.Id.Value) as WorksheetPart).Worksheet; Worksheet worksheet = wsp.Worksheet; //Fetch all the rows present in the Worksheet. IEnumerable <Row> rows = worksheet.GetFirstChild <SheetData>().Descendants <Row>(); //Create a new DataTable. DataTable dt = new DataTable(); //Loop through the Worksheet rows. foreach (Row row in rows) { var newRow = dt.Rows.Add(); int colIndex = 0; for (int j = 0; j < row.Descendants <Cell>().Count(); j++) { var cell = row.Descendants <Cell>().ElementAt(j); // lazily add column on the fly, if necessary if (dt.Columns.Count < j + 1) { dt.Columns.Add(ImportManagerAwwa.ColumnIndexToColumnLetter(j + 1)); } newRow[colIndex] = ImportManagerAwwa.GetValue(doc, cell); colIndex++; } } return(dt); }