/// <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);
}
}
/// <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 CsvFileReaderService_Instance( )
{
Func <ImportFormat, IDataFileReaderService> factory = Factory.Current.Resolve <Func <ImportFormat, IDataFileReaderService> >( );
IDataFileReaderService instance = factory(ImportFormat.CSV);
Assert.That(instance, Is.TypeOf <CsvFileReaderService>( ));
}
/// <summary>
/// Constructor
/// </summary>
/// <param name="innerReaderService">Inner service</param>
public ZipFileReaderService(IDataFileReaderService innerReaderService)
{
if (innerReaderService == null)
{
throw new ArgumentNullException(nameof(innerReaderService));
}
InnerReaderService = innerReaderService;
}
public void ExcelFileReaderService_Instance_ZipFiles( )
{
Func <ImportFormat, IDataFileReaderService> factory = Factory.Current.Resolve <Func <ImportFormat, IDataFileReaderService> >( );
IDataFileReaderService instance = factory(ImportFormat.CSV | ImportFormat.Zip);
Assert.That(instance, Is.TypeOf <ZipFileReaderService>( ));
ZipFileReaderService zipReader = ( ZipFileReaderService )instance;
Assert.That(zipReader.InnerReaderService, Is.TypeOf <CsvFileReaderService>( ));
}
/// <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);
}
}