public void Test_CSV( )
{
string csv =
"TestString,TestNumber\nA,1\nB,2\nC,3\nD,4\nE,5\nA,1\nB,2\nC,3\nD,4\nE,5\n,A,1\nB,2\nC,3\nD,4\nE,5\n";
var service = new CsvFileReaderService( );
var settings = new DataFileReaderSettings( );
SampleDataCreator creator = new SampleDataCreator( );
using (Stream stream = SheetTestHelper.GetCsvStream(csv))
{
SampleTable table = creator.CreateSample(stream, settings, service);
Assert.That(table, Is.Not.Null);
Assert.That(table.Columns, Has.Count.EqualTo(2));
Assert.That(table.Rows, Has.Count.EqualTo(10));
SampleColumn column = table.Columns[0];
Assert.That(column.ColumnName, Is.EqualTo("1"));
Assert.That(column.Name, Is.EqualTo("TestString"));
column = table.Columns[1];
Assert.That(column.ColumnName, Is.EqualTo("2"));
Assert.That(column.Name, Is.EqualTo("TestNumber"));
SampleRow row = table.Rows[0];
Assert.That(row.Values, Has.Count.EqualTo(2));
Assert.That(row.Values[0], Is.EqualTo("A"));
Assert.That(row.Values[1], Is.EqualTo("1"));
row = table.Rows[9];
Assert.That(row.Values, Has.Count.EqualTo(2));
Assert.That(row.Values[0], Is.EqualTo("E"));
Assert.That(row.Values[1], Is.EqualTo("5"));
}
}
public void Test_CSV_HeadingBeyondData( )
{
string csv =
"\n1,2\nA,B";
var service = new CsvFileReaderService( );
var settings = new DataFileReaderSettings( );
settings.FirstDataRowNumber = 2;
settings.HeadingRowNumber = 3;
SampleDataCreator creator = new SampleDataCreator( );
using (Stream stream = SheetTestHelper.GetCsvStream(csv))
{
SampleTable table = creator.CreateSample(stream, settings, service);
Assert.That(table, Is.Not.Null);
Assert.That(table.Columns, Has.Count.EqualTo(2));
Assert.That(table.Rows, Has.Count.EqualTo(0));
SampleColumn column = table.Columns [0];
Assert.That(column.ColumnName, Is.EqualTo("1"));
Assert.That(column.Name, Is.EqualTo("A"));
column = table.Columns [1];
Assert.That(column.ColumnName, Is.EqualTo("2"));
Assert.That(column.Name, Is.EqualTo("B"));
}
}
public void Test_Excel_Test4_AdditionalColumnsFromDataRows( )
{
var service = new ExcelFileReaderService( );
SampleDataCreator creator = new SampleDataCreator( );
using (Stream stream = SheetTestHelper.GetStream("SampleDataTests.xlsx")) // IMPORTANT: Ensure TestRowNumbers has the right number of rows
{
var settings = new DataFileReaderSettings( );
settings.SheetId = SheetTestHelper.GetSheetId("SampleDataTests.xlsx", "Test4");
settings.HeadingRowNumber = 3;
settings.FirstDataRowNumber = 4;
SampleTable table = creator.CreateSample(stream, settings, service);
Assert.That(table, Is.Not.Null);
Assert.That(table.Columns, Has.Count.EqualTo(4));
Assert.That(table.Rows, Has.Count.EqualTo(2));
Assert.That(table.Columns [0].ColumnName, Is.EqualTo("A"));
Assert.That(table.Columns [0].Name, Is.EqualTo("Heading 1"));
Assert.That(table.Columns [1].ColumnName, Is.EqualTo("B"));
Assert.That(table.Columns [1].Name, Is.EqualTo("Heading 2"));
Assert.That(table.Columns [2].ColumnName, Is.EqualTo("C"));
Assert.That(table.Columns [2].Name, Is.EqualTo("C"));
Assert.That(table.Columns [3].ColumnName, Is.EqualTo("D"));
Assert.That(table.Columns [3].Name, Is.EqualTo("D"));
}
}
public static ImportRun RunTest(EntityType entityType, string fileName, ImportFormat importFormat, string sheetName = null)
{
string fileToken;
using (Stream stream = SheetTestHelper.GetStream(fileName))
{
fileToken = FileRepositoryHelper.AddTemporaryFile(stream);
}
EntityType type = entityType;
ImportConfig importConfig = CreateImportConfig(type, importFormat, sheetName);
ImportRun importRun = CreateImportRun(importConfig, fileToken);
ISpreadsheetInspector inspector = Factory.Current.Resolve <ISpreadsheetInspector>( );
SpreadsheetInfo info = inspector.GetSpreadsheetInfo(fileToken, importFormat);
SampleTable sample = inspector.GetSampleTable(fileToken, importFormat, sheetName, 1, 2, null);
AddAllFields(importConfig, sample);
// Run import
IImportRunWorker worker = Factory.Current.Resolve <IImportRunWorker>( );
worker.StartImport(importRun.Id);
return(importRun);
}
public void Test_Excel_Test2_NoHeadingRow( )
{
var service = new ExcelFileReaderService( );
SampleDataCreator creator = new SampleDataCreator( );
using (Stream stream = SheetTestHelper.GetStream("SampleDataTests.xlsx")) // IMPORTANT: Ensure TestRowNumbers has the right number of rows
{
var settings = new DataFileReaderSettings( );
settings.SheetId = SheetTestHelper.GetSheetId("SampleDataTests.xlsx", "Test2");
settings.HeadingRowNumber = 0;
settings.FirstDataRowNumber = 3;
SampleTable table = creator.CreateSample(stream, settings, service);
Assert.That(table, Is.Not.Null);
Assert.That(table.Columns, Has.Count.EqualTo(3));
Assert.That(table.Rows, Has.Count.EqualTo(2));
SampleColumn column = table.Columns [0];
Assert.That(column.ColumnName, Is.EqualTo("A"));
Assert.That(column.Name, Is.EqualTo("A"));
column = table.Columns [2];
Assert.That(column.ColumnName, Is.EqualTo("C"));
Assert.That(column.Name, Is.EqualTo("C"));
SampleRow row = table.Rows [0];
Assert.That(row.Values, Has.Count.EqualTo(3));
Assert.That(row.Values [2], Is.EqualTo("3"));
}
}
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_GetWorksheetByName( )
{
using (Stream stream = SheetTestHelper.GetStream("TestSheet.xlsx"))
using (SpreadsheetDocument doc = SpreadsheetDocument.Open(stream, false))
{
Worksheet worksheet = ExcelHelpers.GetWorksheetByName(doc, "TestSheet");
Assert.That(worksheet, Is.Not.Null);
}
}
public void Test_ReadSheet_NoSheets( )
{
var service = GetService( );
DataFileReaderSettings settings = new DataFileReaderSettings( );
using (Stream stream = SheetTestHelper.GetStream("NoSheets.xlsx"))
using (IObjectsReader reader = service.OpenDataFile(stream, settings))
{
int count = reader.GetObjects( ).Count( );
Assert.That(count, Is.EqualTo(0));
}
}
public void Test_ReadMetadata_27728( )
{
var service = GetService( );
DataFileReaderSettings settings = new DataFileReaderSettings( );
using (Stream stream = SheetTestHelper.GetStream("Qualification.xlsx"))
using (IDataFile reader = service.OpenDataFile(stream, settings))
{
SheetMetadata metadata = reader.ReadMetadata( );
Assert.That(metadata.Fields, Has.Count.EqualTo(2));
Assert.That(metadata.Fields[0].Key, Is.EqualTo("A"));
Assert.That(metadata.Fields [1].Key, Is.EqualTo("B"));
Assert.That(metadata.Fields [0].Title, Is.EqualTo("Name"));
Assert.That(metadata.Fields [1].Title, Is.EqualTo("Qualifcation code"));
}
}
public void Test_CSV_NoData( )
{
string csv = "";
var service = new CsvFileReaderService( );
var settings = new DataFileReaderSettings( );
SampleDataCreator creator = new SampleDataCreator( );
using (Stream stream = SheetTestHelper.GetCsvStream(csv))
{
SampleTable table = creator.CreateSample(stream, settings, service);
Assert.That(table, Is.Not.Null);
Assert.That(table.Columns, Has.Count.EqualTo(0));
Assert.That(table.Rows, Has.Count.EqualTo(0));
}
}
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));
}
}
private List <IObjectReader> TestRecords(string csv, DataFileReaderSettings settings, int expectedCount)
{
var service = GetService( );
List <IObjectReader> objects;
using (Stream stream = SheetTestHelper.GetCsvStream(csv))
using (IObjectsReader objectsReader = service.OpenDataFile(stream, settings ?? new DataFileReaderSettings {
ImportFormat = ImportFormat.Tab
}))
{
objects = objectsReader.GetObjects( ).ToList( );
}
Assert.That(objects, Is.Not.Null, "objects");
Assert.That(objects.Count, Is.EqualTo(expectedCount));
return(objects);
}
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;
}
}
}
}
