public void NullableTest()
{
var workbook = WorkbookFactory.Create(@"..\..\..\products.xlsx");
var excel = new ExcelMapper(workbook);
var products = excel.Fetch <NullableProduct>().ToList();
var nudossi = products[0];
Assert.AreEqual("Nudossi", nudossi.Name);
Assert.AreEqual(60, nudossi.Number);
Assert.AreEqual(1.99m, nudossi.Price);
Assert.IsFalse(nudossi.Offer.Value);
nudossi.OfferEnd = null;
var halloren = products[1];
Assert.IsTrue(halloren.Offer.Value);
Assert.AreEqual(new DateTime(2015, 12, 31), halloren.OfferEnd);
halloren.Number = null;
halloren.Offer = null;
var file = "productsnullable.xlsx";
new ExcelMapper().Save(file, products, "Products");
var productsFetched = new ExcelMapper(file).Fetch <NullableProduct>().ToList();
CollectionAssert.AreEqual(products, productsFetched);
}
public void NullableTest()
{
var excel = new ExcelMapper(@"..\..\products.xlsx");
var products = excel.Fetch<NullableProduct>().ToList();
var nudossi = products[0];
Assert.AreEqual("Nudossi", nudossi.Name);
Assert.AreEqual(60, nudossi.Number);
Assert.AreEqual(1.99m, nudossi.Price);
Assert.IsFalse(nudossi.Offer.Value);
nudossi.OfferEnd = null;
var halloren = products[1];
Assert.IsTrue(halloren.Offer.Value);
Assert.AreEqual(new DateTime(2015, 12, 31), halloren.OfferEnd);
halloren.Number = null;
halloren.Offer = null;
var file = "productsnullable.xlsx";
new ExcelMapper().Save(file, products, "Products");
var productsFetched = new ExcelMapper(file).Fetch<NullableProduct>().ToList();
CollectionAssert.AreEqual(products, productsFetched);
}
public void IgnoreTest()
{
var excel = new ExcelMapper(@"..\..\..\products.xlsx");
excel.Ignore <IgnoreProduct>(p => p.Price);
var products = excel.Fetch <IgnoreProduct>().ToList();
var nudossi = products[0];
Assert.AreEqual("Nudossi", nudossi.Name);
Assert.AreEqual(0, nudossi.Number);
Assert.AreEqual(0m, nudossi.Price);
Assert.IsFalse(nudossi.Offer);
var halloren = products[1];
Assert.IsTrue(halloren.Offer);
Assert.AreEqual(new DateTime(2015, 12, 31), halloren.OfferEnd);
var file = "productsignored.xlsx";
new ExcelMapper().Save(file, products, "Products");
var productsFetched = new ExcelMapper(file).Fetch <IgnoreProduct>().ToList();
CollectionAssert.AreEqual(products, productsFetched);
}
public static List <InputMetadata> LoadAllMetadataFromExcelFiles(string[] inputs)
{
var result = new List <InputMetadata>();
foreach (var input in inputs)
{
var workbook = WorkbookFactory.Create(input);
var mapper = new ExcelMapper(workbook);
foreach (var sheet in workbook)
{
var items = mapper.Fetch <InputMetadata>(sheet.SheetName);
var filtered = from item in items
where item.IsEmpty().Equals(false)
select item;
result.AddRange(filtered);
}
workbook.Close();
}
return(result);
}
private static void Read()
{
var mapper = new ExcelMapper(@"C:\temp\test.xls");
var data = mapper.Fetch <JalleData>("newSheet");//.Select(n=>n.Value);
foreach (var item in data)
{
Console.WriteLine(item.ToString());
}
}
public void TestExcel()
{
var excelMapper = new ExcelMapper(@"..\..\..\1.xlsx");
var list = new List <ConfigData>();
var headers = excelMapper.GetHeders();
foreach (var item in excelMapper.Fetch <ConfigData>())
{
list.Add(item);
}
}
public void DateTest()
{
// see https://github.com/mganss/ExcelMapper/issues/51
var mapper = new ExcelMapper(@"..\..\..\DateTest.xlsx")
{
HeaderRow = true
};
var courses = mapper.Fetch <Course>().ToList();
Assert.AreEqual("00:00.0", courses.First().CourseStartDate);
}
public void ColumnTestUsingFetchWithType()
{
var excel = new ExcelMapper(@"..\..\..\dataitems.xlsx")
{
HeaderRow = false
};
var items = excel.Fetch(typeof(DataItem)).OfType <DataItem>().ToList();
var trackedFile = "dataitemstracked1.xlsx";
excel.Save(trackedFile, "DataItems");
var itemsTracked = excel.Fetch(trackedFile, typeof(DataItem), "DataItems").OfType <DataItem>().ToList();
CollectionAssert.AreEqual(items, itemsTracked);
var saveFile = "dataitemssave1.xlsx";
new ExcelMapper().Save(saveFile, items, "DataItems");
var itemsSaved = new ExcelMapper().Fetch(saveFile, typeof(DataItem), "DataItems").OfType <DataItem>().ToList();
CollectionAssert.AreEqual(items, itemsSaved);
}
public void ColumnTest()
{
var excel = new ExcelMapper(@"..\..\..\dataitems.xlsx")
{
HeaderRow = false
};
var items = excel.Fetch <DataItem>().ToList();
var trackedFile = "dataitemstracked.xlsx";
excel.Save(trackedFile, "DataItems");
var itemsTracked = excel.Fetch <DataItem>(trackedFile, "DataItems").ToList();
CollectionAssert.AreEqual(items, itemsTracked);
var saveFile = "dataitemssave.xlsx";
new ExcelMapper().Save(saveFile, items, "DataItems");
var itemsSaved = new ExcelMapper().Fetch <DataItem>(saveFile, "DataItems").ToList();
CollectionAssert.AreEqual(items, itemsSaved);
}
public void SaveFetchedTest()
{
var excel = new ExcelMapper(@"..\..\..\products.xlsx");
var products = excel.Fetch <Product>().ToList();
products[2].Price += 1.0m;
var file = @"productssavefetched.xlsx";
excel.Save(file, products);
var productsFetched = new ExcelMapper(file).Fetch <Product>().ToList();
CollectionAssert.AreEqual(products, productsFetched);
}
static void Main()
{
// Excel IO
string path = "C:\\Users\\user\\Desktop\\ExcelData.xlsx";
ExcelMapper mapper = new ExcelMapper(path);
IEnumerable <Data> data = mapper.Fetch <Data>(0);
foreach (Data item in data)
{
Console.WriteLine("Name={0},Age={1},Level={2},Score={3}",
item.Name, item.Age, item.Level, item.Score);
}
Console.ReadLine();
}
public IEnumerable <Person> ReadStream()
{
String file = "C:\\Users\\prataps\\source\\repos\\TestExcel.xlsx";
List <Person> persons;
using (FileStream readStream = new FileStream(file, FileMode.Open, FileAccess.Read))
{
var excel = new ExcelMapper()
{
TrackObjects = true
};
persons = excel.Fetch <Person>(readStream, "Customers").ToList();
readStream.Close();
}
return(persons);
}
public static List <TOCScreenshot> ReadTOC(string TOCPath)
{
if (string.IsNullOrWhiteSpace(TOCPath) || !File.Exists(TOCPath))
{
throw new ArgumentException("Blank or non-existant path given", nameof(TOCPath));
}
try
{
var res = new List <TOCScreenshot>();
XSSFWorkbook tocWorkbook;
var sheetNames = new List <string>();
using (FileStream file = new FileStream(TOCPath, FileMode.Open, FileAccess.Read))
{
tocWorkbook = new XSSFWorkbook(file);
for (int i = 1; i < tocWorkbook.NumberOfSheets; i++)
{
sheetNames.Add(tocWorkbook.GetSheetName(i));
}
}
var tocChoices = new ExcelMapper(@TOCPath)
{
TrackObjects = false
};
foreach (var sheetName in sheetNames)
{
var screenshot = new TOCScreenshot()
{
Filename = $"{sheetName}.png"
};
screenshot.Choices = tocChoices.Fetch <TOCChoice>(sheetName).ToList();
res.Add(screenshot);
}
return(res);
}
catch (Exception x)
{
x.Data.Add(nameof(TOCPath), TOCPath);
throw;
}
}
public void JsonMappedTest()
{
var excel = new ExcelMapper(@"..\..\..\productsjson.xlsx");
excel.AddMapping <ProductJsonMapped>("Product", p => p.Product).AsJson();
var products = excel.Fetch <ProductJsonMapped>().ToList();
CollectionAssert.AreEqual(new List <Product>
{
new Product {
Name = "Nudossi", NumberInStock = 60, Price = 1.99m, Value = "C2*D2"
},
new Product {
Name = "Halloren", NumberInStock = 33, Price = 2.99m, Value = "C3*D3"
},
new Product {
Name = "Filinchen", NumberInStock = 100, Price = 0.99m, Value = "C4*D4"
},
}, products.Select(p => p.Product));
}
public void GetterSetterTest()
{
var excel = new ExcelMapper(@"..\..\..\productsconvert.xlsx")
{
TrackObjects = true
};
excel.AddMapping <GetterSetterProduct>("Name", p => p.Name);
excel.AddMapping <GetterSetterProduct>("OfferEnd", p => p.OfferEnd)
.SetCellUsing((c, o) =>
{
if (o == null)
{
c.SetCellValue("NULL");
}
else
{
c.SetCellValue((DateTime)o);
}
})
.SetPropertyUsing(v =>
{
if ((v as string) == "NULL")
{
return(null);
}
return(Convert.ChangeType(v, typeof(DateTime), CultureInfo.InvariantCulture));
});
var products = excel.Fetch <GetterSetterProduct>().ToList();
var file = @"productsconverttracked.xlsx";
excel.Save(file);
var productsFetched = new ExcelMapper(file).Fetch <GetterSetterProduct>().ToList();
CollectionAssert.AreEqual(products, productsFetched);
}
/// <summary>
/// Initializes the ModuleClient and sets up the callback to receive
/// messages containing temperature information
/// </summary>
static async Task Init()
{
// read the route data
_routeData = _routeReader.Fetch <RouteData>().ToList();
// TODO: 11 - update reported properties at a specified time interval
//_timer = new Timer(UpdateReportedProperties, null, TimeSpan.FromSeconds(_secondsToTwinReportedPropertiesUpdate), TimeSpan.FromSeconds(_secondsToTwinReportedPropertiesUpdate));
// the module client is in charge of sending messages in the context of this module (VehicleTelemetry)
// TODO: 3 - Create module client from container environment variable
//_vehicleTelemetryModuleClient = await ModuleClient.CreateFromEnvironmentAsync(TransportType.Mqtt);
await _vehicleTelemetryModuleClient.OpenAsync();
// the device client is responsible for the device twin reported properties updates
// TODO: 4 - Create device client to obtain desired properties from Twin and update reported properties
//_deviceClient = DeviceClient.CreateFromConnectionString(_deviceConnectionString);
//await _deviceClient.SetDesiredPropertyUpdateCallbackAsync(onDesiredPropertiesUpdateAsync, null);
// Register callback to be called when a message is received by this module
await _vehicleTelemetryModuleClient.SetInputMessageHandlerAsync("input1", PipeMessage, _vehicleTelemetryModuleClient);
//initialize device with values obtained from the desired properties in the device twin
// TODO: 5 - initialize device instance with values obtained from the device twin desired properties
//var twin = await _deviceClient.GetTwinAsync();
//var desired = twin.Properties.Desired;
//await UpdateDeviceInstanceFromDesiredProperties(desired);
// TODO: 6 - initialize iot edge storage
// _storageAccount = CloudStorageAccount.Parse(_storageConnectionString);
// _blobClient = _storageAccount.CreateCloudBlobClient();
// _blobContainer = _blobClient.GetContainerReference("telemetry");
// if(!_blobContainer.Exists()){
// _blobContainer.CreateIfNotExists();
// }
//start generating telemetry data
await GenerateTelemetry();
}
public void SaveTrackedObjectsTest()
{
var excel = new ExcelMapper(@"..\..\..\products.xlsx")
{
TrackObjects = true
};
excel.AddMapping(typeof(ProductMapped), "Name", "NameX");
excel.AddMapping <ProductMapped>("Number", p => p.NumberX);
excel.AddMapping <ProductMapped>("Price", p => p.PriceX);
var products = excel.Fetch <ProductMapped>().ToList();
products[1].PriceX += 1.0m;
var file = @"productssavetracked.xlsx";
excel.Save(file);
var productsFetched = new ExcelMapper(file).Fetch <ProductMapped>().ToList();
CollectionAssert.AreEqual(products, productsFetched);
}
public void IgnoreTest()
{
var excel = new ExcelMapper(@"..\..\products.xlsx");
excel.Ignore<IgnoreProduct>(p => p.Price);
var products = excel.Fetch<IgnoreProduct>().ToList();
var nudossi = products[0];
Assert.AreEqual("Nudossi", nudossi.Name);
Assert.AreEqual(0, nudossi.Number);
Assert.AreEqual(0m, nudossi.Price);
Assert.IsFalse(nudossi.Offer);
var halloren = products[1];
Assert.IsTrue(halloren.Offer);
Assert.AreEqual(new DateTime(2015, 12, 31), halloren.OfferEnd);
var file = "productsignored.xlsx";
new ExcelMapper().Save(file, products, "Products");
var productsFetched = new ExcelMapper(file).Fetch<IgnoreProduct>().ToList();
CollectionAssert.AreEqual(products, productsFetched);
}
public IEnumerable <Person> Read()
{
String file_name = "C:\\Users\\prataps\\source\\repos\\TestExcel.xlsx";
//FileStream file_stream = new FileStream(file_name, FileMode.Create);
var excel = new ExcelMapper()
{
TrackObjects = true
};
var persons = excel.Fetch <Person>(file_name, "Customers").ToList();
persons[3].Gender = "Female";
//saving first time
String fileOut = "C:\\Users\\prataps\\source\\repos\\TestExcel_out.xlsx";
excel.Save(fileOut, "Customers");
//change the values
persons[1].Gender = "Unknown";
//saving for the second time
excel.Save(fileOut, "Customers");
//changing the value for third time
persons[2].Gender = "Dont Care";
//saving for the third time
excel.Save(fileOut, persons);
return(persons);
}
public void SaveFetchedTest()
{
var excel = new ExcelMapper(@"..\..\products.xlsx");
var products = excel.Fetch<Product>().ToList();
products[2].Price += 1.0m;
var file = @"productssavefetched.xlsx";
excel.Save(file, products);
var productsFetched = new ExcelMapper(file).Fetch<Product>().ToList();
CollectionAssert.AreEqual(products, productsFetched);
}
public void SaveTrackedObjectsTest()
{
var excel = new ExcelMapper(@"..\..\products.xlsx") { TrackObjects = true };
excel.AddMapping(typeof(ProductMapped), "Name", "NameX");
excel.AddMapping<ProductMapped>("Number", p => p.NumberX);
excel.AddMapping<ProductMapped>("Price", p => p.PriceX);
var products = excel.Fetch<ProductMapped>().ToList();
products[1].PriceX += 1.0m;
var file = @"productssavetracked.xlsx";
excel.Save(file);
var productsFetched = new ExcelMapper(file).Fetch<ProductMapped>().ToList();
CollectionAssert.AreEqual(products, productsFetched);
}
/// <summary>
/// Update Test Results.
/// </summary>
/// <param name="filePath">file path.</param>
/// <param name="isJson">Is input file type Json?</param>
/// <param name="account">Account.</param>
/// <param name="project">Project.</param>
/// <param name="token">Token.</param>
/// <returns>Integer.</returns>
public static int UpdateTestResults(string filePath, bool isJson, string account, string project, string token)
{
AzureDevOpsUtility.UpdateAccountDetails(account, project, token);
if (isJson)
{
var json = File.ReadAllText(filePath);
TestResults = JsonConvert.DeserializeObject <TestResults>(json);
}
else
{
var excel = new ExcelMapper(filePath);
excel.AddMapping <TestCaseOutcome>("TestCaseId", t => t.TestCaseId);
excel.AddMapping <TestCaseOutcome>("TestSuiteId", t => t.TestSuiteId);
excel.AddMapping <TestCaseOutcome>("Outcome", t => t.Outcome)
.SetPropertyUsing(v =>
{
if ((v as string).Equals(Constants.NotExecuted))
{
return(OutcomeType.NotExecuted);
}
if ((v as string).Equals(Constants.Failed))
{
return(OutcomeType.Failed);
}
else
{
return(OutcomeType.Passed);
}
});
var testCases = excel.Fetch <TestCaseOutcome>().ToList();
TestResults = new TestResults()
{
SuiteId = 0,
TestCases = testCases
};
}
try
{
GetTestCasesByPlanAsync().GetAwaiter().GetResult();
GetTestPointIdsAsync().GetAwaiter().GetResult();
CreateNewTestRunsAsync().GetAwaiter().GetResult();
UpdateTestRunsAsync().GetAwaiter().GetResult();
}
catch (FlurlHttpException e)
{
var statusCode = e.Call.Response.StatusCode.ToString();
if (statusCode.Equals(Constants.NonAuthoritativeInformation) || statusCode.Equals(Constants.Unauthorized))
{
Log.Error("Authentication Error!!! Please provide valid Account Details...\n");
}
}
catch (Exception e)
{
CommonUtility.DeleteTestRunsAsync(NewTestRunIds).GetAwaiter().GetResult();
throw e;
}
return(0);
}
