#4 Get Worksheet as datatable

[Anonymous]

Excel2007

public static DataTable CreateDataTable(string filePath, string sheetName)
{
Workbook w = new Workbook(filePath);
Worksheet ws = w.GetWorksheetByName(sheetName);
DataTable dtResult = new DataTable();
int cols = Convert.ToInt32(ws.CellMap.LastCol - ws.CellMap.FirstCol) + 1;
bool colsDefined = false;
for (uint row = ws.CellMap.FirstRow; row <= ws.CellMap.LastRow; row++)
{
object[] data = new object[cols];
int pos = 0;
for (uint col = ws.CellMap.FirstCol; col <= ws.CellMap.LastCol; col++)
{
if (colsDefined == false)
{
DataColumn dc = dtResult.Columns.Add();
dc.ColumnName = ws.GetRow(row).GetCell(col).Value.ToString();
}
else
{
data[pos] = ws.GetRow(row).GetCell(col).Value;
pos++;

}
}
if (pos > 0)
dtResult.LoadDataRow(data, true);
else
colsDefined = true;
}
return dtResult;
}