private void SaveExtractedData(DataOutputFormat dataOutputFormat, IEnumerable <IEnumerable <KeyValuePair <string, object> > > extractedDataCol)
{
ReturnMessage succesfullySaved;
DataTable extractedDataTable = null;
switch (dataOutputFormat)
{
case DataOutputFormat.XLSX:
extractedDataTable = ExtractedDataConverter.GenerateDataTable(extractedDataCol);
extractedDataTable.TableName = "ExtractedData";
var newWorkbook = new XLWorkbook();
newWorkbook.AddWorksheet(extractedDataTable);
succesfullySaved = _xlioService.SaveWorkbook(Path.Combine(ExtractionDirectory, $"{DateTime.Now.Ticks} Output.xlsx"), newWorkbook);
DisplaySuccessOrFailMessage(succesfullySaved);
break;
case DataOutputFormat.CSV:
extractedDataTable = ExtractedDataConverter.GenerateDataTable(extractedDataCol);
var generatedCSV = ExtractedDataConverter.ConvertToCSV(extractedDataTable);
succesfullySaved = _ioService.SaveText(generatedCSV, Path.Combine(ExtractionDirectory, $"{DateTime.Now.Ticks} Output.csv"));
DisplaySuccessOrFailMessage(succesfullySaved);
break;
default:
break;
}
if (extractedDataTable != null)
{
ExtractedDataTable = extractedDataTable;
}
}
public void ConvertToCSVValid()
{
string expectedCSV = File.ReadAllText(Path.Combine(executingAssemblyPath, "Files", "ExpectedCSV-CommaEscaped.txt"));
string actualCSV = ExtractedDataConverter.ConvertToCSV(XLSXDataExtractorTestHelper.GenTwoDimensionalCollectionOfExtractedData());
Assert.That(actualCSV, Is.EqualTo(expectedCSV));
}
public void ConvertToCSVFromDataTable_TabDelimited_NotEscaped()
{
string expectedCSV = ExpectedStrings.ExpectedCSV_TabDelimited_NotEscaped;
var dataTable = ExtractedDataConverter.GenerateDataTable(XLSXDataExtractorTestHelper.GenTwoDimensionalCollectionOfExtractedData());
string actualCSV = ExtractedDataConverter.ConvertToDelimitedString(dataTable, "\t", false);
Assert.That(actualCSV, Is.EqualTo(expectedCSV));
}
public void SheetToDelimitedStringIntegrationTest()
{
DataExtractor dataExtractor = new DataExtractor(Path.Combine(executingAssemblyPath, "Files", "IntegrationTestExample.xlsx"));
var extracted = dataExtractor.SheetToDataTable(dataExtractor.RequiredWorkbook.Worksheet(1));
Assert.That(extracted.Item2.Count(), Is.EqualTo(0));
var generatedCSVText = ExtractedDataConverter.ConvertToCSV(extracted.Item1, false);
Assert.That(generatedCSVText, Is.EqualTo(File.ReadAllText(Path.Combine(executingAssemblyPath, "Files", "IntegrationTestExpectedSheetToCSV.txt"))));
}
public void GenerateDataTableValidTest()
{
var dataTable = ExtractedDataConverter.GenerateDataTable(XLSXDataExtractorTestHelper.GenTwoDimensionalCollectionOfExtractedData());
Assert.That(dataTable.Columns.Count, Is.EqualTo(10));
Assert.That(dataTable.Rows.Count, Is.EqualTo(10));
for (int i = 0; i < 10; i++)
{
DataColumn column = dataTable.Columns[i];
Assert.That(column.ColumnName, Is.EqualTo("Test" + i));
}
}
public void ValidCSVGenerationIntegrationTest()
{
DataExtractor dataExtractor = new DataExtractor(Path.Combine(executingAssemblyPath, "Files", "IntegrationTestExample.xlsx"));
var extractionRequests = new List <ExtractionRequest>()
{
new ExtractionRequest("SalesRep", 2, 3), new ExtractionRequest("SalesRepID", 2, 2)
};
var extracted = dataExtractor.RetrieveDataCollectionFromAllWorksheets <object>(extractionRequests);
var generatedCSVText = ExtractedDataConverter.ConvertToCSV(extracted);
Assert.That(generatedCSVText, Is.EqualTo(File.ReadAllText(Path.Combine(executingAssemblyPath, "Files", "IntegrationTestExpectedCsv.txt"))));
}
public void ConvertToWorksheetValidTest()
{
var worksheet = ExtractedDataConverter.ConvertToWorksheet(XLSXDataExtractorTestHelper.GenTwoDimensionalCollectionOfExtractedData());
var cellsUsed = worksheet.CellsUsed();
bool emptyCellsFromDataTable = cellsUsed.Any(x => x.Address.ColumnNumber == 6 && x.Address.RowNumber > 1);
Assert.That(emptyCellsFromDataTable, Is.False);
foreach (var cell in cellsUsed)
{
//generated table should be 12 cells by 12 cells start from cell 0,0.
Assert.That(cell.Address.RowNumber, Is.LessThan(12));
Assert.That(cell.Address.ColumnNumber, Is.LessThan(12));
}
}
public void ValidWorksheetGenerationIntegrationTest()
{
DataExtractor dataExtractor = new DataExtractor(Path.Combine(executingAssemblyPath, "Files", "IntegrationTestExample.xlsx"));
var extractionRequests = new List <ExtractionRequest>()
{
new ExtractionRequest("SalesRep", 2, 3), new ExtractionRequest("SalesRepID", 2, 2)
};
var extracted = dataExtractor.RetrieveDataCollectionFromAllWorksheets <object>(extractionRequests);
var generatedWorksheet = ExtractedDataConverter.ConvertToWorksheet(extracted);
Assert.That(generatedWorksheet.Cell(1, 1).Value.ToString(), Is.EqualTo("SalesRep"));
Assert.That(generatedWorksheet.Cell(2, 1).Value.ToString(), Is.EqualTo("Jane"));
Assert.That(generatedWorksheet.Cell(3, 1).Value.ToString(), Is.EqualTo("Ashish"));
Assert.That(generatedWorksheet.Cell(4, 1).Value.ToString(), Is.EqualTo("John"));
Assert.That(generatedWorksheet.Cell(1, 2).Value.ToString(), Is.EqualTo("SalesRepID"));
Assert.That(generatedWorksheet.Cell(2, 2).Value.ToString(), Is.EqualTo("456"));
Assert.That(generatedWorksheet.Cell(3, 2).Value.ToString(), Is.EqualTo("789"));
Assert.That(generatedWorksheet.Cell(4, 2).Value.ToString(), Is.EqualTo("123"));
}
public void ConvertToWorksheetCollectionWithNullFieldNames()
{
var ex = Assert.Throws <ArgumentNullException>(() => ExtractedDataConverter.ConvertToWorksheet(XLSXDataExtractorTestHelper.GenTwoDimensionalCollectionOfExtractedDataWithNullFieldNames()));
Assert.That(ex.Message, Is.EqualTo("An ExtractedData object at position 0,5 has a null field name, null field names cannot be added.\r\nParameter name: extractedData"));
}
public void ConvertToWorksheetNullDataTablenTest()
{
DataTable nullObj = null;
var ex = Assert.Throws <ArgumentNullException>(() => ExtractedDataConverter.ConvertToWorksheet(nullObj));
}
public void ConvertToWorksheetNullCollectionTest()
{
IEnumerable <IEnumerable <KeyValuePair <string, object> > > nullObj = null;
var ex = Assert.Throws <ArgumentNullException>(() => ExtractedDataConverter.ConvertToWorksheet(nullObj));
}
