/// <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); } }