/// <summary>
/// Extracts a DataTable from the ExcelWorksheet.
/// </summary>
/// <param name="worksheet">The ExcelWorksheet.</param>
/// <param name="hasHeaderRow">Indicates whether worksheet has a header row or not.</param>
/// <returns></returns>
public static DataTable ToDataTable(this ExcelWorksheet worksheet, bool hasHeaderRow = true)
{
ExcelAddress dataBounds = worksheet.GetDataBounds(hasHeaderRow);
var dataTable = new DataTable(worksheet.Name);
if (dataBounds == null)
{
return(dataTable);
}
IEnumerable <DataColumn> columns = worksheet.AsExcelTable(hasHeaderRow).Columns.Select(x => new DataColumn(!hasHeaderRow ? "Column" + x.Id : x.Name));
dataTable.Columns.AddRange(columns.ToArray());
for (int rowIndex = dataBounds.Start.Row; rowIndex <= dataBounds.End.Row; ++rowIndex)
{
ExcelRangeBase[] inputRow = worksheet.Cells[rowIndex, dataBounds.Start.Column, rowIndex, dataBounds.End.Column].ToArray();
DataRow row = dataTable.Rows.Add();
for (var j = 0; j < inputRow.Length; ++j)
{
row[j] = inputRow[j].Value;
}
}
return(dataTable);
}
/// <summary>
/// Extracts an ExcelTable from given ExcelWorkSheet
/// </summary>
/// <param name="worksheet"></param>
/// <param name="hasHeaderRow"></param>
/// <returns></returns>
public static ExcelTable AsExcelTable(this ExcelWorksheet worksheet, bool hasHeaderRow = true)
{
// Table names should be unique
string tableName = $"Table{new Random(Guid.NewGuid().GetHashCode()).Next(99999)}";
return(worksheet.AsExcelTable(tableName, hasHeaderRow));
}
/// <summary>
/// Converts the worksheet into list of objects as enumerable
/// </summary>
/// <typeparam name="T">Type of object</typeparam>
/// <param name="worksheet"></param>
/// <param name="configurationAction"></param>
/// <returns></returns>
public static IEnumerable <T> AsEnumerable <T>(this ExcelWorksheet worksheet, Action <ExcelReadConfiguration <T> > configurationAction = null) where T : class, new()
{
ExcelReadConfiguration <T> configuration = DefaultExcelReadConfiguration <T> .Instance;
configurationAction?.Invoke(configuration);
return(worksheet.AsExcelTable(configuration.HasHeaderRow).AsEnumerable(configurationAction));
}
public void Should_get_as_Excel_table_with_headers()
{
//-----------------------------------------------------------------------------------------------------------
// Arrange
//-----------------------------------------------------------------------------------------------------------
ExcelWorksheet excelWorksheet = excelPackage1.GetWorksheet("TEST5");
//-----------------------------------------------------------------------------------------------------------
// Act
//-----------------------------------------------------------------------------------------------------------
ExcelTable excelTable = excelWorksheet.AsExcelTable();
//-----------------------------------------------------------------------------------------------------------
// Assert
//-----------------------------------------------------------------------------------------------------------
List <StocksNullable> listOfStocks = excelTable.ToList <StocksNullable>();
listOfStocks.Count.Should().Be(3);
}
public void Test_GetAsExcelTable_Without_Header()
{
//-----------------------------------------------------------------------------------------------------------
// Arrange
//-----------------------------------------------------------------------------------------------------------
ExcelWorksheet excelWorksheet = excelPackage.Workbook.Worksheets["TEST5"];
//-----------------------------------------------------------------------------------------------------------
// Act
//-----------------------------------------------------------------------------------------------------------
ExcelTable excelTable = excelWorksheet.AsExcelTable(false);
//-----------------------------------------------------------------------------------------------------------
// Assert
//-----------------------------------------------------------------------------------------------------------
List <StocksNullable> listOfStocks = excelTable.ToList <StocksNullable>(null, configuration => { configuration.SkipCastingErrors = true; });
listOfStocks.Count.Should().Be(4);
}
public void Test_GetAsExcelTable_With_Header()
{
//-----------------------------------------------------------------------------------------------------------
// Arrange
//-----------------------------------------------------------------------------------------------------------
ExcelWorksheet excelWorksheet = excelPackage.Workbook.Worksheets["TEST5"];
//-----------------------------------------------------------------------------------------------------------
// Act
//-----------------------------------------------------------------------------------------------------------
ExcelTable excelTable = excelWorksheet.AsExcelTable();
//-----------------------------------------------------------------------------------------------------------
// Assert
//-----------------------------------------------------------------------------------------------------------
List <StocksNullable> listOfStocks = excelTable.ToList <StocksNullable>();
listOfStocks.Count.Should().Be(3);
}
public void Should_get_as_Excel_table_without_headers()
{
//-----------------------------------------------------------------------------------------------------------
// Arrange
//-----------------------------------------------------------------------------------------------------------
ExcelWorksheet excelWorksheet = excelPackage1.GetWorksheet("TEST5");
//-----------------------------------------------------------------------------------------------------------
// Act
//-----------------------------------------------------------------------------------------------------------
ExcelTable excelTable = excelWorksheet.AsExcelTable(false);
//-----------------------------------------------------------------------------------------------------------
// Assert
//-----------------------------------------------------------------------------------------------------------
List <StocksNullable> listOfStocks =
excelTable.ToList <StocksNullable>(configuration => configuration.SkipCastingErrors());
listOfStocks.Count.Should().Be(4);
}
/// <summary>
/// Creates an Excel table using the data bounds of the worksheet.
/// </summary>
/// <param name="worksheet"></param>
/// <param name="hasHeaderRow"></param>
/// <returns>ExcelTable</returns>
public static ExcelTable AsExcelTable(this ExcelWorksheet worksheet, bool hasHeaderRow = true)
{
return(worksheet.AsExcelTable(StringHelper.GenerateRandomTableName(), hasHeaderRow));
}
/// <summary>
/// Generic extension method yielding objects of specified type from the ExcelWorksheet
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="worksheet"></param>
/// <param name="hasHeaderRow"></param>
/// <param name="skipCastErrors"></param>
/// <returns></returns>
public static IEnumerable <T> AsEnumerable <T>(this ExcelWorksheet worksheet, bool skipCastErrors = false, bool hasHeaderRow = true) where T : class, new()
{
return(worksheet.AsExcelTable <T>(hasHeaderRow).AsEnumerable <T>(skipCastErrors));
}