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