/// <summary>
/// Imports file as dictionary
/// </summary>
/// <param name="excelReaderManager">The excel reader manager.</param>
/// <param name="workSheetName">The work sheet name.</param>
/// <param name="includeHeader">The include header.</param>
/// <returns>The file data as list of dictionary.</returns>
static List <Dictionary <string, object> > ImportFileAsDictionary(ExcelReaderManager excelReaderManager, string workSheetName, bool includeHeader = false)
{
List <Dictionary <string, object> > dataList = new List <Dictionary <string, object> >();
Worksheet workSheet = excelReaderManager.GetWorksheet(workSheetName);
if (workSheet == null)
{
throw new ArgumentException("The sheet name can not be found");
}
IEnumerable <Row> rows = excelReaderManager.GetWorkSheetRows(workSheet);
if (rows?.Any() == true)
{
int minRow = includeHeader ? 1 : 2;
for (int iRow = minRow; iRow < rows.Count() + 1; iRow++)
{
var rowValue = excelReaderManager.GetRowValues(workSheet, iRow);
if (rowValue != null)
{
dataList.Add(rowValue);
}
}
}
return(dataList);
}
/// <summary>
/// Imports data from excel file
/// </summary>
/// <param name="fileStream">The file stream.</param>
/// <param name="workSheetName">The work sheet name.</param>
/// <returns>The file data as data table.</returns>
public static DataTable ImportFileAsDataTable(Stream fileStream, string workSheetName)
{
DataTable dataTable = new DataTable();
using (ExcelReaderManager excelReaderManager = new ExcelReaderManager())
{
if (!excelReaderManager.LoadDocument(fileStream))
{
throw new ArgumentException("Unable to open the file");
}
dataTable = ImportFileAsDataTable(excelReaderManager, workSheetName);
}
return(dataTable);
}
/// <summary>
/// Loads data from excel file
/// </summary>
/// <param name="fileStream">The file stream.</param>
/// <param name="workSheetName">The work sheet name.</param>
/// <param name="includeHeader">The include header.</param>
/// <returns>The file data as list of dictionary.</returns>
public static List <Dictionary <string, object> > ImportFileAsDictionary(Stream fileStream, string workSheetName, bool includeHeader = false)
{
List <Dictionary <string, object> > dataList = new List <Dictionary <string, object> >();
using (ExcelReaderManager excelReaderManager = new ExcelReaderManager())
{
if (!excelReaderManager.LoadDocument(fileStream))
{
throw new ArgumentException("Unable to open the file");
}
dataList = ImportFileAsDictionary(excelReaderManager, workSheetName, includeHeader);
}
return(dataList);
}
/// <summary>
/// Imports file as data table.
/// </summary>
/// <param name="excelReaderManager">The excel reader manager.</param>
/// <param name="workSheetName">The work sheet name.</param>
/// <returns>The file data as data table.</returns>
static DataTable ImportFileAsDataTable(ExcelReaderManager excelReaderManager, string workSheetName)
{
DataTable dataTable = new DataTable();
Worksheet workSheet = excelReaderManager.GetWorksheet(workSheetName);
if (workSheet == null)
{
throw new ArgumentException("The sheet name can not be found");
}
IEnumerable <Row> rows = excelReaderManager.GetWorkSheetRows(workSheet);
if (rows?.Any() == true)
{
// Retrieve file columns
foreach (Cell cell in rows.ElementAt(0))
{
var cellValue = excelReaderManager.GetCellValue(cell);
if (cellValue != null)
{
dataTable.Columns.Add(cellValue.ToString());
}
}
// Retrieve file rows
foreach (Row row in rows.Skip(1)) //this will also include your header row...
{
DataRow tempRow = dataTable.NewRow();
for (int i = 0; i < row.Descendants <Cell>().Count(); i++)
{
tempRow[i] = excelReaderManager.GetCellValue(row.Descendants <Cell>().ElementAt(i));
}
dataTable.Rows.Add(tempRow);
}
}
return(dataTable);
}
