Ejemplo n.º 1
0
    /// <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";
        }
    }
Ejemplo n.º 2
0
    /// <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()));
        }
Ejemplo n.º 4
0
    /// <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);
        }
    }
Ejemplo n.º 5
0
    /// <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);
        }
    }
Ejemplo n.º 6
0
        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)
                    );
            }
        }
Ejemplo n.º 7
0
    /// <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);
        }
    }
Ejemplo n.º 8
0
        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());
        }
Ejemplo n.º 9
0
        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);
        }
Ejemplo n.º 10
0
    /// <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);
        }
    }
Ejemplo n.º 11
0
    /// <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"]);
        }
    }
Ejemplo n.º 12
0
    /// <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);
        }
    }
Ejemplo n.º 13
0
    /// <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]);
        }
    }
Ejemplo n.º 14
0
        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);
            }
        }
Ejemplo n.º 15
0
        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
        }
Ejemplo n.º 17
0
        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");
        }
Ejemplo n.º 18
0
        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();
        }
Ejemplo n.º 19
0
        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++;
        }