C# ExcelQueryFactory.GetWorksheetNames方法代码示例

        static void Main(string[] args)
        {

            // Получение информации из Excel документа с именем SampleData.xls
            string pathFile = "SampleData.xls";
                      
            var excelFile = new ExcelQueryFactory(pathFile);

            // Вывод списка листов (Worksheet) в Excel документе  
            foreach( var item in excelFile.GetWorksheetNames())
            {
                Console.Write(item+" ");
            }
            Console.Write("\n");

            foreach (var item in excelFile.GetWorksheetNames())
            {
                Console.Write(item + ":");
                foreach (var itemRow in excelFile.GetColumnNames(item))
                {
                    Console.Write(itemRow + " ");
                }
                Console.Write("\n");
            }


            // установка документа "только для чтения"
            excelFile.ReadOnly = true;

            Console.ReadKey();
        }

        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 IEnumerable<Product> GetProductsFromFile(string filename)
        {
            var excel = new ExcelQueryFactory(filename);
            var worksheetNames = excel.GetWorksheetNames().ToArray();
            //
            //Mapping
            //
            for (int i = 0; i < worksheetNames.Length; i++)
            {
                var columnNames = excel.GetColumnNames(worksheetNames[i]);
                var titleRow = FindTitleRow(excel.WorksheetNoHeader(worksheetNames[i]));

                var codeIndex = 0;
                var priceIndex = GetColumnIndex(titleRow, "Price");

                bool skip = true;
                foreach (var product in excel.WorksheetNoHeader(worksheetNames[i])
                    .Where(x => x[codeIndex] != ""))
                {
                    if (skip)
                    {
                        skip = product[codeIndex].ToString().Trim() != "No.:";
                        continue;
                    }
                    yield return
                        new Product
                        {
                            Code = product[codeIndex].ToString().Trim(),
                            Price = product[priceIndex].Cast<decimal>()
                        };
                }
            }
        }

       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;
       }

 //[Test] This test is no longer passing. I believe it has something to do with my computer settings
 public void GetWorksheetNames_does_not_include_named_ranges()
 {
     var excel = new ExcelQueryFactory(_excelFileWithNamedRanges);
     var worksheetNames = excel.GetWorksheetNames();
     Assert.AreEqual(
         "Tabelle1, Tabelle3, WS2",
         string.Join(", ", worksheetNames.ToArray()));
 }

 public void GetWorksheetNames_does_not_include_builtin_worksheets()
 {
     var excel = new ExcelQueryFactory(_excelFileWithBuiltinWorksheets);
     var worksheetNames = excel.GetWorksheetNames();
     Assert.AreEqual(
         "AutoFiltered, ColumnMappings, MoreCompanies, NullCells, Paul's Worksheet, Sheet1",
         string.Join(", ", worksheetNames.ToArray()));
 }

        public void GetWorksheetNames_returns_worksheet_names()
        {
            var excel = new ExcelQueryFactory(_excelFileName);

            var worksheetNames = excel.GetWorksheetNames();
            Assert.AreEqual(
                "ColumnMappings, IMEX Table, More Companies, Null Dates, Range1, Sheet1, TrimSpaces",
                string.Join(", ", worksheetNames.ToArray()));
        }

 public IEnumerable<Surgery> GetAll()
 {
     var fullPath = Path.Combine(Environment.CurrentDirectory, _csvFilePath);
     var excel = new ExcelQueryFactory(fullPath);
     var excelGetWorksheetNames = excel.GetWorksheetNames();
     var surgeries = (from s in excel.Worksheet<Surgery>()
                      select s).ToList();
     return surgeries;
 }

        public List<OneMail> ReadExcelFileByPath(string path)
        {
            int i = 0;
            OneMail mail = new OneMail();
            string[] tmp = null;
            List<OneMail> ret = new List<OneMail>();
            ExcelQueryFactory excel = new ExcelQueryFactory(path);
            int Worksheetcount = excel.GetWorksheetNames().Count();

            for (i = 0; i < Worksheetcount; i++)
            {
                IQueryable<Row> query = from p in excel.Worksheet(i)
                                        select p;
                foreach (Row item in query)
                {
                    if (!string.IsNullOrEmpty(item[0].ToString().Trim()))
                    {
                        if (!string.IsNullOrEmpty(mail.alias))
                        {
                            ret.Add(mail);
                        }
                        mail = new OneMail();
                        tmp = item[2].ToString().Replace(')', ' ').Split('(');
                        mail.alias = item[0].ToString().Trim();
                        mail.TMin = tmp[0].Trim();
                        mail.UT = tmp[1].Trim();

                        /// temporary use
                        mail.MTD = mail.UT;
                        ///

                        mail.SRList = new List<SR>();
                    }
                    else
                    {
                        mail.SRList.Add(new SR(
                            item[1].ToString().Trim(),
                            item[3].ToString().Trim(),
                            Convert.ToInt32(item[2].ToString().Trim()
                            )));
                    }
                }
            }
            if (!string.IsNullOrEmpty(mail.alias))
            {
                ret.Add(mail);
            }
            return ret;
        }

 public void GetNamedRanges()
 {
     var excel = new ExcelQueryFactory(_excelFileWithNamedRanges);
     string sheetName = excel.GetWorksheetNames().First();
     Assert.AreEqual(sheetName, "Tabelle1");
     var namedRanges = excel.GetNamedRanges("NameCell");
     //            ExcelQueryArgs args = new ExcelQueryArgs
     //            {
     //                FileName = excel.FileName,
     //                WorksheetName = "NameCell",
     //                ReadOnly = true
     //            };
     //            var namedRanges = ExcelUtilities.GetNamedRanges(args);
     Assert.AreEqual(
         "NameCell",
         string.Join(", ", namedRanges.ToArray()));
 }

        public void Execute()
        {
            _excelQueryFactory = new ExcelQueryFactory(_excelFileName);

            using (_store = new DocumentStore { Url = _ravenUrl })
            {
                _store.Initialize();
                _store.DatabaseCommands.EnsureDatabaseExists(_ravenDatabaseName);

                using (_session = _store.OpenSession(_ravenDatabaseName))
                {
                    foreach (string worksheetName in _excelQueryFactory.GetWorksheetNames())
                        ImportWorksheet(worksheetName);

                    ResetDocumentKeyGenerationToDefault();
                }
            }
        }

        public void WriteDataFromExcelToXML()
        {
            XDocument doc = new XDocument();
            doc.Add(new XElement("Store"));

            string pathFile = "../../PriceProductBase.xlsx";
            var excelFile = new ExcelQueryFactory(pathFile);

            foreach (var item in excelFile.GetWorksheetNames())
            {
                var category = new XElement(item);
                List<Row> elements;

                if (item == "Prices")
                {
                    elements = (from a in excelFile.Worksheet(item) where a["КодТовара"] != "" select a).Take(5).ToList();
                }
                else
                {
                    elements = (from a in excelFile.Worksheet(item) select a).Take(5).ToList();
                }

                foreach (var el in elements)
                {
                    XElement element = new XElement("Element");
                    foreach(var itemRow in excelFile.GetColumnNames(item))
                    {
                        XElement data = new XElement(itemRow);
                        data.Value = el[itemRow];
                        element.Add(data);
                    }
                    category.Add(element);
                }

                doc.Root.Add(category);
            }

            excelFile.ReadOnly = true;

            doc.Save("PriceProductBase.xml");
        }

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

                excelFile.AddMapping("string prop name","column name");
                //excelFile.AddMapping<PayrollItemDataEntryCustom>(x => x.MonthID, PayrollItemDataEntry.MonthID.ToString());
                excelFile.AddMapping<Exam>(x => x.ExamDate, "ExamDate");
               
                excelFile.StrictMapping = StrictMappingType.ClassStrict;
                excelFile.TrimSpaces = TrimSpacesType.Both;
                excelFile.ReadOnly = true;

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

        static void Main(string[] args)
        {
            // Получение информации из Excel документа с именем SampleData.xls
            string pathFile = "PriceProductBase.xlsx";

            var excelFile = new ExcelQueryFactory(pathFile);

            string sheetName = "Orders";

            // Вывод списка листов (Worksheet) в Excel документе  

            var infoOrders = from a in excelFile.Worksheet(sheetName)
                             select a;

            foreach (var item in excelFile.GetWorksheetNames())
            {
                foreach (var itemRow in excelFile.GetColumnNames(item))
                {
                    Console.Write(itemRow + " ");
                }
                Console.Write("\n");
            }

            foreach (var item in infoOrders)
            {
                string pattern = "{0};{1};{2};{3};{4};{5};{6};";

                // Вывод информации из столбцов OrderDate и Total
                Console.WriteLine(string.Format(pattern, item["Номер заказа"],
                    item["Номер продукта"], item["F3"], 
                    item["F4"], item["F5"], 
                    item["F6"], item["F7"]));
            }


            // установка документа "только для чтения"
            excelFile.ReadOnly = true;

            Console.ReadKey();
        }

        public void LoadWorksheetMetaData()
        {
            var s = new StatusInfo();

            if (File.Exists(ExcelFilePath))
                try
                {
                    ExcelBook = new ExcelQueryFactory(ExcelFilePath);
                    WorkSheets = new ObservableCollection<string>(ExcelBook.GetWorksheetNames());
                    SelectedWorksheet = WorkSheets.First();
                }
                catch (Exception e)
                {
                    s.ErrorMessage = string.Format("File could not be processed {0}", e.Message);
                }
            else
                s.ErrorMessage = string.Format("File Not Found {0}", ExcelFilePath);

            Status = s;
        }