/// <summary>
/// Get spreadsheet Information from the imported file.
/// </summary>
/// <param name="fileUploadId">
/// File upload Id.
/// </param>
/// <param name="fileFormat">
/// Imported file fileFormat ( Excel or CSV)
/// </param>
/// <returns>Spreadsheet info
/// </returns>
public SpreadsheetInfo GetSpreadsheetInfo(string fileUploadId, ImportFormat fileFormat)
{
// Get service
IDataFileReaderService service = _readerActivator(fileFormat);
// Load info about sheets
IReadOnlyList <SheetInfo> sheets;
using (Stream stream = FileRepositoryHelper.GetTemporaryFileDataStream(fileUploadId))
{
// Settings
DataFileReaderSettings settings = new DataFileReaderSettings
{
ImportFormat = fileFormat
};
IDataFile dataFile = service.OpenDataFile(stream, settings);
sheets = dataFile.GetSheets( );
}
var spreadsheetInfo = new SpreadsheetInfo
{
ImportFileFormat = fileFormat,
SheetCollection = sheets
};
return(spreadsheetInfo);
}
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"));
}
}
/// <summary>
/// Get sheet info from the imported spreadsheet.
/// </summary>
/// <param name="fileUploadId">File upload Id.</param>
/// <param name="sheetId">Selected sheet info.</param>
/// <param name="headerRowNo">Header row no.</param>
/// <param name="dataRowNo">Data row number to start reading data.</param>
/// <param name="lastRowNo">Optional last row number to read.</param>
/// <param name="fileFormat">Imported file fileFormat ( Excel or CSV)</param>
/// <returns>
/// Sheet Info.
/// </returns>
public SampleTable GetSampleTable(string fileUploadId, ImportFormat fileFormat, string sheetId, int headerRowNo, int dataRowNo, int?lastRowNo)
{
// Get service
IDataFileReaderService service = _readerActivator(fileFormat);
// Settings
DataFileReaderSettings settings = new DataFileReaderSettings
{
ImportFormat = fileFormat,
HeadingRowNumber = headerRowNo,
FirstDataRowNumber = dataRowNo,
LastDataRowNumber = lastRowNo,
SheetId = sheetId
};
// Open stream
using (Stream stream = FileRepositoryHelper.GetTemporaryFileDataStream(fileUploadId))
{
// Build sample
SampleDataCreator creator = new SampleDataCreator( );
SampleTable sampleTable = creator.CreateSample(stream, settings, service);
// Trim titles
foreach (var col in sampleTable.Columns)
{
col.Name = col.Name?.Trim( ) ?? "";
}
return(sampleTable);
}
}
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_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 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 static DataFileReaderSettings GetSettings(Stream excel, string sheetName)
{
DataFileReaderSettings settings = new DataFileReaderSettings
{
SheetId = GetSheetId(excel, sheetName)
};
return(settings);
}
public void Test_ReadMetadata_NoData( )
{
string csv = "";
var settings = new DataFileReaderSettings();
var metadata = GetMetadata(csv, settings);
Assert.That(metadata.Fields, Has.Count.EqualTo(0));
}
public void Test_ReadSheet_DocHasLessRowsThanFirstRow()
{
string csv = "";
var settings = new DataFileReaderSettings {
FirstDataRowNumber = 10
};
TestRecords(csv, settings, 0);
}
public void Test_ReadSheet_NoHeaderRow( )
{
string csv =
"Data1\t321\n" +
"Data2\t123";
var settings = new DataFileReaderSettings {
FirstDataRowNumber = 1, ImportFormat = ImportFormat.Tab
};
TestTwoRecords(csv, settings);
}
public void Test_ReadSheet_NoHeaderRow()
{
string csv =
"Data1,321\n" +
"Data2,123";
var settings = new DataFileReaderSettings {
FirstDataRowNumber = 1
};
TestTwoRecords(csv, settings);
}
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_ReadSheet_GapAfterHeaderRow_LastRow( )
{
string csv =
"TextCol,NumberCol\n" +
"\n" +
"Data1,321\n" +
"Data2,123";
var settings = new DataFileReaderSettings {
FirstDataRowNumber = 3, LastDataRowNumber = 3
};
TestRecords(csv, settings, 1);
}
// The following cases report an incorrect line number because of the preceeding blank line and an issue in the .Net TextFieldParser. See notes in CsvFileReader.ReadFields
//[TestCase( 0, 1, "\nData\nData", "Line 2" )]
//[TestCase( 0, 2, "Header\n\nData\nData", "Line 3" )]
//[TestCase( 0, 2, "Header\n\nData", "Line 3" )]
//[TestCase( 0, 1, "\nData", "Line 2" )]
public void Test_ReadSheet_GetLocation(int headerRow, int firstData, string csv, string expected)
{
DataFileReaderSettings settings = new DataFileReaderSettings( );
settings.FirstDataRowNumber = firstData;
settings.HeadingRowNumber = headerRow;
var objects = TestRecords(csv, settings);
var reader = objects.First( );
string location = reader.GetLocation( );
Assert.That(reader.GetString("1"), Is.EqualTo("Data"));
Assert.That(location, Is.EqualTo(expected));
}
private void TestTwoRecords(string csv, DataFileReaderSettings settings = null)
{
var objects = TestRecords(csv, settings, 2);
var object1 = objects [0];
Assert.That(object1.GetString("1"), Is.EqualTo("Data1"));
Assert.That(object1.GetInt("2"), Is.EqualTo(321));
var object2 = objects [1];
Assert.That(object2.GetString("1"), Is.EqualTo("Data2"));
Assert.That(object2.GetInt("2"), Is.EqualTo(123));
}
public void Test_ReadMetadata_Default( )
{
string csv =
"TextCol,NumberCol\n" +
"Data1,321\n" +
"Data2,123";
var settings = new DataFileReaderSettings();
var metadata = GetMetadata(csv, settings);
Assert.That(metadata.Fields, Has.Count.EqualTo(2));
Assert.That(metadata.Fields[0].Key, Is.EqualTo("1"));
Assert.That(metadata.Fields[0].Title, Is.EqualTo("TextCol"));
Assert.That(metadata.Fields[1].Key, Is.EqualTo("2"));
Assert.That(metadata.Fields[1].Title, Is.EqualTo("NumberCol"));
}
/// <summary>
/// Get reader settings from the config entity.
/// </summary>
/// <param name="importConfig">The import config entity.</param>
/// <returns>Reader settings</returns>
private static DataFileReaderSettings CreateReaderSettings(ImportConfig importConfig)
{
ImportFormat importFormat = ImportHelpers.GetImportFormat(importConfig);
ApiResourceMapping mapping = importConfig.ImportConfigMapping;
DataFileReaderSettings settings = new DataFileReaderSettings
{
ImportFormat = importFormat,
HeadingRowNumber = mapping.ImportHeadingRow ?? 1,
FirstDataRowNumber = mapping.ImportDataRow ?? 2,
LastDataRowNumber = mapping.ImportLastDataRow, // default is null
SheetId = mapping.MappingSourceReference
};
return(settings);
}
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"));
}
}
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_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_ReadMetadata_NoHeading()
{
string csv =
"Data1,321\n" +
"Data2,123";
var settings = new DataFileReaderSettings {
HeadingRowNumber = 0
};
var metadata = GetMetadata(csv, settings);
Assert.That(metadata.Fields, Has.Count.EqualTo(2));
Assert.That(metadata.Fields[0].Key, Is.EqualTo("1"));
Assert.That(metadata.Fields[0].Title, Is.EqualTo("1"));
Assert.That(metadata.Fields[1].Key, Is.EqualTo("2"));
Assert.That(metadata.Fields[1].Title, Is.EqualTo("2"));
}
private SheetMetadata GetMetadata(string csv, DataFileReaderSettings settings)
{
var service = GetService();
SheetMetadata metadata;
using (MemoryStream stream = new MemoryStream())
using (StreamWriter writer = new StreamWriter(stream))
{
writer.Write(csv);
writer.Flush();
stream.Position = 0;
using (IDataFile dataFile = service.OpenDataFile(stream, settings ?? new DataFileReaderSettings( )))
{
metadata = dataFile.ReadMetadata( );
}
}
Assert.That(metadata, Is.Not.Null, "metadata");
return(metadata);
}
public void Test_ReadSheet_Data_LastRow(string lineend)
{
string csv =
"TextCol,NumberCol\n" +
"Data1,321\n" +
"Data1,321\n" +
"Data2,123\n";
if (lineend == "cr")
{
csv = csv.Replace("\n", "\r");
}
if (lineend == "crlf")
{
csv = csv.Replace("\n", "\r\n");
}
var settings = new DataFileReaderSettings {
LastDataRowNumber = 3
};
TestRecords(csv, settings, 2);
}
/// <summary>
/// Open up a records reader to read the contents of the file.
/// </summary>
/// <remarks>
/// Caller closes stream.
/// </remarks>
private IObjectsReader GetRecordsReader(ImportRun importRun, ImportConfig importConfig)
{
// Get settings
DataFileReaderSettings settings = CreateReaderSettings(importConfig);
// Get the timezone
if (!string.IsNullOrEmpty(importRun.ImportTimeZone))
{
settings.TimeZoneInfo = TimeZoneHelper.GetTimeZoneInfo(importRun.ImportTimeZone);
}
// Get file reader
IDataFileReaderService fileReader = _readerActivator(settings.ImportFormat);
// Open stream
string fileUploadId = importRun.ImportFileId;
if (string.IsNullOrEmpty(fileUploadId))
{
throw new Exception("File handle not set");
}
Stream fileStream;
try
{
fileStream = FileRepository.Get(fileUploadId);
}
catch (Exception ex)
{
throw new Exception("Could not retrieve file. " + ex.Message, ex);
}
IObjectsReader recordsReader = fileReader.OpenDataFile(fileStream, settings);
return(recordsReader);
}
/// <summary>
/// Read sample data from a spreadsheet reader.
/// </summary>
/// <param name="stream">The spreadsheet.</param>
/// <param name="settings">Settings.</param>
/// <param name="service">The reader.</param>
/// <returns></returns>
public SampleTable CreateSample(Stream stream, DataFileReaderSettings settings, IDataFileReaderService service)
{
if (stream == null)
{
throw new ArgumentNullException(nameof(stream));
}
if (settings == null)
{
throw new ArgumentNullException(nameof(settings));
}
if (service == null)
{
throw new ArgumentNullException(nameof(service));
}
// Read field list
using (IDataFile dataFile = service.OpenDataFile(stream, settings))
{
SheetMetadata metadata = dataFile.ReadMetadata( );
SampleTable table = new SampleTable
{
Columns = metadata.Fields.Select(
field =>
new SampleColumn
{
ColumnName = field.Key,
Name = field.Title
}
).ToList( )
};
// Read records
var records = dataFile.GetObjects( ).Take(NumberOfSampleRows);
// Convert to sample rows
List <SampleRow> sampleRows = new List <SampleRow>( );
foreach (IObjectReader record in records)
{
// Read values
var values = metadata.Fields.Select(field =>
{
try
{
return(record.GetString(field.Key));
}
catch
{
return(string.Empty);
}
}).ToList( );
// Create sample row
SampleRow row = new SampleRow
{
Values = values
};
sampleRows.Add(row);
}
table.Rows = sampleRows;
return(table);
}
}
