C# ExcelQueryFactory.Worksheet方法代码示例

        static void Main(string[] args)
        {
            const string excelPath = "c:/temp/excel/Employees.xlsx";

            var excel = new ExcelQueryFactory(excelPath);

            var employees = from e in excel.Worksheet<Employee>("Employee")
                                   select e;

            Console.WriteLine("All Employees");
            Console.WriteLine("");
            WriteInfo(employees);
            Console.WriteLine("");
            Console.WriteLine("");

            var managers = from e in excel.Worksheet<Employee>("Employee")
                            where e.IsManager
                            select e;

            Console.WriteLine("Managers");
            Console.WriteLine("");
            WriteInfo(managers);
            Console.WriteLine("");
            Console.WriteLine("");

            var regularEmployees = from e in excel.Worksheet<Employee>("Employee")
                           where e.IsManager == false
                           select e;

            Console.WriteLine("Regular Employees");
            Console.WriteLine("");
            WriteInfo(regularEmployees);
            Console.WriteLine("");
            Console.Read();
        }

        public List<string> Import(string path, IEnumerable<string> regions)
        {
            var factory = new ExcelQueryFactory {FileName = path};
            var message = (from region in regions
                let stats = (from c in factory.Worksheet<CdmaRegionStatExcel>(region)
                    where c.StatDate > DateTime.Today.AddDays(-30) && c.StatDate <= DateTime.Today
                    select c).ToList()
                let count = _regionStatRepository.Import<ICdmaRegionStatRepository, CdmaRegionStat, CdmaRegionStatExcel>(stats)
                select "完成导入区域：'" + region + "'的日常指标导入" + count + "条").ToList();

            var topDrops = (from c in factory.Worksheet<TopDrop2GCellExcel>(TopDrop2GCellExcel.SheetName)
                            select c).ToList();
            var drops = _top2GRepository.Import<ITopDrop2GCellRepository, TopDrop2GCell, TopDrop2GCellExcel>(topDrops);
            message.Add("完成TOP掉话小区导入" + drops + "个");

            var topConnections = (from c in factory.Worksheet<TopConnection3GCellExcel>(TopConnection3GCellExcel.SheetName)
                                  select c).ToList();
            var connections =
                _top3GRepository.Import<ITopConnection3GRepository, TopConnection3GCell, TopConnection3GCellExcel>(
                    topConnections);
            message.Add("完成TOP连接小区导入" + connections + "个");

            var topConnection2Gs = (from c in factory.Worksheet<TopConnection2GExcel>(TopConnection2GExcel.SheetName)
                select c).ToList();
            var connection2Gs =
                _topConnection2GRepository.Import<ITopConnection2GRepository, TopConnection2GCell, TopConnection2GExcel>
                    (topConnection2Gs);
            message.Add("完成TOP呼建小区导入" + connection2Gs + "个");

            return message;
        }

        private void button1_Click(object sender, EventArgs e)
        {
            OpenFileDialog fileDialog = new OpenFileDialog();

            if (fileDialog.ShowDialog() != System.Windows.Forms.DialogResult.OK)
            {
                return;
            }

            var sheet = new ExcelQueryFactory();
            sheet.FileName = fileDialog.FileName;

            var dinvList = sheet.Worksheet<DINV_HDR>(0);

            foreach (var item in dinvList)
            {

            }

            var list = sheet.Worksheet(0).ToList();

            foreach (var item in list)
            {

            }

            foreach (var item in sheet.Worksheet(0))
            {

            }
        }

 public void ImportCdmaParameters(string path)
 {
     var repo = new ExcelQueryFactory { FileName = path };
     BtsExcels = (from c in repo.Worksheet<BtsExcel>("基站级")
         select c).ToList();
     CdmaCellExcels = (from c in repo.Worksheet<CdmaCellExcel>("小区级")
         select c).ToList();
 }

        private void btnSelectFile_Click(object sender, EventArgs e)
        {
            OpenFileDialog openFileDialog = new OpenFileDialog();
            openFileDialog.InitialDirectory = Environment.CurrentDirectory;
            // openFileDialog.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Personal);
            openFileDialog.Filter = "Excel(*.xlsx)|*.xlsx|Excel 2000-2003(*.xls)|*.xls|CSV(*.csv)|*.csv|所有文件(*.*)|*.*";
            if (openFileDialog.ShowDialog(this) == DialogResult.OK)
            {
                Cursor.Current = Cursors.WaitCursor;
                string FileName = openFileDialog.FileName;

                var execelfile = new ExcelQueryFactory(FileName);

                this.txtContact.Text = FileName;
                this.txtContact.Enabled = false;

                List<string> sheetNames=  execelfile.GetWorksheetNames().ToList();
               
                
                //星级信息
                List<Row> agentStar = execelfile.Worksheet(0).ToList(); ;

                if (agentStar != null && agentStar.Count > 0)
                {
                   
                    this.btnImport.Enabled = true;
                    dgAgentStar.Rows.Clear();
                    dgAgentStar.Columns.Clear();
                    foreach (String coloumn in agentStar[0].ColumnNames)
                    {
                        this.dgAgentStar.Columns.Add(coloumn, coloumn);
                    }

                    for (int i = 0; i < agentStar.Count; i++)
                    {
                        if (String.IsNullOrEmpty(agentStar[i][0]))
                        {
                            continue;
                        }
                        dgAgentStar.Rows.Add();
                        DataGridViewRow row = dgAgentStar.Rows[i];
                        foreach (String coloumn in agentStar[0].ColumnNames)
                        {
                            row.Cells[coloumn].Value = agentStar[i][coloumn];
                        }

                    }
                }


                dgAgentStar.ColumnHeadersDefaultCellStyle.WrapMode = DataGridViewTriState.False;
                dgAgentStar.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;

                dgAgentStar.AutoResizeColumns();

               

                Cursor.Current = Cursors.Default;
            }
        }

 public ActionResult ReconciliationUpload(Guid fileid)
 {
     var model = new List<UploadReconciliationModel>();
     var uploadFilePath = ConfigurationManager.AppSettings["UploadFilePath"];
     if (!Directory.Exists(uploadFilePath))
     {
         Directory.CreateDirectory(uploadFilePath);
     }
     var fileNamePrefix = fileid.ToString();
     var file = System.Web.HttpContext.Current.Request.Files[0];
     if (file.ContentLength > 0)
     {
         var fileName = Path.GetFileName(file.FileName);
         //var fileExtension = Path.GetExtension(fileName);
         //var newFileName = fileNamePrefix + fileExtension;
         //var fileFullPath = uploadFilePath + newFileName;
         var fileFullPath = uploadFilePath + fileNamePrefix;
         try
         {
             file.SaveAs(fileFullPath);
             //return RedirectToAction("Reconciliation", new { filePath = uploadFilePath });
             var excel = new ExcelQueryFactory(fileFullPath);
             model = excel.Worksheet<UploadReconciliationModel>(0).ToList();
         }
         catch (Exception)
         {
             System.IO.File.Delete(fileFullPath);
         }
     }
     return View(model.ToArray());
 }

       public bool ImportStudentFromExcel(string filePath)
       {
           bool result = false;
           if (!string.IsNullOrEmpty(filePath))
           {
               var excelFile = new ExcelQueryFactory(filePath);
               List<string> sheetnames = excelFile.GetWorksheetNames().ToList();

               //excelFile.AddMapping<PayrollItemDataEntryCustom>(x => x.Remarks, PayrollItemDataEntry.Remarks.ToString());
               //excelFile.AddMapping<PayrollItemDataEntryCustom>(x => x.filePath, PayrollItemDataEntry.filePath.ToString());
               //excelFile.AddMapping<PayrollItemDataEntryCustom>(x => x.sheetNames, "tstSheet1");//PayrollItemDataEntry.sheetNames.ToString()

               excelFile.AddMapping<Student>(x => x.SID, "SID");
               excelFile.AddMapping<Student>(x => x.Name,"Name");
               excelFile.AddMapping<Student>(x => x.Minor, "Minor");
               excelFile.AddMapping<Student>(x => x.Major, "Major");
               excelFile.AddMapping<Student>(x => x.Division, "Division"); 

               excelFile.StrictMapping = StrictMappingType.ClassStrict;
               excelFile.TrimSpaces = TrimSpacesType.Both;
               excelFile.ReadOnly = true;

               var AllExcellData = (from ExcelData in excelFile.Worksheet(0)
                                    select ExcelData).ToList();
           }
           return result;
       }

        public ActionResult Index()
        {
            foreach (string upload in Request.Files)
            {
            //    if (!Request.Files[upload]) continue;
                string path = AppDomain.CurrentDomain.BaseDirectory + "uploads/";
                string filename = Path.GetFileName(Request.Files[upload].FileName);
                string pathToExcelFile = Path.Combine(path, filename);

                string sheetName = "Sheet1";

                Request.Files[upload].SaveAs(pathToExcelFile);

                var excelFile = new ExcelQueryFactory(pathToExcelFile);
                var data = from a in excelFile.Worksheet<ExcelData>(sheetName) select a;

                using (var ctx = new Context())
                {
                    foreach (var a in data)
                    {
                        ctx.ExcelData.Add(a);
                        //Console.WriteLine(string.Format(artistInfo, a.Account, a.Account));
                    }

                    ctx.SaveChanges();
                }

            }

            return View();
        }

 public static IList<CollectionImportModel> Read(string fileName)
 {
     var data = new ExcelQueryFactory(fileName);
     SetupColumnMapping(data);
     SetupTransforms(data);
     return data.Worksheet<CollectionImportModel>().ToList();
 }

        private static List<PuzzleGame> GetGamesFromPuzzleGameWorksheet(ExcelQueryFactory excel, int puzzleSubGroupId)
        {
            //Get all games for a sub group
            var games = from c in excel.Worksheet("PuzzleGame")
                        select new
                                   {
                                       PuzzleGameId = c["PuzzleGameId"].Cast<int>(),
                                       PuzzleSubGroupId = c["PuzzleSubGroupId"].Cast<int>(),
                                       Word = c["Word"].Cast<string>(),
                                       Hint = c["Hint"].Cast<string>()
                                   };

            var gamelist = games.ToList();
            var grouped = from c in gamelist
                          where c.PuzzleSubGroupId == puzzleSubGroupId
                          group c by c.PuzzleGameId
                          into g
                          select new {PuzzleGameId = g.Key, Games = g};

            var puzzleGames = new List<PuzzleGame>();
            foreach (var gamesSet in grouped)
            {
                var puzzleGame = new PuzzleGame()
                                     {PuzzleGameId = gamesSet.PuzzleGameId, Words = new Dictionary<string, string>()};
                foreach (var game in gamesSet.Games)
                {
                    puzzleGame.Words.Add(game.Word, game.Hint);
                    Console.WriteLine(game.Word);
                }
                puzzleGames.Add(puzzleGame);
            }

            return puzzleGames;
        }

 protected void btnUpload_FileSelected(object sender, EventArgs e)
 {
     if (btnUpload.HasFile)
     {
         string fileName = btnUpload.ShortFileName;
         if (!fileName.EndsWith(".xlsx"))
         {
             Alert.Show("只支持xlsx文件类型！");
             return;
         }
         string path = ConfigHelper.GetStringProperty("DataFilePath", @"D:\root\Int\data\") + "tmp\\" + fileName;
         btnUpload.SaveAs(path);
         var execelfile = new ExcelQueryFactory(path);
         execelfile.AddMapping<UserInfo>(x => x.Code, "登录帐号");
         execelfile.AddMapping<UserInfo>(x => x.Name, "姓名");
         execelfile.AddMapping<UserInfo>(x => x.NameEn, "英文名");
         execelfile.AddMapping<UserInfo>(x => x.Departments, "部门");
         execelfile.AddMapping<UserInfo>(x => x.JobName, "职位");
         execelfile.AddMapping<UserInfo>(x => x.Phone, "电话");
         execelfile.AddMapping<UserInfo>(x => x.Email, "电子邮箱");
         execelfile.AddMapping<UserInfo>(x => x.LeaderCode, "上级领导");
         var list = execelfile.Worksheet<UserInfo>(0).ToList();
         Import(list);
         this.userGrid.PageIndex = 0;
         GridBind();
         Alert.Show("上传成功");
     }
 }

        public static IEnumerable<Route> Import(string filename, ClimbrContext context)
        {
            var excel = new ExcelQueryFactory(filename);
            var sheetRoutes = excel.Worksheet<SheetRoute>()
                .Where(r => r.Grade != "#REF!" && r.Grade != null)
                .ToList();

            for (int i = 1; i < sheetRoutes.Count; i++)
            {
                if (sheetRoutes[i].Line == null || sheetRoutes[i].Line == "#REF!")
                {
                    sheetRoutes[i].Line = sheetRoutes[i - 1].Line;
                }
            }

            var routes = sheetRoutes.Select(
                sr => new Route
                {
                    Color = context.Colors.Single(c => c.Name.Equals(sr.Colour, StringComparison.InvariantCultureIgnoreCase)),
                    Grade = context.Grades.Single(g => g.Name.Equals(sr.Grade, StringComparison.InvariantCultureIgnoreCase)),
                    Name = sr.Line
                })
                .ToList();

            return routes;
        }

        private void button1_Click(object sender, EventArgs e)
        {
            OpenFileDialog fdlg = new OpenFileDialog();
            if (DialogResult.OK == fdlg.ShowDialog())
            {
                var excel = new ExcelQueryFactory(fdlg.FileName);
                var indianaCompanies = from c in excel.Worksheet("Sheet1")
                                       select c;
                int ix = 0;
                int il = 0;
                foreach (var item in indianaCompanies)
                {
                    TblInfor newInfo = new TblInfor();

                    string content = "<b>" + item[1].ToString().Trim() + "</b> <br />"
                                    + "" + item[10].ToString().Trim()
                                    + "<br>Khu vực: " + item[9].ToString().Trim()
                                   + "<br>ĐC: " + item[4].ToString().Trim()
                                   + "<br> Số Phòng: " + item[6].ToString().Trim()
                                   + "<br> ĐT: " + item[2].ToString().Trim() + " - FAX: " + item[3].ToString().Trim()
                                   + "<br> Email: " + item[5].ToString().Trim()
                                    + "<br> Chủ đầu tư: " + item[7].ToString().Trim()
                                   + "<br>Giám đốc: " + item[8].ToString().Trim();

                    string tag = "Khách sạn, khach san, ks, " + item[11].ToString().Trim() + ", " + item[2].ToString().Trim();

                    newInfo.CreateDate = DateTime.Now;
                    newInfo.CreateUserID = Guid.Parse("b0b6997c-282b-4aff-b286-b0820cdb0100");
                    newInfo.ID = Guid.NewGuid();
                    newInfo.Category = Categories.KhachSan;
                    newInfo.Location = item[9].ToString().Trim();
                    newInfo.InfoContent = HtmlRemoval.StripTagsRegexCompiled(content);
                    newInfo.InfoContentHtml = content;
                    newInfo.InfoTag = tag;
                    newInfo.InfoTitle = item[1].ToString().Trim() + " " + item[9].ToString().Trim();
                    newInfo.InfoType = InformationType.NoLimit;
                    newInfo.Status = InformationStatus.Approved;

                    newInfo.UpdateDate = DateTime.Now;
                    newInfo.UpdateUserID = Guid.Parse("b0b6997c-282b-4aff-b286-b0820cdb0100");

                    try
                    {
                        BBLInfo b = new BBLInfo();
                        b.InsertOrUpdate(newInfo);

                        ix++;
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine("E - > " + ex.Message);
                        // DevExpress.XtraEditors.XtraMessageBox.Show(DevExpress.LookAndFeel.UserLookAndFeel.Default, this.FindForm(), ex.Message, "Có sự cố", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                        il++;
                    }
                }

                Console.WriteLine(ix + " | " + il);
            }
        }

 private IEnumerable<ExcelRow> GetExcelRowEnumerableFromExcelFile(string filePath)
 {
     var excel = new ExcelQueryFactory();
     excel.FileName = filePath;
     IEnumerable<ExcelRow> query = from e in excel.Worksheet<ExcelRow>(0)
                                   select e;
     return query;
 }

 public void bad_column_mapping_in_where_clause()
 {
     var excel = new ExcelQueryFactory(_excelFileName);
     excel.AddMapping<CompanyWithCity>(x => x.City, "Town");
     var list = (from x in excel.Worksheet<CompanyWithCity>("Sheet1")
                 where x.City == "Omaha"
                 select x).ToList();
 }