/// <summary>
/// Default example.
/// </summary>
public static void DefaultExample()
{
Console.WriteLine("\n*** Default example. Default sheet: 'Sheet1' + header row ***");
// Open XL document.
var excel = new LinqToExcel.ExcelQueryFactory(m_xlFile);
// If you set strict mapping to true, every column in the Excel sheet must match a property in the destination class.
// And every property in the destination class must match a column in the sheet. If not an exception is thrown.
// When false (default), you can have extra columns not in the destination class or extra properties not in the sheet.
// excel.StrictMapping=true; // Comment out this line and you get an exception because the CountryInfo class has properties that has no column in the sheet.
// Get a list of countries in europe with a population > 20000000.
var countries = from country in excel.Worksheet <CountryInfo>()
where country.Continent == "Europe" && country.Population > 20000000
select country;
// Print all selected countries.
Console.WriteLine("Countries in Europe with population>20000000");
foreach (var country in countries)
{
Console.WriteLine("Country: {0} - Capital: {1} - Population: {2}", country.Country, country.Capital, country.Population);
// Excel data is copied locally thus data changed here won't be updated in the original sheet.
country.Country = "New name";
}
}
/// <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 IEnumerable <Branch> ReadDistinctBrnaches(string fileName)
{
var book = new LinqToExcel.ExcelQueryFactory(fileName);
ExcelQueryable <Row> a = book.Worksheet(_worksheetName);
return(Enumerable.TakeWhile(a, row => !string.IsNullOrEmpty(row["Branża"].Cast <string>())).Select(row => new Branch()
{
Name = row["Branża"].Cast <string>()
}).GroupBy(brand => brand.Name).Select(brands => brands.First()));
}
/// <summary>
/// Query the sheets in a workbook.
/// </summary>
/// <param name="sheetName">The name of the sheet to access.</param>
static void QueryColumns(string sheetName)
{
Console.WriteLine("\n*** Columns of sheet: {0} ***", sheetName);
// Open XL document.
var excel = new LinqToExcel.ExcelQueryFactory(m_xlFile);
// Get the sheets in the worksbook.
var columnNames = excel.GetColumnNames(sheetName);
foreach (var column in columnNames)
{
Console.WriteLine("- Column name: {0}", column);
}
}
/// <summary>
/// Query the sheets in a workbook.
/// </summary>
static void QuerySheets()
{
Console.WriteLine("\n*** Sheet in the workbook ***");
// Open XL document.
var excel = new LinqToExcel.ExcelQueryFactory(m_xlFile);
// Get the sheets in the worksbook.
var sheetNames = excel.GetWorksheetNames();
foreach (var sheet in sheetNames)
{
Console.WriteLine("- Sheet name: {0}", sheet);
}
}
private void UseLinqToExcel()
{
string path = @"B:\Publish\SupportTools_Visio\TestData.xlsx";
var excel = new LTE.ExcelQueryFactory(path);
var stuff = from c in excel.Worksheet <TestData>()
select c;
foreach (var item in stuff)
{
VisioHlp.DisplayInWatchWindow(
string.Format("Col1:{0} Col2:{1} Col3:{2} Col4:{3} Col5:{4}", item.Col1, item.Col2, item.Col3, item.Col4, item.Col5)
);
}
}
/// <summary>
/// Access a range example.
/// </summary>
/// <param name="sheetName">The name of the sheet to access.</param>
/// <param name="start">The start index.</param>
/// <param name="end">The end index.</param>
static void AccessRange(string sheetName, string start, string end)
{
Console.WriteLine("\n*** Access range ({0}, {1}) ***", start, end);
// Open XL file.
var excel = new LinqToExcel.ExcelQueryFactory(m_xlFile);
// Get a list of countries taken from the given sheet.
var countries = from country in excel.WorksheetRange <CountryInfo>(start, end, sheetName)
select country;
// Print all selected countries.
Console.WriteLine("All countries from sheet {0} range ({1}:{2})", sheetName, start, end);
foreach (var country in countries)
{
Console.WriteLine("Country: {0} - Capital: {1} - Population: {2}", country.Country, country.Capital, country.Population);
}
}
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());
}
public List <ProjectDetails> ReadAllProjectInfo()
{
var projectFile = new LinqToExcel.ExcelQueryFactory(@CSV_FILE_PATH);
List <ProjectDetails> projectList = (from row in projectFile.Worksheet(CSV_SHEET_NAME)
let item = new ProjectDetails()
{
Repository = row["Repository"].Cast <string>(),
Language = row["Language"].Cast <string>(),
URL = row["URL"].Cast <string>(),
FilePath = row["FilePath"].Cast <string>(),
Line = row["Line"].Cast <int>(),
HTTPStatus = row["HTTPStatus"].Cast <string>(),
Domain = row["Domain"].Cast <string>(),
HasURLRevised = row["HasURLRevised"].Cast <string>()
}
select item).ToList();
return(projectList);
}
/// <summary>
/// Explicit sheet name example.
/// </summary>
/// <param name="sheetName">The name of the sheet to access.</param>
static void ExplicitSheetName(string sheetName)
{
Console.WriteLine("\n*** Explicit sheet name: {0} ***", sheetName);
// Open XL file.
var excel = new LinqToExcel.ExcelQueryFactory(m_xlFile);
// Get a list of countries taken from the given sheet.
// Note, instead of a sheet name, you can also pass a sheet index (start index 0).
// But be aware that when using sheet indices, the order is alphabetic and not the order as the sheets appear in Excel.
var countries = from country in excel.Worksheet <CountryInfo>(sheetName)
select country;
// Print all selected countries.
Console.WriteLine("All countries from sheet {0}", sheetName);
foreach (var country in countries)
{
Console.WriteLine("Country: {0} - Capital: {1} - Population: {2}", country.Country, country.Capital, country.Population);
}
}
/// <summary>
/// Access an Excel row through the LinqToExcel.Row class instead of copying it to own class.
/// </summary>
/// <param name="sheetName">The name of the sheet to access.</param>
static void AccessThroughRow(string sheetName)
{
Console.WriteLine("\n*** Access through LinqToExcel.Row instead own class ***");
// Open XL file.
var excel = new LinqToExcel.ExcelQueryFactory(m_xlFile);
// Get a list of countries from europe with population>20000000 taken from the given sheet.
// Note we use [] operator in where clause instead of selecting a field from a class.
// Also note that we need to cast the population field to an int in the where clause.
var countries = from country in excel.Worksheet(sheetName)
where country["Continent"] == "Europe" && country["Population"].Cast <int>() > 20000000
select country;
// Print all selected countries.
Console.WriteLine("All countries in Europe with population>20000000 using LinqToExcel.Row");
foreach (var country in countries)
{
Console.WriteLine("Country: {0} - Capital: {1} - Population: {2}", country["Country"], country["Capital"], country["Population"]);
}
}
/// <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);
}
}
/// <summary>
/// Access a sheet without header row.
/// </summary>
/// <param name="sheetName">The name of the sheet to access.</param>
static void Noheader(string sheetName)
{
Console.WriteLine("\n*** Access sheet with no header ***");
// Open XL file.
var excel = new LinqToExcel.ExcelQueryFactory(m_xlFile);
// Get a list of countries from europe with population>20000000 taken from the given sheet without header.
// Note that we need to use column index numbers.
// Also note that we need to cast the population field to an int in the where clause.
// No header can be combined with no range using the WorksheetRangeNoHeader() method.
var countries = from country in excel.WorksheetNoHeader(sheetName)
where country[2] == "Europe" && country[3].Cast <int>() > 20000000
select country;
// Print all selected countries.
Console.WriteLine("All countries in Europe with population>20000000 using indices");
foreach (var country in countries)
{
Console.WriteLine("Country: {0} - Capital: {1} - Population: {2}", country[0], country[1], country[3]);
}
}
public IEnumerable <ExposureRecord> ReadExposureRecords(string fileName)
{
var book = new LinqToExcel.ExcelQueryFactory(fileName);
var a = book.Worksheet(_worksheetName);
foreach (var row in a)
{
if (string.IsNullOrEmpty(row["Dyscyplina"].Cast <string>()))
{
break;
}
var item = new ExposureRecord()
{
Branch = row["Branża"].Cast <string>(),
Brand = row["Marka"].Cast <string>(),
Discipline = row["Dyscyplina"].Cast <string>(),
Month = row["Miesiąc"].Cast <string>(),
Quantity = row["Liczba ekspozycji"].Cast <long>(),
Value = row["Ekwiwalent reklamowy"].Cast <decimal>()
};
yield return(item);
}
}
static void Main(string[] args)
{
//Set route of file
var excelFile = new ExcelQueryFactory(@"C:\Users\Dell\Desktop\test.xlsx");
//Query for get all data to the file xsl
var Persons = from p in excelFile.Worksheet <Person>()
select p;
//Set Connection String of your Database
SqlConnection Connection = new SqlConnection(@"server=SQLEXPRESS; database=db-LinqToExcel; integrated security = true");
Connection.Open();
Console.WriteLine("Processing...");
foreach (Person person in Persons)
{
String Script = @"INSERT INTO Person(Name, Age) values ('" + person.Name + "'," + person.Age + ")";
SqlCommand Command = new SqlCommand(Script, Connection);
Command.ExecuteNonQuery();
}
Connection.Close();
Console.WriteLine("Ended process.");
}
private string ProcessProviderFile(string FilePath, string ErrorFilePath, string StrRemoteFile, string FileName)
{
var excel = new ExcelQueryFactory(FilePath);
//get excel rows
LinqToExcel.ExcelQueryFactory XlsInfo = new LinqToExcel.ExcelQueryFactory(FilePath);
List <ProviderExcelModel> oPrvToProcess =
(from x in XlsInfo.Worksheet <ProviderExcelModel>(0)
select x).ToList();
List <ProviderExcelResultModel> oPrvToProcessResult = new List <ProviderExcelResultModel>();
string ActualProvider = "";
List <string> ProvidersId = new List <string>();
FileName = FileName.Replace(Path.GetExtension(FileName), "");
var page = excel.GetWorksheetNames();
List <string> Columns = excel.GetColumnNames(page.FirstOrDefault()).ToList();
ProvidersId = oPrvToProcess.Where(x => x.ProviderPublicId != null).Select(x => x.ProviderPublicId).Distinct().ToList();
foreach (string ProviderPublicId in ProvidersId)
{
ProviderModel oProvider = new ProviderModel();
oProvider.RelatedCompany = ProveedoresOnLine.Company.Controller.Company.CompanyGetBasicInfo(ProviderPublicId);
if (ProveedoresOnLine.CompanyProvider.Controller.CompanyProvider.BlackListClearProvider(ProviderPublicId) && oProvider.RelatedCompany != null)
{
int AlertId = oProvider.RelatedCompany.CompanyInfo.Where(x => x.ItemInfoType.ItemId == (int)BackOffice.Models.General.enumCompanyInfoType.UpdateAlert).
Select(x => x.ItemInfoId).DefaultIfEmpty(0).FirstOrDefault();
oProvider.RelatedCompany.CompanyInfo.Add(new GenericItemInfoModel()
{
ItemInfoId = AlertId,
ItemInfoType = new CatalogModel()
{
ItemId = (int)BackOffice.Models.General.enumCompanyInfoType.UpdateAlert,
},
Enable = true,
Value = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
});
int StatusAlertId = oProvider.RelatedCompany.CompanyInfo.Where(x => x.ItemInfoType.ItemId == (int)BackOffice.Models.General.enumCompanyInfoType.Alert).
Select(x => x.ItemInfoId).DefaultIfEmpty(0).FirstOrDefault();
oProvider.RelatedCompany.CompanyInfo.Add(new GenericItemInfoModel()
{
ItemInfoId = StatusAlertId,
ItemInfoType = new CatalogModel()
{
ItemId = (int)BackOffice.Models.General.enumCompanyInfoType.Alert,
},
Enable = true,
Value = ((int)BackOffice.Models.General.enumBlackList.BL_DontShowAlert).ToString(),
});
//Save DateTime of last Update Data
ProveedoresOnLine.CompanyProvider.Controller.CompanyProvider.ProviderUpsert(oProvider);
}
}
#region New Data
oPrvToProcess = oPrvToProcess.GroupBy(x => x.IdentificationNumber).Select(grp => grp.First()).ToList();
oPrvToProcess.Where(prv => (!string.IsNullOrEmpty(prv.ProviderPublicId)) &&
(prv.BlackListStatus == "SI" || prv.BlackListStatus == "si" || prv.BlackListStatus == "Si") && (prv.Estado == "Activo")).All(prv =>
{
try
{
#region Operation
ProviderModel oProviderToInsert = new ProviderModel();
oProviderToInsert.RelatedCompany = new ProveedoresOnLine.Company.Models.Company.CompanyModel();
oProviderToInsert.RelatedCompany.CompanyInfo = new List <GenericItemInfoModel>();
oProviderToInsert.RelatedCompany.CompanyPublicId = prv.ProviderPublicId;
oProviderToInsert.RelatedBlackList = new List <BlackListModel>();
CompanyModel BasicInfo = new CompanyModel();
BasicInfo = ProveedoresOnLine.Company.Controller.Company.CompanyGetBasicInfo(prv.ProviderPublicId);
oProviderToInsert.RelatedBlackList.Add(new BlackListModel
{
BlackListStatus = new ProveedoresOnLine.Company.Models.Util.CatalogModel()
{
ItemId = (int)BackOffice.Models.General.enumBlackList.BL_ShowAlert,
},
User = SessionModel.CurrentLoginUser.Name + "_" + SessionModel.CurrentLoginUser.LastName,
FileUrl = StrRemoteFile,
BlackListInfo = new List <GenericItemInfoModel>()
});
var Rows = from c in excel.Worksheet(page.FirstOrDefault())
where c["ProviderPublicId"] == prv.ProviderPublicId && c["IdentificationNumber"] == prv.IdentificationNumber
select c;
//Load the BlackList info
foreach (string item in Columns)
{
int indexCollumn = Columns.IndexOf(item);
oProviderToInsert.RelatedBlackList.FirstOrDefault().BlackListInfo.Add(new GenericItemInfoModel()
{
ItemInfoId = 0,
ItemInfoType = new ProveedoresOnLine.Company.Models.Util.CatalogModel()
{
ItemName = item,
},
Value = Rows.First()[indexCollumn].Value.ToString(),
Enable = true,
});
}
List <ProviderModel> oProviderResultList = new List <ProviderModel>();
oProviderResultList.Add(ProveedoresOnLine.CompanyProvider.Controller.CompanyProvider.BlackListInsert(oProviderToInsert));
var idResult = oProviderResultList.FirstOrDefault().RelatedBlackList.Where(x => x.BlackListInfo != null).Select(x => x.BlackListInfo.Select(y => y.ItemInfoId)).FirstOrDefault();
#region Set Provider Info
oProviderToInsert.RelatedCompany.CompanyInfo.Add(new GenericItemInfoModel()
{
ItemInfoId = BasicInfo.CompanyInfo.Where(x => x.ItemInfoType.ItemId == (int)enumCompanyInfoType.Alert)
.Select(x => x.ItemInfoId).FirstOrDefault() != 0 ? BasicInfo.CompanyInfo.Where(x => x.ItemInfoType.ItemId == (int)enumCompanyInfoType.Alert)
.Select(x => x.ItemInfoId).FirstOrDefault() : 0,
ItemInfoType = new CatalogModel()
{
ItemId = (int)BackOffice.Models.General.enumCompanyInfoType.Alert,
},
Value = ((int)BackOffice.Models.General.enumBlackList.BL_ShowAlert).ToString(),
Enable = true,
});
//Set large value With the items found
oProviderToInsert.RelatedCompany.CompanyInfo.Add(new GenericItemInfoModel()
{
ItemInfoId = BasicInfo.CompanyInfo.Where(x => x.ItemInfoType.ItemId == (int)enumCompanyInfoType.ListId)
.Select(x => x.ItemInfoId).FirstOrDefault() != 0 ? BasicInfo.CompanyInfo.Where(x => x.ItemInfoType.ItemId == (int)enumCompanyInfoType.ListId)
.Select(x => x.ItemInfoId).FirstOrDefault() : 0,
ItemInfoType = new CatalogModel()
{
ItemId = (int)BackOffice.Models.General.enumCompanyInfoType.ListId,
},
LargeValue = string.Join(",", idResult),
Enable = true,
});
#endregion Set Provider Info
ProveedoresOnLine.Company.Controller.Company.CompanyInfoUpsert(oProviderToInsert.RelatedCompany);
oPrvToProcessResult.Add(new ProviderExcelResultModel()
{
PrvModel = prv,
Success = true,
Error = "Se ha validado el Proveedor '" + oProviderToInsert.RelatedCompany.CompanyPublicId + "'",
});
ActualProvider = prv.ProviderPublicId;
FileName = FileName + ".xls";
#endregion Operation
}
catch (Exception err)
{
oPrvToProcessResult.Add(new ProviderExcelResultModel()
{
PrvModel = prv,
Success = false,
Error = "Error :: " + err.Message + " :: " +
err.StackTrace +
(err.InnerException == null ? string.Empty :
" :: " + err.InnerException.Message + " :: " +
err.InnerException.StackTrace),
});
}
return(true);
});
#endregion New Data
//save log file
#region Error log file
try
{
using (System.IO.StreamWriter sw = new System.IO.StreamWriter(ErrorFilePath))
{
string strSep = ";";
sw.WriteLine
(
"\"ProviderPublicId\"" + strSep +
"\"BlackListStatus\"" + strSep +
"\"Success\"" + strSep +
"\"Message\"");
oPrvToProcessResult.All(lg =>
{
sw.WriteLine
(
"\"" + lg.PrvModel.ProviderPublicId + "\"" + strSep +
"\"" + lg.PrvModel.BlackListStatus + "\"" + strSep +
"\"" + lg.Success + "\"" + strSep +
"\"" + lg.Error + "\"");
return(true);
});
sw.Flush();
sw.Close();
}
//load file to s3
string strRemoteFile = ProveedoresOnLine.FileManager.FileController.LoadFile
(ErrorFilePath,
BackOffice.Models.General.InternalSettings.Instance[BackOffice.Models.General.Constants.C_Settings_File_ExcelDirectory].Value);
//remove temporal file
if (System.IO.File.Exists(ErrorFilePath))
{
System.IO.File.Delete(ErrorFilePath);
}
return(strRemoteFile);
}
catch { }
return(null);
#endregion Error log file
}
public override void Execute()
{
#line 5 "..\..\Views\nvTTThongKe\Import.cshtml"
ViewBag.Title = "Import";
var excel = new LinqToExcel.ExcelQueryFactory(ViewBag.File);
#line default
#line hidden
WriteLiteral("<table>\r\n<tr>\r\n <th>MaNV</th>\r\n <th>KhoaGiangDayChinh</th>\r\n <th>KhoaGia" +
"ngDay</th>\r\n <th>NganhGiangDayChinh</th>\r\n <th>NganhGiangDay</th>\r\n</tr>\r\n" +
"");
#line 17 "..\..\Views\nvTTThongKe\Import.cshtml"
using (var db = new HRMDBEntities())
{
foreach (var row in excel.Worksheet())
{
var MaNV = row["(MaNV)"].Value as string;
var NhanVien = db.NhanViens.All.SingleOrDefault(nv => nv.MaNV == MaNV);
if (NhanVien == null)
{
#line default
#line hidden
WriteLiteral(" <tr><td>");
#line 25 "..\..\Views\nvTTThongKe\Import.cshtml"
Write(MaNV);
#line default
#line hidden
WriteLiteral(" not found!</td></tr>\r\n");
#line 26 "..\..\Views\nvTTThongKe\Import.cshtml"
break;
}
var maLoai = row["(PhanLoaiCBGVNV)"].Value as string;
var Loai = db.dmPhanLoaiCBGVNV.SingleOrDefault(l => l.maPhanLoaiCBGVNV == maLoai);
if (Loai == null && !String.IsNullOrEmpty(maLoai))
{
#line default
#line hidden
WriteLiteral(" <tr><td>");
#line 32 "..\..\Views\nvTTThongKe\Import.cshtml"
Write(maLoai);
#line default
#line hidden
WriteLiteral(" not found!</td></tr>\r\n");
#line 33 "..\..\Views\nvTTThongKe\Import.cshtml"
break;
}
var maKhoa = row["(KhoaGiangDayChinh)"].Value as string;
var Khoa = db.dmKhoaGiangDay.SingleOrDefault(k => k.maKhoaGiangDay == maKhoa);
if (Khoa == null && !String.IsNullOrEmpty(maKhoa))
{
#line default
#line hidden
WriteLiteral(" <tr><td>");
#line 39 "..\..\Views\nvTTThongKe\Import.cshtml"
Write(maKhoa);
#line default
#line hidden
WriteLiteral(" not found!</td></tr>\r\n");
#line 40 "..\..\Views\nvTTThongKe\Import.cshtml"
break;
}
var maKhoas = (row["(KhoaGiangDay)"].Value as string ?? "").Split(',').Where(m => !String.IsNullOrEmpty(m));
var Khoas = (from m in maKhoas select db.dmKhoaGiangDay.SingleOrDefault(k => k.maKhoaGiangDay == m));
if (Khoas.Contains(null) && !String.IsNullOrEmpty(String.Join("", maKhoas)))
{
#line default
#line hidden
WriteLiteral(" <tr><td>");
#line 46 "..\..\Views\nvTTThongKe\Import.cshtml"
Write(maKhoas);
#line default
#line hidden
WriteLiteral(" not found!</td></tr>\r\n");
#line 47 "..\..\Views\nvTTThongKe\Import.cshtml"
break;
}
var maNganh = row["(NganhGiangDayChinh)"].Value as string;
var Nganh = db.dmNganhGiangDay.SingleOrDefault(n => n.maNganhGiangDay == maNganh);
if (Nganh == null && !String.IsNullOrEmpty(maNganh))
{
#line default
#line hidden
WriteLiteral(" <tr><td>");
#line 53 "..\..\Views\nvTTThongKe\Import.cshtml"
Write(maNganh);
#line default
#line hidden
WriteLiteral(" not found!</td></tr>\r\n");
#line 54 "..\..\Views\nvTTThongKe\Import.cshtml"
break;
}
var maNganhs = (row["(NganhGiangDay)"].Value as string ?? "").Split(',').Where(m => !String.IsNullOrEmpty(m));
var Nganhs = (from m in maNganhs select db.dmNganhGiangDay.SingleOrDefault(n => n.maNganhGiangDay == m));
if (Nganhs.Contains(null) && !String.IsNullOrEmpty(String.Join("", maNganhs)))
{
#line default
#line hidden
WriteLiteral(" <tr><td>");
#line 60 "..\..\Views\nvTTThongKe\Import.cshtml"
Write(maNganhs);
#line default
#line hidden
WriteLiteral(" not found!</td></tr>\r\n");
#line 61 "..\..\Views\nvTTThongKe\Import.cshtml"
break;
}
var nvTTThongKe = NhanVien.nvTTThongKes.SingleOrDefault();
if (nvTTThongKe == null)
{
using (var controller = new nvTTThongKeController())
{
controller.Create(NhanVien.id);
}
}
nvTTThongKe.dmPhanLoaiCBGVNV = Loai;
nvTTThongKe.dmKhoaGiangDay = Khoa;
NhanVien.nvKhoaGiangDays.ToList().ForEach(k => db.Entry(k).State = EntityState.Deleted);
Khoas.ToList().ForEach(k => db.nvKhoaGiangDay.Add(new nvKhoaGiangDay {
NV_id = NhanVien.id, KhoaGiangDay = k.id
}));
nvTTThongKe.dmNganhGiangDay = Nganh;
NhanVien.nvNganhGiangDays.ToList().ForEach(n => db.Entry(n).State = EntityState.Deleted);
Nganhs.ToList().ForEach(n => db.nvNganhGiangDay.Add(new nvNganhGiangDay {
NV_id = NhanVien.id, NganhGiangDay = n.id
}));
db.Entry(nvTTThongKe).State = EntityState.Modified;
#line default
#line hidden
WriteLiteral(" <tr>\r\n <td>");
#line 80 "..\..\Views\nvTTThongKe\Import.cshtml"
Write(row["(MaNV)"]);
#line default
#line hidden
WriteLiteral("</td>\r\n <td>");
#line 81 "..\..\Views\nvTTThongKe\Import.cshtml"
Write(row["(KhoaGiangDayChinh)"]);
#line default
#line hidden
WriteLiteral("</td>\r\n <td>");
#line 82 "..\..\Views\nvTTThongKe\Import.cshtml"
Write(row["(KhoaGiangDay)"]);
#line default
#line hidden
WriteLiteral("</td>\r\n <td>");
#line 83 "..\..\Views\nvTTThongKe\Import.cshtml"
Write(row["(NganhGiangDayChinh)"]);
#line default
#line hidden
WriteLiteral("</td>\r\n <td>");
#line 84 "..\..\Views\nvTTThongKe\Import.cshtml"
Write(row["(NganhGiangDay)"]);
#line default
#line hidden
WriteLiteral("</td>\r\n </tr>\r\n");
#line 86 "..\..\Views\nvTTThongKe\Import.cshtml"
}
db.SaveChanges();
}
#line default
#line hidden
WriteLiteral("</table>\r\n");
}
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++;
}
