public void Test_ReadSheet_Data(int firstRow, int?lastRow, int?expectedCount)
{
var service = GetService();
DataFileReaderSettings settings;
using (Stream stream = SheetTestHelper.GetStream("TestSheet.xlsx"))
{
settings = SheetTestHelper.GetSettings(stream, "TestSheet");
settings.FirstDataRowNumber = 10;
settings.LastDataRowNumber = lastRow;
}
using (Stream stream = SheetTestHelper.GetStream("TestSheet.xlsx"))
using (IObjectsReader reader = service.OpenDataFile(stream, settings))
{
int count = 0;
foreach (IObjectReader obj in reader.GetObjects( ))
{
string column = "A";
string dataType = obj.GetString(column);
Assert.That(dataType, Is.Not.Null.Or.Empty);
count++;
if (count == 20)
{
break; // that's enough
}
}
Assert.That(count, Is.GreaterThan(0));
if (expectedCount != null)
{
Assert.That(count, Is.EqualTo(expectedCount.Value));
}
}
}
public void Test_ReadSheet_BlankRows(string sheet, int?lastRow, int expected)
{
var service = GetService( );
DataFileReaderSettings settings;
using (Stream stream = SheetTestHelper.GetStream("Test File.xlsx"))
{
settings = SheetTestHelper.GetSettings(stream, sheet);
settings.LastDataRowNumber = lastRow;
}
using (Stream stream = SheetTestHelper.GetStream("Test File.xlsx"))
using (IObjectsReader reader = service.OpenDataFile(stream, settings))
{
int count = reader.GetObjects( ).Count( );
Assert.That(count, Is.EqualTo(expected));
}
}
public void Test_Scenarios(int rowNum)
{
var service = GetService( );
DataFileReaderSettings settings;
using (Stream stream = SheetTestHelper.GetStream("TestSheet.xlsx")) // IMPORTANT: Ensure TestRowNumbers has the right number of rows
{
settings = SheetTestHelper.GetSettings(stream, "TestSheet");
settings.FirstDataRowNumber = rowNum;
}
using (Stream stream = SheetTestHelper.GetStream("TestSheet.xlsx"))
using (IObjectsReader reader = service.OpenDataFile(stream, settings))
{
IObjectReader obj = reader.GetObjects().First();
string actualColRef = "D";
string[] expectedColumns = { "E", "G", "I", "K", "M", "O", "Q" };
foreach (string expectedColumn in expectedColumns)
{
string expectedNative = obj.GetString(expectedColumn);
if (string.IsNullOrEmpty(expectedNative))
{
continue;
}
switch (expectedColumn)
{
case "E": // String
string actualString = obj.GetString(actualColRef);
string actualSingleLine = StringHelpers.ToSingleLine(actualString);
Assert.That(actualSingleLine, Is.EqualTo(expectedNative));
break;
case "G": // Number
int?actualNumber = obj.GetInt(actualColRef);
int expectedNumber = int.Parse(expectedNative, CultureInfo.InvariantCulture);
Assert.That(actualNumber, Is.EqualTo(expectedNumber));
break;
case "I": // Decimal
decimal?actualDecimal = obj.GetDecimal(actualColRef);
decimal expectedDecimal = decimal.Parse(expectedNative, CultureInfo.InvariantCulture);
Assert.That(actualDecimal, Is.EqualTo(expectedDecimal));
break;
case "K": // Boolean
bool?actualBool = obj.GetBoolean(actualColRef);
bool expectedBool = expectedNative == "Yes" || expectedNative != "No" && bool.Parse(expectedNative);
Assert.That(actualBool, Is.EqualTo(expectedBool));
break;
case "M": // DateTime
DateTime?actualDateTime = obj.GetDateTime(actualColRef);
DateTime expectedDateTime = DateTime.Parse(expectedNative, CultureInfo.InvariantCulture, DateTimeStyles.AssumeUniversal | DateTimeStyles.AdjustToUniversal);
Assert.That(actualDateTime, Is.EqualTo(expectedDateTime));
break;
case "O": // Date
DateTime?actualDate = obj.GetDate(actualColRef);
DateTime expectedDate = DateTime.Parse(expectedNative, CultureInfo.InvariantCulture, DateTimeStyles.AssumeUniversal | DateTimeStyles.AdjustToUniversal);
Assert.That(actualDate, Is.EqualTo(expectedDate));
break;
case "Q": // Time
DateTime?actualTime = obj.GetTime(actualColRef);
DateTime expectedTime = DateTime.Parse(expectedNative, CultureInfo.InvariantCulture, DateTimeStyles.AssumeUniversal | DateTimeStyles.AdjustToUniversal);
Assert.That(actualTime, Is.EqualTo(expectedTime));
break;
}
}
}
}