C# ExcelQueryFactory.WorksheetRange方法代码示例

        public void use_row_where_null()
        {
            var factory = new ExcelQueryFactory(_excelFileName + "x");
            var companies = from c in factory.WorksheetRange("A1", "D4", "NullCells")
                            where c["EmployeeCount"] == null
                            select c;

            Assert.AreEqual(2, companies.Count(), "Count");
        }

 //импорт данных из таблицы Excel
 private void ImportFromExcel(string filename)
 {
     try
     {
         var excel = new ExcelQueryFactory();
         excel.FileName = filename;
         //указываем, что первый столбец называется IP Address
         excel.AddMapping<Network>(x => x.Address, "IP Address");
         //на листе Network
         var data = from c in excel.Worksheet<Network>("Network")
                    select c;
         //указываем, что начало обработки данных в указанном диапазоне
         var network = from c in excel.WorksheetRange<Network>("A1", "B1")
                       select c;
         dataGridViewImport.DataSource = data.ToList();
     }
     catch (Exception ex)
     {
         MessageBox.Show(ex.Message, "Exception", MessageBoxButtons.OK, MessageBoxIcon.Warning);
     }
 }

        private static void TestCase10_LinqToExcel()
        {
            //https://github.com/paulyoder/LinqToExcel
            var table = ExcelQueryFactory.Worksheet("Info",
                Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Desktop), "Test.xlsx"));
            var excelQueryFactory =
                new ExcelQueryFactory(Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Desktop),
                    "Test.xlsx"))
                {
                    UsePersistentConnection = true,
                    ReadOnly = true,
                    DatabaseEngine = DatabaseEngine.Ace,
                    TrimSpaces = TrimSpacesType.Both,
                    StrictMapping = StrictMappingType.Both
                };
            var columnNames = excelQueryFactory.GetColumnNames("Info");
            try
            {
                var infos = excelQueryFactory.Worksheet<Information>("Info").Where(p => p.Status == 1);

                var Num = excelQueryFactory.WorksheetRange("A3", "B103", "Sheet1");
            }
            finally
            {
                excelQueryFactory.Dispose();
            }
        }

        public void Run(string budgetId, string userId, string file)
        {
            var excel = new ExcelQueryFactory(file);
            var anni = new[] { 2013, 2014 };
            var movements = new List<Movimento>();
            foreach (var anno in anni)
            {
                //movements.AddRange(excel.Worksheet<Movement>(anno + "")
                //    .Where(r => r.Data != DateTime.MinValue));

                var laura = excel.WorksheetRange<Movimento>("B6", "E10000", anno + "")
                     .Where(r => r.Data != DateTime.MinValue)
                     .ToList();
                laura.ForEach(m => m.DistributionKey = "Laura");

                var valerio = excel.WorksheetRange<Movimento>("G6", "J10000", anno + "")
                     .Where(r => r.Data != DateTime.MinValue)
                     .ToList();
                valerio.ForEach(m => m.DistributionKey = "Valerio");

                var comune = excel.WorksheetRange<Movimento>("L6", "O10000", anno + "")
                     .Where(r => r.Data != DateTime.MinValue)
                     .ToList();


                movements.AddRange(laura);
                movements.AddRange(valerio);
                movements.AddRange(comune);
            }

            //var tasse = movements.GroupBy(g => g.Categoria).ToList();

            Console.WriteLine("Read {0} movements from {1}", movements.Count, file);
            movements = movements.Where(r => r.Categoria != "Arancio").OrderBy(d => d.Data).ToList();

            
            var importer = new ImportManager(_cm, _pm);
            var categorie = movements.Select(s => s.Categoria).ToArray();
            importer.ImportCategoriesByName(categorie, budgetId, userId);

            var categories = _pm.GetCategories().GetBudgetsCategories(budgetId);

            var createLine = _cm.Create<CreateLine>();
            //foreach (var m in movements.Where(r=> r.Categoria != "Arancio"))
            //    createLine(m.ToCreateLine(new BudgetId(budgetId), userId, categories));

            DateTime last = DateTime.MinValue;
            foreach (var m in movements)
            {
                last = DateTime.Now;
                createLine(m.ToCreateLine(last, new BudgetId(budgetId), userId, categories));
            }


            var bp = _pm.GetBudgetLinesProjection(budgetId);
            var galt = bp.GetAllLines(last);
            galt.Wait();
            var lines = galt.Result;

            Console.WriteLine("Loaded {0} movements into {1}", lines.Count(), budgetId);
        }

        public ActionResult Index(HttpPostedFileBase file)
        {
            List<ProductMap> listpro = new List<ProductMap>();
            List<ProductMap> listerror = new List<ProductMap>();
            List<List<ProductMap>> listduplicate = new List<List<ProductMap>>();

            //Get path after upload file to process
            String path = uploadFile(file);
            var excel = new ExcelQueryFactory();
            excel.FileName = path;
            // Get all product from excel to list
            try
            {
                var list = from x in excel.WorksheetRange("A2", "D900000")
                           select new ProductMap
                           {
                               stt = x["STT"],
                               ten = x["Tên"],
                               trongso = x["Trọng Số"],
                               loai = x["Loại"]
                           };
                listpro = list.ToList();
            }

            catch (Exception e)
            {

            }
            // Gán số thứ tự

            int sttp = 0;
            for (int i = 0; i < listpro.Count; i++)
            {
                listpro[i].stt = sttp.ToString();
                sttp++;
            }

            //Kiểm tra xem có trong database chưa có rồi thì cho vào list đã tồn tại cho người dùng xem.

            using (CPS_SolutionEntities db = new CPS_SolutionEntities())
            {
                List<ProductMap> listExistedProduct = new List<ProductMap>();
                List<Hardware> listNameIndb = new List<Hardware>();
                var listAlias = (from x in db.Hardwares select x);
                listNameIndb = listAlias.ToList();
                for (int i = 0; i < listpro.Count; i++)
                {
                    for (int j = 0; j < listNameIndb.Count; j++)
                    {
                        if (listpro[i].ten.Equals(listNameIndb[j].Name))
                        {
                            listExistedProduct.Add(listpro[i]);
                            listpro.RemoveAt(i);
                            i--;
                            break;
                        }
                    }
                }
                List<Dictionary> listNameMapIndb = new List<Dictionary>();
                var listMap = (from x in db.Dictionaries select x);
                listNameMapIndb = listMap.ToList();
                for (int i = 0; i < listpro.Count; i++)
                {
                    for (int j = 0; j < listNameMapIndb.Count; j++)
                    {
                        if (listpro[i].ten.Equals(listNameMapIndb[j].Name))
                        {
                            listExistedProduct.Add(listpro[i]);
                            listpro.RemoveAt(i);
                            i--;
                            break;
                        }
                    }
                }
                TempData["listExistedProduct"] = listExistedProduct;
            }
            // call function listerror
            listerror = ListErrorProduct(listpro);
            // call function listduplicate
            listduplicate = ListDuplicateProduct(listpro);

            Session["listproduct"] = listpro;
            Session["listerror"] = listerror;
            Session["listduplicate"] = listduplicate;
            // redirect back to the index action to show the form once again
            //   TempData["listproduct"] = listpro;
            //   ViewBag.listproduct = listpro;
            return RedirectToAction("Index");
            // return View();
        }

        public ActionResult Index(HttpPostedFileBase file)
        {
            List<ProductMap> listpro = new List<ProductMap>();
            List<ProductMap> listerror = new List<ProductMap>();
            List<List<ProductMap>> listduplicate = new List<List<ProductMap>>();

            //Get path after upload file to process
            String path = uploadFile(file);
            var excel = new ExcelQueryFactory();
            excel.FileName = path;
            // Get all product from excel to list
            try
            {
                var list = from x in excel.WorksheetRange("A2", "D900000")
                           select new ProductMap
                           {
                               stt = x["STT"],
                               ten = x["Tên"],
                               trongso = x["Trọng Số"],
                               loai = x["Loại"]
                           };
                listpro = list.ToList();
            }
            catch (Exception e)
            {

            }
            // call function listerror
            listerror = ListErrorProduct(listpro);
            // call function listduplicate
            listduplicate = ListDuplicateProduct(listpro);

            Session["listproduct"] = listpro;
            Session["listerror"] = listerror;
            Session["listduplicate"] = listduplicate;
            // redirect back to the index action to show the form once again
            //   TempData["listproduct"] = listpro;
            //   ViewBag.listproduct = listpro;
            return RedirectToAction("Index");
            // return View();
        }

        public ActionResult Index(HttpPostedFileBase file)
        {
            List<LapData> listpro = new List<LapData>();
            List<LapData> listerror = new List<LapData>();
            List<List<LapData>> listduplicate = new List<List<LapData>>();

            //Get path after upload file to process
            String path = uploadFile(file);
            var excel = new ExcelQueryFactory();
            excel.FileName = path;
            // Get all product from excel to list
            try
            {
                var list = from x in excel.WorksheetRange("A2", "J900000")
                           select new LapData
                           {
                               stt = x["STT"],
                               Name = x["Tên"],
                               Imagelink = x["Địa chỉ ảnh"],
                               CPU = x["CPU"],
                               VGA = x["VGA"],
                               HDD = x["HDD"],
                               Display = x["Display"],
                               RAM = x["RAM"],
                               Price = x["Price"],
                               Url   = x["URL"]
                           };
                listpro = list.ToList();
            }

            catch (Exception e)
            {
            }
            // Gán số thứ tự
            int sttp = 0;
            for (int i = 0; i < listpro.Count; i++)
            {
                sttp++;
                listpro[i].stt = sttp.ToString();

            }
            //Kiểm tra xem có trong database chưa có rồi thì cho vào list đã tồn tại cho người dùng xem.
            #region
            using (CPS_SolutionEntities db = new CPS_SolutionEntities())
            {
                List<LapData> listExistedLap = new List<LapData>();
                List<AliasProduct> listNameIndb = new List<AliasProduct>();
                var listAlias = (from x in db.AliasProducts select x);
                listNameIndb = listAlias.ToList();
                for (int i = 0; i < listpro.Count; i++)
                {
                    for (int j = 0; j < listNameIndb.Count; j++)
                    {
                        if (listpro[i].Name.Equals(listNameIndb[j].Name))
                        {
                            listExistedLap.Add(listpro[i]);
                            listpro.RemoveAt(i);
                            i--;
                            break;
                        }
                    }

                }
                TempData["listExistedLap"] = listExistedLap;
            }
            #endregion
            // call function listerror
            listerror = ListErrorProduct(listpro);
            // call function listduplicate
            listduplicate = ListDuplicateProduct(listpro);

            Session["listproductLap"] = listpro;
            Session["listerrorLap"] = listerror;
            Session["listduplicateLap"] = listduplicate;
            Session["danhsachlaploi"] = null;
            // redirect back to the index action to show the form once again
            //   TempData["listproduct"] = listpro;
            //   ViewBag.listproduct = listpro;
            return RedirectToAction("Index");
            // return View();
        }