/// <summary>
/// Apply transformations before storing data.
/// </summary>
/// <param name="sheetName">The name of the sheet to access.</param>
static void Transformations(string sheetName)
{
Console.WriteLine("\n*** Transformations ***");
// Open XL document.
var excel = new LinqToExcel.ExcelQueryFactory(m_xlFile);
// Add transformation 1: Divide population by 1000000.
excel.AddTransformation <CountryInfo>(x => x.Population, cellValue => Int32.Parse(cellValue) / 1000000);
// Add transformation 2: First map the "Continent" string column to the "InEurope" boolean field.
// Then transform the continent string to a boolean (True when continent=="Europe").
excel.AddMapping <CountryInfo>(x => x.InEurope, "Continent");
excel.AddTransformation <CountryInfo>(x => x.InEurope, cellValue => cellValue == "Europe");
// Get a list of countries in europe with a population > 20000000.
var countries = from country in excel.Worksheet <CountryInfo>(sheetName)
select country;
// Print all selected countries.
Console.WriteLine("Countries with population in million and continent transformated to boolean InEurope");
foreach (var country in countries)
{
Console.WriteLine("Country: {0} (EU: {3})- Capital: {1} - Population: {2} million", country.Country, country.Capital, country.Population, country.InEurope);
// Excel data is copied locally thus data changed here won't be updated in the original sheet.
country.Country = "New name";
}
}
public static ScannerData CompareXMLWithData(string scanData)
{
var wb = new LinqToExcel.ExcelQueryFactory(@"C:\Domino\Listener\DB.xlsx");
wb.AddMapping <ScannerData>(x => x.EAN, "Scannad Kod");
wb.AddMapping <ScannerData>(x => x.UnitLoadFootprint1, "Unit Load footprint 1");
wb.AddMapping <ScannerData>(x => x.UnitLoadFootprint2, "Unit Load footprint 2");
wb.AddMapping <ScannerData>(x => x.UnitLoadStackingCapacity, "Unit Load stacking capacity");
wb.AddMapping <ScannerData>(x => x.ArticleNumber, "Article Number");
wb.AddMapping <ScannerData>(x => x.ArticleName, "Art Name");
wb.AddMapping <ScannerData>(x => x.Supplier, "Supplier");
wb.AddMapping <ScannerData>(x => x.Quantity, "Quantity");
wb.AddMapping <ScannerData>(x => x.GrossWeight, "Gross Weight");
var value = from x in wb.Worksheet <ScannerData>("Blad1")
where x.EAN == scanData
select x;
return(value.FirstOrDefault());
}
/// <summary>
/// Column remapping example.
/// </summary>
/// <param name="sheetName">The name of the sheet to access.</param>
static void ColumnRemapping(string sheetName)
{
Console.WriteLine("\n*** Column remapping: Country->Nation ***");
// Open XL file.
var excel = new LinqToExcel.ExcelQueryFactory(m_xlFile);
// Add column mapping. Map the excel "Country" column to the "Nation" field.
excel.AddMapping <CountryInfo>(x => x.Nation, "Country");
// Get a list of nations taken from the given sheet.
var nations = from nation in excel.Worksheet <CountryInfo>(sheetName)
select nation;
// Print all selected nations.
Console.WriteLine("All nations from sheet {0}", sheetName);
foreach (var nation in nations)
{
Console.WriteLine("Nation: {0} - Capital: {1} - Population: {2}", nation.Nation, nation.Capital, nation.Population);
}
}
static void Main(string[] args)
{
var file = new LinqToExcel.ExcelQueryFactory(@"C:\public_libraries.csv");
file.AddMapping<FlatLibrary>(x => x.Name, "Location Name");
ClearAddresses();
AddAddressesToDataStore(file);
AddLibrariesToDataStore(file);
Console.ReadLine();
}
private void button2_Click(object sender, EventArgs e)
{
string[] paths = this.textBox1.Text.Split(',');
int[] numbers = new int[paths.Length];
//Count the number of tickets in total
int ttltickets = 0;
int ind = 0;
foreach (string path in paths)
{
var book = new LinqToExcel.ExcelQueryFactory(path);
book.AddMapping <Person>(x => x.Name, "Name");
book.AddMapping <Person>(x => x.Tickets, "# of tickets");
var query1 = from x in book.Worksheet <Person>()
select x;
foreach (var result in query1)
{
String name = result.Name;
ttltickets += result.Tickets;
}
numbers[ind] = ttltickets;
ind++;
}
Random r = new Random();
int winner = r.Next(1, ttltickets);
ind = 0;
while (numbers[ind] < winner && ind <= numbers.Length - 1)
{
ind++;
}
string selectedPath = paths[ind];
int countFrom = 0;
if (ind > 0)
{
countFrom = numbers[ind - 1];
}
var book1 = new LinqToExcel.ExcelQueryFactory(selectedPath);
book1.AddMapping <Person>(x => x.Name, "Name");
book1.AddMapping <Person>(x => x.Tickets, "# of tickets");
var query = from x in book1.Worksheet <Person>()
select x;
ttltickets = countFrom;
foreach (var result in query)
{
String name = result.Name;
ttltickets += result.Tickets;
if (ttltickets >= winner)
{
Console.WriteLine(name);
new Form2(name).Show();
break;
}
}
numbers[ind] = ttltickets;
ind++;
}
